namespace TCMSDAL.QueryBuilders;

/// <summary>
/// SQL for TCMS Requirement Traceability.
/// All queries run against the TCMS database via IQueryExecutor.
///
/// Dual-source strategy:
///   GetByScreen merges MTCMSSCREENLINK (explicit admin links) with the Tags field
///   using the @FE:{route} prefix convention, so the widget works from day one
///   without any admin setup.
///
/// Required indexes (created by 20260606_TCMS_Traceability_Schema.sql):
///   IX_MTCMSSCREENLINK_ROUTE(SCREENROUTE)
///   IX_MTCMSSCREENLINK_TC(TESTCASEID)
///   IX_MTCMSAPILINK_OP(APIOPERATIONID) WHERE NOT NULL
///   IX_MTCMSAPILINK_TC(TESTCASEID)
/// </summary>
public static class TCMSTraceabilityQB
{
    // ── Screen link CRUD ──────────────────────────────────────────────────────

    public const string INSERT_SCREEN_LINK = @"
        INSERT INTO MTCMSSCREENLINK
            (TESTCASEID, SCREENROUTE, SCREENTITLE, MODULENAME, CREATEDBYID, CREATEDON)
        OUTPUT INSERTED.SCREENLINKID
        VALUES
            (@TestCaseId, @ScreenRoute, @ScreenTitle, @ModuleName, @CreatedById, GETDATE())";

    public const string DELETE_SCREEN_LINK = @"
        DELETE FROM MTCMSSCREENLINK
        WHERE  SCREENLINKID = @ScreenLinkId";

    public const string GET_SCREEN_LINKS_FOR_TESTCASE = @"
        SELECT SCREENLINKID  AS ScreenLinkId,
               TESTCASEID   AS TestCaseId,
               SCREENROUTE  AS ScreenRoute,
               SCREENTITLE  AS ScreenTitle,
               MODULENAME   AS ModuleName,
               CREATEDBYID  AS CreatedById
        FROM   MTCMSSCREENLINK
        WHERE  TESTCASEID = @TestCaseId
        ORDER  BY SCREENTITLE";

    // ── API link CRUD ─────────────────────────────────────────────────────────

    public const string INSERT_API_LINK = @"
        INSERT INTO MTCMSAPILINK
            (TESTCASEID, APIOPERATIONID, APIPATH, HTTPMETHOD, CREATEDBYID, CREATEDON)
        OUTPUT INSERTED.APILINKID
        VALUES
            (@TestCaseId, @ApiOperationId, @ApiPath, @HttpMethod, @CreatedById, GETDATE())";

    public const string DELETE_API_LINK = @"
        DELETE FROM MTCMSAPILINK
        WHERE  APILINKID = @ApiLinkId";

    public const string GET_API_LINKS_FOR_TESTCASE = @"
        SELECT APILINKID      AS ApiLinkId,
               TESTCASEID     AS TestCaseId,
               APIOPERATIONID AS ApiOperationId,
               APIPATH        AS ApiPath,
               HTTPMETHOD     AS HttpMethod,
               CREATEDBYID    AS CreatedById
        FROM   MTCMSAPILINK
        WHERE  TESTCASEID = @TestCaseId
        ORDER  BY HTTPMETHOD, APIPATH";

    // ── GetByScreen — dual source (link table UNION tag prefix) ──────────────

    /// <summary>
    /// Returns slim test case rows for a given FE screen route.
    /// Priority: link-table rows (with ScreenLinkId) come first; tag-convention rows
    /// (@FE:{route}) are included only when no explicit link exists, so each TC appears once.
    /// Steps loaded separately via GET_STEPS_FOR_CASES.
    /// </summary>
    public const string GET_TESTCASES_BY_SCREEN = @"
        -- Explicitly linked test cases — include ScreenLinkId so the FE can unlink
        SELECT sl.SCREENLINKID  AS ScreenLinkId,
               NULL             AS ApiLinkId,
               tc.TESTCASEID    AS TestCaseId,
               tc.TCID          AS TcId,
               tc.TITLE         AS Title,
               tc.PRIORITY      AS Priority,
               tc.TESTTYPE      AS TestType,
               tc.EXECUTIONTYPE AS ExecutionType,
               tc.LASTRUNSTATUS AS LastRunStatus,
               tc.LASTRUNDATE   AS LastRunDate
        FROM   MTCMSTESTCASE tc
        JOIN   MTCMSSCREENLINK sl ON sl.TESTCASEID = tc.TESTCASEID
        WHERE  sl.SCREENROUTE = @ScreenRoute
          AND  tc.STATUS      = 'Active'

        UNION ALL

        -- Tag-convention rows included only when no explicit link table row exists
        SELECT NULL             AS ScreenLinkId,
               NULL             AS ApiLinkId,
               tc.TESTCASEID    AS TestCaseId,
               tc.TCID          AS TcId,
               tc.TITLE         AS Title,
               tc.PRIORITY      AS Priority,
               tc.TESTTYPE      AS TestType,
               tc.EXECUTIONTYPE AS ExecutionType,
               tc.LASTRUNSTATUS AS LastRunStatus,
               tc.LASTRUNDATE   AS LastRunDate
        FROM   MTCMSTESTCASE tc
        WHERE  tc.TAGS  LIKE '%@FE:' + @ScreenRoute + '%'
          AND  tc.STATUS = 'Active'
          AND  NOT EXISTS (
                   SELECT 1 FROM MTCMSSCREENLINK sl2
                   WHERE  sl2.TESTCASEID  = tc.TESTCASEID
                     AND  sl2.SCREENROUTE = @ScreenRoute
               )

        ORDER BY Priority, TcId";

    // ── GetByApiOperation ─────────────────────────────────────────────────────

    public const string GET_TESTCASES_BY_API_OPERATION = @"
        SELECT al.APILINKID     AS ApiLinkId,
               NULL             AS ScreenLinkId,
               tc.TESTCASEID    AS TestCaseId,
               tc.TCID          AS TcId,
               tc.TITLE         AS Title,
               tc.PRIORITY      AS Priority,
               tc.TESTTYPE      AS TestType,
               tc.EXECUTIONTYPE AS ExecutionType,
               tc.LASTRUNSTATUS AS LastRunStatus,
               tc.LASTRUNDATE   AS LastRunDate
        FROM   MTCMSTESTCASE tc
        JOIN   MTCMSAPILINK al ON al.TESTCASEID = tc.TESTCASEID
        WHERE  al.APIOPERATIONID = @ApiOperationId
          AND  tc.STATUS         = 'Active'
        ORDER BY Priority, TcId";

    // ── Steps (bulk load by case-id list, avoid N+1) ─────────────────────────

    /// <summary>
    /// Load steps for a set of test cases in one round-trip.
    /// Caller filters to relevant TestCaseIds using STRING_SPLIT on @Ids.
    /// Dapper maps by TestCaseId for client-side grouping.
    /// </summary>
    public const string GET_STEPS_FOR_CASES = @"
        SELECT s.TESTCASEID  AS TestCaseId,
               s.STEPTYPE    AS StepType,
               s.STEPTEXT    AS StepText
        FROM   MTCMSTESTSTEP s
        WHERE  s.TESTCASEID IN (
                   SELECT TRY_CAST(value AS INT)
                   FROM   STRING_SPLIT(@CaseIdsCsv, ',')
               )
        ORDER  BY s.TESTCASEID, s.STEPORDER";

    // ── Screen tree (left panel) ──────────────────────────────────────────────

    public const string GET_SCREEN_TREE = @"
        SELECT sl.SCREENROUTE  AS ScreenRoute,
               sl.SCREENTITLE  AS ScreenTitle,
               sl.MODULENAME   AS ModuleName,
               COUNT(DISTINCT sl.TESTCASEID) AS TotalCases
        FROM   MTCMSSCREENLINK sl
        JOIN   MTCMSTESTCASE tc ON tc.TESTCASEID = sl.TESTCASEID AND tc.STATUS = 'Active'
        GROUP  BY sl.SCREENROUTE, sl.SCREENTITLE, sl.MODULENAME
        ORDER  BY sl.MODULENAME, sl.SCREENTITLE";

    // ── Coverage gaps ─────────────────────────────────────────────────────────

    /// <summary>
    /// Registered screens with zero active test cases.
    /// All registered routes come from the link table; orphaned routes (no active cases)
    /// appear here so QA can see where coverage has dropped to zero.
    /// </summary>
    public const string GET_SCREEN_GAPS = @"
        SELECT sl.SCREENROUTE  AS Route,
               sl.SCREENTITLE  AS Title,
               sl.MODULENAME   AS ModuleName,
               COUNT(DISTINCT CASE WHEN tc.STATUS = 'Active' THEN tc.TESTCASEID END) AS LinkedCases,
               'Screen'        AS GapType
        FROM   MTCMSSCREENLINK sl
        LEFT   JOIN MTCMSTESTCASE tc ON tc.TESTCASEID = sl.TESTCASEID
        GROUP  BY sl.SCREENROUTE, sl.SCREENTITLE, sl.MODULENAME
        HAVING COUNT(DISTINCT CASE WHEN tc.STATUS = 'Active' THEN tc.TESTCASEID END) = 0
        ORDER  BY sl.MODULENAME, sl.SCREENTITLE";

    public const string GET_API_GAPS = @"
        SELECT al.APIPATH   AS Route,
               al.APIPATH   AS Title,
               al.HTTPMETHOD AS ModuleName,
               COUNT(DISTINCT CASE WHEN tc.STATUS = 'Active' THEN tc.TESTCASEID END) AS LinkedCases,
               'ApiOperation' AS GapType
        FROM   MTCMSAPILINK al
        LEFT   JOIN MTCMSTESTCASE tc ON tc.TESTCASEID = al.TESTCASEID
        GROUP  BY al.APIPATH, al.HTTPMETHOD
        HAVING COUNT(DISTINCT CASE WHEN tc.STATUS = 'Active' THEN tc.TESTCASEID END) = 0
        ORDER  BY al.APIPATH";

    // ── Coverage summary for a screen (used to build TCMSScreenCoverageDTO) ──

    public const string GET_SCREEN_SUMMARY = @"
        SELECT sl.SCREENROUTE   AS ScreenRoute,
               sl.SCREENTITLE   AS ScreenTitle,
               sl.MODULENAME    AS ModuleName,
               COUNT(DISTINCT tc.TESTCASEID)                                    AS TotalCases,
               COUNT(DISTINCT CASE WHEN tc.PRIORITY = 'P1' THEN tc.TESTCASEID END) AS P1Count,
               COUNT(DISTINCT CASE WHEN tc.PRIORITY = 'P2' THEN tc.TESTCASEID END) AS P2Count,
               COUNT(DISTINCT CASE WHEN tc.PRIORITY = 'P3' THEN tc.TESTCASEID END) AS P3Count,
               COUNT(DISTINCT CASE WHEN tc.LASTRUNSTATUS = 'Pass' THEN tc.TESTCASEID END) AS PassCount,
               COUNT(DISTINCT CASE WHEN tc.LASTRUNSTATUS = 'Fail' THEN tc.TESTCASEID END) AS FailCount,
               MAX(tc.LASTRUNDATE)                                              AS LastRunDate
        FROM   MTCMSSCREENLINK sl
        JOIN   MTCMSTESTCASE tc ON tc.TESTCASEID = sl.TESTCASEID AND tc.STATUS = 'Active'
        WHERE  sl.SCREENROUTE = @ScreenRoute
        GROUP  BY sl.SCREENROUTE, sl.SCREENTITLE, sl.MODULENAME";
}