package controlplane

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

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

const migrationIdBackfillScheduledRunReservationCompatibility = "20260513_backfill_scheduled_run_reservation_compatibility"

// BackfillScheduledRunReservationCompatibility bridges legacy
// _System_ScheduledRunReservation rows from the main-branch storage shape to
// the PR #647 runtime contract.
//
// Why this is a one-time migration, not runtime fallback:
//
//   - The scheduled reservation service now reads/writes WindowToken, Status,
//     and ReservedAt directly. Leaving legacy rows blank in those columns would
//     make warm-upgraded reservations invisible to the new runtime contract.
//   - The physical unique key intentionally remains on ScheduleWindowKey during
//     this compatibility bridge. That preserves duplicate prevention for legacy
//     rows while the runtime writes both ScheduleWindowKey and WindowToken.
//   - New rows already persist the new columns correctly. The remaining work is
//     to backfill old rows once, stamp it, and let later cutover work own the
//     final WindowToken-only unique identity.
//
// Fatality: ApplyAll must abort if this migration cannot complete. Otherwise a
// warm-upgraded org can start the new runtime with partially-bridged
// reservation rows and lose restart-safe duplicate prevention.
func BackfillScheduledRunReservationCompatibility(ctx context.Context, db *sql.DB) (int, error) {
	applied, err := bootstate.MigrationApplied(ctx, db, migrationIdBackfillScheduledRunReservationCompatibility)
	if err != nil {
		return 0, err
	}
	if applied {
		log.Printf("   ⏭  BackfillScheduledRunReservationCompatibility: already applied; skipping")
		return 0, nil
	}

	ready, err := scheduledRunReservationCompatibilityColumnsReady(ctx, db)
	if err != nil {
		return 0, err
	}
	if !ready {
		return 0, fmt.Errorf("BackfillScheduledRunReservationCompatibility: required columns missing after reconciliation")
	}

	const updateSQL = `
UPDATE _System_ScheduledRunReservation
SET ` + "`WindowToken`" + ` = CASE
        WHEN ` + "`WindowToken`" + ` IS NULL OR TRIM(` + "`WindowToken`" + `) = '' THEN ` + "`ScheduleWindowKey`" + `
        ELSE ` + "`WindowToken`" + `
    END,
    ` + "`Status`" + ` = CASE
        WHEN ` + "`Status`" + ` IS NULL OR TRIM(` + "`Status`" + `) = '' THEN
            CASE
                WHEN ` + "`State`" + ` IS NULL OR TRIM(` + "`State`" + `) = '' THEN 'reserved'
                ELSE LOWER(TRIM(` + "`State`" + `))
            END
        ELSE ` + "`Status`" + `
    END,
    ` + "`ReservedAt`" + ` = CASE
        WHEN ` + "`ReservedAt`" + ` IS NULL THEN COALESCE(` + "`CreatedDate`" + `, NOW())
        ELSE ` + "`ReservedAt`" + `
    END,
    ` + "`LastModifiedDate`" + ` = NOW()
WHERE (` + "`WindowToken`" + ` IS NULL OR TRIM(` + "`WindowToken`" + `) = '')
   OR (` + "`Status`" + ` IS NULL OR TRIM(` + "`Status`" + `) = '')
   OR ` + "`ReservedAt`" + ` IS NULL`

	res, err := db.ExecContext(ctx, updateSQL)
	if err != nil {
		return 0, fmt.Errorf("BackfillScheduledRunReservationCompatibility: %w", err)
	}
	touched, err := res.RowsAffected()
	if err != nil {
		return 0, fmt.Errorf("BackfillScheduledRunReservationCompatibility rows affected: %w", err)
	}

	var remaining int
	if err := db.QueryRowContext(ctx, `
SELECT COUNT(*)
FROM _System_ScheduledRunReservation
WHERE (`+"`WindowToken`"+` IS NULL OR TRIM(`+"`WindowToken`"+`) = '')
   OR (`+"`Status`"+` IS NULL OR TRIM(`+"`Status`"+`) = '')
   OR `+"`ReservedAt`"+` IS NULL`).Scan(&remaining); err != nil {
		return int(touched), fmt.Errorf("BackfillScheduledRunReservationCompatibility closure check: %w", err)
	}
	if remaining != 0 {
		return int(touched), fmt.Errorf("BackfillScheduledRunReservationCompatibility: %d incompatible row(s) remain after backfill", remaining)
	}

	if err := bootstate.RecordMigrationApplied(
		ctx, db,
		migrationIdBackfillScheduledRunReservationCompatibility,
		1,
		"Backfill scheduled reservation compatibility columns for warm-upgrade rows before WindowToken cutover (#647).",
	); err != nil {
		return int(touched), err
	}

	if touched > 0 {
		log.Printf("   🧼 BackfillScheduledRunReservationCompatibility: backfilled %d reservation row(s)", touched)
	}
	return int(touched), nil
}

func scheduledRunReservationCompatibilityColumnsReady(ctx context.Context, db *sql.DB) (bool, error) {
	var count int
	if err := db.QueryRowContext(ctx, `
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = '_System_ScheduledRunReservation'
  AND COLUMN_NAME IN ('ScheduleWindowKey', 'WindowToken', 'State', 'Status', 'ReservedAt', 'CreatedDate')`).Scan(&count); err != nil {
		return false, fmt.Errorf("scheduledRunReservationCompatibilityColumnsReady: %w", err)
	}
	return count == 6, nil
}