namespace NeonSprawl.Server.Game.Contracts;

/// <summary>PostgreSQL-backed contract instances keyed by normalized contract instance id (NEO-146).</summary>
public sealed class PostgresContractInstanceStore(Npgsql.NpgsqlDataSource dataSource) : IContractInstanceStore
{
    /// <inheritdoc />
    public bool CanWritePlayer(string playerId)
    {
        var player = ContractInstanceIds.NormalizePlayerId(playerId);
        if (player.Length == 0)
        {
            return false;
        }

        PostgresContractInstanceBootstrap.EnsureSchema(dataSource);
        using var conn = dataSource.OpenConnection();
        return PlayerExists(conn, player);
    }

    /// <inheritdoc />
    public bool TryGet(string playerId, string contractInstanceId, out ContractInstanceState snapshot)
    {
        snapshot = null!;
        var player = ContractInstanceIds.NormalizePlayerId(playerId);
        var instanceId = ContractInstanceIds.NormalizeContractInstanceId(contractInstanceId);
        if (player.Length == 0 || instanceId.Length == 0)
        {
            return false;
        }

        PostgresContractInstanceBootstrap.EnsureSchema(dataSource);
        using var conn = dataSource.OpenConnection();
        if (!PlayerExists(conn, player))
        {
            return false;
        }

        using var cmd = new Npgsql.NpgsqlCommand(
            """
            SELECT contract_instance_id, template_id, player_id, status, seed_bucket, issued_at, completed_at
            FROM contract_instance
            WHERE contract_instance_id = @id AND player_id = @pid
            LIMIT 1;
            """,
            conn);
        cmd.Parameters.AddWithValue("id", instanceId);
        cmd.Parameters.AddWithValue("pid", player);
        using var reader = cmd.ExecuteReader();
        if (!reader.Read())
        {
            return false;
        }

        snapshot = ReadSnapshot(reader);
        return true;
    }

    /// <inheritdoc />
    public bool TryGetActiveForPlayer(string playerId, out ContractInstanceState snapshot)
    {
        snapshot = null!;
        var player = ContractInstanceIds.NormalizePlayerId(playerId);
        if (player.Length == 0)
        {
            return false;
        }

        PostgresContractInstanceBootstrap.EnsureSchema(dataSource);
        using var conn = dataSource.OpenConnection();
        if (!PlayerExists(conn, player))
        {
            return false;
        }

        using var cmd = new Npgsql.NpgsqlCommand(
            """
            SELECT contract_instance_id, template_id, player_id, status, seed_bucket, issued_at, completed_at
            FROM contract_instance
            WHERE player_id = @pid AND status = 'active'
            LIMIT 1;
            """,
            conn);
        cmd.Parameters.AddWithValue("pid", player);
        using var reader = cmd.ExecuteReader();
        if (!reader.Read())
        {
            return false;
        }

        snapshot = ReadSnapshot(reader);
        return snapshot.Status == ContractInstanceStatus.Active;
    }

    /// <inheritdoc />
    public bool TryCreateActive(
        string playerId,
        string contractInstanceId,
        string templateId,
        string seedBucket,
        DateTimeOffset issuedAt,
        out ContractInstanceState snapshot)
    {
        snapshot = null!;
        var player = ContractInstanceIds.NormalizePlayerId(playerId);
        var instanceId = ContractInstanceIds.NormalizeContractInstanceId(contractInstanceId);
        var normalizedTemplateId = ContractInstanceIds.NormalizeTemplateId(templateId);
        var normalizedSeedBucket = ContractInstanceIds.NormalizeSeedBucket(seedBucket);
        if (player.Length == 0 ||
            instanceId.Length == 0 ||
            normalizedTemplateId.Length == 0 ||
            normalizedSeedBucket.Length == 0)
        {
            return false;
        }

        PostgresContractInstanceBootstrap.EnsureSchema(dataSource);
        using var conn = dataSource.OpenConnection();
        using var tx = conn.BeginTransaction();
        if (!PlayerExists(conn, player, tx))
        {
            tx.Rollback();
            return false;
        }

        ContractInstanceState? activeSnapshot = null;
        using (var activeSel = new Npgsql.NpgsqlCommand(
                   """
                   SELECT contract_instance_id, template_id, player_id, status, seed_bucket, issued_at, completed_at
                   FROM contract_instance
                   WHERE player_id = @pid AND status = 'active'
                   LIMIT 1
                   FOR UPDATE;
                   """,
                   conn,
                   tx))
        {
            activeSel.Parameters.AddWithValue("pid", player);
            using var reader = activeSel.ExecuteReader();
            if (reader.Read())
            {
                activeSnapshot = ReadSnapshot(reader);
            }
        }

        if (activeSnapshot is not null)
        {
            snapshot = activeSnapshot;
            tx.Rollback();
            return false;
        }

        using var insert = new Npgsql.NpgsqlCommand(
            """
            INSERT INTO contract_instance (
                contract_instance_id, player_id, template_id, status, seed_bucket, issued_at, updated_at)
            VALUES (@id, @pid, @tid, 'active', @seed, @issued, now())
            ON CONFLICT (contract_instance_id) DO NOTHING;
            """,
            conn,
            tx);
        insert.Parameters.AddWithValue("id", instanceId);
        insert.Parameters.AddWithValue("pid", player);
        insert.Parameters.AddWithValue("tid", normalizedTemplateId);
        insert.Parameters.AddWithValue("seed", normalizedSeedBucket);
        insert.Parameters.AddWithValue("issued", issuedAt);
        try
        {
            if (insert.ExecuteNonQuery() > 0)
            {
                tx.Commit();
                snapshot = new ContractInstanceState(
                    instanceId,
                    normalizedTemplateId,
                    player,
                    ContractInstanceStatus.Active,
                    normalizedSeedBucket,
                    issuedAt,
                    null);
                return true;
            }
        }
        catch (Npgsql.PostgresException ex) when (ex.SqlState == Npgsql.PostgresErrorCodes.UniqueViolation)
        {
            tx.Rollback();
            return TryReadDenySnapshot(player, instanceId, out snapshot);
        }

        using (var existingSel = new Npgsql.NpgsqlCommand(
                   """
                   SELECT contract_instance_id, template_id, player_id, status, seed_bucket, issued_at, completed_at
                   FROM contract_instance
                   WHERE contract_instance_id = @id
                   LIMIT 1;
                   """,
                   conn,
                   tx))
        {
            existingSel.Parameters.AddWithValue("id", instanceId);
            using var reader = existingSel.ExecuteReader();
            if (reader.Read())
            {
                snapshot = ReadSnapshot(reader);
            }
        }

        tx.Rollback();
        return false;
    }

    private bool TryReadDenySnapshot(string player, string instanceId, out ContractInstanceState snapshot)
    {
        if (TryGet(player, instanceId, out snapshot))
        {
            return false;
        }

        if (TryGetActiveForPlayer(player, out snapshot))
        {
            return false;
        }

        snapshot = null!;
        return false;
    }

    /// <inheritdoc />
    public bool TryMarkComplete(
        string playerId,
        string contractInstanceId,
        DateTimeOffset completedAt,
        out ContractInstanceState snapshot)
    {
        snapshot = null!;
        var player = ContractInstanceIds.NormalizePlayerId(playerId);
        var instanceId = ContractInstanceIds.NormalizeContractInstanceId(contractInstanceId);
        if (player.Length == 0 || instanceId.Length == 0)
        {
            return false;
        }

        PostgresContractInstanceBootstrap.EnsureSchema(dataSource);
        using var conn = dataSource.OpenConnection();
        using var tx = conn.BeginTransaction();
        if (!PlayerExists(conn, player, tx))
        {
            tx.Rollback();
            return false;
        }

        ContractInstanceState current;
        using (var sel = new Npgsql.NpgsqlCommand(
                   """
                   SELECT contract_instance_id, template_id, player_id, status, seed_bucket, issued_at, completed_at
                   FROM contract_instance
                   WHERE contract_instance_id = @id AND player_id = @pid
                   LIMIT 1
                   FOR UPDATE;
                   """,
                   conn,
                   tx))
        {
            sel.Parameters.AddWithValue("id", instanceId);
            sel.Parameters.AddWithValue("pid", player);
            using var reader = sel.ExecuteReader();
            if (!reader.Read())
            {
                tx.Rollback();
                return false;
            }

            current = ReadSnapshot(reader);
        }

        if (current.Status == ContractInstanceStatus.Completed)
        {
            snapshot = current;
            tx.Rollback();
            return false;
        }

        if (current.Status != ContractInstanceStatus.Active)
        {
            snapshot = current;
            tx.Rollback();
            return false;
        }

        using (var update = new Npgsql.NpgsqlCommand(
                   """
                   UPDATE contract_instance
                   SET status = 'completed',
                       completed_at = @completed,
                       updated_at = now()
                   WHERE contract_instance_id = @id AND player_id = @pid;
                   """,
                   conn,
                   tx))
        {
            update.Parameters.AddWithValue("id", instanceId);
            update.Parameters.AddWithValue("pid", player);
            update.Parameters.AddWithValue("completed", completedAt);
            update.ExecuteNonQuery();
        }

        tx.Commit();
        snapshot = new ContractInstanceState(
            current.ContractInstanceId,
            current.TemplateId,
            current.PlayerId,
            ContractInstanceStatus.Completed,
            current.SeedBucket,
            current.IssuedAt,
            completedAt);
        return true;
    }

    /// <inheritdoc />
    public IReadOnlyList<ContractInstanceState> ListForPlayer(string playerId, int maxCompletedRows)
    {
        var player = ContractInstanceIds.NormalizePlayerId(playerId);
        if (player.Length == 0 || maxCompletedRows < 0)
        {
            return [];
        }

        PostgresContractInstanceBootstrap.EnsureSchema(dataSource);
        using var conn = dataSource.OpenConnection();
        if (!PlayerExists(conn, player))
        {
            return [];
        }

        // Single statement snapshot — avoids active+completed duplicate rows if status flips mid-read.
        using var cmd = new Npgsql.NpgsqlCommand(
            """
            WITH active_row AS (
                SELECT contract_instance_id, template_id, player_id, status, seed_bucket, issued_at, completed_at, 0 AS section
                FROM contract_instance
                WHERE player_id = @pid AND status = 'active'
                LIMIT 1
            ),
            completed_rows AS (
                SELECT contract_instance_id, template_id, player_id, status, seed_bucket, issued_at, completed_at, 1 AS section
                FROM contract_instance
                WHERE player_id = @pid AND status = 'completed'
                ORDER BY issued_at DESC, contract_instance_id ASC
                LIMIT @limit
            )
            SELECT contract_instance_id, template_id, player_id, status, seed_bucket, issued_at, completed_at
            FROM (
                SELECT * FROM active_row
                UNION ALL
                SELECT * FROM completed_rows
            ) AS listed
            ORDER BY section, issued_at DESC, contract_instance_id ASC;
            """,
            conn);
        cmd.Parameters.AddWithValue("pid", player);
        cmd.Parameters.AddWithValue("limit", maxCompletedRows);

        var result = new List<ContractInstanceState>();
        using var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            result.Add(ReadSnapshot(reader));
        }

        return result;
    }

    /// <inheritdoc />
    public bool TryClearInstance(string playerId, string contractInstanceId)
    {
        var player = ContractInstanceIds.NormalizePlayerId(playerId);
        var instanceId = ContractInstanceIds.NormalizeContractInstanceId(contractInstanceId);
        if (player.Length == 0 || instanceId.Length == 0 || !CanWritePlayer(player))
        {
            return false;
        }

        PostgresContractInstanceBootstrap.EnsureSchema(dataSource);
        using var conn = dataSource.OpenConnection();
        using var cmd = new Npgsql.NpgsqlCommand(
            """
            DELETE FROM contract_instance
            WHERE contract_instance_id = @id AND player_id = @pid;
            """,
            conn);
        cmd.Parameters.AddWithValue("id", instanceId);
        cmd.Parameters.AddWithValue("pid", player);
        cmd.ExecuteNonQuery();
        return true;
    }

    private static ContractInstanceState ReadSnapshot(Npgsql.NpgsqlDataReader reader) =>
        new(
            reader.GetString(0),
            reader.GetString(1),
            reader.GetString(2),
            ParseStatus(reader.GetString(3)),
            reader.GetString(4),
            reader.GetFieldValue<DateTimeOffset>(5),
            reader.IsDBNull(6) ? null : reader.GetFieldValue<DateTimeOffset>(6));

    private static ContractInstanceStatus ParseStatus(string raw) =>
        raw switch
        {
            "active" => ContractInstanceStatus.Active,
            "completed" => ContractInstanceStatus.Completed,
            "expired" => ContractInstanceStatus.Expired,
            _ => throw new InvalidOperationException($"Unknown contract instance status '{raw}'."),
        };

    private static bool PlayerExists(Npgsql.NpgsqlConnection conn, string playerIdNormalized) =>
        PlayerExists(conn, playerIdNormalized, null);

    private static bool PlayerExists(
        Npgsql.NpgsqlConnection conn,
        string playerIdNormalized,
        Npgsql.NpgsqlTransaction? tx)
    {
        using var cmd = new Npgsql.NpgsqlCommand(
            "SELECT 1 FROM player_position WHERE player_id = @pid LIMIT 1;",
            conn,
            tx);
        cmd.Parameters.AddWithValue("pid", playerIdNormalized);
        return cmd.ExecuteScalar() is not null;
    }
}