pm04/program/TransportMauiApp/Services/DatabaseService.cs

using Npgsql;
using TransportMauiApp.Models;

namespace TransportMauiApp.Services;

public sealed class DatabaseService
{
    private const string ConnectionString =
        "Host=edu-pg.itiscaf.ru;Port=5432;Database=dbwork;Username=krylatov_go;Password=o#GQ0&n$t47;Search Path=krylatov_go;Include Error Detail=true";

    private readonly NpgsqlDataSource _dataSource = NpgsqlDataSource.Create(ConnectionString);

    public async Task<UserSession?> AuthenticateAsync(string login, string password)
    {
        const string sql = """
            select u.user_id, u.login, u.password, u.role_id, coalesce(r.role_name, '')
            from users u
            left join role r on r.role_id = u.role_id
            where u.login = @login
            """;

        await using var connection = await _dataSource.OpenConnectionAsync();
        await using var command = new NpgsqlCommand(sql, connection);
        command.Parameters.AddWithValue("login", login);

        await using var reader = await command.ExecuteReaderAsync();
        if (!await reader.ReadAsync())
        {
            return null;
        }

        var passwordHash = reader.GetString(2);
        var passwordIsValid = false;

        try
        {
            passwordIsValid = BCrypt.Net.BCrypt.Verify(password, passwordHash);
        }
        catch (Exception)
        {
            passwordIsValid = false;
        }

        if (!passwordIsValid)
        {
            return null;
        }

        return new UserSession
        {
            UserId = reader.GetInt32(0),
            Login = reader.GetString(1),
            RoleId = reader.IsDBNull(3) ? null : reader.GetInt32(3),
            RoleName = reader.GetString(4)
        };
    }

    public Task<int> GetDriversCountAsync()
    {
        return GetCountAsync("select count(*) from driver");
    }

    public Task<int> GetTransportsCountAsync()
    {
        return GetCountAsync("select count(*) from transport");
    }

    public Task<int> GetRoutesCountAsync()
    {
        return GetCountAsync("select count(*) from route");
    }

    public Task<int> GetFlightsCountAsync()
    {
        return GetCountAsync("select count(*) from transport_flight");
    }

    public Task<int> GetFlightInfoCountAsync()
    {
        return GetCountAsync("select count(*) from flight_info_view");
    }

    public async Task<IReadOnlyList<Driver>> GetDriversAsync()
    {
        const string sql = """
            select driver_id, name, surname, patronimic
            from driver
            order by driver_id
            """;

        var result = new List<Driver>();
        await using var connection = await _dataSource.OpenConnectionAsync();
        await using var command = new NpgsqlCommand(sql, connection);
        await using var reader = await command.ExecuteReaderAsync();

        while (await reader.ReadAsync())
        {
            result.Add(new Driver
            {
                DriverId = reader.GetInt32(0),
                Name = reader.GetString(1),
                Surname = reader.GetString(2),
                Patronimic = reader.GetString(3)
            });
        }

        return result;
    }

    public async Task<int> AddDriverAsync(Driver driver)
    {
        const string sql = """
            insert into driver (name, surname, patronimic)
            values (@name, @surname, @patronimic)
            returning driver_id
            """;

        await using var connection = await _dataSource.OpenConnectionAsync();
        await SyncSerialSequenceAsync(connection, "driver");

        await using var command = new NpgsqlCommand(sql, connection);
        command.Parameters.AddWithValue("name", driver.Name);
        command.Parameters.AddWithValue("surname", driver.Surname);
        command.Parameters.AddWithValue("patronimic", driver.Patronimic);
        return Convert.ToInt32(await command.ExecuteScalarAsync());
    }

    public async Task UpdateDriverAsync(Driver driver)
    {
        const string sql = """
            update driver
            set name = @name, surname = @surname, patronimic = @patronimic
            where driver_id = @driver_id
            """;

        await using var connection = await _dataSource.OpenConnectionAsync();
        await using var command = new NpgsqlCommand(sql, connection);
        command.Parameters.AddWithValue("driver_id", driver.DriverId);
        command.Parameters.AddWithValue("name", driver.Name);
        command.Parameters.AddWithValue("surname", driver.Surname);
        command.Parameters.AddWithValue("patronimic", driver.Patronimic);
        await command.ExecuteNonQueryAsync();
    }

    public async Task DeleteDriverAsync(int driverId)
    {
        await using var connection = await _dataSource.OpenConnectionAsync();
        await using var transaction = await connection.BeginTransactionAsync();

        try
        {
            await ExecuteNonQueryAsync(
                connection,
                transaction,
                "update transport set driver_id = null where driver_id = @driver_id",
                command => command.Parameters.AddWithValue("driver_id", driverId));

            await ExecuteNonQueryAsync(
                connection,
                transaction,
                "delete from driver where driver_id = @driver_id",
                command => command.Parameters.AddWithValue("driver_id", driverId));

            await transaction.CommitAsync();
        }
        catch
        {
            await transaction.RollbackAsync();
            throw;
        }
    }

    public async Task<IReadOnlyList<AppTransport>> GetTransportsAsync()
    {
        const string sql = """
            select t.transport_id, t.branch, t.model, t.license_plate_number, t.driver_id,
                   coalesce(d.surname || ' ' || d.name || ' ' || d.patronimic, '')
            from transport t
            left join driver d on d.driver_id = t.driver_id
            order by t.transport_id
            """;

        var result = new List<AppTransport>();
        await using var connection = await _dataSource.OpenConnectionAsync();
        await using var command = new NpgsqlCommand(sql, connection);
        await using var reader = await command.ExecuteReaderAsync();

        while (await reader.ReadAsync())
        {
            result.Add(new AppTransport
            {
                TransportId = reader.GetInt32(0),
                Branch = reader.GetString(1),
                Model = reader.GetString(2),
                LicensePlateNumber = reader.GetString(3),
                DriverId = reader.IsDBNull(4) ? null : reader.GetInt32(4),
                DriverFullName = reader.GetString(5)
            });
        }

        return result;
    }

    public async Task<int> AddTransportAsync(AppTransport transport)
    {
        const string sql = """
            insert into transport (branch, model, license_plate_number, driver_id)
            values (@branch, @model, @license_plate_number, @driver_id)
            returning transport_id
            """;

        await using var connection = await _dataSource.OpenConnectionAsync();
        await SyncSerialSequenceAsync(connection, "transport");

        await using var command = new NpgsqlCommand(sql, connection);
        AddTransportParameters(command, transport);
        return Convert.ToInt32(await command.ExecuteScalarAsync());
    }

    public async Task UpdateTransportAsync(AppTransport transport)
    {
        const string sql = """
            update transport
            set branch = @branch,
                model = @model,
                license_plate_number = @license_plate_number,
                driver_id = @driver_id
            where transport_id = @transport_id
            """;

        await using var connection = await _dataSource.OpenConnectionAsync();
        await using var command = new NpgsqlCommand(sql, connection);
        command.Parameters.AddWithValue("transport_id", transport.TransportId);
        AddTransportParameters(command, transport);
        await command.ExecuteNonQueryAsync();
    }

    public async Task DeleteTransportAsync(int transportId)
    {
        await using var connection = await _dataSource.OpenConnectionAsync();
        await using var transaction = await connection.BeginTransactionAsync();

        try
        {
            await ExecuteNonQueryAsync(
                connection,
                transaction,
                "update transport_flight set transport_id = null where transport_id = @transport_id",
                command => command.Parameters.AddWithValue("transport_id", transportId));

            await ExecuteNonQueryAsync(
                connection,
                transaction,
                "delete from transport where transport_id = @transport_id",
                command => command.Parameters.AddWithValue("transport_id", transportId));

            await transaction.CommitAsync();
        }
        catch
        {
            await transaction.RollbackAsync();
            throw;
        }
    }

    public async Task AssignDriverToTransportAsync(int transportId, int driverId)
    {
        const string sql = "call assign_driver_to_transport(@p_transport_id, @p_driver_id)";
        await ExecuteNonQueryAsync(sql, command =>
        {
            command.Parameters.AddWithValue("p_transport_id", transportId);
            command.Parameters.AddWithValue("p_driver_id", driverId);
        });
    }

    public async Task<IReadOnlyList<TransitRoute>> GetRoutesAsync()
    {
        const string sql = """
            select route_id, route_name
            from route
            order by route_id
            """;

        var result = new List<TransitRoute>();
        await using var connection = await _dataSource.OpenConnectionAsync();
        await using var command = new NpgsqlCommand(sql, connection);
        await using var reader = await command.ExecuteReaderAsync();

        while (await reader.ReadAsync())
        {
            result.Add(new TransitRoute
            {
                RouteId = reader.GetInt32(0),
                RouteName = reader.GetString(1)
            });
        }

        return result;
    }

    public async Task<int> AddRouteAsync(TransitRoute route)
    {
        const string sql = """
            insert into route (route_name)
            values (@route_name)
            returning route_id
            """;

        await using var connection = await _dataSource.OpenConnectionAsync();
        await SyncSerialSequenceAsync(connection, "route");

        await using var command = new NpgsqlCommand(sql, connection);
        command.Parameters.AddWithValue("route_name", route.RouteName);
        return Convert.ToInt32(await command.ExecuteScalarAsync());
    }

    public async Task UpdateRouteAsync(TransitRoute route)
    {
        const string sql = "update route set route_name = @route_name where route_id = @route_id";
        await ExecuteNonQueryAsync(sql, command =>
        {
            command.Parameters.AddWithValue("route_id", route.RouteId);
            command.Parameters.AddWithValue("route_name", route.RouteName);
        });
    }

    public async Task DeleteRouteAsync(int routeId)
    {
        await using var connection = await _dataSource.OpenConnectionAsync();
        await using var transaction = await connection.BeginTransactionAsync();

        try
        {
            await ExecuteNonQueryAsync(
                connection,
                transaction,
                "update transport_flight set route_id = null where route_id = @route_id",
                command => command.Parameters.AddWithValue("route_id", routeId));

            await ExecuteNonQueryAsync(
                connection,
                transaction,
                "update route_point set route_id = null where route_id = @route_id",
                command => command.Parameters.AddWithValue("route_id", routeId));

            await ExecuteNonQueryAsync(
                connection,
                transaction,
                "delete from route where route_id = @route_id",
                command => command.Parameters.AddWithValue("route_id", routeId));

            await transaction.CommitAsync();
        }
        catch
        {
            await transaction.RollbackAsync();
            throw;
        }
    }

    public async Task<IReadOnlyList<TransportFlight>> GetFlightsAsync()
    {
        const string sql = """
            select tf.flight_id, tf.duration, tf.transport_id, tf.route_id,
                   coalesce(t.model || ' ' || t.license_plate_number, ''),
                   coalesce(r.route_name, '')
            from transport_flight tf
            left join transport t on t.transport_id = tf.transport_id
            left join route r on r.route_id = tf.route_id
            order by tf.flight_id
            """;

        var result = new List<TransportFlight>();
        await using var connection = await _dataSource.OpenConnectionAsync();
        await using var command = new NpgsqlCommand(sql, connection);
        await using var reader = await command.ExecuteReaderAsync();

        while (await reader.ReadAsync())
        {
            result.Add(ReadFlight(reader));
        }

        return result;
    }

    public async Task<int> AddFlightAsync(TransportFlight flight)
    {
        const string sql = """
            insert into transport_flight (duration, transport_id, route_id)
            values (@duration, @transport_id, @route_id)
            returning flight_id
            """;

        await using var connection = await _dataSource.OpenConnectionAsync();
        await SyncSerialSequenceAsync(connection, "transport_flight");

        await using var command = new NpgsqlCommand(sql, connection);
        AddFlightParameters(command, flight);
        return Convert.ToInt32(await command.ExecuteScalarAsync());
    }

    public async Task UpdateFlightAsync(TransportFlight flight)
    {
        const string sql = """
            update transport_flight
            set duration = @duration,
                transport_id = @transport_id,
                route_id = @route_id
            where flight_id = @flight_id
            """;

        await using var connection = await _dataSource.OpenConnectionAsync();
        await using var command = new NpgsqlCommand(sql, connection);
        command.Parameters.AddWithValue("flight_id", flight.FlightId);
        AddFlightParameters(command, flight);
        await command.ExecuteNonQueryAsync();
    }

    public async Task DeleteFlightAsync(int flightId)
    {
        const string sql = "delete from transport_flight where flight_id = @flight_id";
        await ExecuteNonQueryAsync(sql, command => command.Parameters.AddWithValue("flight_id", flightId));
    }

    public async Task<IReadOnlyList<FlightInfo>> GetFlightInfoAsync()
    {
        const string sql = """
            select flight_id, duration, route_name, model, license_plate_number, driver_fio
            from flight_info_view
            order by flight_id
            """;

        var result = new List<FlightInfo>();
        await using var connection = await _dataSource.OpenConnectionAsync();
        await using var command = new NpgsqlCommand(sql, connection);
        await using var reader = await command.ExecuteReaderAsync();

        while (await reader.ReadAsync())
        {
            result.Add(new FlightInfo
            {
                FlightId = reader.GetInt32(0),
                Duration = reader.GetTimeSpan(1),
                RouteName = reader.GetString(2),
                Model = reader.GetString(3),
                LicensePlateNumber = reader.GetString(4),
                DriverFio = reader.GetString(5)
            });
        }

        return result;
    }

    public async Task<IReadOnlyList<FlightInfo>> GetFlightsByRouteAsync(int routeId)
    {
        const string sql = """
            select f.flight_id, f.duration, r.route_name, f.model, f.license_plate_number, f.driver_full_name
            from get_flights_by_route(@p_route_id) f
            join route r on r.route_id = @p_route_id
            order by f.flight_id
            """;

        var result = new List<FlightInfo>();
        await using var connection = await _dataSource.OpenConnectionAsync();
        await using var command = new NpgsqlCommand(sql, connection);
        command.Parameters.AddWithValue("p_route_id", routeId);
        await using var reader = await command.ExecuteReaderAsync();

        while (await reader.ReadAsync())
        {
            result.Add(new FlightInfo
            {
                FlightId = reader.GetInt32(0),
                Duration = reader.GetTimeSpan(1),
                RouteName = reader.GetString(2),
                Model = reader.GetString(3),
                LicensePlateNumber = reader.GetString(4),
                DriverFio = reader.GetString(5)
            });
        }

        return result;
    }

    private static TransportFlight ReadFlight(NpgsqlDataReader reader)
    {
        return new TransportFlight
        {
            FlightId = reader.GetInt32(0),
            Duration = reader.GetTimeSpan(1),
            TransportId = reader.IsDBNull(2) ? null : reader.GetInt32(2),
            RouteId = reader.IsDBNull(3) ? null : reader.GetInt32(3),
            TransportName = reader.GetString(4),
            RouteName = reader.GetString(5)
        };
    }

    private static void AddTransportParameters(NpgsqlCommand command, AppTransport transport)
    {
        command.Parameters.AddWithValue("branch", transport.Branch);
        command.Parameters.AddWithValue("model", transport.Model);
        command.Parameters.AddWithValue("license_plate_number", transport.LicensePlateNumber);
        command.Parameters.AddWithValue("driver_id", (object?)transport.DriverId ?? DBNull.Value);
    }

    private static void AddFlightParameters(NpgsqlCommand command, TransportFlight flight)
    {
        command.Parameters.AddWithValue("duration", flight.Duration);
        command.Parameters.AddWithValue("transport_id", (object?)flight.TransportId ?? DBNull.Value);
        command.Parameters.AddWithValue("route_id", (object?)flight.RouteId ?? DBNull.Value);
    }

    private static async Task SyncSerialSequenceAsync(NpgsqlConnection connection, string tableName)
    {
        var sql = tableName switch
        {
            "driver" => """
                select setval(
                    pg_get_serial_sequence('driver', 'driver_id'),
                    coalesce((select max(driver_id) from driver), 0) + 1,
                    false
                )
                """,
            "transport" => """
                select setval(
                    pg_get_serial_sequence('transport', 'transport_id'),
                    coalesce((select max(transport_id) from transport), 0) + 1,
                    false
                )
                """,
            "route" => """
                select setval(
                    pg_get_serial_sequence('route', 'route_id'),
                    coalesce((select max(route_id) from route), 0) + 1,
                    false
                )
                """,
            "transport_flight" => """
                select setval(
                    pg_get_serial_sequence('transport_flight', 'flight_id'),
                    coalesce((select max(flight_id) from transport_flight), 0) + 1,
                    false
                )
                """,
            _ => throw new ArgumentOutOfRangeException(nameof(tableName), tableName, "Sequence sync is not configured for this table.")
        };

        await using var command = new NpgsqlCommand(sql, connection);
        await command.ExecuteNonQueryAsync();
    }

    private async Task ExecuteNonQueryAsync(string sql, Action<NpgsqlCommand> configure)
    {
        await using var connection = await _dataSource.OpenConnectionAsync();
        await using var command = new NpgsqlCommand(sql, connection);
        configure(command);
        await command.ExecuteNonQueryAsync();
    }

    private async Task<int> GetCountAsync(string sql)
    {
        await using var connection = await _dataSource.OpenConnectionAsync();
        await using var command = new NpgsqlCommand(sql, connection);
        return Convert.ToInt32(await command.ExecuteScalarAsync());
    }

    private static async Task ExecuteNonQueryAsync(
        NpgsqlConnection connection,
        NpgsqlTransaction transaction,
        string sql,
        Action<NpgsqlCommand> configure)
    {
        await using var command = new NpgsqlCommand(sql, connection, transaction);
        configure(command);
        await command.ExecuteNonQueryAsync();
    }
}