package commands

import (
	"context"
	"database/sql"
	"encoding/csv"
	"fmt"
	"os"
	"strings"
	"time"

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

	"github.com/spf13/cobra"
)

func NewImportCmd() *cobra.Command {
	cmd := &cobra.Command{
		Use:   "import [file]",
		Short: "Import time entries from external sources",
		Long: `Import time entries from various external time tracking tools and formats. Use --source to specify the format.

For Clockify exports:
1. Go to REPORTS > DETAILED in the sidebar
2. Select your desired time range with the date range picker in the top right
3. Click Export > "Save as CSV" from the menu above the table header`,
		Args: cobra.ExactArgs(1),
		RunE: func(cmd *cobra.Command, args []string) error {
			filepath := args[0]

			// Get flag values
			source, err := cmd.Flags().GetString("source")
			if err != nil {
				return fmt.Errorf("failed to get source flag: %w", err)
			}

			timezone, err := cmd.Flags().GetString("timezone")
			if err != nil {
				return fmt.Errorf("failed to get timezone flag: %w", err)
			}

			// Get database from context (for tests) or create new connection
			queries := punchctx.GetDB(cmd.Context())
			if queries == nil {
				var err error
				queries, err = database.GetDB()
				if err != nil {
					return fmt.Errorf("failed to connect to database: %w", err)
				}
			}

			// Select import function based on source
			switch source {
			case "clockify":
				return importClockifyCSV(queries, filepath, timezone)
			case "":
				return fmt.Errorf("required flag \"source\" not set")
			default:
				return fmt.Errorf("unsupported source: %s", source)
			}
		},
	}

	cmd.Flags().StringP("timezone", "t", "Local", "Timezone of the CSV data (e.g., 'America/New_York', 'UTC', or 'Local')")
	cmd.Flags().StringP("source", "s", "", "Source format of the import file (supported: clockify)")
	cmd.MarkFlagRequired("source")

	return cmd
}

type clockifyEntry struct {
	Project     string
	Client      string
	Description string
	StartDate   string
	StartTime   string
	EndDate     string
	EndTime     string
}

func importClockifyCSV(queries *queries.Queries, filepath, timezone string) error {
	file, err := os.Open(filepath)
	if err != nil {
		return fmt.Errorf("failed to open file: %w", err)
	}
	defer file.Close()

	reader := csv.NewReader(file)
	records, err := reader.ReadAll()
	if err != nil {
		return fmt.Errorf("failed to read CSV: %w", err)
	}

	if len(records) < 2 {
		return fmt.Errorf("CSV file must have at least a header and one data row")
	}

	header := records[0]

	// Find column indices for the fields we actually use
	columnIndices := make(map[string]int)
	for i, columnName := range header {
		columnIndices[columnName] = i
	}

	// Check for required columns (only the ones we actually use)
	requiredColumns := []string{"Project", "Client", "Start Date", "Start Time", "End Date", "End Time"}
	for _, required := range requiredColumns {
		if _, exists := columnIndices[required]; !exists {
			return fmt.Errorf("CSV file missing required column: %s", required)
		}
	}

	// Optional billable columns may be present (unused for now)

	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)
		}
	}

	importedCount := 0
	for i, record := range records[1:] {
		if len(record) < len(header) {
			fmt.Printf("Warning: Row %d has insufficient columns, skipping\n", i+2)
			continue
		}

		// Extract values using column indices
		entry := clockifyEntry{
			Project:     getColumnValue(record, columnIndices, "Project"),
			Client:      getColumnValue(record, columnIndices, "Client"),
			Description: getColumnValue(record, columnIndices, "Description"),
			StartDate:   getColumnValue(record, columnIndices, "Start Date"),
			StartTime:   getColumnValue(record, columnIndices, "Start Time"),
			EndDate:     getColumnValue(record, columnIndices, "End Date"),
			EndTime:     getColumnValue(record, columnIndices, "End Time"),
		}

		if entry.Client == "" || entry.Project == "" {
			fmt.Printf("Warning: Row %d missing client or project, skipping\n", i+2)
			continue
		}

		if err := importSingleEntry(queries, entry, loc); err != nil {
			fmt.Printf("Warning: Row %d failed to import: %v\n", i+2, err)
			continue
		}

		importedCount++
	}

	fmt.Printf("Successfully imported %d time entries\n", importedCount)
	return nil
}

func importSingleEntry(q *queries.Queries, entry clockifyEntry, loc *time.Location) error {
	client, err := getOrCreateClient(q, entry.Client)
	if err != nil {
		return fmt.Errorf("failed to get or create client: %w", err)
	}

	project, err := getOrCreateProject(q, entry.Project, client.ID)
	if err != nil {
		return fmt.Errorf("failed to get or create project: %w", err)
	}

	startTime, err := parseClockifyDateTime(entry.StartDate, entry.StartTime, loc)
	if err != nil {
		return fmt.Errorf("failed to parse start time: %w", err)
	}

	endTime, err := parseClockifyDateTime(entry.EndDate, entry.EndTime, loc)
	if err != nil {
		return fmt.Errorf("failed to parse end time: %w", err)
	}

	var projectID sql.NullInt64
	if project != nil {
		projectID = sql.NullInt64{Int64: project.ID, Valid: true}
	}

	_, err = q.CreateTimeEntryWithTimes(context.Background(), queries.CreateTimeEntryWithTimesParams{
		StartTime:    startTime.UTC(),
		EndTime:      sql.NullTime{Time: endTime.UTC(), Valid: true},
		Description:  sql.NullString{String: entry.Description, Valid: entry.Description != ""},
		ClientID:     client.ID,
		ProjectID:    projectID,
		BillableRate: sql.NullInt64{},
	})

	return err
}

func getOrCreateClient(q *queries.Queries, name string) (*queries.Client, error) {
	client, err := q.GetClientByName(context.Background(), name)
	if err == nil {
		return &client, nil
	}

	if err != sql.ErrNoRows {
		return nil, err
	}

	createdClient, err := q.CreateClient(context.Background(), queries.CreateClientParams{
		Name:         name,
		Email:        sql.NullString{},
		BillableRate: sql.NullInt64{},
	})
	if err != nil {
		return nil, err
	}

	return &createdClient, nil
}

func getOrCreateProject(q *queries.Queries, name string, clientID int64) (*queries.Project, error) {
	project, err := q.GetProjectByNameAndClient(context.Background(), queries.GetProjectByNameAndClientParams{
		Name:     name,
		ClientID: clientID,
	})
	if err == nil {
		return &project, nil
	}

	if err != sql.ErrNoRows {
		return nil, err
	}

	createdProject, err := q.CreateProject(context.Background(), queries.CreateProjectParams{
		Name:         name,
		ClientID:     clientID,
		BillableRate: sql.NullInt64{},
	})
	if err != nil {
		return nil, err
	}

	return &createdProject, nil
}

func parseClockifyDateTime(date, timeStr string, loc *time.Location) (time.Time, error) {
	dateTimeStr := date + " " + timeStr

	layouts := []string{
		"01/02/2006 03:04:05 PM",
		"1/2/2006 03:04:05 PM",
		"01/02/2006 3:04:05 PM",
		"1/2/2006 3:04:05 PM",
		"01/02/2006 15:04:05",
		"1/2/2006 15:04:05",
	}

	for _, layout := range layouts {
		if t, err := time.ParseInLocation(layout, dateTimeStr, loc); err == nil {
			return t, nil
		}
	}

	return time.Time{}, fmt.Errorf("unable to parse date/time: %s", dateTimeStr)
}

// getColumnValue safely extracts a column value from a record using the column index map
func getColumnValue(record []string, columnIndices map[string]int, columnName string) string {
	if index, exists := columnIndices[columnName]; exists && index < len(record) {
		return strings.TrimSpace(record[index])
	}
	return ""
}