psql_tasks/mhd_task/local/clean_eds_tnb.sql

-- ============================================================
-- Скрипт приведения схемы eds_tnb (с проверкой существования)
-- PostgreSQL 16
-- ============================================================

DO $$
DECLARE
    v_obj_name text;
    v_obj_type text;
    v_sql text;
BEGIN
    RAISE NOTICE '=== Начало приведения схемы eds_tnb ===';

    -- Разрешенные таблицы
    CREATE TEMP TABLE IF NOT EXISTS allowed_tables AS
    SELECT unnest(ARRAY[
        'acceptanceinventorymodels',
        'AccessCrcModel',
        'AccessGroupModels',
        'AccountingSystemResendingModel',
        'archiveactdestroymodels',
        'archiveautoconnectwithdoctyperesultmodels',
        'AttachmentForIndexingModel',
        'AttachmentGarbageModel',
        'AttachmentModel',
        'AutoMigrationInfoModel',
        'BarcodeOldBarcodePair',
        'collectionpreferencesmodels',
        'costassetmodels',
        'DeferredReportModel',
        'DeletedModel',
        'DictionarySynchronizeInfoModel',
        'DocumentModel',
        'DvscResendingModel',
        'EdsDocumentModelTask',
        'EdsIntegrationSystemUploadingModel',
        'edsagreementdocumentmodels',
        'edsdocumentmodels',
        'edseventauditmodels',
        'edsloginauditmodels',
        'edsrepoinfos',
        'FileCacheModel',
        'IndexesFullTextSearchModel',
        'informationmodels',
        'integrationsynchronizemodels',
        'legacyrepoinfos',
        'lockmodels',
        'LoggerModel',
        'MSedLogModel',
        'MzedEdsDocumentModel',
        'MzedPackageDocumentModel',
        'nomenclaturefoldermodels',
        'notificationinfomodels',
        'NotificationSettingModel',
        'PackageAggregationModel',
        'PackageDocumentModelTask',
        'packagedocumenteventmodels',
        'packagedocumentmodels',
        'replacementtaskmodels',
        'RelationModel',
        'savedfiltersetmodels',
        'ScanFileModel',
        'ScanHistoryModel',
        'scanuploadingmodel',
        'sendintegrationstatus',
        'signatureverification',
        'StorageTempFileModel',
        'SystemConfigurationModel',
        'transferofcasetasklifecyclemodels',
        'UnionAttachmentsTaskModel',
        'UzdoFileModel',
        'UzdoSynchronizationStatusModel',
        'VersionModel',
        'versionedbarcodes',
        'withdrawtaskmodels'
    ]) AS name;

    -- Разрешенные вьюхи
    CREATE TEMP TABLE IF NOT EXISTS allowed_views AS
    SELECT unnest(ARRAY[
        'CountersMap',
        'EdsJournalMap',
        'EdsJournalMapTask',
        'NotificationInfoMap',
        'View_ArchiveAutoConnectWithDocTypeJournalMap',
        'View_ArchiveAutoConnectWithDocTypeReportMap',
        'View_ArchiveDepartmentMap',
        'View_ArchiveDocumentMap',
        'View_ArchiveExpiredCaseMap',
        'View_ArchiveFormMap',
        'View_ArchiveJournalMap',
        'View_ArchiveNomenclatureMap',
        'View_ArchiveNomenclatureParentsMap',
        'View_ArchiveNomenclatureTreeMap',
        'View_ArchiveTomeChildMap',
        'View_ArchiveTomeMap',
        'View_AuditRoleReportMap',
        'View_DeferredReportMap',
        'View_EdsBarcodeMap',
        'View_EdsCopyReportMap',
        'View_EdsCopyReportReflectedInAcsMap',
        'View_EdsCuratorsReportMap',
        'View_EdsDeadlineExceededReportMap',
        'View_EdsElectronicRegisterMap',
        'View_EdsEventAuditMap',
        'View_EdsIntegrationSystemUploadingMap',
        'View_EdsLogicDeleteDocsMap',
        'View_EdsLoginAuditMap',
        'View_EdsOstDocumentsReportMap',
        'View_EdsReportTranssAndProcessStatisticMap',
        'View_EdsReportTranssAndProcessStatisticReprocMap',
        'View_EdsReturnDocToCuratorsMap',
        'View_EdsSmodReportMap',
        'View_EdsTnfPerformerStatisticReportMap',
        'View_EdsWithdrawMap',
        'View_EfEdsDocumentMap',
        'View_ReadyReportsMap',
        'View_ScanUploadingDocumentMap',
        'View_SearchByRegistryMap',
        'View_TransferJournalMap',
        'View_TransferOfCaseTaskMap',
        'View_UzdoDocumentMap'
    ]) AS name;

    -- ============================================================
    -- Удаление лишних таблиц
    -- ============================================================
    RAISE NOTICE '--- Удаление лишних таблиц ---';

    FOR v_obj_name IN (
        SELECT tablename
        FROM pg_catalog.pg_tables
        WHERE schemaname = 'eds_tnb'
          AND tablename NOT IN (SELECT name FROM allowed_tables)
    ) LOOP
        EXECUTE format('DROP TABLE IF EXISTS eds_tnb.%I CASCADE;', v_obj_name);
        RAISE NOTICE '  Удалена таблица: %', v_obj_name;
    END LOOP;

    -- ============================================================
    -- Удаление лишних вьюх
    -- ============================================================
    RAISE NOTICE '--- Удаление лишних вьюх ---';

    FOR v_obj_name IN (
        SELECT viewname
        FROM pg_catalog.pg_views
        WHERE schemaname = 'eds_tnb'
          AND viewname NOT IN (SELECT name FROM allowed_views)
    ) LOOP
        EXECUTE format('DROP VIEW IF EXISTS eds_tnb.%I CASCADE;', v_obj_name);
        RAISE NOTICE '  Удалена вьюха: %', v_obj_name;
    END LOOP;

    -- ============================================================
    -- Очистка временных таблиц
    -- ============================================================
    DROP TABLE IF EXISTS allowed_tables;
    DROP TABLE IF EXISTS allowed_views;

    -- ============================================================
    -- Финальный отчет
    -- ============================================================
    RAISE NOTICE '=== ИТОГОВЫЙ СОСТАВ ===';
    
    RAISE NOTICE '--- Таблицы ---';
    FOR v_obj_name IN (
        SELECT tablename 
        FROM pg_catalog.pg_tables 
        WHERE schemaname = 'eds_tnb' 
        ORDER BY tablename
    ) LOOP
        RAISE NOTICE '  %', v_obj_name;
    END LOOP;

    RAISE NOTICE '--- Вьюхи ---';
    FOR v_obj_name IN (
        SELECT viewname 
        FROM pg_catalog.pg_views 
        WHERE schemaname = 'eds_tnb' 
        ORDER BY viewname
    ) LOOP
        RAISE NOTICE '  %', v_obj_name;
    END LOOP;

    RAISE NOTICE '=== ГОТОВО ===';
END $$;