package importer

import (
	"bufio"
	"context"
	"encoding/json"
	"encoding/xml"
	"fmt"
	"io"
	"os"
	"path/filepath"
	"strings"

	"gopkg.in/yaml.v3"

	"github.com/SimonWaldherr/tinySQL/internal/storage"
)

// ============================================================================
// File Format Detection and Import
// ============================================================================

// ImportFile detects the file format and imports it into a tinySQL table.
// Supports: CSV, TSV, JSON, XML and common map data formats
// (GeoJSON, KML, Shapefile, OSM XML, MBTiles, routing graph JSON/CSV/NDJSON).
//
// Parameters:
//   - ctx: Context for cancellation
//   - db: Target database instance
//   - tenant: Tenant/schema name (use "default" for single-tenant mode)
//   - tableName: Target table name (if empty, derived from filename)
//   - filePath: Path to the file to import
//   - opts: Optional configuration (nil uses sensible defaults)
//
// Returns ImportResult with metadata and any error encountered.
func ImportFile(
	ctx context.Context,
	db *storage.DB,
	tenant string,
	tableName string,
	filePath string,
	opts *ImportOptions,
) (*ImportResult, error) {
	lowerPath := strings.ToLower(filePath)
	ext := strings.ToLower(filepath.Ext(lowerPath))

	// If table name not provided, derive from filename
	if tableName == "" {
		base := filepath.Base(filePath)
		tableName = strings.TrimSuffix(base, filepath.Ext(base))
		tableName = sanitizeTableName(tableName)
	}

	// Remove .gz extension if present for format detection
	if ext == ".gz" {
		base := strings.TrimSuffix(filePath, ".gz")
		ext = strings.ToLower(filepath.Ext(base))
		lowerPath = strings.ToLower(base)
	}
	isRoutingGraphJSON := strings.HasSuffix(lowerPath, ".graph.json") ||
		strings.HasSuffix(lowerPath, ".routinggraph.json") ||
		strings.HasSuffix(lowerPath, ".routing-graph.json") ||
		strings.HasSuffix(lowerPath, ".routing_graph.json")

	switch ext {
	case ".shp":
		// Shapefiles are multi-file sets; pass the path for the reader to open.
		return ImportShapefile(ctx, db, tenant, tableName, filePath, opts)
	case ".mbtiles":
		// MBTiles is a SQLite database; the importer needs random access to the
		// file path rather than a stream.
		return ImportMBTiles(ctx, db, tenant, tableName, filePath, opts)
	case ".pbf":
		return nil, fmt.Errorf("OSM PBF import is not supported yet; convert %s to .osm XML first", filepath.Base(filePath))
	}

	// Open stream-based formats after path-only formats have been handled.
	f, err := os.Open(filePath)
	if err != nil {
		return nil, fmt.Errorf("open file: %w", err)
	}
	defer func() { _ = f.Close() }()

	// Import based on format
	switch ext {
	case ".geojson":
		return ImportGeoJSON(ctx, db, tenant, tableName, f, opts)

	case ".topojson":
		return ImportTopoJSON(ctx, db, tenant, tableName, f, opts)

	case ".kml":
		return ImportKML(ctx, db, tenant, tableName, f, opts)

	case ".osm":
		return ImportOSM(ctx, db, tenant, tableName, f, opts)

	case ".rg", ".routinggraph", ".routing-graph", ".routing_graph":
		return ImportRoutingGraph(ctx, db, tenant, tableName, f, opts)

	case ".zip":
		return ImportShapefileZip(ctx, db, tenant, tableName, f, opts)

	case ".csv":
		if opts == nil {
			opts = &ImportOptions{}
		}
		if len(opts.DelimiterCandidates) == 0 {
			opts.DelimiterCandidates = []rune{','}
		}
		return ImportCSV(ctx, db, tenant, tableName, f, opts)

	case ".tsv", ".tab":
		if opts == nil {
			opts = &ImportOptions{}
		}
		opts.DelimiterCandidates = []rune{'\t'}
		return ImportCSV(ctx, db, tenant, tableName, f, opts)

	case ".xlsx":
		return ImportXLSX(ctx, db, tenant, tableName, f, opts)

	case ".json":
		if isRoutingGraphJSON {
			return ImportRoutingGraph(ctx, db, tenant, tableName, f, opts)
		}
		return ImportJSON(ctx, db, tenant, tableName, f, opts)

	case ".ndjson", ".jsonl":
		return ImportJSON(ctx, db, tenant, tableName, f, opts)

	case ".yaml", ".yml":
		if opts == nil {
			opts = &ImportOptions{}
		}
		// Simple YAML import: convert to records and reuse JSON path
		return ImportYAML(ctx, db, tenant, tableName, f, opts)

	case ".xml":
		if strings.HasSuffix(lowerPath, ".osm.xml") {
			return ImportOSM(ctx, db, tenant, tableName, f, opts)
		}
		return ImportXML(ctx, db, tenant, tableName, f, opts)

	default:
		// Try auto-detection by content
		return importByContent(ctx, db, tenant, tableName, f, opts)
	}
}

// importByContent attempts to detect format by examining file content.
func importByContent(
	ctx context.Context,
	db *storage.DB,
	tenant string,
	tableName string,
	f *os.File,
	opts *ImportOptions,
) (*ImportResult, error) {
	// Peek at first bytes
	br := bufio.NewReader(f)
	peek, _ := br.Peek(512)

	// Check for JSON
	trimmed := strings.TrimSpace(string(peek))
	if strings.HasPrefix(trimmed, "{") || strings.HasPrefix(trimmed, "[") {
		// Seek back to start
		if _, err := f.Seek(0, io.SeekStart); err != nil {
			return nil, fmt.Errorf("rewind JSON input: %w", err)
		}
		return ImportJSON(ctx, db, tenant, tableName, f, opts)
	}

	// Check for XML
	if strings.HasPrefix(trimmed, "<?xml") || strings.HasPrefix(trimmed, "<") {
		if _, err := f.Seek(0, io.SeekStart); err != nil {
			return nil, fmt.Errorf("rewind XML input: %w", err)
		}
		return ImportXML(ctx, db, tenant, tableName, f, opts)
	}

	// Default to CSV with auto-detection
	if _, err := f.Seek(0, io.SeekStart); err != nil {
		return nil, fmt.Errorf("rewind CSV input: %w", err)
	}
	return ImportCSV(ctx, db, tenant, tableName, f, opts)
}

// ImportYAML provides basic YAML -> table import. It supports a YAML list
// of mappings or a single mapping. This is a convenience helper and does
// not attempt to support every YAML feature; it converts YAML to Go maps
// and processes them similarly to JSON imports.
func ImportYAML(
	ctx context.Context,
	db *storage.DB,
	tenant string,
	tableName string,
	src io.Reader,
	opts *ImportOptions,
) (*ImportResult, error) {
	if opts == nil {
		opts = &ImportOptions{}
	}
	applyDefaults(opts)
	src = limitInput(ctx, src, opts)

	all, err := io.ReadAll(src)
	if err != nil {
		return nil, fmt.Errorf("read YAML: %w", err)
	}

	var records []map[string]any
	// Try YAML list
	if err := yaml.Unmarshal(all, &records); err == nil {
		// ok
	} else {
		// Try single mapping
		var single map[string]any
		if err2 := yaml.Unmarshal(all, &single); err2 == nil {
			records = append(records, single)
		} else {
			return nil, fmt.Errorf("unsupported YAML structure: %v / %v", err, err2)
		}
	}

	if len(records) == 0 {
		return nil, fmt.Errorf("no records found in YAML")
	}

	// Extract column names
	colNames := make([]string, 0, len(records[0]))
	for k := range records[0] {
		colNames = append(colNames, k)
	}
	sanitizeColumnNames(colNames)

	// Convert to strings for type inference
	sampleData := make([][]string, 0, len(records))
	for _, rec := range records {
		row := make([]string, len(colNames))
		for i, col := range colNames {
			if v, ok := rec[col]; ok && v != nil {
				row[i] = fmt.Sprintf("%v", v)
			}
		}
		sampleData = append(sampleData, row)
	}

	colTypes := inferOrDefaultColumnTypes(sampleData, len(colNames), opts)

	result := &ImportResult{Encoding: "utf-8", Errors: make([]string, 0), ColumnNames: colNames, ColumnTypes: colTypes}

	if opts.CreateTable {
		if err := createTable(ctx, db, tenant, tableName, colNames, colTypes); err != nil {
			return nil, err
		}
	}
	if opts.Truncate {
		if err := truncateTable(ctx, db, tenant, tableName); err != nil {
			return nil, err
		}
	}

	tbl, err := db.Get(tenant, tableName)
	if err != nil {
		return nil, fmt.Errorf("get table: %w", err)
	}

	for i, rec := range records {
		row := make([]any, len(colNames))
		for j, col := range colNames {
			if val, ok := rec[col]; ok {
				converted, err := convertValue(fmt.Sprintf("%v", val), colTypes[j], opts.DateTimeFormats, opts.NullLiterals)
				if err != nil && opts.StrictTypes {
					result.Errors = append(result.Errors, fmt.Sprintf("row %d, col %s: %v", i+1, col, err))
					result.RowsSkipped++
					continue
				}
				row[j] = converted
			}
		}
		tbl.Rows = append(tbl.Rows, row)
		result.RowsInserted++
	}

	return result, nil
}

// sanitizeTableName converts a filename to a valid table name.
func sanitizeTableName(name string) string {
	// Replace non-alphanumeric chars with underscore
	name = strings.Map(func(r rune) rune {
		if (r >= 'a' && r <= 'z') || (r >= 'A' && r <= 'Z') ||
			(r >= '0' && r <= '9') || r == '_' {
			return r
		}
		return '_'
	}, name)

	// Remove leading digits
	name = strings.TrimLeftFunc(name, func(r rune) bool {
		return r >= '0' && r <= '9'
	})

	if name == "" {
		name = "imported_table"
	}

	return name
}

// ============================================================================
// JSON Import
// ============================================================================

// ImportJSON imports JSON data from a reader into a tinySQL table.
// Supports:
//   - Array of objects: [{"id": 1, "name": "Alice"}, ...]
//   - JSON Lines format: {"id": 1, "name": "Alice"}\n{"id": 2, "name": "Bob"}
//   - Single object: {"id": 1, "name": "Alice"} (creates single-row table)
//
// decodeJSONArray decodes a JSON array from a reader
//
//nolint:gocyclo // JSON import supports arrays, objects, and error recovery in one routine.
func decodeJSONArray(br *bufio.Reader, result *ImportResult) ([]map[string]any, error) {
	records := []map[string]any{}
	dec := json.NewDecoder(br)
	// consume '['
	tok, err := dec.Token()
	if err != nil {
		return nil, fmt.Errorf("read JSON array token: %w", err)
	}
	if delim, ok := tok.(json.Delim); !ok || delim != '[' {
		return nil, fmt.Errorf("expected JSON array")
	}
	for dec.More() {
		var rec map[string]any
		if err := dec.Decode(&rec); err != nil {
			result.Errors = append(result.Errors, fmt.Sprintf("decode record: %v", err))
			continue
		}
		records = append(records, rec)
	}
	return records, nil
}

// decodeNDJSON decodes newline-delimited JSON from a reader
func decodeNDJSON(br *bufio.Reader, result *ImportResult, maxRecordBytes int) ([]map[string]any, error) {
	records := []map[string]any{}
	scanner := bufio.NewScanner(br)
	if maxRecordBytes > 0 {
		initialBuffer := maxRecordBytes
		if initialBuffer > 64*1024 {
			initialBuffer = 64 * 1024
		}
		scanner.Buffer(make([]byte, initialBuffer), maxRecordBytes)
	}
	for scanner.Scan() {
		line := strings.TrimSpace(scanner.Text())
		if line == "" {
			continue
		}
		var rec map[string]any
		if err := json.Unmarshal([]byte(line), &rec); err != nil {
			result.Errors = append(result.Errors, fmt.Sprintf("decode record: %v", err))
			continue
		}
		records = append(records, rec)
	}
	if err := scanner.Err(); err != nil {
		return nil, fmt.Errorf("scan NDJSON: %w", err)
	}
	return records, nil
}

// extractJSONRecords extracts records from JSON input (array or NDJSON)
func extractJSONRecords(src io.Reader, result *ImportResult, maxRecordBytes int) ([]map[string]any, error) {
	br := bufio.NewReader(src)
	peek, err := br.Peek(512)
	if err != nil && err != io.EOF {
		return nil, err
	}
	trimmed := strings.TrimSpace(string(peek))

	if strings.HasPrefix(trimmed, "[") {
		return decodeJSONArray(br, result)
	}
	if strings.HasPrefix(trimmed, "{") {
		return decodeNDJSON(br, result, maxRecordBytes)
	}
	return nil, fmt.Errorf("unsupported JSON format")
}

// buildJSONSampleData converts JSON records to sample data for type inference
func buildJSONSampleData(records []map[string]any, colNames []string) [][]string {
	sampleData := make([][]string, 0, len(records))
	for _, rec := range records {
		row := make([]string, len(colNames))
		for i, col := range colNames {
			if val, ok := rec[col]; ok && val != nil {
				row[i] = fmt.Sprintf("%v", val)
			}
		}
		sampleData = append(sampleData, row)
	}
	return sampleData
}

// ImportJSON imports JSON records from src into tableName for tenant.
func ImportJSON(
	ctx context.Context,
	db *storage.DB,
	tenant string,
	tableName string,
	src io.Reader,
	opts *ImportOptions,
) (*ImportResult, error) {
	if opts == nil {
		opts = &ImportOptions{}
	}
	applyDefaults(opts)
	src = limitInput(ctx, src, opts)

	result := &ImportResult{
		Encoding: "utf-8",
		Errors:   make([]string, 0),
	}

	// Extract records from JSON
	records, err := extractJSONRecords(src, result, opts.MaxRecordBytes)
	if err != nil {
		return nil, err
	}

	if len(records) == 0 {
		return nil, fmt.Errorf("no records found in JSON")
	}

	// Extract column names from first record
	colNames := make([]string, 0, len(records[0]))
	for key := range records[0] {
		colNames = append(colNames, key)
	}
	sanitizeColumnNames(colNames)
	result.ColumnNames = colNames

	// Build sample data for type inference
	sampleData := buildJSONSampleData(records, colNames)

	// Infer types
	colTypes := inferOrDefaultColumnTypes(sampleData, len(colNames), opts)
	result.ColumnTypes = colTypes

	// Create table
	if opts.CreateTable {
		if err := createTable(ctx, db, tenant, tableName, colNames, colTypes); err != nil {
			return nil, err
		}
	}

	// Truncate if requested
	if opts.Truncate {
		if err := truncateTable(ctx, db, tenant, tableName); err != nil {
			return nil, err
		}
	}

	// Insert data
	tbl, err := db.Get(tenant, tableName)
	if err != nil {
		return nil, fmt.Errorf("get table: %w", err)
	}

	for i, rec := range records {
		if err := ctx.Err(); err != nil {
			return nil, err
		}
		row := make([]any, len(colNames))
		for j, col := range colNames {
			if val, ok := rec[col]; ok {
				converted, err := convertValue(fmt.Sprintf("%v", val), colTypes[j],
					opts.DateTimeFormats, opts.NullLiterals)
				if err != nil && opts.StrictTypes {
					result.Errors = append(result.Errors,
						fmt.Sprintf("row %d, col %s: %v", i+1, col, err))
					result.RowsSkipped++
					continue
				}
				row[j] = converted
			}
		}
		tbl.Rows = append(tbl.Rows, row)
		result.RowsInserted++
	}

	return result, nil
}

// ============================================================================
// XML Import
// ============================================================================

// XMLRecord represents a generic XML element for table import.
type XMLRecord struct {
	XMLName xml.Name
	Attrs   []xml.Attr  `xml:",any,attr"`
	Content string      `xml:",chardata"`
	Nodes   []XMLRecord `xml:",any"`
}

// ImportXML imports XML data from a reader into a tinySQL table.
// Supports simple row-based XML like:
//
//	<root>
//	  <record id="1" name="Alice" />
//	  <record id="2" name="Bob" />
//	</root>
//
// or:
//
//	<root>
//	  <record><id>1</id><name>Alice</name></record>
//	  <record><id>2</id><name>Bob</name></record>
//	</root>
func ImportXML(
	ctx context.Context,
	db *storage.DB,
	tenant string,
	tableName string,
	src io.Reader,
	opts *ImportOptions,
) (*ImportResult, error) {
	if opts == nil {
		opts = &ImportOptions{}
	}
	applyDefaults(opts)
	src = limitInput(ctx, src, opts)

	result := &ImportResult{
		Encoding: "utf-8",
		Errors:   make([]string, 0),
	}

	var root XMLRecord
	if err := xml.NewDecoder(src).Decode(&root); err != nil {
		return nil, fmt.Errorf("decode XML: %w", err)
	}

	records := xmlRecords(root)
	if len(records) == 0 {
		return nil, fmt.Errorf("no records found in XML")
	}

	colNames := make([]string, 0, len(records[0]))
	for key := range records[0] {
		colNames = append(colNames, key)
	}
	sanitizeColumnNames(colNames)
	result.ColumnNames = colNames

	sampleData := buildJSONSampleData(records, colNames)
	colTypes := inferOrDefaultColumnTypes(sampleData, len(colNames), opts)
	result.ColumnTypes = colTypes

	if opts.CreateTable {
		if err := createTable(ctx, db, tenant, tableName, colNames, colTypes); err != nil {
			return nil, err
		}
	}
	if opts.Truncate {
		if err := truncateTable(ctx, db, tenant, tableName); err != nil {
			return nil, err
		}
	}

	tbl, err := db.Get(tenant, tableName)
	if err != nil {
		return nil, fmt.Errorf("get table: %w", err)
	}

	for i, rec := range records {
		select {
		case <-ctx.Done():
			return result, ctx.Err()
		default:
		}

		row := make([]any, len(colNames))
		for j, col := range colNames {
			if val, ok := rec[col]; ok {
				converted, err := convertValue(fmt.Sprintf("%v", val), colTypes[j],
					opts.DateTimeFormats, opts.NullLiterals)
				if err != nil && opts.StrictTypes {
					result.Errors = append(result.Errors,
						fmt.Sprintf("row %d, col %s: %v", i+1, col, err))
					result.RowsSkipped++
					continue
				}
				row[j] = converted
			}
		}
		tbl.Rows = append(tbl.Rows, row)
		result.RowsInserted++
	}

	return result, nil
}

func xmlRecords(root XMLRecord) []map[string]any {
	records := make([]map[string]any, 0, len(root.Nodes))
	for _, node := range root.Nodes {
		record := xmlElementMap(node)
		if len(record) > 0 {
			records = append(records, record)
		}
	}
	if len(records) > 0 {
		return records
	}

	record := xmlElementMap(root)
	if len(record) == 0 {
		return nil
	}
	return []map[string]any{record}
}

func xmlElementMap(node XMLRecord) map[string]any {
	record := make(map[string]any)
	for _, attr := range node.Attrs {
		record[attr.Name.Local] = attr.Value
	}
	for _, child := range node.Nodes {
		name := child.XMLName.Local
		if name == "" {
			continue
		}
		record[uniqueXMLFieldName(record, name)] = xmlElementValue(child)
	}
	if content := strings.TrimSpace(node.Content); content != "" && len(record) == 0 {
		record["value"] = content
	}
	return record
}

func xmlElementValue(node XMLRecord) any {
	if len(node.Attrs) == 0 && len(node.Nodes) == 0 {
		return strings.TrimSpace(node.Content)
	}

	record := xmlElementMap(node)
	if len(record) == 1 {
		if value, ok := record["value"]; ok {
			return value
		}
	}

	data, err := json.Marshal(record)
	if err != nil {
		return fmt.Sprintf("%v", record)
	}
	return string(data)
}

func uniqueXMLFieldName(record map[string]any, base string) string {
	if _, ok := record[base]; !ok {
		return base
	}
	for i := 2; ; i++ {
		name := fmt.Sprintf("%s_%d", base, i)
		if _, ok := record[name]; !ok {
			return name
		}
	}
}

// ============================================================================
// OpenFile - Convenience function to open and query files directly
// ============================================================================

// OpenFile opens a data file (CSV, TSV, JSON, XML) and returns a DB with the data loaded.
// The table name is derived from the filename unless specified in options.
//
// This is a convenience function for quick data exploration:
//
//	db, _ := importer.OpenFile(context.Background(), "data.csv", nil)
//	// Now you can query it with tinySQL
func OpenFile(ctx context.Context, filePath string, opts *ImportOptions) (*storage.DB, string, error) {
	db := storage.NewDB()
	tenant := "default"

	// Derive table name from file if not specified
	tableName := ""
	if opts != nil && opts.TableName != "" {
		tableName = opts.TableName
	} else {
		base := filepath.Base(filePath)
		tableName = strings.TrimSuffix(base, filepath.Ext(base))
		tableName = sanitizeTableName(tableName)
	}

	_, err := ImportFile(ctx, db, tenant, tableName, filePath, opts)
	if err != nil {
		return nil, "", err
	}

	return db, tableName, nil
}
