-- =============================================================================
-- CMS Dynamic Data Binding foundation (Site/Page platform Phase 12)
--
-- MCMSDATASOURCE — a reusable named external-data source: an endpoint URL template with
-- {placeholder} substitution, an auth mode, and a Vault path holding the actual secret (never
-- stored inline — CLAUDE.md's non-negotiable secrets rule). TENANTID defaults -1 (system-wide,
-- reusable across tenants) same convention as MBLOCK/MCONTENTCONTAINERTYPE — a data source
-- definition is a component-TYPE-like concept, not tenant-owned content.
--
-- TCMSBLOCKDATABINDING — 1:1 per ContentBlock that opts into dynamic data (UNIQUE on
-- CONTENTBLOCKID). BINDINGMODE selects who performs the fetch:
--   0 = ServerSideRenderMerge — GB5 fetches+merges into PropsJson before GetSiteDocument returns;
--       the FE/renderer never knows the block is dynamic.
--   1 = ClientSideFetch — FE calls back to GB5's own GetBlockData proxy endpoint at runtime using
--       the opaque {DataSourceCode, ParamsJson} contract; the third-party endpoint and its Vault
--       secret are still never exposed to the browser.
-- TENANTID here must equal its owning ContentBlock's TENANTID (enforced at BLL level via
-- CrossTenantGuard, same pattern as ContentContainerBLL/ContentBlockBLL) — not a DB CHECK,
-- because a block's own TENANTID may legitimately be -1 (a template seed block).
--
-- Idempotent: guarded by IF OBJECT_ID(...) IS NULL.
-- =============================================================================

IF OBJECT_ID('DBO.MCMSDATASOURCE', 'U') IS NULL
BEGIN
    CREATE TABLE DBO.MCMSDATASOURCE (
        DATASOURCEID            INT             NOT NULL IDENTITY(1,1),
        DATASOURCECODE          VARCHAR(50)     NOT NULL,
        DATASOURCENAME          NVARCHAR(200)   NOT NULL,
        DATASOURCEDESCRIPTION   NVARCHAR(1000)  NULL,
        ENDPOINTTEMPLATE        NVARCHAR(1000)  NOT NULL,
        HTTPMETHOD              VARCHAR(10)     NOT NULL CONSTRAINT DF_MCMSDS_HTTPMETHOD DEFAULT 'GET',
        AUTHMODE                TINYINT         NOT NULL CONSTRAINT DF_MCMSDS_AUTHMODE   DEFAULT 0,
        AUTHHEADERNAME          VARCHAR(100)    NULL,
        VAULTSECRETPATH         NVARCHAR(300)   NULL,
        CACHETTLSECONDS         INT             NOT NULL CONSTRAINT DF_MCMSDS_CACHETTL   DEFAULT 300,
        RESPONSEMAPPINGJSON     NVARCHAR(MAX)   NULL,
        STATUS                  TINYINT         NOT NULL CONSTRAINT DF_MCMSDS_STATUS     DEFAULT 1,
        TENANTID                INT             NOT NULL CONSTRAINT DF_MCMSDS_TENANTID   DEFAULT -1,
        CREATEDBYID             INT             NOT NULL,
        CREATEDON               DATETIME        NOT NULL CONSTRAINT DF_MCMSDS_CREATEDON  DEFAULT GETDATE(),
        MODIFIEDBYID            INT             NOT NULL,
        MODIFIEDON              DATETIME        NOT NULL CONSTRAINT DF_MCMSDS_MODIFIEDON DEFAULT GETDATE(),
        CONSTRAINT PK_MCMSDATASOURCE             PRIMARY KEY (DATASOURCEID),
        CONSTRAINT UQ_MCMSDATASOURCE_CODE_TENANT UNIQUE (DATASOURCECODE, TENANTID),
        CONSTRAINT FK_MCMSDS_CREATEDBYID  FOREIGN KEY (CREATEDBYID)  REFERENCES MUSER(USERID),
        CONSTRAINT FK_MCMSDS_MODIFIEDBYID FOREIGN KEY (MODIFIEDBYID) REFERENCES MUSER(USERID)
    );
END
GO

IF OBJECT_ID('DBO.TCMSBLOCKDATABINDING', 'U') IS NULL
BEGIN
    CREATE TABLE DBO.TCMSBLOCKDATABINDING (
        BLOCKDATABINDINGID      INT             NOT NULL IDENTITY(1,1),
        CONTENTBLOCKID          INT             NOT NULL,
        DATASOURCEID            INT             NOT NULL,
        BINDINGMODE             TINYINT         NOT NULL CONSTRAINT DF_TCBDB_BINDINGMODE DEFAULT 0,
        PARAMSJSON              NVARCHAR(MAX)   NULL,
        STATUS                  TINYINT         NOT NULL CONSTRAINT DF_TCBDB_STATUS      DEFAULT 1,
        TENANTID                INT             NOT NULL CONSTRAINT DF_TCBDB_TENANTID    DEFAULT -1,
        CREATEDBYID             INT             NOT NULL,
        CREATEDON               DATETIME        NOT NULL CONSTRAINT DF_TCBDB_CREATEDON   DEFAULT GETDATE(),
        MODIFIEDBYID            INT             NOT NULL,
        MODIFIEDON              DATETIME        NOT NULL CONSTRAINT DF_TCBDB_MODIFIEDON  DEFAULT GETDATE(),
        CONSTRAINT PK_TCMSBLOCKDATABINDING         PRIMARY KEY (BLOCKDATABINDINGID),
        CONSTRAINT UQ_TCMSBLOCKDATABINDING_BLOCKID UNIQUE (CONTENTBLOCKID),
        CONSTRAINT FK_TCBDB_CONTENTBLOCKID  FOREIGN KEY (CONTENTBLOCKID) REFERENCES TCONTENTBLOCK(CONTENTBLOCKID),
        CONSTRAINT FK_TCBDB_DATASOURCEID    FOREIGN KEY (DATASOURCEID)   REFERENCES MCMSDATASOURCE(DATASOURCEID),
        CONSTRAINT FK_TCBDB_CREATEDBYID     FOREIGN KEY (CREATEDBYID)    REFERENCES MUSER(USERID),
        CONSTRAINT FK_TCBDB_MODIFIEDBYID    FOREIGN KEY (MODIFIEDBYID)   REFERENCES MUSER(USERID)
    );
END
GO