namespace CMSDAL.Query.Collection;
public static class CollectionQB
{
    public const string GET_LIST = @"
        SELECT COLLECTIONID AS CollectionId, COLLECTIONCODE AS CollectionCode,
               COLLECTIONNAME AS CollectionName, COLLECTIONTYPE AS CollectionType,
               STATUS AS Status
        FROM DBO.MCMSCOLLECTION WHERE TENANTID = @TenantId AND STATUS = 1 ORDER BY COLLECTIONNAME";

    public const string GET_BY_ID = @"
        SELECT COLLECTIONID AS CollectionId, COLLECTIONCODE AS CollectionCode,
               COLLECTIONNAME AS CollectionName, COLLECTIONTYPE AS CollectionType,
               QUERYJSON AS QueryJson, STATUS AS Status, TENANTID AS TenantId
        FROM DBO.MCMSCOLLECTION WHERE COLLECTIONID = @CollectionId AND TENANTID = @TenantId";

    public const string INSERT = @"
        INSERT INTO DBO.MCMSCOLLECTION (COLLECTIONCODE, COLLECTIONNAME, COLLECTIONTYPE, QUERYJSON, STATUS, TENANTID, CREATEDBYID, MODIFIEDBYID)
        VALUES (@CollectionCode, @CollectionName, @CollectionType, @QueryJson, @Status, @TenantId, @CreatedById, @ModifiedById)";

    public const string UPDATE = @"
        UPDATE DBO.MCMSCOLLECTION SET COLLECTIONNAME=@CollectionName, QUERYJSON=@QueryJson,
               STATUS=@Status, MODIFIEDBYID=@ModifiedById, MODIFIEDON=GETDATE()
        WHERE COLLECTIONID=@CollectionId AND TENANTID=@TenantId";

    public const string GET_MANUAL_ITEMS = @"
        SELECT ci.COLLECTIONITEMID AS CollectionItemId, ci.COLLECTIONID AS CollectionId,
               ci.CONTENTID AS ContentId, c.TITLE AS ContentTitle, c.SLUG AS ContentSlug,
               c.CONTENTSTATUS AS ContentStatus, ci.POSITIONNO AS PositionNo
        FROM DBO.TCMSCOLLECTIONITEM ci
        JOIN DBO.TCONTENT c ON c.CONTENTID = ci.CONTENTID
        WHERE ci.COLLECTIONID = @CollectionId AND ci.TENANTID = @TenantId AND ci.STATUS = 1
        ORDER BY ci.POSITIONNO";
}