namespace SwDAL.Query.Provisioning;

public static class ProvisioningLogQB
{
    // Required index: IX_LSWPROVISIONINGLOG_TENANTID ON SW.LSWPROVISIONINGLOG (TENANTID, CLIENTDBID, CREATEDON DESC)
    public const string LOG_INSERT = @"
        INSERT INTO SW.LSWPROVISIONINGLOG
            (CLIENTDBID, PACKAGEID, DDLSCRIPTID, SYNCGROUPID, LOGTYPE, LOGSTATUS, MESSAGE, ERRORMESSAGE, CREATEDON, CREATEDBYID, TENANTID)
        VALUES
            (@ClientDbId, @PackageId, @DdlScriptId, @SyncGroupId, @LogType, @LogStatus, @Message, @ErrorMessage, @CreatedOn, @CreatedById, @TenantId)";

    // "Applied" = every DDL line of a package has its MOST RECENT log row (by CREATEDON/LOGID,
    // so a later retry supersedes an earlier failure) = Success. Among fully-applied packages,
    // picks the one whose completion (MAX success CREATEDON) is most recent — i.e. what actually
    // finished applying last, not what SEQUENCEINCHAIN happens to say (that field is cross-
    // lineage execution order, not intra-lineage recency — see UpgradePackageQB.GET_PROVISIONING_CHAIN).
    // Required index: existing IX_LSWPROVISIONINGLOG_TENANTID ON (TENANTID, CLIENTDBID, CREATEDON DESC).
    public const string GET_MOST_RECENTLY_APPLIED_PACKAGE = @"
        ;WITH LatestLogPerLine AS (
            SELECT l.PACKAGEID, l.DDLSCRIPTID, l.LOGSTATUS, l.CREATEDON,
                   ROW_NUMBER() OVER (PARTITION BY l.PACKAGEID, l.DDLSCRIPTID
                                       ORDER BY l.CREATEDON DESC, l.LOGID DESC) AS rn
            FROM   SW.LSWPROVISIONINGLOG l
            WHERE  l.CLIENTDBID = @ClientDbId
              AND  l.TENANTID   = @TenantId
              AND  l.LOGTYPE    = 0   -- DdlExecution
              AND  l.PACKAGEID  <> -1 -- exclude sentinel/orphan rows
        ),
        PackageCompletion AS (
            -- MSWUPGRADEPACKAGEDDL rows are owned by whichever tenant authored the package
            -- template (a shared definition applied across many tenants' client databases,
            -- e.g. TENANTID=1 for a platform-authored template) — NOT by the tenant being
            -- queried here. Tenant scoping for THIS query is already fully enforced by
            -- LatestLogPerLine's own WHERE (CLIENTDBID + TenantId); filtering pd by @TenantId
            -- as well incorrectly excluded every package whose authoring tenant differed from
            -- the querying tenant, i.e. every real cross-tenant template package.
            SELECT pd.PACKAGEID,
                   COUNT(*) AS TotalLines,
                   SUM(CASE WHEN ll.rn = 1 AND ll.LOGSTATUS = 0 THEN 1 ELSE 0 END) AS SucceededLines,
                   MAX(CASE WHEN ll.rn = 1 AND ll.LOGSTATUS = 0 THEN ll.CREATEDON END) AS LastSuccessOn
            FROM   SW.MSWUPGRADEPACKAGEDDL pd
            INNER JOIN LatestLogPerLine ll
                    ON ll.PACKAGEID = pd.PACKAGEID AND ll.DDLSCRIPTID = pd.DDLSCRIPTID AND ll.rn = 1
            GROUP  BY pd.PACKAGEID
        )
        SELECT TOP 1
               p.PACKAGEID           AS PackageId,
               p.PACKAGECODE         AS PackageCode,
               p.RELEASEVERSION      AS ReleaseVersion,
               p.SUPERSEDESPACKAGEID AS SupersedesPackageId,
               p.DBMODELID           AS DbModelId
        FROM   PackageCompletion pc
        INNER JOIN SW.MSWUPGRADEPACKAGE p ON p.PACKAGEID = pc.PACKAGEID
        WHERE  pc.TotalLines = pc.SucceededLines AND pc.TotalLines > 0
        ORDER BY pc.LastSuccessOn DESC";
}