DBA.usp_Apply_Qds

CREATE   PROCEDURE DBA.usp_Apply_Qds
    @ServerID int, @DatabaseName sysname, @RowsLoaded bigint OUTPUT, @Reset bit = 0
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    DECLARE @now datetime = DBA.fn_Now(), @tz nvarchar(128) = DBA.fn_TimeZone();
    DECLARE @max bigint = (SELECT MAX(runtime_stats_interval_id) FROM perf.Stg_QdsRuntimeStats WHERE ServerID = @ServerID AND DatabaseName = @DatabaseName);
    SET @RowsLoaded = 0;

    BEGIN TRY
        BEGIN TRAN;

        -- Uzaktan UTC gelen saatler sistemin diliminde yazılır (spec 2026-10-05-yerel-saat-dilimi-design §5)
        UPDATE perf.Stg_QdsRuntimeStats SET IntervalStartUtc = CONVERT(datetime, IntervalStartUtc AT TIME ZONE N'UTC' AT TIME ZONE @tz), IntervalEndUtc = CONVERT(datetime, IntervalEndUtc AT TIME ZONE N'UTC' AT TIME ZONE @tz)
        WHERE ServerID = @ServerID AND DatabaseName = @DatabaseName;
        UPDATE perf.Stg_QdsWaitStats SET IntervalStartUtc = CONVERT(datetime, IntervalStartUtc AT TIME ZONE N'UTC' AT TIME ZONE @tz)
        WHERE ServerID = @ServerID AND DatabaseName = @DatabaseName;
        UPDATE perf.Stg_QdsPlan SET LastCompileUtc = CONVERT(datetime, LastCompileUtc AT TIME ZONE N'UTC' AT TIME ZONE @tz), LastExecutionUtc = CONVERT(datetime, LastExecutionUtc AT TIME ZONE N'UTC' AT TIME ZONE @tz)
        WHERE ServerID = @ServerID AND DatabaseName = @DatabaseName;
        UPDATE perf.Stg_QdsQuery SET LastExecutionUtc = CONVERT(datetime, LastExecutionUtc AT TIME ZONE N'UTC' AT TIME ZONE @tz)
        WHERE ServerID = @ServerID AND DatabaseName = @DatabaseName;

        -- R04-2: QDS sıfırlandıysa eski plan/query id'leri yeniden kullanılacak; bu DB'nin ham QDS verisi silinir
        -- (günlük rollup'lar QueryDailyStats'ta korunur).
        IF @Reset = 1
        BEGIN
            DELETE perf.QdsRuntimeStats WHERE ServerID = @ServerID AND DatabaseName = @DatabaseName;
            DELETE perf.QdsWaitStats    WHERE ServerID = @ServerID AND DatabaseName = @DatabaseName;
            DELETE perf.QdsPlan         WHERE ServerID = @ServerID AND DatabaseName = @DatabaseName;
            DELETE perf.QdsQuery        WHERE ServerID = @ServerID AND DatabaseName = @DatabaseName;
        END

        -- Sorgu metinleri
        SELECT query_text_id, query_sql_text, HASHBYTES('SHA2_256', query_sql_text) AS TextHash
        INTO #txt FROM perf.Stg_QdsQueryText WHERE ServerID = @ServerID AND DatabaseName = @DatabaseName;

        ;WITH t AS (SELECT TextHash, query_sql_text, ROW_NUMBER() OVER (PARTITION BY TextHash ORDER BY query_text_id) AS rn FROM #txt WHERE TextHash IS NOT NULL)
        INSERT perf.QdsQueryText (TextHash, query_sql_text, FirstSeenUtc, LastSeenUtc)
        SELECT TextHash, query_sql_text, @now, @now FROM t
        WHERE rn = 1 AND NOT EXISTS (SELECT 1 FROM perf.QdsQueryText AS e WHERE e.TextHash = t.TextHash);

        -- Sorgular (upsert; TextHash yalnızca yeni metin geldiyse değişir)
        UPDATE q SET q.query_hash = g.query_hash, q.object_id = g.object_id, q.ObjectName = g.ObjectName,
                     q.context_settings_id = g.context_settings_id, q.LastExecutionUtc = g.LastExecutionUtc, q.LastSeenUtc = @now,
                     q.TextHash = COALESCE(x.TextHash, q.TextHash)
        FROM perf.QdsQuery AS q
        JOIN perf.Stg_QdsQuery AS g ON g.ServerID = q.ServerID AND g.DatabaseName = q.DatabaseName AND g.query_id = q.query_id
        LEFT JOIN #txt AS x ON x.query_text_id = g.query_text_id
        WHERE g.ServerID = @ServerID AND g.DatabaseName = @DatabaseName;

        INSERT perf.QdsQuery (ServerID, DatabaseName, query_id, query_hash, query_text_id, TextHash, object_id, ObjectName, context_settings_id, LastExecutionUtc, LastSeenUtc)
        SELECT g.ServerID, g.DatabaseName, g.query_id, g.query_hash, g.query_text_id,
               COALESCE(x.TextHash, (SELECT TOP (1) q2.TextHash FROM perf.QdsQuery AS q2
                                     WHERE q2.ServerID = g.ServerID AND q2.DatabaseName = g.DatabaseName AND q2.query_text_id = g.query_text_id AND q2.TextHash IS NOT NULL)),
               g.object_id, g.ObjectName, g.context_settings_id, g.LastExecutionUtc, @now
        FROM perf.Stg_QdsQuery AS g
        LEFT JOIN #txt AS x ON x.query_text_id = g.query_text_id
        WHERE g.ServerID = @ServerID AND g.DatabaseName = @DatabaseName
          AND NOT EXISTS (SELECT 1 FROM perf.QdsQuery AS q WHERE q.ServerID = g.ServerID AND q.DatabaseName = g.DatabaseName AND q.query_id = g.query_id);

        UPDATE t SET LastSeenUtc = @now FROM perf.QdsQueryText AS t
        WHERE t.TextHash IN (SELECT q.TextHash FROM perf.QdsQuery AS q JOIN perf.Stg_QdsQuery AS g
                             ON g.ServerID = q.ServerID AND g.DatabaseName = q.DatabaseName AND g.query_id = q.query_id
                             WHERE g.ServerID = @ServerID AND g.DatabaseName = @DatabaseName);

        -- Plan XML
        SELECT plan_id, PlanXml, HASHBYTES('SHA2_256', PlanXml) AS PlanHash
        INTO #px FROM perf.Stg_QdsPlanXml WHERE ServerID = @ServerID AND DatabaseName = @DatabaseName AND PlanXml IS NOT NULL;

        ;WITH p AS (SELECT PlanHash, PlanXml, ROW_NUMBER() OVER (PARTITION BY PlanHash ORDER BY plan_id) AS rn FROM #px)
        INSERT perf.QdsPlanXml (PlanHash, PlanXmlCompressed, FirstSeenUtc, LastSeenUtc)
        SELECT PlanHash, COMPRESS(PlanXml), @now, @now FROM p
        WHERE rn = 1 AND NOT EXISTS (SELECT 1 FROM perf.QdsPlanXml AS e WHERE e.PlanHash = p.PlanHash);

        -- Planlar (upsert; PlanHash yalnızca yeni XML geldiyse değişir)
        UPDATE p SET p.query_id = g.query_id, p.query_plan_hash = g.query_plan_hash, p.is_forced_plan = g.is_forced_plan,
                     p.compatibility_level = g.compatibility_level, p.LastCompileUtc = g.LastCompileUtc,
                     p.LastExecutionUtc = g.LastExecutionUtc, p.LastSeenUtc = @now, p.PlanHash = COALESCE(x.PlanHash, p.PlanHash),
                     p.force_failure_count = g.force_failure_count, p.last_force_failure_reason_desc = g.last_force_failure_reason_desc
        FROM perf.QdsPlan AS p
        JOIN perf.Stg_QdsPlan AS g ON g.ServerID = p.ServerID AND g.DatabaseName = p.DatabaseName AND g.plan_id = p.plan_id
        LEFT JOIN #px AS x ON x.plan_id = g.plan_id
        WHERE g.ServerID = @ServerID AND g.DatabaseName = @DatabaseName;

        INSERT perf.QdsPlan (ServerID, DatabaseName, plan_id, query_id, query_plan_hash, PlanHash, is_forced_plan, compatibility_level, LastCompileUtc, LastExecutionUtc, LastSeenUtc,
                             force_failure_count, last_force_failure_reason_desc)
        SELECT g.ServerID, g.DatabaseName, g.plan_id, g.query_id, g.query_plan_hash, x.PlanHash, g.is_forced_plan, g.compatibility_level,
               g.LastCompileUtc, g.LastExecutionUtc, @now, g.force_failure_count, g.last_force_failure_reason_desc
        FROM perf.Stg_QdsPlan AS g
        LEFT JOIN #px AS x ON x.plan_id = g.plan_id
        WHERE g.ServerID = @ServerID AND g.DatabaseName = @DatabaseName
          AND NOT EXISTS (SELECT 1 FROM perf.QdsPlan AS p WHERE p.ServerID = g.ServerID AND p.DatabaseName = g.DatabaseName AND p.plan_id = g.plan_id);

        -- K1: birikmiş (bu turun staging'inde olmayan) planların XML'i
        UPDATE p SET p.PlanHash = x.PlanHash
        FROM perf.QdsPlan AS p JOIN #px AS x ON x.plan_id = p.plan_id
        WHERE p.ServerID = @ServerID AND p.DatabaseName = @DatabaseName AND p.PlanHash IS NULL;

        UPDATE x SET LastSeenUtc = @now FROM perf.QdsPlanXml AS x
        WHERE x.PlanHash IN (SELECT p.PlanHash FROM perf.QdsPlan AS p JOIN perf.Stg_QdsPlan AS g
                             ON g.ServerID = p.ServerID AND g.DatabaseName = p.DatabaseName AND g.plan_id = p.plan_id
                             WHERE g.ServerID = @ServerID AND g.DatabaseName = @DatabaseName);

        -- İstatistikler
        INSERT perf.QdsRuntimeStats
        SELECT g.* FROM perf.Stg_QdsRuntimeStats AS g
        WHERE g.ServerID = @ServerID AND g.DatabaseName = @DatabaseName
          AND NOT EXISTS (SELECT 1 FROM perf.QdsRuntimeStats AS r
                          WHERE r.ServerID = g.ServerID AND r.DatabaseName = g.DatabaseName AND r.plan_id = g.plan_id
                            AND r.runtime_stats_interval_id = g.runtime_stats_interval_id AND r.execution_type = g.execution_type
                            AND r.IntervalStartUtc = g.IntervalStartUtc);
        SET @RowsLoaded = @@ROWCOUNT;

        INSERT perf.QdsWaitStats
        SELECT g.* FROM perf.Stg_QdsWaitStats AS g
        WHERE g.ServerID = @ServerID AND g.DatabaseName = @DatabaseName
          AND NOT EXISTS (SELECT 1 FROM perf.QdsWaitStats AS w
                          WHERE w.ServerID = g.ServerID AND w.DatabaseName = g.DatabaseName AND w.plan_id = g.plan_id
                            AND w.runtime_stats_interval_id = g.runtime_stats_interval_id AND w.execution_type = g.execution_type
                            AND w.wait_category = g.wait_category AND w.IntervalStartUtc = g.IntervalStartUtc);

        IF @max IS NOT NULL
            EXEC DBA.usp_Watermark_Set @ServerID, @DatabaseName, 'QDS', @max;
        ELSE IF @Reset = 1
            EXEC DBA.usp_Watermark_Set @ServerID, @DatabaseName, 'QDS', 0;

        DELETE perf.Stg_QdsRuntimeStats WHERE ServerID = @ServerID AND DatabaseName = @DatabaseName;
        DELETE perf.Stg_QdsWaitStats    WHERE ServerID = @ServerID AND DatabaseName = @DatabaseName;
        DELETE perf.Stg_QdsPlan         WHERE ServerID = @ServerID AND DatabaseName = @DatabaseName;
        DELETE perf.Stg_QdsPlanXml      WHERE ServerID = @ServerID AND DatabaseName = @DatabaseName;
        DELETE perf.Stg_QdsQuery        WHERE ServerID = @ServerID AND DatabaseName = @DatabaseName;
        DELETE perf.Stg_QdsQueryText    WHERE ServerID = @ServerID AND DatabaseName = @DatabaseName;

        COMMIT;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;
        THROW;
    END CATCH
END;