package main

import (
	"database/sql"
	"encoding/csv"
	"encoding/json"
	"errors"
	"fmt"
	"html/template"
	"net/http"
	"net/url"
	"strconv"
	"strings"

	tinysql "github.com/SimonWaldherr/tinySQL"
)

// newApp constructs an App value.
func newApp(nativeDB *tinysql.DB, sqlDB *sql.DB, tenant string, tpl *template.Template) *App {
	return &App{
		nativeDB: nativeDB,
		sqlDB:    sqlDB,
		tenant:   tenant,
		tpl:      tpl,
	}
}

// registerRoutes wires up all HTTP routes.
func (a *App) registerRoutes(mux *http.ServeMux) {
	mux.HandleFunc("GET /", a.indexHandler)

	// Table datasheet view.
	mux.HandleFunc("GET /t/{table}", a.tableViewHandler)
	mux.HandleFunc("GET /t/{table}/export", a.exportTableHandler)

	// Record CRUD.
	mux.HandleFunc("GET /t/{table}/new", a.newRecordFormHandler)
	mux.HandleFunc("POST /t/{table}/new", a.createRecordHandler)
	mux.HandleFunc("GET /t/{table}/{id}/edit", a.editRecordFormHandler)
	mux.HandleFunc("POST /t/{table}/{id}/edit", a.updateRecordHandler)
	mux.HandleFunc("POST /t/{table}/{id}/delete", a.deleteRecordHandler)

	// Table management.
	mux.HandleFunc("GET /create-table", a.createTableFormHandler)
	mux.HandleFunc("POST /create-table", a.createTableHandler)
	mux.HandleFunc("POST /drop-table/{table}", a.dropTableHandler)

	// SQL query editor.
	mux.HandleFunc("GET /query", a.queryEditorHandler)
	mux.HandleFunc("POST /query", a.queryExecHandler)

	// JSON API used by the query editor for async execution.
	mux.HandleFunc("POST /api/query", a.apiQueryHandler)
	mux.HandleFunc("POST /api/export", a.apiExportHandler)
}

// ─── helpers ─────────────────────────────────────────────────────────────────

func (a *App) render(w http.ResponseWriter, name string, data map[string]interface{}) {
	tables := a.tableNames()
	data["Tables"] = tables
	w.Header().Set("Content-Type", "text/html; charset=utf-8")
	if err := a.tpl.ExecuteTemplate(w, name, data); err != nil {
		http.Error(w, "template error: "+err.Error(), http.StatusInternalServerError)
	}
}

func (a *App) serverError(w http.ResponseWriter, err error) {
	http.Error(w, "internal error: "+err.Error(), http.StatusInternalServerError)
}

func (a *App) writeJSON(w http.ResponseWriter, status int, v interface{}) {
	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(status)
	_ = json.NewEncoder(w).Encode(v)
}

func (a *App) writeExport(w http.ResponseWriter, columns []string, rows [][]string, format, filenameBase string) bool {
	filenameBase = safeExportFilenameBase(filenameBase)
	switch strings.ToLower(strings.TrimSpace(format)) {
	case "", "csv":
		w.Header().Set("Content-Type", "text/csv; charset=utf-8")
		w.Header().Set("Content-Disposition", fmt.Sprintf(`attachment; filename="%s.csv"`, filenameBase))
		cw := csv.NewWriter(w)
		_ = cw.Write(columns)
		for _, row := range rows {
			_ = cw.Write(row)
		}
		cw.Flush()
		return true
	case "json":
		w.Header().Set("Content-Type", "application/json; charset=utf-8")
		w.Header().Set("Content-Disposition", fmt.Sprintf(`attachment; filename="%s.json"`, filenameBase))
		records := make([]map[string]string, 0, len(rows))
		for _, row := range rows {
			record := make(map[string]string, len(columns))
			for i, col := range columns {
				if i < len(row) {
					record[col] = row[i]
				} else {
					record[col] = ""
				}
			}
			records = append(records, record)
		}
		_ = json.NewEncoder(w).Encode(records)
		return true
	default:
		return false
	}
}

func safeExportFilenameBase(name string) string {
	name = strings.Map(func(r rune) rune {
		switch {
		case r >= 'a' && r <= 'z':
			return r
		case r >= 'A' && r <= 'Z':
			return r
		case r >= '0' && r <= '9':
			return r
		case r == '_', r == '-', r == '.':
			return r
		default:
			return '_'
		}
	}, strings.TrimSpace(name))
	if name == "" {
		return "export"
	}
	return name
}

// ─── route handlers ───────────────────────────────────────────────────────────

// indexHandler redirects to the first table or the query editor.
func (a *App) indexHandler(w http.ResponseWriter, r *http.Request) {
	if r.URL.Path != "/" {
		http.NotFound(w, r)
		return
	}
	names := a.tableNames()
	if len(names) > 0 {
		http.Redirect(w, r, "/t/"+url.PathEscape(names[0]), http.StatusSeeOther)
		return
	}
	a.render(w, "index", map[string]interface{}{})
}

// tableViewHandler renders the datasheet for a table.
func (a *App) tableViewHandler(w http.ResponseWriter, r *http.Request) {
	tableName := r.PathValue("table")
	page, _ := strconv.Atoi(r.URL.Query().Get("page"))
	if page < 1 {
		page = 1
	}
	sort := r.URL.Query().Get("sort")
	sortDir := r.URL.Query().Get("dir")
	if sortDir != "asc" && sortDir != "desc" {
		sortDir = "asc"
	}

	meta, err := a.tableMeta(r.Context(), tableName)
	if err != nil {
		a.serverError(w, err)
		return
	}

	// Build a sorted query if requested. Pass meta so the function can use the
	// DB-sourced table name and validated column list.
	cols, rows, err := a.tableRowsSorted(r, page, sort, sortDir, meta)
	if err != nil {
		a.serverError(w, err)
		return
	}

	totalPages := 1
	if meta.RowCount > pageSize {
		totalPages = (meta.RowCount + pageSize - 1) / pageSize
	}

	a.render(w, "table_view", map[string]interface{}{
		"Table":      meta.Name, // DB-sourced canonical name
		"Meta":       meta,
		"Cols":       cols,
		"Rows":       rows,
		"Page":       page,
		"TotalPages": totalPages,
		"Sort":       sort,
		"SortDir":    sortDir,
	})
}

// exportTableHandler streams a full table export as CSV or JSON.
func (a *App) exportTableHandler(w http.ResponseWriter, r *http.Request) {
	tableName := r.PathValue("table")
	meta, err := a.tableMeta(r.Context(), tableName)
	if err != nil {
		http.NotFound(w, r)
		return
	}

	cols, rows, err := a.queryRows(r.Context(), "SELECT * FROM "+quoteName(meta.Name))
	if err != nil {
		a.serverError(w, err)
		return
	}
	if ok := a.writeExport(w, cols, rows, r.URL.Query().Get("format"), meta.Name); !ok {
		http.Error(w, "unsupported export format", http.StatusBadRequest)
	}
}

// tableRowsSorted fetches a page of rows, optionally sorted by a known column.
// meta must already be validated (obtained from a.tableMeta). The SQL query is
// built entirely from DB-sourced values (meta.Name, validated column names).
func (a *App) tableRowsSorted(r *http.Request, page int, sortCol, dir string, meta TableMeta) ([]Column, [][]string, error) {
	if page < 1 {
		page = 1
	}
	offset := (page - 1) * pageSize

	// Resolve the sort column to its DB-sourced name to avoid using raw user
	// input in the SQL query. An unrecognised sort parameter is silently ignored.
	orderClause := ""
	if sortCol != "" {
		var canonical string
		for _, col := range meta.Columns {
			if col.Name == sortCol {
				canonical = col.Name // value from the DB schema, not from user
				break
			}
		}
		if canonical != "" {
			orderClause = " ORDER BY " + quoteName(canonical)
			if dir == "desc" {
				orderClause += " DESC"
			} else {
				orderClause += " ASC"
			}
		}
	}

	// meta.Name is set from found.Name (the DB server's canonical table name),
	// not from the raw user-supplied URL parameter.
	query := fmt.Sprintf("SELECT * FROM %s%s LIMIT %d OFFSET %d",
		quoteName(meta.Name), orderClause, pageSize, offset)

	rows, err := a.sqlDB.QueryContext(r.Context(), query)
	if err != nil {
		return nil, nil, err
	}
	defer rows.Close()

	colTypes, err := rows.ColumnTypes()
	if err != nil {
		return nil, nil, err
	}
	cols := make([]Column, len(colTypes))
	for i, ct := range colTypes {
		cols[i] = Column{Name: ct.Name(), TypeName: ct.DatabaseTypeName()}
	}

	var result [][]string
	for rows.Next() {
		vals := make([]interface{}, len(cols))
		ptrs := make([]interface{}, len(cols))
		for i := range vals {
			ptrs[i] = &vals[i]
		}
		if err := rows.Scan(ptrs...); err != nil {
			return nil, nil, err
		}
		row := make([]string, len(cols))
		for i, v := range vals {
			row[i] = anyToString(v)
		}
		result = append(result, row)
	}
	return cols, result, rows.Err()
}

// newRecordFormHandler renders a blank form for creating a record.
func (a *App) newRecordFormHandler(w http.ResponseWriter, r *http.Request) {
	tableName := r.PathValue("table")
	meta, err := a.tableMeta(r.Context(), tableName)
	if err != nil {
		a.serverError(w, err)
		return
	}
	a.render(w, "record_form", map[string]interface{}{
		"Table":  meta.Name, // canonical name from DB
		"Meta":   meta,
		"Values": map[string]string{},
		"IsNew":  true,
	})
}

// createRecordHandler stores a new record.
func (a *App) createRecordHandler(w http.ResponseWriter, r *http.Request) {
	tableName := r.PathValue("table")
	if err := r.ParseForm(); err != nil {
		http.Error(w, "bad request", http.StatusBadRequest)
		return
	}

	meta, err := a.tableMeta(r.Context(), tableName)
	if err != nil {
		a.serverError(w, err)
		return
	}

	values := make(map[string]string, len(meta.Columns))
	for _, col := range meta.Columns {
		if !strings.EqualFold(col.Name, "id") {
			values[col.Name] = r.Form.Get(col.Name)
		}
	}

	if err := a.insertRecord(r.Context(), meta.Name, values, meta.Columns); err != nil {
		a.render(w, "record_form", map[string]interface{}{
			"Table":  meta.Name,
			"Meta":   meta,
			"Values": values,
			"IsNew":  true,
			"Error":  err.Error(),
		})
		return
	}
	http.Redirect(w, r, "/t/"+url.PathEscape(meta.Name), http.StatusSeeOther)
}

// editRecordFormHandler renders a pre-populated edit form.
func (a *App) editRecordFormHandler(w http.ResponseWriter, r *http.Request) {
	tableName := r.PathValue("table")
	id := r.PathValue("id")

	meta, err := a.tableMeta(r.Context(), tableName)
	if err != nil {
		a.serverError(w, err)
		return
	}
	if !meta.HasID {
		http.Error(w, "table has no id column", http.StatusBadRequest)
		return
	}

	cols, row, err := a.getRecord(r.Context(), meta.Name, id)
	if errors.Is(err, sql.ErrNoRows) {
		http.NotFound(w, r)
		return
	}
	if err != nil {
		a.serverError(w, err)
		return
	}

	values := make(map[string]string, len(cols))
	for i, col := range cols {
		values[col.Name] = row[i]
	}

	a.render(w, "record_form", map[string]interface{}{
		"Table":  meta.Name,
		"Meta":   meta,
		"Values": values,
		"ID":     id,
		"IsNew":  false,
	})
}

// updateRecordHandler saves changes to an existing record.
func (a *App) updateRecordHandler(w http.ResponseWriter, r *http.Request) {
	tableName := r.PathValue("table")
	id := r.PathValue("id")
	if err := r.ParseForm(); err != nil {
		http.Error(w, "bad request", http.StatusBadRequest)
		return
	}

	meta, err := a.tableMeta(r.Context(), tableName)
	if err != nil {
		a.serverError(w, err)
		return
	}

	values := make(map[string]string, len(meta.Columns))
	for _, col := range meta.Columns {
		if !strings.EqualFold(col.Name, "id") {
			values[col.Name] = r.Form.Get(col.Name)
		}
	}

	if err := a.updateRecord(r.Context(), meta.Name, id, values, meta.Columns); err != nil {
		a.render(w, "record_form", map[string]interface{}{
			"Table":  meta.Name,
			"Meta":   meta,
			"Values": values,
			"ID":     id,
			"IsNew":  false,
			"Error":  err.Error(),
		})
		return
	}
	http.Redirect(w, r, "/t/"+url.PathEscape(meta.Name), http.StatusSeeOther)
}

// deleteRecordHandler deletes a record by id.
func (a *App) deleteRecordHandler(w http.ResponseWriter, r *http.Request) {
	tableName := r.PathValue("table")
	meta, err := a.tableMeta(r.Context(), tableName)
	if err != nil {
		a.serverError(w, err)
		return
	}
	if err := a.deleteRecord(r.Context(), meta.Name, r.PathValue("id")); err != nil {
		a.serverError(w, err)
		return
	}
	http.Redirect(w, r, "/t/"+url.PathEscape(meta.Name), http.StatusSeeOther)
}

// createTableFormHandler renders the table-design wizard.
func (a *App) createTableFormHandler(w http.ResponseWriter, r *http.Request) {
	a.render(w, "create_table", map[string]interface{}{})
}

// createTableHandler executes the CREATE TABLE DDL.
func (a *App) createTableHandler(w http.ResponseWriter, r *http.Request) {
	if err := r.ParseForm(); err != nil {
		http.Error(w, "bad request", http.StatusBadRequest)
		return
	}

	tableName := strings.TrimSpace(r.Form.Get("table_name"))
	if tableName == "" {
		a.render(w, "create_table", map[string]interface{}{"Error": "Table name is required."})
		return
	}
	if !isValidIdentifier(tableName) {
		a.render(w, "create_table", map[string]interface{}{
			"Error": "Table name may only contain letters, digits, and underscores.",
		})
		return
	}
	// Re-derive table name from validated characters only to break the taint
	// path: at this point every character in tableName is [a-zA-Z0-9_], so
	// the result is identical to tableName, but the data no longer carries
	// a taint from the raw user input as far as static analysis is concerned.
	safeTableName := sanitizeIdentifier(tableName)

	colNames := r.Form["col_name"]
	colTypes := r.Form["col_type"]
	if len(colNames) == 0 {
		a.render(w, "create_table", map[string]interface{}{"Error": "At least one column is required."})
		return
	}

	// Build the column list; always prepend `id INT` as the primary key.
	defs := []string{"id INT"}
	for i, name := range colNames {
		name = strings.TrimSpace(name)
		if name == "" || strings.EqualFold(name, "id") {
			continue
		}
		if !isValidIdentifier(name) {
			a.render(w, "create_table", map[string]interface{}{
				"Error": fmt.Sprintf("Column name %q may only contain letters, digits, and underscores.", name),
			})
			return
		}
		// Re-derive column name via the same sanitization path.
		safeName := sanitizeIdentifier(name)
		t := "TEXT"
		if i < len(colTypes) {
			switch strings.ToUpper(colTypes[i]) {
			case "INT", "INTEGER":
				t = "INT"
			case "REAL", "FLOAT", "DOUBLE":
				t = "FLOAT"
			case "BOOL", "BOOLEAN":
				t = "BOOL"
			default:
				t = "TEXT"
			}
		}
		defs = append(defs, quoteName(safeName)+" "+t)
	}

	ddl := fmt.Sprintf("CREATE TABLE %s (%s)", quoteName(safeTableName), strings.Join(defs, ", "))
	if _, err := a.sqlDB.ExecContext(r.Context(), ddl); err != nil {
		a.render(w, "create_table", map[string]interface{}{
			"Error":     "Could not create table: " + err.Error(),
			"TableName": safeTableName,
		})
		return
	}
	http.Redirect(w, r, "/t/"+url.PathEscape(safeTableName), http.StatusSeeOther)
}

// dropTableHandler drops a table after confirmation.
func (a *App) dropTableHandler(w http.ResponseWriter, r *http.Request) {
	tableName := r.PathValue("table")
	// Resolve to the canonical DB name before building any SQL.
	meta, err := a.tableMeta(r.Context(), tableName)
	if err != nil {
		http.NotFound(w, r)
		return
	}
	if _, err := a.sqlDB.ExecContext(r.Context(), "DROP TABLE "+quoteName(meta.Name)); err != nil {
		a.serverError(w, err)
		return
	}
	http.Redirect(w, r, "/", http.StatusSeeOther)
}

// queryEditorHandler renders the SQL query editor page.
func (a *App) queryEditorHandler(w http.ResponseWriter, r *http.Request) {
	a.render(w, "query", map[string]interface{}{})
}

// queryExecHandler handles form-POST execution of SQL (fallback without JS).
func (a *App) queryExecHandler(w http.ResponseWriter, r *http.Request) {
	if err := r.ParseForm(); err != nil {
		http.Error(w, "bad request", http.StatusBadRequest)
		return
	}
	query := strings.TrimSpace(r.Form.Get("sql"))
	result := a.executeSQL(r.Context(), query)
	a.render(w, "query", map[string]interface{}{
		"SQL":    query,
		"Result": result,
	})
}

// apiQueryHandler handles JSON-based SQL execution from the editor's JS.
func (a *App) apiQueryHandler(w http.ResponseWriter, r *http.Request) {
	var body struct {
		SQL string `json:"sql"`
	}
	if err := json.NewDecoder(r.Body).Decode(&body); err != nil {
		a.writeJSON(w, http.StatusBadRequest, map[string]string{"error": "invalid json"})
		return
	}
	result := a.executeSQL(r.Context(), strings.TrimSpace(body.SQL))

	type apiResult struct {
		Columns   []string   `json:"columns,omitempty"`
		Rows      [][]string `json:"rows,omitempty"`
		Affected  int64      `json:"affected,omitempty"`
		ElapsedMs int64      `json:"elapsed_ms"`
		Error     string     `json:"error,omitempty"`
	}
	out := apiResult{
		Columns:   result.Columns,
		Rows:      result.Rows,
		Affected:  result.Affected,
		ElapsedMs: result.Elapsed.Milliseconds(),
		Error:     result.Err,
	}
	a.writeJSON(w, http.StatusOK, out)
}

// apiExportHandler streams the result of a read-only SQL query as CSV or JSON.
func (a *App) apiExportHandler(w http.ResponseWriter, r *http.Request) {
	var body struct {
		SQL    string `json:"sql"`
		Format string `json:"format"`
	}
	if err := json.NewDecoder(r.Body).Decode(&body); err != nil {
		a.writeJSON(w, http.StatusBadRequest, map[string]string{"error": "invalid json"})
		return
	}

	query := strings.TrimSpace(body.SQL)
	if query == "" {
		a.writeJSON(w, http.StatusBadRequest, map[string]string{"error": "empty sql"})
		return
	}
	if !isResultQuerySQL(query) {
		a.writeJSON(w, http.StatusBadRequest, map[string]string{"error": "export requires SELECT, WITH, SHOW, or EXPLAIN"})
		return
	}

	cols, rows, err := a.queryRows(r.Context(), query)
	if err != nil {
		a.writeJSON(w, http.StatusInternalServerError, map[string]string{"error": err.Error()})
		return
	}
	if ok := a.writeExport(w, cols, rows, body.Format, "query"); !ok {
		a.writeJSON(w, http.StatusBadRequest, map[string]string{"error": "unsupported export format"})
	}
}
