using System;

namespace FrameworkDAL.Query.EIPConversation.EIPCapability
{
    public static class EIPCapabilityQB
    {
        public const string GET_EIPCAPABILITY = @"
            SELECT
                A.CAPABILITYID    AS CapabilityId,
                A.CAPABILITYCODE  AS CapabilityCode,
                A.CAPABILITYNAME  AS Name,
                A.DESCRIPTION     AS CapabilityDescription,
                A.ISACTIVE        AS IsActive,
                A.TENANTID        AS TenantId,
                A.CREATEDBYID     AS CreatedById,
                CB.EMPLOYEENAME   AS CreatedByName,
                A.CREATEDON       AS CreatedOn,
                A.MODIFIEDBYID    AS ModifiedById,
                MB.EMPLOYEENAME   AS ModifiedByName,
                A.MODIFIEDON      AS ModifiedOn,
                A.STATUS          AS Status,
                A.VERSION         AS Version
            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;";


        // Per-capability risk config used by EIPRiskAssessment. Tenant-specific row wins
        // over the -1=global row when both exist (same override convention used elsewhere
        // in this repo, e.g. EIPFlowDAL's tenant-specific-then-global fallback).
        public const string GET_RISK_CONFIG_BY_CODE = @"
            SELECT TOP 1
                RISKLEVEL   AS RiskLevel,
                REQUIRESOTP AS RequiresOtp
            FROM MEIPCAPABILITY
            WHERE CAPABILITYCODE = @CapabilityCode
              AND (TENANTID = @TenantId OR TENANTID = -1)
              AND ISACTIVE = 1
            ORDER BY CASE WHEN TENANTID = @TenantId THEN 0 ELSE 1 END;";


        public const string GET_SELECTLIST_EIPCAPABILITY = @"
            SELECT
                CAPABILITYID   AS Id,
                CAPABILITYCODE AS Code,
                CAPABILITYNAME AS Name
            FROM MEIPCAPABILITY
            WHERE TENANTID = @TenantId
              AND ISACTIVE  = 1;";


        public const string SAVE_EIPCAPABILITY = @"
            INSERT INTO MEIPCAPABILITY (
                CAPABILITYID,
                CAPABILITYCODE,
                CAPABILITYNAME,
                ISACTIVE,
                TENANTID,
                STATUS,
                VERSION,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON
            )
            VALUES (
                @CapabilityId,
                @CapabilityCode,
                @Name,
                @IsActive,
                @TenantId,
                @Status,
                @Version,
                @CreatedById,
                @CreatedOn,
                @ModifiedById,
                @ModifiedOn
            );";


        public const string UPDATE_EIPCAPABILITY = @"
            UPDATE MEIPCAPABILITY
            SET
                CAPABILITYCODE = @CapabilityCode,
                CAPABILITYNAME = @Name,
                ISACTIVE       = @IsActive,
                STATUS         = @Status,
                VERSION        = @Version,
                MODIFIEDBYID   = @ModifiedById,
                MODIFIEDON     = @ModifiedOn
            WHERE CAPABILITYID = @CapabilityId
              AND TENANTID     = @TenantId;";


        public const string DELETE_EIPCAPABILITY = @"
            DELETE FROM MEIPCAPABILITY
            WHERE CAPABILITYID = @CapabilityId
              AND TENANTID     = @TenantId;";
    }
}