import { MigrationInterface, QueryRunner } from 'typeorm';

/**
 * Creates the 4 tables that power the Staff Document Portal:
 *   - documents
 *   - document_role_assignments
 *   - document_otps
 *   - document_access_log
 *
 * ⚠ IMPORTANT — TYPE ALIGNMENT
 * The draft schema you supplied used BIGINT UNSIGNED for uploaded_by / user_id
 * and INT UNSIGNED for role_id. But in scheme_dbs:
 *     users.id  → INT
 *     roles.id  → INT
 * so the original FKs would fail with "incompatible column types".
 * This migration uses INT for uploaded_by / user_id / role_id / assigned_by
 * to match, while keeping BIGINT UNSIGNED for the internal PKs.
 *
 * Run with: npm run typeorm migration:run
 */
export class CreateDocumentPortalTables1724100000000
  implements MigrationInterface
{
  name = 'CreateDocumentPortalTables1724100000000';

  public async up(queryRunner: QueryRunner): Promise<void> {
    // ─── 1. documents ────────────────────────────────────────────────
    await queryRunner.query(`
      CREATE TABLE \`documents\` (
        \`id\`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        \`title\`          VARCHAR(200)    NOT NULL,
        \`category\`       VARCHAR(100)    NOT NULL,
        \`description\`    TEXT            NULL,
        \`file_path\`      VARCHAR(500)    NOT NULL,
        \`file_name\`      VARCHAR(255)    NOT NULL,
        \`file_size\`      BIGINT UNSIGNED NULL,
        \`mime_type\`      VARCHAR(120)    NULL,
        \`password_hash\`  VARCHAR(255)    NULL,
        \`require_otp\`    TINYINT(1)      NOT NULL DEFAULT 1,
        \`allow_download\` TINYINT(1)      NOT NULL DEFAULT 1,
        \`expiry_date\`    DATE            NULL,
        \`status\`         ENUM('active','archived') NOT NULL DEFAULT 'active',
        \`uploaded_by\`    INT             NOT NULL,
        \`created_at\`     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
        \`updated_at\`     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
                                             ON UPDATE CURRENT_TIMESTAMP,

        PRIMARY KEY (\`id\`),
        INDEX \`IDX_documents_category\` (\`category\`),
        INDEX \`IDX_documents_status\`   (\`status\`),
        INDEX \`IDX_documents_uploader\` (\`uploaded_by\`),
        CONSTRAINT \`FK_documents_uploader\`
          FOREIGN KEY (\`uploaded_by\`) REFERENCES \`users\`(\`id\`)
          ON DELETE RESTRICT ON UPDATE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    `);

    // ─── 2. document_role_assignments ────────────────────────────────
    await queryRunner.query(`
      CREATE TABLE \`document_role_assignments\` (
        \`id\`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        \`document_id\` BIGINT UNSIGNED NOT NULL,
        \`role_id\`     INT             NOT NULL,
        \`role_name\`   VARCHAR(80)     NULL,
        \`assigned_by\` INT             NOT NULL,
        \`created_at\`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

        PRIMARY KEY (\`id\`),
        UNIQUE KEY \`uniq_doc_role\` (\`document_id\`, \`role_id\`),
        INDEX \`IDX_dra_role\` (\`role_id\`),
        CONSTRAINT \`FK_dra_document\`
          FOREIGN KEY (\`document_id\`) REFERENCES \`documents\`(\`id\`)
          ON DELETE CASCADE ON UPDATE CASCADE,
        CONSTRAINT \`FK_dra_role\`
          FOREIGN KEY (\`role_id\`) REFERENCES \`roles\`(\`id\`)
          ON DELETE RESTRICT ON UPDATE CASCADE,
        CONSTRAINT \`FK_dra_assigner\`
          FOREIGN KEY (\`assigned_by\`) REFERENCES \`users\`(\`id\`)
          ON DELETE RESTRICT ON UPDATE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    `);

    // ─── 3. document_otps ────────────────────────────────────────────
    await queryRunner.query(`
      CREATE TABLE \`document_otps\` (
        \`id\`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        \`document_id\` BIGINT UNSIGNED NOT NULL,
        \`user_id\`     INT             NOT NULL,
        \`otp_hash\`    VARCHAR(255)    NOT NULL,
        \`expires_at\`  DATETIME        NOT NULL,
        \`consumed\`    TINYINT(1)      NOT NULL DEFAULT 0,
        \`attempts\`    INT             NOT NULL DEFAULT 0,
        \`created_at\`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

        PRIMARY KEY (\`id\`),
        INDEX \`IDX_otp_doc_user\` (\`document_id\`, \`user_id\`),
        CONSTRAINT \`FK_otp_document\`
          FOREIGN KEY (\`document_id\`) REFERENCES \`documents\`(\`id\`)
          ON DELETE CASCADE ON UPDATE CASCADE,
        CONSTRAINT \`FK_otp_user\`
          FOREIGN KEY (\`user_id\`) REFERENCES \`users\`(\`id\`)
          ON DELETE CASCADE ON UPDATE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    `);

    // ─── 4. document_access_log ──────────────────────────────────────
    await queryRunner.query(`
      CREATE TABLE \`document_access_log\` (
        \`id\`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        \`document_id\` BIGINT UNSIGNED NOT NULL,
        \`user_id\`     INT             NOT NULL,
        \`user_name\`   VARCHAR(150)    NULL,
        \`role_name\`   VARCHAR(80)     NULL,
        \`action\`      ENUM('viewed','downloaded','otp_sent','otp_failed','password_failed','denied') NOT NULL,
        \`ip_address\`  VARCHAR(64)     NULL,
        \`user_agent\`  VARCHAR(300)    NULL,
        \`created_at\`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,

        PRIMARY KEY (\`id\`),
        INDEX \`IDX_log_doc\`    (\`document_id\`),
        INDEX \`IDX_log_user\`   (\`user_id\`),
        INDEX \`IDX_log_action\` (\`action\`),
        CONSTRAINT \`FK_log_document\`
          FOREIGN KEY (\`document_id\`) REFERENCES \`documents\`(\`id\`)
          ON DELETE CASCADE ON UPDATE CASCADE
        -- NOTE: intentionally NO FK on user_id here.
        -- If a user is ever removed, we still want the historical log entry
        -- to survive (it already has user_name / role_name snapshots).
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    `);

    // ─── 5. Register the module row so it shows up in your generic
    //     module-driven UI (button_config / column_config). Same shape
    //     the other modules use — safe to remove if you register modules
    //     manually.
    await queryRunner.query(
      `INSERT INTO \`modules\` (\`name\`, \`slug\`, \`description\`, \`api_prefix\`, \`is_active\`, \`created_at\`, \`updated_at\`)
       VALUES ('Documents', 'documents', 'Staff Document Portal — role-based, OTP-secured document sharing with full audit trail', 'documents', 1, NOW(6), NOW(6))
       ON DUPLICATE KEY UPDATE \`updated_at\` = NOW(6)`,
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    // Reverse order — FK-safe.
    await queryRunner.query(`DELETE FROM \`modules\` WHERE \`slug\` = 'documents'`);
    await queryRunner.query(`DROP TABLE IF EXISTS \`document_access_log\``);
    await queryRunner.query(`DROP TABLE IF EXISTS \`document_otps\``);
    await queryRunner.query(`DROP TABLE IF EXISTS \`document_role_assignments\``);
    await queryRunner.query(`DROP TABLE IF EXISTS \`documents\``);
  }
}