:setvar ShadowSourceDatabase "FT546-wcs"
:setvar ShadowEvidenceLogin "ft546_shadow_evidence"
:setvar ShadowEvidenceUser "ft546_shadow_evidence"
:setvar ShadowEvidencePassword "REPLACE_AT_DEPLOY_TIME"

SET NOCOUNT ON;
SET XACT_ABORT ON;

IF DB_ID() = DB_ID(N'$(ShadowSourceDatabase)')
    THROW 51001, 'Evidence objects must be installed in a separate database.', 1;

IF N'$(ShadowEvidencePassword)' = N'REPLACE_AT_DEPLOY_TIME'
    THROW 51002, 'Supply ShadowEvidencePassword through the approved secret channel.', 1;

DECLARE @evidenceLoginCreated bit = 0;
IF NOT EXISTS (SELECT 1 FROM sys.server_principals
               WHERE name = N'$(ShadowEvidenceLogin)')
BEGIN
    DECLARE @createEvidenceLogin nvarchar(max) =
        N'CREATE LOGIN ' + QUOTENAME(N'$(ShadowEvidenceLogin)')
        + N' WITH PASSWORD = N'''
        + REPLACE(N'$(ShadowEvidencePassword)', N'''', N'''''')
        + N''', CHECK_POLICY = ON, CHECK_EXPIRATION = ON';
    EXEC sys.sp_executesql @createEvidenceLogin;
    SET @evidenceLoginCreated = 1;
END;

BEGIN TRY
IF EXISTS (
    SELECT 1
    FROM sys.server_role_members rm
    JOIN sys.server_principals login_principal
      ON login_principal.principal_id = rm.member_principal_id
    WHERE login_principal.name = N'$(ShadowEvidenceLogin)'
)
    THROW 51003, 'The shadow evidence login must not belong to a server role.', 1;

DECLARE @serverContextSet bit = 0;
BEGIN TRY
    EXECUTE AS LOGIN = N'$(ShadowEvidenceLogin)';
    SET @serverContextSet = 1;
    IF EXISTS (
        SELECT 1 FROM sys.fn_my_permissions(NULL, N'SERVER')
        WHERE permission_name NOT IN (N'CONNECT SQL', N'VIEW ANY DATABASE')
    )
        THROW 51009, 'The shadow evidence login retains forbidden server permissions.', 1;
    REVERT;
    SET @serverContextSet = 0;
END TRY
BEGIN CATCH
    IF @serverContextSet = 1
        REVERT;
    THROW;
END CATCH;

BEGIN TRANSACTION;

IF NOT EXISTS (SELECT 1 FROM sys.database_principals
               WHERE name = N'$(ShadowEvidenceUser)')
BEGIN
    DECLARE @createEvidenceUser nvarchar(max) =
        N'CREATE USER ' + QUOTENAME(N'$(ShadowEvidenceUser)')
        + N' FOR LOGIN ' + QUOTENAME(N'$(ShadowEvidenceLogin)');
    EXEC sys.sp_executesql @createEvidenceUser;
END
ELSE IF SUSER_SID(N'$(ShadowEvidenceLogin)')
        <> USER_SID(N'$(ShadowEvidenceUser)')
    THROW 51004, 'The existing shadow evidence user maps to a different login.', 1;

DECLARE @evidencePrincipal nvarchar(258) =
    QUOTENAME(N'$(ShadowEvidenceUser)');
DECLARE @evidenceRole sysname;
DECLARE evidence_roles CURSOR LOCAL FAST_FORWARD FOR
SELECT role_principal.name
FROM sys.database_principals role_principal
JOIN sys.database_role_members rm
  ON rm.role_principal_id = role_principal.principal_id
JOIN sys.database_principals member_principal
  ON member_principal.principal_id = rm.member_principal_id
WHERE role_principal.type = 'R'
  AND member_principal.name = N'$(ShadowEvidenceUser)';

OPEN evidence_roles;
FETCH NEXT FROM evidence_roles INTO @evidenceRole;
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @dropEvidenceRole nvarchar(max) = N'ALTER ROLE '
        + QUOTENAME(@evidenceRole) + N' DROP MEMBER ' + @evidencePrincipal;
    EXEC sys.sp_executesql @dropEvidenceRole;
    FETCH NEXT FROM evidence_roles INTO @evidenceRole;
END;
CLOSE evidence_roles;
DEALLOCATE evidence_roles;

DECLARE @revokeEvidenceDatabase nvarchar(max) =
    N'REVOKE CONTROL, ALTER, TAKE OWNERSHIP, SELECT, INSERT, UPDATE, DELETE, '
    + N'EXECUTE FROM ' + @evidencePrincipal + N';';
EXEC sys.sp_executesql @revokeEvidenceDatabase;

IF OBJECT_ID(N'dbo.shadow_run', N'U') IS NULL
CREATE TABLE dbo.shadow_run (
    run_id uniqueidentifier NOT NULL PRIMARY KEY,
    started_at datetime2(3) NOT NULL,
    artifact_sha256 char(64) NOT NULL,
    configuration_sha256 char(64) NOT NULL,
    source_server nvarchar(256) NOT NULL,
    source_database sysname NOT NULL,
    source_login sysname NOT NULL,
    status varchar(32) NOT NULL
);

IF OBJECT_ID(N'dbo.shadow_cycle', N'U') IS NULL
CREATE TABLE dbo.shadow_cycle (
    cycle_id uniqueidentifier NOT NULL PRIMARY KEY,
    run_id uniqueidentifier NOT NULL,
    input_batch_hash char(64) NOT NULL,
    parent_task_id nvarchar(128) NOT NULL,
    phase varchar(64) NOT NULL,
    route_version bigint NOT NULL,
    status varchar(64) NOT NULL,
    reason_code nvarchar(512) NULL,
    database_captured_at datetime2(3) NULL,
    completed_at datetime2(3) NOT NULL,
    evidence_hash char(64) NOT NULL,
    CONSTRAINT fk_shadow_cycle_run FOREIGN KEY (run_id)
        REFERENCES dbo.shadow_run(run_id),
    CONSTRAINT uq_shadow_cycle_identity UNIQUE
        (run_id, input_batch_hash, parent_task_id, phase, route_version)
);

IF OBJECT_ID(N'dbo.shadow_input_batch', N'U') IS NULL
CREATE TABLE dbo.shadow_input_batch (
    cycle_id uniqueidentifier NOT NULL PRIMARY KEY,
    content_hash char(64) NOT NULL,
    captured_json nvarchar(max) NOT NULL,
    CONSTRAINT fk_shadow_input_cycle FOREIGN KEY (cycle_id)
        REFERENCES dbo.shadow_cycle(cycle_id)
);

IF OBJECT_ID(N'dbo.shadow_legacy_queue', N'U') IS NULL
CREATE TABLE dbo.shadow_legacy_queue (
    cycle_id uniqueidentifier NOT NULL PRIMARY KEY,
    vehicle_id nvarchar(64) NULL,
    path_info nvarchar(max) NULL,
    result_status varchar(64) NOT NULL,
    result_json nvarchar(max) NOT NULL,
    content_hash char(64) NOT NULL,
    CONSTRAINT fk_shadow_legacy_cycle FOREIGN KEY (cycle_id)
        REFERENCES dbo.shadow_cycle(cycle_id)
);

IF OBJECT_ID(N'dbo.shadow_decision', N'U') IS NULL
CREATE TABLE dbo.shadow_decision (
    cycle_id uniqueidentifier NOT NULL PRIMARY KEY,
    decision varchar(64) NOT NULL,
    vehicle_id nvarchar(64) NULL,
    reason_code nvarchar(512) NULL,
    decision_json nvarchar(max) NOT NULL,
    content_hash char(64) NOT NULL,
    CONSTRAINT fk_shadow_decision_cycle FOREIGN KEY (cycle_id)
        REFERENCES dbo.shadow_cycle(cycle_id)
);

IF OBJECT_ID(N'dbo.shadow_encoded_write', N'U') IS NULL
CREATE TABLE dbo.shadow_encoded_write (
    cycle_id uniqueidentifier NOT NULL,
    write_sequence int NOT NULL,
    register_address varchar(16) NOT NULL,
    data_type varchar(16) NOT NULL,
    values_json nvarchar(max) NOT NULL,
    trigger_value bigint NULL,
    CONSTRAINT pk_shadow_encoded_write PRIMARY KEY
        (cycle_id, write_sequence),
    CONSTRAINT fk_shadow_write_cycle FOREIGN KEY (cycle_id)
        REFERENCES dbo.shadow_cycle(cycle_id)
);

IF OBJECT_ID(N'dbo.shadow_difference', N'U') IS NULL
CREATE TABLE dbo.shadow_difference (
    difference_id bigint IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    cycle_id uniqueidentifier NOT NULL,
    category varchar(64) NOT NULL,
    reason_code nvarchar(512) NOT NULL,
    expected_json nvarchar(max) NULL,
    actual_json nvarchar(max) NULL,
    CONSTRAINT fk_shadow_difference_cycle FOREIGN KEY (cycle_id)
        REFERENCES dbo.shadow_cycle(cycle_id)
);

IF OBJECT_ID(N'dbo.shadow_traffic_state', N'U') IS NULL
CREATE TABLE dbo.shadow_traffic_state (
    state_key nvarchar(256) NOT NULL PRIMARY KEY,
    snapshot_version bigint NOT NULL,
    state_json nvarchar(max) NOT NULL,
    updated_at datetime2(3) NOT NULL
);

IF OBJECT_ID(N'dbo.shadow_hold_history', N'U') IS NULL
CREATE TABLE dbo.shadow_hold_history (
    parent_task_id nvarchar(128) NOT NULL,
    vehicle_id nvarchar(64) NOT NULL,
    resource_key nvarchar(256) NOT NULL,
    reason_code nvarchar(512) NOT NULL,
    consecutive_count int NOT NULL,
    updated_at datetime2(3) NOT NULL,
    CONSTRAINT pk_shadow_hold_history PRIMARY KEY
        (parent_task_id, vehicle_id, resource_key, reason_code)
);

IF OBJECT_ID(N'dbo.shadow_startup_check', N'U') IS NULL
CREATE TABLE dbo.shadow_startup_check (
    check_id bigint IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    check_name varchar(128) NOT NULL,
    status varchar(32) NOT NULL,
    checked_at datetime2(3) NOT NULL
);

DECLARE @grantEvidence nvarchar(max) = N'GRANT CONNECT TO '
    + @evidencePrincipal + N';';
DECLARE @evidenceTable sysname;
DECLARE evidence_tables CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM sys.tables
WHERE schema_id = SCHEMA_ID(N'dbo')
  AND name IN (N'shadow_run', N'shadow_cycle', N'shadow_input_batch',
               N'shadow_legacy_queue', N'shadow_decision',
               N'shadow_encoded_write', N'shadow_difference',
               N'shadow_traffic_state', N'shadow_hold_history',
               N'shadow_startup_check');
OPEN evidence_tables;
FETCH NEXT FROM evidence_tables INTO @evidenceTable;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @grantEvidence += N'GRANT SELECT, INSERT, UPDATE, DELETE ON OBJECT::dbo.'
        + QUOTENAME(@evidenceTable) + N' TO ' + @evidencePrincipal + N';';
    FETCH NEXT FROM evidence_tables INTO @evidenceTable;
END;
CLOSE evidence_tables;
DEALLOCATE evidence_tables;
EXEC sys.sp_executesql @grantEvidence;

DECLARE @databaseContextSet bit = 0;
BEGIN TRY
    EXECUTE AS USER = N'$(ShadowEvidenceUser)';
    SET @databaseContextSet = 1;

    IF EXISTS (
        SELECT 1
        FROM sys.database_role_members rm
        JOIN sys.database_principals member_principal
          ON member_principal.principal_id = rm.member_principal_id
        WHERE member_principal.name = USER_NAME()
    )
        THROW 51005, 'The shadow evidence login retains a database role.', 1;

    IF EXISTS (
        SELECT 1 FROM sys.fn_my_permissions(NULL, N'DATABASE')
        WHERE permission_name <> N'CONNECT'
    )
        THROW 51006, 'The shadow evidence login retains forbidden database permissions.', 1;

    IF EXISTS (
        SELECT 1
        FROM sys.schemas s
        CROSS APPLY sys.fn_my_permissions(QUOTENAME(s.name), N'SCHEMA') p
        WHERE s.name NOT IN (N'sys', N'INFORMATION_SCHEMA')
    )
        THROW 51007, 'The shadow evidence login retains forbidden schema permissions.', 1;

    IF EXISTS (
        SELECT 1
        FROM sys.objects o
        JOIN sys.schemas s ON s.schema_id = o.schema_id
        CROSS APPLY sys.fn_my_permissions(
            QUOTENAME(s.name) + N'.' + QUOTENAME(o.name), N'OBJECT') p
        WHERE o.is_ms_shipped = 0
          AND NOT (
              s.name = N'dbo'
              AND o.name IN (
                  N'shadow_run', N'shadow_cycle', N'shadow_input_batch',
                  N'shadow_legacy_queue', N'shadow_decision',
                  N'shadow_encoded_write', N'shadow_difference',
                  N'shadow_traffic_state', N'shadow_hold_history',
                  N'shadow_startup_check')
              AND p.permission_name IN (
                  N'SELECT', N'INSERT', N'UPDATE', N'DELETE')
          )
    )
        THROW 51008, 'The shadow evidence login retains forbidden object permissions.', 1;

    REVERT;
    SET @databaseContextSet = 0;
END TRY
BEGIN CATCH
    IF @databaseContextSet = 1
        REVERT;
    THROW;
END CATCH;

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;
    IF @evidenceLoginCreated = 1
    BEGIN
        DECLARE @dropEvidenceLogin nvarchar(max) =
            N'DROP LOGIN ' + QUOTENAME(N'$(ShadowEvidenceLogin)');
        EXEC sys.sp_executesql @dropEvidenceLogin;
    END;
    THROW;
END CATCH;