using System;

namespace CMSDAL.Query.Content
{
    public static class ContentQB
    {
        public const string GET_CONTENT = @"
            SELECT
                c.CONTENTID              AS ContentId,
                c.CONTENTTYPEID          AS ContentTypeId,
                c.CMSSPACEID             AS ContentCMSSpaceId,
                c.TITLE                  AS ContentTitle,
                c.SLUG                   AS ContentSlug,
                c.CONTENTSTATUS          AS ContentStatus,
                c.DATAJSON               AS ContentDataJson,
                c.METADATAJSON           AS ContentMetaDataJson,

                c.PUBLISHEDVERSION       AS ContentPublishedVersion,
                c.CURRENTVERSION         AS ContentCurrentVersion,
                c.VERSION                AS ContentVersion,

                c.PUBLISHEDATE           AS ContentPublishDate,
                c.PUBLISHEDBYID          AS ContentPublishedById,
                pb.USERNAME              AS ContentPublishedByName,
                c.EXPIRESAT              AS ContentExpiresAt,

                c.TENANTID               AS ContentTenantId,

                c.CREATEDBYID            AS ContentCreatedById,
                c.CREATEDON              AS ContentCreatedOn,
                cb.USERNAME              AS ContentCreatedByName,

                c.MODIFIEDBYID           AS ContentModifiedById,
                c.MODIFIEDON             AS ContentModifiedOn,
                mb.USERNAME              AS ContentModifiedByName,

                c.STATUS                 AS ContentRecordStatus

            FROM DBO.TCONTENT c
            LEFT JOIN DBO.MCONTENTTYPE ct ON c.CONTENTTYPEID = ct.CONTENTTYPEID
            LEFT JOIN DBO.MSPACE s        ON c.CMSSPACEID = s.SPACEID
            LEFT JOIN DBO.MUSER pb        ON c.PUBLISHEDBYID = pb.USERID
            LEFT JOIN DBO.MUSER cb        ON c.CREATEDBYID = cb.USERID
            LEFT JOIN DBO.MUSER mb        ON c.MODIFIEDBYID = mb.USERID
            LEFT JOIN DBO.MCLIENT cl      ON c.TENANTID = cl.CLIENTID
            WHERE c.CONTENTID = @contentid
              AND c.TENANTID  = @TenantId;";

        // Resolves a pasted CMS slug or URL back to its Content record — backs the
        // ShowContentLink "paste slug/URL instead of typing a raw ContentId" picker.
        public const string GET_CONTENT_BY_SLUG = @"
            SELECT
                c.CONTENTID              AS ContentId,
                c.TITLE                  AS ContentTitle,
                c.SLUG                   AS ContentSlug
            FROM DBO.TCONTENT c
            WHERE c.SLUG     = @Slug
              AND c.TENANTID = @TenantId;";


        public const string SAVE_CONTENT = @"
            INSERT INTO DBO.TCONTENT
            (
                CONTENTTYPEID,
                CMSSPACEID,
                TITLE,
                SLUG,
                CONTENTSTATUS,
                DATAJSON,
                METADATAJSON,
                PUBLISHEDVERSION,
                CURRENTVERSION,
                PUBLISHEDATE,
                PUBLISHEDBYID,
                EXPIRESAT,
                VERSION,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON,
                TENANTID,
                STATUS
            )
            VALUES
            (
                @ContentTypeId,
                @ContentCMSSpaceId,
                @ContentTitle,
                @ContentSlug,
                @ContentStatus,
                @ContentDataJson,
                @ContentMetaDataJson,
                @ContentPublishedVersion,
                @ContentCurrentVersion,
                @ContentPublishDate,
                @ContentPublishedById,
                @ContentExpiresAt,
                @ContentVersion,
                @ContentCreatedById,
                @ContentCreatedOn,
                @ContentModifiedById,
                @ContentModifiedOn,
                @ContentTenantId,
                @ContentRecordStatus
            );
            SELECT SCOPE_IDENTITY();";


        public const string UPDATE_CONTENT = @"
            UPDATE DBO.TCONTENT
            SET
                CONTENTTYPEID = @ContentTypeId,
                CMSSPACEID = @ContentCMSSpaceId,
                TITLE = @ContentTitle,
                SLUG = @ContentSlug,
                CONTENTSTATUS = @ContentStatus,
                DATAJSON = @ContentDataJson,
                METADATAJSON = @ContentMetaDataJson,
                PUBLISHEDVERSION = @ContentPublishedVersion,
                CURRENTVERSION = @ContentCurrentVersion,
                PUBLISHEDATE = @ContentPublishDate,
                PUBLISHEDBYID = @ContentPublishedById,
                EXPIRESAT = @ContentExpiresAt,
                VERSION = @ContentVersion,
                MODIFIEDBYID = @ContentModifiedById,
                MODIFIEDON = @ContentModifiedOn,
                STATUS = @ContentRecordStatus
            WHERE
                CONTENTID = @ContentId
              AND TENANTID = @TenantId;";


        public const string DELETE_CONTENT = @"
            UPDATE DBO.TCONTENT SET STATUS = 0, MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETDATE()
            WHERE CONTENTID = @ContentId AND TENANTID = @TenantId";


        public const string GET_SELECTLIST_CONTENT = @"
            WITH ContentList AS (
                SELECT
                    A.CONTENTID  AS ContentId,
                    A.TITLE      AS ContentTitle,
                    A.SLUG AS ContentSlug,
                    ROW_NUMBER() OVER (ORDER BY A.CONTENTID) AS RowNum
                FROM DBO.TCONTENT A
                WHERE A.TENANTID = @TenantId AND A.STATUS = 1
            )
            SELECT ContentId, ContentTitle , ContentSlug
            FROM ContentList
            WHERE
                (@firstnumber = -1 AND @maxresult = -1)
                OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_CONTENT_LIST = @"
            SELECT c.CONTENTID AS ContentId, c.TITLE AS ContentTitle, c.SLUG AS ContentSlug,
                   c.CONTENTSTATUS AS ContentStatus, ct.CONTENTTYPENAME AS ContentTypeName,
                   s.SPACENAME AS SpaceName, cb.USERNAME AS CreatedBy,
                   c.CREATEDON AS ContentCreatedOn, c.MODIFIEDON AS ContentModifiedOn,
                   c.PUBLISHEDATE AS ContentPublishDate
            FROM DBO.TCONTENT c
            LEFT JOIN DBO.MCONTENTTYPE ct ON ct.CONTENTTYPEID = c.CONTENTTYPEID
            LEFT JOIN DBO.MSPACE s        ON s.SPACEID = c.CMSSPACEID
            LEFT JOIN DBO.MUSER cb        ON cb.USERID = c.CREATEDBYID
            WHERE c.TENANTID = @TenantId AND c.STATUS = 1
              AND (@SpaceId = -1 OR c.CMSSPACEID = @SpaceId)
              AND (@ContentTypeId = -1 OR c.CONTENTTYPEID = @ContentTypeId)
              AND (@ContentStatus = -1 OR c.CONTENTSTATUS = @ContentStatus)
              AND (@AuthorId = -1 OR c.CREATEDBYID = @AuthorId)
              AND (@FromDate IS NULL OR c.CREATEDON >= @FromDate)
              AND (@ToDate IS NULL OR c.CREATEDON <= @ToDate)
            ORDER BY c.MODIFIEDON DESC";

        public const string SEARCH_CONTENT = @"
            SELECT c.CONTENTID AS ContentId, c.TITLE AS ContentTitle, c.SLUG AS ContentSlug,
                   c.CONTENTSTATUS AS ContentStatus, ct.CONTENTTYPENAME AS ContentTypeName,
                   c.MODIFIEDON AS ContentModifiedOn
            FROM DBO.TCONTENT c
            LEFT JOIN DBO.MCONTENTTYPE ct ON ct.CONTENTTYPEID = c.CONTENTTYPEID
            WHERE c.TENANTID = @TenantId AND c.STATUS = 1
              AND (c.TITLE LIKE '%' + @SearchText + '%' OR c.SLUG LIKE '%' + @SearchText + '%')
              AND (@SpaceId = -1 OR c.CMSSPACEID = @SpaceId)
              AND (@ContentTypeId = -1 OR c.CONTENTTYPEID = @ContentTypeId)
            ORDER BY c.MODIFIEDON DESC";

        public const string UPDATE_CONTENT_STATUS = @"
            UPDATE DBO.TCONTENT SET CONTENTSTATUS = @ContentStatus, MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETDATE()
            WHERE CONTENTID = @ContentId AND TENANTID = @TenantId";

        public const string UPDATE_CONTENT_STATUS_PUBLISH = @"
            UPDATE DBO.TCONTENT SET CONTENTSTATUS = @ContentStatus, PUBLISHEDATE = GETDATE(),
                MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETDATE()
            WHERE CONTENTID = @ContentId AND TENANTID = @TenantId";

        public const string GET_CONTENT_STATUS = @"
            SELECT CONTENTSTATUS FROM DBO.TCONTENT WHERE CONTENTID = @ContentId AND TENANTID = @TenantId";
    }
}