namespace FrameworkDAL.Query.EIPConversation.EIPActionRegistryAdmin
{
    // INDEX required on MEIPACTIONREGISTRY:
    //   IX_MEIPACTIONREGISTRY_TENANT_STATUS on (TENANTID, STATUS) INCLUDE (ACTIONCODE, ACTIONNAME)
    public static class EIPActionRegistryAdminQB
    {
        public const string GET_ACTIONREGISTRY_LIST = @"
            SELECT
                A.ACTIONID        AS ActionId,
                A.TENANTID        AS TenantId,
                A.ACTIONCODE      AS ActionCode,
                A.ACTIONNAME      AS ActionName,
                A.ASSEMBLYNAME    AS AssemblyName,
                A.CLASSNAME       AS ClassName,
                A.ISIDEMPOTENT    AS IsIdempotent,
                A.TIMEOUTMS       AS TimeoutMs,
                A.ACTIONVERSION   AS ActionVersion,
                A.ISACTIVE        AS IsActive,
                A.VERSION         AS Version,
                A.STATUS          AS Status,
                A.CREATEDBYID     AS CreatedById,
                CB.EMPLOYEENAME   AS CreatedByName,
                A.CREATEDON       AS CreatedOn,
                A.MODIFIEDBYID    AS ModifiedById,
                MB.EMPLOYEENAME   AS ModifiedByName,
                A.MODIFIEDON      AS ModifiedOn
            FROM MEIPACTIONREGISTRY A
            LEFT JOIN MEMPLOYEE CB ON A.CREATEDBYID = CB.EMPLOYEEID
            LEFT JOIN MEMPLOYEE MB ON A.MODIFIEDBYID = MB.EMPLOYEEID
            WHERE A.TENANTID = @TenantId
              AND A.STATUS   = 1
              AND (
                    @Search IS NULL
                    OR A.ACTIONCODE LIKE '%' + @Search + '%'
                    OR A.ACTIONNAME LIKE '%' + @Search + '%'
                  )
            ORDER BY A.ACTIONCODE ASC";

        public const string GET_ACTIONREGISTRY = @"
            SELECT
                A.ACTIONID        AS ActionId,
                A.TENANTID        AS TenantId,
                A.ACTIONCODE      AS ActionCode,
                A.ACTIONNAME      AS ActionName,
                A.ASSEMBLYNAME    AS AssemblyName,
                A.CLASSNAME       AS ClassName,
                A.ISIDEMPOTENT    AS IsIdempotent,
                A.TIMEOUTMS       AS TimeoutMs,
                A.ACTIONVERSION   AS ActionVersion,
                A.ISACTIVE        AS IsActive,
                A.VERSION         AS Version,
                A.STATUS          AS Status,
                A.CREATEDBYID     AS CreatedById,
                CB.EMPLOYEENAME   AS CreatedByName,
                A.CREATEDON       AS CreatedOn,
                A.MODIFIEDBYID    AS ModifiedById,
                MB.EMPLOYEENAME   AS ModifiedByName,
                A.MODIFIEDON      AS ModifiedOn
            FROM MEIPACTIONREGISTRY A
            LEFT JOIN MEMPLOYEE CB ON A.CREATEDBYID = CB.EMPLOYEEID
            LEFT JOIN MEMPLOYEE MB ON A.MODIFIEDBYID = MB.EMPLOYEEID
            WHERE A.ACTIONID = @ActionId
              AND A.TENANTID = @TenantId
              AND A.STATUS   = 1";

        // ACTIONID is a plain INT PK (NOT IDENTITY)
        public const string INSERT_ACTIONREGISTRY = @"
            INSERT INTO MEIPACTIONREGISTRY (
                ACTIONID,
                TENANTID,
                ACTIONCODE,
                ACTIONNAME,
                ASSEMBLYNAME,
                CLASSNAME,
                ISIDEMPOTENT,
                TIMEOUTMS,
                ACTIONVERSION,
                ISACTIVE,
                VERSION,
                STATUS,
                SORTORDER,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON,
                SOURCETYPE
            )
            VALUES (
                @ActionId,
                @TenantId,
                @ActionCode,
                @ActionName,
                @AssemblyName,
                @ClassName,
                @IsIdempotent,
                @TimeoutMs,
                @ActionVersion,
                @IsActive,
                @Version,
                1,
                9999,
                @CreatedById,
                GETDATE(),
                @CreatedById,
                GETDATE(),
                1
            )";

        public const string UPDATE_ACTIONREGISTRY = @"
            UPDATE MEIPACTIONREGISTRY
            SET
                ACTIONCODE     = @ActionCode,
                ACTIONNAME     = @ActionName,
                ASSEMBLYNAME   = @AssemblyName,
                CLASSNAME      = @ClassName,
                ISIDEMPOTENT   = @IsIdempotent,
                TIMEOUTMS      = @TimeoutMs,
                ACTIONVERSION  = @ActionVersion,
                ISACTIVE       = @IsActive,
                VERSION        = @Version,
                MODIFIEDBYID   = @ModifiedById,
                MODIFIEDON     = GETDATE()
            WHERE ACTIONID = @ActionId
              AND TENANTID = @TenantId
              AND STATUS   = 1";

        public const string DELETE_ACTIONREGISTRY = @"
            UPDATE MEIPACTIONREGISTRY
            SET
                STATUS       = 0,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = GETDATE()
            WHERE ACTIONID = @ActionId
              AND TENANTID = @TenantId
              AND STATUS   = 1";

        // ACTIONCODE is globally unique (UK_MEIPACTIONREGISTRY_ACTIONCODE) — not per-tenant.
        public const string EXISTS_ACTIONCODE = @"
            SELECT CASE WHEN EXISTS (
                SELECT 1
                FROM   MEIPACTIONREGISTRY
                WHERE  ACTIONCODE = @ActionCode
                  AND  STATUS     = 1
                  AND  (@ActionId = 0 OR ACTIONID <> @ActionId)
            ) THEN 1 ELSE 0 END";
    }
}