-- DESTRUCTIVE rollback draft for migration 002.
-- Do not execute while monthly_backfill rows or dependent report data exist.
-- Target database: booking_test.

BEGIN;

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM finance.daily_versions
        WHERE ingestion_mode = 'monthly_backfill'
    ) THEN
        RAISE EXCEPTION
            'remove or migrate monthly_backfill data before rolling back migration 002';
    END IF;
END;
$$;

DROP VIEW finance.v_channel_booking_enriched;

CREATE OR REPLACE VIEW finance.v_company_report_source AS
SELECT
    current_version.business_date,
    records.id AS daily_record_id,
    records.channel_key,
    records.company_key,
    records.company_name,
    records.block_code,
    records.group_code_key,
    records.res_comment,
    records.room_category_label AS type_of_room,
    records.no_of_rooms AS actual_room_quantity,
    records.arrival,
    records.departure,
    records.nights,
    records.real_price,
    records.total_price,
    records.booking_parse_version_id,
    records.booking_match_status
FROM finance.current_daily_versions AS current_version
JOIN finance.daily_records AS records
  ON records.daily_version_id = current_version.daily_version_id
WHERE records.outcome = 'retained';

DROP TABLE finance.report_booking_sources;

ALTER TABLE finance.report_versions
    DROP COLUMN result_schema_version,
    DROP COLUMN rule_set_sha256,
    DROP COLUMN processor_version,
    DROP COLUMN as_of_date;

DROP INDEX finance.daily_records_booking_source_status_idx;
DROP INDEX finance.daily_records_booking_source_row_idx;
DROP INDEX finance.daily_records_source_coordinate_unique;

ALTER TABLE finance.daily_records
    DROP COLUMN booking_source_match_status,
    DROP COLUMN booking_source_row_id,
    DROP COLUMN source_row_no,
    DROP COLUMN source_worksheet;

DROP INDEX finance.daily_versions_source_date_mode_unique;

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 daily_report_file_id IS NOT NULL
            AND result_json_file_id IS NOT NULL
            AND failure_code IS NULL
        )
    );

ALTER TABLE finance.daily_versions
    DROP COLUMN ingestion_mode;

COMMENT ON VIEW finance.v_company_report_source IS
  'Privacy-minimized daily facts used to build company ten-day room reports.';

COMMIT;