package controlplane

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

	bootstate "github.com/nexuscrm/db/pkg/bootstate"
)

const (
	migrationIdListViewLegacyCutover = "20260625_listview_legacy_cutover_owner_fk"
	listViewOwnerForeignKeyName      = "fk__System_ListView_OwnerId"
)

const listViewBlankOwnerNormalizeSQL = `
UPDATE _System_ListView
   SET OwnerId = NULL,
       LastModifiedDate = NOW()
 WHERE OwnerId IS NOT NULL
   AND TRIM(OwnerId) = ''`

const listViewOrphanOwnerDeleteSQL = `
DELETE FROM _System_ListView
 WHERE NULLIF(TRIM(OwnerId), '') IS NOT NULL
   AND NOT EXISTS (
       SELECT 1
         FROM ` + "`User`" + ` u
        WHERE u.Id = _System_ListView.OwnerId
   )`

const listViewOwnerAddForeignKeySQL = "ALTER TABLE `_System_ListView` ADD CONSTRAINT `" + listViewOwnerForeignKeyName + "` FOREIGN KEY (`OwnerId`) REFERENCES `User` (`Id`) ON DELETE CASCADE"

// BackfillListViewLegacyCutover closes the warm-upgrade gap left by the
// metadata-only phase of private list views. Legacy NULL/NULL rows remain a
// deliberately quarantined catalog-compatibility shape; ownerful rows must have
// a live User parent before the physical OwnerId FK can be installed.
func BackfillListViewLegacyCutover(ctx context.Context, db *sql.DB) (int64, error) {
	if db == nil {
		return 0, fmt.Errorf("BackfillListViewLegacyCutover requires a database")
	}
	applied, err := bootstate.MigrationApplied(ctx, db, migrationIdListViewLegacyCutover)
	if err != nil {
		return 0, fmt.Errorf("BackfillListViewLegacyCutover: check stamp: %w", err)
	}
	ready, err := bootstate.ListViewLegacyCutoverColumnsReady(ctx, db)
	if err != nil {
		return 0, err
	}
	if !ready {
		return 0, fmt.Errorf("BackfillListViewLegacyCutover: required _System_ListView columns missing after reconciliation")
	}
	if applied {
		closed, err := bootstate.ListViewLegacyCutoverClosed(ctx, db)
		if err != nil {
			return 0, err
		}
		if closed {
			log.Println("   BackfillListViewLegacyCutover: already applied; skipping")
			return 0, nil
		}
		log.Println("   BackfillListViewLegacyCutover: completed receipt exists but live closure check failed; repairing")
	}

	normalized, err := execRowsAffected(ctx, db, listViewBlankOwnerNormalizeSQL, "normalize blank _System_ListView.OwnerId")
	if err != nil {
		return normalized, err
	}
	deleted, err := execRowsAffected(ctx, db, listViewOrphanOwnerDeleteSQL, "delete orphaned _System_ListView OwnerId rows")
	if err != nil {
		return normalized + deleted, err
	}

	remaining, err := bootstate.ListViewDanglingOwnerCount(ctx, db)
	if err != nil {
		return normalized + deleted, fmt.Errorf("BackfillListViewLegacyCutover closure check: %w", err)
	}
	if remaining != 0 {
		return normalized + deleted, fmt.Errorf("BackfillListViewLegacyCutover: %d _System_ListView row(s) still have dangling OwnerId", remaining)
	}
	if err := ensureListViewOwnerForeignKeyCascade(ctx, db); err != nil {
		return normalized + deleted, err
	}
	if err := bootstate.RecordMigrationApplied(
		ctx, db,
		migrationIdListViewLegacyCutover,
		1,
		"Normalize list-view OwnerId cutover state and ensure OwnerId User cascade FK.",
	); err != nil {
		return normalized + deleted, err
	}
	if normalized > 0 || deleted > 0 {
		log.Printf("   BackfillListViewLegacyCutover: normalized blank OwnerId rows=%d orphan owner rows deleted=%d", normalized, deleted)
	}
	return normalized + deleted, nil
}

func execRowsAffected(ctx context.Context, db *sql.DB, statement, label string) (int64, error) {
	res, err := db.ExecContext(ctx, statement)
	if err != nil {
		return 0, fmt.Errorf("BackfillListViewLegacyCutover %s: %w", label, err)
	}
	rows, err := res.RowsAffected()
	if err != nil {
		return 0, fmt.Errorf("BackfillListViewLegacyCutover %s rows affected: %w", label, err)
	}
	return rows, nil
}

func ensureListViewOwnerForeignKeyCascade(ctx context.Context, db *sql.DB) error {
	shape, err := bootstate.ListViewOwnerForeignKeyShapeForDB(ctx, db)
	if err != nil {
		return err
	}
	if shape.Exists {
		if strings.EqualFold(strings.TrimSpace(shape.DeleteRule), "CASCADE") {
			return nil
		}
		dropSQL := "ALTER TABLE `_System_ListView` DROP FOREIGN KEY " + quoteSQLIdentifier(shape.ConstraintName)
		if _, err := db.ExecContext(ctx, dropSQL); err != nil {
			return fmt.Errorf("BackfillListViewLegacyCutover drop non-cascade OwnerId FK %q: %w", shape.ConstraintName, err)
		}
	}
	if _, err := db.ExecContext(ctx, listViewOwnerAddForeignKeySQL); err != nil {
		if isDuplicateConstraintError(err) {
			return nil
		}
		return fmt.Errorf("BackfillListViewLegacyCutover add OwnerId FK: %w", err)
	}
	return nil
}

func quoteSQLIdentifier(identifier string) string {
	return "`" + strings.ReplaceAll(identifier, "`", "``") + "`"
}

func isDuplicateConstraintError(err error) bool {
	if err == nil {
		return false
	}
	msg := strings.ToLower(err.Error())
	return strings.Contains(msg, "duplicate") || strings.Contains(msg, "already exists")
}