namespace TCMSDAL.QueryBuilders;

/// <summary>
/// SQL constants for MTCMSTESTCASE + MTCMSTESTSTEP.
/// Required indexes:
///   IX_MTCMSTESTCASE_MODULE   (TENANTID, MODULEID, ISACTIVE)
///   IX_MTCMSTESTCASE_TCID     (TENANTID, TCID)             -- unique
///   IX_MTCMSTESTCASE_PIESEEN  (TENANTID, PIESCENID)        -- for import dedup
///   IX_MTCMSTESTSTEP_TC       (TESTCASEID, STEPORDER)
/// </summary>
public static class TCMSTestCaseQB
{
    // ── Single record ───────────────────────────────────────────────────────

    public const string GET_TESTCASE = @"
        SELECT TC.TESTCASEID       AS TestCaseId,
               TC.TCID             AS TcId,
               TC.TITLE            AS Title,
               TC.DESCRIPTION      AS Description,
               TC.MODULEID         AS ModuleId,
               M.MODULENAME        AS ModuleName,
               M.MODULECODE        AS ModuleCode,
               TC.FEATUREID        AS FeatureId,
               F.FEATURENAME       AS FeatureName,
               TC.PRIORITY         AS Priority,
               TC.TESTTYPE         AS TestType,
               TC.EXECUTIONTYPE    AS ExecutionType,
               TC.SCENARIOTYPE     AS ScenarioType,
               TC.STATUS           AS Status,
               TC.PRECONDITIONS    AS Preconditions,
               TC.POSTCONDITIONS   AS PostConditions,
               TC.TAGS             AS Tags,
               TC.PIESCENID        AS PieScenId,
               TC.PIEVARIANTID     AS PieVariantId,
               TC.ISAUTOGENERATED  AS IsAutoGenerated,
               TC.LASTRUNSTATUS    AS LastRunStatus,
               TC.LASTRUNDATE      AS LastRunDate,
               TC.ISACTIVE         AS IsActive,
               TC.TENANTID         AS TenantId,
               TC.CREATEDBY        AS CreatedBy,
               TC.CREATEDDATE      AS CreatedDate,
               TC.MODIFIEDBY       AS ModifiedBy,
               TC.MODIFIEDDATE     AS ModifiedDate
        FROM   MTCMSTESTCASE TC
        JOIN   MTCMSMODULE  M ON M.MODULEID  = TC.MODULEID
        LEFT JOIN MTCMSFEATURE F ON F.FEATUREID = TC.FEATUREID
        WHERE  TC.TESTCASEID = @TestCaseId
        AND    TC.TENANTID   = @TenantId";

    public const string GET_STEPS = @"
        SELECT STEPID          AS StepId,
               TESTCASEID      AS TestCaseId,
               STEPORDER       AS StepOrder,
               STEPTYPE        AS StepType,
               STEPTEXT        AS StepText,
               EXPECTEDRESULT  AS ExpectedResult,
               TESTDATA        AS TestData,
               ISACTIVE        AS IsActive,
               TENANTID        AS TenantId
        FROM   MTCMSTESTSTEP
        WHERE  TESTCASEID = @TestCaseId
        AND    ISACTIVE   = 1
        ORDER BY STEPORDER";

    // ── List ────────────────────────────────────────────────────────────────

    public const string GET_TESTCASE_LIST = @"
        SELECT TC.TESTCASEID      AS TestCaseId,
               TC.TCID            AS TcId,
               TC.TITLE           AS Title,
               M.MODULENAME       AS ModuleName,
               F.FEATURENAME      AS FeatureName,
               TC.PRIORITY        AS Priority,
               TC.TESTTYPE        AS TestType,
               TC.STATUS          AS Status,
               TC.LASTRUNSTATUS   AS LastRunStatus,
               TC.ISAUTOGENERATED AS IsAutoGenerated,
               TC.ISACTIVE        AS IsActive
        FROM   MTCMSTESTCASE TC
        JOIN   MTCMSMODULE   M ON M.MODULEID  = TC.MODULEID
        LEFT JOIN MTCMSFEATURE F ON F.FEATUREID = TC.FEATUREID
        WHERE  TC.TENANTID  = @TenantId
        AND    TC.MODULEID  = @ModuleId
        AND    (@Priority   IS NULL OR TC.PRIORITY  = @Priority)
        AND    (@TestType   IS NULL OR TC.TESTTYPE  = @TestType)
        AND    (@Status     IS NULL OR TC.STATUS    = @Status)
        AND    (@IsActive   IS NULL OR TC.ISACTIVE  = @IsActive)
        ORDER BY TC.PRIORITY, TC.TCID";

    public const string GET_SELECT_LIST = @"
        SELECT TESTCASEID AS TestCaseId,
               TCID       AS TcId,
               TITLE      AS Title
        FROM   MTCMSTESTCASE
        WHERE  TENANTID = @TenantId
        AND    MODULEID = @ModuleId
        AND    ISACTIVE = 1
        ORDER BY TCID";

    // ── Save / Update ────────────────────────────────────────────────────────

    public const string SAVE_TESTCASE = @"
        INSERT INTO MTCMSTESTCASE
            (TCID, TITLE, DESCRIPTION, MODULEID, FEATUREID,
             PRIORITY, TESTTYPE, EXECUTIONTYPE, SCENARIOTYPE, STATUS,
             PRECONDITIONS, POSTCONDITIONS, TAGS,
             PIESCENID, PIEVARIANTID, ISAUTOGENERATED,
             ISACTIVE, TENANTID, CREATEDBY, CREATEDDATE)
        VALUES
            (@TcId, @Title, @Description, @ModuleId, @FeatureId,
             @Priority, @TestType, @ExecutionType, @ScenarioType, @Status,
             @Preconditions, @PostConditions, @Tags,
             @PieScenId, @PieVariantId, @IsAutoGenerated,
             1, @TenantId, @CreatedBy, GETUTCDATE())";

    public const string UPDATE_TESTCASE = @"
        UPDATE MTCMSTESTCASE
        SET    TITLE           = @Title,
               DESCRIPTION     = @Description,
               MODULEID        = @ModuleId,
               FEATUREID       = @FeatureId,
               PRIORITY        = @Priority,
               TESTTYPE        = @TestType,
               EXECUTIONTYPE   = @ExecutionType,
               SCENARIOTYPE    = @ScenarioType,
               STATUS          = @Status,
               PRECONDITIONS   = @Preconditions,
               POSTCONDITIONS  = @PostConditions,
               TAGS            = @Tags,
               ISACTIVE        = @IsActive,
               MODIFIEDBY      = @ModifiedBy,
               MODIFIEDDATE    = GETUTCDATE()
        WHERE  TESTCASEID = @TestCaseId
        AND    TENANTID   = @TenantId";

    public const string UPDATE_LAST_RUN_STATUS = @"
        UPDATE MTCMSTESTCASE
        SET    LASTRUNSTATUS  = @LastRunStatus,
               LASTRUNDATE    = GETUTCDATE()
        WHERE  TESTCASEID     = @TestCaseId
        AND    TENANTID       = @TenantId";

    public const string DELETE_TESTCASE = @"
        UPDATE MTCMSTESTCASE
        SET    ISACTIVE    = 0,
               MODIFIEDBY  = @ModifiedBy,
               MODIFIEDDATE = GETUTCDATE()
        WHERE  TESTCASEID = @TestCaseId
        AND    TENANTID   = @TenantId";

    // ── Steps ────────────────────────────────────────────────────────────────

    public const string SAVE_STEP = @"
        INSERT INTO MTCMSTESTSTEP
            (TESTCASEID, STEPORDER, STEPTYPE, STEPTEXT, EXPECTEDRESULT, TESTDATA, ISACTIVE, TENANTID)
        VALUES
            (@TestCaseId, @StepOrder, @StepType, @StepText, @ExpectedResult, @TestData, 1, @TenantId)";

    public const string DELETE_STEPS_FOR_TESTCASE = @"
        DELETE FROM MTCMSTESTSTEP
        WHERE  TESTCASEID = @TestCaseId
        AND    TENANTID   = @TenantId";

    // ── Auto-number helpers ──────────────────────────────────────────────────

    /// <summary>
    /// Returns the next sequence for a module code so BLL can form TC-[MOD]-[SEQ].
    /// Uses an advisory lock pattern: SELECT + UPDATE in same transaction.
    /// </summary>
    public const string GET_NEXT_SEQUENCE = @"
        SELECT ISNULL(MAX(CAST(SUBSTRING(TCID, LEN(@Prefix)+2, 10) AS INT)), 0) + 1
        FROM   MTCMSTESTCASE
        WHERE  TENANTID = @TenantId
        AND    TCID LIKE @Prefix + '-%'";

    // ── PIE import dedup check ───────────────────────────────────────────────

    public const string CHECK_PIESCENID_EXISTS = @"
        SELECT TESTCASEID FROM MTCMSTESTCASE
        WHERE  PIESCENID = @PieScenId
        AND    TENANTID  = @TenantId";
}