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

namespace CMSDAL.Query.Taxonomy
{
    public static class TaxonomyQB
    {
        // ======================= GET TAXONOMY =======================
        public const string GET_TAXONOMY = @"SELECT
                                                T.TAXONOMYID          AS TaxonomyId,
                                                T.NAME                AS Name,
                                                T.SLUG                AS Slug,
                                                T.DESCRIPTION         AS Description,
                                                T.TYPE                AS Type,
                                                T.ALLOWMULTIPLEVALUES AS AllowMultipleValues,
                                                T.ISREQUIRED          AS IsRequired,
                                                T.DISPLAYORDER        AS DisplayOrder,
                                                T.METADATA            AS MetaData,

                                                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 MTAXONOMY 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.TAXONOMYID = @TaxonomyId;";


        // ======================= SAVE TAXONOMY =======================
        public const string SAVE_TAXONOMY = @"INSERT INTO MTAXONOMY
                                            (
                                                TAXONOMYID,
                                                NAME,
                                                SLUG,
                                                DESCRIPTION,
                                                TYPE,
                                                ALLOWMULTIPLEVALUES,
                                                ISREQUIRED,
                                                DISPLAYORDER,
                                                METADATA,
                                                CREATEDBYID,
                                                CREATEDON,
                                                MODIFIEDBYID,
                                                MODIFIEDON,
                                                SORTORDER,
                                                STATUS,
                                                VERSION,
                                                SOURCETYPE,
                                                TENANTID
                                            )
                                            VALUES
                                            (
                                                @TaxonomyId,
                                                @Name,
                                                @Slug,
                                                @Description,
                                                @Type,
                                                @AllowMultipleValues,
                                                @IsRequired,
                                                @DisplayOrder,
                                                @MetaData,
                                                @CreatedById,
                                                @CreatedOn,
                                                @ModifiedById,
                                                @ModifiedOn,
                                                @SortOrder,
                                                @Status,
                                                @Version,
                                                @SourceType,
                                                @TenantId
                                            );";


        // ======================= UPDATE TAXONOMY =======================
        public const string UPDATE_TAXONOMY = @"UPDATE MTAXONOMY
                                                SET
                                                    NAME                = @Name,
                                                    SLUG                = @Slug,
                                                    DESCRIPTION         = @Description,
                                                    TYPE                = @Type,
                                                    ALLOWMULTIPLEVALUES = @AllowMultipleValues,
                                                    ISREQUIRED          = @IsRequired,
                                                    DISPLAYORDER        = @DisplayOrder,
                                                    METADATA            = @MetaData,
                                                    SORTORDER           = @SortOrder,
                                                    STATUS              = @Status,
                                                    VERSION             = @Version,
                                                    SOURCETYPE          = @SourceType,
                                                    TENANTID            = @TenantId,
                                                    MODIFIEDBYID        = @ModifiedById,
                                                    MODIFIEDON          = @ModifiedOn
                                                WHERE
                                                    TAXONOMYID = @TaxonomyId;";


        // ======================= DELETE TAXONOMY =======================
        public const string DELETE_TAXONOMY = @"DELETE FROM MTAXONOMY
                                                WHERE TAXONOMYID = @TaxonomyId;";


        // ======================= SELECT LIST TAXONOMY =======================
        public const string GET_SELECTLIST_TAXONOMY = @"SELECT
                                                            T.TAXONOMYID AS TaxonomyId,
                                                            T.NAME       AS Name
                                                        FROM MTAXONOMY T
                                                        WHERE T.STATUS = 1
                                                        ORDER BY T.NAME;";
    }
}