practice/practiceApplicaiton/Repository.cs

using Npgsql;

namespace practiceApplicaiton;

/// <summary>
/// Все операции с БД. Использует параметризованные запросы
/// для защиты от SQL-инъекций.
/// </summary>
public static class Repository
{
    // ── БРОНИРОВАНИЯ ────────────────────────────────────────────────

    public static async Task<List<Booking>> GetBookingsAsync(string search = "")
    {
        var list = new List<Booking>();
        await using var conn = Database.GetConnection();
        await conn.OpenAsync();

        var sql = """
            SELECT b.booking_id, b.client_id, b.room_id,
                   b.check_in_date, b.check_out_date, b.price_per_night,
                   c.last_name || ' ' || c.first_name || ' ' || COALESCE(c.patronymic,'') AS full_name,
                   r.room_number, rt.type_name
            FROM Booking b
            JOIN Client c   ON c.client_id = b.client_id
            JOIN Room r     ON r.room_id = b.room_id
            JOIN RoomType rt ON rt.room_type_id = r.room_type_id
            WHERE (@search = '' OR LOWER(c.last_name) LIKE LOWER(@search)
                               OR CAST(r.room_number AS TEXT) LIKE @search)
            ORDER BY b.check_in_date DESC
            """;

        await using var cmd = new NpgsqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("search", string.IsNullOrEmpty(search) ? "" : $"%{search}%");

        await using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            list.Add(new Booking
            {
                BookingId = reader.GetInt32(0),
                ClientId = reader.GetInt32(1),
                RoomId = reader.GetInt32(2),
                CheckInDate = reader.GetDateTime(3),
                CheckOutDate = reader.GetDateTime(4),
                PricePerNight = reader.GetDecimal(5),
                ClientFullName = reader.GetString(6).Trim(),
                RoomNumber = reader.GetInt32(7),
                RoomType = reader.GetString(8),
            });
        }
        return list;
    }

    public static async Task CreateBookingAsync(int clientId, int roomId,
        DateTime checkIn, DateTime checkOut, decimal price)
    {
        await using var conn = Database.GetConnection();
        await conn.OpenAsync();
        // Вызываем хранимую процедуру
        await using var cmd = new NpgsqlCommand(
            "CALL create_booking(@cid, @rid, @ci, @co, @price)", conn);
        cmd.Parameters.AddWithValue("cid", clientId);
        cmd.Parameters.AddWithValue("rid", roomId);
        cmd.Parameters.AddWithValue("ci", checkIn);
        cmd.Parameters.AddWithValue("co", checkOut);
        cmd.Parameters.AddWithValue("price", price);
        await cmd.ExecuteNonQueryAsync();
    }

    public static async Task UpdateBookingAsync(int bookingId, DateTime checkIn,
        DateTime checkOut, decimal price)
    {
        await using var conn = Database.GetConnection();
        await conn.OpenAsync();
        await using var cmd = new NpgsqlCommand("""
            UPDATE Booking SET check_in_date=@ci, check_out_date=@co, price_per_night=@price
            WHERE booking_id=@id
            """, conn);
        cmd.Parameters.AddWithValue("ci", checkIn);
        cmd.Parameters.AddWithValue("co", checkOut);
        cmd.Parameters.AddWithValue("price", price);
        cmd.Parameters.AddWithValue("id", bookingId);
        await cmd.ExecuteNonQueryAsync();
    }

    public static async Task DeleteBookingAsync(int bookingId)
    {
        await using var conn = Database.GetConnection();
        await conn.OpenAsync();
        await using var cmd = new NpgsqlCommand(
            "DELETE FROM Booking WHERE booking_id=@id", conn);
        cmd.Parameters.AddWithValue("id", bookingId);
        await cmd.ExecuteNonQueryAsync();
    }

    // ── КЛИЕНТЫ ─────────────────────────────────────────────────────

    public static async Task<List<Client>> GetClientsAsync(string search = "")
    {
        var list = new List<Client>();
        await using var conn = Database.GetConnection();
        await conn.OpenAsync();

        var sql = """
            SELECT c.client_id, c.firm_id, c.last_name, c.first_name,
                   COALESCE(c.patronymic,''), c.phone_number, tf.firm_name
            FROM Client c
            JOIN TourFirm tf ON tf.firm_id = c.firm_id
            WHERE (@s = '' OR LOWER(c.last_name) LIKE LOWER(@s)
                           OR c.phone_number LIKE @s)
            ORDER BY c.last_name
            """;
        await using var cmd = new NpgsqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("s", string.IsNullOrEmpty(search) ? "" : $"%{search}%");

        await using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
            list.Add(new Client
            {
                ClientId = reader.GetInt32(0),
                FirmId = reader.GetInt32(1),
                LastName = reader.GetString(2),
                FirstName = reader.GetString(3),
                Patronymic = reader.GetString(4),
                PhoneNumber = reader.GetString(5),
                FirmName = reader.GetString(6),
            });
        return list;
    }

    public static async Task AddClientAsync(Client c)
    {
        await using var conn = Database.GetConnection();
        await conn.OpenAsync();
        await using var cmd = new NpgsqlCommand("""
            INSERT INTO Client (firm_id,last_name,first_name,patronymic,phone_number)
            VALUES (@fid,@ln,@fn,@pat,@ph)
            """, conn);
        cmd.Parameters.AddWithValue("fid", c.FirmId);
        cmd.Parameters.AddWithValue("ln", c.LastName);
        cmd.Parameters.AddWithValue("fn", c.FirstName);
        cmd.Parameters.AddWithValue("pat", (object?)c.Patronymic ?? DBNull.Value);
        cmd.Parameters.AddWithValue("ph", c.PhoneNumber);
        await cmd.ExecuteNonQueryAsync();
    }

    public static async Task UpdateClientAsync(Client c)
    {
        await using var conn = Database.GetConnection();
        await conn.OpenAsync();
        await using var cmd = new NpgsqlCommand("""
            UPDATE Client SET firm_id=@fid, last_name=@ln, first_name=@fn,
                              patronymic=@pat, phone_number=@ph
            WHERE client_id=@id
            """, conn);
        cmd.Parameters.AddWithValue("fid", c.FirmId);
        cmd.Parameters.AddWithValue("ln", c.LastName);
        cmd.Parameters.AddWithValue("fn", c.FirstName);
        cmd.Parameters.AddWithValue("pat", (object?)c.Patronymic ?? DBNull.Value);
        cmd.Parameters.AddWithValue("ph", c.PhoneNumber);
        cmd.Parameters.AddWithValue("id", c.ClientId);
        await cmd.ExecuteNonQueryAsync();
    }

    public static async Task DeleteClientAsync(int clientId)
    {
        await using var conn = Database.GetConnection();
        await conn.OpenAsync();
        await using var cmd = new NpgsqlCommand(
            "DELETE FROM Client WHERE client_id=@id", conn);
        cmd.Parameters.AddWithValue("id", clientId);
        await cmd.ExecuteNonQueryAsync();
    }

    // ── КОМНАТЫ ─────────────────────────────────────────────────────

    public static async Task<List<Room>> GetRoomsAsync()
    {
        var list = new List<Room>();
        await using var conn = Database.GetConnection();
        await conn.OpenAsync();
        await using var cmd = new NpgsqlCommand("""
            SELECT r.room_id, r.room_type_id, r.room_number, r.occupancy, r.rating, rt.type_name
            FROM Room r JOIN RoomType rt ON rt.room_type_id = r.room_type_id
            ORDER BY r.room_number
            """, conn);
        await using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
            list.Add(new Room
            {
                RoomId = reader.GetInt32(0),
                RoomTypeId = reader.GetInt32(1),
                RoomNumber = reader.GetInt32(2),
                Occupancy = reader.GetInt32(3),
                Rating = reader.GetDecimal(4),
                TypeName = reader.GetString(5),
            });
        return list;
    }

    public static async Task UpdateRoomRatingAsync(int roomId, decimal rating)
    {
        await using var conn = Database.GetConnection();
        await conn.OpenAsync();
        await using var cmd = new NpgsqlCommand(
            "UPDATE Room SET rating=@r WHERE room_id=@id", conn);
        cmd.Parameters.AddWithValue("r", rating);
        cmd.Parameters.AddWithValue("id", roomId);
        await cmd.ExecuteNonQueryAsync();
    }

    // ── ВСПОМОГАТЕЛЬНЫЕ ─────────────────────────────────────────────

    public static async Task<List<TourFirm>> GetFirmsAsync()
    {
        var list = new List<TourFirm>();
        await using var conn = Database.GetConnection();
        await conn.OpenAsync();
        await using var cmd = new NpgsqlCommand(
            "SELECT firm_id, firm_name, people_count FROM TourFirm ORDER BY firm_name", conn);
        await using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
            list.Add(new TourFirm
            {
                FirmId = reader.GetInt32(0),
                FirmName = reader.GetString(1),
                PeopleCount = reader.GetInt32(2),
            });
        return list;
    }

    // ── ОТЧЁТ — итоговая стоимость бронирований ─────────────────────

    public static async Task<List<Booking>> GetBookingReportAsync(
        DateTime from, DateTime to)
    {
        var list = new List<Booking>();
        await using var conn = Database.GetConnection();
        await conn.OpenAsync();
        await using var cmd = new NpgsqlCommand("""
            SELECT b.booking_id, b.client_id, b.room_id,
                   b.check_in_date, b.check_out_date, b.price_per_night,
                   c.last_name || ' ' || c.first_name AS full_name,
                   r.room_number, rt.type_name
            FROM BookingCostView b
            JOIN Booking bk ON bk.booking_id = b.booking_id
            JOIN Client c   ON c.client_id = bk.client_id
            JOIN Room r     ON r.room_id = bk.room_id
            JOIN RoomType rt ON rt.room_type_id = r.room_type_id
            WHERE bk.check_in_date >= @from AND bk.check_out_date <= @to
            ORDER BY bk.check_in_date
            """, conn);
        // Проще использовать BookingCostView напрямую
        cmd.CommandText = """
            SELECT booking_id, 0, 0,
                   check_in_date, check_out_date, price_per_night,
                   client_full_name, room_number, room_type
            FROM BookingCostView
            WHERE check_in_date >= @from AND check_out_date <= @to
            ORDER BY check_in_date
            """;
        cmd.Parameters.AddWithValue("from", from);
        cmd.Parameters.AddWithValue("to", to);
        await using var reader = await cmd.ExecuteReaderAsync();
        while (await reader.ReadAsync())
            list.Add(new Booking
            {
                BookingId = reader.GetInt32(0),
                CheckInDate = reader.GetDateTime(3),
                CheckOutDate = reader.GetDateTime(4),
                PricePerNight = reader.GetDecimal(5),
                ClientFullName = reader.GetString(6).Trim(),
                RoomNumber = reader.GetInt32(7),
                RoomType = reader.GetString(8),
            });
        return list;
    }
}