using System.Net;
using System.Security.Cryptography;
using System.Text;
using Npgsql;
using NpgsqlTypes;

namespace AiLogia.Auth.Data;

public sealed class AuthDb
{
    private readonly string _cs;
    public AuthDb(IConfiguration cfg) => _cs = cfg.GetConnectionString("Default")!;

    public async Task<long> UpsertUserAsync(long tgId, string? username, string? fullName, CancellationToken ct)
    {
        await using var con = new NpgsqlConnection(_cs);
        await con.OpenAsync(ct);
        await using var cmd = new NpgsqlCommand(@"
            insert into users(telegram_user_id, username, full_name)
            values (@tg, @u, @f)
            on conflict (telegram_user_id) do update
            set username = excluded.username,
                full_name = excluded.full_name
            returning id;", con);
        cmd.Parameters.AddWithValue("tg", tgId);
        cmd.Parameters.AddWithValue("u", (object?)username ?? DBNull.Value);
        cmd.Parameters.AddWithValue("f", (object?)fullName ?? DBNull.Value);
        var id = (long)(await cmd.ExecuteScalarAsync(ct))!;
        return id;
    }

    public static byte[] Sha256(string token)
    {
        using var sha = SHA256.Create();
        return sha.ComputeHash(Encoding.UTF8.GetBytes(token));
    }

    public async Task InsertMagicLinkAsync(
        long userId, byte[] tokenHash, DateTime expiresAt,
        IPAddress? ip, string? ua, CancellationToken ct)
    {
        await using var con = new NpgsqlConnection(_cs);
        await con.OpenAsync(ct);
        await using var cmd = new NpgsqlCommand(@"
        insert into magic_links(user_id, token_hash, expires_at, created_ip, created_ua)
        values (@uid, @hash, @exp, @ip, @ua);", con);

        cmd.Parameters.AddWithValue("uid", userId);
        cmd.Parameters.AddWithValue("hash", tokenHash);
        cmd.Parameters.AddWithValue("exp", expiresAt);

        var pIp = new NpgsqlParameter("ip", NpgsqlDbType.Inet)
        { Value = (object?)ip ?? DBNull.Value };
        cmd.Parameters.Add(pIp);

        cmd.Parameters.AddWithValue("ua", (object?)ua ?? DBNull.Value);

        await cmd.ExecuteNonQueryAsync(ct);
    }

    public async Task<(bool found, long userId, DateTime expiresAt, DateTime? usedAt, long tgId, string? uname, string? fname)>
        GetLinkWithUserAsync(byte[] tokenHash, CancellationToken ct)
    {
        await using var con = new NpgsqlConnection(_cs);
        await con.OpenAsync(ct);
        await using var cmd = new NpgsqlCommand(@"
            select l.user_id, l.expires_at, l.used_at,
                   u.telegram_user_id, u.username, u.full_name
            from magic_links l
            join users u on u.id = l.user_id
            where l.token_hash = @h;", con);
        cmd.Parameters.AddWithValue("h", tokenHash);
        await using var rd = await cmd.ExecuteReaderAsync(ct);
        if (!await rd.ReadAsync(ct)) return (false, 0, default, null, 0, null, null);
        var userId = rd.GetInt64(0);
        var exp = rd.GetDateTime(1);
        var used = rd.IsDBNull(2) ? (DateTime?)null : rd.GetDateTime(2);
        var tgId = rd.GetInt64(3);
        var uname = rd.IsDBNull(4) ? null : rd.GetString(4);
        var fname = rd.IsDBNull(5) ? null : rd.GetString(5);
        return (true, userId, exp, used, tgId, uname, fname);
    }

    public async Task<bool> ConsumeAsync(byte[] tokenHash, CancellationToken ct)
    {
        await using var con = new NpgsqlConnection(_cs);
        await con.OpenAsync(ct);
        await using var cmd = new NpgsqlCommand(@"
            update magic_links set used_at = now()
            where token_hash = @h and used_at is null;", con);
        cmd.Parameters.AddWithValue("h", tokenHash);
        var n = await cmd.ExecuteNonQueryAsync(ct);
        return n > 0;
    }
}