namespace CMSDAL.Query.ContentBlock;
public static class ContentBlockQB
{
    public const string GET_BY_CONTAINER = @"
        SELECT cb.CONTENTBLOCKID AS ContentBlockId, cb.CONTENTCONTAINERID AS ContentContainerId,
               cb.BLOCKID AS BlockId, b.BLOCKCODE AS BlockCode, b.BLOCKNAME AS BlockName,
               b.SCHEMAJSON AS SchemaJson, cb.ZONENAME AS ZoneName, cb.POSITIONNO AS PositionNo,
               cb.PROPSJSON AS PropsJson, cb.STATUS AS Status
        FROM DBO.TCONTENTBLOCK cb
        JOIN DBO.MBLOCK b ON b.BLOCKID = cb.BLOCKID
        WHERE cb.CONTENTCONTAINERID = @ContainerId AND cb.TENANTID = @TenantId AND cb.STATUS = 1
        ORDER BY cb.ZONENAME, cb.POSITIONNO";

    public const string GET_FULL_DOCUMENT = @"
        SELECT
            c.CONTENTID AS ContentId, c.TITLE AS ContentTitle, c.SLUG AS ContentSlug,
            c.CONTENTSTATUS AS ContentStatus,
            cc.CONTENTCONTAINERID AS ContentContainerId, cc.POSITIONNO AS PositionNo,
            cc.CONTAINERLABEL AS ContainerLabel, cct.CONTAINERTYPECODE AS ContainerTypeCode,
            cct.ZONEDEFINITIONSJSON AS ZoneDefinitionsJson,
            cb.CONTENTBLOCKID AS ContentBlockId, cb.ZONENAME AS ZoneName,
            cb.POSITIONNO AS PositionNo, b.BLOCKCODE AS BlockCode,
            b.BLOCKNAME AS BlockName, b.SCHEMAJSON AS SchemaJson, cb.PROPSJSON AS PropsJson
        FROM DBO.TCONTENT c
        JOIN DBO.TCONTENTCONTAINER cc ON cc.CONTENTID = c.CONTENTID AND cc.TENANTID = @TenantId AND cc.STATUS = 1
        JOIN DBO.MCONTENTCONTAINERTYPE cct ON cct.CONTAINERTYPEID = cc.CONTAINERTYPEID
        LEFT JOIN DBO.TCONTENTBLOCK cb ON cb.CONTENTCONTAINERID = cc.CONTENTCONTAINERID AND cb.TENANTID = @TenantId AND cb.STATUS = 1
        LEFT JOIN DBO.MBLOCK b ON b.BLOCKID = cb.BLOCKID
        WHERE c.CONTENTID = @ContentId AND c.TENANTID = @TenantId
        ORDER BY cc.POSITIONNO, cb.ZONENAME, cb.POSITIONNO";

    public const string INSERT = @"
        INSERT INTO DBO.TCONTENTBLOCK
            (CONTENTCONTAINERID, BLOCKID, ZONENAME, POSITIONNO, PROPSJSON, STATUS, TENANTID, CREATEDBYID, MODIFIEDBYID)
        VALUES
            (@ContentContainerId, @BlockId, @ZoneName, @PositionNo, @PropsJson, 1, @TenantId, @CreatedById, @ModifiedById)";

    public const string UPDATE_PROPS = @"
        UPDATE DBO.TCONTENTBLOCK
        SET PROPSJSON = @PropsJson, POSITIONNO = @PositionNo, ZONENAME = @ZoneName,
            MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETDATE()
        WHERE CONTENTBLOCKID = @ContentBlockId AND TENANTID = @TenantId";

    public const string SOFT_DELETE = @"
        UPDATE DBO.TCONTENTBLOCK SET STATUS = 0, MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETDATE()
        WHERE CONTENTBLOCKID = @ContentBlockId AND TENANTID = @TenantId";

    // Deliberately NOT tenant-filtered — used by the cross-tenant guard to discover a
    // container's TRUE owning tenant before attaching a new block to it, regardless of which
    // tenant the caller claims to be.
    public const string GET_CONTAINER_TENANT = @"
        SELECT TENANTID FROM DBO.TCONTENTCONTAINER WHERE CONTENTCONTAINERID = @ContentContainerId";

    // Used by the cross-tenant guard when attaching a data binding (Phase 12) — returns the
    // block's true owning TenantId, independent of the caller's own claimed tenant.
    public const string GET_CONTENTBLOCK_TENANT = @"
        SELECT TENANTID FROM DBO.TCONTENTBLOCK WHERE CONTENTBLOCKID = @ContentBlockId";

    // Site/Page platform Phase 14 — the template materializer authors ContainerTreeJson blocks
    // by BlockCode (matches ContentBlockDTO's own shape) but TCONTENTBLOCK.INSERT requires the
    // real BlockId FK; this resolves one to the other. Same "own tenant OR system-wide (-1)"
    // resolution as MBLOCK's other lookups.
    public const string GET_BLOCK_BY_CODE = @"
        SELECT BLOCKID FROM DBO.MBLOCK
        WHERE BLOCKCODE = @BlockCode AND (TENANTID = @TenantId OR TENANTID = -1) AND STATUS = 1";
}