using System.Data;
using CareFix.Api.Infrastructure;
using CareFix.Api.Knowledge;
using CareFix.Api.Security;
using CareFix.Core;
using Dapper;
using Microsoft.Data.SqlClient;

namespace CareFix.Api.Hospitals;

public sealed record HospitalInput(string Code, string Name, string? City, string? HisVersion, string Channel, string Status, string? Notes);
public sealed record ConnectionInput(string Server, int Port, string DbName, string RoUser, string? RoPassword, string RwUser, string? RwPassword, bool Encrypt, bool TrustServerCert);

public sealed class HospitalAdminService(ControlDb db, CredentialVault vault, IHospitalExecutor exec, KnowledgeBase kb, AuditService audit)
{
    public async Task<IEnumerable<dynamic>> ListAsync(int userId, bool all, CancellationToken ct)
    {
        await using var c = await db.OpenAsync(ct);
        return await c.QueryAsync("""
            SELECT h.HospitalId, h.Code, h.Name, h.City, h.HisVersion, h.Channel, h.Status, h.Notes, h.SchemaCapturedAt,
                   CAST(CASE WHEN cn.HospitalId IS NULL THEN 0 ELSE 1 END AS BIT) AS HasConnection,
                   cn.Server, cn.Port, cn.DbName, cn.RoUser, cn.RwUser, cn.Encrypt, cn.TrustServerCert,
                   CAST(CASE WHEN ag.KeyHash IS NULL THEN 0 ELSE 1 END AS BIT) AS HasAgentKey,
                   ag.LastSeenAt AS AgentLastSeenAt, ag.Version AS AgentVersion, ag.MachineName AS AgentMachine
            FROM CF_HOSPITAL h
            LEFT JOIN CF_CONNECTION cn ON cn.HospitalId = h.HospitalId
            LEFT JOIN CF_AGENT ag ON ag.HospitalId = h.HospitalId
            WHERE @all = 1 OR h.HospitalId IN (SELECT HospitalId FROM CF_USER_HOSPITAL WHERE UserId = @userId)
            ORDER BY h.Name
            """, new { all, userId });
    }

    public async Task<int> SaveAsync(int userId, int? hospitalId, HospitalInput i, CancellationToken ct)
    {
        if (string.IsNullOrWhiteSpace(i.Code) || string.IsNullOrWhiteSpace(i.Name)) throw AppException.BadRequest("Code and name are required.");
        if (i.Channel is not ("Direct" or "Agent")) throw AppException.BadRequest("Channel must be Direct or Agent.");
        await using var c = await db.OpenAsync(ct);
        try
        {
            int id;
            if (hospitalId is null)
                id = await c.ExecuteScalarAsync<int>("""
                    INSERT CF_HOSPITAL (Code, Name, City, HisVersion, Channel, Status, Notes)
                    OUTPUT INSERTED.HospitalId VALUES (@Code, @Name, @City, @HisVersion, @Channel, @Status, @Notes)
                    """, i);
            else
            {
                id = hospitalId.Value;
                var n = await c.ExecuteAsync("""
                    UPDATE CF_HOSPITAL SET Code=@Code, Name=@Name, City=@City, HisVersion=@HisVersion, Channel=@Channel, Status=@Status, Notes=@Notes
                    WHERE HospitalId = @id
                    """, new { i.Code, i.Name, i.City, i.HisVersion, i.Channel, i.Status, i.Notes, id });
                if (n == 0) throw AppException.NotFound("Hospital not found.");
            }
            (exec as RoutingHospitalExecutor)?.Invalidate(id);
            await audit.LogAsync(userId, null, hospitalId is null ? "HospitalCreated" : "HospitalUpdated", new { id, i.Code, i.Name, i.Channel }, ct);
            return id;
        }
        catch (SqlException ex) when (ex.Number is 2627 or 2601)
        {
            throw AppException.Conflict("A hospital with this code already exists.");
        }
    }

    public async Task SaveConnectionAsync(int userId, int hospitalId, ConnectionInput i, CancellationToken ct)
    {
        if (string.IsNullOrWhiteSpace(i.Server) || string.IsNullOrWhiteSpace(i.DbName) || string.IsNullOrWhiteSpace(i.RoUser) || string.IsNullOrWhiteSpace(i.RwUser))
            throw AppException.BadRequest("Server, database, read login and write login are required.");
        if (i.Port is < 1 or > 65535) throw AppException.BadRequest("Port must be between 1 and 65535.");
        await using var c = await db.OpenAsync(ct);
        var exists = await c.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM CF_CONNECTION WHERE HospitalId = @hospitalId", new { hospitalId }) > 0;
        if (!exists && (string.IsNullOrEmpty(i.RoPassword) || string.IsNullOrEmpty(i.RwPassword)))
            throw AppException.BadRequest("Both passwords are required when adding a connection.");

        byte[]? ro = string.IsNullOrEmpty(i.RoPassword) ? null : vault.Encrypt(i.RoPassword);
        byte[]? rw = string.IsNullOrEmpty(i.RwPassword) ? null : vault.Encrypt(i.RwPassword);
        var p = new DynamicParameters(new { hospitalId, i.Server, i.Port, i.DbName, i.RoUser, i.RwUser, i.Encrypt, i.TrustServerCert, userId });
        p.Add("ro", ro, DbType.Binary); p.Add("rw", rw, DbType.Binary);
        if (exists)
            await c.ExecuteAsync("""
                UPDATE CF_CONNECTION SET Server=@Server, Port=@Port, DbName=@DbName, RoUser=@RoUser, RwUser=@RwUser,
                       RoPasswordEnc = ISNULL(@ro, RoPasswordEnc), RwPasswordEnc = ISNULL(@rw, RwPasswordEnc),
                       Encrypt=@Encrypt, TrustServerCert=@TrustServerCert, UpdatedAt=SYSUTCDATETIME(), UpdatedBy=@userId
                WHERE HospitalId=@hospitalId
                """, p);
        else
            await c.ExecuteAsync("""
                INSERT CF_CONNECTION (HospitalId, Server, Port, DbName, RoUser, RoPasswordEnc, RwUser, RwPasswordEnc, Encrypt, TrustServerCert, UpdatedBy)
                VALUES (@hospitalId, @Server, @Port, @DbName, @RoUser, @ro, @RwUser, @rw, @Encrypt, @TrustServerCert, @userId)
                """, p);
        await audit.LogAsync(userId, null, "ConnectionSaved", new { hospitalId, i.Server, i.Port, i.DbName, i.RoUser, i.RwUser }, ct);
    }

    public async Task<object> CaptureSchemaAsync(int userId, int hospitalId, CancellationToken ct)
    {
        var cols = await exec.ReadSchemaAsync(hospitalId, ct);
        if (cols.Count == 0) throw AppException.BadRequest("No tables were visible to the read-only login. Check GRANT SELECT ON SCHEMA::dbo TO carefix_ro.");

        var dt = new DataTable();
        dt.Columns.Add("HospitalId", typeof(int)); dt.Columns.Add("SchemaName", typeof(string)); dt.Columns.Add("TableName", typeof(string));
        dt.Columns.Add("ColumnName", typeof(string)); dt.Columns.Add("DataType", typeof(string));
        dt.Columns.Add("IsNullable", typeof(bool)); dt.Columns.Add("IsPk", typeof(bool));
        foreach (var col in cols) dt.Rows.Add(hospitalId, col.SchemaName, col.TableName, col.ColumnName, col.DataType, col.IsNullable, col.IsPk);

        await using var c = await db.OpenAsync(ct);
        await using var tx = (SqlTransaction)await c.BeginTransactionAsync(ct);
        await c.ExecuteAsync("DELETE CF_HOSPITAL_SCHEMA WHERE HospitalId = @hospitalId", new { hospitalId }, tx);
        using (var bulk = new SqlBulkCopy(c, SqlBulkCopyOptions.Default, tx) { DestinationTableName = "dbo.CF_HOSPITAL_SCHEMA" })
        {
            foreach (DataColumn dc in dt.Columns) bulk.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
            await bulk.WriteToServerAsync(dt, ct);
        }
        await c.ExecuteAsync("UPDATE CF_HOSPITAL SET SchemaCapturedAt = SYSUTCDATETIME() WHERE HospitalId = @hospitalId", new { hospitalId }, tx);
        await tx.CommitAsync(ct);
        kb.Invalidate(hospitalId);

        var tables = cols.Select(x => x.TableName).Distinct(StringComparer.OrdinalIgnoreCase).Count();
        await audit.LogAsync(userId == 0 ? null : userId, null, "SchemaCaptured", new { hospitalId, tables, columns = cols.Count }, ct);
        return new { tables, columns = cols.Count };
    }
}