/* =====================================================================
   CareFix demo hospital  —  CS_CAREFIX_DEMO
   A small, realistic HIS-shaped database with deliberate data faults, so the
   team can rehearse diagnosis, approval, execution, verification and rollback
   without touching a real hospital. No real patient data: names and numbers
   are generated.

   1. Run this script.
   2. Run 02_hospital_setup.sql with <HIS_DB> replaced by CS_CAREFIX_DEMO.
   3. Run the allow-list block at the end of THIS file.
   4. Add the hospital in CareFix (code DEMO-01, channel Direct), save the
      connection, test it and capture the schema.
   5. Import kb-templates/demo/*.csv under Knowledge base.
   6. Work through docs/DEMO.md.
   ===================================================================== */
IF DB_ID('CS_CAREFIX_DEMO') IS NULL
    CREATE DATABASE CS_CAREFIX_DEMO;
GO
USE CS_CAREFIX_DEMO;
GO

DROP TABLE IF EXISTS dbo.LAB_ORDER, dbo.OPD_VISIT, dbo.RECEIPT_MASTER, dbo.IPD_BILL_DETAIL,
                     dbo.IPD_BILL, dbo.IPD_PHARMACY_ISSUE, dbo.IPD_ADMISSION, dbo.ITEM_MASTER, dbo.PATIENT_MASTER;
GO

CREATE TABLE dbo.PATIENT_MASTER (
    UHID        VARCHAR(12)  NOT NULL PRIMARY KEY,
    PAT_NAME    NVARCHAR(100) NOT NULL,
    PAT_MOBILE  VARCHAR(15)  NULL,
    AADHAR_NO   VARCHAR(14)  NULL,
    ADDRESS     NVARCHAR(200) NULL,
    GENDER      CHAR(1)      NULL,
    DOB         DATE         NULL,
    REG_DATE    DATETIME     NOT NULL
);

CREATE TABLE dbo.ITEM_MASTER (
    ITEM_ID    INT NOT NULL PRIMARY KEY,
    ITEM_NAME  NVARCHAR(100) NOT NULL,
    ITEM_GROUP NVARCHAR(50)  NOT NULL,
    RATE       DECIMAL(12,2) NOT NULL
);

CREATE TABLE dbo.IPD_ADMISSION (
    IPD_NO         INT NOT NULL PRIMARY KEY,
    UHID           VARCHAR(12) NOT NULL,
    ADMIT_DATE     DATETIME NOT NULL,
    DISCHARGE_DATE DATETIME NULL,
    WARD           NVARCHAR(40) NOT NULL,
    BED_NO         NVARCHAR(10) NULL,
    STATUS         CHAR(1) NOT NULL,        -- A = admitted, D = discharged
    DISCHARGE_FINAL BIT NOT NULL DEFAULT 0  -- 1 = discharge summary finalised
);

CREATE TABLE dbo.IPD_PHARMACY_ISSUE (
    ISSUE_DTL_ID INT NOT NULL PRIMARY KEY,
    IPD_NO       INT NOT NULL,
    ITEM_ID      INT NOT NULL,
    QTY          DECIMAL(10,2) NOT NULL,
    RATE         DECIMAL(12,2) NOT NULL,
    AMOUNT       DECIMAL(12,2) NOT NULL,
    ISSUE_DATE   DATETIME NOT NULL,
    INDENT_NO    INT NULL,
    CANCEL_FLAG  CHAR(1) NOT NULL DEFAULT 'N'
);

CREATE TABLE dbo.IPD_BILL (
    BILL_ID      INT NOT NULL PRIMARY KEY,
    IPD_NO       INT NOT NULL,
    BILL_DATE    DATETIME NOT NULL,
    PHARMACY_AMT DECIMAL(12,2) NOT NULL DEFAULT 0,
    SERVICE_AMT  DECIMAL(12,2) NOT NULL DEFAULT 0,
    DISCOUNT_AMT DECIMAL(12,2) NOT NULL DEFAULT 0,
    NET_AMT      DECIMAL(12,2) NOT NULL DEFAULT 0,
    PAID_AMT     DECIMAL(12,2) NOT NULL DEFAULT 0,
    SETTLED_FLAG CHAR(1) NOT NULL DEFAULT 'N',
    TALLY_POSTED BIT NOT NULL DEFAULT 0
);

CREATE TABLE dbo.IPD_BILL_DETAIL (
    BILL_DTL_ID INT NOT NULL PRIMARY KEY,
    BILL_ID     INT NOT NULL,
    DESCRIPTION NVARCHAR(100) NOT NULL,
    QTY         DECIMAL(10,2) NOT NULL,
    RATE        DECIMAL(12,2) NOT NULL,
    AMOUNT      DECIMAL(12,2) NOT NULL,
    CANCEL_FLAG CHAR(1) NOT NULL DEFAULT 'N'
);

CREATE TABLE dbo.RECEIPT_MASTER (
    RECEIPT_ID   INT NOT NULL PRIMARY KEY,
    UHID         VARCHAR(12) NOT NULL,
    BILL_ID      INT NULL,
    RECEIPT_DATE DATETIME NOT NULL,
    AMOUNT       DECIMAL(12,2) NOT NULL,
    PAY_MODE     NVARCHAR(20) NOT NULL,
    CANCEL_FLAG  CHAR(1) NOT NULL DEFAULT 'N',
    TALLY_POSTED BIT NOT NULL DEFAULT 0
);

CREATE TABLE dbo.OPD_VISIT (
    VISIT_ID   INT NOT NULL PRIMARY KEY,
    UHID       VARCHAR(12) NOT NULL,
    VISIT_DATE DATETIME NOT NULL,
    DEPT       NVARCHAR(40) NOT NULL,
    DOCTOR     NVARCHAR(60) NOT NULL,
    STATUS     CHAR(1) NOT NULL          -- W = waiting, C = consulted, X = cancelled
);

CREATE TABLE dbo.LAB_ORDER (
    ORDER_ID       INT NOT NULL PRIMARY KEY,
    UHID           VARCHAR(12) NOT NULL,
    VISIT_ID       INT NULL,
    TEST_NAME      NVARCHAR(80) NOT NULL,
    ORDER_DATE     DATETIME NOT NULL,
    RESULT_ENTERED BIT NOT NULL DEFAULT 0,
    STATUS         CHAR(1) NOT NULL       -- P = pending, R = reported, X = cancelled
);
GO

/* ---------------- generated data ---------------- */
DECLARE @i INT = 1, @base DATE = DATEADD(DAY, -60, CAST(SYSDATETIME() AS DATE));

WHILE @i <= 60
BEGIN
    INSERT dbo.PATIENT_MASTER (UHID, PAT_NAME, PAT_MOBILE, AADHAR_NO, ADDRESS, GENDER, DOB, REG_DATE)
    VALUES ('240' + RIGHT('000' + CAST(500 + @i AS VARCHAR(10)), 3),
            CHOOSE(1 + @i % 10, N'Test Patient A', N'Test Patient B', N'Test Patient C', N'Test Patient D', N'Test Patient E',
                                 N'Test Patient F', N'Test Patient G', N'Test Patient H', N'Test Patient J', N'Test Patient K')
            + N' ' + CAST(@i AS NVARCHAR(4)),
            '9' + RIGHT('000000000' + CAST(100000000 + @i * 7919 AS VARCHAR(12)), 9),
            RIGHT('0000' + CAST(1000 + @i AS VARCHAR(6)), 4) + ' 5678 ' + RIGHT('0000' + CAST(2000 + @i AS VARCHAR(6)), 4),
            N'Flat ' + CAST(@i AS NVARCHAR(4)) + N', Demo Society, Nashik',
            CASE WHEN @i % 2 = 0 THEN 'M' ELSE 'F' END,
            DATEADD(YEAR, -20 - (@i % 50), @base),
            DATEADD(DAY, -(@i % 55), @base));
    SET @i += 1;
END;

INSERT dbo.ITEM_MASTER (ITEM_ID, ITEM_NAME, ITEM_GROUP, RATE) VALUES
 (101, N'PAN 40 TAB', N'Medicine', 18.60), (102, N'CROCIN 650 TAB', N'Medicine', 2.40),
 (103, N'MONOCEF 1GM INJ', N'Medicine', 96.00), (104, N'NS 500ML', N'IV Fluid', 42.00),
 (105, N'RL 500ML', N'IV Fluid', 44.50), (106, N'SYRINGE 5ML', N'Consumable', 6.50),
 (107, N'IV SET', N'Consumable', 28.00), (108, N'GLOVES PAIR', N'Consumable', 9.00),
 (109, N'AUGMENTIN 625 TAB', N'Medicine', 21.30), (110, N'EMESET 4MG INJ', N'Medicine', 17.75);

SET @i = 1;
WHILE @i <= 24
BEGIN
    INSERT dbo.IPD_ADMISSION (IPD_NO, UHID, ADMIT_DATE, DISCHARGE_DATE, WARD, BED_NO, STATUS, DISCHARGE_FINAL)
    VALUES (8850 + @i,
            '240' + RIGHT('000' + CAST(500 + @i AS VARCHAR(10)), 3),
            DATEADD(DAY, @i, @base),
            CASE WHEN @i <= 20 THEN DATEADD(DAY, @i + 3, @base) ELSE NULL END,
            CHOOSE(1 + @i % 4, N'General Ward', N'Semi Private', N'Private', N'ICU'),
            N'B' + CAST(100 + @i AS NVARCHAR(5)),
            CASE WHEN @i <= 20 THEN 'D' ELSE 'A' END,
            CASE WHEN @i <= 18 THEN 1 ELSE 0 END);
    SET @i += 1;
END;

/* pharmacy issues: 8 lines per admission */
DECLARE @a INT = 1, @line INT, @id INT = 118800, @item INT, @qty DECIMAL(10,2), @rate DECIMAL(12,2);
WHILE @a <= 24
BEGIN
    SET @line = 1;
    WHILE @line <= 8
    BEGIN
        SET @item = 101 + ((@a + @line) % 10);
        SELECT @rate = RATE FROM dbo.ITEM_MASTER WHERE ITEM_ID = @item;
        SET @qty = 1 + ((@a + @line) % 5);
        SET @id += 1;
        INSERT dbo.IPD_PHARMACY_ISSUE (ISSUE_DTL_ID, IPD_NO, ITEM_ID, QTY, RATE, AMOUNT, ISSUE_DATE, INDENT_NO, CANCEL_FLAG)
        VALUES (@id, 8850 + @a, @item, @qty, @rate, @qty * @rate, DATEADD(HOUR, 9 + @line, DATEADD(DAY, @a + 1, @base)), 55000 + @a * 10 + @line, 'N');
        SET @line += 1;
    END;
    SET @a += 1;
END;

/* bills, bill details and receipts */
SET @a = 1;
DECLARE @pharm DECIMAL(12,2), @svc DECIMAL(12,2), @net DECIMAL(12,2);
WHILE @a <= 24
BEGIN
    SELECT @pharm = ISNULL(SUM(AMOUNT), 0) FROM dbo.IPD_PHARMACY_ISSUE WHERE IPD_NO = 8850 + @a AND CANCEL_FLAG = 'N';
    SET @svc = 4500 + (@a % 7) * 1250;
    SET @net = @pharm + @svc;
    INSERT dbo.IPD_BILL (BILL_ID, IPD_NO, BILL_DATE, PHARMACY_AMT, SERVICE_AMT, DISCOUNT_AMT, NET_AMT, PAID_AMT, SETTLED_FLAG, TALLY_POSTED)
    VALUES (77400 + @a, 8850 + @a, DATEADD(DAY, @a + 3, @base), @pharm, @svc, 0, @net,
            CASE WHEN @a <= 18 THEN @net ELSE 0 END,
            CASE WHEN @a <= 18 THEN 'Y' ELSE 'N' END,
            CASE WHEN @a <= 15 THEN 1 ELSE 0 END);

    INSERT dbo.IPD_BILL_DETAIL (BILL_DTL_ID, BILL_ID, DESCRIPTION, QTY, RATE, AMOUNT, CANCEL_FLAG) VALUES
        (@a * 10 + 1, 77400 + @a, N'Room rent', 4, 1500, 6000, 'N'),
        (@a * 10 + 2, 77400 + @a, N'Doctor visit', 4, 600, 2400, 'N'),
        (@a * 10 + 3, 77400 + @a, N'Nursing charges', 4, 350, 1400, 'N');

    IF @a <= 18
        INSERT dbo.RECEIPT_MASTER (RECEIPT_ID, UHID, BILL_ID, RECEIPT_DATE, AMOUNT, PAY_MODE, CANCEL_FLAG, TALLY_POSTED)
        VALUES (5500 + @a, '240' + RIGHT('000' + CAST(500 + @a AS VARCHAR(10)), 3), 77400 + @a,
                DATEADD(DAY, @a + 3, @base), @net, CHOOSE(1 + @a % 3, N'Cash', N'Card', N'UPI'), 'N',
                CASE WHEN @a <= 15 THEN 1 ELSE 0 END);
    SET @a += 1;
END;

/* OPD visits and lab orders */
SET @i = 1;
WHILE @i <= 80
BEGIN
    INSERT dbo.OPD_VISIT (VISIT_ID, UHID, VISIT_DATE, DEPT, DOCTOR, STATUS)
    VALUES (91000 + @i, '240' + RIGHT('000' + CAST(500 + (@i % 60) + 1 AS VARCHAR(10)), 3),
            DATEADD(HOUR, 10 + (@i % 8), DATEADD(DAY, @i % 55, @base)),
            CHOOSE(1 + @i % 5, N'General Medicine', N'Orthopaedics', N'Paediatrics', N'Gynaecology', N'ENT'),
            CHOOSE(1 + @i % 4, N'Dr Demo Sharma', N'Dr Demo Iyer', N'Dr Demo Khan', N'Dr Demo Rao'),
            CASE WHEN @i % 11 = 0 THEN 'X' ELSE 'C' END);

    IF @i % 2 = 0
        INSERT dbo.LAB_ORDER (ORDER_ID, UHID, VISIT_ID, TEST_NAME, ORDER_DATE, RESULT_ENTERED, STATUS)
        VALUES (61000 + @i, '240' + RIGHT('000' + CAST(500 + (@i % 60) + 1 AS VARCHAR(10)), 3), 91000 + @i,
                CHOOSE(1 + @i % 5, N'CBC', N'LFT', N'KFT', N'HbA1c', N'Thyroid Profile'),
                DATEADD(HOUR, 11, DATEADD(DAY, @i % 55, @base)), 1, 'R');
    SET @i += 1;
END;
GO

/* =====================================================================
   Deliberate faults. Each matches a rehearsal case in docs/DEMO.md.
   ===================================================================== */

/* 1. Duplicate pharmacy issue on IPD 8871, bill inflated by the duplicate. */
INSERT dbo.IPD_PHARMACY_ISSUE (ISSUE_DTL_ID, IPD_NO, ITEM_ID, QTY, RATE, AMOUNT, ISSUE_DATE, INDENT_NO, CANCEL_FLAG)
SELECT 119999, IPD_NO, ITEM_ID, QTY, RATE, AMOUNT, ISSUE_DATE, INDENT_NO, 'N'
FROM dbo.IPD_PHARMACY_ISSUE WHERE ISSUE_DTL_ID = (SELECT MIN(ISSUE_DTL_ID) FROM dbo.IPD_PHARMACY_ISSUE WHERE IPD_NO = 8871);
UPDATE b SET PHARMACY_AMT = PHARMACY_AMT + d.AMOUNT, NET_AMT = NET_AMT + d.AMOUNT
FROM dbo.IPD_BILL b CROSS JOIN (SELECT AMOUNT FROM dbo.IPD_PHARMACY_ISSUE WHERE ISSUE_DTL_ID = 119999) d
WHERE b.IPD_NO = 8871;

/* 2. Receipt 5517 booked against the wrong UHID: it belongs to bill 77417 (UHID 240517),
      but was saved under UHID 240507. */
UPDATE dbo.RECEIPT_MASTER SET UHID = '240507' WHERE RECEIPT_ID = 5517;

/* 3. Discharge date typed with last year's year on IPD 8862. */
UPDATE dbo.IPD_ADMISSION SET DISCHARGE_DATE = DATEADD(YEAR, -1, DISCHARGE_DATE) WHERE IPD_NO = 8862;

/* 4. OPD visit still showing Waiting although the consultation happened. */
UPDATE dbo.OPD_VISIT SET STATUS = 'W' WHERE VISIT_ID = 91014;

/* 5. Lab order stuck at Pending although the result was entered. */
UPDATE dbo.LAB_ORDER SET STATUS = 'P' WHERE ORDER_ID = 61020;

/* 6. Discount keyed into the wrong field: bill 77405 shows a 500 discount that was never approved,
      and the bill is already settled and posted to Tally (high risk + lock rules). */
UPDATE dbo.IPD_BILL SET DISCOUNT_AMT = 500, NET_AMT = NET_AMT - 500 WHERE BILL_ID = 77405;
GO

PRINT 'Demo data ready. Faults seeded on IPD 8871, receipt 5517, IPD 8862, visit 91014, lab order 61020 and bill 77405.';
GO

/* =====================================================================
   Run 02_hospital_setup.sql against CS_CAREFIX_DEMO first, then this block.
   ===================================================================== */
IF OBJECT_ID('carefix.CF_ALLOWLIST') IS NOT NULL
BEGIN
    DELETE carefix.CF_ALLOWLIST;
    INSERT carefix.CF_ALLOWLIST (TableName, ColumnName) VALUES
        ('IPD_PHARMACY_ISSUE', 'CANCEL_FLAG'),
        ('IPD_BILL', 'PHARMACY_AMT'),
        ('IPD_BILL', 'DISCOUNT_AMT'),
        ('IPD_BILL', 'NET_AMT'),
        ('IPD_BILL_DETAIL', 'CANCEL_FLAG'),
        ('RECEIPT_MASTER', 'UHID'),
        ('RECEIPT_MASTER', 'CANCEL_FLAG'),
        ('IPD_ADMISSION', 'DISCHARGE_DATE'),
        ('OPD_VISIT', 'STATUS'),
        ('LAB_ORDER', 'STATUS');
    PRINT 'Allow-list loaded with 10 columns.';
END
ELSE
    PRINT 'carefix.CF_ALLOWLIST not found. Run 02_hospital_setup.sql against CS_CAREFIX_DEMO, then run this block again.';
GO