package cmd

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

	"skraak/tools"
)

// RunSQL handles the "sql" subcommand
//
// 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) error {
	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 {
		return fmt.Errorf("parsing flags: %w", err)
	}

	if *dbPath == "" {
		fs.Usage()
		return fmt.Errorf("--db is required")
	}

	remaining := fs.Args()
	if len(remaining) == 0 {
		fs.Usage()
		return fmt.Errorf("query is required")
	}
	query := strings.Join(remaining, " ")

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

	output, err := tools.ExecuteSQL(context.Background(), input)
	if err != nil {
		return fmt.Errorf("executing query: %w", err)
	}

	enc := json.NewEncoder(os.Stdout)
	enc.SetIndent("", "  ")
	if err := enc.Encode(output); err != nil {
		return fmt.Errorf("encoding output: %w", err)
	}
	return nil
}