namespace FrameworkDAL.Query.EIPConversation.EIPChannelEndpointAdmin
{
    // INDEX required on MEIPCHANNELENDPOINT:
    //   IX_MEIPCHANNELENDPOINT_TENANT_STATUS on (TENANTID, STATUS) INCLUDE (CHANNELTYPE, ISACTIVE)
    public static class EIPChannelEndpointAdminQB
    {
        public const string GET_ENDPOINT_LIST = @"
            SELECT
                A.ENDPOINTID          AS EndpointId,
                A.TENANTID            AS TenantId,
                A.CHANNELTYPE         AS ChannelType,
                A.ENDPOINTPURPOSE     AS EndpointPurpose,
                A.PHONENUMBER         AS PhoneNumber,
                A.PROVIDERNAME        AS ProviderName,
                A.PROVIDERCONFIGJSON  AS ProviderConfigJson,
                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 MEIPCHANNELENDPOINT 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 (@ChannelType IS NULL OR A.CHANNELTYPE = @ChannelType)
            ORDER BY A.CHANNELTYPE ASC, A.ENDPOINTID ASC";

        public const string GET_ENDPOINT = @"
            SELECT
                A.ENDPOINTID          AS EndpointId,
                A.TENANTID            AS TenantId,
                A.CHANNELTYPE         AS ChannelType,
                A.ENDPOINTPURPOSE     AS EndpointPurpose,
                A.PHONENUMBER         AS PhoneNumber,
                A.PROVIDERNAME        AS ProviderName,
                A.PROVIDERCONFIGJSON  AS ProviderConfigJson,
                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 MEIPCHANNELENDPOINT A
            LEFT JOIN MEMPLOYEE CB ON A.CREATEDBYID = CB.EMPLOYEEID
            LEFT JOIN MEMPLOYEE MB ON A.MODIFIEDBYID = MB.EMPLOYEEID
            WHERE A.ENDPOINTID = @EndpointId
              AND A.TENANTID   = @TenantId
              AND A.STATUS     = 1";

        // ENDPOINTID is a plain INT PK (NOT IDENTITY)
        public const string INSERT_ENDPOINT = @"
            INSERT INTO MEIPCHANNELENDPOINT (
                ENDPOINTID,
                TENANTID,
                CHANNELTYPE,
                ENDPOINTPURPOSE,
                PHONENUMBER,
                PROVIDERNAME,
                PROVIDERCONFIGJSON,
                ISACTIVE,
                VERSION,
                STATUS,
                SORTORDER,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON,
                SOURCETYPE
            )
            VALUES (
                @EndpointId,
                @TenantId,
                @ChannelType,
                @EndpointPurpose,
                @PhoneNumber,
                @ProviderName,
                @ProviderConfigJson,
                @IsActive,
                @Version,
                1,
                9999,
                @CreatedById,
                GETDATE(),
                @CreatedById,
                GETDATE(),
                1
            )";

        public const string UPDATE_ENDPOINT = @"
            UPDATE MEIPCHANNELENDPOINT
            SET
                CHANNELTYPE         = @ChannelType,
                ENDPOINTPURPOSE     = @EndpointPurpose,
                PHONENUMBER         = @PhoneNumber,
                PROVIDERNAME        = @ProviderName,
                PROVIDERCONFIGJSON  = @ProviderConfigJson,
                ISACTIVE            = @IsActive,
                VERSION             = @Version,
                MODIFIEDBYID        = @ModifiedById,
                MODIFIEDON          = GETDATE()
            WHERE ENDPOINTID = @EndpointId
              AND TENANTID   = @TenantId
              AND STATUS     = 1";

        public const string DELETE_ENDPOINT = @"
            UPDATE MEIPCHANNELENDPOINT
            SET
                STATUS       = 0,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = GETDATE()
            WHERE ENDPOINTID = @EndpointId
              AND TENANTID   = @TenantId
              AND STATUS     = 1";

        // No uniqueness constraint on (TENANTID, CHANNELTYPE, PHONENUMBER) in the DDL — this
        // duplicate-check is a BLL-level business rule, not a DB-enforced one: prevent two
        // active endpoints for the same tenant+channel+phone number combination.
        public const string EXISTS_ENDPOINT_PHONE = @"
            SELECT CASE WHEN EXISTS (
                SELECT 1 FROM MEIPCHANNELENDPOINT
                WHERE TENANTID    = @TenantId
                  AND CHANNELTYPE = @ChannelType
                  AND PHONENUMBER = @PhoneNumber
                  AND STATUS      = 1
                  AND (@EndpointId = 0 OR ENDPOINTID <> @EndpointId)
            ) THEN 1 ELSE 0 END";
    }
}