namespace FrameworkDAL.Query.EIPConversation.EIPMessageTemplateAdmin
{
    // INDEX required on MEIPMESSAGETEMPLATE:
    //   IX_MEIPMESSAGETEMPLATE_TENANT_STATUS on (TENANTID, STATUS) INCLUDE (MESSAGEKEY, LANGUAGECODE, CHANNELTYPE)
    public static class EIPMessageTemplateAdminQB
    {
        // =====================================================
        // List — filterable by free-text search on MessageKey. Excludes soft-deleted rows (STATUS = 0).
        // =====================================================
        public const string GET_TEMPLATE_LIST = @"
            SELECT
                A.MESSAGEID          AS MessageId,
                A.TENANTID           AS TenantId,
                A.MESSAGEKEY         AS MessageKey,
                A.LANGUAGECODE       AS LanguageCode,
                A.CHANNELTYPE        AS ChannelType,
                A.TEMPLATECONTENT    AS TemplateContent,
                A.TEMPLATEVERSION    AS TemplateVersion,
                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 MEIPMESSAGETEMPLATE 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.MESSAGEKEY LIKE '%' + @Search + '%'
                  )
            ORDER BY A.MESSAGEKEY ASC, A.LANGUAGECODE ASC, A.CHANNELTYPE ASC";

        // =====================================================
        // Single record
        // =====================================================
        public const string GET_TEMPLATE = @"
            SELECT
                A.MESSAGEID          AS MessageId,
                A.TENANTID           AS TenantId,
                A.MESSAGEKEY         AS MessageKey,
                A.LANGUAGECODE       AS LanguageCode,
                A.CHANNELTYPE        AS ChannelType,
                A.TEMPLATECONTENT    AS TemplateContent,
                A.TEMPLATEVERSION    AS TemplateVersion,
                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 MEIPMESSAGETEMPLATE A
            LEFT JOIN MEMPLOYEE CB ON A.CREATEDBYID = CB.EMPLOYEEID
            LEFT JOIN MEMPLOYEE MB ON A.MODIFIEDBYID = MB.EMPLOYEEID
            WHERE A.MESSAGEID = @MessageId
              AND A.TENANTID  = @TenantId
              AND A.STATUS    = 1";

        // =====================================================
        // Insert — MESSAGEID is a plain INT PK (NOT IDENTITY)
        // =====================================================
        public const string INSERT_TEMPLATE = @"
            INSERT INTO MEIPMESSAGETEMPLATE (
                MESSAGEID,
                TENANTID,
                MESSAGEKEY,
                LANGUAGECODE,
                CHANNELTYPE,
                TEMPLATECONTENT,
                TEMPLATEVERSION,
                ISACTIVE,
                VERSION,
                STATUS,
                SORTORDER,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON,
                SOURCETYPE
            )
            VALUES (
                @MessageId,
                @TenantId,
                @MessageKey,
                @LanguageCode,
                @ChannelType,
                @TemplateContent,
                @TemplateVersion,
                @IsActive,
                @Version,
                1,
                9999,
                @CreatedById,
                GETDATE(),
                @CreatedById,
                GETDATE(),
                1
            )";

        // =====================================================
        // Update
        // =====================================================
        public const string UPDATE_TEMPLATE = @"
            UPDATE MEIPMESSAGETEMPLATE
            SET
                MESSAGEKEY       = @MessageKey,
                LANGUAGECODE     = @LanguageCode,
                CHANNELTYPE      = @ChannelType,
                TEMPLATECONTENT  = @TemplateContent,
                TEMPLATEVERSION  = @TemplateVersion,
                ISACTIVE         = @IsActive,
                VERSION          = @Version,
                MODIFIEDBYID     = @ModifiedById,
                MODIFIEDON       = GETDATE()
            WHERE MESSAGEID = @MessageId
              AND TENANTID  = @TenantId
              AND STATUS    = 1";

        // =====================================================
        // Soft-delete
        // =====================================================
        public const string DELETE_TEMPLATE = @"
            UPDATE MEIPMESSAGETEMPLATE
            SET
                STATUS       = 0,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = GETDATE()
            WHERE MESSAGEID = @MessageId
              AND TENANTID  = @TenantId
              AND STATUS    = 1";

        // =====================================================
        // Duplicate-check before insert/update — matches UK_MEIPMESSAGETEMPLATE_KEY_LANG_CHANNEL
        // (TENANTID, MESSAGEKEY, LANGUAGECODE, CHANNELTYPE, TEMPLATEVERSION).
        // =====================================================
        public const string EXISTS_TEMPLATE_KEY = @"
            SELECT CASE WHEN EXISTS (
                SELECT 1
                FROM   MEIPMESSAGETEMPLATE
                WHERE  TENANTID        = @TenantId
                  AND  MESSAGEKEY      = @MessageKey
                  AND  LANGUAGECODE    = @LanguageCode
                  AND  CHANNELTYPE     = @ChannelType
                  AND  TEMPLATEVERSION = @TemplateVersion
                  AND  STATUS          = 1
                  AND  (@MessageId = 0 OR MESSAGEID <> @MessageId)
            ) THEN 1 ELSE 0 END";
    }
}