// Package connectorfixture provisions real TiDB tests from the canonical core
// schema. It contains no copied connector table definitions.
package connectorfixture

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"os"
	"path/filepath"
	"strings"
	"sync"
	"testing"
)

type column struct {
	Name, Type, Default, PhysicalName, CollationName string
	PrimaryKey, Nullable, Unique, AutoIncrement      bool
}
type index struct {
	Name    string
	Columns []string
	Unique  bool
}
type table struct {
	TableName   string
	Columns     []column
	Indices     []index
	ForeignKeys []json.RawMessage
}

var catalog struct {
	sync.Once
	tables map[string]table
	err    error
}

func quote(s string) string { return "`" + strings.ReplaceAll(s, "`", "``") + "`" }

// CreateSystemTables requires the same core checkout as the frozen candidate.
// Missing canonical schema is a failure, never a weaker fallback fixture.
func CreateSystemTables(t testing.TB, db *sql.DB, names ...string) {
	t.Helper()
	if err := CreateSystemTablesContext(context.Background(), db, names...); err != nil {
		t.Fatal(err)
	}
}

// CreateSystemTablesContext is shared with the local pressure runner. It reads
// exactly the same frozen Core catalog as the integration tests.
func CreateSystemTablesContext(ctx context.Context, db interface {
	ExecContext(context.Context, string, ...any) (sql.Result, error)
}, names ...string) error {
	catalog.Do(func() {
		root := os.Getenv("NEXUSCRM_REPO_ROOT")
		if root == "" {
			catalog.err = fmt.Errorf("NEXUSCRM_REPO_ROOT must identify the candidate core checkout for canonical connector schema")
			return
		}
		data, err := os.ReadFile(filepath.Join(root, "nexuscrm-kernel/pkg/schemacatalog/system_tables.json"))
		if err != nil {
			catalog.err = err
			return
		}
		var defs []table
		if err = json.Unmarshal(data, &defs); err != nil {
			catalog.err = err
			return
		}
		catalog.tables = map[string]table{}
		for _, def := range defs {
			catalog.tables[def.TableName] = def
		}
	})
	if catalog.err != nil {
		return catalog.err
	}
	for _, name := range names {
		def, ok := catalog.tables[name]
		if !ok {
			return fmt.Errorf("canonical schema has no %s", name)
		}
		if len(def.ForeignKeys) != 0 {
			return fmt.Errorf("fixture renderer must implement canonical foreign keys before provisioning %s", name)
		}
		parts := []string{}
		for _, c := range def.Columns {
			physical := c.Name
			if c.PhysicalName != "" {
				physical = c.PhysicalName
			}
			part := quote(physical) + " " + c.Type
			if c.CollationName != "" {
				part += " COLLATE " + c.CollationName
			}
			if !c.Nullable || c.PrimaryKey {
				part += " NOT NULL"
			}
			if c.Default != "" {
				part += " DEFAULT " + c.Default
			}
			if c.AutoIncrement {
				part += " AUTO_INCREMENT"
			}
			if c.PrimaryKey {
				part += " PRIMARY KEY"
			} else if c.Unique {
				part += " UNIQUE"
			}
			parts = append(parts, part)
		}
		for _, idx := range def.Indices {
			cols := make([]string, len(idx.Columns))
			for i, c := range idx.Columns {
				cols[i] = quote(c)
			}
			kind := "KEY "
			if idx.Unique {
				kind = "UNIQUE KEY "
			}
			if idx.Name != "" {
				kind += quote(idx.Name)
			}
			parts = append(parts, kind+" ("+strings.Join(cols, ",")+")")
		}
		if _, err := db.ExecContext(ctx, "CREATE TABLE IF NOT EXISTS "+quote(name)+" ("+strings.Join(parts, ",")+")"); err != nil {
			return fmt.Errorf("provision canonical %s: %w", name, err)
		}
	}
	return nil
}