Zimogorov_Arseniy_OrdersManager/DatabaseHelper.cs

using System;
using System.Data;
using System.Security.Cryptography;
using System.Text;
using Npgsql;
using System.Configuration;
using System.Windows.Forms;

namespace WindowsFormsApp4
{
    public static class DatabaseHelper
    {
        private static string connectionString = ConfigurationManager.ConnectionStrings["PostgreSQL"].ConnectionString;

        public static NpgsqlConnection GetConnection()
        {
            return new NpgsqlConnection(connectionString);
        }

        public static bool TestConnection()
        {
            try
            {
                using (var conn = GetConnection())
                {
                    conn.Open();
                    return true;
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine($"Ошибка подключения: {ex.Message}");
                return false;
            }
        }

        public static string HashPassword(string password)
        {
            using (SHA256 sha256 = SHA256.Create())
            {
                byte[] bytes = sha256.ComputeHash(Encoding.UTF8.GetBytes(password));
                StringBuilder builder = new StringBuilder();
                foreach (byte b in bytes)
                    builder.Append(b.ToString("x2"));
                return builder.ToString();
            }
        }

        public class User
        {
            public int UserId { get; set; }
            public string LastName { get; set; }
            public string FirstName { get; set; }
            public string MiddleName { get; set; }
            public string Phone { get; set; }
            public string Role { get; set; }
            public bool IsActive { get; set; }
            public int? ClientId { get; set; }
            public int? ManagerId { get; set; }
        }

        public static User Authenticate(string login, string password)
        {
            string passwordHash = HashPassword(password);

            using (var conn = GetConnection())
            {
                conn.Open();
                string sql = @"
                    SELECT u.user_id, u.last_name, u.first_name, u.middle_name, 
                           u.phone, u.role_name, u.is_active, u.client_id, u.manager_id
                    FROM users u
                    JOIN user_auth a ON u.user_id = a.user_id
                    WHERE a.login = @login AND a.password_hash = @password_hash AND u.is_active = true";

                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@login", login);
                    cmd.Parameters.AddWithValue("@password_hash", passwordHash);

                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return new User
                            {
                                UserId = reader.GetInt32(0),
                                LastName = reader.GetString(1),
                                FirstName = reader.GetString(2),
                                MiddleName = reader.IsDBNull(3) ? null : reader.GetString(3),
                                Phone = reader.GetString(4),
                                Role = reader.GetString(5),
                                IsActive = reader.GetBoolean(6),
                                ClientId = reader.IsDBNull(7) ? null : (int?)reader.GetInt32(7),
                                ManagerId = reader.IsDBNull(8) ? null : (int?)reader.GetInt32(8)
                            };
                        }
                    }
                }
            }
            return null;
        }

        public static DataTable GetRequests(int userId, string role, int? clientId = null, int? managerId = null)
        {
            DataTable dt = new DataTable();
            string sql;

            if (role == "admin")
            {
                sql = @"
            SELECT r.id, c.last_name || ' ' || c.first_name AS client_name,
                   COALESCE(m.last_name || ' ' || m.first_name, 'Не назначен') AS manager_name,
                   r.request_date, r.measurement_datetime, r.status
            FROM request r
            LEFT JOIN client c ON r.client_id = c.client_id
            LEFT JOIN manager m ON r.manager_id = m.id
            ORDER BY r.request_date DESC";
            }
            else if (role == "manager" && managerId.HasValue)
            {
                sql = @"
        SELECT r.id, c.last_name || ' ' || c.first_name AS client_name,
               r.request_date, r.measurement_datetime, r.status
        FROM request r
        JOIN client c ON r.client_id = c.client_id
        WHERE r.status = 'new' OR r.manager_id = @managerId
        ORDER BY r.request_date DESC";
            }
            else if (role == "client" && clientId.HasValue)
            {
                sql = @"
            SELECT r.id, r.request_date, r.measurement_datetime, r.status
            FROM request r
            WHERE r.client_id = @clientId
            ORDER BY r.request_date DESC";
            }
            else
            {
                return dt;
            }

            using (var conn = GetConnection())
            {
                conn.Open();
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    if (role == "manager" && managerId.HasValue)
                        cmd.Parameters.AddWithValue("@managerId", managerId.Value);
                    if (role == "client" && clientId.HasValue)
                        cmd.Parameters.AddWithValue("@clientId", clientId.Value);

                    using (var adapter = new NpgsqlDataAdapter(cmd))
                    {
                        adapter.Fill(dt);
                    }
                }
            }
            return dt;
        }
        public static bool UpdateRequestStatus(int requestId, string newStatus)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                string sql = "UPDATE request SET status = @status WHERE id = @id";  
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@status", newStatus);
                    cmd.Parameters.AddWithValue("@id", requestId);
                    return cmd.ExecuteNonQuery() > 0;
                }
            }
        }

        public static DataTable GetClients()
        {
            DataTable dt = new DataTable();
            string sql = "SELECT client_id, last_name, first_name, middle_name, phone FROM client ORDER BY last_name";

            using (var conn = GetConnection())
            {
                conn.Open();
                using (var adapter = new NpgsqlDataAdapter(sql, conn))
                {
                    adapter.Fill(dt);
                }
            }
            return dt;
        }

        public static DataTable GetManagers()
        {
            DataTable dt = new DataTable();
            string sql = "SELECT id AS manager_id, last_name, first_name, phone FROM manager ORDER BY last_name";

            using (var conn = GetConnection())
            {
                conn.Open();
                using (var adapter = new NpgsqlDataAdapter(sql, conn))
                {
                    adapter.Fill(dt);
                }
            }
            return dt;
        }

        public static DataTable GetManagerStatistics()
        {
            DataTable dt = new DataTable();
            string sql = @"SELECT * FROM zimogorov_aa.manager_statistics ORDER BY completed_requests DESC";

            using (var conn = GetConnection())
            {
                conn.Open();
                using (var adapter = new NpgsqlDataAdapter(sql, conn))
                {
                    adapter.Fill(dt);
                }
            }
            return dt;
        }

        public static DataTable GetManagerPerformance(DateTime startDate, DateTime endDate)
        {
            DataTable dt = new DataTable();
            string sql = @"SELECT * FROM zimogorov_aa.get_manager_performance(@start_date::date, @end_date::date)";

            using (var conn = GetConnection())
            {
                conn.Open();
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@start_date", startDate);
                    cmd.Parameters.AddWithValue("@end_date", endDate);
                    using (var adapter = new NpgsqlDataAdapter(cmd))
                    {
                        adapter.Fill(dt);
                    }
                }
            }
            return dt;
        }

        public static int AddClient(string lastName, string firstName, string middleName, string phone)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                string sql = @"
            INSERT INTO client (last_name, first_name, middle_name, phone) 
            VALUES (@last_name, @first_name, @middle_name, @phone)
            RETURNING client_id";
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@last_name", lastName);
                    cmd.Parameters.AddWithValue("@first_name", firstName);
                    cmd.Parameters.AddWithValue("@middle_name", string.IsNullOrEmpty(middleName) ? (object)DBNull.Value : middleName);
                    cmd.Parameters.AddWithValue("@phone", phone);
                    return (int)cmd.ExecuteScalar();
                }
            }
        }

        public static int AddManager(string lastName, string firstName, string middleName, string phone)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                string sql = @"
            INSERT INTO manager (last_name, first_name, middle_name, phone) 
            VALUES (@last_name, @first_name, @middle_name, @phone)
            RETURNING id";
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@last_name", lastName);
                    cmd.Parameters.AddWithValue("@first_name", firstName);
                    cmd.Parameters.AddWithValue("@middle_name", string.IsNullOrEmpty(middleName) ? (object)DBNull.Value : middleName);
                    cmd.Parameters.AddWithValue("@phone", phone);
                    return (int)cmd.ExecuteScalar();  
                }
            }
        }

        public static bool DeleteClient(int clientId)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                string sql = "DELETE FROM client WHERE client_id = @client_id";
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@client_id", clientId);
                    return cmd.ExecuteNonQuery() > 0;
                }
            }
        }

        public static bool DeleteManager(int managerId)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                string sql = "DELETE FROM manager WHERE id = @id";
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@id", managerId);
                    return cmd.ExecuteNonQuery() > 0;
                }
            }
        }
        public static bool LoginExists(string login)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                string sql = "SELECT COUNT(*) FROM user_auth WHERE login = @login";
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@login", login);
                    long count = (long)cmd.ExecuteScalar();
                    return count > 0;
                }
            }
        }

        public static bool PhoneExists(string phone)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                string sql = "SELECT COUNT(*) FROM client WHERE phone = @phone";
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@phone", phone);
                    long count = (long)cmd.ExecuteScalar();
                    return count > 0;
                }
            }
        }

        public static int RegisterClient(string lastName, string firstName, string middleName, string phone)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                string sql = @"
            INSERT INTO client (last_name, first_name, middle_name, phone) 
            VALUES (@last_name, @first_name, @middle_name, @phone)
            RETURNING client_id";
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@last_name", lastName);
                    cmd.Parameters.AddWithValue("@first_name", firstName);
                    cmd.Parameters.AddWithValue("@middle_name", string.IsNullOrEmpty(middleName) ? (object)DBNull.Value : middleName);
                    cmd.Parameters.AddWithValue("@phone", phone);
                    return (int)cmd.ExecuteScalar();
                }
            }
        }

        public static int CreateUser(string lastName, string firstName, string middleName, string phone, string role, int? clientId, int? managerId)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                string sql = @"
            INSERT INTO users (last_name, first_name, middle_name, phone, role_name, client_id, manager_id, created_at, is_active) 
            VALUES (@last_name, @first_name, @middle_name, @phone, @role::user_role, @client_id, @manager_id, CURRENT_DATE, true)
            RETURNING user_id";
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@last_name", lastName);
                    cmd.Parameters.AddWithValue("@first_name", firstName);
                    cmd.Parameters.AddWithValue("@middle_name", string.IsNullOrEmpty(middleName) ? (object)DBNull.Value : middleName);
                    cmd.Parameters.AddWithValue("@phone", phone);
                    cmd.Parameters.AddWithValue("@role", role);
                    cmd.Parameters.AddWithValue("@client_id", clientId.HasValue ? (object)clientId.Value : DBNull.Value);
                    cmd.Parameters.AddWithValue("@manager_id", managerId.HasValue ? (object)managerId.Value : DBNull.Value);
                    return (int)cmd.ExecuteScalar();
                }
            }
        }

        public static bool CreateUserAuth(int userId, string login, string password)
        {
            string passwordHash = HashPassword(password);
            using (var conn = GetConnection())
            {
                conn.Open();
                string sql = @"
            INSERT INTO user_auth (user_id, login, password_hash, created_at, is_current) 
            VALUES (@user_id, @login, @password_hash, CURRENT_TIMESTAMP, true)";
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@user_id", userId);
                    cmd.Parameters.AddWithValue("@login", login);
                    cmd.Parameters.AddWithValue("@password_hash", passwordHash);
                    return cmd.ExecuteNonQuery() > 0;
                }
            }
        }
        public static bool UpdateClient(int clientId, string lastName, string firstName, string middleName, string phone)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                string sql = @"
            UPDATE client 
            SET last_name = @last_name, 
                first_name = @first_name, 
                middle_name = @middle_name, 
                phone = @phone 
            WHERE client_id = @client_id";
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@client_id", clientId);
                    cmd.Parameters.AddWithValue("@last_name", lastName);
                    cmd.Parameters.AddWithValue("@first_name", firstName);
                    cmd.Parameters.AddWithValue("@middle_name", string.IsNullOrEmpty(middleName) ? (object)DBNull.Value : middleName);
                    cmd.Parameters.AddWithValue("@phone", phone);
                    return cmd.ExecuteNonQuery() > 0;
                }
            }
        }


        public static bool UpdateManager(int managerId, string lastName, string firstName, string middleName, string phone)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                string sql = @"
            UPDATE manager 
            SET last_name = @last_name, 
                first_name = @first_name, 
                middle_name = @middle_name, 
                phone = @phone 
            WHERE id = @id";
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@id", managerId);
                    cmd.Parameters.AddWithValue("@last_name", lastName);
                    cmd.Parameters.AddWithValue("@first_name", firstName);
                    cmd.Parameters.AddWithValue("@middle_name", string.IsNullOrEmpty(middleName) ? (object)DBNull.Value : middleName);
                    cmd.Parameters.AddWithValue("@phone", phone);
                    return cmd.ExecuteNonQuery() > 0;
                }
            }
        }

        public static int GetManagerActiveRequests(int managerId)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                string sql = "SELECT COUNT(*) FROM request WHERE manager_id = @manager_id AND status IN ('new', 'in_progress')";
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@manager_id", managerId);
                    return Convert.ToInt32(cmd.ExecuteScalar());
                }
            }
        }

        public static DataTable GetClientAddresses(int clientId)
        {
            DataTable dt = new DataTable();
            string sql = @"
        SELECT a.id AS address_id, 
               a.city || ', ' || a.street || ', ' || a.house || 
               CASE WHEN a.apartment IS NOT NULL AND a.apartment != '' 
                    THEN ', кв. ' || a.apartment 
                    ELSE '' END AS address_text
        FROM client_address ca
        JOIN address a ON ca.address_id = a.id
        WHERE ca.client_id = @client_id
        ORDER BY ca.priority ASC";

            using (var conn = GetConnection())
            {
                conn.Open();
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@client_id", clientId);
                    using (var adapter = new NpgsqlDataAdapter(cmd))
                    {
                        adapter.Fill(dt);
                    }
                }
            }
            return dt;
        }

        public static int CreateRequest(int clientId, DateTime measurementDateTime)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                string sql = @"
            INSERT INTO request (client_id, request_date, measurement_datetime, status) 
            VALUES (@client_id, CURRENT_DATE, @measurement_datetime, 'new')
            RETURNING id";
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@client_id", clientId);
                    cmd.Parameters.AddWithValue("@measurement_datetime", measurementDateTime);
                    return (int)cmd.ExecuteScalar();
                }
            }
        }

        public static DataTable GetClientAddressesWithDetails(int clientId)
        {
            DataTable dt = new DataTable();
            string sql = @"
        SELECT a.id AS address_id, a.city, a.street, a.house, a.building, a.apartment,
               ca.address_type, ca.priority
        FROM client_address ca
        JOIN address a ON ca.address_id = a.id
        WHERE ca.client_id = @client_id
        ORDER BY ca.priority ASC";

            using (var conn = GetConnection())
            {
                conn.Open();
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@client_id", clientId);
                    using (var adapter = new NpgsqlDataAdapter(cmd))
                    {
                        adapter.Fill(dt);
                    }
                }
            }
            return dt;
        }
        public static int AddAddress(string city, string street, string house, string building, string apartment)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                string sql = @"
            INSERT INTO address (city, street, house, building, apartment) 
            VALUES (@city, @street, @house, @building, @apartment)
            RETURNING id";
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@city", city);
                    cmd.Parameters.AddWithValue("@street", street);
                    cmd.Parameters.AddWithValue("@house", house);
                    cmd.Parameters.AddWithValue("@building", string.IsNullOrEmpty(building) ? (object)DBNull.Value : building);
                    cmd.Parameters.AddWithValue("@apartment", string.IsNullOrEmpty(apartment) ? (object)DBNull.Value : apartment);
                    return (int)cmd.ExecuteScalar();
                }
            }
        }

        public static bool UpdateAddress(int addressId, string city, string street, string house, string building, string apartment)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                string sql = @"
            UPDATE address 
            SET city = @city, street = @street, house = @house, 
                building = @building, apartment = @apartment 
            WHERE id = @id";
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@id", addressId);
                    cmd.Parameters.AddWithValue("@city", city);
                    cmd.Parameters.AddWithValue("@street", street);
                    cmd.Parameters.AddWithValue("@house", house);
                    cmd.Parameters.AddWithValue("@building", string.IsNullOrEmpty(building) ? (object)DBNull.Value : building);
                    cmd.Parameters.AddWithValue("@apartment", string.IsNullOrEmpty(apartment) ? (object)DBNull.Value : apartment);
                    return cmd.ExecuteNonQuery() > 0;
                }
            }
        }

        public static bool AddClientAddress(int clientId, int addressId, string addressType, int priority)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                string sql = @"
            INSERT INTO client_address (client_id, address_id, address_type, priority) 
            VALUES (@client_id, @address_id, @address_type, @priority)";
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@client_id", clientId);
                    cmd.Parameters.AddWithValue("@address_id", addressId);
                    cmd.Parameters.AddWithValue("@address_type", addressType);
                    cmd.Parameters.AddWithValue("@priority", priority);
                    return cmd.ExecuteNonQuery() > 0;
                }
            }
        }

        public static bool DeleteClientAddress(int clientId, int addressId)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                string sql = "DELETE FROM client_address WHERE client_id = @client_id AND address_id = @address_id";
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@client_id", clientId);
                    cmd.Parameters.AddWithValue("@address_id", addressId);
                    return cmd.ExecuteNonQuery() > 0;
                }
            }
        }
        public static bool SetMainAddress(int clientId, int addressId)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                using (var trans = conn.BeginTransaction())
                {
                    string sqlReset = "UPDATE client_address SET priority = 10 WHERE client_id = @client_id";
                    using (var cmdReset = new NpgsqlCommand(sqlReset, conn, trans))
                    {
                        cmdReset.Parameters.AddWithValue("@client_id", clientId);
                        cmdReset.ExecuteNonQuery();
                    }

                    string sqlSet = "UPDATE client_address SET priority = 1 WHERE client_id = @client_id AND address_id = @address_id";
                    using (var cmdSet = new NpgsqlCommand(sqlSet, conn, trans))
                    {
                        cmdSet.Parameters.AddWithValue("@client_id", clientId);
                        cmdSet.Parameters.AddWithValue("@address_id", addressId);
                        cmdSet.ExecuteNonQuery();
                    }

                    trans.Commit();
                    return true;
                }
            }
        }
        public static bool AssignManagerToRequest(int requestId, int managerId)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                string sql = "UPDATE request SET manager_id = @manager_id WHERE id = @request_id";
                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@request_id", requestId);
                    cmd.Parameters.AddWithValue("@manager_id", managerId);
                    return cmd.ExecuteNonQuery() > 0;
                }
            }
        }
    }
}