/* =====================================================================
   CareFix control database  (run once on the CareFix server)
   SQL Server 2017+  |  Creates all tables in database CS_CAREFIX
   ===================================================================== */
-- CREATE DATABASE CS_CAREFIX;
-- GO
USE CS_CAREFIX;
GO

CREATE TABLE dbo.CF_USER (
    UserId        INT IDENTITY(1,1) PRIMARY KEY,
    Username      NVARCHAR(100) NOT NULL UNIQUE,
    FullName      NVARCHAR(150) NOT NULL,
    PasswordHash  NVARCHAR(200) NOT NULL,
    TotpSecret    NVARCHAR(100) NULL,
    TotpPending   NVARCHAR(100) NULL,
    Role          NVARCHAR(20)  NOT NULL CHECK (Role IN ('Engineer','Lead','ProductOwner','Head','Admin')),
    IsActive      BIT NOT NULL DEFAULT 1,
    CreatedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE dbo.CF_HOSPITAL (
    HospitalId  INT IDENTITY(1,1) PRIMARY KEY,
    Code        NVARCHAR(30)  NOT NULL UNIQUE,
    Name        NVARCHAR(200) NOT NULL,
    City        NVARCHAR(100) NULL,
    HisVersion  NVARCHAR(50)  NULL,
    Channel     NVARCHAR(10)  NOT NULL DEFAULT 'Direct' CHECK (Channel IN ('Direct','Agent')),
    Status      NVARCHAR(20)  NOT NULL DEFAULT 'Active' CHECK (Status IN ('Active','Paused','Inactive')),
    Notes       NVARCHAR(MAX) NULL,
    SchemaCapturedAt DATETIME2 NULL,
    CreatedAt   DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE dbo.CF_CONNECTION (
    HospitalId      INT PRIMARY KEY REFERENCES dbo.CF_HOSPITAL(HospitalId),
    Server          NVARCHAR(200) NOT NULL,
    Port            INT NOT NULL DEFAULT 1433,
    DbName          NVARCHAR(128) NOT NULL,
    RoUser          NVARCHAR(128) NOT NULL,
    RoPasswordEnc   VARBINARY(MAX) NOT NULL,
    RwUser          NVARCHAR(128) NOT NULL,
    RwPasswordEnc   VARBINARY(MAX) NOT NULL,
    Encrypt         BIT NOT NULL DEFAULT 1,
    TrustServerCert BIT NOT NULL DEFAULT 0,
    UpdatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedBy       INT NULL
);

CREATE TABLE dbo.CF_USER_HOSPITAL (
    UserId     INT NOT NULL REFERENCES dbo.CF_USER(UserId),
    HospitalId INT NOT NULL REFERENCES dbo.CF_HOSPITAL(HospitalId),
    PRIMARY KEY (UserId, HospitalId)
);

CREATE TABLE dbo.CF_TICKET (
    TicketId    INT IDENTITY(1,1) PRIMARY KEY,
    TicketNo    AS ('CF-' + RIGHT('000000' + CAST(TicketId AS VARCHAR(10)), 6)) PERSISTED,
    HospitalId  INT NOT NULL REFERENCES dbo.CF_HOSPITAL(HospitalId),
    RaisedBy    INT NOT NULL REFERENCES dbo.CF_USER(UserId),
    Title       NVARCHAR(200) NOT NULL,
    IssueText   NVARCHAR(MAX) NOT NULL,
    State       NVARCHAR(20) NOT NULL DEFAULT 'New'
                CHECK (State IN ('New','Diagnosing','NeedsInfo','FixProposed','Approved','Executed','Verified','RolledBack','Closed')),
    Risk        NVARCHAR(10) NULL,
    AiBusy      BIT NOT NULL DEFAULT 0,
    ExternalRef NVARCHAR(100) NULL,
    CloseNote   NVARCHAR(1000) NULL,
    CreatedAt   DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt   DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    ClosedAt    DATETIME2 NULL
);
CREATE INDEX IX_CF_TICKET_State ON dbo.CF_TICKET(State, HospitalId);

CREATE TABLE dbo.CF_TICKET_MESSAGE (
    MessageId  BIGINT IDENTITY(1,1) PRIMARY KEY,
    TicketId   INT NOT NULL REFERENCES dbo.CF_TICKET(TicketId),
    Sender     NVARCHAR(20) NOT NULL CHECK (Sender IN ('Engineer','AI','System')),
    UserId     INT NULL,
    Body       NVARCHAR(MAX) NOT NULL,
    CreatedAt  DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_CF_TICKET_MESSAGE_Ticket ON dbo.CF_TICKET_MESSAGE(TicketId, MessageId);

CREATE TABLE dbo.CF_AI_TRANSCRIPT (
    TicketId   INT PRIMARY KEY REFERENCES dbo.CF_TICKET(TicketId),
    StateJson  NVARCHAR(MAX) NOT NULL,
    UpdatedAt  DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE dbo.CF_QUERY_LOG (
    QueryId      BIGINT IDENTITY(1,1) PRIMARY KEY,
    TicketId     INT NOT NULL REFERENCES dbo.CF_TICKET(TicketId),
    HospitalId   INT NOT NULL,
    SqlText      NVARCHAR(MAX) NOT NULL,
    Purpose      NVARCHAR(400) NULL,
    RowsReturned INT NULL,
    Truncated    BIT NOT NULL DEFAULT 0,
    DurationMs   INT NULL,
    Blocked      BIT NOT NULL DEFAULT 0,
    BlockReason  NVARCHAR(400) NULL,
    ResultJson   NVARCHAR(MAX) NULL,      -- masked result only
    CreatedAt    DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_CF_QUERY_LOG_Ticket ON dbo.CF_QUERY_LOG(TicketId, QueryId);

CREATE TABLE dbo.CF_FIX (
    FixId              INT IDENTITY(1,1) PRIMARY KEY,
    TicketId           INT NOT NULL REFERENCES dbo.CF_TICKET(TicketId),
    Summary            NVARCHAR(1000) NOT NULL,
    Evidence           NVARCHAR(MAX) NULL,
    Risk               NVARCHAR(10) NOT NULL CHECK (Risk IN ('Low','Medium','High')),
    RiskReasons        NVARCHAR(2000) NULL,
    RowsExpected       INT NOT NULL,
    State              NVARCHAR(20) NOT NULL DEFAULT 'Proposed'
                       CHECK (State IN ('Proposed','Approved','Rejected','Executed','Failed','RolledBack','Superseded')),
    LockOverrideNeeded BIT NOT NULL DEFAULT 0,
    ConsentFileName    NVARCHAR(260) NULL,
    ConsentFile        VARBINARY(MAX) NULL,
    ConsentBy          INT NULL,
    ProposedAt         DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_CF_FIX_Ticket ON dbo.CF_FIX(TicketId);
CREATE INDEX IX_CF_FIX_State ON dbo.CF_FIX(State);

CREATE TABLE dbo.CF_FIX_STEP (
    StepId     INT IDENTITY(1,1) PRIMARY KEY,
    FixId      INT NOT NULL REFERENCES dbo.CF_FIX(FixId),
    StepNo     INT NOT NULL,
    TableName  NVARCHAR(128) NOT NULL,
    PkColumn   NVARCHAR(128) NOT NULL,
    PkValue    NVARCHAR(200) NOT NULL,
    ColumnName NVARCHAR(128) NOT NULL,
    OldValue   NVARCHAR(4000) NULL,
    NewValue   NVARCHAR(4000) NULL,
    Reason     NVARCHAR(500) NULL
);
CREATE INDEX IX_CF_FIX_STEP_Fix ON dbo.CF_FIX_STEP(FixId, StepNo);

CREATE TABLE dbo.CF_APPROVAL (
    ApprovalId   INT IDENTITY(1,1) PRIMARY KEY,
    FixId        INT NOT NULL REFERENCES dbo.CF_FIX(FixId),
    ApproverId   INT NOT NULL REFERENCES dbo.CF_USER(UserId),
    ApproverRole NVARCHAR(20) NOT NULL,
    Decision     NVARCHAR(10) NOT NULL CHECK (Decision IN ('Approved','Rejected')),
    Reason       NVARCHAR(1000) NULL,
    DecidedAt    DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE dbo.CF_EXECUTION (
    ExecutionId      INT IDENTITY(1,1) PRIMARY KEY,
    FixId            INT NOT NULL REFERENCES dbo.CF_FIX(FixId),
    ExecutedBy       INT NOT NULL,
    ExecutedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    Status           NVARCHAR(20) NOT NULL CHECK (Status IN ('Running','Success','Failed')),
    RowsAffected     INT NULL,
    Error            NVARCHAR(MAX) NULL,
    VerifiedAt       DATETIME2 NULL,
    VerifiedResolved BIT NULL,
    VerifyNote       NVARCHAR(2000) NULL,
    RolledBackAt     DATETIME2 NULL,
    RolledBackBy     INT NULL,
    RollbackReason   NVARCHAR(1000) NULL
);

CREATE TABLE dbo.CF_ROW_SNAPSHOT (
    SnapshotId  BIGINT IDENTITY(1,1) PRIMARY KEY,
    ExecutionId INT NOT NULL REFERENCES dbo.CF_EXECUTION(ExecutionId),
    TableName   NVARCHAR(128) NOT NULL,
    PkColumn    NVARCHAR(128) NOT NULL,
    PkValue     NVARCHAR(200) NOT NULL,
    RowJson     NVARCHAR(MAX) NOT NULL,
    CapturedAt  DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

/* ---------- Knowledge base ---------- */
CREATE TABLE dbo.CF_KB_TABLE (
    TableName NVARCHAR(128) PRIMARY KEY,
    Module    NVARCHAR(50)  NULL,
    Meaning   NVARCHAR(1000) NULL,
    UpdatedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE dbo.CF_KB_COLUMN (
    TableName  NVARCHAR(128) NOT NULL,
    ColumnName NVARCHAR(128) NOT NULL,
    Meaning    NVARCHAR(1000) NULL,
    ValueCodes NVARCHAR(2000) NULL,     -- e.g. "C=Cancelled, A=Active"
    IsPii      BIT NOT NULL DEFAULT 0,
    Editable   BIT NOT NULL DEFAULT 1,
    RiskLevel  NVARCHAR(10) NOT NULL DEFAULT 'Medium' CHECK (RiskLevel IN ('Low','Medium','High')),
    UpdatedAt  DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    PRIMARY KEY (TableName, ColumnName)
);

CREATE TABLE dbo.CF_KB_RELATION (
    RelationId  INT IDENTITY(1,1) PRIMARY KEY,
    ParentTable NVARCHAR(128) NOT NULL,
    ChildTable  NVARCHAR(128) NOT NULL,
    JoinKeys    NVARCHAR(400) NOT NULL,     -- e.g. "BILLNO=BILLNO"
    CascadeNote NVARCHAR(1000) NULL
);

CREATE TABLE dbo.CF_KB_RULE (
    RuleId       INT IDENTITY(1,1) PRIMARY KEY,
    Module       NVARCHAR(50) NULL,
    RuleText     NVARCHAR(2000) NOT NULL,
    LockTable    NVARCHAR(128) NULL,        -- table this lock applies to
    LockCheckSql NVARCHAR(MAX) NULL,        -- SELECT that returns a row when locked; uses @PkValue
    LockMessage  NVARCHAR(500) NULL
);

CREATE TABLE dbo.CF_PLAYBOOK (
    PlaybookId   INT IDENTITY(1,1) PRIMARY KEY,
    Title        NVARCHAR(200) NOT NULL,
    IssueType    NVARCHAR(100) NULL,
    Module       NVARCHAR(50)  NULL,
    Keywords     NVARCHAR(400) NULL,        -- comma separated
    Description  NVARCHAR(2000) NULL,
    DiagnosisSql NVARCHAR(MAX) NULL,
    FixGuidance  NVARCHAR(MAX) NULL,
    Risk         NVARCHAR(10) NOT NULL DEFAULT 'Medium',
    IsActive     BIT NOT NULL DEFAULT 1,
    SuccessCount INT NOT NULL DEFAULT 0,
    SourceFixId  INT NULL,
    CreatedBy    INT NULL,
    CreatedAt    DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE dbo.CF_HOSPITAL_SCHEMA (
    HospitalId INT NOT NULL,
    SchemaName NVARCHAR(128) NOT NULL,
    TableName  NVARCHAR(128) NOT NULL,
    ColumnName NVARCHAR(128) NOT NULL,
    DataType   NVARCHAR(128) NOT NULL,
    IsNullable BIT NOT NULL,
    IsPk       BIT NOT NULL,
    CapturedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    PRIMARY KEY (HospitalId, SchemaName, TableName, ColumnName)
);

/* ---------- Audit (append-only) ---------- */
CREATE TABLE dbo.CF_AUDIT (
    EventId  BIGINT IDENTITY(1,1) PRIMARY KEY,
    At       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    UserId   INT NULL,
    TicketId INT NULL,
    Action   NVARCHAR(60) NOT NULL,
    Detail   NVARCHAR(MAX) NULL
);
GO
CREATE TRIGGER dbo.TR_CF_AUDIT_NoChange ON dbo.CF_AUDIT
INSTEAD OF UPDATE, DELETE
AS
BEGIN
    THROW 50001, 'CF_AUDIT is append-only.', 1;
END;
GO