import { MigrationInterface, QueryRunner } from 'typeorm';

/**
 * Creates the audit_requests table and adds the audit_request_id column
 * to the existing audit_schedule_rows table for FK linkage.
 *
 * Run with: npm run typeorm migration:run
 */
export class CreateAuditRequestsTable1715800000000 implements MigrationInterface {
  name = 'CreateAuditRequestsTable1715800000000';

  public async up(queryRunner: QueryRunner): Promise<void> {
    // ─── 1. Create the audit_requests table ───────────────────────────
    await queryRunner.query(`
      CREATE TABLE \`audit_requests\` (
        \`id\` BIGINT NOT NULL AUTO_INCREMENT,

        -- Client & auditee
        \`company_id\` BIGINT NOT NULL,
        \`auditee_name\` VARCHAR(150) NOT NULL,
        \`auditee_contact\` VARCHAR(30) NOT NULL,
        \`auditee_email\` VARCHAR(150) NOT NULL,

        -- Audit scope
        \`standard_ids\` JSON NOT NULL,
        \`certification_type\` ENUM('INITIAL', 'SURVEILLANCE_1', 'SURVEILLANCE_2', 'RECERTIFICATION', 'SURVEILLANCE_RECERT') NOT NULL,
        \`accreditation\` VARCHAR(50) NOT NULL,

        -- Scheduling preference
        \`proposed_date\` DATE NOT NULL,
        \`proposed_time\` TIME NOT NULL,
        \`location\` VARCHAR(255) NOT NULL,
        \`mode\` ENUM('ONLINE', 'ONSITE', 'HYBRID') NOT NULL,
        \`marketing_remarks\` TEXT NULL,

        -- Coordinator-set fields
        \`coordinator_remarks\` TEXT NULL,
        \`status\` ENUM('DRAFT', 'SUBMITTED', 'UNDER_REVIEW', 'SCHEDULED', 'REJECTED', 'CANCELLED', 'COMPLETED') NOT NULL DEFAULT 'SUBMITTED',
        \`reviewed_by_id\` BIGINT NULL,
        \`reviewed_at\` TIMESTAMP NULL,
        \`scheduled_at\` TIMESTAMP NULL,
        \`rejection_reason\` TEXT NULL,

        -- Link back to the actual scheduled audit row
        \`audit_schedule_row_id\` BIGINT NULL,

        -- Audit trail
        \`requested_by_id\` BIGINT 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_audit_requests_status\` (\`status\`),
        INDEX \`IDX_audit_requests_company\` (\`company_id\`),
        INDEX \`IDX_audit_requests_requested_by\` (\`requested_by_id\`),
        INDEX \`IDX_audit_requests_proposed_date\` (\`proposed_date\`),
        INDEX \`IDX_audit_requests_schedule_row\` (\`audit_schedule_row_id\`)
      ) ENGINE=InnoDB
    `);

    // ─── 2. Add foreign keys on audit_requests ────────────────────────
    await queryRunner.query(`
      ALTER TABLE \`audit_requests\`
      ADD CONSTRAINT \`FK_audit_requests_company\`
        FOREIGN KEY (\`company_id\`) REFERENCES \`companies\`(\`id\`)
        ON DELETE RESTRICT ON UPDATE CASCADE
    `);

    await queryRunner.query(`
      ALTER TABLE \`audit_requests\`
      ADD CONSTRAINT \`FK_audit_requests_requested_by\`
        FOREIGN KEY (\`requested_by_id\`) REFERENCES \`users\`(\`id\`)
        ON DELETE RESTRICT ON UPDATE CASCADE
    `);

    await queryRunner.query(`
      ALTER TABLE \`audit_requests\`
      ADD CONSTRAINT \`FK_audit_requests_reviewed_by\`
        FOREIGN KEY (\`reviewed_by_id\`) REFERENCES \`users\`(\`id\`)
        ON DELETE SET NULL ON UPDATE CASCADE
    `);

    // ─── 3. Add audit_request_id column to existing audit_schedule_rows
    // Nullable — legacy rows created via the old email-based flow won't have it.
    await queryRunner.query(`
      ALTER TABLE \`audit_schedule_rows\`
      ADD COLUMN \`audit_request_id\` BIGINT NULL AFTER \`notes\`
    `);

    await queryRunner.query(`
      ALTER TABLE \`audit_schedule_rows\`
      ADD INDEX \`IDX_audit_schedule_rows_request\` (\`audit_request_id\`)
    `);

    await queryRunner.query(`
      ALTER TABLE \`audit_schedule_rows\`
      ADD CONSTRAINT \`FK_audit_schedule_rows_request\`
        FOREIGN KEY (\`audit_request_id\`) REFERENCES \`audit_requests\`(\`id\`)
        ON DELETE SET NULL ON UPDATE CASCADE
    `);

    // ─── 4. Add reverse FK on audit_requests.audit_schedule_row_id ────
    // Done last so both tables exist with both columns first.
    await queryRunner.query(`
      ALTER TABLE \`audit_requests\`
      ADD CONSTRAINT \`FK_audit_requests_schedule_row\`
        FOREIGN KEY (\`audit_schedule_row_id\`) REFERENCES \`audit_schedule_rows\`(\`id\`)
        ON DELETE SET NULL ON UPDATE CASCADE
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    // Drop in reverse order

    // 1. Remove reverse FK + column from audit_schedule_rows
    await queryRunner.query(`
      ALTER TABLE \`audit_schedule_rows\`
      DROP FOREIGN KEY \`FK_audit_schedule_rows_request\`
    `);
    await queryRunner.query(`
      ALTER TABLE \`audit_schedule_rows\`
      DROP INDEX \`IDX_audit_schedule_rows_request\`
    `);
    await queryRunner.query(`
      ALTER TABLE \`audit_schedule_rows\`
      DROP COLUMN \`audit_request_id\`
    `);

    // 2. Drop the audit_requests table (FKs drop with it)
    await queryRunner.query(`DROP TABLE \`audit_requests\``);
  }
}