-- Allow durable monthly publication artifacts in the existing private OSS.
-- Historical local artifacts remain valid and readable.

BEGIN;

DO $$
BEGIN
    IF current_database() <> 'booking_test' THEN
        RAISE EXCEPTION
            'ARR monthly OSS artifact migration is allowed only in booking_test';
    END IF;
    IF to_regclass('reporting.monthly_runs') IS NULL
       OR to_regclass('ingestion.artifacts') IS NULL
       OR to_regprocedure('reporting.validate_monthly_run_publication()') IS NULL THEN
        RAISE EXCEPTION
            'ARR migration 012 must be applied first';
    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 COALESCE(workbook_provider, '') NOT IN ('oss', 's3', 'local')
       OR result_kind IS DISTINCT FROM 'result_json'
       OR COALESCE(result_provider, '') NOT IN ('oss', 's3', 'local') THEN
        RAISE EXCEPTION
            'published monthly run must reference OSS or controlled local monthly 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;
$$;

COMMENT ON FUNCTION reporting.validate_monthly_run_publication() IS
  'Published monthly artifacts may be private OSS/S3 or legacy controlled-local objects.';

COMMIT;