-- DMS file-property and version-lifecycle database regression.
-- Prerequisite: DMS V2.3 plus upgrade_dms_file_properties_20260820.sql.
-- The entire test runs in one transaction and always rolls back its test data.

\set ON_ERROR_STOP on

SET lock_timeout = '5s';
SET statement_timeout = '60s';

BEGIN;

DO $dms_file_property_schema_assertions$
DECLARE
    required_object TEXT;
BEGIN
    FOREACH required_object IN ARRAY ARRAY[
        'public.dms_version_states',
        'public.dms_version_relations',
        'public.uni_dms_documents_document_no',
        'public.uni_dms_file_versions_document_label',
        'public.uni_dms_file_versions_document_id_id',
        'public.uni_dms_version_states_document_effective',
        'public.idx_dms_version_states_document_status',
        'public.idx_dms_version_states_pending_effective',
        'public.idx_dms_version_states_review_due'
    ] LOOP
        IF to_regclass(required_object) IS NULL THEN
            RAISE EXCEPTION 'required DMS file-property object % is missing', required_object;
        END IF;
    END LOOP;
END;
$dms_file_property_schema_assertions$;

INSERT INTO public.dms_folders (
    id, tenant_id, parent_id, name, created_by
) VALUES (
    '019d1000-0000-7000-8000-000000000001',
    'dms-file-property-test',
    NULL,
    'File property regression root',
    'dms-file-property-test'
);

INSERT INTO public.dms_permission_resources (
    id, tenant_id, resource_type, source_type, source_id, registered_by
) VALUES (
    '019d1000-0000-7000-8000-000000000001',
    'dms-file-property-test',
    'FOLDER',
    'MIGRATION',
    'file-property-folder',
    'dms-file-property-test'
);

-- Document A starts in the legacy/raw-upload state so one-time completion can be tested.
INSERT INTO public.dms_documents (
    id, tenant_id, folder_id, name, created_by
) VALUES (
    '019d1000-0000-7000-8000-000000000002',
    'dms-file-property-test',
    '019d1000-0000-7000-8000-000000000001',
    'Controlled document A',
    'dms-file-property-test'
);

INSERT INTO public.dms_permission_resources (
    id, tenant_id, resource_type, source_type, source_id, registered_by
) VALUES (
    '019d1000-0000-7000-8000-000000000002',
    'dms-file-property-test',
    'DOCUMENT',
    'MIGRATION',
    'file-property-document-a',
    'dms-file-property-test'
);

INSERT INTO public.dms_file_versions (
    id, tenant_id, document_id, version_no, file_name, created_by
) VALUES (
    '019d1000-0000-7000-8000-000000000003',
    'dms-file-property-test',
    '019d1000-0000-7000-8000-000000000002',
    1,
    'controlled-document-a-v1.docx',
    'dms-file-property-test'
);

INSERT INTO public.dms_permission_resources (
    id, tenant_id, resource_type, source_type, source_id, registered_by
) VALUES (
    '019d1000-0000-7000-8000-000000000003',
    'dms-file-property-test',
    'FILE_VERSION',
    'MIGRATION',
    'file-property-version-a1',
    'dms-file-property-test'
);

DO $dms_incomplete_metadata_rejected$
BEGIN
    BEGIN
        INSERT INTO public.dms_version_states (
            file_version_id, tenant_id, document_id, lifecycle_status,
            approved_at, approved_by, archived_at, effective_from,
            approval_source_type, approval_source_id, status_changed_by
        ) VALUES (
            '019d1000-0000-7000-8000-000000000003',
            'dms-file-property-test',
            '019d1000-0000-7000-8000-000000000002',
            'EFFECTIVE',
            CURRENT_TIMESTAMP - INTERVAL '3 days',
            'quality-user',
            CURRENT_TIMESTAMP - INTERVAL '2 days',
            CURRENT_TIMESTAMP - INTERVAL '1 day',
            'MIGRATION',
            'incomplete-state-must-fail',
            'quality-user'
        );
        RAISE EXCEPTION 'incomplete business metadata entered the lifecycle';
    EXCEPTION
        WHEN check_violation THEN NULL;
    END;
END;
$dms_incomplete_metadata_rejected$;

UPDATE public.dms_documents
SET document_no = 'MM-R02',
    document_type = 'QUALITY_MANUAL',
    responsible_dept_id = 'dept-quality'
WHERE id = '019d1000-0000-7000-8000-000000000002';

UPDATE public.dms_file_versions
SET version_label = 'D(09)',
    prepared_by = 'author-a',
    prepared_on = DATE '2023-08-30'
WHERE id = '019d1000-0000-7000-8000-000000000003';

DO $dms_completed_metadata_immutable$
BEGIN
    BEGIN
        UPDATE public.dms_documents
        SET document_no = 'MM-R03'
        WHERE id = '019d1000-0000-7000-8000-000000000002';
        RAISE EXCEPTION 'completed document number was changed';
    EXCEPTION
        WHEN object_not_in_prerequisite_state THEN NULL;
    END;

    BEGIN
        UPDATE public.dms_file_versions
        SET version_label = 'D(10)'
        WHERE id = '019d1000-0000-7000-8000-000000000003';
        RAISE EXCEPTION 'completed version label was changed';
    EXCEPTION
        WHEN object_not_in_prerequisite_state THEN NULL;
    END;
END;
$dms_completed_metadata_immutable$;

INSERT INTO public.dms_version_states (
    file_version_id, tenant_id, document_id, lifecycle_status,
    approved_at, approved_by, archived_at, effective_from, effective_to,
    review_cycle_months, next_review_on,
    approval_source_type, approval_source_id, status_changed_by
) VALUES (
    '019d1000-0000-7000-8000-000000000003',
    'dms-file-property-test',
    '019d1000-0000-7000-8000-000000000002',
    'EFFECTIVE',
    CURRENT_TIMESTAMP - INTERVAL '3 days',
    'quality-user',
    CURRENT_TIMESTAMP - INTERVAL '2 days',
    CURRENT_TIMESTAMP - INTERVAL '1 day',
    CURRENT_TIMESTAMP + INTERVAL '365 days',
    36,
    CURRENT_DATE + 365,
    'WORKFLOW',
    'workflow-a-v1-approved',
    'quality-user'
);

-- A second revision is already approved and due for promotion, but remains pending.
INSERT INTO public.dms_file_versions (
    id, tenant_id, document_id, version_no, version_label,
    file_name, prepared_by, prepared_on, created_by
) VALUES (
    '019d1000-0000-7000-8000-000000000004',
    'dms-file-property-test',
    '019d1000-0000-7000-8000-000000000002',
    2,
    'D(10)',
    'controlled-document-a-v2.docx',
    'author-b',
    CURRENT_DATE - 10,
    'dms-file-property-test'
);

INSERT INTO public.dms_permission_resources (
    id, tenant_id, resource_type, source_type, source_id, registered_by
) VALUES (
    '019d1000-0000-7000-8000-000000000004',
    'dms-file-property-test',
    'FILE_VERSION',
    'MIGRATION',
    'file-property-version-a2',
    'dms-file-property-test'
);

INSERT INTO public.dms_version_states (
    file_version_id, tenant_id, document_id, lifecycle_status,
    approved_at, approved_by, archived_at, effective_from,
    approval_source_type, approval_source_id, status_changed_by
) VALUES (
    '019d1000-0000-7000-8000-000000000004',
    'dms-file-property-test',
    '019d1000-0000-7000-8000-000000000002',
    'PENDING_EFFECTIVE',
    CURRENT_TIMESTAMP - INTERVAL '3 hours',
    'quality-user',
    CURRENT_TIMESTAMP - INTERVAL '2 hours',
    CURRENT_TIMESTAMP - INTERVAL '1 hour',
    'WORKFLOW',
    'workflow-a-v2-approved',
    'quality-user'
);

DO $dms_current_pending_not_history$
DECLARE
    current_count INTEGER;
    pending_count INTEGER;
    history_count INTEGER;
BEGIN
    SELECT count(*) INTO current_count
    FROM public.dms_version_states
    WHERE tenant_id = 'dms-file-property-test'
      AND document_id = '019d1000-0000-7000-8000-000000000002'
      AND lifecycle_status = 'EFFECTIVE'
      AND effective_from <= CURRENT_TIMESTAMP
      AND (effective_to IS NULL OR CURRENT_TIMESTAMP < effective_to);

    SELECT count(*) INTO pending_count
    FROM public.dms_version_states
    WHERE tenant_id = 'dms-file-property-test'
      AND document_id = '019d1000-0000-7000-8000-000000000002'
      AND lifecycle_status = 'PENDING_EFFECTIVE';

    SELECT count(*) INTO history_count
    FROM public.dms_version_states
    WHERE tenant_id = 'dms-file-property-test'
      AND document_id = '019d1000-0000-7000-8000-000000000002'
      AND lifecycle_status IN ('SUPERSEDED', 'EXPIRED', 'REVOKED', 'INVALIDATED', 'OBSOLETE');

    IF current_count <> 1 OR pending_count <> 1 OR history_count <> 0 THEN
        RAISE EXCEPTION 'unexpected classification: current %, pending %, history %',
            current_count, pending_count, history_count;
    END IF;
END;
$dms_current_pending_not_history$;

-- Promotion is one transaction: retire the current revision before activating the pending one.
UPDATE public.dms_version_states
SET lifecycle_status = 'SUPERSEDED',
    effective_to = CURRENT_TIMESTAMP,
    status_reason = 'Superseded by D(10)',
    status_changed_by = 'dms-scheduler',
    revision = revision + 1
WHERE file_version_id = '019d1000-0000-7000-8000-000000000003';

UPDATE public.dms_version_states
SET lifecycle_status = 'EFFECTIVE',
    status_reason = 'Scheduled promotion',
    status_changed_by = 'dms-scheduler',
    revision = revision + 1
WHERE file_version_id = '019d1000-0000-7000-8000-000000000004';

-- Review extension changes the lifecycle row, not the immutable FileVersion.
UPDATE public.dms_version_states
SET review_cycle_months = 24,
    last_reviewed_on = CURRENT_DATE,
    next_review_on = CURRENT_DATE + 730,
    effective_to = CURRENT_TIMESTAMP + INTERVAL '730 days',
    status_reason = 'Periodic review passed',
    status_changed_by = 'quality-user',
    revision = revision + 1
WHERE file_version_id = '019d1000-0000-7000-8000-000000000004';

DO $dms_lifecycle_after_promotion$
BEGIN
    IF (SELECT count(*) FROM public.dms_version_states
        WHERE tenant_id = 'dms-file-property-test'
          AND document_id = '019d1000-0000-7000-8000-000000000002'
          AND lifecycle_status = 'EFFECTIVE') <> 1 THEN
        RAISE EXCEPTION 'promotion did not leave exactly one current revision';
    END IF;

    IF (SELECT count(*) FROM public.dms_version_states
        WHERE tenant_id = 'dms-file-property-test'
          AND document_id = '019d1000-0000-7000-8000-000000000002'
          AND lifecycle_status IN ('SUPERSEDED', 'EXPIRED', 'REVOKED', 'INVALIDATED', 'OBSOLETE')) <> 1 THEN
        RAISE EXCEPTION 'superseded revision was not classified as history';
    END IF;

    BEGIN
        UPDATE public.dms_version_states
        SET lifecycle_status = 'EFFECTIVE',
            effective_to = CURRENT_TIMESTAMP + INTERVAL '30 days',
            status_reason = 'Illegal rollback',
            status_changed_by = 'quality-user',
            revision = revision + 1
        WHERE file_version_id = '019d1000-0000-7000-8000-000000000003';
        RAISE EXCEPTION 'terminal lifecycle state was reversed';
    EXCEPTION
        WHEN object_not_in_prerequisite_state THEN NULL;
    END;
END;
$dms_lifecycle_after_promotion$;

-- Document B is the target of a version-owned related-file declaration.
INSERT INTO public.dms_documents (
    id, tenant_id, folder_id, document_no, name, document_type,
    responsible_dept_id, created_by
) VALUES (
    '019d1000-0000-7000-8000-000000000005',
    'dms-file-property-test',
    '019d1000-0000-7000-8000-000000000001',
    'SOP-R01',
    'Related document B',
    'SOP',
    'dept-quality',
    'dms-file-property-test'
);

INSERT INTO public.dms_permission_resources (
    id, tenant_id, resource_type, source_type, source_id, registered_by
) VALUES (
    '019d1000-0000-7000-8000-000000000005',
    'dms-file-property-test',
    'DOCUMENT',
    'MIGRATION',
    'file-property-document-b',
    'dms-file-property-test'
);

INSERT INTO public.dms_version_relations (
    tenant_id, document_id, file_version_id, related_document_id,
    relation_type, created_by
) VALUES (
    'dms-file-property-test',
    '019d1000-0000-7000-8000-000000000002',
    '019d1000-0000-7000-8000-000000000004',
    '019d1000-0000-7000-8000-000000000005',
    'REFERENCE',
    'quality-user'
);

DO $dms_related_document_constraints$
BEGIN
    BEGIN
        INSERT INTO public.dms_version_relations (
            tenant_id, document_id, file_version_id, related_document_id,
            relation_type, created_by
        ) VALUES (
            'dms-file-property-test',
            '019d1000-0000-7000-8000-000000000002',
            '019d1000-0000-7000-8000-000000000004',
            '019d1000-0000-7000-8000-000000000002',
            'REFERENCE',
            'quality-user'
        );
        RAISE EXCEPTION 'self-related document was accepted';
    EXCEPTION
        WHEN check_violation THEN NULL;
    END;

    BEGIN
        DELETE FROM public.dms_version_relations
        WHERE tenant_id = 'dms-file-property-test';
        RAISE EXCEPTION 'append-only version relation was deleted';
    EXCEPTION
        WHEN object_not_in_prerequisite_state THEN NULL;
    END;
END;
$dms_related_document_constraints$;

SET CONSTRAINTS ALL IMMEDIATE;
ROLLBACK;

\echo 'DMS file-property and version-lifecycle regression passed.'