namespace FrameworkDAL.Query.EIPConversation.EIPCapabilityActionMap
{
    // MEIPCAPABILITYACTIONMAP DDL columns (only 5 — no audit fields, no TENANTID):
    //   CAPABILITYACTIONMAPID  INT NOT NULL PK
    //   CAPABILITYID           INT NOT NULL FK → MEIPCAPABILITY
    //   SLNO                   INT NOT NULL  (display/execution order, formerly SEQUENCE)
    //   ACTIONID               INT NOT NULL FK → MEIPACTIONREGISTRY
    //   ISENABLED              TINYINT NOT NULL
    //
    // Because DTO carries CAPABILITYCODE/ACTIONCODE (string codes, not integer IDs),
    // INSERT and UPDATE use sub-selects to resolve codes → IDs at write time.
    // GET queries filter by CAPABILITYCODE via JOIN (matching the DAL's CapabilityCode param).
    public static class EIPCapabilityActionMapQB
    {
        public const string GET_EIPCAPABILITYACTIONMAP = @"
            SELECT
                M.CAPABILITYACTIONMAPID  AS MapId,
                C.CAPABILITYCODE         AS CapabilityCode,
                A.ACTIONCODE             AS ActionCode,
                A.ACTIONNAME             AS ActionName,
                M.SLNO                   AS Sequence,
                M.ISENABLED              AS IsEnabled
            FROM MEIPCAPABILITYACTIONMAP M
            INNER JOIN MEIPCAPABILITY    C ON M.CAPABILITYID = C.CAPABILITYID
            INNER JOIN MEIPACTIONREGISTRY A ON M.ACTIONID    = A.ACTIONID
            WHERE C.CAPABILITYCODE = @CapabilityCode
            ORDER BY M.SLNO;";


        public const string GET_SELECTLIST_EIPCAPABILITYACTIONMAP = @"
            SELECT
                M.CAPABILITYACTIONMAPID AS Id,
                A.ACTIONCODE            AS Code,
                M.SLNO                  AS Name
            FROM MEIPCAPABILITYACTIONMAP M
            INNER JOIN MEIPCAPABILITY    C ON M.CAPABILITYID = C.CAPABILITYID
            INNER JOIN MEIPACTIONREGISTRY A ON M.ACTIONID    = A.ACTIONID
            WHERE C.CAPABILITYCODE = @CapabilityCode
            ORDER BY M.SLNO;";


        // Sub-selects resolve CapabilityCode → CAPABILITYID and ActionCode → ACTIONID.
        // If either code is not found the INSERT produces no rows (not an error) —
        // callers must validate existence before saving.
        public const string SAVE_EIPCAPABILITYACTIONMAP = @"
            INSERT INTO MEIPCAPABILITYACTIONMAP
                (CAPABILITYACTIONMAPID, CAPABILITYID, SLNO, ACTIONID, ISENABLED)
            SELECT
                @MapId,
                C.CAPABILITYID,
                @Sequence,
                A.ACTIONID,
                1
            FROM        MEIPCAPABILITY     C
            CROSS JOIN  MEIPACTIONREGISTRY A
            WHERE C.CAPABILITYCODE = @CapabilityCode
              AND A.ACTIONCODE     = @ActionCode;";


        public const string UPDATE_EIPCAPABILITYACTIONMAP = @"
            UPDATE MEIPCAPABILITYACTIONMAP
            SET ACTIONID  = (SELECT ACTIONID FROM MEIPACTIONREGISTRY WHERE ACTIONCODE = @ActionCode),
                SLNO      = @Sequence,
                ISENABLED = @IsEnabled
            WHERE CAPABILITYACTIONMAPID = @MapId;";


        public const string DELETE_EIPCAPABILITYACTIONMAP = @"
            DELETE FROM MEIPCAPABILITYACTIONMAP
            WHERE CAPABILITYACTIONMAPID = @MapId;";
    }
}