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

namespace CMSDAL.Query.Tag
{
    public static class TagQB
    {
        // ======================= GET TAG =======================
        public const string GET_TAG = @"SELECT
                                            T.TAGID           AS TagId,
                                            T.NAME            AS Name,
                                            T.SLUG            AS Slug,
                                            T.COLORID           AS ColorId,
                                            T.TAGTYPE         AS TagType,
                                            T.USAGECOUNT      AS UsageCount,

                                            T.TENANTID        AS TenantId,

                                            T.CREATEDBYID     AS CreatedById,
                                            T.CREATEDON       AS CreatedOn,
                                            CU.USERNAME       AS CreatedByName,

                                            T.MODIFIEDBYID    AS ModifiedById,
                                            T.MODIFIEDON      AS ModifiedOn,
                                            MU.USERNAME       AS ModifiedByName,

                                            T.SORTORDER       AS SortOrder,
                                            T.STATUS          AS Status,
                                            T.VERSION         AS Version,
                                            T.SOURCETYPE      AS SourceType
                                        FROM MTAG T
                                        LEFT JOIN MUSER TN ON TN.USERID = T.TENANTID
                                        LEFT JOIN MUSER CU ON CU.USERID = T.CREATEDBYID
                                        LEFT JOIN MUSER MU ON MU.USERID = T.MODIFIEDBYID
                                        WHERE T.TAGID = @TagId;";


        // ======================= SAVE TAG =======================
        public const string SAVE_TAG = @"INSERT INTO MTAG
                                        (
                                            TENANTID,
                                            NAME,
                                            SLUG,
                                            COLORID,
                                            TAGTYPE,
                                            USAGECOUNT,
                                            CREATEDBYID,
                                            CREATEDON,
                                            MODIFIEDBYID,
                                            MODIFIEDON,
                                            SORTORDER,
                                            STATUS,
                                            VERSION,
                                            SOURCETYPE
                                        )
                                        VALUES
                                        (
                                            @TenantId,
                                            @Name,
                                            @Slug,
                                            @ColorId,
                                            @TagType,
                                            @UsageCount,
                                            @CreatedById,
                                            @CreatedOn,
                                            @ModifiedById,
                                            @ModifiedOn,
                                            @SortOrder,
                                            @Status,
                                            @Version,
                                            @SourceType
                                        );";


        // ======================= UPDATE TAG =======================
        public const string UPDATE_TAG = @"UPDATE MTAG
                                            SET
                                                NAME         = @Name,
                                                SLUG         = @Slug,
                                                COLORID       = @ColorId,
                                                TAGTYPE      = @TagType,
                                                USAGECOUNT   = @UsageCount,
                                                TENANTID     = @TenantId,
                                                SORTORDER    = @SortOrder,
                                                STATUS       = @Status,
                                                VERSION      = @Version,
                                                SOURCETYPE   = @SourceType,
                                                MODIFIEDBYID = @ModifiedById,
                                                MODIFIEDON   = @ModifiedOn
                                            WHERE
                                                TAGID = @TagId;";


        // ======================= DELETE TAG =======================
        public const string DELETE_TAG = @"DELETE FROM MTAG
                                            WHERE TAGID = @TagId;";


        // ======================= SELECT LIST TAG =======================
        public const string GET_SELECTLIST_TAG = @"SELECT
                                                        T.TAGID AS TagId,
                                                        T.NAME  AS Name,
                                                        T.SLUG AS Slug
                                                    FROM MTAG T
                                                    WHERE T.STATUS = 1
                                                    ORDER BY T.NAME;";
    }
}