\set ON_ERROR_STOP on

BEGIN;

DO $dms_test$
BEGIN
    BEGIN
        INSERT INTO public.dms_folders (id, tenant_id, parent_id, name, created_by)
        VALUES (
            '018f0000-0000-7000-8000-000000000098',
            'dms-v22-test',
            NULL,
            'Missing registry',
            'dms-test'
        );
        SET CONSTRAINTS trg_dms_folders_require_registry IMMEDIATE;
        RAISE EXCEPTION 'business object without a registry row was accepted';
    EXCEPTION
        WHEN foreign_key_violation THEN NULL;
    END;
END;
$dms_test$;

INSERT INTO public.dms_folders (id, tenant_id, parent_id, name, created_by)
VALUES
    ('018f0000-0000-7000-8000-000000000001', 'dms-v22-test', NULL, 'Root', 'dms-test'),
    ('018f0000-0000-7000-8000-000000000005', 'dms-v22-test', NULL, 'Other', 'dms-test');

INSERT INTO public.dms_permission_resources (
    id, tenant_id, resource_type, source_type, source_id, registered_by
)
VALUES
    ('018f0000-0000-7000-8000-000000000001', 'dms-v22-test', 'FOLDER', 'SYSTEM', 'resource-root', 'dms-test'),
    ('018f0000-0000-7000-8000-000000000005', 'dms-v22-test', 'FOLDER', 'SYSTEM', 'resource-other', 'dms-test');

INSERT INTO public.dms_folders (id, tenant_id, parent_id, name, created_by)
VALUES (
    '018f0000-0000-7000-8000-000000000002',
    'dms-v22-test',
    '018f0000-0000-7000-8000-000000000001',
    'Child',
    'dms-test'
);

INSERT INTO public.dms_permission_resources (
    id, tenant_id, resource_type, source_type, source_id, registered_by
)
VALUES (
    '018f0000-0000-7000-8000-000000000002',
    'dms-v22-test',
    'FOLDER',
    'SYSTEM',
    'resource-child',
    'dms-test'
);

INSERT INTO public.dms_documents (id, tenant_id, folder_id, name, created_by)
VALUES (
    '018f0000-0000-7000-8000-000000000003',
    'dms-v22-test',
    '018f0000-0000-7000-8000-000000000002',
    'Document',
    'dms-test'
);

INSERT INTO public.dms_permission_resources (
    id, tenant_id, resource_type, source_type, source_id, registered_by
)
VALUES (
    '018f0000-0000-7000-8000-000000000003',
    'dms-v22-test',
    'DOCUMENT',
    'SYSTEM',
    'resource-document',
    'dms-test'
);

INSERT INTO public.dms_file_versions (
    id, tenant_id, document_id, version_no, file_name, mime_type,
    size_bytes, checksum_sha256, created_by
)
VALUES (
    '018f0000-0000-7000-8000-000000000004',
    'dms-v22-test',
    '018f0000-0000-7000-8000-000000000003',
    1,
    'document-v1.pdf',
    'application/pdf',
    1024,
    repeat('a', 64),
    'dms-test'
);

INSERT INTO public.dms_permission_resources (
    id, tenant_id, resource_type, source_type, source_id, registered_by
)
VALUES (
    '018f0000-0000-7000-8000-000000000004',
    'dms-v22-test',
    'FILE_VERSION',
    'SYSTEM',
    'resource-version',
    'dms-test'
);

DO $dms_test$
BEGIN
    BEGIN
        INSERT INTO public.dms_permission_resources (
            id, tenant_id, resource_type, source_type, source_id, registered_by
        ) VALUES (
            '018f0000-0000-7000-8000-000000000099',
            'dms-v22-test',
            'DOCUMENT',
            'SYSTEM',
            'missing-business-object',
            'dms-test'
        );
        RAISE EXCEPTION 'registry without a business object was accepted';
    EXCEPTION
        WHEN foreign_key_violation THEN NULL;
    END;
END;
$dms_test$;

INSERT INTO public.dms_file_permissions (
    tenant_id, resource_type, resource_id, resource_scope,
    principal_type, principal_id, action_code, source_type, source_id,
    max_use_count, granted_by_type, granted_by_id
)
VALUES (
    'dms-v22-test',
    'DOCUMENT',
    '018f0000-0000-7000-8000-000000000003',
    'SELF',
    'USER',
    'user-1',
    'UPDATE',
    'SYSTEM',
    'grant-document-update',
    1,
    'SERVICE',
    'dms-test'
);

INSERT INTO public.dms_permission_usages (
    tenant_id, permission_id, user_id, action_code,
    actual_resource_type, actual_resource_id, operation_id
)
SELECT
    tenant_id,
    id,
    'user-1',
    'UPDATE',
    'FILE_VERSION',
    '018f0000-0000-7000-8000-000000000004',
    '018f0000-0000-7000-8000-000000000101'
FROM public.dms_file_permissions
WHERE tenant_id = 'dms-v22-test'
  AND source_id = 'grant-document-update';

DO $dms_test$
DECLARE
    actual_used BIGINT;
BEGIN
    SELECT used_count INTO actual_used
    FROM public.dms_file_permissions
    WHERE tenant_id = 'dms-v22-test'
      AND source_id = 'grant-document-update';

    IF actual_used <> 1 THEN
        RAISE EXCEPTION 'expected used_count=1, got %', actual_used;
    END IF;
END;
$dms_test$;

DO $dms_test$
DECLARE
    permission_uuid UUID;
BEGIN
    SELECT id INTO permission_uuid
    FROM public.dms_file_permissions
    WHERE tenant_id = 'dms-v22-test'
      AND source_id = 'grant-document-update';

    BEGIN
        INSERT INTO public.dms_permission_usages (
            tenant_id, permission_id, user_id, action_code,
            actual_resource_type, actual_resource_id, operation_id
        ) VALUES (
            'dms-v22-test',
            permission_uuid,
            'user-1',
            'UPDATE',
            'FILE_VERSION',
            '018f0000-0000-7000-8000-000000000004',
            '018f0000-0000-7000-8000-000000000101'
        );
        RAISE EXCEPTION 'duplicate operation_id was accepted';
    EXCEPTION
        WHEN insufficient_privilege OR unique_violation THEN NULL;
    END;
END;
$dms_test$;

DO $dms_test$
DECLARE
    actual_used BIGINT;
BEGIN
    SELECT used_count INTO actual_used
    FROM public.dms_file_permissions
    WHERE tenant_id = 'dms-v22-test'
      AND source_id = 'grant-document-update';

    IF actual_used <> 1 THEN
        RAISE EXCEPTION 'retry changed used_count to %', actual_used;
    END IF;
END;
$dms_test$;

INSERT INTO public.dms_file_permissions (
    tenant_id, resource_type, resource_id, resource_scope,
    principal_type, principal_id, action_code, source_type, source_id,
    max_use_count, granted_by_type, granted_by_id
)
VALUES (
    'dms-v22-test',
    'FOLDER',
    '018f0000-0000-7000-8000-000000000005',
    'SUBTREE',
    'USER',
    'user-1',
    'READ',
    'SYSTEM',
    'grant-other-folder',
    1,
    'SERVICE',
    'dms-test'
);

DO $dms_test$
DECLARE
    permission_uuid UUID;
BEGIN
    SELECT id INTO permission_uuid
    FROM public.dms_file_permissions
    WHERE tenant_id = 'dms-v22-test'
      AND source_id = 'grant-other-folder';

    BEGIN
        INSERT INTO public.dms_permission_usages (
            tenant_id, permission_id, user_id, action_code,
            actual_resource_type, actual_resource_id, operation_id
        ) VALUES (
            'dms-v22-test',
            permission_uuid,
            'user-1',
            'READ',
            'FILE_VERSION',
            '018f0000-0000-7000-8000-000000000004',
            '018f0000-0000-7000-8000-000000000102'
        );
        RAISE EXCEPTION 'unrelated Folder permission covered a FileVersion';
    EXCEPTION
        WHEN insufficient_privilege THEN NULL;
    END;
END;
$dms_test$;

DO $dms_test$
BEGIN
    BEGIN
        UPDATE public.dms_folders
        SET name = 'Changed'
        WHERE id = '018f0000-0000-7000-8000-000000000001';
        RAISE EXCEPTION 'immutable Folder update was accepted';
    EXCEPTION
        WHEN object_not_in_prerequisite_state THEN NULL;
    END;
END;
$dms_test$;

SET CONSTRAINTS ALL IMMEDIATE;

ROLLBACK;