-- Restore the 012 local-only publication guard.

BEGIN;

DO $$
BEGIN
    IF current_database() <> 'booking_test' THEN
        RAISE EXCEPTION
            'ARR monthly OSS artifact rollback is allowed only in booking_test';
    END IF;
    IF to_regclass('reporting.monthly_runs') IS NULL
       OR to_regprocedure('reporting.validate_monthly_run_publication()') IS NULL THEN
        RAISE EXCEPTION
            'ARR migration 016 is not applied';
    END IF;
    IF EXISTS (
        SELECT 1
        FROM reporting.monthly_runs AS run
        JOIN ingestion.artifacts AS artifact
          ON artifact.id IN (run.workbook_artifact_id, run.result_artifact_id)
        WHERE run.report_status IN ('active', 'superseded')
          AND artifact.storage_provider <> 'local'
    ) THEN
        RAISE EXCEPTION
            'ARR monthly OSS artifact rollback refused while a published OSS artifact exists';
    END IF;
END;
$$;

CREATE OR REPLACE FUNCTION reporting.validate_monthly_run_publication()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    workbook_kind text;
    workbook_provider text;
    result_kind text;
    result_provider text;
    manifest_count integer;
    manifest_rows bigint;
    manifest_min integer;
    manifest_max integer;
    lineage_count integer;
BEGIN
    IF NEW.report_status NOT IN ('active', 'superseded') THEN
        RETURN NEW;
    END IF;

    SELECT artifact_kind, storage_provider
    INTO workbook_kind, workbook_provider
    FROM ingestion.artifacts
    WHERE id = NEW.workbook_artifact_id;

    SELECT artifact_kind, storage_provider
    INTO result_kind, result_provider
    FROM ingestion.artifacts
    WHERE id = NEW.result_artifact_id;

    IF workbook_kind IS DISTINCT FROM 'monthly_xlsx'
       OR workbook_provider IS DISTINCT FROM 'local'
       OR result_kind IS DISTINCT FROM 'result_json'
       OR result_provider IS DISTINCT FROM 'local' THEN
        RAISE EXCEPTION
            'published monthly run must reference controlled local monthly_xlsx and result_json artifacts';
    END IF;

    SELECT count(*), COALESCE(sum(row_count), 0), min(worksheet_order), max(worksheet_order)
    INTO manifest_count, manifest_rows, manifest_min, manifest_max
    FROM reporting.monthly_channel_manifest
    WHERE report_id = NEW.id;

    IF manifest_count <> NEW.channel_count
       OR manifest_rows <> NEW.row_count
       OR manifest_min <> 1
       OR manifest_max <> manifest_count THEN
        RAISE EXCEPTION
            'published monthly run requires a continuous reconciled channel manifest';
    END IF;

    SELECT count(*)
    INTO lineage_count
    FROM reporting.monthly_run_daily_versions
    WHERE report_id = NEW.id;

    IF lineage_count < 1 THEN
        RAISE EXCEPTION
            'published monthly run requires daily-version lineage';
    END IF;

    RETURN NEW;
END;
$$;

COMMIT;