-- ARR XML processing delivery, audit, and deterministic ingestion support
-- Structured-result contract: 3.0; processor allowlist is enforced by ARR code.
-- PostgreSQL 15+
-- Target database: booking_test (connect to that database before running).
-- This migration is additive; do not edit applied migrations 001-004 in place.

BEGIN;

ALTER TABLE booking.file_objects
    DROP CONSTRAINT file_objects_file_kind_check;

ALTER TABLE booking.file_objects
    ADD CONSTRAINT file_objects_file_kind_check CHECK (file_kind IN (
        'booking_excel',
        'opera_xml',
        'daily_xlsx',
        'monthly_xlsx',
        'company_ten_day_xlsx',
        'exception_xlsx',
        'result_json',
        'structured_result_json'
    ));

CREATE TABLE finance.processing_jobs (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    job_key text NOT NULL UNIQUE CHECK (
        job_key ~ '^[A-Za-z0-9][A-Za-z0-9._:-]{0,127}$'
    ),
    source_file_id bigint NOT NULL
        REFERENCES booking.file_objects(id),
    job_status text NOT NULL CHECK (job_status IN (
        'uploaded',
        'queued',
        'running',
        'delivered',
        'validating',
        'succeeded',
        'failed',
        'cancelled'
    )),
    requested_processor_version text NOT NULL CHECK (
        btrim(requested_processor_version) <> ''
    ),
    requested_rule_set_sha256 character(64) NOT NULL CHECK (
        requested_rule_set_sha256 ~ '^[0-9a-f]{64}$'
    ),
    current_attempt_no integer NOT NULL DEFAULT 0 CHECK (
        current_attempt_no >= 0
    ),
    business_date date,
    failure_code text,
    failure_message text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    finished_at timestamptz,
    CONSTRAINT processing_jobs_terminal_shape CHECK (
        (
            job_status = 'succeeded'
            AND business_date IS NOT NULL
            AND failure_code IS NULL
            AND finished_at IS NOT NULL
        )
        OR (
            job_status = 'failed'
            AND failure_code IS NOT NULL
            AND finished_at IS NOT NULL
        )
        OR (
            job_status = 'cancelled'
            AND finished_at IS NOT NULL
        )
        OR job_status NOT IN ('succeeded', 'failed', 'cancelled')
    )
);

COMMENT ON TABLE finance.processing_jobs IS
  'ARR-owned XML processing jobs. SuperAgent may execute attempts but never writes this table or Finance facts.';

CREATE TABLE finance.processing_attempts (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    processing_job_id bigint NOT NULL
        REFERENCES finance.processing_jobs(id),
    attempt_no integer NOT NULL CHECK (attempt_no > 0),
    attempt_status text NOT NULL CHECK (attempt_status IN (
        'queued',
        'dispatched',
        'running',
        'delivered',
        'succeeded',
        'failed',
        'cancelled'
    )),
    idempotency_key character(64) NOT NULL UNIQUE CHECK (
        idempotency_key ~ '^[0-9a-f]{64}$'
    ),
    remote_run_id text,
    failure_code text,
    failure_message text,
    created_at timestamptz NOT NULL DEFAULT now(),
    started_at timestamptz,
    finished_at timestamptz,
    CONSTRAINT processing_attempts_job_number_unique
        UNIQUE (processing_job_id, attempt_no),
    CONSTRAINT processing_attempts_id_job_unique
        UNIQUE (id, processing_job_id),
    CONSTRAINT processing_attempts_remote_run_unique
        UNIQUE (remote_run_id),
    CONSTRAINT processing_attempts_terminal_shape CHECK (
        (
            attempt_status = 'succeeded'
            AND failure_code IS NULL
            AND finished_at IS NOT NULL
        )
        OR (
            attempt_status = 'failed'
            AND failure_code IS NOT NULL
            AND finished_at IS NOT NULL
        )
        OR (
            attempt_status = 'cancelled'
            AND finished_at IS NOT NULL
        )
        OR attempt_status NOT IN ('succeeded', 'failed', 'cancelled')
    )
);

COMMENT ON TABLE finance.processing_attempts IS
  'Recoverable dispatch attempts correlated to one ARR processing job and an optional remote SuperAgent run.';

CREATE TABLE finance.processing_callbacks (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    callback_key text NOT NULL UNIQUE CHECK (
        callback_key ~ '^[A-Za-z0-9][A-Za-z0-9._:-]{0,127}$'
    ),
    processing_job_id bigint NOT NULL,
    attempt_id bigint NOT NULL,
    envelope_sha256 character(64) NOT NULL CHECK (
        envelope_sha256 ~ '^[0-9a-f]{64}$'
    ),
    envelope_json jsonb NOT NULL,
    callback_status text NOT NULL CHECK (callback_status IN (
        'received',
        'validating',
        'committed',
        'recorded_failure',
        'rejected'
    )),
    result_status text NOT NULL CHECK (result_status IN (
        'success', 'failed'
    )),
    processor_version text NOT NULL CHECK (btrim(processor_version) <> ''),
    rule_set_sha256 character(64) NOT NULL CHECK (
        rule_set_sha256 ~ '^[0-9a-f]{64}$'
    ),
    result_schema_version text NOT NULL CHECK (
        result_schema_version = '3.0'
    ),
    business_date date,
    source_file_id bigint REFERENCES booking.file_objects(id),
    daily_report_file_id bigint REFERENCES booking.file_objects(id),
    result_json_file_id bigint REFERENCES booking.file_objects(id),
    structured_result_file_id bigint REFERENCES booking.file_objects(id),
    exception_report_file_id bigint REFERENCES booking.file_objects(id),
    daily_version_id bigint REFERENCES finance.daily_versions(id),
    failure_code text,
    failure_message text,
    received_at timestamptz NOT NULL DEFAULT now(),
    validated_at timestamptz,
    committed_at timestamptz,
    CONSTRAINT processing_callbacks_attempt_job_fk
        FOREIGN KEY (attempt_id, processing_job_id)
        REFERENCES finance.processing_attempts(id, processing_job_id),
    CONSTRAINT processing_callbacks_attempt_envelope_unique
        UNIQUE (attempt_id, envelope_sha256),
    CONSTRAINT processing_callbacks_terminal_shape CHECK (
        (
            callback_status = 'committed'
            AND result_status = 'success'
            AND business_date IS NOT NULL
            AND source_file_id IS NOT NULL
            AND daily_report_file_id IS NOT NULL
            AND result_json_file_id IS NOT NULL
            AND structured_result_file_id IS NOT NULL
            AND exception_report_file_id IS NULL
            AND daily_version_id IS NOT NULL
            AND failure_code IS NULL
            AND validated_at IS NOT NULL
            AND committed_at IS NOT NULL
        )
        OR (
            callback_status = 'recorded_failure'
            AND result_status = 'failed'
            AND source_file_id IS NOT NULL
            AND daily_report_file_id IS NULL
            AND result_json_file_id IS NOT NULL
            AND structured_result_file_id IS NOT NULL
            AND exception_report_file_id IS NOT NULL
            AND daily_version_id IS NULL
            AND failure_code IS NOT NULL
            AND validated_at IS NOT NULL
            AND committed_at IS NOT NULL
        )
        OR (
            callback_status = 'rejected'
            AND daily_version_id IS NULL
            AND failure_code IS NOT NULL
            AND committed_at IS NULL
        )
        OR callback_status IN ('received', 'validating')
    )
);

COMMENT ON TABLE finance.processing_callbacks IS
  'Idempotent ARR receipt and validation record for one structured SuperAgent delivery; envelope JSON contains object identities, not XML bytes.';

CREATE TABLE finance.outbox_events (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_key text NOT NULL UNIQUE CHECK (btrim(event_key) <> ''),
    aggregate_type text NOT NULL CHECK (aggregate_type IN (
        'processing_job', 'daily_version', 'report_version'
    )),
    aggregate_id bigint NOT NULL CHECK (aggregate_id > 0),
    event_type text NOT NULL CHECK (btrim(event_type) <> ''),
    payload jsonb NOT NULL,
    publish_status text NOT NULL DEFAULT 'pending' CHECK (
        publish_status IN ('pending', 'publishing', 'published', 'dead')
    ),
    publish_attempts integer NOT NULL DEFAULT 0 CHECK (
        publish_attempts >= 0
    ),
    available_at timestamptz NOT NULL DEFAULT now(),
    created_at timestamptz NOT NULL DEFAULT now(),
    published_at timestamptz,
    last_error_code text,
    CONSTRAINT outbox_events_published_shape CHECK (
        (publish_status = 'published' AND published_at IS NOT NULL)
        OR publish_status <> 'published'
    )
);

COMMENT ON TABLE finance.outbox_events IS
  'Privacy-minimized transactional outbox; payloads must contain opaque IDs, dates, counts, and artifact identities only.';

ALTER TABLE finance.daily_versions
    ADD COLUMN structured_result_file_id bigint
        REFERENCES booking.file_objects(id),
    ADD COLUMN processing_job_id bigint
        REFERENCES finance.processing_jobs(id);

DROP INDEX finance.daily_versions_source_date_mode_unique;

CREATE UNIQUE INDEX daily_versions_source_date_rule_unique
    ON finance.daily_versions (
        source_file_id,
        business_date,
        ingestion_mode,
        processor_version,
        COALESCE(rule_set_sha256, repeat('0', 64)::character(64))
    );

CREATE UNIQUE INDEX daily_versions_processing_job_unique
    ON finance.daily_versions (processing_job_id)
    WHERE processing_job_id IS NOT NULL;

ALTER TABLE finance.daily_versions
    DROP CONSTRAINT daily_version_active_shape;

ALTER TABLE finance.daily_versions
    ADD CONSTRAINT daily_version_active_shape CHECK (
        version_status <> 'active'
        OR (
            business_date IS NOT NULL
            AND validated_at IS NOT NULL
            AND activated_at IS NOT NULL
            AND failure_code IS NULL
            AND (
                (
                    ingestion_mode = 'opera_xml'
                    AND daily_report_file_id IS NOT NULL
                    AND result_json_file_id IS NOT NULL
                    AND structured_result_file_id IS NOT NULL
                    AND result_schema_version = '3.0'
                )
                OR ingestion_mode = 'monthly_backfill'
            )
        )
    );

COMMENT ON COLUMN finance.daily_versions.structured_result_file_id IS
  'Original validated structured-result.json v3 object used by ARR for this immutable Opera daily version.';

COMMENT ON COLUMN finance.daily_versions.processing_job_id IS
  'ARR processing job that committed this Opera daily version; null for approved historical backfills.';

-- Successful v3 batches persist every XML source row. Rows excluded before full
-- business validation may legitimately contain otherwise-invalid optional
-- values, so business-value checks apply only to retained facts.
ALTER TABLE finance.daily_records
    DROP CONSTRAINT daily_records_people_valid,
    DROP CONSTRAINT daily_records_room_count_valid,
    DROP CONSTRAINT daily_records_dates_and_nights_valid,
    DROP CONSTRAINT daily_records_total_valid,
    DROP CONSTRAINT daily_records_kb_valid;

ALTER TABLE finance.daily_records
    ADD CONSTRAINT daily_records_people_valid CHECK (
        outcome <> 'retained'
        OR (
            (adults IS NULL OR adults >= 0)
            AND (children IS NULL OR children >= 0)
        )
    ),
    ADD CONSTRAINT daily_records_room_count_valid CHECK (
        outcome <> 'retained'
        OR no_of_rooms IS NULL
        OR no_of_rooms > 0
    ),
    ADD CONSTRAINT daily_records_dates_and_nights_valid CHECK (
        outcome <> 'retained'
        OR arrival IS NULL
        OR departure IS NULL
        OR nights IS NULL
        OR (
            departure >= arrival
            AND nights = departure - arrival
        )
    ),
    ADD CONSTRAINT daily_records_total_valid CHECK (
        outcome <> 'retained'
        OR real_price IS NULL
        OR no_of_rooms IS NULL
        OR nights IS NULL
        OR total_price IS NULL
        OR total_price = real_price * no_of_rooms * nights
    ),
    ADD CONSTRAINT daily_records_kb_valid CHECK (
        outcome <> 'retained'
        OR kb_amount IS NULL
        OR no_of_rooms IS NULL
        OR kb_amount = no_of_rooms * 100
    );

COMMENT ON CONSTRAINT daily_records_people_valid
    ON finance.daily_records IS
  'Business-value validity applies to retained facts; pre-validation exclusions remain auditable without becoming active facts.';

ALTER TABLE finance.daily_channel_metrics
    ADD COLUMN worksheet_order integer,
    ADD CONSTRAINT daily_channel_metrics_order_valid CHECK (
        worksheet_order IS NULL OR worksheet_order > 0
    );

CREATE UNIQUE INDEX daily_channel_metrics_order_unique
    ON finance.daily_channel_metrics (daily_version_id, worksheet_order)
    WHERE worksheet_order IS NOT NULL;

COMMENT ON COLUMN finance.daily_channel_metrics.worksheet_order IS
  'One-based deterministic channel order for new Opera daily versions; null on historical rows until a report-level manifest is approved.';

COMMIT;