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 custom date range
  punch report invoice -c "Acme Corp" -d "2025-06-01 to 2025-06-30"
  
  # Generate invoice for specific project
  punch report invoice -p "Website Redesign" -d "2025-01-01 to 2025-01-31"`,
		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 ('last week', 'last month', 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 custom date range
  punch report timesheet -c "Acme Corp" -d "2025-06-01 to 2025-06-30"
  
  # Generate timesheet for specific project
  punch report timesheet -p "Website Redesign" -d "2025-01-01 to 2025-01-31"`,
		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 ('last week', 'last month', 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 custom date range
  punch report unified -c "Acme Corp" -d "2025-06-01 to 2025-06-30"
  
  # Generate unified report for specific project
  punch report unified -p "Website Redesign" -d "2025-01-01 to 2025-01-31"`,
		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 ('last week', 'last month', 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)
}