namespace FrameworkDAL.Query.EIPConversation.EIPCapabilityAdmin
{
    // INDEX required on MEIPCAPABILITY:
    //   IX_MEIPCAPABILITY_TENANT_STATUS on (TENANTID, STATUS) INCLUDE (CAPABILITYCODE, CAPABILITYNAME)
    public static class EIPCapabilityAdminQB
    {
        // =====================================================
        // List — filterable by free-text search. Excludes soft-deleted rows (STATUS = 0).
        // CAPABILITYCODE is globally unique (not per-tenant) but rows are still tenant-scoped
        // for visibility, same as every other EIP admin master table.
        // =====================================================
        public const string GET_CAPABILITY_LIST = @"
            SELECT
                A.CAPABILITYID       AS CapabilityId,
                A.TENANTID           AS TenantId,
                A.CAPABILITYCODE     AS CapabilityCode,
                A.CAPABILITYNAME     AS CapabilityName,
                A.DESCRIPTION        AS Description,
                A.RISKLEVEL          AS RiskLevel,
                A.REQUIRESOTP        AS RequiresOtp,
                A.REQUIRESAPPROVAL   AS RequiresApproval,
                A.AUDITREQUIRED      AS AuditRequired,
                A.CAPABILITYVERSION  AS CapabilityVersion,
                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 MEIPCAPABILITY 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.CAPABILITYCODE LIKE '%' + @Search + '%'
                    OR A.CAPABILITYNAME LIKE '%' + @Search + '%'
                  )
            ORDER BY A.CAPABILITYCODE ASC";

        // =====================================================
        // Single record
        // =====================================================
        public const string GET_CAPABILITY = @"
            SELECT
                A.CAPABILITYID       AS CapabilityId,
                A.TENANTID           AS TenantId,
                A.CAPABILITYCODE     AS CapabilityCode,
                A.CAPABILITYNAME     AS CapabilityName,
                A.DESCRIPTION        AS Description,
                A.RISKLEVEL          AS RiskLevel,
                A.REQUIRESOTP        AS RequiresOtp,
                A.REQUIRESAPPROVAL   AS RequiresApproval,
                A.AUDITREQUIRED      AS AuditRequired,
                A.CAPABILITYVERSION  AS CapabilityVersion,
                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 MEIPCAPABILITY A
            LEFT JOIN MEMPLOYEE CB ON A.CREATEDBYID = CB.EMPLOYEEID
            LEFT JOIN MEMPLOYEE MB ON A.MODIFIEDBYID = MB.EMPLOYEEID
            WHERE A.CAPABILITYID = @CapabilityId
              AND A.TENANTID     = @TenantId
              AND A.STATUS       = 1";

        // =====================================================
        // Insert — CAPABILITYID is a plain INT PK (NOT IDENTITY)
        // =====================================================
        public const string INSERT_CAPABILITY = @"
            INSERT INTO MEIPCAPABILITY (
                CAPABILITYID,
                TENANTID,
                CAPABILITYCODE,
                CAPABILITYNAME,
                DESCRIPTION,
                RISKLEVEL,
                REQUIRESOTP,
                REQUIRESAPPROVAL,
                AUDITREQUIRED,
                CAPABILITYVERSION,
                ISACTIVE,
                VERSION,
                STATUS,
                SORTORDER,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON,
                SOURCETYPE
            )
            VALUES (
                @CapabilityId,
                @TenantId,
                @CapabilityCode,
                @CapabilityName,
                @Description,
                @RiskLevel,
                @RequiresOtp,
                @RequiresApproval,
                @AuditRequired,
                @CapabilityVersion,
                @IsActive,
                @Version,
                1,
                9999,
                @CreatedById,
                GETDATE(),
                @CreatedById,
                GETDATE(),
                1
            )";

        // =====================================================
        // Update
        // =====================================================
        public const string UPDATE_CAPABILITY = @"
            UPDATE MEIPCAPABILITY
            SET
                CAPABILITYCODE     = @CapabilityCode,
                CAPABILITYNAME     = @CapabilityName,
                DESCRIPTION        = @Description,
                RISKLEVEL          = @RiskLevel,
                REQUIRESOTP        = @RequiresOtp,
                REQUIRESAPPROVAL   = @RequiresApproval,
                AUDITREQUIRED      = @AuditRequired,
                CAPABILITYVERSION  = @CapabilityVersion,
                ISACTIVE           = @IsActive,
                VERSION            = @Version,
                MODIFIEDBYID       = @ModifiedById,
                MODIFIEDON         = GETDATE()
            WHERE CAPABILITYID = @CapabilityId
              AND TENANTID     = @TenantId
              AND STATUS       = 1";

        // =====================================================
        // Soft-delete
        // =====================================================
        public const string DELETE_CAPABILITY = @"
            UPDATE MEIPCAPABILITY
            SET
                STATUS       = 0,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = GETDATE()
            WHERE CAPABILITYID = @CapabilityId
              AND TENANTID     = @TenantId
              AND STATUS       = 1";

        // =====================================================
        // Duplicate-check before insert — CAPABILITYCODE is globally unique (UK_MEIPCAPABILITY_CAPABILITYCODE),
        // not per-tenant, so this deliberately does NOT filter by TenantId.
        // =====================================================
        public const string EXISTS_CAPABILITYCODE = @"
            SELECT CASE WHEN EXISTS (
                SELECT 1
                FROM   MEIPCAPABILITY
                WHERE  CAPABILITYCODE = @CapabilityCode
                  AND  STATUS         = 1
                  AND  (@CapabilityId = 0 OR CAPABILITYID <> @CapabilityId)
            ) THEN 1 ELSE 0 END";
    }
}