#!/usr/bin/env bash
# Linux-native installer for Fivetran SQL Server Binary Log Reader (backup log mode).
# Functional equivalent of BinLogInstall.ps1 for the backup-log-only installation path.
set -euo pipefail

SCRIPT_VERSION="1.0.0"
SQL_INSTANCE=""
SOURCE_DATABASE=""
SQL_ADMIN_USER=""
SQL_PASSWORD=""
MINIMAL_PERMISSIONS_USER=""
USE_DB_DATAREADER_ROLE=false
TRUST_SERVER_CERTIFICATE=false
MASTER_KEY_PASSWORD="${MASTER_KEY_PASSWORD:-}"

show_help() {
    cat <<'EOF'
Usage: ./BinLogInstall.sh --SqlInstance <instance> --SourceDatabase <database>
         [--SqlAdminUser <user> --SqlPassword <password>]
         [--MinimalPermissionsUser <normal_user>]
         [--UseDBDatareaderRole] [--TrustServerCertificate]
         [--Version] [--Help]

Parameters:
  --SqlInstance             (Required) SQL Server instance (e.g., localhost or localhost\SQLEXPRESS)
  --SourceDatabase          (Required) Database name to install the stored procedures
  --SqlAdminUser            (Optional) SQL Admin username. If not provided, uses Kerberos Authentication
  --SqlPassword             (Optional) SQL Admin password. Required if --SqlAdminUser is provided
  --MinimalPermissionsUser  (Optional) SQL Server LOGIN name for minimal permission grants.
                            NOTE: This login must already exist in SQL Server.
                            By default, the script creates [fivetran_datareader] if needed and adds
                            this user to it.
  --UseDBDatareaderRole     (Optional) Add the minimal-permissions user to [db_datareader] instead of
                            creating and using [fivetran_datareader]. With [db_datareader], no separate
                            SELECT grants on schemas or tables are needed.
  --TrustServerCertificate  (Optional) Trust the server certificate without verification. Use when
                            connecting to servers with self-signed certificates (typically with mssql-tools18 / ODBC Driver 18).
                            WARNING: This disables certificate verification and should only be used in trusted environments.
  --Version                 (Optional) Print the script version and exit
  --Help                    (Optional) Show this help message

Prerequisites:
  - sqlcmd must be installed (mssql-tools or mssql-tools18 package)
  - For Kerberos/Windows Authentication: run 'kinit <user>@<DOMAIN>' before invoking this script
  - For minimal permissions: MASTER_KEY_PASSWORD prompted interactively or set via environment variable

Environment Variables:
  MASTER_KEY_PASSWORD: (Optional) Password for database master key (required for minimal permissions with -MinimalPermissionsUser).
                      If not provided interactively, set via: export MASTER_KEY_PASSWORD="password"

Examples:
  # Kerberos auth with minimal permissions (interactive password prompt)
  ./BinLogInstall.sh --SqlInstance localhost --SourceDatabase MyDB --MinimalPermissionsUser reg_user

  # SQL auth with minimal permissions (environment variable password)
  export MASTER_KEY_PASSWORD="YourSecurePassword!"
  ./BinLogInstall.sh --SqlInstance localhost --SourceDatabase MyDB \
    --SqlAdminUser sa --SqlPassword "MyPassword" --MinimalPermissionsUser reg_user
EOF
}

log_info()  { echo "[INFO]  $*"; }
log_ok()    { echo "[OK]    $*"; }
log_warn()  { echo "[WARN]  $*" >&2; }
log_error() { echo "[ERROR] $*" >&2; }
show_version() { echo "BinLogInstall.sh version $SCRIPT_VERSION"; }

# ── Argument parsing ──────────────────────────────────────────────────────────

if [[ $# -eq 0 ]]; then
    show_version
    show_help
    exit 0
fi

while [[ $# -gt 0 ]]; do
    case "$1" in
        --SqlInstance)             SQL_INSTANCE="$2";             shift 2 ;;
        --SourceDatabase)          SOURCE_DATABASE="$2";          shift 2 ;;
        --SqlAdminUser)            SQL_ADMIN_USER="$2";           shift 2 ;;
        --SqlPassword)             SQL_PASSWORD="$2";             shift 2 ;;
        --MinimalPermissionsUser)  MINIMAL_PERMISSIONS_USER="$2"; shift 2 ;;
        --UseDBDatareaderRole)     USE_DB_DATAREADER_ROLE=true;   shift   ;;
        --TrustServerCertificate)  TRUST_SERVER_CERTIFICATE=true;  shift   ;;
        --Version|--version|-Version|-version) show_version; exit 0 ;;
        --Help|-h)                 show_help; exit 0              ;;
        *) log_error "Unknown parameter: $1"; echo; show_help; exit 1 ;;
    esac
done

show_version

# ── Validation ────────────────────────────────────────────────────────────────

missing_params=()
[[ -z "$SQL_INSTANCE" ]]              && missing_params+=("--SqlInstance")
[[ -z "$SOURCE_DATABASE" ]]           && missing_params+=("--SourceDatabase")


if [[ ${#missing_params[@]} -gt 0 ]]; then
    log_error "Missing required parameters: ${missing_params[*]}"
    echo
    show_help
    exit 1
fi

if [[ -n "$SQL_ADMIN_USER" && -z "$SQL_PASSWORD" ]]; then
    log_error "--SqlPassword is required when --SqlAdminUser is provided"
    exit 1
fi
if [[ -n "$SQL_PASSWORD" && -z "$SQL_ADMIN_USER" ]]; then
    log_error "--SqlAdminUser is required when --SqlPassword is provided"
    exit 1
fi

USE_SQL_AUTH=false
[[ -n "$SQL_ADMIN_USER" ]] && USE_SQL_AUTH=true

INSTALL_MINIMAL_PERMISSION=false
[[ -n "$MINIMAL_PERMISSIONS_USER" ]] && INSTALL_MINIMAL_PERMISSION=true

# ── Summary ───────────────────────────────────────────────────────────────────

log_info "Parameters validated."
log_info "Server Name:   $SQL_INSTANCE"
log_info "Database Name: $SOURCE_DATABASE"
if [[ "$USE_SQL_AUTH" == false ]]; then
    log_info "Authentication: Kerberos/Windows Authentication (Integrated Security)"
else
    log_info "Authentication: SQL Authentication"
    log_info "SQL Admin:      $SQL_ADMIN_USER"
fi
log_info "Enable Minimal Permission: $INSTALL_MINIMAL_PERMISSION"
log_info "Normal User:   ${MINIMAL_PERMISSIONS_USER:-<none>}"
if [[ "$USE_DB_DATAREADER_ROLE" == true ]]; then
    log_info "Reader Role: db_datareader"
else
    log_info "Reader Role: Fivetran scoped role"
fi
if [[ "$TRUST_SERVER_CERTIFICATE" == true ]]; then
    log_warn "Trust Server Certificate: ENABLED (certificate verification disabled)"
fi

# ── Prompt for master key password (if minimal permissions enabled and not provided) ──

if [[ "$INSTALL_MINIMAL_PERMISSION" == true ]]; then
    if [[ -z "$MASTER_KEY_PASSWORD" ]]; then
        if [[ -t 0 ]]; then
            # Interactive terminal: prompt for password
            echo
            log_info "A password is required for the database master key that encrypts the certificate used for bulk file operations."
            read -sp "Enter a password for the SQL Server database master key: " MASTER_KEY_PASSWORD
            echo
        else
            # Non-interactive (sandbox/automation): require env var
            log_error "Non-interactive installation requires MASTER_KEY_PASSWORD environment variable to be set (e.g., export MASTER_KEY_PASSWORD=...)"
            exit 1
        fi
    fi
    if [[ -z "$MASTER_KEY_PASSWORD" ]]; then
        log_error "Master key password cannot be empty"
        exit 1
    fi
    log_info "Master key password set for database master key encryption (length: ${#MASTER_KEY_PASSWORD} characters)"
fi

# ── Locate sqlcmd ─────────────────────────────────────────────────────────────

SQLCMD=""
for candidate in sqlcmd /opt/mssql-tools18/bin/sqlcmd /opt/mssql-tools/bin/sqlcmd; do
    if command -v "$candidate" &>/dev/null; then
        SQLCMD="$(command -v "$candidate" 2>/dev/null || echo "$candidate")"
        break
    fi
done

if [[ -z "$SQLCMD" ]]; then
    log_error "sqlcmd not found. Install mssql-tools or mssql-tools18 and ensure it is on PATH."
    exit 1
fi
log_info "Using sqlcmd: $SQLCMD"

# ── SQL execution helpers ─────────────────────────────────────────────────────

_build_sqlcmd_args() {
    local -n _out_args=$1  # nameref
    _out_args=(-S "$SQL_INSTANCE" -d "master" -b)
    if [[ "$USE_SQL_AUTH" == true ]]; then
        _out_args+=(-U "$SQL_ADMIN_USER")
    else
        _out_args+=(-E)
    fi
    if [[ "$TRUST_SERVER_CERTIFICATE" == true ]]; then
        if [[ "$SQLCMD" != *mssql-tools18* ]]; then
            log_error "--TrustServerCertificate requires mssql-tools18 sqlcmd (ODBC Driver 18). Install mssql-tools18 or ensure /opt/mssql-tools18/bin is first on PATH."
            exit 1
        fi
        _out_args+=(-C)
    fi
}

run_sql_script() {
    local script="$1"
    local name="${2:-SQL script}"

    local tmpfile
    tmpfile=$(mktemp /tmp/fivetran_sql_XXXXXX.sql)
    # shellcheck disable=SC2064
    trap "rm -f '$tmpfile'" RETURN

    printf '%s' "$script" > "$tmpfile"

    local sqlcmd_args
    _build_sqlcmd_args sqlcmd_args
    sqlcmd_args+=(-i "$tmpfile")

    if ! SQLCMDPASSWORD="$SQL_PASSWORD" "$SQLCMD" "${sqlcmd_args[@]}"; then
        log_error "CRITICAL ERROR during '$name'"
        exit 1
    fi
    log_ok "$name executed."
}

# ── Database access state check ───────────────────────────────────────────────

get_source_database_access_state() {
    local tmpfile
    tmpfile=$(mktemp /tmp/fivetran_sql_XXXXXX.sql)
    # shellcheck disable=SC2064
    trap "rm -f '$tmpfile'" RETURN

    local db_escaped="${SOURCE_DATABASE//\'/\'\'}"

    cat > "$tmpfile" <<ENDSQL
SET NOCOUNT ON;
SELECT CASE
    WHEN d.state_desc <> 'ONLINE' THEN 'Unavailable'
    WHEN SERVERPROPERTY('IsHadrEnabled') = 1
         AND d.replica_id IS NOT NULL
         AND ISNULL(sys.fn_hadr_is_primary_replica(N'${db_escaped}'), 0) = 0 THEN 'ReadOnlyAgSecondary'
    WHEN d.is_read_only = 0 THEN 'Writable'
    ELSE 'ReadOnly'
END
FROM sys.databases d
WHERE d.name = N'${db_escaped}';
ENDSQL

    local sqlcmd_args
    _build_sqlcmd_args sqlcmd_args
    sqlcmd_args+=(-h -1 -W -i "$tmpfile")

    local result
    result=$(SQLCMDPASSWORD="$SQL_PASSWORD" "$SQLCMD" "${sqlcmd_args[@]}" 2>&1) || {
        log_error "CRITICAL ERROR during source database access check: $result"
        exit 1
    }

    result=$(echo "$result" | grep -v '^Changed database' | tr -d '[:space:]')

    if [[ -z "$result" ]]; then
        log_error "Database [$SOURCE_DATABASE] was not found."
        exit 1
    fi

    echo "$result"
}

SOURCE_DB_ACCESS_STATE=$(get_source_database_access_state)
SOURCE_DB_WRITABLE=false

case "$SOURCE_DB_ACCESS_STATE" in
    Writable)
        SOURCE_DB_WRITABLE=true
        log_ok "Source database [$SOURCE_DATABASE] is writable."
        ;;
    ReadOnlyAgSecondary)
        log_warn "Database [$SOURCE_DATABASE] is read-only on an AOAG secondary. Skipping installation of Fivetran stored procedures for [$SOURCE_DATABASE]."
        ;;
    Unavailable)
        log_warn "Database [$SOURCE_DATABASE] is not ONLINE. This commonly happens when the AG primary is down or the database is still recovering. Skipping installation of Fivetran stored procedures for [$SOURCE_DATABASE]."
        ;;
    *)
        log_error "Database [$SOURCE_DATABASE] is read-only and is not identified as an AOAG secondary. Installation cannot continue."
        exit 1
        ;;
esac

# ── Minimal permissions setup ─────────────────────────────────────────────────

run_minimal_permission_script() {
    local user_name="$1"
    local db_name="$2"
    local use_db_datareader="$3"
    local reader_permission_sql

    if [[ "$use_db_datareader" == true ]]; then
        log_info "Adding [$user_name] to [db_datareader]"
        reader_permission_sql="ALTER ROLE db_datareader ADD MEMBER [${user_name}];"
    else
        log_info "Ensuring database role [fivetran_datareader] exists in [$db_name]"
        log_info "Adding [$user_name] to [fivetran_datareader]"
        log_info "Granting SELECT on [sys] and [cdc] schemas to [fivetran_datareader]"
        reader_permission_sql="
IF NOT EXISTS (
    SELECT 1
    FROM sys.database_principals
    WHERE name = N'fivetran_datareader' AND type = 'R'
)
BEGIN
    CREATE ROLE [fivetran_datareader];
END
ALTER ROLE [fivetran_datareader] ADD MEMBER [${user_name}];"
    fi

    # Escape user_name only for string literals in SQL
    local user_name_escaped="${user_name//\'/\'\'}"
    local db_name_escaped="${db_name//\'/\'\'}"

    local sql="
-- Validate the login exists at the server level before proceeding
USE [master];
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'${user_name_escaped}')
BEGIN
    RAISERROR('The SQL Server login ''${user_name_escaped}'' does not exist. Before running this script, create the login using: CREATE LOGIN [${user_name}] WITH PASSWORD = ''YourSecurePassword'';', 16, 1);
    RETURN;
END
"

    if [[ "$SOURCE_DB_WRITABLE" == true ]]; then
        sql+="
USE [${db_name}];

IF NOT EXISTS (
    SELECT 1
    FROM sys.database_principals
    WHERE name = N'${user_name_escaped}'
)
BEGIN
    CREATE USER [${user_name}] FOR LOGIN [${user_name}];
END
${reader_permission_sql}
"
    fi

    run_sql_script "$sql" "Create database user [${user_name}] if it doesn't exist."

    local sql2="
USE [master];
DECLARE @sql NVARCHAR(MAX);
IF CAST(SERVERPROPERTY('ProductMajorVersion') AS INT) >= 16
    SET @sql = 'GRANT VIEW SERVER PERFORMANCE STATE TO [${user_name}];';
ELSE
    SET @sql = 'GRANT VIEW SERVER STATE TO [${user_name}];';
EXEC sp_executesql @sql;

"

    if [[ "$SOURCE_DB_WRITABLE" == true ]]; then
        sql2+="
USE [${db_name}];
-- Enable CDC for database if not already enabled
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = N'${db_name_escaped}' AND is_cdc_enabled = 1)
BEGIN
    EXEC sp_cdc_enable_db;
END
USE [${db_name}];
GRANT VIEW DEFINITION ON DATABASE::[${db_name}] TO [${user_name}];
"
        if [[ "$use_db_datareader" == false ]]; then
            sql2+="
GRANT SELECT ON SCHEMA::[sys] TO [fivetran_datareader];
GRANT SELECT ON SCHEMA::[cdc] TO [fivetran_datareader];
"
        fi

        sql2+="
IF OBJECT_ID(N'sp_fivetran_cdc_enable_db', 'P') IS NOT NULL DROP PROCEDURE sp_fivetran_cdc_enable_db;
EXEC('CREATE PROCEDURE sp_fivetran_cdc_enable_db WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; EXEC sys.sp_cdc_enable_db; END');

IF OBJECT_ID(N'sp_fivetran_cdc_drop_job', 'P') IS NOT NULL DROP PROCEDURE sp_fivetran_cdc_drop_job;
EXEC('CREATE PROCEDURE sp_fivetran_cdc_drop_job WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; EXEC sp_cdc_drop_job @job_type = N''capture''; END');

IF OBJECT_ID(N'sp_fivetran_cdc_stop_job', 'P') IS NOT NULL DROP PROCEDURE sp_fivetran_cdc_stop_job;
EXEC('CREATE PROCEDURE sp_fivetran_cdc_stop_job WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; EXEC sp_cdc_stop_job @job_type = N''capture''; END');

IF OBJECT_ID(N'sp_fivetran_cdc_enable_table', 'P') IS NOT NULL DROP PROCEDURE sp_fivetran_cdc_enable_table;
EXEC('
CREATE PROCEDURE sp_fivetran_cdc_enable_table
    @source_schema NVARCHAR(500),
    @source_name   NVARCHAR(500),
    @capture_instance NVARCHAR(500) = NULL,
    @role_name        NVARCHAR(500) = NULL
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    IF @capture_instance IS NULL
        SET @capture_instance = N''fivetran_'' + CAST(OBJECT_ID(@source_schema + ''.'' + @source_name) AS NVARCHAR(500));
    EXEC sys.sp_cdc_enable_table
        @source_schema    = @source_schema,
        @source_name      = @source_name,
        @capture_instance = @capture_instance,
        @role_name        = @role_name;
END
');

IF OBJECT_ID(N'sp_fivetran_replflush', 'P') IS NOT NULL DROP PROCEDURE sp_fivetran_replflush;
EXEC('CREATE PROCEDURE sp_fivetran_replflush WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; EXEC sp_replflush; END');

IF OBJECT_ID(N'sp_fivetran_repldone', 'P') IS NOT NULL DROP PROCEDURE sp_fivetran_repldone;
EXEC('
CREATE PROCEDURE sp_fivetran_repldone
    @xactid     BINARY(10),
    @xact_seqno BINARY(10),
    @numtrans   INTEGER = NULL,
    @time       INTEGER = NULL,
    @reset      INTEGER = NULL
WITH EXECUTE AS SELF
AS
BEGIN
    DECLARE @stmt VARCHAR(200);
    SET @stmt = ''EXEC sp_repldone '' +
                ''@xactid= '' + COALESCE(CONVERT(VARCHAR, @xactid, 1), ''NULL'') +
                '', @xact_seqno= '' + COALESCE(CONVERT(VARCHAR, @xact_seqno, 1), ''NULL'');
    IF @numtrans IS NOT NULL SET @stmt += '', @numtrans= '' + CONVERT(VARCHAR, @numtrans);
    IF @time IS NOT NULL     SET @stmt += '', @time= '' + CONVERT(VARCHAR, @time);
    IF @reset IS NOT NULL    SET @stmt += '', @reset= '' + CONVERT(VARCHAR, @reset);
    EXEC(@stmt);
END
');

GRANT EXECUTE ON sp_fivetran_cdc_enable_db TO [${user_name}];
GRANT EXECUTE ON sp_fivetran_cdc_drop_job TO [${user_name}];
GRANT EXECUTE ON sp_fivetran_cdc_stop_job TO [${user_name}];
GRANT EXECUTE ON sp_fivetran_cdc_enable_table TO [${user_name}];
GRANT EXECUTE ON sp_fivetran_replflush TO [${user_name}];
GRANT EXECUTE ON sp_fivetran_repldone TO [${user_name}];
"
    fi

    run_sql_script "$sql2" "Grant Minimal Permissions to ${user_name}"
}

# ── BulkOpsCert setup (needed for sp_fivetran_read_backup signing) ───────────

install_bulk_ops_cert() {
    local master_key_pwd="$1"
    local master_key_pwd_escaped="${master_key_pwd//\'/\'\'}"
    local sql

    read -r -d '' sql << ENDSQL || true
USE [msdb];

-- Database master key is required before creating a certificate
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'${master_key_pwd_escaped}';

-- Create certificate in msdb (private key stays here for signing sp_fivetran_read_backup)
IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = N'BulkOpsCert')
    CREATE CERTIFICATE BulkOpsCert
        WITH SUBJECT = 'Fivetran Bulk Operations',
             EXPIRY_DATE = '99991231';

-- Export public key only (no DMK/CERTPRIVATEKEY needed) and install in master
-- to create a sysadmin cert login (required for OPENROWSET BULK on Linux)
DECLARE @certHex NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), CERTENCODED(CERT_ID(N'BulkOpsCert')), 1);
DECLARE @sql NVARCHAR(MAX) =
    N'USE [master]; ' +
    N'IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = N''BulkOpsCert'') ' +
    N'    CREATE CERTIFICATE BulkOpsCert FROM BINARY = ' + @certHex + N'; ' +
    N'IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N''##BulkOpsCertLogin##'') ' +
    N'BEGIN ' +
    N'    CREATE LOGIN [##BulkOpsCertLogin##] FROM CERTIFICATE BulkOpsCert; ' +
    N'    ALTER SERVER ROLE sysadmin ADD MEMBER [##BulkOpsCertLogin##]; ' +
    N'END';
EXEC (@sql);
ENDSQL
    run_sql_script "$sql" "Create BulkOpsCert for bulk operations"
}

# ── msdb stored procedures for BLR ───────────────────────────────────────────

install_blr_msdb_procedures() {
    local user_name="$1"
    # Escape user_name only for string literals in SQL
    local user_name_escaped="${user_name//\'/\'\'}"

    local sql3
    read -r -d '' sql3 << ENDSQL || true
USE [msdb];
GO

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'${user_name_escaped}')
BEGIN
    CREATE USER [${user_name}] FOR LOGIN [${user_name}];
END
GO

IF OBJECT_ID(N'dbo.sp_fivetran_xp_dirtree', 'P') IS NOT NULL
    DROP PROCEDURE dbo.sp_fivetran_xp_dirtree;
GO

CREATE PROCEDURE dbo.sp_fivetran_xp_dirtree
    @path NVARCHAR(4000),
    @depth INT = 1,
    @fileFlag INT = 1
WITH EXECUTE AS OWNER
AS
    SET NOCOUNT ON;
    EXEC master.dbo.xp_dirtree @path, @depth, @fileFlag;
GO

IF OBJECT_ID(N'dbo.sp_fivetran_xp_fileexist', 'P') IS NOT NULL
    DROP PROCEDURE dbo.sp_fivetran_xp_fileexist;
GO

CREATE PROCEDURE dbo.sp_fivetran_xp_fileexist
    @path NVARCHAR(4000)
WITH EXECUTE AS OWNER
AS
    SET NOCOUNT ON;
    EXEC master.dbo.xp_fileexist @path;
GO

IF OBJECT_ID(N'dbo.sp_fivetran_restore_info', 'P') IS NOT NULL
    DROP PROCEDURE dbo.sp_fivetran_restore_info;
GO

CREATE PROCEDURE dbo.sp_fivetran_restore_info
    @backupFile NVARCHAR(4000),
    @infoType NVARCHAR(20)
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX);
    DECLARE @backupFileEscaped NVARCHAR(4000) = REPLACE(@backupFile, '''', '''''');
    IF @infoType = N'HEADER'
        SET @sql = N'RESTORE HEADERONLY FROM DISK = N''' + @backupFileEscaped + N''';'
    ELSE IF @infoType = N'FILELIST'
        SET @sql = N'RESTORE FILELISTONLY FROM DISK = N''' + @backupFileEscaped + N''';'
    ELSE IF @infoType = N'LABEL'
        SET @sql = N'RESTORE LABELONLY FROM DISK = N''' + @backupFileEscaped + N''';'
    ELSE IF @infoType = N'VERIFY'
        SET @sql = N'RESTORE VERIFYONLY FROM DISK = N''' + @backupFileEscaped + N''';'
    ELSE
    BEGIN
        RAISERROR(N'Invalid InfoType', 16, 1);
        RETURN;
    END
    EXEC sp_executesql @sql;
END
GO

IF OBJECT_ID(N'dbo.sp_fivetran_dbcc_dbtable', 'P') IS NOT NULL
    DROP PROCEDURE dbo.sp_fivetran_dbcc_dbtable;
GO

CREATE PROCEDURE dbo.sp_fivetran_dbcc_dbtable
    @db_name NVARCHAR(500)
WITH EXECUTE AS OWNER
AS
    SET NOCOUNT ON;
    DBCC DBTABLE (@db_name) WITH TABLERESULTS;
GO

IF OBJECT_ID(N'dbo.sp_fivetran_dbcc_loginfo', 'P') IS NOT NULL
    DROP PROCEDURE dbo.sp_fivetran_dbcc_loginfo;
GO

CREATE PROCEDURE dbo.sp_fivetran_dbcc_loginfo
    @db_name NVARCHAR(500)
WITH EXECUTE AS OWNER
AS
    SET NOCOUNT ON;
    DBCC LOGINFO (@db_name) WITH TABLERESULTS;
GO

IF OBJECT_ID(N'dbo.sp_fivetran_read_backup', 'P') IS NOT NULL
    DROP PROCEDURE dbo.sp_fivetran_read_backup;
GO

CREATE PROCEDURE dbo.sp_fivetran_read_backup
    @path NVARCHAR(4000),
    @offset BIGINT = 0
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX);
    IF @offset = 0
        SET @sql = N'SELECT BulkCol FROM OPENROWSET(BULK N''' + REPLACE(@path, '''', '''''') + ''', SINGLE_BLOB) AS X(BulkCol)';
    ELSE
        SET @sql = N'SELECT SUBSTRING(BulkCol, ' + CAST(@offset + 1 AS NVARCHAR(20)) + N', DATALENGTH(BulkCol)) FROM OPENROWSET(BULK N''' + REPLACE(@path, '''', '''''') + ''', SINGLE_BLOB) AS X(BulkCol)';
    EXEC sp_executesql @sql;
END;
GO

ADD SIGNATURE TO [dbo].[sp_fivetran_read_backup] BY CERTIFICATE [BulkOpsCert];
GO

GRANT EXECUTE ON dbo.sp_fivetran_xp_dirtree TO [${user_name}];
GRANT EXECUTE ON dbo.sp_fivetran_xp_fileexist TO [${user_name}];
GRANT EXECUTE ON dbo.sp_fivetran_restore_info TO [${user_name}];
GRANT EXECUTE ON dbo.sp_fivetran_dbcc_dbtable TO [${user_name}];
GRANT EXECUTE ON dbo.sp_fivetran_dbcc_loginfo TO [${user_name}];
GRANT EXECUTE ON dbo.sp_fivetran_read_backup TO [${user_name}];
GO

PRINT N'[OK] BLR msdb procedures created successfully for user [${user_name}]';
GO
ENDSQL

    run_sql_script "$sql3" "Create BLR msdb procedures for ${user_name}"
}

if [[ "$INSTALL_MINIMAL_PERMISSION" == true ]]; then
    run_minimal_permission_script "$MINIMAL_PERMISSIONS_USER" "$SOURCE_DATABASE" "$USE_DB_DATAREADER_ROLE"
    install_bulk_ops_cert "$MASTER_KEY_PASSWORD"
    install_blr_msdb_procedures "$MINIMAL_PERMISSIONS_USER"
fi

log_ok "Installation complete."