---

USE [myNewVasya];
GO
SET NOCOUNT ON;

DROP TABLE IF EXISTS #Events;
DROP TABLE IF EXISTS #Members;
DROP TABLE IF EXISTS #Reports;
DROP TABLE IF EXISTS #Send;
DROP TABLE IF EXISTS #Counts;

CREATE TABLE #Events (kod int PRIMARY KEY);

INSERT INTO #Events (kod)
VALUES
    (14514), (14522), (14523),
    (15605), (15606), (15608), (15609),
    (15613), (15614), (15616), (15617), (15618),
    (15619), (15620), (15621), (15622), (15623),
    (15624), (15625), (15626), (15627), (15628),
    (15629), (15630), (15631), (15632), (15634),
    (15637), (15638), (15639), (15640), (15641),
    (15642), (15643);

-- 1. Участники выбранных мероприятий.
SELECT m.kod
INTO #Members
FROM dbo.dat_members AS m
WHERE m.kodMerop IN (SELECT kod FROM #Events);

CREATE UNIQUE CLUSTERED INDEX IX_Members ON #Members(kod);

-- 2. Отчёты участников, а также отчёты,
-- явно связанные с выбранными мероприятиями через сертификаты.
SELECT r.kod
INTO #Reports
FROM dbo.sb_reports AS r
WHERE r.kodMember IN (SELECT kod FROM #Members)
   OR EXISTS (
       SELECT 1
       FROM dbo.dat_mts_certificates AS c
       WHERE c.kodReport = r.kod
         AND (
             c.kodMerop IN (SELECT kod FROM #Events)
             OR c.kodMember IN (SELECT kod FROM #Members)
         )
   );

CREATE UNIQUE CLUSTERED INDEX IX_Reports ON #Reports(kod);

-- 3. Письма, связанные с мероприятиями, участниками или отчётами.
SELECT s.kod
INTO #Send
FROM dbo.sb_send AS s
WHERE s.kodMerop IN (SELECT kod FROM #Events)
   OR s.kodMember IN (SELECT kod FROM #Members)
   OR s.kodReport IN (SELECT kod FROM #Reports)
   OR s.kod_doubleReport IN (SELECT kod FROM #Reports)
   OR EXISTS (
       SELECT 1
       FROM dbo.sb_reports AS r
       WHERE r.kod IN (SELECT kod FROM #Reports)
         AND r.kodSend = s.kod
   );

CREATE UNIQUE CLUSTERED INDEX IX_Send ON #Send(kod);

-- Отдельно показать отсутствующие коды.
SELECT
    N'Код отсутствует в dat_activity' AS Проверка,
    e.kod
FROM #Events AS e
LEFT JOIN dbo.dat_activity AS a ON a.kod = e.kod
WHERE a.kod IS NULL;

-- 4. Подробный просмотр рабочих таблиц.

SELECT N'dat_activity' AS Таблица, a.*
FROM dbo.dat_activity AS a
WHERE a.kod IN (SELECT kod FROM #Events)
ORDER BY a.kod;

SELECT N'dat_members' AS Таблица, m.*
FROM dbo.dat_members AS m
WHERE m.kod IN (SELECT kod FROM #Members)
ORDER BY m.kodMerop, m.kod;

SELECT N'sb_reports' AS Таблица, r.*
FROM dbo.sb_reports AS r
WHERE r.kod IN (SELECT kod FROM #Reports)
ORDER BY r.kodMember, r.kod;

SELECT N'sb_answer' AS Таблица, a.*
FROM dbo.sb_answer AS a
WHERE a.kodMerop IN (SELECT kod FROM #Events)
   OR a.kodMember IN (SELECT kod FROM #Members)
ORDER BY a.kodMerop, a.kod;

SELECT N'sb_answerData' AS Таблица, a.*
FROM dbo.sb_answerData AS a
WHERE a.kodMerop IN (SELECT kod FROM #Events)
   OR a.kodMember IN (SELECT kod FROM #Members)
ORDER BY a.kodMerop, a.kod;

SELECT N'dat_mts_certificates' AS Таблица, c.*
FROM dbo.dat_mts_certificates AS c
WHERE c.kodMerop IN (SELECT kod FROM #Events)
   OR c.kodMember IN (SELECT kod FROM #Members)
   OR c.kodReport IN (SELECT kod FROM #Reports)
ORDER BY c.kodMerop, c.kod;

SELECT N'sb_send' AS Таблица, s.*
FROM dbo.sb_send AS s
WHERE s.kod IN (SELECT kod FROM #Send)
ORDER BY s.kod;

SELECT N'sb_toSend' AS Таблица, q.*
FROM dbo.sb_toSend AS q
WHERE q.kodFl IN (SELECT kod FROM #Send)
ORDER BY q.kodFl;

-- 5. Сводные количества.
CREATE TABLE #Counts (
    table_name sysname,
    row_count bigint
);

INSERT INTO #Counts
SELECT N'dat_activity', COUNT_BIG(*)
FROM dbo.dat_activity
WHERE kod IN (SELECT kod FROM #Events)
UNION ALL
SELECT N'dat_members', COUNT_BIG(*) FROM #Members
UNION ALL
SELECT N'sb_reports', COUNT_BIG(*) FROM #Reports
UNION ALL
SELECT N'sb_answer', COUNT_BIG(*)
FROM dbo.sb_answer
WHERE kodMerop IN (SELECT kod FROM #Events)
   OR kodMember IN (SELECT kod FROM #Members)
UNION ALL
SELECT N'sb_answerData', COUNT_BIG(*)
FROM dbo.sb_answerData
WHERE kodMerop IN (SELECT kod FROM #Events)
   OR kodMember IN (SELECT kod FROM #Members)
UNION ALL
SELECT N'dat_mts_certificates', COUNT_BIG(*)
FROM dbo.dat_mts_certificates
WHERE kodMerop IN (SELECT kod FROM #Events)
   OR kodMember IN (SELECT kod FROM #Members)
   OR kodReport IN (SELECT kod FROM #Reports)
UNION ALL
SELECT N'sb_send', COUNT_BIG(*) FROM #Send
UNION ALL
SELECT N'sb_toSend', COUNT_BIG(*)
FROM dbo.sb_toSend
WHERE kodFl IN (SELECT kod FROM #Send);

-- Необязательные таблицы: проверяем наличие.
-- Динамический SQL позволяет запускать скрипт и без этих таблиц.
IF OBJECT_ID(N'dbo.dat_mts_scores', N'U') IS NOT NULL
BEGIN
    EXEC sys.sp_executesql N'
        SELECT N''dat_mts_scores'' AS Таблица, s.*
        FROM dbo.dat_mts_scores AS s
        WHERE s.kodMerop IN (SELECT kod FROM #Events)
           OR s.kodMember IN (SELECT kod FROM #Members)
           OR s.kodReport IN (SELECT kod FROM #Reports);

        INSERT INTO #Counts
        SELECT N''dat_mts_scores'', COUNT_BIG(*)
        FROM dbo.dat_mts_scores
        WHERE kodMerop IN (SELECT kod FROM #Events)
           OR kodMember IN (SELECT kod FROM #Members)
           OR kodReport IN (SELECT kod FROM #Reports);
    ';
END;

IF OBJECT_ID(N'dbo.mts_daemon_mail_v12', N'U') IS NOT NULL
BEGIN
    EXEC sys.sp_executesql N'
        SELECT N''mts_daemon_mail_v12'' AS Таблица, j.*
        FROM dbo.mts_daemon_mail_v12 AS j
        WHERE j.kodReport IN (SELECT kod FROM #Reports)
           OR j.kodMember IN (SELECT kod FROM #Members)
           OR j.sendKod IN (SELECT kod FROM #Send);

        INSERT INTO #Counts
        SELECT N''mts_daemon_mail_v12'', COUNT_BIG(*)
        FROM dbo.mts_daemon_mail_v12
        WHERE kodReport IN (SELECT kod FROM #Reports)
           OR kodMember IN (SELECT kod FROM #Members)
           OR sendKod IN (SELECT kod FROM #Send);
    ';
END;

SELECT table_name AS Таблица, row_count AS Количество
FROM #Counts
ORDER BY table_name;

-- 6. Дополнительные зависимости, которые нужно проверить до DELETE.
-- Эти результаты — описание связей, а не строки для удаления.
SELECT
    fk.name AS Ограничение,
    OBJECT_SCHEMA_NAME(fk.parent_object_id) AS ДочерняяСхема,
    OBJECT_NAME(fk.parent_object_id) AS ДочерняяТаблица,
    pc.name AS ДочернееПоле,
    OBJECT_NAME(fk.referenced_object_id) AS РодительскаяТаблица,
    rc.name AS РодительскоеПоле,
    fk.delete_referential_action_desc AS ДействиеПриУдалении
FROM sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fc
    ON fc.constraint_object_id = fk.object_id
JOIN sys.columns AS pc
    ON pc.object_id = fc.parent_object_id
   AND pc.column_id = fc.parent_column_id
JOIN sys.columns AS rc
    ON rc.object_id = fc.referenced_object_id
   AND rc.column_id = fc.referenced_column_id
WHERE OBJECT_NAME(fk.referenced_object_id) IN (
    N'dat_activity', N'dat_members', N'sb_reports',
    N'dat_mts_certificates', N'dat_mts_scores',
    N'sb_answer', N'sb_answerData', N'sb_send',
    N'sb_toSend', N'mts_daemon_mail_v12'
)
ORDER BY РодительскаяТаблица, ДочерняяТаблица;

-- Триггеры могут выполнять дополнительные действия при удалении.
SELECT
    OBJECT_NAME(t.parent_id) AS Таблица,
    t.name AS Триггер,
    t.is_disabled AS Отключён,
    OBJECT_DEFINITION(t.object_id) AS Определение
FROM sys.triggers AS t
WHERE t.parent_id IN (
    OBJECT_ID(N'dbo.dat_activity'),
    OBJECT_ID(N'dbo.dat_members'),
    OBJECT_ID(N'dbo.sb_reports'),
    OBJECT_ID(N'dbo.dat_mts_certificates'),
    OBJECT_ID(N'dbo.dat_mts_scores'),
    OBJECT_ID(N'dbo.sb_answer'),
    OBJECT_ID(N'dbo.sb_answerData'),
    OBJECT_ID(N'dbo.sb_send'),
    OBJECT_ID(N'dbo.sb_toSend'),
    OBJECT_ID(N'dbo.mts_daemon_mail_v12')
);