-- =============================================================================
-- GEF (Guided Execution Framework) + SIP (Signal & Insight Processor) — Framework Schema (SQL Server)
-- Migration: 20260901
--
-- Adds the two persistence tables backing this session's GB5Framework GEF/SIP slice
-- (plan: ~/.claude/plans/in-entitlement-also-we-async-charm.md, Phase 0 — "GEF skeleton...
-- SIP skeleton (IPL table + one real event subscription)"). Both tables are pure, append-only
-- logs — never updated, never deleted — the same shape as this repo's existing GOP execution
-- logs (LGOPEXECUTIONNODELOG / LGOPEXECUTIONSTATELOG — see DB/Migrations/GOP_DDL.sql and
-- FrameworkDAL/Query/GOP/GopQueueQB.cs).
--
-- PK STRATEGY — DEVIATION FROM CLAUDE-DB-SCHEMA.md's DEFAULT T-PREFIX RULE, NOTED EXPLICITLY:
-- CLAUDE-DB-SCHEMA.md §1/§4 defines T-prefix ("Transaction") tables as Application-generated
-- (AutoNumber) PK, and reserves IDENTITY for L-prefix ("Log/Audit") append-only tables. Both
-- tables below are named with a T-prefix (TGEFOUTCOMEFEEDBACK / TSIPPATTERNENTRY, per this
-- session's build brief) but are semantically append-only logs, not mutable business
-- transactions — SIP's own contract (GB5Shared/SIP/PatternEntryDTO.cs) explicitly states its
-- ledger rows are "immutable once written... never updated or deleted". Rather than either
-- (a) using an AutoNumber PK on a table that will never be updated (which would need two new
-- MAUTONUMBER seed rows purely to satisfy a naming convention the table's own semantics don't
-- need), or (b) silently renaming the tables to an L-prefix the brief didn't ask for, this
-- migration keeps the brief's literal T-prefixed table names but gives both an IDENTITY PK —
-- matching the *semantic* rule in CLAUDE-DB-SCHEMA.md §4 ("Log... these are never referenced as
-- FKs") and the exact precedent already live in this same FrameworkDAL for GOP's own append-only
-- log tables. Flagged here for whoever reviews this next — if a future consumer needs to
-- FK-reference a row in either table, that is the signal to migrate it to a real AutoNumber PK
-- (true T-prefix semantics) instead.
--
-- Column casing / constraint naming: ALL CAPS columns, PK_{TABLE}, DF_{TABLE}_{COLUMN},
-- FK_{TABLE}_{COLUMN}, CK_{TABLE}_{COLUMN}, IX_{TABLE}_{COLUMNS} — matches the rest of this repo.
-- Idempotent via IF OBJECT_ID(...) IS NULL guards (safe to run against a partially-migrated DB).
--
-- NOT exercised against a live DB in this session (explicitly out of scope per this task's
-- brief — no DB connection expected to be available). Verify against a real dev DB before this
-- phase is called done, per the standing lesson from the Recruitment stabilization pass
-- (feedback_gb5_migration_verification.md: verify NOT NULL/CHECK/max-length via
-- INFORMATION_SCHEMA/sys.check_constraints before trusting a template migration).
-- =============================================================================

-- ─────────────────────────────────────────────────────────────────────────────
-- 1. TGEFOUTCOMEFEEDBACK — GEF outcome-feedback log (IGEFEngine.RecordOutcomeFeedbackAsync)
--    FOCUSADHERENCE: 0=No, 1=Yes (TINYINT per CLAUDE-DB-SCHEMA.md §3 — booleans are TINYINT,
--    never BIT, in this repo's DDL convention; OutcomeFeedbackDTO.FocusAdherence (C# bool) maps
--    to this column at the DAL boundary — GEFOutcomeFeedbackDAL.SaveOutcomeFeedback).
-- ─────────────────────────────────────────────────────────────────────────────
IF OBJECT_ID('TGEFOUTCOMEFEEDBACK', 'U') IS NULL
BEGIN
CREATE TABLE TGEFOUTCOMEFEEDBACK
(
    OUTCOMEFEEDBACKID    INT             IDENTITY(1,1),
    DOMAIN               NVARCHAR(50)    NOT NULL,
    ENTITYCODE           NVARCHAR(50)    NOT NULL,
    ENTITYID             INT             NOT NULL,
    OUTCOMETYPE          NVARCHAR(50)    NOT NULL,
    BLOCKER              NVARCHAR(500)   NULL,
    FOCUSADHERENCE       TINYINT         NOT NULL    CONSTRAINT DF_TGEFOUTCOMEFEEDBACK_FOCUSADHERENCE DEFAULT 0,
    TENANTID             INT             NOT NULL    CONSTRAINT DF_TGEFOUTCOMEFEEDBACK_TENANTID DEFAULT -1,
    RECORDEDBYID         INT             NOT NULL    CONSTRAINT DF_TGEFOUTCOMEFEEDBACK_RECORDEDBYID DEFAULT -1,
    RECORDEDON           DATETIME        NOT NULL    CONSTRAINT DF_TGEFOUTCOMEFEEDBACK_RECORDEDON DEFAULT GETDATE(),

    CONSTRAINT PK_TGEFOUTCOMEFEEDBACK              PRIMARY KEY (OUTCOMEFEEDBACKID),
    CONSTRAINT FK_TGEFOUTCOMEFEEDBACK_TENANTID     FOREIGN KEY (TENANTID)     REFERENCES MCLIENT(CLIENTID),
    CONSTRAINT FK_TGEFOUTCOMEFEEDBACK_RECORDEDBYID FOREIGN KEY (RECORDEDBYID) REFERENCES MUSER(USERID),
    CONSTRAINT CK_TGEFOUTCOMEFEEDBACK_FOCUSADHERENCE CHECK (FOCUSADHERENCE IN (0,1))
);
END
GO

-- Covers GEFOutcomeFeedbackQB's tenant scoping and any future GetOutcomeFeedback-by-entity read.
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('TGEFOUTCOMEFEEDBACK') AND name = 'IX_TGEFOUTCOMEFEEDBACK_LOOKUP')
    CREATE INDEX IX_TGEFOUTCOMEFEEDBACK_LOOKUP ON TGEFOUTCOMEFEEDBACK (TENANTID, DOMAIN, ENTITYCODE, ENTITYID);
GO


-- ─────────────────────────────────────────────────────────────────────────────
-- 2. TSIPPATTERNENTRY — Interaction Pattern Ledger (IPL) row (ISIPSignalService.EmitSignalAsync)
--    Immutable once written — SIP never updates or deletes a row here (see
--    FrameworkBLL/SIP/SIPSignalService.cs class doc). GetPatternSummaryAsync groups by
--    SIGNALTYPE for a given (TENANTID, DOMAIN, ENTITYCODE, ENTITYID) — see index below.
-- ─────────────────────────────────────────────────────────────────────────────
IF OBJECT_ID('TSIPPATTERNENTRY', 'U') IS NULL
BEGIN
CREATE TABLE TSIPPATTERNENTRY
(
    PATTERNENTRYID       INT             IDENTITY(1,1),
    DOMAIN               NVARCHAR(50)    NOT NULL,
    ENTITYCODE           NVARCHAR(50)    NOT NULL,
    ENTITYID             INT             NOT NULL,
    SIGNALTYPE           NVARCHAR(50)    NOT NULL,
    DETAIL               NVARCHAR(1000)  NULL,
    STRENGTH             NVARCHAR(20)    NOT NULL,
    ACTORROLE            NVARCHAR(50)    NULL,
    ACTORID              INT             NULL,   -- FK MUSER(USERID), nullable: EmitSignalAsync's
                                                   -- contract does not always have a human actor
    TENANTID             INT             NOT NULL    CONSTRAINT DF_TSIPPATTERNENTRY_TENANTID DEFAULT -1,
    DETECTEDON           DATETIME        NOT NULL    CONSTRAINT DF_TSIPPATTERNENTRY_DETECTEDON DEFAULT GETDATE(),

    CONSTRAINT PK_TSIPPATTERNENTRY          PRIMARY KEY (PATTERNENTRYID),
    CONSTRAINT FK_TSIPPATTERNENTRY_TENANTID FOREIGN KEY (TENANTID) REFERENCES MCLIENT(CLIENTID),
    CONSTRAINT FK_TSIPPATTERNENTRY_ACTORID  FOREIGN KEY (ACTORID)  REFERENCES MUSER(USERID)
);
END
GO

-- Required index (per this session's build brief) — GetPatternSummaryAsync's WHERE + GROUP BY
-- SIGNALTYPE filters on exactly this column combination.
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('TSIPPATTERNENTRY') AND name = 'IX_TSIPPATTERNENTRY_LOOKUP')
    CREATE INDEX IX_TSIPPATTERNENTRY_LOOKUP ON TSIPPATTERNENTRY (TENANTID, DOMAIN, ENTITYCODE, ENTITYID, SIGNALTYPE);
GO