1 files changed, 275 insertions, 0 deletions
diff --git a/internal/commands/import.go b/internal/commands/import.go
new file mode 100644
index 0000000..a767923
--- /dev/null
+++ b/internal/commands/import.go
@@ -0,0 +1,275 @@
+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 ""
+}