package cmd

import (
	"context"
	"encoding/json"
	"flag"
	"fmt"
	"os"
	"strings"

	"skraak/tools"
)

// RunSQL handles the "sql" subcommand
// RunSQL handles CLI SQL query execution with direct database access
//
// JSON output schema:
//
//	{
//	  "rows": [{"column_name": value, ...}, ...],  // Query result rows
//	  "row_count": int,                             // Number of rows returned
//	  "columns": [                                   // Column metadata
//	    {"name": string, "type": string}
//	  ],
//	  "limited": bool,                               // Whether results were truncated due to row limit
//	  "query_executed": string                       // The actual query executed (with LIMIT applied)
//	}
func RunSQL(args []string) {
	fs := flag.NewFlagSet("sql", flag.ExitOnError)
	dbPath := fs.String("db", "", "Path to DuckDB database (required)")
	limit := fs.Int("limit", 0, "Maximum rows to return (default 1000, max 10000)")

	fs.Usage = func() {
		fmt.Fprintf(os.Stderr, "Usage: skraak sql --db <path> [options] <query>\n\n")
		fmt.Fprintf(os.Stderr, "Execute a SQL SELECT query against the database.\n\n")
		fmt.Fprintf(os.Stderr, "Options:\n")
		fs.PrintDefaults()
		fmt.Fprintf(os.Stderr, "\nExamples:\n")
		fmt.Fprintf(os.Stderr, "  skraak sql --db ./db/skraak.duckdb \"SELECT COUNT(*) FROM file WHERE active = true\"\n")
		fmt.Fprintf(os.Stderr, "  skraak sql --db ./db/skraak.duckdb --limit 10 \"SELECT * FROM dataset\"\n")
	}

	if err := fs.Parse(args); err != nil {
		os.Exit(1)
	}

	if *dbPath == "" {
		fmt.Fprintf(os.Stderr, "Error: --db is required\n\n")
		fs.Usage()
		os.Exit(1)
	}

	// Remaining args are the query
	remaining := fs.Args()
	if len(remaining) == 0 {
		fmt.Fprintf(os.Stderr, "Error: query is required\n\n")
		fs.Usage()
		os.Exit(1)
	}
	query := strings.Join(remaining, " ")

	tools.SetDBPath(*dbPath)

	input := tools.ExecuteSQLInput{
		Query: query,
	}
	if *limit > 0 {
		input.Limit = limit
	}

	output, err := tools.ExecuteSQL(context.Background(), input)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Error: %v\n", err)
		os.Exit(1)
	}

	enc := json.NewEncoder(os.Stdout)
	enc.SetIndent("", "  ")
	if err := enc.Encode(output); err != nil {
		fmt.Fprintf(os.Stderr, "Error encoding output: %v\n", err)
		os.Exit(1)
	}
}