/* =====================================================================
   CareFix control database: learn-from-tickets + helpdesk integration
   Run on CS_CAREFIX after 03_agent_channel.sql.
   ===================================================================== */
USE CS_CAREFIX;
GO

CREATE TABLE dbo.CF_MINE_BATCH (
    BatchId     INT IDENTITY(1,1) PRIMARY KEY,
    FileName    NVARCHAR(260) NOT NULL,
    TicketCount INT NOT NULL,
    State       NVARCHAR(12) NOT NULL DEFAULT 'Queued' CHECK (State IN ('Queued','Processing','Done','Failed')),
    Progress    NVARCHAR(200) NULL,
    Error       NVARCHAR(2000) NULL,
    CreatedBy   INT NOT NULL,
    CreatedAt   DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    FinishedAt  DATETIME2 NULL
);

CREATE TABLE dbo.CF_MINE_TICKET (
    BatchId    INT NOT NULL REFERENCES dbo.CF_MINE_BATCH(BatchId),
    RowNo      INT NOT NULL,
    Ref        NVARCHAR(100) NOT NULL,
    Module     NVARCHAR(50) NULL,
    IssueText  NVARCHAR(MAX) NOT NULL,     -- masked on import
    Resolution NVARCHAR(MAX) NULL,
    SqlUsed    NVARCHAR(MAX) NULL,
    PRIMARY KEY (BatchId, RowNo)
);

CREATE TABLE dbo.CF_PLAYBOOK_DRAFT (
    DraftId      INT IDENTITY(1,1) PRIMARY KEY,
    BatchId      INT NOT NULL REFERENCES dbo.CF_MINE_BATCH(BatchId),
    Title        NVARCHAR(200) NOT NULL,
    IssueType    NVARCHAR(100) NULL,
    Module       NVARCHAR(50) NULL,
    Keywords     NVARCHAR(400) NULL,
    Description  NVARCHAR(2000) NULL,
    DiagnosisSql NVARCHAR(MAX) NULL,
    FixGuidance  NVARCHAR(MAX) NULL,
    Risk         NVARCHAR(10) NOT NULL DEFAULT 'Medium',
    TicketCount  INT NOT NULL,
    SampleRefs   NVARCHAR(2000) NULL,
    State        NVARCHAR(10) NOT NULL DEFAULT 'Draft' CHECK (State IN ('Draft','Accepted','Discarded')),
    ReviewedBy   INT NULL,
    ReviewedAt   DATETIME2 NULL,
    PlaybookId   INT NULL
);

/* Outbound status updates to the helpdesk. Durable, retried with back-off. */
CREATE TABLE dbo.CF_WEBHOOK_OUTBOX (
    OutboxId      BIGINT IDENTITY(1,1) PRIMARY KEY,
    TicketId      INT NOT NULL,
    PayloadJson   NVARCHAR(MAX) NOT NULL,
    State         NVARCHAR(10) NOT NULL DEFAULT 'Pending' CHECK (State IN ('Pending','Sent','Failed')),
    Attempts      INT NOT NULL DEFAULT 0,
    NextAttemptAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    LastError     NVARCHAR(1000) NULL,
    CreatedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    SentAt        DATETIME2 NULL
);
CREATE INDEX IX_CF_WEBHOOK_OUTBOX_Due ON dbo.CF_WEBHOOK_OUTBOX(State, NextAttemptAt);

CREATE UNIQUE INDEX UX_CF_TICKET_ExternalRef ON dbo.CF_TICKET(HospitalId, ExternalRef) WHERE ExternalRef IS NOT NULL;
GO