namespace GB5Shared.GOP.Qualifier;

// SQL constants for TQUALIFIERBULKRUN / LQUALIFIERBULKFINDING — column names verified
// against GOP_DDL.sql plus 20260907_GOP_QualifierBulkRun_Schema_Fix_SqlServer.sql (the
// STAGE/SCOPE addition). QUALIFIERBULKRUNID reads as a plain INT (no IDENTITY keyword) in
// GOP_DDL.sql's own CREATE TABLE text, but live-verified against GB5DEMO's actual
// sys.columns.is_identity: it IS an IDENTITY column there — same "DDL text lied, DB is the
// source of truth" bug class hit earlier this session for MQUALIFIERRULEGROUP/CONDITION/etc.
// INSERT_RUN therefore does NOT supply QUALIFIERBULKRUNID and returns SCOPE_IDENTITY() —
// called via IQueryExecutor.ExecuteIdentityAsync (int), not AutoNumber. QUALIFIERBULKFINDINGID
// IS IDENTITY too, but findings are written via IQueryExecutor.BulkInsertAsync (a batch, not
// one-row-at-a-time), so no individual identity value is ever needed back for those.
public static class QualifierBulkRunQB
{
    public const string INSERT_RUN = @"
        INSERT INTO TQUALIFIERBULKRUN
            (QUALIFIERID, ENTITYCODE, STAGE, SCOPE, RUNSTATUS,
             TOTALRECORDS, TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
        VALUES
            (@QualifierId, @EntityCode, @Stage, @Scope, 0,
             @TotalRecords, @TenantId, @CreatedById, GETDATE(), @CreatedById, GETDATE());
        SELECT CAST(SCOPE_IDENTITY() AS INT);";

    // First call (from the background task, right before the per-record loop starts)
    // flips Pending(0)->Running(1) and stamps STARTEDON — mirrors IceImportRunQB.
    // UPDATE_STAGE's identical Queued->Running-on-claim convention.
    public const string MARK_RUNNING = @"
        UPDATE TQUALIFIERBULKRUN
        SET    RUNSTATUS  = 1,
               STARTEDON  = GETDATE(),
               MODIFIEDON = GETDATE()
        WHERE  QUALIFIERBULKRUNID = @QualifierBulkRunId
          AND  TENANTID           = @TenantId";

    public const string UPDATE_PROGRESS = @"
        UPDATE TQUALIFIERBULKRUN
        SET    PROCESSEDRECORDS = @ProcessedRecords,
               ERRORCOUNT       = @ErrorCount,
               WARNINGCOUNT     = @WarningCount,
               MODIFIEDON       = GETDATE()
        WHERE  QUALIFIERBULKRUNID = @QualifierBulkRunId
          AND  TENANTID           = @TenantId";

    public const string COMPLETE_RUN = @"
        UPDATE TQUALIFIERBULKRUN
        SET    RUNSTATUS        = @RunStatus,
               PROCESSEDRECORDS = @ProcessedRecords,
               ERRORCOUNT       = @ErrorCount,
               WARNINGCOUNT     = @WarningCount,
               ERRORMESSAGE     = @ErrorMessage,
               COMPLETEDON      = GETDATE(),
               MODIFIEDON       = GETDATE()
        WHERE  QUALIFIERBULKRUNID = @QualifierBulkRunId
          AND  TENANTID           = @TenantId";

    // Covering index note: an index on (ENTITYCODE, CREATEDON DESC) INCLUDE (RUNSTATUS,
    // TENANTID) would serve this well at scale — not added yet since the table has zero
    // rows in production today; add alongside real usage if this becomes a hot path.
    public const string GET_HISTORY_PAGE = @"
        SELECT   br.QUALIFIERBULKRUNID AS QualifierBulkRunId, br.QUALIFIERID AS QualifierId,
                 qd.QUALIFIERCODE AS QualifierCode,
                 br.ENTITYCODE AS EntityCode, br.STAGE AS Stage, br.SCOPE AS Scope,
                 br.RUNSTATUS AS RunStatus, br.TOTALRECORDS AS TotalRecords,
                 br.PROCESSEDRECORDS AS ProcessedRecords, br.ERRORCOUNT AS ErrorCount,
                 br.WARNINGCOUNT AS WarningCount, br.STARTEDON AS StartedOn,
                 br.COMPLETEDON AS CompletedOn, br.TENANTID AS TenantId,
                 br.CREATEDBYID AS CreatedById, br.CREATEDON AS CreatedOn
        FROM     TQUALIFIERBULKRUN br
        LEFT JOIN MQUALIFIERDEFINITION qd
               ON qd.QUALIFIERID = br.QUALIFIERID AND qd.TENANTID = br.TENANTID
        WHERE    br.TENANTID   = @TenantId
          AND    br.ENTITYCODE = @EntityCode
        ORDER BY br.CREATEDON DESC, br.QUALIFIERBULKRUNID DESC
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;";

    public const string GET_HISTORY_COUNT = @"
        SELECT COUNT(*)
        FROM   TQUALIFIERBULKRUN
        WHERE  TENANTID = @TenantId AND ENTITYCODE = @EntityCode";

    public const string GET_BY_ID = @"
        SELECT   br.QUALIFIERBULKRUNID AS QualifierBulkRunId, br.QUALIFIERID AS QualifierId,
                 qd.QUALIFIERCODE AS QualifierCode,
                 br.ENTITYCODE AS EntityCode, br.STAGE AS Stage, br.SCOPE AS Scope,
                 br.RUNSTATUS AS RunStatus, br.TOTALRECORDS AS TotalRecords,
                 br.PROCESSEDRECORDS AS ProcessedRecords, br.ERRORCOUNT AS ErrorCount,
                 br.WARNINGCOUNT AS WarningCount, br.STARTEDON AS StartedOn,
                 br.COMPLETEDON AS CompletedOn, br.ERRORMESSAGE AS ErrorMessage,
                 br.TENANTID AS TenantId, br.CREATEDBYID AS CreatedById, br.CREATEDON AS CreatedOn
        FROM     TQUALIFIERBULKRUN br
        LEFT JOIN MQUALIFIERDEFINITION qd
               ON qd.QUALIFIERID = br.QUALIFIERID AND qd.TENANTID = br.TENANTID
        WHERE    br.QUALIFIERBULKRUNID = @QualifierBulkRunId
          AND    br.TENANTID           = @TenantId;";

    // Dapper's list-parameter expansion (connection.ExecuteAsync(sql, items, ...)) turns this
    // one statement into one execute per item under the hood -- see IQueryExecutor.BulkInsertAsync.
    public const string BULK_INSERT_FINDINGS = @"
        INSERT INTO LQUALIFIERBULKFINDING
            (QUALIFIERBULKRUNID, QUALIFIERID, QUALIFIERCODE, RECORDKEY,
             SEVERITY, FINDINGCODE, FINDINGMESSAGE, DOCUMENTPATH, TENANTID)
        VALUES
            (@QualifierBulkRunId, @QualifierId, @QualifierCode, @RecordKey,
             @Severity, @FindingCode, @FindingMessage, @DocumentPath, @TenantId);";

    public const string GET_FINDINGS_PAGE = @"
        SELECT   QUALIFIERBULKFINDINGID AS QualifierBulkFindingId, QUALIFIERBULKRUNID AS QualifierBulkRunId,
                 QUALIFIERID AS QualifierId, QUALIFIERCODE AS QualifierCode, RECORDKEY AS RecordKey,
                 SEVERITY AS Severity, FINDINGCODE AS FindingCode, FINDINGMESSAGE AS FindingMessage,
                 DOCUMENTPATH AS DocumentPath, CREATEDON AS CreatedOn
        FROM     LQUALIFIERBULKFINDING
        WHERE    QUALIFIERBULKRUNID = @QualifierBulkRunId
          AND    TENANTID           = @TenantId
          AND    (@Severity IS NULL OR SEVERITY = @Severity)
        ORDER BY QUALIFIERBULKFINDINGID
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;";

    public const string GET_FINDINGS_COUNT = @"
        SELECT COUNT(*)
        FROM   LQUALIFIERBULKFINDING
        WHERE  QUALIFIERBULKRUNID = @QualifierBulkRunId
          AND  TENANTID           = @TenantId
          AND  (@Severity IS NULL OR SEVERITY = @Severity)";
}