package persistence

import (
	"context"
	"database/sql"
	"fmt"
	"regexp"
	"strings"
	"sync"
	"time"

	"github.com/nexuscrm/backend/internal/domain/schema"
	apperrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/backend/pkg/query"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// CreatePhysicalTable creates the table structure without registering metadata
func (r *SchemaRepository) CreatePhysicalTable(ctx context.Context, def schema.TableDefinition) error {
	r.logger.Info(fmt.Sprintf("creating table: %s", def.TableName))

	// VALIDATION: Table Name
	// System tables (starting with _System_) are exempt from strict snake_case
	if !constants.IsSystemTable(def.TableName) {
		validName := regexp.MustCompile(constants.TableNamePattern)
		if !validName.MatchString(def.TableName) {
			return apperrors.NewValidationError("tableName", fmt.Sprintf("table name '%s' must be alphanumeric (PascalCase/snake_case)", def.TableName))
		}
	}

	// Build CREATE TABLE statement with indexes inline
	var ddl strings.Builder
	ddl.WriteString(fmt.Sprintf("CREATE TABLE IF NOT EXISTS `%s` (\n", def.TableName))

	// Add columns
	for i, col := range def.Columns {
		// VALIDATION: Fail fast if schema assumptions are violated
		if err := r.ValidateFieldDefinition(col); err != nil {
			return apperrors.NewValidationError(col.Name, fmt.Sprintf("invalid column definition for '%s': %v", col.Name, err))
		}

		ddl.WriteString("  ")

		// Physical names now perfectly match Salesforce logical names (e.g. Id)

		ddl.WriteString(r.buildColumnDDL(col, true))
		// Always add comma if there are more columns, indexes, or foreign keys
		if i < len(def.Columns)-1 || len(def.Indices) > 0 || len(def.ForeignKeys) > 0 {
			ddl.WriteString(",")
		}
		ddl.WriteString("\n")
	}

	// Add indexes inline (KEY or UNIQUE KEY)
	for i, idx := range def.Indices {
		ddl.WriteString("  ")
		ddl.WriteString(r.buildIndexDDL(def.TableName, idx))
		if i < len(def.Indices)-1 || len(def.ForeignKeys) > 0 {
			ddl.WriteString(",")
		}
		ddl.WriteString("\n")
	}

	// Add foreign keys
	for i, fk := range def.ForeignKeys {
		ddl.WriteString("  ")
		ddl.WriteString(r.buildForeignKeyDDL(fk))
		if i < len(def.ForeignKeys)-1 {
			ddl.WriteString(",")
		}
		ddl.WriteString("\n")
	}
	ddl.WriteString(fmt.Sprintf(") %s", constants.DefaultTableOptions))

	// Execute DDL using a dedicated connection to ensure SET FOREIGN_KEY_CHECKS works

	conn, err := r.db.Conn(ctx)
	if err != nil {
		return apperrors.NewInternalError("failed to get database connection", err)
	}
	defer func() { _ = conn.Close() }()

	// Disable foreign key checks for this DDL session
	if _, err := conn.ExecContext(ctx, "SET FOREIGN_KEY_CHECKS=0"); err != nil {
		r.logger.Warn(fmt.Sprintf("failed to disable FK checks: %v", err))
	}

	// Normalize FK columns (both source and referenced) to prevent Error 3780.
	// Uses per-column MODIFY instead of table-level CONVERT TO CHARACTER SET
	// because TiDB rejects CONVERT on indexed columns (Error 8200).
	if len(def.ForeignKeys) > 0 {
		r.normalizeTableForFK(ctx, conn, def.TableName, def.ForeignKeys)
	}

	r.logger.Debug(fmt.Sprintf("executing DDL for %s: \n%s", def.TableName, ddl.String()))
	if _, err := conn.ExecContext(ctx, ddl.String()); err != nil {
		r.logger.Error(fmt.Sprintf("failed to create table %s", def.TableName), err)
		return apperrors.NewInternalError(fmt.Sprintf("failed to create table %s", def.TableName), err)
	}
	r.logger.Info(fmt.Sprintf("DDL executed successfully for %s", def.TableName))

	return nil
}

// normalizeTableForFK ensures an existing table's string columns use the expected
// charset/collation (utf8mb4_unicode_ci) and width (VARCHAR(255) for FK columns).
//
// TiDB does NOT support `ALTER TABLE CONVERT TO CHARACTER SET` when indexed columns
// are involved (Error 8200). Instead, we normalize per-column via MODIFY COLUMN,
// which works even on PRIMARY KEY columns.
//
// This function normalizes:
//  1. The table being created (tableName) — its FK source columns
//  2. Referenced tables — their target columns (typically Id)
func (r *SchemaRepository) normalizeTableForFK(ctx context.Context, conn *sql.Conn, tableName string, fks []schema.ForeignKeyDefinition) {
	// Collect all (table, column) pairs that participate in FKs
	type colRef struct{ table, column string }
	var targets []colRef

	for _, fk := range fks {
		// Source column: tableName.fk.Column
		targets = append(targets, colRef{tableName, fk.Column})

		// Referenced column: parse "RefTable(RefCol)"
		refTable := fk.References
		refColumn := ""
		if idx := strings.Index(refTable, "("); idx != -1 {
			end := strings.Index(refTable, ")")
			if end > idx {
				refColumn = strings.TrimSpace(refTable[idx+1 : end])
			}
			refTable = strings.TrimSpace(refTable[:idx])
		}
		if refColumn != "" {
			targets = append(targets, colRef{refTable, refColumn})
		}
	}

	for _, t := range targets {
		var colType, collation, isNullable, colKey sql.NullString
		err := conn.QueryRowContext(ctx,
			`SELECT COLUMN_TYPE, COLLATION_NAME, IS_NULLABLE, COLUMN_KEY
			 FROM INFORMATION_SCHEMA.COLUMNS
			 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?`,
			t.table, t.column).Scan(&colType, &collation, &isNullable, &colKey)
		if err != nil {
			// Table or column doesn't exist yet — will be created in this batch
			continue
		}

		upper := strings.ToUpper(colType.String)
		needsFix := false
		targetType := "VARCHAR(255)"

		// Check collation mismatch
		if collation.Valid && collation.String != "utf8mb4_unicode_ci" {
			needsFix = true
		}
		// Check width mismatch (e.g. VARCHAR(18) vs VARCHAR(255))
		if strings.HasPrefix(upper, "VARCHAR") && upper != "VARCHAR(255)" {
			needsFix = true
		}

		if !needsFix {
			continue
		}

		// Build MODIFY COLUMN with correct nullability
		nullClause := "NOT NULL"
		if isNullable.Valid && isNullable.String == "YES" {
			nullClause = "DEFAULT NULL"
		}

		r.logger.Info(fmt.Sprintf("normalizing %s.%s (%s %s -> VARCHAR(255) utf8mb4_unicode_ci)",
			t.table, t.column, colType.String, collation.String))

		modifySQL := fmt.Sprintf(
			"ALTER TABLE `%s` MODIFY COLUMN `%s` %s CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci %s",
			t.table, t.column, targetType, nullClause)
		if _, err := conn.ExecContext(ctx, modifySQL); err != nil {
			r.logger.Warn(fmt.Sprintf("failed to normalize %s.%s: %v", t.table, t.column, err))
		}
	}
}

// CreateTableWithStrictMetadata creates a table ensuring metadata uniqueness (Strict Insert)
func (r *SchemaRepository) CreateTableWithStrictMetadata(ctx context.Context, def schema.TableDefinition, objectMeta *models.ObjectMetadata) (err error) {
	// 1. Create Physical Table
	if err = r.CreatePhysicalTable(ctx, def); err != nil {
		return err
	}

	// TRANSACTION: Register Metadata
	tx, err := r.db.Begin()
	if err != nil {
		return apperrors.NewInternalError("failed to begin transaction", err)
	}
	// Ensure rollback on panic or error (safe to call after Commit)
	defer func() {
		_ = tx.Rollback()
	}()

	// 1. Register in _System_Table
	if err = r.registerTable(def, tx); err != nil {
		return apperrors.NewInternalError(fmt.Sprintf("failed to register table %s", def.TableName), err)
	}

	// 2. Register Object Metadata (Strict)
	// We use InsertObjectMetadata instead of BatchSaveObjectMetadata to fail on uniqueness
	if err = r.InsertObjectMetadata(context.Background(), objectMeta, tx); err != nil {
		r.logger.Warn(fmt.Sprintf("strict metadata registration failed for %s; preserving physical table for fail-safe recovery", def.TableName))
		return apperrors.NewInternalError(fmt.Sprintf("failed to register object (strict) %s", def.TableName), err)
	}

	// 3. Register Fields (Batch is OK here as fields belong to new object)
	batchFields := make([]schema.FieldWithContext, 0, len(def.Columns))
	for _, col := range def.Columns {
		fc := r.PrepareFieldForBatch(def.TableName, col)
		// CRITICAL: PrepareFieldForBatch generates a new random Object ID now that we use UUIDs.
		// We must override it with the actual Object ID we just registered to ensure linkage.
		if objectMeta != nil && objectMeta.ID != "" {
			fc.ObjectID = objectMeta.ID
		}
		batchFields = append(batchFields, fc)
	}

	if err = r.BatchSaveFieldMetadata(context.Background(), batchFields, tx); err != nil {
		return apperrors.NewInternalError(fmt.Sprintf("failed to register fields for %s", def.TableName), err)
	}

	if err = tx.Commit(); err != nil {
		return apperrors.NewInternalError("failed to commit metadata transaction", err)
	}

	r.logger.Info(fmt.Sprintf("table created and registered (strict): %s", def.TableName))
	return nil
}

// BatchCreatePhysicalTables performs parallel DDL creation and then batch registers in _System_Table.
// Fail-safe behavior: this method never drops managed tables on failure.
// It returns the first error so callers can retry or resume safely.
func (r *SchemaRepository) BatchCreatePhysicalTables(ctx context.Context, defs []schema.TableDefinition) error {
	sem := make(chan struct{}, 10)
	var wg sync.WaitGroup
	var errMu sync.Mutex
	var ddlErrors []error

	r.logger.Info(fmt.Sprintf("starting parallel DDL for %d tables...", len(defs)))

	for _, def := range defs {
		wg.Add(1)
		go func(d schema.TableDefinition) {
			defer wg.Done()
			sem <- struct{}{}
			defer func() { <-sem }()

			if createErr := r.CreatePhysicalTable(ctx, d); createErr != nil {
				errMu.Lock()
				ddlErrors = append(ddlErrors, apperrors.NewInternalError(
					fmt.Sprintf("failed to create table %s", d.TableName), createErr))
				errMu.Unlock()
			}
		}(def)
	}

	wg.Wait()

	if len(ddlErrors) > 0 {
		firstErr := ddlErrors[0]
		r.logger.Error(fmt.Sprintf("batch DDL had %d error(s); fail-safe mode preserves all tables for resume",
			len(ddlErrors)), firstErr)
		return firstErr
	}

	// 2. Batch Register in _System_Table
	r.logger.Info(fmt.Sprintf("batch registering %d tables in _System_Table...", len(defs)))
	if regErr := r.BatchRegisterTables(defs, r.db); regErr != nil {
		r.logger.Error("batch registration failed; fail-safe mode preserves tables for resume", regErr)
		return apperrors.NewInternalError("failed to batch register tables", regErr)
	}

	return nil
}

// registerTable registers the table in _System_Table registry
func (r *SchemaRepository) registerTable(def schema.TableDefinition, tx interface{}) error {
	var exec Executor
	if tx != nil {
		if e, ok := tx.(Executor); ok {
			exec = e
		}
	}
	if exec == nil {
		exec = r.db
	}
	return r.BatchRegisterTables([]schema.TableDefinition{def}, exec)
}

// BatchRegisterTables registers multiple tables in _System_Table registry in a single statement
func (r *SchemaRepository) BatchRegisterTables(defs []schema.TableDefinition, tx interface{}) error {
	if len(defs) == 0 {
		return nil
	}
	var exec Executor
	if tx != nil {
		if e, ok := tx.(Executor); ok {
			exec = e
		}
	}
	if exec == nil {
		exec = r.db
	}

	var batchMaps []map[string]interface{}
	for _, def := range defs {
		id := schema.GenerateTableID(def.TableName)
		record := map[string]interface{}{
			constants.ColID:                       id,
			constants.FieldSysTable_TableName:     def.TableName,
			constants.FieldSysTable_TableType:     def.TableType,
			constants.FieldSysTable_Category:      def.Category,
			constants.FieldSysTable_Description:   def.Description,
			constants.FieldSysTable_IsManaged:     def.IsManaged,
			constants.FieldSysTable_SchemaVersion: def.SchemaVersion,
			constants.FieldSysTable_CreatedBy:     CreatedByBootstrap,
			constants.ColCreatedDate:              time.Now().UTC(),
			constants.ColLastModifiedDate:         time.Now().UTC(),
		}
		batchMaps = append(batchMaps, record)
	}

	cols := []string{
		constants.ColID, constants.FieldSysTable_TableName, constants.FieldSysTable_TableType,
		constants.FieldSysTable_Category, constants.FieldSysTable_Description, constants.FieldSysTable_IsManaged,
		constants.FieldSysTable_SchemaVersion, constants.FieldSysTable_CreatedBy,
		constants.ColCreatedDate, constants.ColLastModifiedDate,
	}

	sqlStr, params := query.BulkInsertOrdered(constants.TableTable, cols, batchMaps, nil)

	// Append ON DUPLICATE KEY UPDATE to handle re-registration gracefully.
	sqlStr += fmt.Sprintf(" ON DUPLICATE KEY UPDATE %s = NOW()", constants.ColLastModifiedDate)

	_, err := exec.Exec(sqlStr, params...)
	return err
}

// DropTable drops a table and removes it from the registry
func (r *SchemaRepository) DropTable(tableName string) error {
	r.logger.Warn(fmt.Sprintf("dropping table: %s", tableName))

	// Drop the table
	if _, err := r.db.Exec(fmt.Sprintf("DROP TABLE IF EXISTS `%s`", tableName)); err != nil {
		return apperrors.NewInternalError(fmt.Sprintf("failed to drop table %s", tableName), err)
	}

	// Unregister from _System_Table
	qr := query.Delete(constants.TableTable).
		Where(constants.FieldSysTable_TableName, tableName).
		Build()
	if _, err := r.db.Exec(qr.SQL, qr.Params...); err != nil {
		r.logger.Warn(fmt.Sprintf("failed to unregister table %s: %v", tableName, err))
	}

	// UNREGISTER from _System_Object and _System_Field
	// Deleting the Object metadata should cascade to fields if configured,
	// but we explicitly delete fields first to ensure clean removal.

	// Delete fields for this object
	// Subquery get object ID
	subQ := query.From(constants.TableObject).
		Select([]string{constants.FieldID}).
		Where(constants.FieldSysObject_APIName, tableName).
		Build()

	qrFields := query.Delete(constants.TableField).
		WhereRaw(fmt.Sprintf("%s IN (%s)", constants.FieldObjectID, subQ.SQL), subQ.Params).
		Build()

	if _, err := r.db.Exec(qrFields.SQL, qrFields.Params...); err != nil {
		r.logger.Warn(fmt.Sprintf("failed to delete fields for object %s: %v", tableName, err))
	}

	// Delete object
	qrObj := query.Delete(constants.TableObject).
		Where(constants.FieldSysObject_APIName, tableName).
		Build()
	if _, err := r.db.Exec(qrObj.SQL, qrObj.Params...); err != nil {
		r.logger.Warn(fmt.Sprintf("failed to delete object metadata %s: %v", tableName, err))
	}

	// Autonumber v2: `_System_Field.AutoNumberFormat` + its
	// `_System_AutoNumberCounter` rows are cascade-deleted by the
	// generic field/object purge path above. The retired
	// `_System_AutoNumber` table no longer exists.

	// Delete Object Permissions
	qrOp := query.Delete(constants.TableObjectPermissions).
		Where(constants.FieldObjectPermissions_SobjectType, tableName).
		Build()
	if _, err := r.db.Exec(qrOp.SQL, qrOp.Params...); err != nil {
		r.logger.Warn(fmt.Sprintf("failed to delete object permissions for %s: %v", tableName, err))
	}

	// Delete Field Permissions
	qrFp := query.Delete(constants.TableFieldPermissions).
		Where(constants.FieldFieldPermissions_SobjectType, tableName).
		Build()
	if _, err := r.db.Exec(qrFp.SQL, qrFp.Params...); err != nil {
		r.logger.Warn(fmt.Sprintf("failed to delete field permissions for %s: %v", tableName, err))
	}

	r.logger.Info(fmt.Sprintf("table dropped and metadata cleaned: %s", tableName))
	return nil
}

// ValidateSchema checks if a table matches its expected definition by comparing
// the actual database schema (from INFORMATION_SCHEMA) with the registered metadata.
// Returns an error if drift is detected, nil if schema matches or table doesn't exist.
func (r *SchemaRepository) ValidateSchema(tableName string) error {
	// Get expected columns from _System_Field metadata
	expectedQR := query.From(constants.TableField).
		Select([]string{
			fmt.Sprintf("`%s`.`%s`", constants.TableField, constants.FieldAPIName),
			fmt.Sprintf("`%s`.`%s`", constants.TableField, constants.FieldType),
		}).
		Join("JOIN", constants.TableObject, "o",
			fmt.Sprintf("`%s`.`%s` = `o`.`%s`", constants.TableField, constants.FieldObjectID, constants.FieldID)).
		WhereRaw(fmt.Sprintf("`o`.`%s` = ?", constants.FieldObjectAPIName), []interface{}{tableName}).
		Build()

	expectedRows, err := r.db.Query(expectedQR.SQL, expectedQR.Params...)
	if err != nil {
		return apperrors.NewInternalError("failed to query expected schema", err)
	}
	defer func() { _ = expectedRows.Close() }()

	expectedFields := make(map[string]string) // field_name -> type
	for expectedRows.Next() {
		var fieldName, fieldType string
		if err := expectedRows.Scan(&fieldName, &fieldType); err != nil {
			continue
		}
		expectedFields[fieldName] = fieldType
	}
	if err := expectedRows.Err(); err != nil {
		return apperrors.NewInternalError("error iterating expected fields", err)
	}

	// If no metadata registered, skip validation (table might be new or system-internal)
	if len(expectedFields) == 0 {
		return nil
	}

	// Get actual columns from INFORMATION_SCHEMA (raw SQL is appropriate for system introspection).

	actualQuery := `
		SELECT COLUMN_NAME, DATA_TYPE 
		FROM INFORMATION_SCHEMA.COLUMNS 
		WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?
	`
	actualRows, err := r.db.Query(actualQuery, tableName)
	if err != nil {
		return apperrors.NewInternalError("failed to query actual schema", err)
	}
	defer func() { _ = actualRows.Close() }()

	actualFields := make(map[string]string)
	for actualRows.Next() {
		var columnName, dataType string
		if err := actualRows.Scan(&columnName, &dataType); err != nil {
			continue
		}
		actualFields[columnName] = dataType
	}
	if err := actualRows.Err(); err != nil {
		return apperrors.NewInternalError("error iterating actual fields", err)
	}

	// Compare: Find missing columns in database
	var missingColumns []string
	for expectedField := range expectedFields {
		if _, exists := actualFields[expectedField]; !exists {
			missingColumns = append(missingColumns, expectedField)
		}
	}

	if len(missingColumns) > 0 {
		return apperrors.NewValidationError("schema", fmt.Sprintf("schema drift detected for %s: missing columns %v", tableName, missingColumns))
	}

	return nil
}

// ValidateFieldDefinition validates the field schema against core assertions
func (r *SchemaRepository) ValidateFieldDefinition(field schema.ColumnDefinition) error {
	// 1. Naming Convention: snake_case (allow leading underscores for system fields)
	validName := regexp.MustCompile(constants.FieldNamePattern)
	if !validName.MatchString(field.Name) {
		return apperrors.NewValidationError("fieldName", fmt.Sprintf("field name '%s' must be alphanumeric (PascalCase/snake_case)", field.Name))
	}

	// 2. Type-Specific Assertions
	// Strict Validation: Ensure the type is resolvable
	if _, err := ResolveSQLType(field.Type); err != nil {
		return apperrors.NewValidationError(field.Name, fmt.Sprintf("invalid field type '%s' for column '%s': %v", field.Type, field.Name, err))
	}

	// Use LogicalType for validation to catch metadata issues (e.g. Picklist options)
	// regardless of the underlying SQL storage type (e.g. VARCHAR)
	checkType := field.LogicalType
	if checkType == "" {
		checkType = field.Type
	}

	switch checkType {
	case string(constants.FieldTypeReference):
		// User assumption: Lookups must specific a target
		if len(field.ReferenceTo) == 0 {
			return apperrors.NewValidationError(field.Name, fmt.Sprintf("lookup field '%s' must have a valid 'reference_to' target", field.Name))
		}
	case string(constants.FieldTypePicklist):
		// User assumption: Picklists must have options
		if len(field.Options) == 0 {
			// Fail Fast: Reject metadata that maps to a Picklist but has no options.
			return apperrors.NewValidationError(field.Name, fmt.Sprintf("picklist field '%s' must have at least one option", field.Name))
		}
	case string(constants.FieldTypeFormula):
		if field.Formula == "" {
			return apperrors.NewValidationError(field.Name, fmt.Sprintf("formula field '%s' must have a formula expression", field.Name))
		}
		if field.ReturnType == "" {
			return apperrors.NewValidationError(field.Name, fmt.Sprintf("formula field '%s' must have a valid return_type", field.Name))
		}
	}

	return nil
}

// SchemaHealth removed (moved to domain/schema)

// ValidateSchemaRegistry compares the registry against actual DB tables
func (r *SchemaRepository) ValidateSchemaRegistry() (*schema.SchemaHealth, error) {
	// Get expected tables
	qr := query.From(constants.TableTable).
		Select([]string{constants.FieldSysTable_TableName}).
		Where(constants.FieldSysTable_IsManaged, 1).
		Build()

	expectedRows, err := r.db.Query(qr.SQL, qr.Params...)
	if err != nil {
		return nil, err
	}
	defer func() { _ = expectedRows.Close() }()

	expected := make(map[string]bool)
	for expectedRows.Next() {
		var tableName string
		if err := expectedRows.Scan(&tableName); err != nil {
			continue
		}
		expected[tableName] = true
	}
	if err := expectedRows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating expected tables", err)
	}

	// Get actual tables
	actualRows, err := r.db.Query(`
		SELECT TABLE_NAME 
		FROM INFORMATION_SCHEMA.TABLES 
		WHERE TABLE_SCHEMA = DATABASE()
	`)
	if err != nil {
		return nil, err
	}
	defer func() { _ = actualRows.Close() }()

	actual := make(map[string]bool)
	for actualRows.Next() {
		var tableName string
		if err := actualRows.Scan(&tableName); err != nil {
			continue
		}
		actual[tableName] = true
	}
	if err := actualRows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating actual tables", err)
	}

	// Find missing tables
	var missing []string
	for exp := range expected {
		if !actual[exp] {
			missing = append(missing, exp)
		}
	}

	status := StatusHealthy
	if len(missing) > 0 {
		status = StatusUnhealthy
	}

	return &schema.SchemaHealth{
		Status:         status,
		ExpectedCount:  len(expected),
		ActualCount:    len(actual),
		MissingTables:  missing,
		RegistryHealth: StatusOperational,
	}, nil
}