//go:build js && wasm

package main

import (
	"bytes"
	"context"
	"encoding/base64"
	"encoding/csv"
	"encoding/json"
	"encoding/xml"
	"errors"
	"fmt"
	"io"
	"sort"
	"strings"
	"syscall/js"
	"time"
	"unicode"

	tinysql "github.com/SimonWaldherr/tinySQL"
)

const (
	defaultTenant        = "default"
	defaultQueryTimeout  = 30 * time.Second
	defaultImportTimeout = 60 * time.Second
	maxSQLBytes          = 256 * 1024
	queryCacheSize       = 256
)

var (
	db         *tinysql.DB
	tenant     = defaultTenant
	queryCache *tinysql.QueryCache

	// lastResult caches the most recent query result for client-side export.
	lastResult     *tinysql.ResultSet
	lastQueryDurMs float64
)

func main() {
	c := make(chan struct{})

	db = tinysql.NewDB()
	queryCache = tinysql.NewQueryCache(queryCacheSize)
	registerDemoStoredProcedures()

	js.Global().Set("importFile", js.FuncOf(importFile))
	js.Global().Set("executeQuery", js.FuncOf(executeQuery))
	js.Global().Set("executeMulti", js.FuncOf(executeMulti))
	js.Global().Set("getResultPage", js.FuncOf(getResultPage))
	js.Global().Set("clearDatabase", js.FuncOf(clearDatabase))
	js.Global().Set("dropTable", js.FuncOf(dropTable))
	js.Global().Set("listTables", js.FuncOf(listTables))
	js.Global().Set("exportResults", js.FuncOf(exportResults))
	js.Global().Set("getTableSchema", js.FuncOf(getTableSchema))
	js.Global().Set("exportDatabase", js.FuncOf(exportDatabase))
	js.Global().Set("importDatabase", js.FuncOf(importDatabase))

	println("TinySQL Query Files WASM initialized!")
	<-c
}

func registerDemoStoredProcedures() {
	if err := tinysql.RegisterStoredProcedure("demo_table_summary", func(ctx tinysql.ProcedureContext, args []any) (*tinysql.ResultSet, error) {
		return ctx.ExecuteSQL("SELECT name, columns, rows FROM sys.tables ORDER BY name")
	}); err != nil {
		println("Failed to register demo_table_summary:", err.Error())
	}

	if err := tinysql.RegisterStoredProcedure("demo_release_features", func(ctx tinysql.ProcedureContext, args []any) (*tinysql.ResultSet, error) {
		return ctx.ExecuteSQL(`
			SELECT 'Geo imports' AS area, 'GeoJSON, KML, OSM XML, routing graph, Shapefile ZIP, MBTiles' AS feature, 'partly direct in browser; binary formats in Go/CLI/server' AS wasm_status
			UNION ALL SELECT 'Spatial SQL', 'ST_MakePoint, ST_X, ST_Y, ST_DISTANCE, ST_DWITHIN, ST_WITHIN_BBOX', 'direct'
			UNION ALL SELECT 'Search/RAG', 'HYBRID_SEARCH, wildcard FTS, VEC_SEARCH, RAG_SEARCH, RAG_CONTEXT', 'direct'
			UNION ALL SELECT 'Analytics SQL', 'CTEs, views, materialized views, PIVOT, window functions, RETURNING, EXPLAIN', 'direct'
			UNION ALL SELECT 'Introspection', 'sys.tables, sys.columns, sys.functions, sys.objects, sys.dependencies, PRAGMA compatibility', 'direct'
			UNION ALL SELECT 'Operations', 'RBAC, audit logs, WAL/storage, tinysqld, MCP server, tinyORM', 'core/server-side; showcased as metadata and SQL recipes'
			ORDER BY area
		`)
	}); err != nil {
		println("Failed to register demo_release_features:", err.Error())
	}
}

func jsErr(msg string) map[string]interface{} {
	return map[string]interface{}{"success": false, "error": msg}
}

func normalizeSQLInput(raw string) (string, error) {
	raw = strings.TrimSpace(raw)
	if raw == "" {
		return "", fmt.Errorf("SQL must not be empty")
	}
	if len(raw) > maxSQLBytes {
		return "", fmt.Errorf("SQL exceeds max size (%d bytes)", maxSQLBytes)
	}
	return raw, nil
}

func executeSQLText(sqlText string) (*tinysql.ResultSet, error) {
	ctx, cancel := context.WithTimeout(context.Background(), defaultQueryTimeout)
	defer cancel()

	compiled, err := queryCache.Compile(sqlText)
	if err != nil {
		return nil, fmt.Errorf("Parse error: %w", err)
	}

	result, err := tinysql.ExecuteCompiled(ctx, db, tenant, compiled)
	if err != nil {
		if errors.Is(ctx.Err(), context.DeadlineExceeded) {
			return nil, fmt.Errorf("Query timeout after %s", defaultQueryTimeout)
		}
		return nil, fmt.Errorf("Execute error: %w", err)
	}
	return result, nil
}

func resultRowsToJS(columns []string, rows []tinysql.Row) []interface{} {
	safeRows := make([]interface{}, 0, len(rows))
	for _, row := range rows {
		outRow := make(map[string]interface{}, len(columns))
		for _, col := range columns {
			val, ok := tinysql.GetVal(row, col)
			if !ok || val == nil {
				outRow[col] = nil
				continue
			}
			switch v := val.(type) {
			case string, int, int32, int64, float32, float64, bool:
				outRow[col] = v
			default:
				outRow[col] = fmt.Sprintf("%v", v)
			}
		}
		safeRows = append(safeRows, outRow)
	}
	return safeRows
}

func successResultPayload(result *tinysql.ResultSet, statementsRun int) map[string]interface{} {
	if result == nil {
		payload := map[string]interface{}{
			"success":    true,
			"columns":    []interface{}{},
			"rows":       []interface{}{},
			"durationMs": lastQueryDurMs,
		}
		if statementsRun > 0 {
			payload["statementsRun"] = statementsRun
		}
		return payload
	}

	page := buildResultPage(result, 0, defaultResultPageSize, "", "", "asc")
	payload := map[string]interface{}{
		"success":      true,
		"columns":      stringsToInterfaces(result.Cols),
		"rows":         resultRowsToJS(result.Cols, page.Rows),
		"totalRows":    page.TotalRows,
		"filteredRows": page.FilteredRows,
		"pageOffset":   page.Offset,
		"pageLimit":    page.Limit,
		"serverPaged":  true,
		"durationMs":   lastQueryDurMs,
	}
	if statementsRun > 0 {
		payload["statementsRun"] = statementsRun
	}
	return payload
}

// getResultPage filters, sorts, and pages the latest query result inside Go.
// This avoids materializing thousands of row objects across syscall/js when
// the browser can only render a small page at a time.
func getResultPage(this js.Value, args []js.Value) interface{} {
	if lastResult == nil {
		return jsErr("No query result available")
	}

	offset := 0
	limit := defaultResultPageSize
	filterText := ""
	sortColumn := ""
	sortDirection := "asc"
	if len(args) > 0 {
		offset = args[0].Int()
	}
	if len(args) > 1 {
		limit = args[1].Int()
	}
	if len(args) > 2 {
		filterText = args[2].String()
	}
	if len(args) > 3 {
		sortColumn = args[3].String()
	}
	if len(args) > 4 {
		sortDirection = args[4].String()
	}

	page := buildResultPage(lastResult, offset, limit, filterText, sortColumn, sortDirection)
	return map[string]interface{}{
		"success":      true,
		"columns":      stringsToInterfaces(lastResult.Cols),
		"rows":         resultRowsToJS(lastResult.Cols, page.Rows),
		"totalRows":    page.TotalRows,
		"filteredRows": page.FilteredRows,
		"pageOffset":   page.Offset,
		"pageLimit":    page.Limit,
	}
}

// importFile imports a file (CSV, JSON, XML) into the database.
func importFile(this js.Value, args []js.Value) interface{} {
	if len(args) < 3 {
		return jsErr("Usage: importFile(fileName, fileContent, tableName)")
	}

	fileName := strings.TrimSpace(args[0].String())
	fileContent := args[1].String()
	tableName := strings.TrimSpace(args[2].String())
	if tableName == "" {
		tableName = "table"
	}

	lowerName := strings.ToLower(fileName)
	ext := ""
	if idx := strings.LastIndex(lowerName, "."); idx != -1 {
		ext = lowerName[idx:]
	}

	ctx, cancel := context.WithTimeout(context.Background(), defaultImportTimeout)
	defer cancel()

	opts := &tinysql.FuzzyImportOptions{
		ImportOptions: &tinysql.ImportOptions{
			CreateTable:   true,
			Truncate:      false,
			HeaderMode:    "auto",
			TypeInference: true,
			TableName:     tableName,
		},
		SkipInvalidRows:    true,
		TrimWhitespace:     true,
		FixQuotes:          true,
		CoerceTypes:        true,
		AllowMixedTypes:    true,
		MaxSkippedRows:     100,
		FuzzyJSON:          true,
		RemoveInvalidChars: true,
		AutoFixDelimiters:  true,
	}

	reader := strings.NewReader(fileContent)
	var impResult *tinysql.ImportResult
	var err error

	switch {
	case ext == ".csv" || ext == ".tsv" || ext == ".txt":
		impResult, err = tinysql.FuzzyImportCSV(ctx, db, tenant, tableName, reader, opts)
	case ext == ".json" || ext == ".jsonl" || ext == ".ndjson":
		if strings.HasSuffix(lowerName, ".graph.json") ||
			strings.HasSuffix(lowerName, ".routinggraph.json") ||
			strings.HasSuffix(lowerName, ".routing-graph.json") ||
			strings.HasSuffix(lowerName, ".routing_graph.json") {
			impResult, err = tinysql.ImportRoutingGraph(ctx, db, tenant, tableName, reader, opts.ImportOptions)
		} else {
			impResult, err = tinysql.FuzzyImportJSON(ctx, db, tenant, tableName, reader, opts)
		}
	case ext == ".yaml" || ext == ".yml":
		impResult, err = tinysql.ImportYAML(ctx, db, tenant, tableName, reader, opts.ImportOptions)
	case ext == ".geojson":
		impResult, err = tinysql.ImportGeoJSON(ctx, db, tenant, tableName, reader, opts.ImportOptions)
	case ext == ".kml":
		impResult, err = tinysql.ImportKML(ctx, db, tenant, tableName, reader, opts.ImportOptions)
	case ext == ".osm" || strings.HasSuffix(lowerName, ".osm.xml"):
		impResult, err = tinysql.ImportOSM(ctx, db, tenant, tableName, reader, opts.ImportOptions)
	case ext == ".rg" || ext == ".routinggraph" || ext == ".routing-graph" || ext == ".routing_graph":
		impResult, err = tinysql.ImportRoutingGraph(ctx, db, tenant, tableName, reader, opts.ImportOptions)
	case ext == ".xml":
		xmlRows, xmlErr := parseSimpleXML(fileContent)
		if xmlErr != nil {
			return jsErr("XML parse error: " + xmlErr.Error())
		}
		jsonBytes, _ := json.Marshal(xmlRows)
		impResult, err = tinysql.FuzzyImportJSON(ctx, db, tenant, tableName, strings.NewReader(string(jsonBytes)), opts)
	default:
		return jsErr("Unsupported file format: " + ext + ". Supported: .csv, .tsv, .txt, .json, .jsonl, .ndjson, .yaml, .xml, .geojson, .kml, .osm, .rg")
	}

	if err != nil {
		if errors.Is(ctx.Err(), context.DeadlineExceeded) {
			return jsErr("Import timeout after " + defaultImportTimeout.String())
		}
		return jsErr("Import failed: " + err.Error())
	}
	if impResult == nil {
		return jsErr("Import failed: no result returned")
	}

	warnings := []string{}
	if len(impResult.Errors) > 0 {
		for i, errMsg := range impResult.Errors {
			if i >= 10 {
				warnings = append(warnings, "... and more")
				break
			}
			warnings = append(warnings, errMsg)
		}
	}

	columns := []string{}
	if impResult.ColumnNames != nil {
		columns = impResult.ColumnNames
	}
	delimiter := ","
	if impResult.Delimiter != 0 {
		delimiter = string(impResult.Delimiter)
	}

	// DDL/DML changed; clear cached last result only.
	lastResult = nil

	return map[string]interface{}{
		"success":      true,
		"tableName":    tableName,
		"rowsImported": int(impResult.RowsInserted),
		"rowsSkipped":  int(impResult.RowsSkipped),
		"columns":      stringsToInterfaces(columns),
		"warnings":     stringsToInterfaces(warnings),
		"delimiter":    delimiter,
		"hadHeader":    impResult.HadHeader,
	}
}

// executeQuery executes a single SQL query and returns results.
func executeQuery(this js.Value, args []js.Value) interface{} {
	if len(args) < 1 {
		return jsErr("Usage: executeQuery(sqlQuery)")
	}

	queryStr, err := normalizeSQLInput(args[0].String())
	if err != nil {
		return jsErr(err.Error())
	}

	start := time.Now()
	result, err := executeSQLText(queryStr)
	if err != nil {
		return jsErr(err.Error())
	}
	lastQueryDurMs = float64(time.Since(start).Microseconds()) / 1000.0
	lastResult = result

	return successResultPayload(result, 0)
}

// executeMulti runs multiple semicolon-separated SQL statements and returns
// the result of the last SELECT (or an aggregate summary).
func executeMulti(this js.Value, args []js.Value) interface{} {
	if len(args) < 1 {
		return jsErr("Usage: executeMulti(sql)")
	}

	raw := args[0].String()
	if len(raw) > maxSQLBytes {
		return jsErr(fmt.Sprintf("SQL exceeds max size (%d bytes)", maxSQLBytes))
	}

	stmts := splitStatements(raw)
	if len(stmts) == 0 {
		return jsErr("No SQL statements found")
	}

	start := time.Now()
	// Do not leave a previous query exportable when this batch fails part-way
	// through. Earlier statements may still have mutated the local database.
	lastResult = nil
	var lastRS *tinysql.ResultSet
	for i, stmtRaw := range stmts {
		stmtSQL, err := normalizeSQLInput(stmtRaw)
		if err != nil {
			return jsErr(fmt.Sprintf("Statement %d: %v", i+1, err))
		}
		rs, err := executeSQLText(stmtSQL)
		if err != nil {
			return jsErr(fmt.Sprintf("Statement %d: %v", i+1, err))
		}
		if rs != nil {
			lastRS = rs
		}
	}

	lastQueryDurMs = float64(time.Since(start).Microseconds()) / 1000.0
	lastResult = lastRS
	return successResultPayload(lastRS, len(stmts))
}

// clearDatabase clears all tables from the database.
func clearDatabase(this js.Value, args []js.Value) interface{} {
	db = tinysql.NewDB()
	queryCache = tinysql.NewQueryCache(queryCacheSize)
	lastResult = nil
	return map[string]interface{}{
		"success": true,
		"message": "Database cleared",
	}
}

// exportDatabase serializes the current database as a base64 GOB snapshot.
func exportDatabase(this js.Value, args []js.Value) interface{} {
	data, err := tinysql.SaveToBytes(db)
	if err != nil {
		return jsErr("export failed: " + err.Error())
	}
	return map[string]interface{}{
		"success":    true,
		"data":       base64.StdEncoding.EncodeToString(data),
		"sizeBytes":  len(data),
		"exportedAt": time.Now().UTC().Format(time.RFC3339),
	}
}

// importDatabase replaces the current database from a base64 GOB snapshot.
func importDatabase(this js.Value, args []js.Value) interface{} {
	if len(args) < 1 {
		return jsErr("Usage: importDatabase(snapshot)")
	}
	encoded := strings.TrimSpace(args[0].String())
	if encoded == "" {
		return jsErr("snapshot must not be empty")
	}
	data, err := base64.StdEncoding.DecodeString(encoded)
	if err != nil {
		return jsErr("invalid base64 snapshot: " + err.Error())
	}
	loaded, err := tinysql.LoadFromBytes(data)
	if err != nil {
		return jsErr("import failed: " + err.Error())
	}
	db = loaded
	queryCache = tinysql.NewQueryCache(queryCacheSize)
	lastResult = nil
	return map[string]interface{}{
		"success":   true,
		"message":   "Database imported",
		"sizeBytes": len(data),
	}
}

// dropTable removes a user table from the database.
func dropTable(this js.Value, args []js.Value) interface{} {
	if len(args) < 1 {
		return jsErr("Usage: dropTable(tableName)")
	}
	name := strings.TrimSpace(args[0].String())
	if name == "" {
		return jsErr("table name must not be empty")
	}
	lower := strings.ToLower(name)
	if strings.HasPrefix(lower, "sys.") || strings.HasPrefix(lower, "catalog.") {
		return jsErr("virtual tables cannot be dropped")
	}
	if err := db.Drop(tenant, name); err != nil {
		return jsErr("drop failed: " + err.Error())
	}
	lastResult = nil
	return map[string]interface{}{
		"success": true,
		"message": "Table dropped",
		"table":   name,
	}
}

// listTables returns the names and row counts of all loaded tables,
// plus virtual sys.* and catalog.* tables.
func listTables(this js.Value, args []js.Value) interface{} {
	tables := db.ListTables(tenant)
	sort.Slice(tables, func(i, j int) bool {
		return strings.ToLower(tables[i].Name) < strings.ToLower(tables[j].Name)
	})

	out := make([]interface{}, 0, len(tables)+20)
	for _, tbl := range tables {
		cols := make([]interface{}, len(tbl.Cols))
		for i, c := range tbl.Cols {
			cols[i] = map[string]interface{}{
				"name": c.Name,
				"type": c.Type.String(),
			}
		}
		out = append(out, map[string]interface{}{
			"name":    tbl.Name,
			"rows":    len(tbl.Rows),
			"columns": cols,
			"kind":    "table",
		})
	}

	sysNames := []string{"tables", "columns", "constraints", "indexes", "views", "functions", "variables", "status", "memory", "storage", "config", "connections", "objects", "dependencies", "statistics"}
	for _, n := range sysNames {
		out = append(out, map[string]interface{}{
			"name":    "sys." + n,
			"rows":    -1,
			"columns": []interface{}{},
			"kind":    "virtual",
		})
	}

	catNames := []string{"tables", "columns", "functions", "jobs", "views"}
	for _, n := range catNames {
		out = append(out, map[string]interface{}{
			"name":    "catalog." + n,
			"rows":    -1,
			"columns": []interface{}{},
			"kind":    "virtual",
		})
	}

	return map[string]interface{}{"success": true, "tables": out}
}

// getTableSchema returns column names, types and row count for a table.
func getTableSchema(this js.Value, args []js.Value) interface{} {
	if len(args) < 1 {
		return jsErr("Usage: getTableSchema(tableName)")
	}
	name := strings.TrimSpace(args[0].String())
	if name == "" {
		return jsErr("table name must not be empty")
	}

	lower := strings.ToLower(name)
	if strings.HasPrefix(lower, "sys.") || strings.HasPrefix(lower, "catalog.") {
		q := fmt.Sprintf("SELECT * FROM %s LIMIT 1", name)
		rs, err := executeSQLText(q)
		if err != nil {
			return jsErr(err.Error())
		}
		if rs == nil {
			rs = &tinysql.ResultSet{}
		}
		cols := make([]interface{}, len(rs.Cols))
		for i, c := range rs.Cols {
			cols[i] = map[string]interface{}{"name": c, "type": "DYNAMIC"}
		}
		return map[string]interface{}{
			"success": true,
			"name":    name,
			"rows":    len(rs.Rows),
			"virtual": true,
			"columns": cols,
		}
	}

	tbl, err := db.Get(tenant, name)
	if err != nil {
		return jsErr("Table not found: " + name)
	}
	cols := make([]interface{}, len(tbl.Cols))
	for i, c := range tbl.Cols {
		cols[i] = map[string]interface{}{"name": c.Name, "type": c.Type.String()}
	}
	return map[string]interface{}{
		"success": true,
		"name":    name,
		"rows":    len(tbl.Rows),
		"columns": cols,
	}
}

// exportResults exports the last query result in the requested format.
// format: csv, json, xml
func exportResults(this js.Value, args []js.Value) interface{} {
	if len(args) < 1 {
		return jsErr("Usage: exportResults(format)")
	}
	format := strings.ToLower(strings.TrimSpace(args[0].String()))
	if lastResult == nil || len(lastResult.Rows) == 0 {
		return jsErr("No results to export")
	}

	var buf bytes.Buffer
	var mimeType, ext string

	switch format {
	case "csv":
		mimeType, ext = "text/csv", "csv"
		w := csv.NewWriter(&buf)
		_ = w.Write(lastResult.Cols)
		for _, row := range lastResult.Rows {
			rec := make([]string, len(lastResult.Cols))
			for i, c := range lastResult.Cols {
				if v, ok := tinysql.GetVal(row, c); ok && v != nil {
					rec[i] = fmt.Sprintf("%v", v)
				}
			}
			_ = w.Write(rec)
		}
		w.Flush()
	case "json":
		mimeType, ext = "application/json", "json"
		out := make([]map[string]interface{}, len(lastResult.Rows))
		for i, row := range lastResult.Rows {
			m := make(map[string]interface{}, len(lastResult.Cols))
			for _, c := range lastResult.Cols {
				v, _ := tinysql.GetVal(row, c)
				m[c] = v
			}
			out[i] = m
		}
		enc := json.NewEncoder(&buf)
		enc.SetIndent("", "  ")
		_ = enc.Encode(out)
	case "xml":
		mimeType, ext = "application/xml", "xml"
		buf.WriteString("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<rows>\n")
		for _, row := range lastResult.Rows {
			buf.WriteString("  <row>\n")
			for _, c := range lastResult.Cols {
				tag := xmlTagName(c)
				v, _ := tinysql.GetVal(row, c)
				text := ""
				if v != nil {
					text = fmt.Sprintf("%v", v)
				}
				buf.WriteString("    <" + tag + ">")
				xml.EscapeText(&buf, []byte(text))
				buf.WriteString("</" + tag + ">\n")
			}
			buf.WriteString("  </row>\n")
		}
		buf.WriteString("</rows>\n")
	default:
		return jsErr("Unknown format: " + format + ". Use csv, json or xml.")
	}

	return map[string]interface{}{
		"success":  true,
		"data":     buf.String(),
		"mimeType": mimeType,
		"ext":      ext,
	}
}

// stringsToInterfaces converts a []string to []interface{} for JS interop.
func stringsToInterfaces(ss []string) []interface{} {
	out := make([]interface{}, len(ss))
	for i, s := range ss {
		out[i] = s
	}
	return out
}

// parseSimpleXML converts row-based XML into []map[string]string so it can be
// fed through the JSON importer.
func parseSimpleXML(data string) ([]map[string]string, error) {
	decoder := xml.NewDecoder(strings.NewReader(data))
	var rows []map[string]string
	var currentRow map[string]string
	var currentKey string
	depth := 0

	for {
		tok, err := decoder.Token()
		if err != nil {
			if errors.Is(err, io.EOF) {
				break
			}
			return nil, err
		}

		switch t := tok.(type) {
		case xml.StartElement:
			depth++
			if depth == 2 {
				currentRow = make(map[string]string)
				for _, a := range t.Attr {
					currentRow[a.Name.Local] = a.Value
				}
			} else if depth == 3 {
				currentKey = t.Name.Local
			}
		case xml.CharData:
			if depth == 3 && currentKey != "" && currentRow != nil {
				value := strings.TrimSpace(string(t))
				if value != "" {
					currentRow[currentKey] = value
				}
			}
		case xml.EndElement:
			if depth == 3 {
				currentKey = ""
			}
			if depth == 2 && currentRow != nil {
				if len(currentRow) > 0 {
					rows = append(rows, currentRow)
				}
				currentRow = nil
			}
			depth--
		}
	}

	if len(rows) == 0 {
		return nil, fmt.Errorf("no row elements found in XML")
	}
	return rows, nil
}

func xmlTagName(col string) string {
	col = strings.TrimSpace(col)
	if col == "" {
		return "col"
	}
	var b strings.Builder
	for i, r := range col {
		valid := unicode.IsLetter(r) || unicode.IsDigit(r) || r == '_' || r == '-' || r == '.'
		if i == 0 && !(unicode.IsLetter(r) || r == '_') {
			b.WriteString("c_")
		}
		if valid {
			b.WriteRune(r)
		} else {
			b.WriteRune('_')
		}
	}
	if b.Len() == 0 {
		return "col"
	}
	return b.String()
}
