namespace PIEDAL.Query.QC;

/// <summary>
/// SQL constants for PIE QC sub-domain (read-only).
/// Phase 2 — behavior matrix + test template retrieval.
/// </summary>
public static class PIEQcQB
{
    // ── Behavior Matrix ───────────────────────────────────────────────────────
    //
    // Required indexes (PostgreSQL):
    //   CREATE INDEX IF NOT EXISTS ix_mpievariant_moduleid    ON MPIEVARIANT (PIEMODULEID, TENANTID);
    //   CREATE INDEX IF NOT EXISTS ix_mpievariantimp_varid    ON MPIEVARIANTIMPLICATION (PIEVARIANTID);
    //
    public const string GET_BEHAVIOR_MATRIX = @"
        SELECT  v.PIEVARIANTID                                              AS VariantId,
                v.PIEVARIANTCODE                                            AS VariantCode,
                v.PIEVARIANTNAME                                            AS VariantName,
                v.DESCRIPTION                                               AS VariantDescription,
                COUNT(vi.PIEVARIANTIMPLICATIONID)                           AS ImplicationCount,
                COALESCE(SUM(vi.EFFORTDELTAHOURS), 0)                       AS TotalEffortDelta,
                MAX(CASE WHEN vi.RISKNOTE IS NOT NULL
                              AND  vi.RISKNOTE <> ''   THEN 1 ELSE 0 END) AS HasRiskNotes,
                m.PIEMODULEID                                               AS ModuleId
        FROM    MPIEVARIANT v
        JOIN    MPIEMODULE  m  ON m.PIEMODULEID  = v.PIEMODULEID
                               AND m.TENANTID    = @TenantId
        LEFT JOIN MPIEVARIANTIMPLICATION vi ON vi.PIEVARIANTID = v.PIEVARIANTID
        WHERE   m.PIEMODULEID = @ModuleId
        AND     v.ISACTIVE    = 1
        GROUP BY v.PIEVARIANTID, v.PIEVARIANTCODE, v.PIEVARIANTNAME,
                 v.DESCRIPTION, m.PIEMODULEID
        ORDER BY v.DISPLAYORDER, v.PIEVARIANTCODE";

    public const string GET_BEHAVIOR_MATRIX_HEADER = @"
        SELECT  m.PIEMODULEID   AS ModuleId,
                m.PIEMODULENAME AS ModuleName,
                COUNT(DISTINCT v.PIEVARIANTID)                  AS TotalBehaviors,
                COUNT(DISTINCT CASE
                    WHEN vi.IMPLICATIONTYPE = 1 THEN vi.REFID
                    ELSE NULL END)                              AS TotalConfigItems
        FROM    MPIEMODULE m
        LEFT JOIN MPIEVARIANT           v  ON v.PIEMODULEID   = m.PIEMODULEID
                                           AND v.ISACTIVE      = 1
        LEFT JOIN MPIEVARIANTIMPLICATION vi ON vi.PIEVARIANTID = v.PIEVARIANTID
        WHERE   m.PIEMODULEID = @ModuleId
        AND     m.TENANTID    = @TenantId
        GROUP BY m.PIEMODULEID, m.PIEMODULENAME";

    // ── Test Templates (UAT Scenarios) ────────────────────────────────────────
    //
    // Required indexes:
    //   CREATE INDEX IF NOT EXISTS ix_mpieuatscen_moduleid ON MPIEUATSCENARIO (PIEMODULEID, ISACTIVE, TENANTID);
    //
    public const string GET_TEST_TEMPLATES = @"
        SELECT  us.PIEUATSCENARIOID     AS ScenId,
                us.SCENARIOTITLE        AS Title,
                us.PRECONDITIONS        AS Preconditions,
                us.STEPS                AS Steps,
                us.EXPECTEDRESULT       AS ExpectedResult,
                us.COMPLEXITY           AS ComplexityLevel,
                m.PIEMODULEID           AS ModuleId,
                m.PIEMODULENAME         AS ModuleName
        FROM    MPIEUATSCENARIO us
        JOIN    MPIEMODULE m ON m.PIEMODULEID = us.PIEMODULEID
                             AND m.TENANTID   = @TenantId
        WHERE   us.PIEMODULEID = @ModuleId
        AND     us.STATUS      = 1
        ORDER BY us.COMPLEXITY, us.SCENARIOTITLE";
}