using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FrameworkDAL.Query.MailTemplate
{
    public class MailTemplateQB
    {
        public const string GET_MAILTEMPLATE = @"SELECT 
                                                    MT.MAILTEMPLATEID        AS MailTemplateId,
                                                    MT.MAILTEMPLATECODE      AS MailTemplateCode,
                                                    MT.MAILTEMPLATENAME      AS MailTemplateName,
                                                    MT.MAILTEMPLATETYPE      AS MailTemplateType,
                                                    MT.ATTACHMENTFILEID      AS AttachmentFileId,
                                                    AF.FILENAME              AS AttachmentFileName,
                                                    MT.BODYFILEID            AS BodyFileId,
                                                    BF.FILENAME              AS BodyFileName,
                                                    MT.TEMPLATEATTACHMENTID  AS TemplateAttachmentId,
                                                    MT.EVENTTYPEID           AS EventTypeId,
                                                    ET.EVENTTYPECODE         AS EventTypeCode,
                                                    ET.EVENTTYPENAME         AS EventTypeName,
                                                    MT.SUBJECT               AS MailTemplateSubject,
                                                    MT.BODY                  AS MailTemplateBody,
                                                    MT.REMARKS               AS MailTemplateRemarks,
                                                    MT.SORTORDER             AS MailTemplateSortOrder,
                                                    MT.STATUS                AS MailTemplateStatus,
                                                    MT.VERSION               AS MailTemplateVersion,
                                                    MT.SOURCETYPE            AS MailTemplateSourceType,
                                                    MT.CREATEDBYID           AS MailTemplateCreatedById,
                                                    MT.CREATEDON             AS MailTemplateCreatedOn,
                                                    MT.MODIFIEDBYID          AS MailTemplateModifiedById,
                                                    MT.MODIFIEDON            AS MailTemplateModifiedOn
                                                FROM MMAILTEMPLATE MT
                                                LEFT JOIN MFILE AF ON MT.ATTACHMENTFILEID = AF.FILEID
                                                LEFT JOIN MFILE BF ON MT.BODYFILEID = BF.FILEID
                                                LEFT JOIN MEVENTTYPE ET ON MT.EVENTTYPEID = ET.EVENTTYPEID
                                                WHERE MT.MAILTEMPLATEID = @mailtemplateid;";

        public const string SAVE_MAILTEMPLATE = @"INSERT INTO MMAILTEMPLATE 
                                                    (
                                                        MAILTEMPLATEID,
                                                        MAILTEMPLATECODE,
                                                        MAILTEMPLATENAME,
                                                        MAILTEMPLATETYPE,
                                                        ATTACHMENTFILEID,
                                                        BODYFILEID,
                                                        TEMPLATEATTACHMENTID,
                                                        EVENTTYPEID,
                                                        SUBJECT,
                                                        BODY,
                                                        REMARKS,
                                                        SORTORDER,
                                                        STATUS,
                                                        VERSION,
                                                        SOURCETYPE,
                                                        CREATEDBYID,
                                                        CREATEDON,
                                                        MODIFIEDBYID,
                                                        MODIFIEDON
                                                    )
                                                    VALUES
                                                    (
                                                        @MailTemplateId,
                                                        @MailTemplateCode,
                                                        @MailTemplateName,
                                                        @MailTemplateType,
                                                        @AttachmentFileId,
                                                        @BodyFileId,
                                                        @TemplateAttachmentId,
                                                        @EventTypeId,
                                                        @MailTemplateSubject,
                                                        @MailTemplateBody,
                                                        @MailTemplateRemarks,
                                                        @MailTemplateSortOrder,
                                                        @MailTemplateStatus,
                                                        @MailTemplateVersion,
                                                        @MailTemplateSourceType,
                                                        @MailTemplateCreatedById,
                                                        @MailTemplateCreatedOn,
                                                        @MailTemplateModifiedById,
                                                        @MailTemplateModifiedOn
                                                    );";

        public const string UPDATE_MAILTEMPLATE = @"UPDATE MMAILTEMPLATE
                                                    SET 
                                                        MAILTEMPLATECODE      = @MailTemplateCode,
                                                        MAILTEMPLATENAME      = @MailTemplateName,
                                                        MAILTEMPLATETYPE      = @MailTemplateType,
                                                        ATTACHMENTFILEID      = @AttachmentFileId,
                                                        BODYFILEID            = @BodyFileId,
                                                        TEMPLATEATTACHMENTID  = @TemplateAttachmentId,
                                                        EVENTTYPEID           = @EventTypeId,
                                                        SUBJECT               = @MailTemplateSubject,
                                                        BODY                  = @MailTemplateBody,
                                                        REMARKS               = @MailTemplateRemarks,
                                                        SORTORDER             = @MailTemplateSortOrder,
                                                        STATUS                = @MailTemplateStatus,
                                                        VERSION               = @MailTemplateVersion,
                                                        SOURCETYPE            = @MailTemplateSourceType,
                                                        MODIFIEDBYID          = @MailTemplateModifiedById,
                                                        MODIFIEDON            = @MailTemplateModifiedOn
                                                    WHERE MAILTEMPLATEID = @MailTemplateId;";

        public const string DELETE_MAILTEMPLATE = @"DELETE FROM MMAILTEMPLATE
                                                    WHERE MAILTEMPLATEID = @mailtemplateid;";

        // INDEX: IX_MMAILTEMPLATE_CODE_CLIENT (MAILTEMPLATECODE, CLIENTID) INCLUDE (MAILTEMPLATEID, STATUS)
        public const string GET_MAILTEMPLATE_BY_CODE = @"
            SELECT TOP 1
                MT.MAILTEMPLATEID        AS MailTemplateId,
                MT.MAILTEMPLATECODE      AS MailTemplateCode,
                MT.MAILTEMPLATENAME      AS MailTemplateName,
                MT.MAILTEMPLATETYPE      AS MailTemplateType,
                MT.ATTACHMENTFILEID      AS AttachmentFileId,
                AF.FILENAME              AS AttachmentFileName,
                MT.BODYFILEID            AS BodyFileId,
                BF.FILENAME              AS BodyFileName,
                MT.EVENTTYPEID           AS EventTypeId,
                ET.EVENTTYPECODE         AS EventTypeCode,
                ET.EVENTTYPENAME         AS EventTypeName,
                MT.SUBJECT               AS MailTemplateSubject,
                MT.BODY                  AS MailTemplateBody,
                MT.REMARKS               AS MailTemplateRemarks,
                MT.SORTORDER             AS MailTemplateSortOrder,
                MT.STATUS                AS MailTemplateStatus,
                MT.VERSION               AS MailTemplateVersion,
                MT.SOURCETYPE            AS MailTemplateSourceType,
                MT.CLIENTID              AS ClientId,
                MT.CREATEDBYID           AS MailTemplateCreatedById,
                MT.CREATEDON             AS MailTemplateCreatedOn,
                MT.MODIFIEDBYID          AS MailTemplateModifiedById,
                MT.MODIFIEDON            AS MailTemplateModifiedOn
            FROM MMAILTEMPLATE MT
            LEFT JOIN MFILE AF ON MT.ATTACHMENTFILEID = AF.FILEID
            LEFT JOIN MFILE BF ON MT.BODYFILEID = BF.FILEID
            LEFT JOIN MEVENTTYPE ET ON MT.EVENTTYPEID = ET.EVENTTYPEID
            WHERE MT.MAILTEMPLATECODE = @mailtemplatecode
              AND MT.STATUS = 1
              AND (MT.CLIENTID = @clientid OR MT.CLIENTID IS NULL)
            ORDER BY CASE WHEN MT.CLIENTID IS NOT NULL THEN 0 ELSE 1 END;";

        // INDEX: MMAILTEMPLATE (MAILTEMPLATEID, STATUS) — covered by PK + STATUS
        public const string GET_FLOWCODE_BY_TEMPLATEID = @"
            SELECT MT.FLOWCODE
            FROM   MMAILTEMPLATE MT
            WHERE  MT.MAILTEMPLATEID = @MailTemplateId
            AND    MT.STATUS = 1";

        public const string GET_SELECTLIST_MAILTEMPLATE = @"WITH PagedMailTemplate AS
                                                            (
                                                                SELECT
                                                                    MT.MAILTEMPLATEID          AS Id,
                                                                    MT.MAILTEMPLATECODE        AS Code,
                                                                    MT.MAILTEMPLATENAME        AS Name,
                                                               
                                                                    ROW_NUMBER() OVER (ORDER BY MT.MAILTEMPLATENAME) AS RowNum
                                                                FROM MMAILTEMPLATE MT
                                                                WHERE MT.STATUS = 1
                                                            )
                                                            SELECT
                                                                Id,
                                                                Code,
                                                                Name
                                              
                                                            FROM PagedMailTemplate
                                                        WHERE
                                                         (@firstnumber = -1 AND @maxresult = -1)
                                                         OR (RowNum BETWEEN @firstnumber AND @maxresult);";
    }
}