using System;

namespace CMSDAL.Query.TaxonomyTerm
{
    public static class TaxonomyTermQB
    {
        // ======================= GET TAXONOMY TERM =======================
        public const string GET_TAXONOMYTERM = @"SELECT
                                                    TT.TAXONOMYTERMID       AS TaxonomyTermId,
                                                    TT.TAXONOMYID           AS TaxonomyId,
                                                    TT.PARENTTAXONOMYTERMID AS ParentTaxonomyTermId,
                                                    TT.NAME                 AS Name,
                                                    TT.SLUG                 AS Slug,
                                                    TT.DESCRIPTION          AS Description,
                                                    TT.PATH                 AS Path,
                                                    TT.LEVELNO              AS LevelNo,
                                                    TT.DISPLAYORDER         AS DisplayOrder,
                                                    TT.COLOR                AS Color,
                                                    TT.ICON                 AS Icon,
                                                    TT.USAGECOUNT           AS UsageCount,
                                                    TT.METADATA             AS MetaData,

                                                    TT.TENANTID             AS TenantId,

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

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

                                                    TT.SORTORDER            AS SortOrder,
                                                    TT.STATUS               AS Status,
                                                    TT.VERSION              AS Version,
                                                    TT.SOURCETYPE           AS SourceType
                                                FROM MTAXONOMYTERM TT
                                                LEFT JOIN MUSER CU ON CU.USERID = TT.CREATEDBYID
                                                LEFT JOIN MUSER MU ON MU.USERID = TT.MODIFIEDBYID
                                                WHERE TT.TAXONOMYTERMID = @TaxonomyTermId;";


        // ======================= SAVE TAXONOMY TERM =======================
        public const string SAVE_TAXONOMYTERM = @"INSERT INTO MTAXONOMYTERM
                                                (
                                                    TAXONOMYTERMID,
                                                    TAXONOMYID,
                                                    PARENTTAXONOMYTERMID,
                                                    NAME,
                                                    SLUG,
                                                    DESCRIPTION,
                                                    PATH,
                                                    LEVELNO,
                                                    DISPLAYORDER,
                                                    COLOR,
                                                    ICON,
                                                    USAGECOUNT,
                                                    METADATA,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON,
                                                    SORTORDER,
                                                    STATUS,
                                                    VERSION,
                                                    SOURCETYPE,
                                                    TENANTID
                                                )
                                                VALUES
                                                (
                                                    @TaxonomyTermId,
                                                    @TaxonomyId,
                                                    @ParentTaxonomyTermId,
                                                    @Name,
                                                    @Slug,
                                                    @Description,
                                                    @Path,
                                                    @LevelNo,
                                                    @DisplayOrder,
                                                    @Color,
                                                    @Icon,
                                                    @UsageCount,
                                                    @MetaData,
                                                    @CreatedById,
                                                    @CreatedOn,
                                                    @ModifiedById,
                                                    @ModifiedOn,
                                                    @SortOrder,
                                                    @Status,
                                                    @Version,
                                                    @SourceType,
                                                    @TenantId
                                                );";


        // ======================= UPDATE TAXONOMY TERM =======================
        public const string UPDATE_TAXONOMYTERM = @"UPDATE MTAXONOMYTERM
                                                    SET
                                                        TAXONOMYID           = @TaxonomyId,
                                                        PARENTTAXONOMYTERMID = @ParentTaxonomyTermId,
                                                        NAME                 = @Name,
                                                        SLUG                 = @Slug,
                                                        DESCRIPTION          = @Description,
                                                        PATH                 = @Path,
                                                        LEVELNO              = @LevelNo,
                                                        DISPLAYORDER         = @DisplayOrder,
                                                        COLOR                = @Color,
                                                        ICON                 = @Icon,
                                                        USAGECOUNT           = @UsageCount,
                                                        METADATA             = @MetaData,
                                                        SORTORDER            = @SortOrder,
                                                        STATUS               = @Status,
                                                        VERSION              = @Version,
                                                        SOURCETYPE           = @SourceType,
                                                        MODIFIEDBYID         = @ModifiedById,
                                                        MODIFIEDON           = @ModifiedOn
                                                    WHERE
                                                        TAXONOMYTERMID = @TaxonomyTermId
                                                        AND TENANTID = @TenantId;";


        // ======================= DELETE TAXONOMY TERM =======================
        public const string DELETE_TAXONOMYTERM = @"DELETE FROM MTAXONOMYTERM
                                                    WHERE TAXONOMYTERMID = @TaxonomyTermId
                                                      AND TENANTID = @TenantId;";


        // ======================= SELECT LIST TAXONOMY TERM =======================
        public const string GET_SELECTLIST_TAXONOMYTERM = @"SELECT
                                                                TT.TAXONOMYTERMID       AS Id,
                                                                TT.NAME                 AS Name,
                                                            FROM MTAXONOMYTERM TT
                                                            WHERE TT.TAXONOMYID = @TaxonomyId
                                                              AND TT.STATUS = 1
                                                            ORDER BY TT.NAME;";
    }
}