namespace TCMSDAL.QueryBuilders;

/// <summary>
/// SQL constants for PIE import + PIE sync tracking.
/// </summary>
public static class TCMSImportQB
{
    public const string GET_PIE_SYNC_STATUS = @"
        SELECT SYNCID          AS SyncId,
               MODULEID        AS ModuleId,
               PIEMODULEID     AS PieModuleId,
               LASTSYNCDATE    AS LastSyncDate,
               SCENARIOSIMPORTED AS ScenariosImported,
               TENANTID        AS TenantId
        FROM   MTCMSPIESYNC
        WHERE  MODULEID  = @ModuleId
        AND    TENANTID  = @TenantId";

    public const string UPSERT_PIE_SYNC = @"
        MERGE MTCMSPIESYNC AS T
        USING (SELECT @ModuleId AS MODULEID, @TenantId AS TENANTID) AS S
        ON (T.MODULEID = S.MODULEID AND T.TENANTID = S.TENANTID)
        WHEN MATCHED THEN
            UPDATE SET LASTSYNCDATE       = GETUTCDATE(),
                       SCENARIOSIMPORTED  = @ScenariosImported,
                       PIEMODULEID        = @PieModuleId
        WHEN NOT MATCHED THEN
            INSERT (MODULEID, PIEMODULEID, LASTSYNCDATE, SCENARIOSIMPORTED, TENANTID)
            VALUES (@ModuleId, @PieModuleId, GETUTCDATE(), @ScenariosImported, @TenantId);";

    /// <summary>Fetch rows for UAT checklist export (joined with run results if run specified).</summary>
    public const string GET_UAT_CHECKLIST_ROWS = @"
        SELECT TC.TCID          AS TcId,
               ISNULL(F.FEATURENAME, 'General') AS Feature,
               TC.TITLE         AS Scenario,
               TC.PRIORITY      AS Priority,
               TC.TESTTYPE      AS TestType,
               ISNULL(
                 STUFF((
                   SELECT ' | ' + S.STEPTEXT
                   FROM MTCMSTESTSTEP S
                   WHERE S.TESTCASEID = TC.TESTCASEID AND S.STEPTYPE = 'Given' AND S.ISACTIVE=1
                   ORDER BY S.STEPORDER FOR XML PATH('')), 1, 3, ''), '') AS Given,
               ISNULL(
                 STUFF((
                   SELECT ' | ' + S.STEPTEXT
                   FROM MTCMSTESTSTEP S
                   WHERE S.TESTCASEID = TC.TESTCASEID AND S.STEPTYPE = 'When' AND S.ISACTIVE=1
                   ORDER BY S.STEPORDER FOR XML PATH('')), 1, 3, ''), '') AS When,
               ISNULL(
                 STUFF((
                   SELECT ' | ' + S.STEPTEXT
                   FROM MTCMSTESTSTEP S
                   WHERE S.TESTCASEID = TC.TESTCASEID AND S.STEPTYPE = 'Then' AND S.ISACTIVE=1
                   ORDER BY S.STEPORDER FOR XML PATH('')), 1, 3, ''), '') AS Then,
               RR.EXECUTEDBY    AS Tester,
               RR.RESULT        AS Status,
               RR.NOTES         AS Notes
        FROM   MTCMSTESTCASE TC
        LEFT JOIN MTCMSFEATURE F ON F.FEATUREID = TC.FEATUREID
        LEFT JOIN TTCMSRUNRESULT RR ON RR.TESTCASEID = TC.TESTCASEID
                  AND (@TestRunId IS NULL OR RR.TESTRUNID = @TestRunId)
        WHERE  TC.MODULEID = @ModuleId
        AND    TC.TENANTID = @TenantId
        AND    TC.ISACTIVE = 1
        ORDER BY TC.PRIORITY, TC.TCID";
}