// Package importer provides automatic file import with type detection for tinySQL.
//
// This package supports importing structured data from various formats (CSV, TSV, JSON, XML)
// directly into tinySQL tables. It auto-detects delimiters, headers, encodings, and column
// types to minimize manual configuration.
//
// Features:
//   - Auto-detect delimiter: ',', ';', '\t', '|' (configurable)
//   - Auto-detect header row (configurable override)
//   - Encoding: UTF-8, UTF-8 BOM, UTF-16LE/BE (BOM-based)
//   - Transparent GZIP input
//   - Smart type inference (INT, FLOAT, BOOL, TEXT, TIME, JSON)
//   - Streaming import with batched INSERTs
//   - Optional CREATE TABLE and TRUNCATE
//
// Example:
//
//	import "github.com/SimonWaldherr/tinySQL/internal/importer"
//
//	f, _ := os.Open("data.csv")
//	result, err := importer.ImportCSV(ctx, db, tenant, "mytable", f, nil)
//	fmt.Printf("Imported %d rows with %d columns\n", result.RowsInserted, len(result.ColumnNames))
package importer

import (
	"bufio"
	"bytes"
	"compress/gzip"
	"context"
	"encoding/csv"
	"errors"
	"fmt"
	"io"
	"math"
	"sort"
	"strconv"
	"strings"
	"time"
	"unicode/utf16"
	"unicode/utf8"

	"golang.org/x/text/encoding/charmap"
	"golang.org/x/text/transform"

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

// ============================================================================
// Public API Types
// ============================================================================

// ImportOptions configures the importer behavior. All fields are optional.
type ImportOptions struct {
	// MaxInputBytes rejects input that exceeds this size. Zero leaves the source
	// unbounded, which preserves the historic library behavior. Set this for
	// network and user-supplied input. For gzip CSV, the limit applies after
	// decompression.
	MaxInputBytes int64

	// MaxRecordBytes caps an individual NDJSON record. Zero uses the scanner's
	// standard limit. CSV records remain governed by encoding/csv.
	MaxRecordBytes int

	// Encoding selects the source text encoding. Empty or "auto" accepts a
	// UTF BOM when present and otherwise requires UTF-8. Legacy single-byte
	// encodings are opt-in: iso-8859-1, iso-8859-2, iso-8859-15, and
	// windows-1252. tinySQL always stores resulting TEXT as UTF-8.
	Encoding string

	// BatchSize controls how many rows are buffered before executing INSERT (default 1000).
	BatchSize int

	// NullLiterals are treated as SQL NULL (case-insensitive, trimmed).
	// Defaults: "", "null", "na", "n/a", "none", "#n/a"
	NullLiterals []string

	// CreateTable creates the table if it doesn't exist using inferred column types (default true).
	// Disable this if you want to create the table manually with specific types/constraints.
	CreateTable bool

	// Truncate clears the table before import (default false).
	Truncate bool

	// HeaderMode controls header detection:
	//   "auto" (default)  → heuristic decides based on data analysis
	//   "present"         → first row is always treated as header
	//   "absent"          → first row is data, synthetic column names generated (col_1, col_2, ...)
	HeaderMode string

	// DelimiterCandidates tested during auto-detection. Default: , ; \t |
	// Override to force specific delimiter(s).
	DelimiterCandidates []rune

	// TableName overrides the target table name (useful when importing from stdin/pipes).
	TableName string

	// SampleBytes caps the amount of data used for detection (default 128KB).
	SampleBytes int

	// SampleRecords caps the number of records analyzed for detection (default 500).
	SampleRecords int

	// TypeInference controls automatic type detection (default true).
	// When enabled, analyzes sample data to determine INT, FLOAT, BOOL, TIME, or TEXT.
	// When disabled, all columns default to TEXT type.
	TypeInference bool

	// DateTimeFormats lists custom datetime formats to try during type detection.
	// Defaults include RFC3339, ISO8601, common US/EU formats.
	DateTimeFormats []string

	// StrictTypes when true causes import to fail if data doesn't match detected types (default false).
	// When false, falls back to TEXT on type conversion errors.
	StrictTypes bool

	// TopoJSONObject selects a single top-level "objects" key when importing
	// a TopoJSON Topology (ImportTopoJSON). Empty imports every object; if
	// more than one object key exists in that case, each resulting row gets
	// an added "topo_object" property naming its source object, so a
	// multi-object Topology's provenance isn't silently lost. Ignored by
	// every other importer.
	TopoJSONObject string

	// XLSXSheet selects a single worksheet by name when importing an Excel
	// workbook (ImportXLSX). Empty selects the workbook's first sheet in
	// declared order. Ignored by every other importer.
	XLSXSheet string
}

// ImportResult returns metadata about the import operation.
type ImportResult struct {
	RowsInserted int64             // Total rows successfully inserted
	RowsSkipped  int64             // Rows skipped due to errors (if StrictTypes=false)
	Delimiter    rune              // Detected or configured delimiter
	HadHeader    bool              // Whether a header row was detected/configured
	Encoding     string            // Detected encoding: "utf-8", "utf-8-bom", "utf-16le", "utf-16be"
	LineEnding   string            // Detected line ending: "\n" or "\r\n"
	ColumnNames  []string          // Final column names used
	ColumnTypes  []storage.ColType // Detected column types
	Errors       []string          // Non-fatal errors encountered during import
}

// ============================================================================
// CSV/TSV Import
// ============================================================================

// ImportCSV imports delimited data (CSV/TSV) from a reader into a tinySQL table.
//
// This function auto-detects the file format, creates the table if needed, and streams
// data insertion with batching for performance. It handles various encodings, compressed
// input (gzip), and performs smart type inference.
//
// Parameters:
//   - ctx: Context for cancellation
//   - db: Target database instance
//   - tenant: Tenant/schema name (use "default" for single-tenant mode)
//   - tableName: Target table name (can be overridden via opts.TableName)
//   - src: Input reader (file, network stream, stdin, etc.)
//   - opts: Optional configuration (nil uses sensible defaults)
//
// Returns ImportResult with metadata and any error encountered.
//
// ImportCSV is the entrypoint for CSV/TSV imports. It orchestrates detection,
// decoding, sampling, type inference and insertion while delegating detailed
// tasks to smaller helpers to keep cyclomatic complexity low.
func ImportCSV(
	ctx context.Context,
	db *storage.DB,
	tenant string,
	tableName string,
	src io.Reader,
	opts *ImportOptions,
) (*ImportResult, error) {
	if opts == nil {
		opts = &ImportOptions{}
	}
	applyDefaults(opts)

	if opts.TableName != "" {
		tableName = opts.TableName
	}
	if tableName == "" {
		return nil, fmt.Errorf("table name is required")
	}

	result := &ImportResult{Errors: make([]string, 0)}

	// Prepare reader and detect encoding
	rr, enc, _, err := prepareReader(ctx, src, opts)
	if err != nil {
		return nil, err
	}
	result.Encoding = enc

	// Detect delimiter, header and initialize CSV reader
	sr, csvr, colNames, firstDataRow, delim, hasHeader, lineEnding, err := initCSVFromReader(rr, opts)
	if err != nil {
		return nil, err
	}
	result.Delimiter = delim
	result.HadHeader = hasHeader
	result.ColumnNames = colNames
	result.LineEnding = lineEnding

	// Keep a bounded sample for type inference, then stream the remainder into INSERT batches.
	sampleData := make([][]string, 0, opts.SampleRecords)
	if firstDataRow != nil {
		sampleData = append(sampleData, firstDataRow)
	}
	if opts.TypeInference {
		for len(sampleData) < opts.SampleRecords {
			select {
			case <-ctx.Done():
				return nil, ctx.Err()
			default:
			}
			rec, err := csvr.Read()
			if err == io.EOF {
				break
			}
			if err != nil {
				result.Errors = append(result.Errors, fmt.Sprintf("read error: %v", err))
				continue
			}
			sampleData = append(sampleData, rec)
		}
	}

	// Type inference
	colTypes := inferOrDefaultColumnTypes(sampleData, len(colNames), opts)
	result.ColumnTypes = colTypes

	// Create / truncate table as requested
	if opts.CreateTable {
		if err := createTable(ctx, db, tenant, tableName, colNames, colTypes); err != nil {
			return nil, fmt.Errorf("create table: %w", err)
		}
	}
	if opts.Truncate {
		if err := truncateTable(ctx, db, tenant, tableName); err != nil {
			return nil, fmt.Errorf("truncate table: %w", err)
		}
	}

	// Insert the sampled rows first, then continue streaming from the reader.
	rows, skipped, errs := insertCSVRecords(ctx, db, tenant, tableName, colNames, colTypes, sampleData, csvr, opts)
	result.RowsInserted = rows
	result.RowsSkipped = skipped
	result.Errors = append(result.Errors, errs...)

	// Ensure the bufio.Reader is consumed/closed if needed
	_ = sr

	return result, nil
}

// prepareReader handles gzip detection and encoding normalization, returning an
// io.Reader ready for CSV parsing plus encoding metadata.
func prepareReader(ctx context.Context, src io.Reader, opts *ImportOptions) (io.Reader, string, bool, error) {
	r := limitInput(ctx, maybeGzip(contextReader{ctx: ctx, r: src}), opts)
	br := bufio.NewReader(r)
	sampleBytes, _ := br.Peek(maxInt(opts.SampleBytes, 16))
	enc, hasBOM := detectEncoding(sampleBytes)
	if requested := strings.ToLower(strings.TrimSpace(opts.Encoding)); requested != "" && requested != "auto" {
		var err error
		enc, err = normalizeTextEncoding(requested)
		if err != nil {
			return nil, "", false, err
		}
	}

	switch enc {
	case "utf-16le", "utf-16be":
		all, err := io.ReadAll(br)
		if err != nil {
			return nil, "", false, fmt.Errorf("read UTF-16 stream: %w", err)
		}
		utf8data, err := decodeUTF16All(all, enc == "utf-16be")
		if err != nil {
			return nil, "", false, fmt.Errorf("decode UTF-16: %w", err)
		}
		return bytes.NewReader(utf8data), enc, hasBOM, nil
	case "iso-8859-1", "iso-8859-2", "iso-8859-15", "windows-1252":
		decoder, err := textDecoder(enc)
		if err != nil {
			return nil, "", false, err
		}
		return newStrictUTF8Reader(transform.NewReader(br, decoder)), enc, false, nil
	default:
		if hasBOM {
			if _, err := br.Discard(3); err != nil {
				return nil, "", false, fmt.Errorf("discard UTF-8 BOM: %w", err)
			}
		}
		return newStrictUTF8Reader(br), enc, hasBOM, nil
	}
}

func normalizeTextEncoding(enc string) (string, error) {
	switch enc {
	case "utf-8", "utf8":
		return "utf-8", nil
	case "utf-16le", "utf16le":
		return "utf-16le", nil
	case "utf-16be", "utf16be":
		return "utf-16be", nil
	case "iso-8859-1", "iso8859-1", "latin1":
		return "iso-8859-1", nil
	case "iso-8859-2", "iso8859-2":
		return "iso-8859-2", nil
	case "iso-8859-15", "iso8859-15", "latin9":
		return "iso-8859-15", nil
	case "windows-1252", "cp1252":
		return "windows-1252", nil
	default:
		return "", fmt.Errorf("unsupported text encoding %q", enc)
	}
}

func textDecoder(enc string) (transform.Transformer, error) {
	switch enc {
	case "iso-8859-1":
		return charmap.ISO8859_1.NewDecoder(), nil
	case "iso-8859-2":
		return charmap.ISO8859_2.NewDecoder(), nil
	case "iso-8859-15":
		return charmap.ISO8859_15.NewDecoder(), nil
	case "windows-1252":
		return charmap.Windows1252.NewDecoder(), nil
	default:
		return nil, fmt.Errorf("unsupported text encoding %q", enc)
	}
}

// strictUTF8Reader validates the entire decoded stream without buffering it
// in memory. A malformed sequence is an import error rather than a silent
// U+FFFD replacement, preserving the TEXT/BLOB boundary.
type strictUTF8Reader struct {
	r       io.Reader
	out     []byte
	tail    []byte
	readErr error
}

func newStrictUTF8Reader(r io.Reader) *strictUTF8Reader { return &strictUTF8Reader{r: r} }

func (r *strictUTF8Reader) Read(p []byte) (int, error) {
	for len(r.out) == 0 && r.readErr == nil {
		buf := make([]byte, 32*1024)
		n, err := r.r.Read(buf)
		if n > 0 {
			r.process(append(r.tail, buf[:n]...))
		}
		if err != nil {
			if err == io.EOF && len(r.tail) > 0 {
				r.readErr = fmt.Errorf("invalid UTF-8 at end of input")
			} else {
				r.readErr = err
			}
		}
	}
	if len(r.out) > 0 {
		n := copy(p, r.out)
		r.out = r.out[n:]
		return n, nil
	}
	if r.readErr == io.EOF {
		return 0, io.EOF
	}
	return 0, r.readErr
}

func (r *strictUTF8Reader) process(data []byte) {
	r.tail = r.tail[:0]
	i := 0
	for i < len(data) {
		if !utf8.FullRune(data[i:]) {
			r.tail = append(r.tail, data[i:]...)
			break
		}
		_, size := utf8.DecodeRune(data[i:])
		if size == 1 && data[i] >= 0x80 {
			r.readErr = fmt.Errorf("invalid UTF-8 sequence")
			return
		}
		i += size
	}
	r.out = append(r.out, data[:i]...)
}

// initCSVFromReader samples the reader to detect delimiter/header and returns a
// prepared bufio.Reader, csv.Reader, column names and first data row (if any).
func initCSVFromReader(rr io.Reader, opts *ImportOptions) (*bufio.Reader, *csv.Reader, []string, []string, rune, bool, string, error) {
	sr := bufio.NewReader(rr)
	peek := peekN(sr, opts.SampleBytes)
	lines := splitUniversal(string(peek))

	delim := detectDelimiter(lines, candidateDelims(opts.DelimiterCandidates))
	records := parseRecords(lines, delim, opts.SampleRecords)
	hasHeader := decideHeader(records, opts.HeaderMode)
	lineEnding := detectLineEnding(string(peek))

	csvr := csv.NewReader(sr)
	csvr.Comma = delim
	csvr.FieldsPerRecord = -1
	csvr.LazyQuotes = true
	csvr.TrimLeadingSpace = true

	firstRec, err := csvr.Read()
	if err != nil {
		if errors.Is(err, io.EOF) {
			return nil, nil, nil, nil, 0, false, "", fmt.Errorf("empty input")
		}
		return nil, nil, nil, nil, 0, false, "", fmt.Errorf("read first record: %w", err)
	}

	var colNames []string
	var firstDataRow []string
	if hasHeader {
		colNames = sanitizeColumnNames(firstRec)
	} else {
		colNames = generateColumnNames(len(firstRec))
		firstDataRow = firstRec
	}

	return sr, csvr, colNames, firstDataRow, delim, hasHeader, lineEnding, nil
}

// detectLineEnding inspects a sample of the file and returns "\r\n" if
// CRLF is prevalent, otherwise returns "\n".
func detectLineEnding(s string) string {
	if strings.Contains(s, "\r\n") {
		return "\r\n"
	}
	return "\n"
}

// ============================================================================
// Helper Functions - Encoding & Detection
// ============================================================================

func applyDefaults(o *ImportOptions) {
	if o.BatchSize <= 0 {
		o.BatchSize = 1000
	}
	if len(o.NullLiterals) == 0 {
		o.NullLiterals = []string{"", "null", "na", "n/a", "none", "#n/a"}
	}
	if o.HeaderMode == "" {
		o.HeaderMode = "auto"
	}
	if len(o.DelimiterCandidates) == 0 {
		o.DelimiterCandidates = []rune{',', ';', '\t', '|'}
	}
	if o.SampleBytes <= 0 {
		o.SampleBytes = 128 * 1024
	}
	if o.SampleRecords <= 0 {
		o.SampleRecords = 500
	}
	if len(o.DateTimeFormats) == 0 {
		o.DateTimeFormats = []string{
			time.RFC3339,
			time.RFC3339Nano,
			time.RFC1123,
			time.RFC1123Z,
			time.RFC822,
			time.RFC822Z,
			time.ANSIC,
			time.UnixDate,
			time.RubyDate,
			"2006-01-02",
			"2006-01-02 15:04:05",
			"2006-01-02T15:04:05",
			"01/02/2006",
			"01/02/2006 15:04:05",
			"02.01.2006",
			"02.01.2006 15:04:05",
			"02-Jan-2006",
			"2 Jan 2006",
			"2006/01/02",
		}
	}
	if !o.CreateTable && !o.Truncate {
		// Enable CreateTable by default if neither option is explicitly set
		o.CreateTable = true
	}
	if !o.TypeInference && o.CreateTable {
		// Default TypeInference to true if not explicitly disabled
		o.TypeInference = true
	}
}

func maybeGzip(r io.Reader) io.Reader {
	br := bufio.NewReader(r)
	magic, _ := br.Peek(2)
	if len(magic) >= 2 && magic[0] == 0x1F && magic[1] == 0x8B {
		gr, err := gzip.NewReader(br)
		if err == nil {
			return gr
		}
	}
	return br
}

func detectEncoding(b []byte) (enc string, hasUTF8BOM bool) {
	if len(b) >= 3 && b[0] == 0xEF && b[1] == 0xBB && b[2] == 0xBF {
		return "utf-8-bom", true
	}
	if len(b) >= 2 && b[0] == 0xFF && b[1] == 0xFE {
		return "utf-16le", false
	}
	if len(b) >= 2 && b[0] == 0xFE && b[1] == 0xFF {
		return "utf-16be", false
	}
	return "utf-8", false
}

func decodeUTF16All(b []byte, bigEndian bool) ([]byte, error) {
	// Strip BOM if present
	if bigEndian && len(b) >= 2 && b[0] == 0xFE && b[1] == 0xFF {
		b = b[2:]
	} else if !bigEndian && len(b) >= 2 && b[0] == 0xFF && b[1] == 0xFE {
		b = b[2:]
	}

	// An incomplete code unit is malformed input. Padding would silently
	// fabricate a character and violates the strict text import contract.
	if len(b)%2 != 0 {
		return nil, fmt.Errorf("odd UTF-16 byte length")
	}

	u16s := make([]uint16, len(b)/2)
	for i := 0; i < len(u16s); i++ {
		if bigEndian {
			u16s[i] = uint16(b[i*2])<<8 | uint16(b[i*2+1])
		} else {
			u16s[i] = uint16(b[i*2+1])<<8 | uint16(b[i*2])
		}
	}

	runes := utf16.Decode(u16s)
	return []byte(string(runes)), nil
}

func candidateDelims(c []rune) []rune {
	out := make([]rune, 0, len(c))
	for _, r := range c {
		if r != 0 {
			out = append(out, r)
		}
	}
	if len(out) == 0 {
		return []rune{',', ';', '\t', '|'}
	}
	return out
}

func peekN(br *bufio.Reader, n int) []byte {
	if n <= 0 {
		n = 1
	}
	b, _ := br.Peek(n)
	return b
}

func splitUniversal(s string) []string {
	var out []string
	start := 0
	for i := 0; i < len(s); {
		switch s[i] {
		case '\r':
			out = append(out, s[start:i])
			i++
			if i < len(s) && s[i] == '\n' {
				i++
			}
			start = i
		case '\n':
			out = append(out, s[start:i])
			i++
			start = i
		default:
			i++
		}
	}
	if start <= len(s) {
		out = append(out, s[start:])
	}
	// Drop trailing empty line
	if len(out) > 0 && out[len(out)-1] == "" {
		out = out[:len(out)-1]
	}
	return out
}

func parseRecords(lines []string, delim rune, maxRecs int) [][]string {
	var out [][]string
	for _, ln := range lines {
		if strings.TrimSpace(ln) == "" {
			continue
		}
		rec := naiveSplitOutsideQuotes(ln, delim)
		out = append(out, rec)
		if maxRecs > 0 && len(out) >= maxRecs {
			break
		}
	}
	return out
}

func detectDelimiter(lines []string, cands []rune) rune {
	type score struct {
		cand   rune
		stdev  float64
		fields int
	}
	var best *score

	for _, cand := range cands {
		var counts []int
		seen := 0
		for _, ln := range lines {
			if strings.TrimSpace(ln) == "" {
				continue
			}
			if seen >= 200 {
				break
			}
			cnt := countDelimsOutsideQuotes(ln, cand)
			counts = append(counts, cnt+1)
			seen++
		}
		if len(counts) == 0 {
			continue
		}
		_, sd := meanStd(counts)
		fields := mode(counts)
		if fields <= 1 {
			continue
		}
		sc := score{cand: cand, stdev: sd, fields: fields}
		if best == nil || sc.stdev < best.stdev ||
			(math.Abs(sc.stdev-best.stdev) < 1e-9 && sc.fields > best.fields) {
			cp := sc
			best = &cp
		}
	}
	if best != nil {
		return best.cand
	}
	return ','
}

func countDelimsOutsideQuotes(ln string, delim rune) int {
	inQ := false
	count := 0
	for i, w := 0, 0; i < len(ln); i += w {
		r, size := utf8.DecodeRuneInString(ln[i:])
		w = size
		if r == '"' {
			peek, _ := utf8.DecodeRuneInString(ln[i+w:])
			if inQ && peek == '"' {
				i += w
				continue
			}
			inQ = !inQ
			continue
		}
		if !inQ && r == delim {
			count++
		}
	}
	return count
}

func naiveSplitOutsideQuotes(ln string, delim rune) []string {
	var out []string
	var sb strings.Builder
	inQ := false

	for i := 0; i < len(ln); {
		r, w := utf8.DecodeRuneInString(ln[i:])
		i += w
		if r == '\r' || r == '\n' {
			break
		}
		if r == '"' {
			if inQ {
				if i < len(ln) {
					r2, w2 := utf8.DecodeRuneInString(ln[i:])
					if r2 == '"' {
						i += w2
						sb.WriteRune('"')
						continue
					}
				}
				inQ = false
				continue
			} else if sb.Len() == 0 {
				inQ = true
				continue
			}
		}
		if !inQ && r == delim {
			out = append(out, sb.String())
			sb.Reset()
			continue
		}
		sb.WriteRune(r)
	}
	out = append(out, sb.String())
	return out
}

// explicitOrTooShortHeader resolves the header-mode question either from an
// explicit mode string or from having too few records to decide at all,
// returning the answer and whether it was decided here (decided == false
// means the caller must run its own heuristic).
func explicitOrTooShortHeader(records [][]string, mode string) (value bool, decided bool) {
	switch strings.ToLower(strings.TrimSpace(mode)) {
	case "present":
		return true, true
	case "absent":
		return false, true
	}
	if len(records) < 2 {
		return false, true
	}
	return false, false
}

// columnNumericRatio scans column col of body and reports how many of its
// cells parse as numeric, out of how many rows had that column at all.
func columnNumericRatio(body [][]string, col int) (numericCount, rows int) {
	for _, r := range body {
		if col >= len(r) {
			continue
		}
		if looksNumeric(r[col]) {
			numericCount++
		}
		rows++
	}
	return numericCount, rows
}

func decideHeader(records [][]string, mode string) bool {
	if v, decided := explicitOrTooShortHeader(records, mode); decided {
		return v
	}

	first := records[0]
	body := records[1:]
	cols := len(first)
	headerish := 0
	numericHeaderCells := 0

	for c := 0; c < cols; c++ {
		headNum := looksNumeric(first[c])
		dataNum, rows := columnNumericRatio(body, c)
		if rows > 0 && !headNum && float64(dataNum)/float64(rows) > 0.6 {
			headerish++
		}
		if headNum {
			numericHeaderCells++
		}
	}
	if float64(headerish)/float64(cols) >= 0.5 {
		return true
	}

	// The ratio test above needs half the columns to be numeric in the body,
	// which misses the most ordinary shape of all: an attribute table with one
	// numeric id and several text columns. For "id,name,note" over "1,alice,x"
	// only one of three columns qualifies, so the file was treated as
	// header-less — column names became col_1..col_3 and the header row was
	// inserted as data, silently corrupting the import.
	//
	// A single type disagreement is strong evidence on its own, provided no
	// header cell is itself numeric: a real data row would not have text where
	// every other row has a number. Requiring numericHeaderCells == 0 is what
	// keeps a genuinely header-less numeric file (whose first row looks like its
	// others) from being misread.
	return numericHeaderCells == 0 && headerish > 0
}

func looksNumeric(s string) bool {
	s = strings.TrimSpace(strings.ReplaceAll(s, ",", ""))
	if s == "" {
		return false
	}
	if _, err := strconv.ParseInt(s, 10, 64); err == nil {
		return true
	}
	if _, err := strconv.ParseFloat(s, 64); err == nil {
		return true
	}
	return false
}

func meanStd(vals []int) (float64, float64) {
	if len(vals) == 0 {
		return 0, 0
	}
	var sum float64
	for _, v := range vals {
		sum += float64(v)
	}
	avg := sum / float64(len(vals))
	var ss float64
	for _, v := range vals {
		d := float64(v) - avg
		ss += d * d
	}
	return avg, math.Sqrt(ss / float64(len(vals)))
}

func mode(vals []int) int {
	if len(vals) == 0 {
		return 0
	}
	m := map[int]int{}
	for _, v := range vals {
		m[v]++
	}
	type kv struct{ v, c int }
	var arr []kv
	for v, c := range m {
		arr = append(arr, kv{v, c})
	}
	sort.Slice(arr, func(i, j int) bool {
		if arr[i].c == arr[j].c {
			return arr[i].v > arr[j].v
		}
		return arr[i].c > arr[j].c
	})
	return arr[0].v
}

func sanitizeColumnNames(h []string) []string {
	out := make([]string, len(h))
	for i, s := range h {
		s = strings.TrimSpace(s)
		if s == "" {
			s = fmt.Sprintf("col_%d", i+1)
		}
		// Replace spaces and special chars with underscores
		s = strings.Map(func(r rune) rune {
			if r == ' ' || r == '-' || r == '.' || r == '/' {
				return '_'
			}
			if (r >= 'a' && r <= 'z') || (r >= 'A' && r <= 'Z') ||
				(r >= '0' && r <= '9') || r == '_' {
				return r
			}
			return '_'
		}, s)
		out[i] = s
	}
	return out
}

func generateColumnNames(n int) []string {
	out := make([]string, n)
	for i := 0; i < n; i++ {
		out[i] = fmt.Sprintf("col_%d", i+1)
	}
	return out
}

func maxInt(a, b int) int {
	if a > b {
		return a
	}
	return b
}
