using System;

namespace CMSDAL.Query.ContentType
{
    public static class ContentTypeQB
    {
        public const string GET_CONTENTTYPE = @"
SELECT
    ct.CONTENTTYPEID        AS ContentTypeId,
    ct.SPACEID              AS ContentTypeSpaceId,
    s.SPACENAME             AS ContentTypeSpaceName,

    ct.NAME                 AS ContentTypeName,
    ct.SLUG                 AS ContentTypeSlug,
    ct.DESCRIPTION          AS ContentTypeDescription,
    ct.ICON                 AS ContentTypeIcon,

    ct.CONTENTCATEGORYID    AS ContentTypeContentCategoryId,
    cc.CONTENTCATEGORYNAME  AS ContentCategoryName,

    ct.DISPLAYFIELD         AS ContentTypeDisplayField,
    ct.FIELDS               AS ContentTypeFields,
    ct.VALIDATION           AS ContentTypeValidation,
    ct.AIFEATURES           AS ContentTypeAIFeatures,

    ct.ISSYSTEM             AS ContentTypeIsSystem,

    ct.TENANTID             AS ContentTypeTenantId,
    cl.CLIENTNAME           AS ContentTypeTenantName,

    ct.CREATEDBYID          AS ContentTypeCreatedById,
    cb.USERNAME             AS ContentTypeCreatedByName,
    ct.CREATEDON            AS ContentTypeCreatedOn,

    ct.MODIFIEDBYID         AS ContentTypeModifiedById,
    mb.USERNAME             AS ContentTypeModifiedByName,
    ct.MODIFIEDON           AS ContentTypeModifiedOn,

    ct.VERSION              AS ContentTypeVersion,
    ct.SORTORDER            AS ContentTypeSortOrder,
    ct.STATUS               AS ContentTypeStatus,
    ct.SOURCETYPE           AS ContentTypeSourceType

FROM dbo.MCONTENTTYPE ct

LEFT JOIN dbo.MSPACE s
       ON ct.SPACEID = s.SPACEID

LEFT JOIN dbo.MCONTENTCATEGORY cc
       ON ct.CONTENTCATEGORYID = cc.CONTENTCATEGORYID

LEFT JOIN dbo.MUSER cb
       ON ct.CREATEDBYID = cb.USERID

LEFT JOIN dbo.MUSER mb
       ON ct.MODIFIEDBYID = mb.USERID

LEFT JOIN dbo.MCLIENT cl
       ON ct.TENANTID = cl.CLIENTID

WHERE ct.CONTENTTYPEID = @ContentTypeId;
";
        public const string SAVE_CONTENTTYPE = @"
            INSERT INTO MCONTENTTYPE
            (
                SPACEID,
                NAME,
                SLUG,
                DESCRIPTION,
                ICON,
                CONTENTCATEGORYID,
                DISPLAYFIELD,
                FIELDS,
                VALIDATION,
                AIFEATURES,
                ISSYSTEM,
                VERSION,
                SORTORDER,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON,
                TENANTID,
                STATUS,
                SOURCETYPE
            )
            VALUES
            (
                @ContentTypeSpaceId,
                @ContentTypeName,
                @ContentTypeSlug,
                @ContentTypeDescription,
                @ContentTypeIcon,
                @ContentTypeContentCategoryId,
                @ContentTypeDisplayField,
                @ContentTypeFields,
                @ContentTypeValidation,
                @ContentTypeAIFeatures,
                @ContentTypeIsSystem,
                @ContentTypeVersion,
                @ContentTypeSortOrder,
                @ContentTypeCreatedById,
                @ContentTypeCreatedOn,
                @ContentTypeModifiedById,
                @ContentTypeModifiedOn,
                @ContentTypeTenantId,
                @ContentTypeStatus,
                @ContentTypeSourceType
            );";
        public const string UPDATE_CONTENTTYPE = @"
            UPDATE MCONTENTTYPE
            SET
                SPACEID = @ContentTypeSpaceId,
                NAME = @ContentTypeName,
                SLUG = @ContentTypeSlug,
                DESCRIPTION = @ContentTypeDescription,
                ICON = @ContentTypeIcon,
                CONTENTCATEGORYID = @ContentTypeContentCategoryId,
                DISPLAYFIELD = @ContentTypeDisplayField,
                FIELDS = @ContentTypeFields,
                VALIDATION = @ContentTypeValidation,
                AIFEATURES = @ContentTypeAIFeatures,
                ISSYSTEM = @ContentTypeIsSystem,
                VERSION = @ContentTypeVersion,
                SORTORDER = @ContentTypeSortOrder,
                STATUS = @ContentTypeStatus,
                SOURCETYPE = @ContentTypeSourceType,
                MODIFIEDBYID = @ContentTypeModifiedById,
                MODIFIEDON = @ContentTypeModifiedOn
            WHERE
                CONTENTTYPEID = @ContentTypeId;";
        public const string DELETE_CONTENTTYPE = @"
            DELETE FROM MCONTENTTYPE
            WHERE CONTENTTYPEID = @contenttypeid;";
        public const string GET_SELECTLIST_CONTENTTYPE = @"WITH ContentType AS
                                                        (
                                                            SELECT
                                                                ct.CONTENTTYPEID AS ContentTypeId,
                                                                ct.SPACEID AS ContentTypeSpaceId,
                                                                ct.NAME AS ContentTypeName,
                                                                ct.SLUG AS ContentTypeSlug,
                                                                ct.DESCRIPTION AS ContentTypeDescription,
                                                                ct.ICON AS ContentTypeIcon,
                                                                ct.CONTENTCATEGORYID AS ContentTypeContentCategoryId,
                                                                ct.DISPLAYFIELD AS ContentTypeDisplayField,
                                                                ct.FIELDS AS ContentTypeFields,
                                                                ct.VALIDATION AS ContentTypeValidation,
                                                                ct.AIFEATURES AS ContentTypeAIFeatures,
                                                                ct.ISSYSTEM AS ContentTypeIsSystem,
                                                                ct.TENANTID AS ContentTypeTenantId,
                                                                ct.CREATEDBYID AS ContentTypeCreatedById,
                                                                ct.CREATEDON AS ContentTypeCreatedOn,
                                                                cb.USERNAME AS ContentTypeCreatedByName,
                                                                ct.MODIFIEDBYID AS ContentTypeModifiedById,
                                                                ct.MODIFIEDON AS ContentTypeModifiedOn,
                                                                mb.USERNAME AS ContentTypeModifiedByName,
                                                                ct.VERSION AS ContentTypeVersion,
                                                                ct.SORTORDER AS ContentTypeSortOrder,
                                                                ct.STATUS AS ContentTypeStatus,
                                                                ct.SOURCETYPE AS ContentTypeSourceType,

                                                                ROW_NUMBER() OVER (ORDER BY ct.CONTENTTYPEID) AS RowNum

                                                            FROM dbo.MCONTENTTYPE ct
                                                            LEFT JOIN dbo.MSPACE s      ON ct.SPACEID = s.SPACEID
                                                            LEFT JOIN dbo.MUSER cb      ON ct.CREATEDBYID = cb.USERID
                                                            LEFT JOIN dbo.MUSER mb      ON ct.MODIFIEDBYID = mb.USERID
                                                            LEFT JOIN dbo.MCLIENT cl    ON ct.TENANTID = cl.CLIENTID
                                                        )

                                                        SELECT*
                                                        FROM ContentType
                                                        WHERE
                                                            (@firstnumber = -1 AND @maxresult = -1)
                                                            OR(RowNum BETWEEN @firstnumber AND @maxresult);";
        public const string GET_CONTENTTYPE_LIST = @"SELECT
                                                    ct.CONTENTTYPEID      AS ContentTypeId,
                                                    ct.SPACEID            AS ContentTypeSpaceId,
                                                    ct.NAME               AS ContentTypeName,
                                                    ct.SLUG               AS ContentTypeSlug,
                                                    ct.DESCRIPTION        AS ContentTypeDescription,
                                                    ct.ICON               AS ContentTypeIcon,
                                                    ct.CONTENTCATEGORYID  AS ContentCategoryId,
                                                    ct.DISPLAYFIELD       AS ContentTypeDisplayField,
                                                    ct.FIELDS             AS ContentTypeFields,
                                                    ct.VALIDATION         AS ContentTypeValidation,
                                                    ct.AIFEATURES         AS ContentTypeAIFeatures,
                                                    ct.ISSYSTEM           AS ContentTypeIsSystem,
                                                    ct.CREATEDBYID        AS ContentTypeCreatedById,
                                                    ct.CREATEDON          AS ContentTypeCreatedOn,
                                                    ct.MODIFIEDBYID       AS ContentTypeModifiedById,
                                                    ct.MODIFIEDON         AS ContentTypeModifiedOn,
                                                    ct.SORTORDER          AS ContentTypeSortOrder,
                                                    ct.STATUS             AS ContentTypeStatus,
                                                    ct.VERSION            AS ContentTypeVersion,
                                                    ct.SOURCETYPE         AS ContentTypeSourceType,
                                                    ct.TENANTID           AS ContentTypeTenantId
                                                FROM MCONTENTTYPE ct;";
    }
}