-- =============================================================================
-- Phase 5 of the Portlet+Page+Dashboard backend roadmap: provisioning tiers
-- (Standard vs Custom + Entitlement gating) + MUSERVSDASHBOARD.
--
-- SOURCETYPE semantics for MPORTLETTYPE/MPORTLET/MPAGE/MDASHBOARD (no column
-- change needed — it already exists on all four via the standard-fields
-- template). Live GB5DEMO data check (2026-08-01) showed real, MEANINGFULLY
-- USED values across this repo's existing 5-tier convention (1=Framework,
-- 2=DevAdmin, 3=ImpAdmin, 4=Admin, 5=User) — NOT a flat single value as
-- originally assumed. Rather than redefining SOURCETYPE to a new 0/1 scheme
-- (which would require migrating already-live Phase 1-3 data and diverge from
-- every other module in this codebase), this phase ENFORCES a Standard/Custom
-- split on the EXISTING tiers:
--   SourceType IN (1,2)     = Standard (Framework/DevAdmin — vendor-authored,
--                              eligible for Entitlement gating)
--   SourceType IN (3,4,5)   = Custom   (ImpAdmin/Admin/User — tenant-authored,
--                              entitlement check bypassed; already tenant-scoped)
-- Enforced in FrameworkBLL.PortletType.PortletTypeBLL / FrameworkBLL.Dashboard.
-- DashboardBLL, not by a DB constraint (existing SourceType values 1-5 all
-- remain valid; this is a read-time policy, not a schema restriction).
--
-- CORRECTED 2026-08-01 (live GB5DEMO check + user pushback): Entitlement does
-- NOT live in each client's own OLTP database. EntitlementBLL.Options.
-- EntitlementDbOptions (doc comment) and EntitlementLoginFactory both confirm
-- MENTITLEMENT* tables live in ONE SHARED PLATFORM database (logical
-- connection name "EntitlementDb", e.g. physical DB USCIMPSYS per
-- 20260724_Entitlement_EntitlementDb_Provisioning_SqlServer.sql's MSERVERCONFIG
-- seed) — NOT the per-client OLTP DB where MPORTLETTYPE/MDASHBOARD live (e.g.
-- GB5DEMO, which has zero MENTITLEMENT* tables, confirmed live). A FEATUREID
-- FK from a client-DB table to MENTITLEMENTFEATURE is therefore impossible
-- (cross-database FKs don't exist in SQL Server) — this was a design error in
-- the original version of this migration.
--
-- Fix: mirror MENTITLEMENTFEATUREMENUSETMAP's existing precedent for this
-- exact cross-boundary shape, but using the business-key FEATURECODE (not the
-- numeric FeatureId, which has no meaning outside EntitlementDb) as the
-- unenforced cross-database reference — this also matches how
-- IEntitlementService.IsFeatureEnabledAsync is actually called (by
-- FeatureCode, never FeatureId). PORTLETTYPEID/DASHBOARDID keep real enforced
-- FKs since these mapping tables live in the SAME client OLTP DB as
-- MPORTLETTYPE/MDASHBOARD.
-- =============================================================================

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MENTITLEMENTFEATUREPORTLETTYPEMAP')
BEGIN
    CREATE TABLE [DBO].[MENTITLEMENTFEATUREPORTLETTYPEMAP]
    (
        FEATUREPORTLETTYPEMAPID INT           NOT NULL CONSTRAINT PK_MENTITLEMENTFEATUREPORTLETTYPEMAP PRIMARY KEY,
        FEATURECODE             NVARCHAR(100) NOT NULL,   -- unenforced cross-DB business key — MENTITLEMENTFEATURE.FEATURECODE lives in the separate EntitlementDb platform database
        PORTLETTYPEID           INT           NOT NULL CONSTRAINT FK_MENTITLEMENTFEATUREPORTLETTYPEMAP_PORTLETTYPE
                                                         REFERENCES MPORTLETTYPE(PORTLETTYPEID),
        CONSTRAINT UX_MENTITLEMENTFEATUREPORTLETTYPEMAP UNIQUE (FEATURECODE, PORTLETTYPEID)
    );
    PRINT 'MENTITLEMENTFEATUREPORTLETTYPEMAP table created.';
END
ELSE
    PRINT 'MENTITLEMENTFEATUREPORTLETTYPEMAP table already exists — skipped.';
GO

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MENTITLEMENTFEATUREDASHBOARDMAP')
BEGIN
    CREATE TABLE [DBO].[MENTITLEMENTFEATUREDASHBOARDMAP]
    (
        FEATUREDASHBOARDMAPID INT           NOT NULL CONSTRAINT PK_MENTITLEMENTFEATUREDASHBOARDMAP PRIMARY KEY,
        FEATURECODE           NVARCHAR(100) NOT NULL,   -- unenforced cross-DB business key — see MENTITLEMENTFEATUREPORTLETTYPEMAP note above
        DASHBOARDID           INT           NOT NULL CONSTRAINT FK_MENTITLEMENTFEATUREDASHBOARDMAP_DASHBOARD
                                                       REFERENCES MDASHBOARD(DASHBOARDID),
        CONSTRAINT UX_MENTITLEMENTFEATUREDASHBOARDMAP UNIQUE (FEATURECODE, DASHBOARDID)
    );
    PRINT 'MENTITLEMENTFEATUREDASHBOARDMAP table created.';
END
ELSE
    PRINT 'MENTITLEMENTFEATUREDASHBOARDMAP table already exists — skipped.';
GO

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MUSERVSDASHBOARD')
BEGIN
    -- Completes the User-personalization tier for Dashboard, mirroring
    -- MUSERVSPORTLET's role for Portlet: Entitlement/License -> Admin (custom
    -- authoring) -> Role default (MROLEVSDASHBOARD) -> User (this table).
    CREATE TABLE [DBO].[MUSERVSDASHBOARD]
    (
        USERVSDASHBOARDID INT      NOT NULL CONSTRAINT PK_MUSERVSDASHBOARD PRIMARY KEY,
        USERID            INT      NOT NULL CONSTRAINT FK_MUSERVSDASHBOARD_USERID REFERENCES MUSER(USERID),
        DASHBOARDID       INT      NOT NULL CONSTRAINT FK_MUSERVSDASHBOARD_DASHBOARDID REFERENCES MDASHBOARD(DASHBOARDID),
        SLNO              SMALLINT NOT NULL CONSTRAINT DF_MUSERVSDASHBOARD_SLNO DEFAULT 1,
        ISPINNED          TINYINT  NOT NULL CONSTRAINT DF_MUSERVSDASHBOARD_ISPINNED DEFAULT 0,
        CONSTRAINT UX_MUSERVSDASHBOARD_USERID_DASHBOARDID UNIQUE (USERID, DASHBOARDID),
        CONSTRAINT CK_MUSERVSDASHBOARD_ISPINNED CHECK (ISPINNED IN (0,1))
    );
    PRINT 'MUSERVSDASHBOARD table created.';
END
ELSE
    PRINT 'MUSERVSDASHBOARD table already exists — skipped.';
GO

SET QUOTED_IDENTIFIER ON;
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_MUSERVSDASHBOARD_USERID' AND object_id = OBJECT_ID('MUSERVSDASHBOARD')
)
BEGIN
    CREATE INDEX IX_MUSERVSDASHBOARD_USERID ON MUSERVSDASHBOARD (USERID);
    PRINT 'MUSERVSDASHBOARD: IX_MUSERVSDASHBOARD_USERID index created.';
END
ELSE
    PRINT 'MUSERVSDASHBOARD: IX_MUSERVSDASHBOARD_USERID index already exists — skipped.';
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_MENTITLEMENTFEATUREPORTLETTYPEMAP_TYPEID' AND object_id = OBJECT_ID('MENTITLEMENTFEATUREPORTLETTYPEMAP')
)
BEGIN
    CREATE INDEX IX_MENTITLEMENTFEATUREPORTLETTYPEMAP_TYPEID ON MENTITLEMENTFEATUREPORTLETTYPEMAP (PORTLETTYPEID);
    PRINT 'MENTITLEMENTFEATUREPORTLETTYPEMAP: IX_MENTITLEMENTFEATUREPORTLETTYPEMAP_TYPEID index created.';
END
ELSE
    PRINT 'MENTITLEMENTFEATUREPORTLETTYPEMAP: IX_MENTITLEMENTFEATUREPORTLETTYPEMAP_TYPEID index already exists — skipped.';
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE name = 'IX_MENTITLEMENTFEATUREDASHBOARDMAP_DASHID' AND object_id = OBJECT_ID('MENTITLEMENTFEATUREDASHBOARDMAP')
)
BEGIN
    CREATE INDEX IX_MENTITLEMENTFEATUREDASHBOARDMAP_DASHID ON MENTITLEMENTFEATUREDASHBOARDMAP (DASHBOARDID);
    PRINT 'MENTITLEMENTFEATUREDASHBOARDMAP: IX_MENTITLEMENTFEATUREDASHBOARDMAP_DASHID index created.';
END
ELSE
    PRINT 'MENTITLEMENTFEATUREDASHBOARDMAP: IX_MENTITLEMENTFEATUREDASHBOARDMAP_DASHID index already exists — skipped.';
GO

-- =============================================================================
-- MAUTONUMBER seed — required for AutoNumber.GetAutoNumber("USERVSDASHBOARD", ...)
-- =============================================================================
IF NOT EXISTS (SELECT 1 FROM MAUTONUMBER WHERE ENTITYCODE = 'USERVSDASHBOARD')
    INSERT INTO MAUTONUMBER (ENTITYID, ENTITYCODE, AUTOID) VALUES (-1384000003, 'USERVSDASHBOARD', -1499999999);
GO

-- =============================================================================
-- END OF MIGRATION 20260801_ProvisioningTiers (SQL Server)
-- =============================================================================