package commands

import (
	"context"
	"database/sql"
	"fmt"
	"path/filepath"
	"time"

	punchctx "punchcard/internal/context"
	"punchcard/internal/database"
	"punchcard/internal/queries"
	"punchcard/internal/reports"

	"github.com/spf13/cobra"
)

func NewReportCmd() *cobra.Command {
	cmd := &cobra.Command{
		Use:   "report",
		Short: "Generate reports from tracked time",
		Long:  "Generate various types of reports (invoices, timesheets, etc.) from tracked time data.",
	}

	cmd.AddCommand(NewReportInvoiceCmd())
	cmd.AddCommand(NewReportTimesheetCmd())
	cmd.AddCommand(NewReportUnifiedCmd())

	return cmd
}

func NewReportInvoiceCmd() *cobra.Command {
	cmd := &cobra.Command{
		Use:   "invoice",
		Short: "Generate a PDF invoice",
		Long: `Generate a PDF invoice from tracked time. Either --client or --project must be specified.

Examples:
  # Generate invoice for last month (default)
  punch report invoice -c "Acme Corp"

  # Generate invoice for last week
  punch report invoice -c "Acme Corp" -d "last week"

  # Generate invoice for this month
  punch report invoice -c "Acme Corp" -d "this month"

  # Generate invoice for a specific month (most recent February)
  punch report invoice -c "Acme Corp" -d "february"

  # Generate invoice for month and year
  punch report invoice -c "Acme Corp" -d "july 2023"

  # Generate invoice for custom date range
  punch report invoice -c "Acme Corp" -d "2025-06-01 to 2025-06-30"`,
		RunE: func(cmd *cobra.Command, args []string) error {
			return runInvoiceCommand(cmd, args)
		},
	}

	cmd.Flags().StringP("client", "c", "", "Generate invoice for specific client")
	cmd.Flags().StringP("project", "p", "", "Generate invoice for specific project")
	cmd.Flags().StringP("dates", "d", "last month", "Date range ('this week', 'this month', 'last week', 'last month', month names like 'february', 'month year' like 'july 2023', or 'YYYY-MM-DD to YYYY-MM-DD')")
	cmd.Flags().StringP("output", "o", "", "Output file path (default: auto-generated filename)")

	return cmd
}

func runInvoiceCommand(cmd *cobra.Command, args []string) error {
	// Get flag values
	clientName, _ := cmd.Flags().GetString("client")
	projectName, _ := cmd.Flags().GetString("project")
	dateStr, _ := cmd.Flags().GetString("dates")
	outputPath, _ := cmd.Flags().GetString("output")

	// Validate flags
	if clientName == "" && projectName == "" {
		return fmt.Errorf("either --client or --project must be specified")
	}
	if clientName != "" && projectName != "" {
		return fmt.Errorf("--client and --project are mutually exclusive")
	}

	// Parse date range
	dateRange, err := reports.ParseDateRange(dateStr)
	if err != nil {
		return fmt.Errorf("invalid date range: %w", err)
	}

	// Get database connection
	q := punchctx.GetDB(cmd.Context())
	if q == nil {
		var err error
		q, err = database.GetDB()
		if err != nil {
			return fmt.Errorf("failed to connect to database: %w", err)
		}
	}

	// Generate invoice based on client or project
	var invoiceData *reports.InvoiceData
	if clientName != "" {
		invoiceData, err = generateClientInvoice(q, clientName, dateRange)
	} else {
		invoiceData, err = generateProjectInvoice(q, projectName, dateRange)
	}
	if err != nil {
		return err
	}

	// Generate output filename if not specified
	if outputPath == "" {
		outputPath = reports.GenerateDefaultInvoiceFilename(invoiceData.ClientName, invoiceData.ProjectName, dateRange)
	}

	// Convert to absolute path
	outputPath, err = filepath.Abs(outputPath)
	if err != nil {
		return fmt.Errorf("failed to resolve output path: %w", err)
	}

	// Generate PDF
	err = reports.GenerateInvoicePDF(invoiceData, outputPath)
	if err != nil {
		return fmt.Errorf("failed to generate invoice PDF: %w", err)
	}

	if _, err := q.CreateInvoice(cmd.Context(), queries.CreateInvoiceParams{
		Year:        int64(invoiceData.DateRange.Start.Year()),
		Month:       int64(invoiceData.DateRange.Start.Month()),
		Number:      invoiceData.InvoiceNumber,
		ClientID:    invoiceData.ClientID,
		TotalAmount: int64(invoiceData.TotalAmount * 100),
	}); err != nil {
		return fmt.Errorf("failed to record invoice in database: %w", err)
	}

	fmt.Printf("Invoice generated successfully: %s\n", outputPath)
	fmt.Printf("Total hours: %.2f\n", invoiceData.TotalHours)
	fmt.Printf("Total amount: $%.2f\n", invoiceData.TotalAmount)

	return nil
}

func generateClientInvoice(q *queries.Queries, clientName string, dateRange reports.DateRange) (*reports.InvoiceData, error) {
	// Find client
	client, err := findClient(context.Background(), q, clientName)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("client not found: %s", clientName)
		}
		return nil, fmt.Errorf("failed to find client: %w", err)
	}

	// Get contractor data
	contractor, err := q.GetContractor(context.Background())
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("no contractor information found - please add contractor details first")
		}
		return nil, fmt.Errorf("failed to get contractor information: %w", err)
	}

	highestNumber, err := q.GetHighestInvoiceNumber(context.Background(), queries.GetHighestInvoiceNumberParams{
		Year:  int64(dateRange.Start.Year()),
		Month: int64(dateRange.Start.Month()),
	})
	if err != nil {
		return nil, fmt.Errorf("failed to get highest invoice number: %w", err)
	}

	// Get invoice data
	entries, err := q.GetInvoiceDataByClient(context.Background(), queries.GetInvoiceDataByClientParams{
		ClientID:  client.ID,
		StartTime: dateRange.Start,
		EndTime:   dateRange.End,
	})
	if err != nil {
		return nil, fmt.Errorf("failed to get invoice data: %w", err)
	}

	if len(entries) == 0 {
		return nil, fmt.Errorf("no completed time entries found for client %s in the specified date range", client.Name)
	}

	return reports.GenerateInvoiceData(entries, client.ID, client.Name, "", contractor, highestNumber+1, dateRange)
}

func generateProjectInvoice(q *queries.Queries, projectName string, dateRange reports.DateRange) (*reports.InvoiceData, error) {
	// We need to find the project, but we need the client info too
	// Let's first find all projects with this name
	project, err := findProject(context.Background(), q, projectName)
	if err != nil {
		return nil, fmt.Errorf("failed to find project: %w", err)
	}

	// Get client info
	clients, err := q.FindClient(context.Background(), queries.FindClientParams{
		ID:   project.ClientID,
		Name: "",
	})
	if err != nil || len(clients) == 0 {
		return nil, fmt.Errorf("failed to find client for project")
	}
	client := clients[0]

	// Get contractor data
	contractor, err := q.GetContractor(context.Background())
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("no contractor information found - please add contractor details first")
		}
		return nil, fmt.Errorf("failed to get contractor information: %w", err)
	}

	highestNumber, err := q.GetHighestInvoiceNumber(context.Background(), queries.GetHighestInvoiceNumberParams{
		Year:  int64(dateRange.Start.Year()),
		Month: int64(dateRange.Start.Month()),
	})
	if err != nil {
		return nil, fmt.Errorf("failed to get highest invoice number: %w", err)
	}

	// Get invoice data
	entries, err := q.GetInvoiceDataByProject(context.Background(), queries.GetInvoiceDataByProjectParams{
		ProjectID: project.ID,
		StartTime: dateRange.Start,
		EndTime:   dateRange.End,
	})
	if err != nil {
		return nil, fmt.Errorf("failed to get invoice data: %w", err)
	}

	if len(entries) == 0 {
		return nil, fmt.Errorf("no completed time entries found for project %s in the specified date range", projectName)
	}

	return reports.GenerateInvoiceData(entries, client.ID, client.Name, projectName, contractor, highestNumber+1, dateRange)
}

func NewReportTimesheetCmd() *cobra.Command {
	cmd := &cobra.Command{
		Use:   "timesheet",
		Short: "Generate a PDF timesheet",
		Long: `Generate a PDF timesheet report from tracked time. Either --client or --project must be specified.

Examples:
  # Generate timesheet for last month (default)
  punch report timesheet -c "Acme Corp"

  # Generate timesheet for last week
  punch report timesheet -c "Acme Corp" -d "last week"

  # Generate timesheet for this month
  punch report timesheet -c "Acme Corp" -d "this month"

  # Generate timesheet for a specific month (most recent February)
  punch report timesheet -c "Acme Corp" -d "february"

  # Generate timesheet for month and year
  punch report timesheet -c "Acme Corp" -d "july 2023"

  # Generate timesheet for custom date range
  punch report timesheet -c "Acme Corp" -d "2025-06-01 to 2025-06-30"`,
		RunE: func(cmd *cobra.Command, args []string) error {
			return runTimesheetCommand(cmd, args)
		},
	}

	cmd.Flags().StringP("client", "c", "", "Generate timesheet for specific client")
	cmd.Flags().StringP("project", "p", "", "Generate timesheet for specific project")
	cmd.Flags().StringP("dates", "d", "last month", "Date range ('this week', 'this month', 'last week', 'last month', month names like 'february', 'month year' like 'july 2023', or 'YYYY-MM-DD to YYYY-MM-DD')")
	cmd.Flags().StringP("output", "o", "", "Output file path (default: auto-generated filename)")
	cmd.Flags().StringP("timezone", "t", "Local", "Timezone for displaying times (e.g., 'America/New_York', 'UTC', or 'Local')")

	return cmd
}

func runTimesheetCommand(cmd *cobra.Command, args []string) error {
	// Get flag values
	clientName, _ := cmd.Flags().GetString("client")
	projectName, _ := cmd.Flags().GetString("project")
	dateStr, _ := cmd.Flags().GetString("dates")
	outputPath, _ := cmd.Flags().GetString("output")
	timezone, _ := cmd.Flags().GetString("timezone")

	// Validate flags
	if clientName == "" && projectName == "" {
		return fmt.Errorf("either --client or --project must be specified")
	}
	if clientName != "" && projectName != "" {
		return fmt.Errorf("--client and --project are mutually exclusive")
	}

	// Parse date range
	dateRange, err := reports.ParseDateRange(dateStr)
	if err != nil {
		return fmt.Errorf("invalid date range: %w", err)
	}

	// Parse timezone
	var loc *time.Location
	if timezone == "Local" {
		loc = time.Local
	} else {
		loc, err = time.LoadLocation(timezone)
		if err != nil {
			return fmt.Errorf("invalid timezone '%s': %w", timezone, err)
		}
	}

	// Get database connection
	q := punchctx.GetDB(cmd.Context())
	if q == nil {
		var err error
		q, err = database.GetDB()
		if err != nil {
			return fmt.Errorf("failed to connect to database: %w", err)
		}
	}

	// Generate timesheet based on client or project
	var timesheetData *reports.TimesheetData
	if clientName != "" {
		timesheetData, err = generateClientTimesheet(q, clientName, dateRange, loc)
	} else {
		timesheetData, err = generateProjectTimesheet(q, projectName, dateRange, loc)
	}
	if err != nil {
		return err
	}

	// Generate output filename if not specified
	if outputPath == "" {
		outputPath = reports.GenerateDefaultTimesheetFilename(timesheetData.ClientName, timesheetData.ProjectName, dateRange)
	}

	// Convert to absolute path
	outputPath, err = filepath.Abs(outputPath)
	if err != nil {
		return fmt.Errorf("failed to resolve output path: %w", err)
	}

	// Generate PDF
	err = reports.GenerateTimesheetPDF(timesheetData, outputPath)
	if err != nil {
		return fmt.Errorf("failed to generate timesheet PDF: %w", err)
	}

	fmt.Printf("Timesheet generated successfully: %s\n", outputPath)
	fmt.Printf("Total hours: %.2f\n", timesheetData.TotalHours)
	fmt.Printf("Total entries: %d\n", len(timesheetData.Entries))

	return nil
}

func generateClientTimesheet(q *queries.Queries, clientName string, dateRange reports.DateRange, loc *time.Location) (*reports.TimesheetData, error) {
	// Find client
	client, err := findClient(context.Background(), q, clientName)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("client not found: %s", clientName)
		}
		return nil, fmt.Errorf("failed to find client: %w", err)
	}

	// Get contractor data
	contractor, err := q.GetContractor(context.Background())
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("no contractor information found - please add contractor details first")
		}
		return nil, fmt.Errorf("failed to get contractor information: %w", err)
	}

	// Get timesheet data
	entries, err := q.GetTimesheetDataByClient(context.Background(), queries.GetTimesheetDataByClientParams{
		ClientID:  client.ID,
		StartTime: dateRange.Start,
		EndTime:   dateRange.End,
	})
	if err != nil {
		return nil, fmt.Errorf("failed to get timesheet data: %w", err)
	}

	if len(entries) == 0 {
		return nil, fmt.Errorf("no completed time entries found for client %s in the specified date range", client.Name)
	}

	return reports.GenerateTimesheetData(entries, client.ID, client.Name, "", contractor, dateRange, loc)
}

func generateProjectTimesheet(q *queries.Queries, projectName string, dateRange reports.DateRange, loc *time.Location) (*reports.TimesheetData, error) {
	// Find project
	project, err := findProject(context.Background(), q, projectName)
	if err != nil {
		return nil, fmt.Errorf("failed to find project: %w", err)
	}

	// Get client info
	clients, err := q.FindClient(context.Background(), queries.FindClientParams{
		ID:   project.ClientID,
		Name: "",
	})
	if err != nil || len(clients) == 0 {
		return nil, fmt.Errorf("failed to find client for project")
	}
	client := clients[0]

	// Get contractor data
	contractor, err := q.GetContractor(context.Background())
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("no contractor information found - please add contractor details first")
		}
		return nil, fmt.Errorf("failed to get contractor information: %w", err)
	}

	// Get timesheet data
	entries, err := q.GetTimesheetDataByProject(context.Background(), queries.GetTimesheetDataByProjectParams{
		ProjectID: project.ID,
		StartTime: dateRange.Start,
		EndTime:   dateRange.End,
	})
	if err != nil {
		return nil, fmt.Errorf("failed to get timesheet data: %w", err)
	}

	if len(entries) == 0 {
		return nil, fmt.Errorf("no completed time entries found for project %s in the specified date range", projectName)
	}

	return reports.GenerateTimesheetData(entries, client.ID, client.Name, projectName, contractor, dateRange, loc)
}

func NewReportUnifiedCmd() *cobra.Command {
	cmd := &cobra.Command{
		Use:   "unified",
		Short: "Generate a unified PDF report (invoice + timesheet)",
		Long: `Generate a unified PDF report combining invoice and timesheet on separate pages. Either --client or --project must be specified.

Examples:
  # Generate unified report for last month (default)
  punch report unified -c "Acme Corp"

  # Generate unified report for last week
  punch report unified -c "Acme Corp" -d "last week"

  # Generate unified report for this month
  punch report unified -c "Acme Corp" -d "this month"

  # Generate unified report for a specific month (most recent February)
  punch report unified -c "Acme Corp" -d "february"

  # Generate unified report for month and year
  punch report unified -c "Acme Corp" -d "july 2023"

  # Generate unified report for custom date range
  punch report unified -c "Acme Corp" -d "2025-06-01 to 2025-06-30"`,
		RunE: func(cmd *cobra.Command, args []string) error {
			return runUnifiedCommand(cmd, args)
		},
	}

	cmd.Flags().StringP("client", "c", "", "Generate unified report for specific client")
	cmd.Flags().StringP("project", "p", "", "Generate unified report for specific project")
	cmd.Flags().StringP("dates", "d", "last month", "Date range ('this week', 'this month', 'last week', 'last month', month names like 'february', 'month year' like 'july 2023', or 'YYYY-MM-DD to YYYY-MM-DD')")
	cmd.Flags().StringP("output", "o", "", "Output file path (default: auto-generated filename)")
	cmd.Flags().StringP("timezone", "t", "Local", "Timezone for displaying times (e.g., 'America/New_York', 'UTC', or 'Local')")

	return cmd
}

func runUnifiedCommand(cmd *cobra.Command, args []string) error {
	// Get flag values
	clientName, _ := cmd.Flags().GetString("client")
	projectName, _ := cmd.Flags().GetString("project")
	dateStr, _ := cmd.Flags().GetString("dates")
	outputPath, _ := cmd.Flags().GetString("output")
	timezone, _ := cmd.Flags().GetString("timezone")

	// Validate flags
	if clientName == "" && projectName == "" {
		return fmt.Errorf("either --client or --project must be specified")
	}
	if clientName != "" && projectName != "" {
		return fmt.Errorf("--client and --project are mutually exclusive")
	}

	// Parse date range
	dateRange, err := reports.ParseDateRange(dateStr)
	if err != nil {
		return fmt.Errorf("invalid date range: %w", err)
	}

	// Parse timezone
	var loc *time.Location
	if timezone == "Local" {
		loc = time.Local
	} else {
		loc, err = time.LoadLocation(timezone)
		if err != nil {
			return fmt.Errorf("invalid timezone '%s': %w", timezone, err)
		}
	}

	// Get database connection
	q := punchctx.GetDB(cmd.Context())
	if q == nil {
		var err error
		q, err = database.GetDB()
		if err != nil {
			return fmt.Errorf("failed to connect to database: %w", err)
		}
	}

	// Generate unified report based on client or project
	var unifiedData *reports.UnifiedReportData
	if clientName != "" {
		unifiedData, err = generateClientUnified(q, clientName, dateRange, loc)
	} else {
		unifiedData, err = generateProjectUnified(q, projectName, dateRange, loc)
	}
	if err != nil {
		return err
	}

	// Generate output filename if not specified
	if outputPath == "" {
		outputPath = reports.GenerateDefaultUnifiedFilename(unifiedData.InvoiceData.ClientName, unifiedData.InvoiceData.ProjectName, dateRange)
	}

	// Convert to absolute path
	outputPath, err = filepath.Abs(outputPath)
	if err != nil {
		return fmt.Errorf("failed to resolve output path: %w", err)
	}

	// Generate PDF
	err = reports.GenerateUnifiedPDF(unifiedData, outputPath)
	if err != nil {
		return fmt.Errorf("failed to generate unified PDF: %w", err)
	}

	// Record invoice in database
	if _, err := q.CreateInvoice(cmd.Context(), queries.CreateInvoiceParams{
		Year:        int64(unifiedData.InvoiceData.DateRange.Start.Year()),
		Month:       int64(unifiedData.InvoiceData.DateRange.Start.Month()),
		Number:      unifiedData.InvoiceData.InvoiceNumber,
		ClientID:    unifiedData.InvoiceData.ClientID,
		TotalAmount: int64(unifiedData.InvoiceData.TotalAmount * 100),
	}); err != nil {
		return fmt.Errorf("failed to record invoice in database: %w", err)
	}

	fmt.Printf("Unified report generated successfully: %s\n", outputPath)
	fmt.Printf("Invoice total: $%.2f (%d hours)\n", unifiedData.InvoiceData.TotalAmount, int(unifiedData.InvoiceData.TotalHours))
	fmt.Printf("Timesheet total: %.2f hours (%d entries)\n", unifiedData.TimesheetData.TotalHours, len(unifiedData.TimesheetData.Entries))

	return nil
}

func generateClientUnified(q *queries.Queries, clientName string, dateRange reports.DateRange, loc *time.Location) (*reports.UnifiedReportData, error) {
	// Find client
	client, err := findClient(context.Background(), q, clientName)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("client not found: %s", clientName)
		}
		return nil, fmt.Errorf("failed to find client: %w", err)
	}

	// Get contractor data
	contractor, err := q.GetContractor(context.Background())
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("no contractor information found - please add contractor details first")
		}
		return nil, fmt.Errorf("failed to get contractor information: %w", err)
	}

	// Get invoice number
	highestNumber, err := q.GetHighestInvoiceNumber(context.Background(), queries.GetHighestInvoiceNumberParams{
		Year:  int64(dateRange.Start.Year()),
		Month: int64(dateRange.Start.Month()),
	})
	if err != nil {
		return nil, fmt.Errorf("failed to get highest invoice number: %w", err)
	}

	// Get data for both invoice and timesheet
	invoiceEntries, err := q.GetInvoiceDataByClient(context.Background(), queries.GetInvoiceDataByClientParams{
		ClientID:  client.ID,
		StartTime: dateRange.Start,
		EndTime:   dateRange.End,
	})
	if err != nil {
		return nil, fmt.Errorf("failed to get invoice data: %w", err)
	}

	timesheetEntries, err := q.GetTimesheetDataByClient(context.Background(), queries.GetTimesheetDataByClientParams{
		ClientID:  client.ID,
		StartTime: dateRange.Start,
		EndTime:   dateRange.End,
	})
	if err != nil {
		return nil, fmt.Errorf("failed to get timesheet data: %w", err)
	}

	if len(invoiceEntries) == 0 || len(timesheetEntries) == 0 {
		return nil, fmt.Errorf("no completed time entries found for client %s in the specified date range", client.Name)
	}

	return reports.GenerateUnifiedReportData(invoiceEntries, client.ID, client.Name, "", contractor, highestNumber+1, dateRange, loc)
}

func generateProjectUnified(q *queries.Queries, projectName string, dateRange reports.DateRange, loc *time.Location) (*reports.UnifiedReportData, error) {
	// Find project
	project, err := findProject(context.Background(), q, projectName)
	if err != nil {
		return nil, fmt.Errorf("failed to find project: %w", err)
	}

	// Get client info
	clients, err := q.FindClient(context.Background(), queries.FindClientParams{
		ID:   project.ClientID,
		Name: "",
	})
	if err != nil || len(clients) == 0 {
		return nil, fmt.Errorf("failed to find client for project")
	}
	client := clients[0]

	// Get contractor data
	contractor, err := q.GetContractor(context.Background())
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("no contractor information found - please add contractor details first")
		}
		return nil, fmt.Errorf("failed to get contractor information: %w", err)
	}

	// Get invoice number
	highestNumber, err := q.GetHighestInvoiceNumber(context.Background(), queries.GetHighestInvoiceNumberParams{
		Year:  int64(dateRange.Start.Year()),
		Month: int64(dateRange.Start.Month()),
	})
	if err != nil {
		return nil, fmt.Errorf("failed to get highest invoice number: %w", err)
	}

	// Get data for both invoice and timesheet
	invoiceEntries, err := q.GetInvoiceDataByProject(context.Background(), queries.GetInvoiceDataByProjectParams{
		ProjectID: project.ID,
		StartTime: dateRange.Start,
		EndTime:   dateRange.End,
	})
	if err != nil {
		return nil, fmt.Errorf("failed to get invoice data: %w", err)
	}

	timesheetEntries, err := q.GetTimesheetDataByProject(context.Background(), queries.GetTimesheetDataByProjectParams{
		ProjectID: project.ID,
		StartTime: dateRange.Start,
		EndTime:   dateRange.End,
	})
	if err != nil {
		return nil, fmt.Errorf("failed to get timesheet data: %w", err)
	}

	if len(invoiceEntries) == 0 || len(timesheetEntries) == 0 {
		return nil, fmt.Errorf("no completed time entries found for project %s in the specified date range", projectName)
	}

	return reports.GenerateUnifiedReportData(invoiceEntries, client.ID, client.Name, projectName, contractor, highestNumber+1, dateRange, loc)
}