<?php

declare(strict_types=1);

namespace KimaAI\Database\Migrations;

use KimaAI\Database\Db;

defined('ABSPATH') || exit;

/**
 * Migration cookbook file.
 *
 * Copy this file to a real versioned class like Version2026012702 and register it in Migrator::updates().
 *
 * Patterns covered:
 *  1) Create new table
 *  2) Add column (safe) + backfill + then enforce NOT NULL/default (2-step)
 *  3) Rename column (non-breaking: new column + backfill + keep old)
 *  4) Change column type (safe: shadow column + backfill + swap later)
 *  5) Add index
 *  6) Add UNIQUE index (with dedupe before)
 *  7) Backfill computed data (hash, derived fields)
 *  8) Migrate data from JSON column -> normalized child table
 *  9) Merge duplicate rows / cleanup data
 * 10) Drop column / drop table (only when explicitly enabled)
 *
 * All scenarios are OFF by default.
 */
final class VersionYYYYMMDDNN {

  /**
   * Toggle what you want to test.
   * Run one scenario at a time in real testing to isolate.
   *
   * @var array<string,bool>
   */
  private const RUN_SCENARIOS = [
    'create_new_table' => false,
    'add_column_and_backfill' => false,
    'rename_column_non_breaking' => false,
    'change_type_via_shadow_column' => false,
    'add_index' => false,
    'add_unique_index_with_dedupe' => false,
    'backfill_computed_hash' => false,
    'json_to_child_table_migration' => false,
    'merge_duplicates_cleanup' => false,
    'danger_drop_column' => false,
    'danger_drop_table' => false,
  ];

  /**
   * Hard time budget (seconds) per run.
   * If you run migrations via cron single-step, keep this conservative.
   */
  private const TIME_BUDGET_SECONDS = 12;

  /**
   * Chunk sizes for big updates.
   */
  private const CHUNK_SIZE = 500;

  /**
   * Read enabled scenarios for THIS migration from an option.
   *
   * Stored as:
   * option kimaai_migration_scenarios = [
   *   'KimaAI\\Database\\Migrations\\Version2026012702' => [
   *      'add_column_and_backfill' => true,
   *      ...
   *   ]
   * ]
   */
  private static function isScenarioEnabled(string $scenario): bool {
    $all = get_option('kimaai_migration_scenarios', []);
    if (!is_array($all)) {
      return false;
    }
    $cls = self::class;
    $map = $all[$cls] ?? [];
    return is_array($map) && !empty($map[$scenario]);
  }


  public static function up(): void {
    if (self::isScenarioEnabled('create_new_table')) {
      self::scenarioCreateNewTable();
    }

    if (self::isScenarioEnabled('add_column_and_backfill')) {
      self::scenarioAddColumnAndBackfill();
    }

    if (self::isScenarioEnabled('rename_column_non_breaking')) {
      self::scenarioRenameColumnNonBreaking();
    }

    if (self::isScenarioEnabled('change_type_via_shadow_column')) {
      self::scenarioChangeTypeViaShadowColumn();
    }

    if (self::isScenarioEnabled('add_index')) {
      self::scenarioAddIndex();
    }

    if (self::isScenarioEnabled('add_unique_index_with_dedupe')) {
      self::scenarioAddUniqueIndexWithDedupe();
    }

    if (self::isScenarioEnabled('backfill_computed_hash')) {
      self::scenarioBackfillComputedHash();
    }

    if (self::isScenarioEnabled('json_to_child_table_migration')) {
      self::scenarioJsonToChildTableMigration();
    }

    if (self::isScenarioEnabled('merge_duplicates_cleanup')) {
      self::scenarioMergeDuplicatesCleanup();
    }

    // Dangerous ones still possible for testing:
    if (self::isScenarioEnabled('danger_drop_column')) {
      self::scenarioDangerDropColumn();
    }

    if (self::isScenarioEnabled('danger_drop_table')) {
      self::scenarioDangerDropTable();
    }
  }

  /*
  ---------------------------------------------------------------------- */
  /*
  Helpers (time budget + chunked loops)                                   */
  /* ---------------------------------------------------------------------- */

  private static function startTime(): float {
    return microtime(true);
  }

  private static function timeRemaining(float $started_at): float {
    return self::TIME_BUDGET_SECONDS - (microtime(true) - $started_at);
  }

  /**
   * Run a chunked loop until time budget is nearly exhausted.
   *
   * @param callable $step function(): bool  returns true if more work remains
   */
  private static function runChunked(callable $step): void {
    $started_at = self::startTime();
    while (self::timeRemaining($started_at) > 1.5) {
      $more = (bool) $step();
      if (!$more) {
        break;
      }
    }
  }

  /**
   * Option key helper for per-migration progress markers.
   */
  private static function opt(string $suffix): string {
    return 'kimaai_mig_' . self::class . '_' . $suffix;
  }

  /*
  ---------------------------------------------------------------------- */
  /*
  Scenario 1: Create a new table (custom SQL example)                      */
  /* ---------------------------------------------------------------------- */

  private static function scenarioCreateNewTable(): void {
    $wpdb = Db::wpdb();

    $table = Db::tableName('kimaai_example_new_table');
    if (Db::tableExists($table)) {
      return;
    }

    $charset = $wpdb->get_charset_collate();
    $sql = "
      CREATE TABLE {$table} (
        id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        ref_id BIGINT(20) UNSIGNED NOT NULL,
        status VARCHAR(20) NOT NULL DEFAULT 'new',
        payload LONGTEXT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY idx_ref_id (ref_id),
        KEY idx_status (status)
      ) {$charset} ENGINE=InnoDB;
    ";

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared
    $wpdb->query($sql);
  }

  /*
  ---------------------------------------------------------------------- */
  /*
  Scenario 2: Add column + backfill + then enforce NOT NULL/default        */
  /* ---------------------------------------------------------------------- */

  private static function scenarioAddColumnAndBackfill(): void {
    $wpdb = Db::wpdb();
    $table = Db::tableName('kimaai_chats');

    if (!Db::tableExists($table)) {
      return;
    }

    // Step A: add nullable column first (non-breaking).
    Db::addColumnIfMissing($table, 'user_agent', 'user_agent VARCHAR(255) NULL DEFAULT NULL');

    // Step B: backfill in chunks to avoid timeouts.
    $progress_key = self::opt('addcol_backfill_last_id');
    $last_id = (int) get_option($progress_key, 0);

    self::runChunked(function () use ($wpdb, $table, $progress_key, &$last_id): bool {
      // Fetch a chunk.
      // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
      $rows = $wpdb->get_results(
        $wpdb->prepare(
          "SELECT id FROM {$table} WHERE id > %d AND (user_agent IS NULL OR user_agent = '') ORDER BY id ASC LIMIT %d",
          $last_id,
          self::CHUNK_SIZE
        ),
        ARRAY_A
      );

      if (!$rows) {
        // Done backfilling.
        delete_option($progress_key);

        // Step C (optional): enforce NOT NULL after backfill.
        // Be careful: this is a schema change that can lock tables on big sites.
        // For real production: do it in a later release after you’re confident.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared
        // $wpdb->query("ALTER TABLE {$table} MODIFY user_agent VARCHAR(255) NOT NULL DEFAULT ''");

        return false;
      }

      $ids = array_map(static fn($r) => (int) $r['id'], $rows);
      $last_id = max($ids);
      update_option($progress_key, $last_id, false);

      // Example backfill value: empty string.
      // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
      $wpdb->query(
        $wpdb->prepare(
          "UPDATE {$table} SET user_agent = %s WHERE id IN (" . implode(',', array_fill(0, count($ids), '%d')) . ')',
          array_merge([''], $ids)
        )
      );

      return true; // More work likely remains.
    });
  }

  /*
  ---------------------------------------------------------------------- */
  /*
  Scenario 3: Rename column (non-breaking, recommended)                    */
  /* ---------------------------------------------------------------------- */

  private static function scenarioRenameColumnNonBreaking(): void {
    $wpdb = Db::wpdb();
    $table = Db::tableName('kimaai_chat_feedback');

    if (!Db::tableExists($table)) {
      return;
    }

    // Example: rename comment -> feedback_comment (non-breaking approach)
    $old = 'comment';
    $new = 'feedback_comment';

    // If new already exists, assume already migrated.
    if (Db::columnExists($table, $new)) {
      return;
    }

    // Add new column first.
    Db::addColumnIfMissing($table, $new, "{$new} TEXT NULL");

    // Backfill: copy data in chunks.
    $progress_key = self::opt('renamecol_last_id');
    $last_id = (int) get_option($progress_key, 0);

    self::runChunked(function () use ($wpdb, $table, $old, $new, $progress_key, &$last_id): bool {
      // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
      $rows = $wpdb->get_results(
        $wpdb->prepare(
          "SELECT id FROM {$table} WHERE id > %d AND {$new} IS NULL AND {$old} IS NOT NULL ORDER BY id ASC LIMIT %d",
          $last_id,
          self::CHUNK_SIZE
        ),
        ARRAY_A
      );

      if (!$rows) {
        delete_option($progress_key);
        // Keep old column for at least one release for safety.
        return false;
      }

      $ids = array_map(static fn($r) => (int) $r['id'], $rows);
      $last_id = max($ids);
      update_option($progress_key, $last_id, false);

      // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared
      $wpdb->query(
        "UPDATE {$table}
            SET {$new} = {$old}
          WHERE id IN (" . implode(',', array_map('intval', $ids)) . ')'
      );

      return true;
    });
  }

  /*
  ---------------------------------------------------------------------- */
  /*
  Scenario 4: Change column type via shadow column (safe)                  */
  /* ---------------------------------------------------------------------- */

  private static function scenarioChangeTypeViaShadowColumn(): void {
    $wpdb = Db::wpdb();
    $table = Db::tableName('kimaai_chat_csat');

    if (!Db::tableExists($table)) {
      return;
    }

    // Example: rating currently TINYINT; you want SMALLINT (or enforce range rules)
    // Don’t ALTER in-place on huge sites; use a shadow column:
    $shadow = 'rating_v2';

    Db::addColumnIfMissing($table, $shadow, "{$shadow} SMALLINT NULL DEFAULT NULL");

    // Backfill shadow from original.
    $progress_key = self::opt('changetype_last_id');
    $last_id = (int) get_option($progress_key, 0);

    self::runChunked(function () use ($wpdb, $table, $progress_key, &$last_id, $shadow): bool {
      // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
      $rows = $wpdb->get_results(
        $wpdb->prepare(
          "SELECT id FROM {$table} WHERE id > %d AND {$shadow} IS NULL ORDER BY id ASC LIMIT %d",
          $last_id,
          self::CHUNK_SIZE
        ),
        ARRAY_A
      );

      if (!$rows) {
        delete_option($progress_key);

        // Swap strategy (recommended across releases):
        // Release N: write both columns (app code).
        // Release N+1: read from shadow (app code).
        // Release N+2: drop old column (optional).
        //
        // For testing only, you could do a risky swap here, but production: avoid.
        return false;
      }

      $ids = array_map(static fn($r) => (int) $r['id'], $rows);
      $last_id = max($ids);
      update_option($progress_key, $last_id, false);

      // Copy rating -> rating_v2
      // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared
      $wpdb->query(
        "UPDATE {$table}
            SET {$shadow} = rating
          WHERE id IN (" . implode(',', array_map('intval', $ids)) . ')'
      );

      return true;
    });
  }

  /*
  ---------------------------------------------------------------------- */
  /*
  Scenario 5: Add index safely                                             */
  /* ---------------------------------------------------------------------- */

  private static function scenarioAddIndex(): void {
    $table = Db::tableName('kimaai_embeddings');
    if (!Db::tableExists($table)) {
      return;
    }

    // Example: index on (env_id, post_id) for faster lookups.
    Db::addIndexIfMissing($table, 'idx_env_post', 'KEY idx_env_post (env_id, post_id)');
  }

  /*
  ---------------------------------------------------------------------- */
  /*
  Scenario 6: Add UNIQUE index with dedupe first                            */
  /* ---------------------------------------------------------------------- */

  private static function scenarioAddUniqueIndexWithDedupe(): void {
    $wpdb = Db::wpdb();
    $table = Db::tableName('kimaai_link_suggestions');

    if (!Db::tableExists($table)) {
      return;
    }

    // Suppose you want UNIQUE(object_id) but you’re not sure old data has duplicates.
    // First: dedupe by keeping newest (highest id) and deleting others.
    $dedupe_done_key = self::opt('dedupe_done');

    if (!get_option($dedupe_done_key)) {
      self::runChunked(function () use ($wpdb, $table): bool {
        // Find duplicate object_ids in small batches.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $dups = $wpdb->get_results(
          "SELECT object_id, COUNT(*) AS c
             FROM {$table}
            GROUP BY object_id
           HAVING c > 1
            LIMIT 50",
          ARRAY_A
        );

        if (!$dups) {
          return false;
        }

        foreach ($dups as $d) {
          $oid = (int) $d['object_id'];

          // Keep the newest row (max id), delete the rest.
          // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
          $keep_id = (int) $wpdb->get_var(
            $wpdb->prepare("SELECT MAX(id) FROM {$table} WHERE object_id = %d", $oid)
          );

          if ($keep_id > 0) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query(
              $wpdb->prepare("DELETE FROM {$table} WHERE object_id = %d AND id <> %d", $oid, $keep_id)
            );
          }
        }

        return true;
      });

      update_option($dedupe_done_key, 1, false);
    }

    // Now add unique index.
    Db::addIndexIfMissing($table, 'object_id', 'UNIQUE KEY object_id (object_id)');
  }

  /*
  ---------------------------------------------------------------------- */
  /*
  Scenario 7: Backfill computed hash / derived field                        */
  /* ---------------------------------------------------------------------- */

  private static function scenarioBackfillComputedHash(): void {
    $wpdb = Db::wpdb();
    $table = Db::tableName('kimaai_chats');

    if (!Db::tableExists($table)) {
      return;
    }

    // Add a computed column (nullable first).
    Db::addColumnIfMissing($table, 'row_hash', 'row_hash CHAR(40) NULL DEFAULT NULL');
    Db::addIndexIfMissing($table, 'idx_row_hash', 'KEY idx_row_hash (row_hash)');

    $progress_key = self::opt('hash_last_id');
    $last_id = (int) get_option($progress_key, 0);

    self::runChunked(function () use ($wpdb, $table, $progress_key, &$last_id): bool {
      // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
      $rows = $wpdb->get_results(
        $wpdb->prepare(
          "SELECT id, session_id, ip, created_at
             FROM {$table}
            WHERE id > %d AND (row_hash IS NULL OR row_hash = '')
            ORDER BY id ASC
            LIMIT %d",
          $last_id,
          self::CHUNK_SIZE
        ),
        ARRAY_A
      );

      if (!$rows) {
        delete_option($progress_key);
        return false;
      }

      $ids = [];
      foreach ($rows as $r) {
        $id = (int) $r['id'];
        $ids[] = $id;
        $last_id = max($last_id, $id);

        $hash = sha1(
          (string) ($r['session_id'] ?? '') . '|' .
          (string) ($r['ip'] ?? '') . '|' .
          (string) ($r['created_at'] ?? '')
        );

        // Update per-row (safe, small chunk). You can also build a CASE WHEN bulk update.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->query(
          $wpdb->prepare("UPDATE {$table} SET row_hash = %s WHERE id = %d", $hash, $id)
        );
      }

      update_option($progress_key, $last_id, false);
      return true;
    });
  }

  /*
  ---------------------------------------------------------------------- */
  /*
  Scenario 8: Migrate JSON column -> normalized child table                 */
  /* ---------------------------------------------------------------------- */

  private static function scenarioJsonToChildTableMigration(): void {
    $wpdb = Db::wpdb();

    // Example: kimaai_link_suggestions has outgoing_internal_ids JSON.
    // You want normalized rows in kimaai_link_suggestion_links:
    // (id, object_id, link_type, target_id, created_at)

    $parent = Db::tableName('kimaai_link_suggestions');
    if (!Db::tableExists($parent)) {
      return;
    }

    $child = Db::tableName('kimaai_link_suggestion_links');
    if (!Db::tableExists($child)) {
      $charset = $wpdb->get_charset_collate();
      // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared
      $wpdb->query("
        CREATE TABLE {$child} (
          id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
          object_id BIGINT(20) UNSIGNED NOT NULL,
          link_type VARCHAR(32) NOT NULL, -- incoming|outgoing|external
          target_id BIGINT(20) UNSIGNED NULL,
          target_url TEXT NULL,
          created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          PRIMARY KEY (id),
          KEY idx_object (object_id),
          KEY idx_type (link_type),
          KEY idx_target (target_id)
        ) {$charset} ENGINE=InnoDB;
      ");
    }

    // Backfill child table from parent JSON in chunks.
    $progress_key = self::opt('json_child_last_id');
    $last_id = (int) get_option($progress_key, 0);

    self::runChunked(function () use ($wpdb, $parent, $child, $progress_key, &$last_id): bool {
      // Read parent rows.
      // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
      $rows = $wpdb->get_results(
        $wpdb->prepare(
          "SELECT id, object_id, outgoing_internal_ids, incoming_internal_ids
             FROM {$parent}
            WHERE id > %d
            ORDER BY id ASC
            LIMIT %d",
          $last_id,
          self::CHUNK_SIZE
        ),
        ARRAY_A
      );

      if (!$rows) {
        delete_option($progress_key);
        return false;
      }

      foreach ($rows as $r) {
        $last_id = max($last_id, (int) $r['id']);
        $object_id = (int) ($r['object_id'] ?? 0);
        if ($object_id <= 0) {
          continue;
        }

        $outgoing = json_decode((string) ($r['outgoing_internal_ids'] ?? ''), true);
        $incoming = json_decode((string) ($r['incoming_internal_ids'] ?? ''), true);

        $outgoing = is_array($outgoing) ? array_values(array_unique(array_map('intval', $outgoing))) : [];
        $incoming = is_array($incoming) ? array_values(array_unique(array_map('intval', $incoming))) : [];

        // Insert child rows (ignore duplicates by checking existence).
        foreach ($outgoing as $tid) {
          if ($tid <= 0) {
            continue;
          }
          // naive existence check; for big sites add a UNIQUE(object_id,link_type,target_id) and use INSERT IGNORE.
          // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
          $exists = (int) $wpdb->get_var(
            $wpdb->prepare(
              "SELECT 1 FROM {$child} WHERE object_id=%d AND link_type=%s AND target_id=%d LIMIT 1",
              $object_id,
              'outgoing',
              $tid
            )
          );
          if (!$exists) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->insert($child, [
              'object_id' => $object_id,
              'link_type' => 'outgoing',
              'target_id' => $tid,
              'target_url' => null,
              'created_at' => current_time('mysql'),
            ], ['%d', '%s', '%d', '%s', '%s']);
          }
        }

        foreach ($incoming as $tid) {
          if ($tid <= 0) {
            continue;
          }
          // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
          $exists = (int) $wpdb->get_var(
            $wpdb->prepare(
              "SELECT 1 FROM {$child} WHERE object_id=%d AND link_type=%s AND target_id=%d LIMIT 1",
              $object_id,
              'incoming',
              $tid
            )
          );
          if (!$exists) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->insert($child, [
              'object_id' => $object_id,
              'link_type' => 'incoming',
              'target_id' => $tid,
              'target_url' => null,
              'created_at' => current_time('mysql'),
            ], ['%d', '%s', '%d', '%s', '%s']);
          }
        }
      }

      update_option($progress_key, $last_id, false);
      return true;
    });

    // In real life: keep JSON column for one release, then remove from writes, then optionally drop later.
  }

  /*
  ---------------------------------------------------------------------- */
  /*
  Scenario 9: Merge duplicates / cleanup                                   */
  /* ---------------------------------------------------------------------- */

  private static function scenarioMergeDuplicatesCleanup(): void {
    $wpdb = Db::wpdb();
    $table = Db::tableName('kimaai_chat_feedback');

    if (!Db::tableExists($table)) {
      return;
    }

    // Example: normalize rating values (e.g., any >0 becomes 1, any <0 becomes -1)
    $done_key = self::opt('normalize_rating_done');
    if (get_option($done_key)) {
      return;
    }

    // This is a single statement, usually safe:
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared
    $wpdb->query("
      UPDATE {$table}
         SET rating = CASE
           WHEN rating > 0 THEN 1
           WHEN rating < 0 THEN -1
           ELSE 0
         END
       WHERE rating NOT IN (-1, 0, 1)
    ");

    update_option($done_key, 1, false);
  }

  /*
  ---------------------------------------------------------------------- */
  /*
  Scenario 10: Drop column (dangerous)                                     */
  /* ---------------------------------------------------------------------- */

  private static function scenarioDangerDropColumn(): void {
    $wpdb = Db::wpdb();
    $table = Db::tableName('kimaai_chat_feedback');

    if (!Db::tableExists($table)) {
      return;
    }

    // Example: drop old column after you have fully cut over in app code.
    $col = 'comment';
    if (!Db::columnExists($table, $col)) {
      return;
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared
    $wpdb->query("ALTER TABLE {$table} DROP COLUMN {$col}");
  }

  /*
  ---------------------------------------------------------------------- */
  /*
  Scenario 11: Drop table (dangerous)                                      */
  /* ---------------------------------------------------------------------- */

  private static function scenarioDangerDropTable(): void {
    $wpdb = Db::wpdb();
    $table = Db::tableName('kimaai_example_new_table');

    if (!Db::tableExists($table)) {
      return;
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared
    $wpdb->query("DROP TABLE {$table}");
  }
}
