autoprokatIS/Database.cs

using System.Data;
using System.Security.Cryptography;
using Npgsql;

namespace AutorentWinForms;

public sealed record UserSession(int UserId, string Login, string FullName, string Role);

public sealed class Database : IDisposable
{
    private readonly string _connectionString;
    private UserSession? _session;

    public Database(string connectionString)
    {
        _connectionString = connectionString;
    }

    public void Initialize()
    {
        ExecuteNonQuery(SchemaSql);
        Seed();
        SyncSequences();
    }

    public void SetSession(UserSession session)
    {
        _session = session;
        ExecuteNonQuery(
            """
            INSERT INTO app_context(key, value) VALUES('current_user', @value)
            ON CONFLICT(key) DO UPDATE SET value = EXCLUDED.value
            """,
            new() { ["@value"] = session.Login });
    }

    public UserSession? Authenticate(string login, string password)
    {
        return login.Trim() switch
        {
            "garanin_gi" when password == "6qw#6xUyG%#" =>
                new UserSession(1, "garanin_gi", "Администратор PostgreSQL", "admin"),
            "manager" when password == "Manager123!" =>
                new UserSession(2, "manager", "Менеджер проката", "manager"),
            "shagavaliev_iv" when password == "75&n057$380" =>
                new UserSession(3, "shagavaliev_iv", "Пользователь просмотра", "viewer"),
            _ => null
        };
    }

    public DataTable Query(string sql, Dictionary<string, object?>? parameters = null)
    {
        using var connection = OpenConnection();
        using var command = new NpgsqlCommand(sql, connection);
        AddParameters(command, parameters);

        using var reader = command.ExecuteReader();
        var table = new DataTable();
        table.Load(reader);
        return table;
    }

    public int ExecuteNonQuery(string sql, Dictionary<string, object?>? parameters = null)
    {
        using var connection = OpenConnection();
        using var command = new NpgsqlCommand(sql, connection);
        AddParameters(command, parameters);
        return command.ExecuteNonQuery();
    }

    public int ExecuteScalarInt(string sql, Dictionary<string, object?>? parameters = null)
    {
        using var connection = OpenConnection();
        using var command = new NpgsqlCommand(sql, connection);
        AddParameters(command, parameters);
        var value = command.ExecuteScalar();
        return value is null or DBNull ? 0 : Convert.ToInt32(value);
    }

    public void Audit(string action, string entity, int? entityId, string details)
    {
        ExecuteNonQuery(
            """
            INSERT INTO audit_log(user_login, action, entity, entity_id, details)
            VALUES(@u, @a, @e, @id, @d)
            """,
            new()
            {
                ["@u"] = _session?.Login ?? "system",
                ["@a"] = action,
                ["@e"] = entity,
                ["@id"] = entityId,
                ["@d"] = details
            });
    }

    public static string HashPassword(string password, string saltHex)
    {
        var salt = Convert.FromHexString(saltHex);
        var hash = Rfc2898DeriveBytes.Pbkdf2(password, salt, 120_000, HashAlgorithmName.SHA256, 32);
        return Convert.ToHexString(hash);
    }

    public static string NewSalt()
    {
        Span<byte> salt = stackalloc byte[16];
        RandomNumberGenerator.Fill(salt);
        return Convert.ToHexString(salt);
    }

    private NpgsqlConnection OpenConnection()
    {
        var connection = new NpgsqlConnection(_connectionString);
        connection.Open();
        return connection;
    }

    private static void AddParameters(NpgsqlCommand command, Dictionary<string, object?>? parameters)
    {
        if (parameters is null) return;

        foreach (var (name, value) in parameters)
        {
            var parameterName = name.StartsWith('@') ? name : "@" + name;
            command.Parameters.AddWithValue(parameterName, value ?? DBNull.Value);
        }
    }

    private void Seed()
    {
        if (ExecuteScalarInt("SELECT COUNT(*) FROM brand") == 0)
        {
            ExecuteNonQuery("""
                INSERT INTO brand(name,country) VALUES
                ('Toyota','Япония'),('BMW','Германия'),('Lada','Россия'),('Hyundai','Корея'),
                ('Mercedes-Benz','Германия'),('Kia','Корея'),('Ford','США'),('Skoda','Чехия');

                INSERT INTO car(license_plate,brand_id,year,daily_rate,status) VALUES
                ('А123БВ77',1,2021,3500,'Свободен'),('В456ГД77',2,2020,5500,'В аренде'),
                ('Е789ЖЗ99',3,2019,1800,'Свободен'),('К321ЛМ50',4,2022,2900,'На ТО'),
                ('М001НО77',5,2023,7200,'Свободен'),('Р555СТ77',6,2021,2600,'Свободен'),
                ('У888ФХ50',1,2020,3200,'В аренде'),('Х111ЦЧ77',7,2018,3000,'Свободен'),
                ('Ш222ЩЭ99',8,2022,2400,'На ТО'),('Ю333ЯА77',4,2023,3100,'Свободен');

                INSERT INTO client(full_name,phone,address,license_no) VALUES
                ('Иванов Алексей Петрович','+7(916)123-45-67','Москва, ул. Ленина, 10','77АА123456'),
                ('Смирнова Елена Игоревна','+7(903)987-65-43','Москва, пр. Мира, 55','77ББ654321'),
                ('Козлов Дмитрий Владимирович','+7(925)555-11-22','Мытищи, ул. Садовая, 3','50ВВ112233'),
                ('Новикова Анна Сергеевна','+7(499)321-00-11','Москва, ул. Тверская, 22','77ГГ445566'),
                ('Фёдоров Игорь Николаевич','+7(926)777-88-99','Люберцы, ул. Парковая, 7','50ДД778899'),
                ('Белова Марина Андреевна','+7(915)100-20-30','Москва, ул. Арбат, 15','77ЕЕ001122'),
                ('Тимофеев Сергей Юрьевич','+7(977)333-44-55','Химки, ул. Речная, 21','50ЖЖ334455'),
                ('Орлова Юлия Викторовна','+7(968)666-77-88','Москва, пр. Вернадского, 88','77ЗЗ667788');

                INSERT INTO rental(car_id,client_id,start_date,end_date,actual_return,total_cost,status) VALUES
                (2,1,'2025-04-01','2025-04-07','2025-04-07',33000,'Завершена'),
                (1,2,'2025-04-15','2025-04-20','2025-04-20',17500,'Завершена'),
                (3,3,'2025-04-10','2025-04-15','2025-04-15',9000,'Завершена'),
                (2,4,'2025-05-01','2025-05-07',NULL,38500,'Активна'),
                (7,5,'2025-05-10','2025-05-17',NULL,22400,'Активна'),
                (5,1,'2025-03-20','2025-03-25','2025-03-25',36000,'Завершена'),
                (6,6,'2025-04-22','2025-04-28','2025-04-28',15600,'Завершена'),
                (8,7,'2025-05-12','2025-05-15',NULL,9000,'Активна'),
                (1,8,'2025-02-01','2025-02-05','2025-02-06',17500,'Завершена'),
                (3,2,'2025-05-18','2025-05-23',NULL,9000,'Активна');
                """);
        }

        if (ExecuteScalarInt("SELECT COUNT(*) FROM app_user") == 0)
        {
            CreateUser("admin", "Администратор БД", "admin", "Admin123!");
            CreateUser("manager", "Менеджер проката", "manager", "Manager123!");
            CreateUser("viewer", "Аналитик", "viewer", "Viewer123!");
        }
    }

    private void SyncSequences()
    {
        ExecuteNonQuery("""
            SELECT setval(pg_get_serial_sequence('brand', 'brand_id'), COALESCE((SELECT MAX(brand_id) FROM brand), 1), true)
            WHERE pg_get_serial_sequence('brand', 'brand_id') IS NOT NULL;

            SELECT setval(pg_get_serial_sequence('car', 'car_id'), COALESCE((SELECT MAX(car_id) FROM car), 1), true)
            WHERE pg_get_serial_sequence('car', 'car_id') IS NOT NULL;

            SELECT setval(pg_get_serial_sequence('client', 'client_id'), COALESCE((SELECT MAX(client_id) FROM client), 1), true)
            WHERE pg_get_serial_sequence('client', 'client_id') IS NOT NULL;

            SELECT setval(pg_get_serial_sequence('rental', 'rental_id'), COALESCE((SELECT MAX(rental_id) FROM rental), 1), true)
            WHERE pg_get_serial_sequence('rental', 'rental_id') IS NOT NULL;

            SELECT setval(pg_get_serial_sequence('app_user', 'user_id'), COALESCE((SELECT MAX(user_id) FROM app_user), 1), true)
            WHERE pg_get_serial_sequence('app_user', 'user_id') IS NOT NULL;
            """);
    }

    private void CreateUser(string login, string fullName, string role, string password)
    {
        var salt = NewSalt();
        ExecuteNonQuery(
            """
            INSERT INTO app_user(login, full_name, role, password_hash, password_salt)
            VALUES(@l, @n, @r, @h, @s)
            """,
            new()
            {
                ["@l"] = login,
                ["@n"] = fullName,
                ["@r"] = role,
                ["@h"] = HashPassword(password, salt),
                ["@s"] = salt
            });
    }

    public void Dispose()
    {
    }

    private const string SchemaSql = """
        SET search_path TO garanin_gi, public;

        CREATE TABLE IF NOT EXISTS app_context(
          key TEXT PRIMARY KEY,
          value TEXT NOT NULL
        );
        INSERT INTO app_context(key,value) VALUES('current_user','system')
        ON CONFLICT(key) DO NOTHING;

        CREATE TABLE IF NOT EXISTS brand(
          brand_id SERIAL PRIMARY KEY,
          name VARCHAR(50) NOT NULL UNIQUE,
          country VARCHAR(50)
        );

        CREATE TABLE IF NOT EXISTS car(
          car_id SERIAL PRIMARY KEY,
          license_plate VARCHAR(15) NOT NULL UNIQUE,
          brand_id INT NOT NULL REFERENCES brand(brand_id) ON UPDATE CASCADE ON DELETE RESTRICT,
          year SMALLINT CHECK(year BETWEEN 1990 AND 2030),
          daily_rate NUMERIC(10,2) NOT NULL CHECK(daily_rate > 0),
          status VARCHAR(20) NOT NULL DEFAULT 'Свободен' CHECK(status IN ('Свободен','В аренде','На ТО'))
        );

        CREATE TABLE IF NOT EXISTS client(
          client_id SERIAL PRIMARY KEY,
          full_name VARCHAR(100) NOT NULL,
          phone VARCHAR(20) UNIQUE,
          address TEXT,
          license_no VARCHAR(20) UNIQUE,
          created_at TIMESTAMPTZ NOT NULL DEFAULT now()
        );

        CREATE TABLE IF NOT EXISTS rental(
          rental_id SERIAL PRIMARY KEY,
          car_id INT NOT NULL REFERENCES car(car_id) ON UPDATE CASCADE ON DELETE RESTRICT,
          client_id INT NOT NULL REFERENCES client(client_id) ON UPDATE CASCADE ON DELETE RESTRICT,
          start_date DATE NOT NULL,
          end_date DATE NOT NULL CHECK(end_date >= start_date),
          actual_return DATE,
          total_cost NUMERIC(10,2) CHECK(total_cost >= 0),
          status VARCHAR(20) NOT NULL DEFAULT 'Активна' CHECK(status IN ('Активна','Завершена'))
        );

        CREATE INDEX IF NOT EXISTS idx_car_brand_id ON car(brand_id);
        CREATE INDEX IF NOT EXISTS idx_rental_car_id ON rental(car_id);
        CREATE INDEX IF NOT EXISTS idx_rental_client_id ON rental(client_id);
        CREATE INDEX IF NOT EXISTS idx_rental_dates ON rental(start_date, end_date);
        CREATE INDEX IF NOT EXISTS idx_rental_status ON rental(status);

        CREATE TABLE IF NOT EXISTS app_user(
          user_id SERIAL PRIMARY KEY,
          login VARCHAR(50) NOT NULL UNIQUE,
          full_name VARCHAR(100) NOT NULL,
          role VARCHAR(20) NOT NULL CHECK(role IN ('admin','manager','viewer')),
          password_hash TEXT NOT NULL,
          password_salt TEXT NOT NULL,
          is_active BOOLEAN NOT NULL DEFAULT true
        );

        CREATE TABLE IF NOT EXISTS audit_log(
          audit_id BIGSERIAL PRIMARY KEY,
          changed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
          user_login TEXT NOT NULL DEFAULT 'system',
          action TEXT NOT NULL,
          entity TEXT NOT NULL,
          entity_id INT,
          details TEXT
        );

        CREATE OR REPLACE VIEW v_rentals_full AS
          SELECT r.rental_id, c.full_name AS client_name, c.phone AS client_phone,
                 ca.license_plate, b.name AS brand_name, r.start_date, r.end_date,
                 r.actual_return, r.total_cost, r.status
          FROM rental r
          JOIN client c ON c.client_id = r.client_id
          JOIN car ca ON ca.car_id = r.car_id
          JOIN brand b ON b.brand_id = ca.brand_id;

        CREATE OR REPLACE VIEW v_available_cars AS
          SELECT ca.car_id, ca.license_plate, b.name AS brand, b.country, ca.year, ca.daily_rate
          FROM car ca JOIN brand b ON b.brand_id = ca.brand_id
          WHERE ca.status = 'Свободен';

        CREATE OR REPLACE VIEW v_client_stats AS
          SELECT c.client_id, c.full_name, COUNT(r.rental_id) AS total_rentals,
                 COALESCE(SUM(r.total_cost), 0) AS total_spent
          FROM client c LEFT JOIN rental r ON r.client_id = c.client_id
          GROUP BY c.client_id, c.full_name;

        CREATE OR REPLACE FUNCTION fn_autorent_current_user()
        RETURNS TEXT
        LANGUAGE sql
        AS $$
          SELECT COALESCE((SELECT value FROM app_context WHERE key = 'current_user'), current_user::text)
        $$;

        CREATE OR REPLACE FUNCTION fn_autorent_audit()
        RETURNS TRIGGER
        LANGUAGE plpgsql
        AS $$
        DECLARE
          pk_name TEXT := TG_ARGV[0];
          row_id INT;
          info TEXT;
        BEGIN
          IF TG_OP = 'INSERT' THEN
            row_id := (to_jsonb(NEW)->>pk_name)::INT;
            info := to_jsonb(NEW)::TEXT;
            INSERT INTO audit_log(user_login, action, entity, entity_id, details)
            VALUES(fn_autorent_current_user(), TG_OP, TG_TABLE_NAME, row_id, info);
            RETURN NEW;
          ELSIF TG_OP = 'UPDATE' THEN
            row_id := (to_jsonb(NEW)->>pk_name)::INT;
            info := jsonb_build_object('old', to_jsonb(OLD), 'new', to_jsonb(NEW))::TEXT;
            INSERT INTO audit_log(user_login, action, entity, entity_id, details)
            VALUES(fn_autorent_current_user(), TG_OP, TG_TABLE_NAME, row_id, info);
            RETURN NEW;
          ELSE
            row_id := (to_jsonb(OLD)->>pk_name)::INT;
            info := to_jsonb(OLD)::TEXT;
            INSERT INTO audit_log(user_login, action, entity, entity_id, details)
            VALUES(fn_autorent_current_user(), TG_OP, TG_TABLE_NAME, row_id, info);
            RETURN OLD;
          END IF;
        END;
        $$;

        DROP TRIGGER IF EXISTS trg_audit_brand ON brand;
        CREATE TRIGGER trg_audit_brand AFTER INSERT OR UPDATE OR DELETE ON brand
        FOR EACH ROW EXECUTE FUNCTION fn_autorent_audit('brand_id');

        DROP TRIGGER IF EXISTS trg_audit_car ON car;
        CREATE TRIGGER trg_audit_car AFTER INSERT OR UPDATE OR DELETE ON car
        FOR EACH ROW EXECUTE FUNCTION fn_autorent_audit('car_id');

        DROP TRIGGER IF EXISTS trg_audit_client ON client;
        CREATE TRIGGER trg_audit_client AFTER INSERT OR UPDATE OR DELETE ON client
        FOR EACH ROW EXECUTE FUNCTION fn_autorent_audit('client_id');

        DROP TRIGGER IF EXISTS trg_audit_rental ON rental;
        CREATE TRIGGER trg_audit_rental AFTER INSERT OR UPDATE OR DELETE ON rental
        FOR EACH ROW EXECUTE FUNCTION fn_autorent_audit('rental_id');
        """;
}