using System.Data;
using System.Diagnostics;
using Dapper;
using Microsoft.Data.SqlClient;

namespace CareFix.Core;

/// <summary>
/// Every operation CareFix performs on a hospital database. Used by the server (direct channel)
/// and by the on-prem agent, so both enforce exactly the same limits.
/// Reads run as the read-only login; writes only through carefix.usp_CF_UpdateRow as the write login.
/// </summary>
public sealed class SqlOps(string readConnectionString, string writeConnectionString, SqlLimits limits)
{
    public static string BuildConnectionString(string server, int port, string database, string user, string password,
                                               bool encrypt, bool trustServerCert, string appName) =>
        new SqlConnectionStringBuilder
        {
            DataSource = $"{server},{port}",
            InitialCatalog = database,
            UserID = user,
            Password = password,
            Encrypt = encrypt ? SqlConnectionEncryptOption.Mandatory : SqlConnectionEncryptOption.Optional,
            TrustServerCertificate = trustServerCert,
            ApplicationName = appName,
            ConnectTimeout = 15,
        }.ConnectionString;

    private static async Task<SqlConnection> OpenAsync(string cs, CancellationToken ct)
    {
        var conn = new SqlConnection(cs);
        try { await conn.OpenAsync(ct); }
        catch (SqlException ex)
        {
            await conn.DisposeAsync();
            throw new OpsException(502, $"Could not connect to the hospital database: {ex.Message}");
        }
        return conn;
    }

    private async Task PrepareReadSessionAsync(SqlConnection conn, CancellationToken ct)
    {
        await using var pre = new SqlCommand($"SET LOCK_TIMEOUT {limits.LockTimeoutMs}; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;", conn);
        await pre.ExecuteNonQueryAsync(ct);
    }

    public async Task<QueryResult> RunSelectAsync(string sql, CancellationToken ct)
    {
        await using var conn = await OpenAsync(readConnectionString, ct);
        await PrepareReadSessionAsync(conn, ct);
        var sw = Stopwatch.StartNew();
        await using var cmd = new SqlCommand(sql, conn) { CommandTimeout = limits.SelectTimeoutSeconds };
        var rows = new List<object?[]>();
        var truncated = false;
        List<string> cols = [];
        try
        {
            await using var reader = await cmd.ExecuteReaderAsync(ct);
            cols = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();
            while (await reader.ReadAsync(ct))
            {
                if (rows.Count >= limits.MaxRowsPerSelect) { truncated = true; cmd.Cancel(); break; }
                var vals = new object?[reader.FieldCount];
                for (var i = 0; i < vals.Length; i++) vals[i] = Normalize(reader.IsDBNull(i) ? null : reader.GetValue(i));
                rows.Add(vals);
            }
        }
        catch (SqlException ex) when (!truncated)
        {
            throw new OpsException(400, ex.Number switch
            {
                -2 => $"The query timed out after {limits.SelectTimeoutSeconds} seconds. Filter on indexed identifiers (bill no, UHID, IPD no) or a narrow date range.",
                1222 => "The rows are locked by the hospital's live work right now. Try again in a minute.",
                _ => "SQL error: " + ex.Message,
            });
        }
        catch (SqlException) when (truncated)
        {
            // Expected: the command was cancelled on purpose after the row cap.
        }
        return new QueryResult(cols, rows, truncated, (int)sw.ElapsedMilliseconds);
    }

    public async Task<bool> AnyRowsAsync(string sql, string pkValue, CancellationToken ct)
    {
        await using var conn = await OpenAsync(readConnectionString, ct);
        await PrepareReadSessionAsync(conn, ct);
        await using var cmd = new SqlCommand(sql, conn) { CommandTimeout = limits.SelectTimeoutSeconds };
        cmd.Parameters.Add(new SqlParameter("@PkValue", SqlDbType.NVarChar, 200) { Value = pkValue });
        try
        {
            await using var reader = await cmd.ExecuteReaderAsync(ct);
            return await reader.ReadAsync(ct);
        }
        catch (SqlException ex)
        {
            throw new OpsException(400, "Lock check failed: " + ex.Message);
        }
    }

    private sealed record GetValueRow(int RowCount, string? Value, bool IsNull);

    public async Task<ValueRead> GetValueAsync(string table, string pkColumn, string pkValue, string column, CancellationToken ct)
    {
        await using var conn = await OpenAsync(readConnectionString, ct);
        try
        {
            var r = await conn.QuerySingleAsync<GetValueRow>(new CommandDefinition("carefix.usp_CF_GetValue",
                new { TableName = table, PkColumn = pkColumn, PkValue = pkValue, ColumnName = column },
                commandType: CommandType.StoredProcedure, commandTimeout: limits.SelectTimeoutSeconds, cancellationToken: ct));
            return new ValueRead(r.RowCount, r.IsNull ? null : r.Value);
        }
        catch (SqlException ex) when (ex.Number is >= 51000 and < 52000)
        {
            throw new OpsException(400, ex.Message);
        }
        catch (SqlException ex) when (ex.Number == 2812)
        {
            throw new OpsException(400, "CareFix procedures are not installed on this hospital database. Run sql/02_hospital_setup.sql.");
        }
    }

    public async Task<Dictionary<string, object?>?> GetRowAsync(string table, string pkColumn, string pkValue, CancellationToken ct)
    {
        await using var conn = await OpenAsync(readConnectionString, ct);
        await PrepareReadSessionAsync(conn, ct);
        var sql = $"SELECT TOP (2) * FROM dbo.{Q(table)} WHERE {Q(pkColumn)} = @pk";
        var rows = (await conn.QueryAsync(new CommandDefinition(sql, new { pk = pkValue }, commandTimeout: limits.SelectTimeoutSeconds, cancellationToken: ct))).ToList();
        if (rows.Count != 1) return null;
        var dict = (IDictionary<string, object?>)rows[0];
        return dict.ToDictionary(kv => kv.Key, kv => Normalize(kv.Value is DBNull ? null : kv.Value));
    }

    public async Task<int> ApplyStepsAsync(IReadOnlyList<StepChange> steps, string ticketRef, CancellationToken ct)
    {
        await using var conn = await OpenAsync(writeConnectionString, ct);
        await using var tx = (SqlTransaction)await conn.BeginTransactionAsync(IsolationLevel.ReadCommitted, ct);
        try
        {
            var total = 0;
            foreach (var st in steps)
            {
                var rc = await conn.ExecuteScalarAsync<int>(new CommandDefinition("carefix.usp_CF_UpdateRow",
                    new
                    {
                        TableName = st.Table, PkColumn = st.PkColumn, PkValue = st.PkValue, ColumnName = st.Column,
                        ExpectedOld = st.ExpectedOld, ExpectedOldIsNull = st.ExpectedOld is null,
                        NewValue = st.NewValue, NewValueIsNull = st.NewValue is null, TicketRef = ticketRef,
                    },
                    tx, 30, CommandType.StoredProcedure, cancellationToken: ct));
                if (rc != 1) throw new OpsException(409, $"{st.Table}.{st.Column} for {st.PkColumn}={st.PkValue} affected {rc} rows instead of 1.");
                total += rc;
            }
            await tx.CommitAsync(ct);
            return total;
        }
        catch (Exception ex)
        {
            try { await tx.RollbackAsync(CancellationToken.None); } catch { /* already rolled back by XACT_ABORT */ }
            if (ex is SqlException sql)
                throw new OpsException(409, (sql.Number is >= 51000 and < 52000 ? sql.Message : "SQL error: " + sql.Message) + " Nothing was changed.");
            if (ex is OpsException ops) throw new OpsException(409, ops.Message + " Nothing was changed.");
            throw;
        }
    }

    public async Task<IReadOnlyList<SchemaColumn>> ReadSchemaAsync(CancellationToken ct)
    {
        const string sql = """
            SELECT c.TABLE_SCHEMA AS SchemaName, c.TABLE_NAME AS TableName, c.COLUMN_NAME AS ColumnName, c.DATA_TYPE AS DataType,
                   CAST(CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END AS BIT) AS IsNullable,
                   CAST(CASE WHEN k.COLUMN_NAME IS NULL THEN 0 ELSE 1 END AS BIT) AS IsPk
            FROM INFORMATION_SCHEMA.COLUMNS c
            JOIN INFORMATION_SCHEMA.TABLES t ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
            LEFT JOIN (SELECT ku.TABLE_SCHEMA, ku.TABLE_NAME, ku.COLUMN_NAME
                       FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                       JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku ON ku.CONSTRAINT_NAME = tc.CONSTRAINT_NAME AND ku.TABLE_SCHEMA = tc.TABLE_SCHEMA
                       WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY') k
                   ON k.TABLE_SCHEMA = c.TABLE_SCHEMA AND k.TABLE_NAME = c.TABLE_NAME AND k.COLUMN_NAME = c.COLUMN_NAME
            WHERE c.TABLE_SCHEMA = 'dbo' AND t.TABLE_TYPE IN ('BASE TABLE', 'VIEW')
            """;
        await using var conn = await OpenAsync(readConnectionString, ct);
        return (await conn.QueryAsync<SchemaColumn>(new CommandDefinition(sql, commandTimeout: 120, cancellationToken: ct))).ToList();
    }

    public async Task<Dictionary<string, object?>> TestAsync(CancellationToken ct)
    {
        string? readDb, version; bool procsReadable, procsWritable;
        await using (var ro = await OpenAsync(readConnectionString, ct))
        {
            readDb = await ro.ExecuteScalarAsync<string>("SELECT DB_NAME()");
            version = await ro.ExecuteScalarAsync<string>("SELECT CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(50))");
            procsReadable = await ro.ExecuteScalarAsync<int?>("SELECT OBJECT_ID('carefix.usp_CF_GetValue')") is not null;
        }
        await using (var rw = await OpenAsync(writeConnectionString, ct))
        {
            procsWritable = await rw.ExecuteScalarAsync<int?>("SELECT OBJECT_ID('carefix.usp_CF_UpdateRow')") is not null;
        }
        return new Dictionary<string, object?>
        {
            ["database"] = readDb, ["sqlServerVersion"] = version, ["readLoginOk"] = true, ["writeLoginOk"] = true,
            ["getValueProcedure"] = procsReadable, ["updateProcedure"] = procsWritable, ["ready"] = procsReadable && procsWritable,
        };
    }

    public static string Q(string identifier) => "[" + identifier.Replace("]", "]]") + "]";

    private static object? Normalize(object? v) => v switch
    {
        null => null,
        DateTime dt => dt.ToString("yyyy-MM-ddTHH:mm:ss.fff"),
        DateTimeOffset dto => dto.ToString("yyyy-MM-ddTHH:mm:ss.fffzzz"),
        TimeSpan ts => ts.ToString(),
        byte[] b => $"[binary {b.Length} bytes]",
        Guid g => g.ToString(),
        _ => v,
    };
}