package schema

import (
	"context"
	"database/sql"
	"fmt"
	"strings"

	"github.com/mem9-ai/drive9/pkg/logger"
	"github.com/mem9-ai/drive9/pkg/mysqlutil"
	"go.uber.org/zap"
)

// SharedTiDBSchemaStatements returns the complete shared (multi-tenant) schema
// DDL for TiDB in one list: the Core FS, journal, vault, git workspace, and FS
// layer shared shapes concatenated in creation order. The shared schema has 30
// tables; llm_usage is deliberately omitted (the central meta DB ledger is
// authoritative in multi-tenant deployments — see
// CoreFSTiDBSharedSchemaStatements). There are no foreign keys, so statement
// order is informational only. For plain MySQL use SharedMySQLSchemaStatements.
func SharedTiDBSchemaStatements() []string {
	stmts := CoreFSTiDBSharedSchemaStatements()
	stmts = append(stmts, JournalTiDBSharedSchemaStatements()...)
	stmts = append(stmts, VaultTiDBSharedSchemaStatements()...)
	stmts = append(stmts, GitWorkspaceTiDBSharedSchemaStatements()...)
	stmts = append(stmts, FSLayerTiDBSharedSchemaStatements()...)
	return stmts
}

// CurrentSharedTiDBSchemaVersion is derived from the checked-in shared schema
// SQL. A physical DB pool stores this value only after its idempotent ensure
// succeeds, so normal opens can skip repeated DDL work.
var CurrentSharedTiDBSchemaVersion = currentTiDBTenantSchemaVersion(SharedTiDBSchemaStatements())

type sharedSchemaDiffError struct {
	diffs []tidbSchemaDiff
}

func (e *sharedSchemaDiffError) Error() string {
	if e == nil || len(e.diffs) == 0 {
		return "shared schema contract mismatch"
	}
	return "shared schema contract mismatch: " + strings.Join(summarizeTiDBSchemaDiffs(e.diffs), "; ")
}

// SharedMySQLSchemaStatements is the plain-MySQL variant of
// SharedTiDBSchemaStatements, derived by removing TiDB-only keywords. Use it
// for local development databases and MySQL-backed tests/e2e.
func SharedMySQLSchemaStatements() []string {
	return mysqlCompatibleSharedStatements(SharedTiDBSchemaStatements())
}

// SharedSchemaStatementsForDB selects the shared schema DDL matching the
// connected database's dialect: TiDB clusters get the CLUSTERED variant,
// anything else (plain MySQL, e.g. local e2e) the compatible variant. A
// dialect-detection failure is an error, never a silent MySQL fallback —
// applying the non-CLUSTERED variant to a TiDB cluster would lose row
// co-location without any visible failure.
func SharedSchemaStatementsForDB(ctx context.Context, db *sql.DB) ([]string, error) {
	isTiDB, err := IsTiDBClusterE(ctx, db)
	if err != nil {
		return nil, err
	}
	if isTiDB {
		return SharedTiDBSchemaStatements(), nil
	}
	return SharedMySQLSchemaStatements(), nil
}

// InitSharedSchema initializes the shared (multi-tenant) schema on the
// database at dsn: it applies SharedSchemaStatementsForDB with the usual
// idempotent-DDL error tolerance (already-exists / duplicate key / duplicate
// column errors are skipped) and then, on TiDB clusters only, best-effort
// applies the optional FTS/vector indexes. Those optional indexes require
// TiDB Cloud features — plain TiDB without columnar support rejects them — so
// their failures are tolerated with a warning instead of failing init.
func InitSharedSchema(ctx context.Context, dsn string) error {
	db, err := OpenSharedSchemaDB(ctx, dsn, "", "")
	if err != nil {
		return err
	}
	defer func() { _ = mysqlutil.CloseInstrumented(db) }()
	return EnsureSharedSchema(ctx, db)
}

// OpenSharedSchemaDB opens the dedicated shared-schema DDL handle for dsn. It
// carries mysqlutil.RoleSharedSchema rather than the tenant RoleUserSchema, so
// shared bootstrap work is attributable to the physical pool it targets.
func OpenSharedSchemaDB(ctx context.Context, dsn, dbPoolUUID, tidbCloudOrgID string) (*sql.DB, error) {
	if HasMultiStatements(dsn) {
		return nil, fmt.Errorf("multiStatements is not allowed")
	}
	return mysqlutil.OpenInstrumentedForSharedSchemaDB(ctx, dsn, dbPoolUUID, tidbCloudOrgID)
}

// EnsureSharedSchema applies the shared schema through an already-open
// physical DB handle. This is used by the shared connection cache so schema
// comparison and initialization happen once at DB-pool open time.
// Callers pass a handle opened with mysqlutil.RoleSharedSchema so this DDL is
// observed apart from the role="shared" data-plane series.
func EnsureSharedSchema(ctx context.Context, db *sql.DB) error {
	stmts, err := SharedSchemaStatementsForDB(ctx, db)
	if err != nil {
		return err
	}
	if err := ExecSchemaStatementsParallelByTableContext(ctx, db, stmts); err != nil {
		return err
	}
	if err := ensureSharedSchemaContract(ctx, db, stmts); err != nil {
		return err
	}
	if !IsTiDBCluster(ctx, db) {
		return nil
	}
	skipped, err := ExecOptionalSchemaStatements(ctx, db, TiDBSharedOptionalSchemaStatements())
	if err != nil {
		logger.Warn(ctx, "shared_schema_optional_indexes_failed", zap.Error(err))
	} else if skipped > 0 {
		logger.Warn(ctx, "shared_schema_optional_indexes_skipped",
			zap.Int("skipped_count", skipped),
			zap.String("reason", "unsupported_tidb_features"))
	}
	return nil
}

func ensureSharedSchemaContract(ctx context.Context, db *sql.DB, stmts []string) error {
	spec, err := tidbSchemaSpecFromStatements(stmts)
	if err != nil {
		return fmt.Errorf("build shared schema contract: %w", err)
	}
	const maxRepairPasses = 6
	for range maxRepairPasses {
		diffs, _, err := diffTiDBTables(ctx, db, spec.tables)
		if err != nil {
			return err
		}
		if len(diffs) == 0 {
			return nil
		}
		for _, diff := range diffs {
			if diff.repairSQL == "" || !isSafeTiDBRepairDiff(diff) {
				return &sharedSchemaDiffError{diffs: diffs}
			}
		}
		repairs := plannedTiDBSchemaRepairs(diffs)
		if len(repairs) == 0 {
			return &sharedSchemaDiffError{diffs: diffs}
		}
		if err := applyTiDBSchemaRepairs(ctx, db, repairs); err != nil {
			return err
		}
	}
	diffs, _, err := diffTiDBTables(ctx, db, spec.tables)
	if err != nil {
		return err
	}
	if len(diffs) > 0 {
		return &sharedSchemaDiffError{diffs: diffs}
	}
	return nil
}