-- =============================================================================
-- GB5 BI platform — Phase 1: MBIVIEW, the independent, reusable saved-view table.
-- Database: SQL Server (tenant business DB)
-- Plan:     /Users/venkatv/.claude/plans/gb5-in-product-analytics-dapper-bear.md
-- Run AFTER: Analytics_BIRebrand_TableRename_SqlServer.sql (MBICATALOG must exist).
--
-- Mirrors MREPORT (1) <-> MREPORTVIEW (many): one MBICATALOG (dataset) can back many
-- saved MBIVIEW rows (e.g. a KPI total AND a separate region-by-month pivot, both over
-- the same catalog). A Portlet placement (MPORTLET.BIVIEWID, added below) just POINTS AT
-- a view — it never owns the view's definition, so the same saved view can eventually be
-- reused across more than one placement without duplication.
--
-- WIDGETTYPE is deliberately VARCHAR, not a new numeric enum: GB5WidgetType is already a
-- TS string union ('kpi-card'/'apex-chart'/'table'/'pivot-table') consumed directly in
-- Angular @switch/@case blocks, with no existing numeric convention to reuse (unlike
-- FIELDTYPE/Aggregation, which do have one worth mirroring elsewhere in this schema).
-- =============================================================================

CREATE TABLE [DBO].MBIVIEW
(
    BIVIEWID                INT             NOT NULL,
    BICATALOGID              INT             NOT NULL,
    BIVIEWCODE               NVARCHAR(20)    NOT NULL,
    BIVIEWNAME               NVARCHAR(200)   NOT NULL,
    WIDGETTYPE               VARCHAR(20)     NOT NULL,       -- 'kpi-card' | 'apex-chart' | 'table' | 'pivot-table'

    BIDEFINITIONJSON         NVARCHAR(MAX)   NOT NULL,       -- serialized AnalysisQueryDefinition (Dimensions/Measures/Filters/Rows/Columns/Sort/TopN)
    ADAPTERCONFIGJSON        NVARCHAR(MAX)   NULL,           -- serialized GB5WidgetAdapterConfig + saved pivot slice (Phase 5)

    SOURCETYPE               TINYINT         NOT NULL CONSTRAINT DF_MBIVIEW_SOURCETYPE DEFAULT 5,  -- mirrors MREPORTVIEW.SOURCETYPE: 1 Framework,2 DevAdmin,3 ImpAdmin,4 Admin,5 User
    TENANTID                 INT             NOT NULL CONSTRAINT DF_MBIVIEW_TENANTID    DEFAULT -1, -- ownership sentinel, matches MBICATALOG/MWORKSPACE/MDATASOURCE convention

    VERSION                  SMALLINT        NOT NULL CONSTRAINT DF_MBIVIEW_VERSION   DEFAULT 0,
    STATUS                   TINYINT         NOT NULL CONSTRAINT DF_MBIVIEW_STATUS    DEFAULT 1,
    SORTORDER                SMALLINT        NOT NULL CONSTRAINT DF_MBIVIEW_SORTORDER DEFAULT 9999,
    CREATEDBYID              INT             NOT NULL,
    CREATEDON                DATETIME        NOT NULL CONSTRAINT DF_MBIVIEW_CREATEDON  DEFAULT GETDATE(),
    MODIFIEDBYID             INT             NOT NULL,
    MODIFIEDON               DATETIME        NOT NULL CONSTRAINT DF_MBIVIEW_MODIFIEDON DEFAULT GETDATE(),

    CONSTRAINT PK_MBIVIEW_BIVIEWID       PRIMARY KEY (BIVIEWID),
    CONSTRAINT UK_MBIVIEW_CODE           UNIQUE (BIVIEWCODE, TENANTID),
    CONSTRAINT FK_MBIVIEW_BICATALOGID    FOREIGN KEY (BICATALOGID) REFERENCES MBICATALOG(BICATALOGID),
    CONSTRAINT FK_MBIVIEW_TENANTID       FOREIGN KEY (TENANTID)    REFERENCES MCLIENT(CLIENTID),
    CONSTRAINT CK_MBIVIEW_WIDGETTYPE     CHECK (WIDGETTYPE IN ('kpi-card','apex-chart','table','pivot-table')),
    CONSTRAINT CK_MBIVIEW_SOURCETYPE     CHECK (SOURCETYPE IN (1,2,3,4,5)),
    CONSTRAINT CK_MBIVIEW_STATUS         CHECK (STATUS IN (0,1,2,3,4,5))
)
GO

CREATE INDEX IX_MBIVIEW_BICATALOGID ON MBIVIEW (BICATALOGID)
GO

-- MPORTLET.BIVIEWID — a lightweight, nullable FK pointer, the same idiom MPORTLET already
-- uses for DEFAULTCRITERIACONFIGID/WEBSERVICESETTINGID/FORMLINKMENUID/REPORTFORMATID.
-- NULL means "legacy Analysis-type portlet, still using the flat CHARTXAXIS/CHARTYAXIS pair"
-- (Gb5WidgetPortletComponent's fallback path, Phase 3) — nothing is backfilled here.
IF COL_LENGTH('MPORTLET', 'BIVIEWID') IS NULL
    ALTER TABLE MPORTLET ADD BIVIEWID INT NULL;
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.foreign_keys
    WHERE name = 'FK_MPORTLET_BIVIEWID' AND parent_object_id = OBJECT_ID('MPORTLET')
)
    ALTER TABLE MPORTLET ADD CONSTRAINT FK_MPORTLET_BIVIEWID FOREIGN KEY (BIVIEWID) REFERENCES MBIVIEW(BIVIEWID);
GO

-- =============================================================================
-- END OF MIGRATION 20260806_BIView_Schema (SQL Server)
-- =============================================================================