/* =====================================================================
   CareFix control database — on-prem agent channel (Phase 3)
   Run on CS_CAREFIX after 01_control_db.sql.
   ===================================================================== */
USE CS_CAREFIX;
GO

CREATE TABLE dbo.CF_AGENT (
    HospitalId  INT PRIMARY KEY REFERENCES dbo.CF_HOSPITAL(HospitalId),
    KeyHash     VARBINARY(32) NOT NULL,          -- SHA-256 of the agent key; the key itself is never stored
    KeyIssuedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    KeyIssuedBy INT NULL,
    LastSeenAt  DATETIME2 NULL,
    Version     NVARCHAR(30) NULL,
    MachineName NVARCHAR(128) NULL,
    LastIp      NVARCHAR(64) NULL
);

CREATE TABLE dbo.CF_AGENT_JOB (
    JobId       BIGINT IDENTITY(1,1) PRIMARY KEY,
    HospitalId  INT NOT NULL REFERENCES dbo.CF_HOSPITAL(HospitalId),
    Kind        NVARCHAR(20) NOT NULL,
    PayloadJson NVARCHAR(MAX) NOT NULL,
    State       NVARCHAR(10) NOT NULL DEFAULT 'Pending' CHECK (State IN ('Pending','Taken','Done','Failed','Expired')),
    ResultJson  NVARCHAR(MAX) NULL,              -- cleared as soon as the server reads it
    ErrorStatus INT NULL,
    Error       NVARCHAR(2000) NULL,
    CreatedAt   DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    ExpiresAt   DATETIME2 NOT NULL,              -- the agent may not start a job after this
    TakenAt     DATETIME2 NULL,
    DoneAt      DATETIME2 NULL
);
CREATE INDEX IX_CF_AGENT_JOB_Pickup ON dbo.CF_AGENT_JOB(HospitalId, State, JobId) INCLUDE (ExpiresAt);
GO