import { MigrationInterface, QueryRunner } from 'typeorm';

/**
 * Contract–Booking separation (additive phase). Introduces a first-class
 * `freight.contracts` aggregate that owns the legal/commercial agreement (scope
 * + unit rates, no quantities) and spawns shipment `bookings` via `contract_id`.
 *
 * Purely additive: no legacy columns are dropped here. The data backfill and
 * legacy-column removal happen in a later cutover migration.
 *
 * See docs/new-doc.md §5.
 */
export class CreateContracts1822000000000 implements MigrationInterface {
  name = 'CreateContracts1822000000000';

  public async up(queryRunner: QueryRunner): Promise<void> {
    // ── contracts ───────────────────────────────────────────────────────────
    await queryRunner.query(`
      CREATE TABLE IF NOT EXISTS freight.contracts (
        id                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        reference                   VARCHAR(64) NOT NULL UNIQUE,

        company_id                  UUID,
        company_profile_id          UUID,
        is_government               BOOLEAN NOT NULL DEFAULT FALSE,
        government_institution      VARCHAR(255),

        contract_kind               VARCHAR(20) NOT NULL,
        renewal_of_id               UUID REFERENCES freight.contracts(id),
        trade_direction             VARCHAR(10) NOT NULL,
        freight_type                VARCHAR(20) NOT NULL,

        service_type_id             UUID NOT NULL,
        payment_currency            VARCHAR(5) NOT NULL,
        customs_clearing_enabled    BOOLEAN NOT NULL DEFAULT FALSE,
        customs_clearing_agent      VARCHAR(200),
        equipment_return            VARCHAR(20),

        first_mile_pickup_address   TEXT,
        first_mile_pickup_lat       NUMERIC(10,7),
        first_mile_pickup_lng       NUMERIC(10,7),
        last_mile_delivery_address  TEXT,
        last_mile_delivery_lat      NUMERIC(10,7),
        last_mile_delivery_lng      NUMERIC(10,7),

        is_hazardous                BOOLEAN NOT NULL DEFAULT FALSE,
        is_reefer                   BOOLEAN NOT NULL DEFAULT FALSE,

        estimated_shipment_date     TIMESTAMPTZ,
        contract_validity_days      INT,
        contract_valid_from         TIMESTAMPTZ,
        contract_valid_until        TIMESTAMPTZ,
        expires_at                  TIMESTAMPTZ,

        status                      VARCHAR(40) NOT NULL DEFAULT 'DRAFT',
        clearance_status            VARCHAR(40) NOT NULL DEFAULT 'NOT_APPLICABLE',
        clearance_cycle_number      INT NOT NULL DEFAULT 0,

        pricing_breakdown           JSONB,
        pricing_display_mode        VARCHAR(20) DEFAULT 'UNIT_RATES',

        contract_type               VARCHAR(20),
        contract_template_key       VARCHAR(128),
        contract_generated_at       TIMESTAMPTZ,
        contract_summary            TEXT,
        version_number              INT NOT NULL DEFAULT 1,
        financial_terms             JSONB,

        approved_by_staff_id        UUID,
        approved_by_staff_at        TIMESTAMPTZ,
        signed_by_director_id       UUID,
        signed_by_director_at       TIMESTAMPTZ,
        signed_by_ceo_id            UUID,
        signed_by_ceo_at            TIMESTAMPTZ,
        customer_signed_at          TIMESTAMPTZ,
        fully_executed_at           TIMESTAMPTZ,
        locked_at                   TIMESTAMPTZ,

        created_at                  TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at                  TIMESTAMPTZ NOT NULL DEFAULT now(),
        deleted_at                  TIMESTAMPTZ
      );
    `);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_contracts_company ON freight.contracts(company_id);`);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_contracts_status ON freight.contracts(status);`);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_contracts_kind ON freight.contracts(contract_kind);`);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_contracts_valid_until ON freight.contracts(contract_valid_until);`);

    // ── contract_routes ──────────────────────────────────────────────────────
    await queryRunner.query(`
      CREATE TABLE IF NOT EXISTS freight.contract_routes (
        id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        contract_id         UUID NOT NULL REFERENCES freight.contracts(id) ON DELETE CASCADE,
        origin_yard_id      UUID NOT NULL,
        destination_yard_id UUID NOT NULL,
        km                  NUMERIC(10,2),
        sort_order          SMALLINT NOT NULL DEFAULT 0,
        created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
        deleted_at          TIMESTAMPTZ,
        CONSTRAINT uq_contract_route UNIQUE (contract_id, origin_yard_id, destination_yard_id)
      );
    `);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_contract_routes_contract ON freight.contract_routes(contract_id);`);

    // ── contract_cargo_scope ─────────────────────────────────────────────────
    await queryRunner.query(`
      CREATE TABLE IF NOT EXISTS freight.contract_cargo_scope (
        id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        contract_id         UUID NOT NULL REFERENCES freight.contracts(id) ON DELETE CASCADE,
        container_size      VARCHAR(10),
        cargo_type_id       UUID,
        cargo_free_text     VARCHAR(200),
        created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
        deleted_at          TIMESTAMPTZ,
        CONSTRAINT uq_contract_container_size UNIQUE NULLS NOT DISTINCT (contract_id, container_size)
      );
    `);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_contract_cargo_scope_contract ON freight.contract_cargo_scope(contract_id);`);

    // ── contract_signatures ──────────────────────────────────────────────────
    await queryRunner.query(`
      CREATE TABLE IF NOT EXISTS freight.contract_signatures (
        id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        contract_id         UUID NOT NULL REFERENCES freight.contracts(id) ON DELETE CASCADE,
        role                VARCHAR(20) NOT NULL,
        signer_display_name VARCHAR(255) NOT NULL,
        signature_file_id   UUID,
        consent_text        TEXT,
        signed_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
        created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
        deleted_at          TIMESTAMPTZ
      );
    `);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_contract_signatures_contract ON freight.contract_signatures(contract_id);`);

    // ── contract_approval_steps ──────────────────────────────────────────────
    await queryRunner.query(`
      CREATE TABLE IF NOT EXISTS freight.contract_approval_steps (
        id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        contract_id         UUID NOT NULL REFERENCES freight.contracts(id) ON DELETE CASCADE,
        step_order          SMALLINT NOT NULL DEFAULT 0,
        required_role       VARCHAR(40) NOT NULL,
        blocks_role         VARCHAR(40),
        status              VARCHAR(20) NOT NULL DEFAULT 'PENDING',
        acted_by_staff_id   UUID,
        acted_at            TIMESTAMPTZ,
        note                TEXT,
        created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
        deleted_at          TIMESTAMPTZ
      );
    `);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_contract_approval_steps_contract ON freight.contract_approval_steps(contract_id);`);

    // ── contract_rate_snapshots ──────────────────────────────────────────────
    await queryRunner.query(`
      CREATE TABLE IF NOT EXISTS freight.contract_rate_snapshots (
        id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        contract_id         UUID NOT NULL REFERENCES freight.contracts(id) ON DELETE CASCADE,
        rate_id             UUID,
        rate_code           VARCHAR(64) NOT NULL,
        description         VARCHAR(255),
        unit_price          NUMERIC(14,2) NOT NULL,
        unit_of_measure     VARCHAR(32) NOT NULL,
        currency            VARCHAR(5) NOT NULL,
        container_size      VARCHAR(10),
        is_surcharge        BOOLEAN DEFAULT FALSE,
        conditional_on      VARCHAR(32),
        created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
        deleted_at          TIMESTAMPTZ
      );
    `);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_contract_rate_snapshots_contract ON freight.contract_rate_snapshots(contract_id);`);

    // ── contract_review_notes ────────────────────────────────────────────────
    await queryRunner.query(`
      CREATE TABLE IF NOT EXISTS freight.contract_review_notes (
        id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        contract_id         UUID NOT NULL REFERENCES freight.contracts(id) ON DELETE CASCADE,
        note_type           VARCHAR(40) NOT NULL,
        body                TEXT NOT NULL,
        author_role         VARCHAR(20),
        author_user_id      UUID,
        created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
        deleted_at          TIMESTAMPTZ
      );
    `);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_contract_review_notes_contract ON freight.contract_review_notes(contract_id);`);

    // ── contract_clearance_cycles ────────────────────────────────────────────
    await queryRunner.query(`
      CREATE TABLE IF NOT EXISTS freight.contract_clearance_cycles (
        id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        contract_id         UUID NOT NULL REFERENCES freight.contracts(id) ON DELETE CASCADE,
        cycle_number        INT NOT NULL,
        status              VARCHAR(40) NOT NULL DEFAULT 'AWAITING_DOCUMENTS',
        booking_id          UUID,
        started_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
        clearance_ready_at  TIMESTAMPTZ,
        completed_at        TIMESTAMPTZ,
        created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
        deleted_at          TIMESTAMPTZ,
        CONSTRAINT uq_contract_clearance_cycle UNIQUE (contract_id, cycle_number)
      );
    `);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_contract_clearance_cycles_contract ON freight.contract_clearance_cycles(contract_id);`);

    // ── contract_document_review (pre-booking clearance, Path B) ─────────────
    await queryRunner.query(`
      CREATE TABLE IF NOT EXISTS freight.contract_document_review (
        id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        contract_id         UUID NOT NULL REFERENCES freight.contracts(id) ON DELETE CASCADE,
        clearance_cycle_id  UUID REFERENCES freight.contract_clearance_cycles(id),
        setting_code        VARCHAR(128) NOT NULL,
        file_key            VARCHAR(128) NOT NULL,
        file_record_id      UUID,
        status              VARCHAR(20) NOT NULL DEFAULT 'PENDING',
        note                TEXT,
        uploaded_by_role    VARCHAR(20) NOT NULL DEFAULT 'CUSTOMER',
        reviewed_by_staff_id UUID,
        reviewed_at         TIMESTAMPTZ,
        created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
        deleted_at          TIMESTAMPTZ,
        CONSTRAINT uq_contract_document_review_doc
          UNIQUE NULLS NOT DISTINCT (contract_id, clearance_cycle_id, setting_code, file_key)
      );
    `);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_contract_doc_review_contract ON freight.contract_document_review(contract_id);`);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_contract_doc_review_status ON freight.contract_document_review(status);`);

    // ── clearance_milestones (GL tracking) ───────────────────────────────────
    await queryRunner.query(`
      CREATE TABLE IF NOT EXISTS freight.clearance_milestones (
        id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        booking_id          UUID REFERENCES freight.bookings(id) ON DELETE CASCADE,
        contract_id         UUID REFERENCES freight.contracts(id) ON DELETE CASCADE,
        clearance_cycle_id  UUID REFERENCES freight.contract_clearance_cycles(id),
        milestone_code      VARCHAR(64) NOT NULL,
        milestone_label     VARCHAR(255) NOT NULL,
        status              VARCHAR(20) NOT NULL DEFAULT 'PENDING',
        owner_region        VARCHAR(5),
        triggered_by_doc    BOOLEAN DEFAULT FALSE,
        triggered_at        TIMESTAMPTZ,
        triggered_by_user_id UUID,
        note                TEXT,
        sort_order          SMALLINT NOT NULL DEFAULT 0,
        created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
        deleted_at          TIMESTAMPTZ
      );
    `);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_clearance_milestones_booking ON freight.clearance_milestones(booking_id);`);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_clearance_milestones_contract ON freight.clearance_milestones(contract_id);`);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_clearance_milestones_region ON freight.clearance_milestones(owner_region, status);`);
    // booking-scoped and contract-cycle-scoped uniqueness for milestone codes
    await queryRunner.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS uq_clearance_milestone_booking
      ON freight.clearance_milestones(booking_id, milestone_code) WHERE booking_id IS NOT NULL;
    `);
    await queryRunner.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS uq_clearance_milestone_cycle
      ON freight.clearance_milestones(clearance_cycle_id, milestone_code) WHERE clearance_cycle_id IS NOT NULL;
    `);

    // ── booking_container_units (per-unit container detail) ──────────────────
    await queryRunner.query(`
      CREATE TABLE IF NOT EXISTS freight.booking_container_units (
        id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        booking_container_id  UUID NOT NULL REFERENCES freight.booking_container(id) ON DELETE CASCADE,
        container_number      VARCHAR(64) NOT NULL,
        seal_number           VARCHAR(64),
        vgm_tons              NUMERIC(10,3) NOT NULL,
        is_hazardous          BOOLEAN DEFAULT FALSE,
        is_reefer             BOOLEAN DEFAULT FALSE,
        sort_order            SMALLINT NOT NULL DEFAULT 0,
        created_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
        deleted_at            TIMESTAMPTZ,
        CONSTRAINT uq_booking_container_unit_number UNIQUE (booking_container_id, container_number)
      );
    `);

    // ── ALTER bookings ───────────────────────────────────────────────────────
    await queryRunner.query(`ALTER TABLE freight.bookings ADD COLUMN IF NOT EXISTS contract_id UUID REFERENCES freight.contracts(id);`);
    await queryRunner.query(`ALTER TABLE freight.bookings ADD COLUMN IF NOT EXISTS contract_route_id UUID REFERENCES freight.contract_routes(id);`);
    await queryRunner.query(`ALTER TABLE freight.bookings ADD COLUMN IF NOT EXISTS created_by_role VARCHAR(20) DEFAULT 'CUSTOMER';`);
    await queryRunner.query(`ALTER TABLE freight.bookings ADD COLUMN IF NOT EXISTS created_by_user_id UUID;`);
    await queryRunner.query(`CREATE INDEX IF NOT EXISTS idx_bookings_contract ON freight.bookings(contract_id);`);
    // One active booking per ONE_TIME contract. Postgres forbids a subquery in an
    // index predicate, so we denormalize the contract kind onto the booking and
    // predicate on that. The column is stamped at booking creation from the
    // contract; the app layer (ContractBookingService) is the primary guard and
    // this index is the backstop.
    await queryRunner.query(`ALTER TABLE freight.bookings ADD COLUMN IF NOT EXISTS contract_kind VARCHAR(20);`);
    await queryRunner.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS uq_one_active_booking_per_one_time_contract
      ON freight.bookings (contract_id)
      WHERE status NOT IN ('EXPIRED', 'CANCELLED', 'COMPLETED', 'REJECTED')
        AND contract_id IS NOT NULL
        AND contract_kind = 'ONE_TIME';
    `);

    // ── ALTER booking_container ──────────────────────────────────────────────
    await queryRunner.query(`ALTER TABLE freight.booking_container ADD COLUMN IF NOT EXISTS container_size VARCHAR(10);`);
    await queryRunner.query(`ALTER TABLE freight.booking_container ADD COLUMN IF NOT EXISTS hazardous_quantity SMALLINT DEFAULT 0;`);
    await queryRunner.query(`ALTER TABLE freight.booking_container ADD COLUMN IF NOT EXISTS reefer_quantity SMALLINT DEFAULT 0;`);

    // ── ALTER booking_document_review (denormalized contract link) ───────────
    await queryRunner.query(`ALTER TABLE freight.booking_document_review ADD COLUMN IF NOT EXISTS contract_id UUID REFERENCES freight.contracts(id);`);

    // ── Extend file_upload_fields with phased GL metadata ────────────────────
    await queryRunner.query(`ALTER TABLE freight.file_upload_fields ADD COLUMN IF NOT EXISTS phase VARCHAR(40);`);
    await queryRunner.query(`ALTER TABLE freight.file_upload_fields ADD COLUMN IF NOT EXISTS owner_region VARCHAR(5);`);
    await queryRunner.query(`ALTER TABLE freight.file_upload_fields ADD COLUMN IF NOT EXISTS trade_direction VARCHAR(10);`);
    await queryRunner.query(`ALTER TABLE freight.file_upload_fields ADD COLUMN IF NOT EXISTS triggers_milestone_code VARCHAR(64);`);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`ALTER TABLE freight.file_upload_fields DROP COLUMN IF EXISTS triggers_milestone_code;`);
    await queryRunner.query(`ALTER TABLE freight.file_upload_fields DROP COLUMN IF EXISTS trade_direction;`);
    await queryRunner.query(`ALTER TABLE freight.file_upload_fields DROP COLUMN IF EXISTS owner_region;`);
    await queryRunner.query(`ALTER TABLE freight.file_upload_fields DROP COLUMN IF EXISTS phase;`);

    await queryRunner.query(`ALTER TABLE freight.booking_document_review DROP COLUMN IF EXISTS contract_id;`);

    await queryRunner.query(`ALTER TABLE freight.booking_container DROP COLUMN IF EXISTS reefer_quantity;`);
    await queryRunner.query(`ALTER TABLE freight.booking_container DROP COLUMN IF EXISTS hazardous_quantity;`);
    await queryRunner.query(`ALTER TABLE freight.booking_container DROP COLUMN IF EXISTS container_size;`);

    await queryRunner.query(`DROP INDEX IF EXISTS freight.uq_one_active_booking_per_one_time_contract;`);
    await queryRunner.query(`DROP INDEX IF EXISTS freight.idx_bookings_contract;`);
    await queryRunner.query(`ALTER TABLE freight.bookings DROP COLUMN IF EXISTS contract_kind;`);
    await queryRunner.query(`ALTER TABLE freight.bookings DROP COLUMN IF EXISTS created_by_user_id;`);
    await queryRunner.query(`ALTER TABLE freight.bookings DROP COLUMN IF EXISTS created_by_role;`);
    await queryRunner.query(`ALTER TABLE freight.bookings DROP COLUMN IF EXISTS contract_route_id;`);
    await queryRunner.query(`ALTER TABLE freight.bookings DROP COLUMN IF EXISTS contract_id;`);

    await queryRunner.query(`DROP TABLE IF EXISTS freight.booking_container_units;`);
    await queryRunner.query(`DROP TABLE IF EXISTS freight.clearance_milestones;`);
    await queryRunner.query(`DROP TABLE IF EXISTS freight.contract_document_review;`);
    await queryRunner.query(`DROP TABLE IF EXISTS freight.contract_clearance_cycles;`);
    await queryRunner.query(`DROP TABLE IF EXISTS freight.contract_review_notes;`);
    await queryRunner.query(`DROP TABLE IF EXISTS freight.contract_rate_snapshots;`);
    await queryRunner.query(`DROP TABLE IF EXISTS freight.contract_approval_steps;`);
    await queryRunner.query(`DROP TABLE IF EXISTS freight.contract_signatures;`);
    await queryRunner.query(`DROP TABLE IF EXISTS freight.contract_cargo_scope;`);
    await queryRunner.query(`DROP TABLE IF EXISTS freight.contract_routes;`);
    await queryRunner.query(`DROP TABLE IF EXISTS freight.contracts;`);
  }
}
