-- DMS permission V2.3 database regression.
-- Prerequisite: V2.2 schema followed by upgrade_dms_permission_v2_2_to_v2_3_20260817.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_v23_schema_assertions$
DECLARE
    required_object TEXT;
BEGIN
    FOREACH required_object IN ARRAY ARRAY[
        'public.dms_access_exclusions',
        'public.dms_permission_revisions',
        'public.idx_dms_folders_parent_name',
        'public.idx_dms_documents_folder_name',
        'public.idx_dms_file_versions_document_id'
    ] LOOP
        IF to_regclass(required_object) IS NULL THEN
            RAISE EXCEPTION 'required V2.3 object % is missing', required_object;
        END IF;
    END LOOP;

    IF EXISTS (
        SELECT 1
        FROM pg_catalog.pg_index i
        WHERE i.indexrelid IN (
            'public.idx_dms_folders_parent_name'::REGCLASS,
            'public.idx_dms_documents_folder_name'::REGCLASS,
            'public.idx_dms_file_versions_document_id'::REGCLASS
        )
          AND (NOT i.indisvalid OR NOT i.indisready)
    ) THEN
        RAISE EXCEPTION 'one or more DMS V2.3 paging indexes are invalid';
    END IF;
END;
$dms_v23_schema_assertions$;

INSERT INTO public.dms_folders (
    id, tenant_id, parent_id, name, created_by
) VALUES (
    '019d0000-0000-7000-8000-000000000001',
    'dms-v23-regression',
    NULL,
    'V2.3 regression root',
    'dms-v23-test'
);

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

INSERT INTO public.dms_documents (
    id, tenant_id, folder_id, name, created_by
) VALUES (
    '019d0000-0000-7000-8000-000000000002',
    'dms-v23-regression',
    '019d0000-0000-7000-8000-000000000001',
    'V2.3 regression document',
    'dms-v23-test'
);

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

INSERT INTO public.dms_file_versions (
    id, tenant_id, document_id, version_no, file_name, created_by
) VALUES (
    '019d0000-0000-7000-8000-000000000003',
    'dms-v23-regression',
    '019d0000-0000-7000-8000-000000000002',
    1,
    'v2.3-regression.txt',
    'dms-v23-test'
);

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

INSERT INTO public.dms_file_permissions (
    id, tenant_id, resource_type, resource_id, resource_scope,
    principal_type, principal_id, include_child_orgs, action_code,
    source_type, source_id, max_use_count, grant_reason,
    granted_by_type, granted_by_id
) VALUES (
    '019d0000-0000-7000-8000-000000000100',
    'dms-v23-regression',
    'DOCUMENT',
    '019d0000-0000-7000-8000-000000000002',
    'SELF',
    'USER',
    'dms-v23-user',
    FALSE,
    'READ',
    'MIGRATION',
    'dms-v23-regression-permission',
    5,
    'DMS V2.3 database regression',
    'SERVICE',
    'dms-v23-test'
);

DO $dms_v23_permission_insert_revision$
BEGIN
    IF (SELECT revision FROM public.dms_permission_revisions
        WHERE tenant_id = 'dms-v23-regression') <> 1 THEN
        RAISE EXCEPTION 'one permission INSERT statement must initialize tenant revision to 1';
    END IF;
END;
$dms_v23_permission_insert_revision$;

-- Empty statements fire statement-level triggers but must not change the tenant revision.
INSERT INTO public.dms_file_permissions (
    tenant_id, resource_type, resource_id, resource_scope,
    principal_type, principal_id, action_code, source_type, source_id,
    granted_by_type, granted_by_id
)
SELECT
    'dms-v23-regression',
    'DOCUMENT',
    '019d0000-0000-7000-8000-000000000002',
    'SELF',
    'USER',
    'nobody',
    'READ',
    'MIGRATION',
    'dms-v23-empty-permission',
    'SERVICE',
    'dms-v23-test'
WHERE FALSE;

DO $dms_v23_empty_permission_revision$
BEGIN
    IF (SELECT revision FROM public.dms_permission_revisions
        WHERE tenant_id = 'dms-v23-regression') <> 1 THEN
        RAISE EXCEPTION 'empty permission INSERT must not change tenant revision';
    END IF;
END;
$dms_v23_empty_permission_revision$;

-- A usage changes the grant row revision but must not change the tenant permission revision.
INSERT INTO public.dms_permission_usages (
    tenant_id, permission_id, user_id, action_code,
    actual_resource_type, actual_resource_id, operation_id
) VALUES (
    'dms-v23-regression',
    '019d0000-0000-7000-8000-000000000100',
    'dms-v23-user',
    'READ',
    'FILE_VERSION',
    '019d0000-0000-7000-8000-000000000003',
    '019d0000-0000-7000-8000-000000000200'
);

DO $dms_v23_usage_revision$
BEGIN
    IF (SELECT revision FROM public.dms_permission_revisions
        WHERE tenant_id = 'dms-v23-regression') <> 1 THEN
        RAISE EXCEPTION 'permission usage must not change tenant revision';
    END IF;
    IF (SELECT used_count FROM public.dms_file_permissions
        WHERE id = '019d0000-0000-7000-8000-000000000100') <> 1 THEN
        RAISE EXCEPTION 'permission usage did not increment used_count';
    END IF;
END;
$dms_v23_usage_revision$;

-- Two exclusions in one statement must bump the tenant revision only once.
INSERT INTO public.dms_access_exclusions (
    id, tenant_id, resource_type, resource_id, user_id, action_code,
    source_type, source_id, exclude_reason, excluded_by_type, excluded_by_id
) VALUES
(
    '019d0000-0000-7000-8000-000000000301',
    'dms-v23-regression',
    'DOCUMENT',
    '019d0000-0000-7000-8000-000000000002',
    'dms-v23-user',
    'READ',
    'MIGRATION',
    'dms-v23-regression-exclusions',
    'Document exclusion regression',
    'SERVICE',
    'dms-v23-test'
),
(
    '019d0000-0000-7000-8000-000000000302',
    'dms-v23-regression',
    'FILE_VERSION',
    '019d0000-0000-7000-8000-000000000003',
    'dms-v23-user',
    'READ',
    'MIGRATION',
    'dms-v23-regression-exclusions',
    'FileVersion exclusion regression',
    'SERVICE',
    'dms-v23-test'
);

DO $dms_v23_exclusion_insert_revision$
BEGIN
    IF (SELECT revision FROM public.dms_permission_revisions
        WHERE tenant_id = 'dms-v23-regression') <> 2 THEN
        RAISE EXCEPTION 'one exclusion INSERT statement must bump tenant revision exactly once';
    END IF;
END;
$dms_v23_exclusion_insert_revision$;

INSERT INTO public.dms_access_exclusions (
    tenant_id, resource_type, resource_id, user_id,
    source_type, source_id, excluded_by_type, excluded_by_id
)
SELECT
    'dms-v23-regression',
    'DOCUMENT',
    '019d0000-0000-7000-8000-000000000002',
    'nobody',
    'MIGRATION',
    'dms-v23-empty-exclusion',
    'SERVICE',
    'dms-v23-test'
WHERE FALSE;

DO $dms_v23_empty_exclusion_revision$
BEGIN
    IF (SELECT revision FROM public.dms_permission_revisions
        WHERE tenant_id = 'dms-v23-regression') <> 2 THEN
        RAISE EXCEPTION 'empty exclusion INSERT must not change tenant revision';
    END IF;
END;
$dms_v23_empty_exclusion_revision$;

UPDATE public.dms_access_exclusions
SET revoked_at = CURRENT_TIMESTAMP,
    revoked_by_type = 'SERVICE',
    revoked_by_id = 'dms-v23-test',
    revoke_reason = 'DMS V2.3 regression revocation',
    revision = revision + 1
WHERE tenant_id = 'dms-v23-regression';

DO $dms_v23_exclusion_revoke_revision$
BEGIN
    IF (SELECT revision FROM public.dms_permission_revisions
        WHERE tenant_id = 'dms-v23-regression') <> 3 THEN
        RAISE EXCEPTION 'one exclusion revocation statement must bump tenant revision exactly once';
    END IF;
END;
$dms_v23_exclusion_revoke_revision$;

UPDATE public.dms_file_permissions
SET revoked_at = CURRENT_TIMESTAMP,
    revoked_by_type = 'SERVICE',
    revoked_by_id = 'dms-v23-test',
    revoke_reason = 'DMS V2.3 regression revocation',
    revision = revision + 1
WHERE id = '019d0000-0000-7000-8000-000000000100';

DO $dms_v23_permission_revoke_revision$
BEGIN
    IF (SELECT revision FROM public.dms_permission_revisions
        WHERE tenant_id = 'dms-v23-regression') <> 4 THEN
        RAISE EXCEPTION 'permission revocation must bump tenant revision exactly once';
    END IF;
END;
$dms_v23_permission_revoke_revision$;

DO $dms_v23_immutable_assertions$
BEGIN
    BEGIN
        UPDATE public.dms_file_permissions
        SET id = '019d0000-0000-7000-8000-000000000101',
            revision = revision + 1
        WHERE id = '019d0000-0000-7000-8000-000000000100';
        RAISE EXCEPTION 'permission id update unexpectedly succeeded';
    EXCEPTION
        WHEN SQLSTATE '55000' THEN NULL;
    END;

    BEGIN
        UPDATE public.dms_access_exclusions
        SET revoked_at = CURRENT_TIMESTAMP,
            revoked_by_type = 'SERVICE',
            revoked_by_id = 'dms-v23-test',
            revoke_reason = 'Repeated revocation',
            revision = revision + 1
        WHERE id = '019d0000-0000-7000-8000-000000000301';
        RAISE EXCEPTION 'repeated exclusion revocation unexpectedly succeeded';
    EXCEPTION
        WHEN SQLSTATE '55000' THEN NULL;
    END;

    BEGIN
        DELETE FROM public.dms_access_exclusions
        WHERE tenant_id = 'dms-v23-regression';
        RAISE EXCEPTION 'physical exclusion delete unexpectedly succeeded';
    EXCEPTION
        WHEN SQLSTATE '55000' THEN NULL;
    END;

    BEGIN
        TRUNCATE TABLE public.dms_access_exclusions;
        RAISE EXCEPTION 'physical exclusion truncate unexpectedly succeeded';
    EXCEPTION
        WHEN SQLSTATE '55000' THEN NULL;
    END;

    BEGIN
        DELETE FROM public.dms_permission_revisions
        WHERE tenant_id = 'dms-v23-regression';
        RAISE EXCEPTION 'physical permission revision delete unexpectedly succeeded';
    EXCEPTION
        WHEN SQLSTATE '55000' THEN NULL;
    END;
END;
$dms_v23_immutable_assertions$;

DO $dms_v23_constraint_assertions$
BEGIN
    BEGIN
        INSERT INTO public.dms_access_exclusions (
            tenant_id, resource_type, resource_id, user_id,
            source_type, source_id, excluded_by_type, excluded_by_id
        ) VALUES (
            'dms-v23-regression',
            'FOLDER',
            '019d0000-0000-7000-8000-000000000001',
            'dms-v23-user',
            'MIGRATION',
            'dms-v23-invalid-folder-exclusion',
            'SERVICE',
            'dms-v23-test'
        );
        RAISE EXCEPTION 'FOLDER exclusion unexpectedly succeeded';
    EXCEPTION
        WHEN check_violation THEN NULL;
    END;

    BEGIN
        INSERT INTO public.dms_access_exclusions (
            tenant_id, resource_type, resource_id, user_id,
            source_type, source_id, excluded_by_type, excluded_by_id
        ) VALUES (
            'another-tenant',
            'DOCUMENT',
            '019d0000-0000-7000-8000-000000000002',
            'dms-v23-user',
            'MIGRATION',
            'dms-v23-cross-tenant-exclusion',
            'SERVICE',
            'dms-v23-test'
        );
        RAISE EXCEPTION 'cross-tenant exclusion unexpectedly succeeded';
    EXCEPTION
        WHEN foreign_key_violation THEN NULL;
    END;
END;
$dms_v23_constraint_assertions$;

ROLLBACK;

SELECT 'DMS permission V2.3 database regression passed' AS result;