/* =====================================================================
   CareFix hospital-side setup  (run by the DBA on EACH hospital HIS DB)
   SQL Server 2016 SP1+ (on older versions change CREATE OR ALTER to DROP + CREATE)
   - carefix_ro : read-only login used for diagnosis
   - carefix_rw : can ONLY execute CareFix guarded procedures
   - carefix.CF_ALLOWLIST : columns CareFix is allowed to change
   - carefix.CF_CHANGE_LOG : local log of every change CareFix makes
   Replace <HIS_DB>, and set strong passwords before running.
   ===================================================================== */
USE master;
GO
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'carefix_ro')
    CREATE LOGIN carefix_ro WITH PASSWORD = 'CHANGE-ME-Ro#2026!', CHECK_POLICY = ON, DEFAULT_DATABASE = [<HIS_DB>];
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'carefix_rw')
    CREATE LOGIN carefix_rw WITH PASSWORD = 'CHANGE-ME-Rw#2026!', CHECK_POLICY = ON, DEFAULT_DATABASE = [<HIS_DB>];
GO

USE [<HIS_DB>];
GO
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'carefix_ro') CREATE USER carefix_ro FOR LOGIN carefix_ro;
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'carefix_rw') CREATE USER carefix_rw FOR LOGIN carefix_rw;
GO
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'carefix') EXEC('CREATE SCHEMA carefix AUTHORIZATION dbo');
GO

/* Read-only access for diagnosis. To hide a sensitive table add: DENY SELECT ON dbo.<table> TO carefix_ro; */
GRANT SELECT ON SCHEMA::dbo TO carefix_ro;
GO

IF OBJECT_ID('carefix.CF_ALLOWLIST') IS NULL
CREATE TABLE carefix.CF_ALLOWLIST (
    TableName   SYSNAME NOT NULL,
    ColumnName  SYSNAME NOT NULL,
    AllowUpdate BIT NOT NULL DEFAULT 1,
    AddedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    PRIMARY KEY (TableName, ColumnName)
);
IF OBJECT_ID('carefix.CF_CHANGE_LOG') IS NULL
CREATE TABLE carefix.CF_CHANGE_LOG (
    ChangeId   BIGINT IDENTITY(1,1) PRIMARY KEY,
    TicketRef  NVARCHAR(30)   NOT NULL,
    TableName  SYSNAME        NOT NULL,
    PkColumn   SYSNAME        NOT NULL,
    PkValue    NVARCHAR(200)  NOT NULL,
    ColumnName SYSNAME        NOT NULL,
    OldValue   NVARCHAR(4000) NULL,
    NewValue   NVARCHAR(4000) NULL,
    ChangedBy  NVARCHAR(128)  NOT NULL,
    ChangedAt  DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

/* Canonical text form of a column, identical for reading and for the "value unchanged" check */
CREATE OR ALTER FUNCTION carefix.fn_CF_CanonExpr (@ObjectId INT, @ColumnName SYSNAME)
RETURNS NVARCHAR(400)
AS
BEGIN
    DECLARE @type SYSNAME = (SELECT t.name FROM sys.columns c JOIN sys.types t ON t.user_type_id = c.user_type_id
                             WHERE c.object_id = @ObjectId AND c.name = @ColumnName);
    DECLARE @q NVARCHAR(260) = QUOTENAME(@ColumnName);
    RETURN CASE
        WHEN @type IN ('date','datetime','datetime2','smalldatetime','datetimeoffset','time')
             THEN N'CONVERT(NVARCHAR(4000),' + @q + N',126)'
        WHEN @type IN ('float','real')
             THEN N'CONVERT(NVARCHAR(4000),CAST(' + @q + N' AS DECIMAL(38,6)))'
        WHEN @type IN ('money','smallmoney')
             THEN N'CONVERT(NVARCHAR(4000),' + @q + N',2)'
        ELSE N'CONVERT(NVARCHAR(4000),' + @q + N')'
    END;
END;
GO

CREATE OR ALTER PROCEDURE carefix.usp_CF_GetValue
    @TableName SYSNAME, @PkColumn SYSNAME, @PkValue NVARCHAR(200), @ColumnName SYSNAME
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @obj INT = OBJECT_ID(N'dbo.' + QUOTENAME(@TableName), N'U');
    IF @obj IS NULL THROW 51002, 'CareFix: table not found in dbo.', 1;
    IF COL_LENGTH(N'dbo.' + QUOTENAME(@TableName), @PkColumn) IS NULL OR COL_LENGTH(N'dbo.' + QUOTENAME(@TableName), @ColumnName) IS NULL
        THROW 51003, 'CareFix: column not found.', 1;

    DECLARE @canon NVARCHAR(400) = carefix.fn_CF_CanonExpr(@obj, @ColumnName);
    DECLARE @sql NVARCHAR(MAX) =
        N'SELECT @cnt = COUNT(*) FROM dbo.' + QUOTENAME(@TableName) + N' WHERE ' + QUOTENAME(@PkColumn) + N' = @pk;' +
        N'IF @cnt = 1 SELECT @val = ' + @canon + N', @isnull = CASE WHEN ' + QUOTENAME(@ColumnName) + N' IS NULL THEN 1 ELSE 0 END ' +
        N'FROM dbo.' + QUOTENAME(@TableName) + N' WHERE ' + QUOTENAME(@PkColumn) + N' = @pk;';
    DECLARE @cnt INT, @val NVARCHAR(4000), @isnull BIT = 0;
    EXEC sp_executesql @sql, N'@pk NVARCHAR(200), @cnt INT OUTPUT, @val NVARCHAR(4000) OUTPUT, @isnull BIT OUTPUT',
         @pk = @PkValue, @cnt = @cnt OUTPUT, @val = @val OUTPUT, @isnull = @isnull OUTPUT;
    SELECT [RowCount] = @cnt, [Value] = CASE WHEN @isnull = 1 THEN NULL ELSE @val END, [IsNull] = @isnull;
END;
GO

CREATE OR ALTER PROCEDURE carefix.usp_CF_UpdateRow
    @TableName SYSNAME, @PkColumn SYSNAME, @PkValue NVARCHAR(200), @ColumnName SYSNAME,
    @ExpectedOld NVARCHAR(4000), @ExpectedOldIsNull BIT,
    @NewValue NVARCHAR(4000), @NewValueIsNull BIT,
    @TicketRef NVARCHAR(30)
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON; SET XACT_ABORT ON;

    IF NOT EXISTS (SELECT 1 FROM carefix.CF_ALLOWLIST WHERE TableName = @TableName AND ColumnName = @ColumnName AND AllowUpdate = 1)
        THROW 51001, 'CareFix: this column is not in the hospital allow-list (carefix.CF_ALLOWLIST).', 1;

    DECLARE @obj INT = OBJECT_ID(N'dbo.' + QUOTENAME(@TableName), N'U');
    IF @obj IS NULL THROW 51002, 'CareFix: table not found in dbo.', 1;
    IF COL_LENGTH(N'dbo.' + QUOTENAME(@TableName), @PkColumn) IS NULL OR COL_LENGTH(N'dbo.' + QUOTENAME(@TableName), @ColumnName) IS NULL
        THROW 51003, 'CareFix: column not found.', 1;

    DECLARE @cnt INT;
    DECLARE @countSql NVARCHAR(MAX) = N'SELECT @cnt = COUNT(*) FROM dbo.' + QUOTENAME(@TableName) + N' WHERE ' + QUOTENAME(@PkColumn) + N' = @pk;';
    EXEC sp_executesql @countSql, N'@pk NVARCHAR(200), @cnt INT OUTPUT', @pk = @PkValue, @cnt = @cnt OUTPUT;
    IF @cnt <> 1 THROW 51004, 'CareFix: key does not identify exactly one row.', 1;

    DECLARE @canon NVARCHAR(400) = carefix.fn_CF_CanonExpr(@obj, @ColumnName);
    DECLARE @rc INT;
    DECLARE @sql NVARCHAR(MAX) =
        N'UPDATE dbo.' + QUOTENAME(@TableName) +
        N' SET ' + QUOTENAME(@ColumnName) + N' = CASE WHEN @nvIsNull = 1 THEN NULL ELSE @nv END' +
        N' WHERE ' + QUOTENAME(@PkColumn) + N' = @pk' +
        N' AND ((@eoIsNull = 1 AND ' + QUOTENAME(@ColumnName) + N' IS NULL) OR (@eoIsNull = 0 AND ' + @canon + N' = @eo));' +
        N' SET @rc = @@ROWCOUNT;';
    EXEC sp_executesql @sql,
         N'@pk NVARCHAR(200), @nv NVARCHAR(4000), @nvIsNull BIT, @eo NVARCHAR(4000), @eoIsNull BIT, @rc INT OUTPUT',
         @pk = @PkValue, @nv = @NewValue, @nvIsNull = @NewValueIsNull, @eo = @ExpectedOld, @eoIsNull = @ExpectedOldIsNull, @rc = @rc OUTPUT;

    IF @rc <> 1 THROW 51005, 'CareFix: the current value no longer matches the approved "before" value. Nothing was changed.', 1;

    INSERT carefix.CF_CHANGE_LOG (TicketRef, TableName, PkColumn, PkValue, ColumnName, OldValue, NewValue, ChangedBy)
    VALUES (@TicketRef, @TableName, @PkColumn, @PkValue, @ColumnName,
            CASE WHEN @ExpectedOldIsNull = 1 THEN NULL ELSE @ExpectedOld END,
            CASE WHEN @NewValueIsNull = 1 THEN NULL ELSE @NewValue END,
            ORIGINAL_LOGIN());

    SELECT @rc;
END;
GO

GRANT EXECUTE ON carefix.usp_CF_GetValue  TO carefix_ro;
GRANT EXECUTE ON carefix.usp_CF_GetValue  TO carefix_rw;
GRANT EXECUTE ON carefix.usp_CF_UpdateRow TO carefix_rw;
GO

/* ---- Allow-list: add ONLY columns support is permitted to correct. Examples: ----
INSERT carefix.CF_ALLOWLIST (TableName, ColumnName) VALUES
 ('PATIENT_MASTER','PAT_MOBILE'),
 ('OPD_BILL_DETAILS','CANCEL_FLAG'),
 ('IPD_PHARMACY_ISSUE','CANCEL_FLAG');
*/