-- =============================================================================
--  KMS (Knowledge Management System) — Initial Schema
--  Date   : 2026-06-05
--  Author : GoodBooks Platform Team
--  Target : SQL Server — KMS Database (or KMS schema within enterprise DB)
-- =============================================================================
--  Tables created:
--    MKMSPACE          — Knowledge Spaces (top-level grouping)
--    MKMDOMAIN         — Knowledge Domains within a Space
--    MKMARTIFACT       — Knowledge Artifacts (header)
--    MKMARTIFACTCONTENT— Artifact Content (detail, no standard fields)
--    MKMARTIFACTVERSION— Immutable publish snapshots (log)
--    MKMRELATIONSHIP   — Knowledge graph edges
--    MKMCONNECTOR      — Source connector configuration
--    TKMJOB            — Extraction pipeline job tracking
--    TKMCANDIDATE      — LLM-extracted candidates awaiting review
--    TKMKNOWLEDGEGAP   — Unanswered query tracking
--    LKMGOVERNANCE     — Governance transition log (append-only)
--    LKMAUDIT          — General audit log (append-only)
-- =============================================================================

-- ─────────────────────────────────────────────────────────────
--  MKMSPACE  — Knowledge Space (top-level grouping)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE [DBO].[MKMSPACE]
(
    -- PK
    SPACEID         INT           NOT NULL,

    -- BUSINESS COLUMNS
    SPACECODE       NVARCHAR(20)  NOT NULL,
    SPACENAME       NVARCHAR(200) NOT NULL,
    DESCRIPTION     NVARCHAR(1000),

    -- STANDARD FIELDS
    VERSION         SMALLINT  NOT NULL CONSTRAINT DF_MKMSPACE_VERSION    DEFAULT 0,
    STATUS          TINYINT   NOT NULL CONSTRAINT DF_MKMSPACE_STATUS      DEFAULT 1,   -- 0 Pending, 1 Active, 2 Deleted, 3 Amended, 4 Inactive, 5 Archived
    SORTORDER       SMALLINT  NOT NULL CONSTRAINT DF_MKMSPACE_SORTORDER   DEFAULT 9999,
    CREATEDBYID     INT       NOT NULL,
    CREATEDON       DATETIME  NOT NULL CONSTRAINT DF_MKMSPACE_CREATEDON   DEFAULT GETDATE(),
    MODIFIEDBYID    INT       NOT NULL,
    MODIFIEDON      DATETIME  NOT NULL CONSTRAINT DF_MKMSPACE_MODIFIEDON  DEFAULT GETDATE(),
    SOURCETYPE      TINYINT   NOT NULL CONSTRAINT DF_MKMSPACE_SOURCETYPE  DEFAULT 5,   -- 1 Framework, 2 Devadmin, 3 Impadmin, 4 Admin, 5 User
    TENANTID        INT       NOT NULL CONSTRAINT DF_MKMSPACE_TENANTID    DEFAULT -1,

    CONSTRAINT PK_MKMSPACE_SPACEID              PRIMARY KEY (SPACEID),
    CONSTRAINT UK_MKMSPACE_SPACECODE_TENANTID   UNIQUE (SPACECODE, TENANTID),
    CONSTRAINT FK_MKMSPACE_CREATEDBYID          FOREIGN KEY (CREATEDBYID)  REFERENCES MUSER(USERID),
    CONSTRAINT FK_MKMSPACE_MODIFIEDBYID         FOREIGN KEY (MODIFIEDBYID) REFERENCES MUSER(USERID),
    CONSTRAINT FK_MKMSPACE_TENANTID             FOREIGN KEY (TENANTID)     REFERENCES MCLIENT(CLIENTID),
    CONSTRAINT CK_MKMSPACE_STATUS               CHECK (STATUS     IN (0,1,2,3,4,5)),
    CONSTRAINT CK_MKMSPACE_SOURCETYPE           CHECK (SOURCETYPE IN (1,2,3,4,5))
)
GO

-- ─────────────────────────────────────────────────────────────
--  MKMDOMAIN  — Knowledge Domain (within a Space)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE [DBO].[MKMDOMAIN]
(
    -- PK
    DOMAINID        INT           NOT NULL,

    -- PARENT FK
    SPACEID         INT           NOT NULL,

    -- BUSINESS COLUMNS
    DOMAINCODE      NVARCHAR(20)  NOT NULL,
    DOMAINNAME      NVARCHAR(200) NOT NULL,
    DESCRIPTION     NVARCHAR(1000),

    -- STANDARD FIELDS
    VERSION         SMALLINT  NOT NULL CONSTRAINT DF_MKMDOMAIN_VERSION    DEFAULT 0,
    STATUS          TINYINT   NOT NULL CONSTRAINT DF_MKMDOMAIN_STATUS      DEFAULT 1,
    SORTORDER       SMALLINT  NOT NULL CONSTRAINT DF_MKMDOMAIN_SORTORDER   DEFAULT 9999,
    CREATEDBYID     INT       NOT NULL,
    CREATEDON       DATETIME  NOT NULL CONSTRAINT DF_MKMDOMAIN_CREATEDON   DEFAULT GETDATE(),
    MODIFIEDBYID    INT       NOT NULL,
    MODIFIEDON      DATETIME  NOT NULL CONSTRAINT DF_MKMDOMAIN_MODIFIEDON  DEFAULT GETDATE(),
    SOURCETYPE      TINYINT   NOT NULL CONSTRAINT DF_MKMDOMAIN_SOURCETYPE  DEFAULT 5,
    TENANTID        INT       NOT NULL CONSTRAINT DF_MKMDOMAIN_TENANTID    DEFAULT -1,

    CONSTRAINT PK_MKMDOMAIN_DOMAINID                PRIMARY KEY (DOMAINID),
    CONSTRAINT UK_MKMDOMAIN_DOMAINCODE_TENANTID     UNIQUE (DOMAINCODE, TENANTID),
    CONSTRAINT FK_MKMDOMAIN_SPACEID                 FOREIGN KEY (SPACEID)      REFERENCES MKMSPACE(SPACEID),
    CONSTRAINT FK_MKMDOMAIN_CREATEDBYID             FOREIGN KEY (CREATEDBYID)  REFERENCES MUSER(USERID),
    CONSTRAINT FK_MKMDOMAIN_MODIFIEDBYID            FOREIGN KEY (MODIFIEDBYID) REFERENCES MUSER(USERID),
    CONSTRAINT FK_MKMDOMAIN_TENANTID                FOREIGN KEY (TENANTID)     REFERENCES MCLIENT(CLIENTID),
    CONSTRAINT CK_MKMDOMAIN_STATUS                  CHECK (STATUS     IN (0,1,2,3,4,5)),
    CONSTRAINT CK_MKMDOMAIN_SOURCETYPE              CHECK (SOURCETYPE IN (1,2,3,4,5))
)
GO

-- ─────────────────────────────────────────────────────────────
--  MKMARTIFACT  — Knowledge Artifact (header)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE [DBO].[MKMARTIFACT]
(
    -- PK
    ARTIFACTID          INT           NOT NULL,

    -- PARENT FK
    DOMAINID            INT           NOT NULL,

    -- BUSINESS COLUMNS
    ARTIFACTCODE        NVARCHAR(20)  NOT NULL,
    TITLE               NVARCHAR(500) NOT NULL,
    SUMMARY             NVARCHAR(MAX),
    ARTIFACTTYPE        TINYINT       NOT NULL CONSTRAINT DF_MKMARTIFACT_ARTIFACTTYPE    DEFAULT 6,  -- 0 Concept, 1 Fact, 2 Policy, 3 Rule, 4 Process, 5 Insight, 6 Document
    SOURCEKIND          TINYINT       NOT NULL CONSTRAINT DF_MKMARTIFACT_SOURCEKIND      DEFAULT 0,  -- 0 Manual, 1 FileUpload, 2 CMS, 3 DMS, 4 Database, 5 API, 6 ExternalFeed
    GOVERNANCESTATE     TINYINT       NOT NULL CONSTRAINT DF_MKMARTIFACT_GOVERNANCESTATE DEFAULT 0,  -- 0 Draft, 1 UnderReview, 2 Approved, 3 Published, 4 Retired
    SCOPELEVEL          TINYINT       NOT NULL CONSTRAINT DF_MKMARTIFACT_SCOPELEVEL      DEFAULT 3,  -- 0 Global, 1 Industry, 2 Region, 3 Tenant, 4 Department, 5 Role
    SCOPEVALUE          NVARCHAR(200) NOT NULL CONSTRAINT DF_MKMARTIFACT_SCOPEVALUE      DEFAULT '',
    VERSIONNUMBER       SMALLINT      NOT NULL CONSTRAINT DF_MKMARTIFACT_VERSIONNUMBER   DEFAULT 1,
    PARENTARTIFACTID    INT           NOT NULL CONSTRAINT DF_MKMARTIFACT_PARENTARTIFACTID DEFAULT -1,
    OWNERUSERID         INT           NOT NULL CONSTRAINT DF_MKMARTIFACT_OWNERUSERID     DEFAULT -1,
    REVIEWERUSERID      INT           NOT NULL CONSTRAINT DF_MKMARTIFACT_REVIEWERUSERID  DEFAULT -1,
    CONFIDENCESCORE     NUMERIC(9,4)  NOT NULL CONSTRAINT DF_MKMARTIFACT_CONFIDENCESCORE DEFAULT 0,
    EFFECTIVEFROM       DATE,
    EFFECTIVETO         DATE,
    SOURCEREFERENCE     NVARCHAR(1000),
    SOURCEMETADATA      NVARCHAR(MAX),
    QDRANTPOINTID       NVARCHAR(200),
    EMBEDDINGMODEL      NVARCHAR(100),
    LASTEMBEDDEDON      DATETIME,
    EXTRACTIONJOBID     INT           NOT NULL CONSTRAINT DF_MKMARTIFACT_EXTRACTIONJOBID DEFAULT -1,
    TAGS                NVARCHAR(MAX),

    -- STANDARD FIELDS
    VERSION             SMALLINT  NOT NULL CONSTRAINT DF_MKMARTIFACT_VERSION    DEFAULT 0,
    STATUS              TINYINT   NOT NULL CONSTRAINT DF_MKMARTIFACT_STATUS      DEFAULT 1,
    SORTORDER           SMALLINT  NOT NULL CONSTRAINT DF_MKMARTIFACT_SORTORDER   DEFAULT 9999,
    CREATEDBYID         INT       NOT NULL,
    CREATEDON           DATETIME  NOT NULL CONSTRAINT DF_MKMARTIFACT_CREATEDON   DEFAULT GETDATE(),
    MODIFIEDBYID        INT       NOT NULL,
    MODIFIEDON          DATETIME  NOT NULL CONSTRAINT DF_MKMARTIFACT_MODIFIEDON  DEFAULT GETDATE(),
    SOURCETYPE          TINYINT   NOT NULL CONSTRAINT DF_MKMARTIFACT_SOURCETYPE  DEFAULT 5,
    TENANTID            INT       NOT NULL CONSTRAINT DF_MKMARTIFACT_TENANTID    DEFAULT -1,

    CONSTRAINT PK_MKMARTIFACT_ARTIFACTID                PRIMARY KEY (ARTIFACTID),
    CONSTRAINT UK_MKMARTIFACT_ARTIFACTCODE_TENANTID     UNIQUE (ARTIFACTCODE, TENANTID),
    CONSTRAINT FK_MKMARTIFACT_DOMAINID                  FOREIGN KEY (DOMAINID)          REFERENCES MKMDOMAIN(DOMAINID),
    CONSTRAINT FK_MKMARTIFACT_PARENTARTIFACTID          FOREIGN KEY (PARENTARTIFACTID)  REFERENCES MKMARTIFACT(ARTIFACTID),
    CONSTRAINT FK_MKMARTIFACT_OWNERUSERID               FOREIGN KEY (OWNERUSERID)       REFERENCES MUSER(USERID),
    CONSTRAINT FK_MKMARTIFACT_REVIEWERUSERID            FOREIGN KEY (REVIEWERUSERID)    REFERENCES MUSER(USERID),
    CONSTRAINT FK_MKMARTIFACT_CREATEDBYID               FOREIGN KEY (CREATEDBYID)       REFERENCES MUSER(USERID),
    CONSTRAINT FK_MKMARTIFACT_MODIFIEDBYID              FOREIGN KEY (MODIFIEDBYID)      REFERENCES MUSER(USERID),
    CONSTRAINT FK_MKMARTIFACT_TENANTID                  FOREIGN KEY (TENANTID)          REFERENCES MCLIENT(CLIENTID),
    CONSTRAINT CK_MKMARTIFACT_ARTIFACTTYPE              CHECK (ARTIFACTTYPE      IN (0,1,2,3,4,5,6)),
    CONSTRAINT CK_MKMARTIFACT_SOURCEKIND                CHECK (SOURCEKIND        IN (0,1,2,3,4,5,6)),
    CONSTRAINT CK_MKMARTIFACT_GOVERNANCESTATE           CHECK (GOVERNANCESTATE   IN (0,1,2,3,4)),
    CONSTRAINT CK_MKMARTIFACT_SCOPELEVEL                CHECK (SCOPELEVEL        IN (0,1,2,3,4,5)),
    CONSTRAINT CK_MKMARTIFACT_STATUS                    CHECK (STATUS            IN (0,1,2,3,4,5)),
    CONSTRAINT CK_MKMARTIFACT_SOURCETYPE                CHECK (SOURCETYPE        IN (1,2,3,4,5))
)
GO

-- Index for tenant-scoped artifact listing
CREATE INDEX IX_MKMARTIFACT_TENANTID_DOMAINID    ON [DBO].[MKMARTIFACT] (TENANTID, DOMAINID)
GO
CREATE INDEX IX_MKMARTIFACT_GOVERNANCESTATE      ON [DBO].[MKMARTIFACT] (TENANTID, GOVERNANCESTATE)
GO

-- ─────────────────────────────────────────────────────────────
--  MKMARTIFACTCONTENT  — Artifact Content (detail, no standard fields)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE [DBO].[MKMARTIFACTCONTENT]
(
    -- PK + PARENT FK
    CONTENTID       INT           NOT NULL,
    ARTIFACTID      INT           NOT NULL,

    -- SEQUENCE
    SLNO            SMALLINT      NOT NULL CONSTRAINT DF_MKMARTIFACTCONTENT_SLNO         DEFAULT 1,

    -- BUSINESS COLUMNS
    CONTENTBODY     NVARCHAR(MAX) NOT NULL,
    CONTENTTYPE     NVARCHAR(50)  NOT NULL CONSTRAINT DF_MKMARTIFACTCONTENT_CONTENTTYPE  DEFAULT 'Text',
    LANGUAGECODE    NVARCHAR(10)  NOT NULL CONSTRAINT DF_MKMARTIFACTCONTENT_LANGUAGECODE DEFAULT 'en',
    ISPRIMARY       TINYINT       NOT NULL CONSTRAINT DF_MKMARTIFACTCONTENT_ISPRIMARY    DEFAULT 0,  -- 0 No, 1 Yes
    CHUNKINDEX      SMALLINT      NOT NULL CONSTRAINT DF_MKMARTIFACTCONTENT_CHUNKINDEX   DEFAULT 0,
    CHUNKTOTAL      SMALLINT      NOT NULL CONSTRAINT DF_MKMARTIFACTCONTENT_CHUNKTOTAL   DEFAULT 1,
    CREATEDON       DATETIME      NOT NULL CONSTRAINT DF_MKMARTIFACTCONTENT_CREATEDON    DEFAULT GETDATE(),

    CONSTRAINT PK_MKMARTIFACTCONTENT_CONTENTID      PRIMARY KEY (CONTENTID),
    CONSTRAINT FK_MKMARTIFACTCONTENT_ARTIFACTID     FOREIGN KEY (ARTIFACTID) REFERENCES MKMARTIFACT(ARTIFACTID),
    CONSTRAINT CK_MKMARTIFACTCONTENT_ISPRIMARY      CHECK (ISPRIMARY IN (0,1))
)
GO

-- ─────────────────────────────────────────────────────────────
--  MKMARTIFACTVERSION  — Immutable Publish Snapshots (log)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE [DBO].[MKMARTIFACTVERSION]
(
    ARTIFACTVERSIONID   INT IDENTITY(1,1) NOT NULL,
    ARTIFACTID          INT           NOT NULL,
    VERSIONNUMBER       SMALLINT      NOT NULL,
    SNAPSHOTJSON        NVARCHAR(MAX) NOT NULL,
    GOVERNANCESTATE     TINYINT       NOT NULL,   -- 0 Draft, 1 UnderReview, 2 Approved, 3 Published, 4 Retired
    CHANGESUMMARY       NVARCHAR(1000),
    CREATEDBYID         INT           NOT NULL,
    CREATEDON           DATETIME      NOT NULL CONSTRAINT DF_MKMARTIFACTVERSION_CREATEDON DEFAULT GETDATE(),

    CONSTRAINT PK_MKMARTIFACTVERSION_ARTIFACTVERSIONID  PRIMARY KEY (ARTIFACTVERSIONID),
    CONSTRAINT UK_MKMARTIFACTVERSION_ART_VER            UNIQUE (ARTIFACTID, VERSIONNUMBER),
    CONSTRAINT FK_MKMARTIFACTVERSION_ARTIFACTID         FOREIGN KEY (ARTIFACTID)   REFERENCES MKMARTIFACT(ARTIFACTID),
    CONSTRAINT FK_MKMARTIFACTVERSION_CREATEDBYID        FOREIGN KEY (CREATEDBYID)  REFERENCES MUSER(USERID),
    CONSTRAINT CK_MKMARTIFACTVERSION_GOVERNANCESTATE    CHECK (GOVERNANCESTATE IN (0,1,2,3,4))
)
GO

-- ─────────────────────────────────────────────────────────────
--  MKMRELATIONSHIP  — Knowledge Graph Edges
-- ─────────────────────────────────────────────────────────────
CREATE TABLE [DBO].[MKMRELATIONSHIP]
(
    -- PK
    RELATIONSHIPID      INT     NOT NULL,

    -- BUSINESS COLUMNS
    SOURCEARTIFACTID    INT     NOT NULL,
    TARGETARTIFACTID    INT     NOT NULL,
    RELATIONTYPE        TINYINT NOT NULL,  -- 0 Explains, 1 DependsOn, 2 References, 3 DerivedFrom, 4 ReplacedBy, 5 Contradicts
    CONFIDENCESCORE     NUMERIC(9,4) NOT NULL CONSTRAINT DF_MKMRELATIONSHIP_CONFIDENCESCORE DEFAULT 0,
    ISLLMDERIVED        TINYINT NOT NULL CONSTRAINT DF_MKMRELATIONSHIP_ISLLMDERIVED DEFAULT 0,  -- 0 No, 1 Yes

    -- STANDARD FIELDS
    VERSION             SMALLINT  NOT NULL CONSTRAINT DF_MKMRELATIONSHIP_VERSION    DEFAULT 0,
    STATUS              TINYINT   NOT NULL CONSTRAINT DF_MKMRELATIONSHIP_STATUS      DEFAULT 1,
    SORTORDER           SMALLINT  NOT NULL CONSTRAINT DF_MKMRELATIONSHIP_SORTORDER   DEFAULT 9999,
    CREATEDBYID         INT       NOT NULL,
    CREATEDON           DATETIME  NOT NULL CONSTRAINT DF_MKMRELATIONSHIP_CREATEDON   DEFAULT GETDATE(),
    MODIFIEDBYID        INT       NOT NULL,
    MODIFIEDON          DATETIME  NOT NULL CONSTRAINT DF_MKMRELATIONSHIP_MODIFIEDON  DEFAULT GETDATE(),
    SOURCETYPE          TINYINT   NOT NULL CONSTRAINT DF_MKMRELATIONSHIP_SOURCETYPE  DEFAULT 5,
    TENANTID            INT       NOT NULL CONSTRAINT DF_MKMRELATIONSHIP_TENANTID    DEFAULT -1,

    CONSTRAINT PK_MKMRELATIONSHIP_RELATIONSHIPID                PRIMARY KEY (RELATIONSHIPID),
    CONSTRAINT UK_MKMRELATIONSHIP_SRC_TGT_RELATIONTYPE         UNIQUE (SOURCEARTIFACTID, TARGETARTIFACTID, RELATIONTYPE),
    CONSTRAINT FK_MKMRELATIONSHIP_SOURCEARTIFACTID             FOREIGN KEY (SOURCEARTIFACTID) REFERENCES MKMARTIFACT(ARTIFACTID),
    CONSTRAINT FK_MKMRELATIONSHIP_TARGETARTIFACTID             FOREIGN KEY (TARGETARTIFACTID) REFERENCES MKMARTIFACT(ARTIFACTID),
    CONSTRAINT FK_MKMRELATIONSHIP_CREATEDBYID                  FOREIGN KEY (CREATEDBYID)      REFERENCES MUSER(USERID),
    CONSTRAINT FK_MKMRELATIONSHIP_MODIFIEDBYID                 FOREIGN KEY (MODIFIEDBYID)     REFERENCES MUSER(USERID),
    CONSTRAINT FK_MKMRELATIONSHIP_TENANTID                     FOREIGN KEY (TENANTID)         REFERENCES MCLIENT(CLIENTID),
    CONSTRAINT CK_MKMRELATIONSHIP_RELATIONTYPE                 CHECK (RELATIONTYPE  IN (0,1,2,3,4,5)),
    CONSTRAINT CK_MKMRELATIONSHIP_ISLLMDERIVED                 CHECK (ISLLMDERIVED  IN (0,1)),
    CONSTRAINT CK_MKMRELATIONSHIP_STATUS                       CHECK (STATUS        IN (0,1,2,3,4,5)),
    CONSTRAINT CK_MKMRELATIONSHIP_SOURCETYPE                   CHECK (SOURCETYPE    IN (1,2,3,4,5))
)
GO

-- ─────────────────────────────────────────────────────────────
--  MKMCONNECTOR  — Source Connector Configuration
-- ─────────────────────────────────────────────────────────────
CREATE TABLE [DBO].[MKMCONNECTOR]
(
    -- PK
    CONNECTORID         INT           NOT NULL,

    -- BUSINESS COLUMNS
    CONNECTORCODE       NVARCHAR(20)  NOT NULL,
    CONNECTORNAME       NVARCHAR(200) NOT NULL,
    CONNECTORTYPE       TINYINT       NOT NULL,  -- 0 File, 1 CMS, 2 DMS, 3 Database, 4 API, 5 ExternalFeed
    SPACEID             INT           NOT NULL CONSTRAINT DF_MKMCONNECTOR_SPACEID     DEFAULT -1,
    DOMAINID            INT           NOT NULL CONSTRAINT DF_MKMCONNECTOR_DOMAINID    DEFAULT -1,
    CONFIGJSON          NVARCHAR(MAX) NOT NULL,
    SCHEDULECRON        NVARCHAR(100),
    LASTRUNON           DATETIME,
    LASTRUNSTATUS       TINYINT       NOT NULL CONSTRAINT DF_MKMCONNECTOR_LASTRUNSTATUS DEFAULT 0,  -- 0 NotRun, 1 Success, 2 Failed

    -- STANDARD FIELDS
    VERSION             SMALLINT  NOT NULL CONSTRAINT DF_MKMCONNECTOR_VERSION    DEFAULT 0,
    STATUS              TINYINT   NOT NULL CONSTRAINT DF_MKMCONNECTOR_STATUS      DEFAULT 1,
    SORTORDER           SMALLINT  NOT NULL CONSTRAINT DF_MKMCONNECTOR_SORTORDER   DEFAULT 9999,
    CREATEDBYID         INT       NOT NULL,
    CREATEDON           DATETIME  NOT NULL CONSTRAINT DF_MKMCONNECTOR_CREATEDON   DEFAULT GETDATE(),
    MODIFIEDBYID        INT       NOT NULL,
    MODIFIEDON          DATETIME  NOT NULL CONSTRAINT DF_MKMCONNECTOR_MODIFIEDON  DEFAULT GETDATE(),
    SOURCETYPE          TINYINT   NOT NULL CONSTRAINT DF_MKMCONNECTOR_SOURCETYPE  DEFAULT 5,
    TENANTID            INT       NOT NULL CONSTRAINT DF_MKMCONNECTOR_TENANTID    DEFAULT -1,

    CONSTRAINT PK_MKMCONNECTOR_CONNECTORID              PRIMARY KEY (CONNECTORID),
    CONSTRAINT UK_MKMCONNECTOR_CONNECTORCODE_TENANTID   UNIQUE (CONNECTORCODE, TENANTID),
    CONSTRAINT FK_MKMCONNECTOR_SPACEID                  FOREIGN KEY (SPACEID)      REFERENCES MKMSPACE(SPACEID),
    CONSTRAINT FK_MKMCONNECTOR_DOMAINID                 FOREIGN KEY (DOMAINID)     REFERENCES MKMDOMAIN(DOMAINID),
    CONSTRAINT FK_MKMCONNECTOR_CREATEDBYID              FOREIGN KEY (CREATEDBYID)  REFERENCES MUSER(USERID),
    CONSTRAINT FK_MKMCONNECTOR_MODIFIEDBYID             FOREIGN KEY (MODIFIEDBYID) REFERENCES MUSER(USERID),
    CONSTRAINT FK_MKMCONNECTOR_TENANTID                 FOREIGN KEY (TENANTID)     REFERENCES MCLIENT(CLIENTID),
    CONSTRAINT CK_MKMCONNECTOR_CONNECTORTYPE            CHECK (CONNECTORTYPE    IN (0,1,2,3,4,5)),
    CONSTRAINT CK_MKMCONNECTOR_LASTRUNSTATUS            CHECK (LASTRUNSTATUS    IN (0,1,2)),
    CONSTRAINT CK_MKMCONNECTOR_STATUS                   CHECK (STATUS           IN (0,1,2,3,4,5)),
    CONSTRAINT CK_MKMCONNECTOR_SOURCETYPE               CHECK (SOURCETYPE       IN (1,2,3,4,5))
)
GO

-- ─────────────────────────────────────────────────────────────
--  TKMJOB  — Extraction Pipeline Job (Transaction)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE [DBO].[TKMJOB]
(
    -- PK
    JOBID               INT           NOT NULL,

    -- BUSINESS COLUMNS
    CONNECTORID         INT           NOT NULL CONSTRAINT DF_TKMJOB_CONNECTORID     DEFAULT -1,
    SOURCEREFERENCE     NVARCHAR(1000) NOT NULL,
    CONTENTHASH         NVARCHAR(64),
    JOBSTATUS           TINYINT       NOT NULL CONSTRAINT DF_TKMJOB_JOBSTATUS       DEFAULT 0,  -- 0 Pending, 1 Chunking, 2 Embedding, 3 Extracting, 4 CandidateReview, 5 Completed, 6 Failed
    ERRORMESSAGE        NVARCHAR(MAX),
    CHUNKSTOTAL         INT           NOT NULL CONSTRAINT DF_TKMJOB_CHUNKSTOTAL     DEFAULT 0,
    CHUNKSPROCESSED     INT           NOT NULL CONSTRAINT DF_TKMJOB_CHUNKSPROCESSED DEFAULT 0,
    CANDIDATESCREATED   INT           NOT NULL CONSTRAINT DF_TKMJOB_CANDIDATESCREATED DEFAULT 0,
    CELERYTASKID        NVARCHAR(255),
    STARTEDON           DATETIME,
    COMPLETEDON         DATETIME,

    -- STANDARD FIELDS
    VERSION             SMALLINT  NOT NULL CONSTRAINT DF_TKMJOB_VERSION    DEFAULT 0,
    STATUS              TINYINT   NOT NULL CONSTRAINT DF_TKMJOB_STATUS      DEFAULT 1,
    SORTORDER           SMALLINT  NOT NULL CONSTRAINT DF_TKMJOB_SORTORDER   DEFAULT 9999,
    CREATEDBYID         INT       NOT NULL,
    CREATEDON           DATETIME  NOT NULL CONSTRAINT DF_TKMJOB_CREATEDON   DEFAULT GETDATE(),
    MODIFIEDBYID        INT       NOT NULL,
    MODIFIEDON          DATETIME  NOT NULL CONSTRAINT DF_TKMJOB_MODIFIEDON  DEFAULT GETDATE(),
    SOURCETYPE          TINYINT   NOT NULL CONSTRAINT DF_TKMJOB_SOURCETYPE  DEFAULT 5,
    TENANTID            INT       NOT NULL CONSTRAINT DF_TKMJOB_TENANTID    DEFAULT -1,

    CONSTRAINT PK_TKMJOB_JOBID              PRIMARY KEY (JOBID),
    CONSTRAINT FK_TKMJOB_CONNECTORID        FOREIGN KEY (CONNECTORID)  REFERENCES MKMCONNECTOR(CONNECTORID),
    CONSTRAINT FK_TKMJOB_CREATEDBYID        FOREIGN KEY (CREATEDBYID)  REFERENCES MUSER(USERID),
    CONSTRAINT FK_TKMJOB_MODIFIEDBYID       FOREIGN KEY (MODIFIEDBYID) REFERENCES MUSER(USERID),
    CONSTRAINT FK_TKMJOB_TENANTID           FOREIGN KEY (TENANTID)     REFERENCES MCLIENT(CLIENTID),
    CONSTRAINT CK_TKMJOB_JOBSTATUS          CHECK (JOBSTATUS   IN (0,1,2,3,4,5,6)),
    CONSTRAINT CK_TKMJOB_STATUS             CHECK (STATUS      IN (0,1,2,3,4,5)),
    CONSTRAINT CK_TKMJOB_SOURCETYPE         CHECK (SOURCETYPE  IN (1,2,3,4,5))
)
GO

CREATE INDEX IX_TKMJOB_TENANTID_JOBSTATUS ON [DBO].[TKMJOB] (TENANTID, JOBSTATUS)
GO

-- ─────────────────────────────────────────────────────────────
--  TKMCANDIDATE  — LLM Extraction Candidate (awaiting review)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE [DBO].[TKMCANDIDATE]
(
    -- PK
    CANDIDATEID         INT           NOT NULL,

    -- BUSINESS COLUMNS
    JOBID               INT           NOT NULL,
    DOMAINID            INT           NOT NULL CONSTRAINT DF_TKMCANDIDATE_DOMAINID           DEFAULT -1,
    PROPOSEDTITLE       NVARCHAR(500) NOT NULL,
    PROPOSEDTYPE        TINYINT       NOT NULL,  -- 0 Concept, 1 Fact, 2 Policy, 3 Rule, 4 Process, 5 Insight, 6 Document
    PROPOSEDCONTENT     NVARCHAR(MAX) NOT NULL,
    PROPOSEDTAGS        NVARCHAR(MAX),
    PROPOSEDSCOPELEVEL  TINYINT       NOT NULL CONSTRAINT DF_TKMCANDIDATE_PROPOSEDSCOPELEVEL DEFAULT 3,  -- 0 Global..5 Role
    CONFIDENCESCORE     NUMERIC(9,4)  NOT NULL CONSTRAINT DF_TKMCANDIDATE_CONFIDENCESCORE    DEFAULT 0,
    SOURCECHUNKINDEX    SMALLINT,
    SOURCECHUNKTEXT     NVARCHAR(MAX),
    LLMMODEL            NVARCHAR(100),
    REVIEWSTATUS        TINYINT       NOT NULL CONSTRAINT DF_TKMCANDIDATE_REVIEWSTATUS       DEFAULT 0,  -- 0 Pending, 1 Accepted, 2 Rejected, 3 Modified
    REVIEWEDUSERID      INT           NOT NULL CONSTRAINT DF_TKMCANDIDATE_REVIEWEDUSERID     DEFAULT -1,
    REVIEWEDON          DATETIME,
    REVIEWNOTES         NVARCHAR(1000),
    ARTIFACTID          INT           NOT NULL CONSTRAINT DF_TKMCANDIDATE_ARTIFACTID         DEFAULT -1,

    -- STANDARD FIELDS
    VERSION             SMALLINT  NOT NULL CONSTRAINT DF_TKMCANDIDATE_VERSION    DEFAULT 0,
    STATUS              TINYINT   NOT NULL CONSTRAINT DF_TKMCANDIDATE_STATUS      DEFAULT 1,
    SORTORDER           SMALLINT  NOT NULL CONSTRAINT DF_TKMCANDIDATE_SORTORDER   DEFAULT 9999,
    CREATEDBYID         INT       NOT NULL,
    CREATEDON           DATETIME  NOT NULL CONSTRAINT DF_TKMCANDIDATE_CREATEDON   DEFAULT GETDATE(),
    MODIFIEDBYID        INT       NOT NULL,
    MODIFIEDON          DATETIME  NOT NULL CONSTRAINT DF_TKMCANDIDATE_MODIFIEDON  DEFAULT GETDATE(),
    SOURCETYPE          TINYINT   NOT NULL CONSTRAINT DF_TKMCANDIDATE_SOURCETYPE  DEFAULT 5,
    TENANTID            INT       NOT NULL CONSTRAINT DF_TKMCANDIDATE_TENANTID    DEFAULT -1,

    CONSTRAINT PK_TKMCANDIDATE_CANDIDATEID              PRIMARY KEY (CANDIDATEID),
    CONSTRAINT FK_TKMCANDIDATE_JOBID                    FOREIGN KEY (JOBID)           REFERENCES TKMJOB(JOBID),
    CONSTRAINT FK_TKMCANDIDATE_DOMAINID                 FOREIGN KEY (DOMAINID)        REFERENCES MKMDOMAIN(DOMAINID),
    CONSTRAINT FK_TKMCANDIDATE_REVIEWEDUSERID           FOREIGN KEY (REVIEWEDUSERID)  REFERENCES MUSER(USERID),
    CONSTRAINT FK_TKMCANDIDATE_CREATEDBYID              FOREIGN KEY (CREATEDBYID)     REFERENCES MUSER(USERID),
    CONSTRAINT FK_TKMCANDIDATE_MODIFIEDBYID             FOREIGN KEY (MODIFIEDBYID)    REFERENCES MUSER(USERID),
    CONSTRAINT FK_TKMCANDIDATE_TENANTID                 FOREIGN KEY (TENANTID)        REFERENCES MCLIENT(CLIENTID),
    CONSTRAINT CK_TKMCANDIDATE_PROPOSEDTYPE             CHECK (PROPOSEDTYPE       IN (0,1,2,3,4,5,6)),
    CONSTRAINT CK_TKMCANDIDATE_PROPOSEDSCOPELEVEL       CHECK (PROPOSEDSCOPELEVEL IN (0,1,2,3,4,5)),
    CONSTRAINT CK_TKMCANDIDATE_REVIEWSTATUS             CHECK (REVIEWSTATUS       IN (0,1,2,3)),
    CONSTRAINT CK_TKMCANDIDATE_STATUS                   CHECK (STATUS             IN (0,1,2,3,4,5)),
    CONSTRAINT CK_TKMCANDIDATE_SOURCETYPE               CHECK (SOURCETYPE         IN (1,2,3,4,5))
)
GO

CREATE INDEX IX_TKMCANDIDATE_TENANTID_REVIEWSTATUS ON [DBO].[TKMCANDIDATE] (TENANTID, REVIEWSTATUS)
GO

-- ─────────────────────────────────────────────────────────────
--  TKMKNOWLEDGEGAP  — Unanswered Query Tracking
-- ─────────────────────────────────────────────────────────────
CREATE TABLE [DBO].[TKMKNOWLEDGEGAP]
(
    -- PK
    GAPID               INT           NOT NULL,

    -- BUSINESS COLUMNS
    QUERYTEXT           NVARCHAR(MAX) NOT NULL,
    QUERYHASH           NVARCHAR(64)  NOT NULL,
    BESTSCORERETURNED   NUMERIC(9,4)  NOT NULL CONSTRAINT DF_TKMKNOWLEDGEGAP_BESTSCORERETURNED DEFAULT 0,
    BESTARTIFACTID      INT           NOT NULL CONSTRAINT DF_TKMKNOWLEDGEGAP_BESTARTIFACTID    DEFAULT -1,
    CALLERJSON          NVARCHAR(MAX),
    GAPSTATUS           TINYINT       NOT NULL CONSTRAINT DF_TKMKNOWLEDGEGAP_GAPSTATUS         DEFAULT 0,  -- 0 Open, 1 InProgress, 2 Resolved, 3 WontFix
    OCCURRENCECOUNT     INT           NOT NULL CONSTRAINT DF_TKMKNOWLEDGEGAP_OCCURRENCECOUNT   DEFAULT 1,
    LASTQUERIEDON       DATETIME      NOT NULL CONSTRAINT DF_TKMKNOWLEDGEGAP_LASTQUERIEDON     DEFAULT GETDATE(),
    RESOLUTIONNOTES     NVARCHAR(1000),
    RESOLVEDBYID        INT           NOT NULL CONSTRAINT DF_TKMKNOWLEDGEGAP_RESOLVEDBYID      DEFAULT -1,
    RESOLVEDON          DATETIME,

    -- STANDARD FIELDS
    VERSION             SMALLINT  NOT NULL CONSTRAINT DF_TKMKNOWLEDGEGAP_VERSION    DEFAULT 0,
    STATUS              TINYINT   NOT NULL CONSTRAINT DF_TKMKNOWLEDGEGAP_STATUS      DEFAULT 1,
    SORTORDER           SMALLINT  NOT NULL CONSTRAINT DF_TKMKNOWLEDGEGAP_SORTORDER   DEFAULT 9999,
    CREATEDBYID         INT       NOT NULL,
    CREATEDON           DATETIME  NOT NULL CONSTRAINT DF_TKMKNOWLEDGEGAP_CREATEDON   DEFAULT GETDATE(),
    MODIFIEDBYID        INT       NOT NULL,
    MODIFIEDON          DATETIME  NOT NULL CONSTRAINT DF_TKMKNOWLEDGEGAP_MODIFIEDON  DEFAULT GETDATE(),
    SOURCETYPE          TINYINT   NOT NULL CONSTRAINT DF_TKMKNOWLEDGEGAP_SOURCETYPE  DEFAULT 5,
    TENANTID            INT       NOT NULL CONSTRAINT DF_TKMKNOWLEDGEGAP_TENANTID    DEFAULT -1,

    CONSTRAINT PK_TKMKNOWLEDGEGAP_GAPID                 PRIMARY KEY (GAPID),
    CONSTRAINT UK_TKMKNOWLEDGEGAP_QUERYHASH_TENANTID    UNIQUE (QUERYHASH, TENANTID),
    CONSTRAINT FK_TKMKNOWLEDGEGAP_RESOLVEDBYID          FOREIGN KEY (RESOLVEDBYID)  REFERENCES MUSER(USERID),
    CONSTRAINT FK_TKMKNOWLEDGEGAP_CREATEDBYID           FOREIGN KEY (CREATEDBYID)   REFERENCES MUSER(USERID),
    CONSTRAINT FK_TKMKNOWLEDGEGAP_MODIFIEDBYID          FOREIGN KEY (MODIFIEDBYID)  REFERENCES MUSER(USERID),
    CONSTRAINT FK_TKMKNOWLEDGEGAP_TENANTID              FOREIGN KEY (TENANTID)      REFERENCES MCLIENT(CLIENTID),
    CONSTRAINT CK_TKMKNOWLEDGEGAP_GAPSTATUS             CHECK (GAPSTATUS   IN (0,1,2,3)),
    CONSTRAINT CK_TKMKNOWLEDGEGAP_STATUS                CHECK (STATUS      IN (0,1,2,3,4,5)),
    CONSTRAINT CK_TKMKNOWLEDGEGAP_SOURCETYPE            CHECK (SOURCETYPE  IN (1,2,3,4,5))
)
GO

CREATE INDEX IX_TKMKNOWLEDGEGAP_TENANTID_GAPSTATUS ON [DBO].[TKMKNOWLEDGEGAP] (TENANTID, GAPSTATUS)
GO

-- ─────────────────────────────────────────────────────────────
--  LKMGOVERNANCE  — Governance Transition Log (append-only)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE [DBO].[LKMGOVERNANCE]
(
    LKMGOVERNANCEID     INT IDENTITY(1,1) NOT NULL,
    ARTIFACTID          INT           NOT NULL,
    FROMSTATE           TINYINT,
    TOSTATE             TINYINT       NOT NULL,   -- 0 Draft, 1 UnderReview, 2 Approved, 3 Published, 4 Retired
    TRANSITIONEDBYID    INT           NOT NULL,
    TRANSITIONEDON      DATETIME      NOT NULL CONSTRAINT DF_LKMGOVERNANCE_TRANSITIONEDON DEFAULT GETDATE(),
    NOTES               NVARCHAR(1000),
    REJECTIONREASON     NVARCHAR(1000),
    CREATEDBYID         INT           NOT NULL,
    CREATEDON           DATETIME      NOT NULL CONSTRAINT DF_LKMGOVERNANCE_CREATEDON      DEFAULT GETDATE(),

    CONSTRAINT PK_LKMGOVERNANCE_LKMGOVERNANCEID     PRIMARY KEY (LKMGOVERNANCEID),
    CONSTRAINT FK_LKMGOVERNANCE_ARTIFACTID          FOREIGN KEY (ARTIFACTID)        REFERENCES MKMARTIFACT(ARTIFACTID),
    CONSTRAINT FK_LKMGOVERNANCE_TRANSITIONEDBYID    FOREIGN KEY (TRANSITIONEDBYID)  REFERENCES MUSER(USERID),
    CONSTRAINT FK_LKMGOVERNANCE_CREATEDBYID         FOREIGN KEY (CREATEDBYID)       REFERENCES MUSER(USERID),
    CONSTRAINT CK_LKMGOVERNANCE_TOSTATE             CHECK (TOSTATE IN (0,1,2,3,4))
)
GO

CREATE INDEX IX_LKMGOVERNANCE_ARTIFACTID ON [DBO].[LKMGOVERNANCE] (ARTIFACTID)
GO

-- ─────────────────────────────────────────────────────────────
--  LKMAUDIT  — General Audit Log (append-only)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE [DBO].[LKMAUDIT]
(
    LKMAUDITID          INT IDENTITY(1,1) NOT NULL,
    ENTITYTYPE          NVARCHAR(50)  NOT NULL,
    ENTITYID            INT           NOT NULL,
    ACTION              NVARCHAR(50)  NOT NULL,
    BEFORESTATEJSON     NVARCHAR(MAX),
    AFTERSTATEJSON      NVARCHAR(MAX),
    METADATAJSON        NVARCHAR(MAX),
    CREATEDBYID         INT           NOT NULL,
    CREATEDON           DATETIME      NOT NULL CONSTRAINT DF_LKMAUDIT_CREATEDON DEFAULT GETDATE(),

    CONSTRAINT PK_LKMAUDIT_LKMAUDITID      PRIMARY KEY (LKMAUDITID),
    CONSTRAINT FK_LKMAUDIT_CREATEDBYID     FOREIGN KEY (CREATEDBYID) REFERENCES MUSER(USERID)
)
GO

CREATE INDEX IX_LKMAUDIT_ENTITYTYPE_ENTITYID ON [DBO].[LKMAUDIT] (ENTITYTYPE, ENTITYID)
GO
CREATE INDEX IX_LKMAUDIT_CREATEDON ON [DBO].[LKMAUDIT] (CREATEDON DESC)
GO

-- =============================================================================
--  AUTO-NUMBER SEED DATA
--  Insert seed records into the auto-number master so KMS entities can get IDs
-- =============================================================================
-- NOTE: Replace @StartValue with your system's current max or use the AutoNumber
--       stored procedure registration mechanism specific to your environment.
-- INSERT INTO MAUTONUMBER (ENTITYCODE, CURRENTVALUE, TENANTID, STATUS, ...)
-- VALUES ('KMSPACE', 1000, -1, 1, ...)
-- (Add similar entries for KMDOMAIN, KMARTIFACT, KMCONTENT, KMCONNECTOR, TKMJOB, TKMCANDIDATE, TKMGAP, KMRELATIONSHIP)
-- =============================================================================