namespace FrameworkDAL.Query.EIPConversation.EIPCapabilityActionMapAdmin
{
    // MEIPCAPABILITYACTIONMAP has NO Version/Status/audit/TenantId columns (confirmed against
    // the live DDL) — Delete here is a real hard DELETE, and list/get scope by tenant via a
    // join to the parent MEIPCAPABILITY.
    public static class EIPCapabilityActionMapAdminQB
    {
        public const string GET_MAP_LIST = @"
            SELECT
                M.CAPABILITYACTIONMAPID AS CapabilityActionMapId,
                M.CAPABILITYID          AS CapabilityId,
                M.SLNO                  AS Slno,
                M.ACTIONID               AS ActionId,
                M.ISENABLED              AS IsEnabled,
                C.CAPABILITYCODE         AS CapabilityCode,
                A.ACTIONCODE             AS ActionCode
            FROM MEIPCAPABILITYACTIONMAP M
            INNER JOIN MEIPCAPABILITY     C ON M.CAPABILITYID = C.CAPABILITYID
            INNER JOIN MEIPACTIONREGISTRY A ON M.ACTIONID     = A.ACTIONID
            WHERE C.TENANTID = @TenantId
              AND C.STATUS   = 1
              AND (@CapabilityId = 0 OR M.CAPABILITYID = @CapabilityId)
            ORDER BY M.CAPABILITYID, M.SLNO";

        public const string GET_MAP = @"
            SELECT
                M.CAPABILITYACTIONMAPID AS CapabilityActionMapId,
                M.CAPABILITYID          AS CapabilityId,
                M.SLNO                  AS Slno,
                M.ACTIONID               AS ActionId,
                M.ISENABLED              AS IsEnabled,
                C.CAPABILITYCODE         AS CapabilityCode,
                A.ACTIONCODE             AS ActionCode
            FROM MEIPCAPABILITYACTIONMAP M
            INNER JOIN MEIPCAPABILITY     C ON M.CAPABILITYID = C.CAPABILITYID
            INNER JOIN MEIPACTIONREGISTRY A ON M.ACTIONID     = A.ACTIONID
            WHERE M.CAPABILITYACTIONMAPID = @CapabilityActionMapId
              AND C.TENANTID              = @TenantId
              AND C.STATUS                = 1";

        // CAPABILITYACTIONMAPID is a plain INT PK (NOT IDENTITY)
        public const string INSERT_MAP = @"
            INSERT INTO MEIPCAPABILITYACTIONMAP (
                CAPABILITYACTIONMAPID,
                CAPABILITYID,
                SLNO,
                ACTIONID,
                ISENABLED
            )
            VALUES (
                @CapabilityActionMapId,
                @CapabilityId,
                @Slno,
                @ActionId,
                @IsEnabled
            )";

        public const string UPDATE_MAP = @"
            UPDATE MEIPCAPABILITYACTIONMAP
            SET
                SLNO      = @Slno,
                ACTIONID  = @ActionId,
                ISENABLED = @IsEnabled
            WHERE CAPABILITYACTIONMAPID = @CapabilityActionMapId";

        // Real hard delete — no STATUS column to soft-delete against.
        public const string DELETE_MAP = @"
            DELETE FROM MEIPCAPABILITYACTIONMAP
            WHERE CAPABILITYACTIONMAPID = @CapabilityActionMapId";

        // No DB-enforced uniqueness on (CAPABILITYID, ACTIONID) in the DDL — this duplicate-check
        // is a BLL-level business rule to avoid wiring the same action to a capability twice.
        public const string EXISTS_CAPABILITY_ACTION = @"
            SELECT CASE WHEN EXISTS (
                SELECT 1 FROM MEIPCAPABILITYACTIONMAP
                WHERE CAPABILITYID = @CapabilityId
                  AND ACTIONID     = @ActionId
                  AND (@CapabilityActionMapId = 0 OR CAPABILITYACTIONMAPID <> @CapabilityActionMapId)
            ) THEN 1 ELSE 0 END";

        // FK existence checks — CapabilityId/ActionId are supplied as plain ints (no DB FK
        // violation message is admin-friendly), so validate up front in the BLL.
        public const string EXISTS_CAPABILITYID = @"
            SELECT CASE WHEN EXISTS (
                SELECT 1 FROM MEIPCAPABILITY WHERE CAPABILITYID = @CapabilityId AND STATUS = 1
            ) THEN 1 ELSE 0 END";

        public const string EXISTS_ACTIONID = @"
            SELECT CASE WHEN EXISTS (
                SELECT 1 FROM MEIPACTIONREGISTRY WHERE ACTIONID = @ActionId AND STATUS = 1
            ) THEN 1 ELSE 0 END";
    }
}