using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;

namespace Учет_вещей
{
    public static class DbManager
    {
        private const string DbFileName = "inventory.db";
        private static readonly string ConnectionString = $"Data Source={DbFileName};Version=3;";

        // Инициализация базы данных
        public static void InitializeDatabase()
        {
            // Проверяем существование файла базы данных
            bool isNewDatabase = !File.Exists(DbFileName);

            if (isNewDatabase)
            {
                // Создаем базу данных и необходимые таблицы
                SQLiteConnection.CreateFile(DbFileName);

                using (var connection = new SQLiteConnection(ConnectionString))
                {
                    connection.Open();

                    // Создаем таблицы
                    string createUsersTable = @"
                        CREATE TABLE Users (
                            Username TEXT PRIMARY KEY,
                            Password TEXT NOT NULL,
                            Role INTEGER NOT NULL
                        )";

                    string createDevicesTable = @"
                        CREATE TABLE Devices (
                            Id INTEGER PRIMARY KEY AUTOINCREMENT,
                            Name TEXT NOT NULL,
                            InventoryNumber TEXT NOT NULL,
                            Type INTEGER NOT NULL,
                            Location TEXT,
                            ResponsiblePerson TEXT,
                            State INTEGER NOT NULL,
                            RegistrationDate TEXT NOT NULL
                        )";

                    using (var command = new SQLiteCommand(createUsersTable, connection))
                    {
                        command.ExecuteNonQuery();
                    }

                    using (var command = new SQLiteCommand(createDevicesTable, connection))
                    {
                        command.ExecuteNonQuery();
                    }

                    // Добавляем предустановленных пользователей
                    string insertDefaultUsers = @"
                        INSERT INTO Users (Username, Password, Role) VALUES
                        ('admin', 'admin', 1),
                        ('user', 'user', 0)";

                    using (var command = new SQLiteCommand(insertDefaultUsers, connection))
                    {
                        command.ExecuteNonQuery();
                    }
                }
            }
        }

        #region Пользователи

        // Авторизация пользователя
        public static User Authenticate(string username, string password)
        {
            using (var connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();
                string query = "SELECT Username, Password, Role FROM Users WHERE Username = @Username AND Password = @Password";

                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Username", username);
                    command.Parameters.AddWithValue("@Password", password);

                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return new User
                            {
                                Username = reader["Username"].ToString(),
                                Password = reader["Password"].ToString(),
                                Role = (UserRole)Convert.ToInt32(reader["Role"])
                            };
                        }
                    }
                }
            }

            return null;
        }

        #endregion

        #region Устройства

        // Получение всех устройств
        public static List<Device> GetAllDevices()
        {
            List<Device> devices = new List<Device>();

            using (var connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();
                string query = "SELECT * FROM Devices";

                using (var command = new SQLiteCommand(query, connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            devices.Add(ReadDeviceFromDb(reader));
                        }
                    }
                }
            }

            return devices;
        }

        // Добавление нового устройства
        public static void AddDevice(Device device)
        {
            using (var connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();
                string query = @"
                    INSERT INTO Devices (Name, InventoryNumber, Type, Location, ResponsiblePerson, State, RegistrationDate) 
                    VALUES (@Name, @InventoryNumber, @Type, @Location, @ResponsiblePerson, @State, @RegistrationDate);
                    SELECT last_insert_rowid();";

                using (var command = new SQLiteCommand(query, connection))
                {
                    SetDeviceParameters(command, device);
                    device.Id = Convert.ToInt32(command.ExecuteScalar());
                }
            }
        }

        // Обновление существующего устройства
        public static void UpdateDevice(Device device)
        {
            using (var connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();
                string query = @"
                    UPDATE Devices 
                    SET Name = @Name, 
                        InventoryNumber = @InventoryNumber, 
                        Type = @Type, 
                        Location = @Location, 
                        ResponsiblePerson = @ResponsiblePerson, 
                        State = @State, 
                        RegistrationDate = @RegistrationDate 
                    WHERE Id = @Id";

                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Id", device.Id);
                    SetDeviceParameters(command, device);
                    command.ExecuteNonQuery();
                }
            }
        }

        // Удаление устройства
        public static void DeleteDevice(int id)
        {
            using (var connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();
                string query = "DELETE FROM Devices WHERE Id = @Id";

                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Id", id);
                    command.ExecuteNonQuery();
                }
            }
        }

        // Фильтрация устройств
        public static List<Device> FilterDevices(DeviceType? type = null, DeviceState? state = null,
            string location = null, string searchText = null)
        {
            List<Device> devices = new List<Device>();
            StringBuilder queryBuilder = new StringBuilder("SELECT * FROM Devices WHERE 1=1");
            Dictionary<string, object> parameters = new Dictionary<string, object>();

            if (type.HasValue)
            {
                queryBuilder.Append(" AND Type = @Type");
                parameters.Add("@Type", (int)type.Value);
            }

            if (state.HasValue)
            {
                queryBuilder.Append(" AND State = @State");
                parameters.Add("@State", (int)state.Value);
            }

            if (!string.IsNullOrEmpty(location))
            {
                queryBuilder.Append(" AND Location LIKE @Location");
                parameters.Add("@Location", "%" + location + "%");
            }

            if (!string.IsNullOrEmpty(searchText))
            {
                queryBuilder.Append(" AND (Name LIKE @SearchText OR InventoryNumber LIKE @SearchText)");
                parameters.Add("@SearchText", "%" + searchText.ToLower() + "%");
            }

            using (var connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                using (var command = new SQLiteCommand(queryBuilder.ToString(), connection))
                {
                    foreach (var param in parameters)
                    {
                        command.Parameters.AddWithValue(param.Key, param.Value);
                    }

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            devices.Add(ReadDeviceFromDb(reader));
                        }
                    }
                }
            }

            return devices;
        }

        // Статистика по типам устройств
        public static Dictionary<DeviceType, int> GetDeviceTypeStats()
        {
            var result = new Dictionary<DeviceType, int>();

            using (var connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();
                string query = "SELECT Type, COUNT(*) as Count FROM Devices GROUP BY Type";

                using (var command = new SQLiteCommand(query, connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            DeviceType type = (DeviceType)Convert.ToInt32(reader["Type"]);
                            int count = Convert.ToInt32(reader["Count"]);
                            result[type] = count;
                        }
                    }
                }
            }

            return result;
        }

        // Экспорт данных в CSV
        public static void ExportToCsv(string filePath)
        {
            var devices = GetAllDevices();

            var lines = new List<string>
            {
                "ID;Наименование;Инвентарный номер;Тип;Местоположение;Ответственное лицо;Состояние;Дата постановки на учёт"
            };

            foreach (var device in devices)
            {
                lines.Add($"{device.Id};{device.Name};{device.InventoryNumber};{device.TypeString};" +
                    $"{device.Location};{device.ResponsiblePerson};{device.StateString};{device.RegistrationDate:dd.MM.yyyy}");
            }

            File.WriteAllLines(filePath, lines, Encoding.UTF8);
        }

        #endregion

        #region Вспомогательные методы

        // Чтение устройства из базы данных
        private static Device ReadDeviceFromDb(SQLiteDataReader reader)
        {
            return new Device
            {
                Id = Convert.ToInt32(reader["Id"]),
                Name = reader["Name"].ToString(),
                InventoryNumber = reader["InventoryNumber"].ToString(),
                Type = (DeviceType)Convert.ToInt32(reader["Type"]),
                Location = reader["Location"].ToString(),
                ResponsiblePerson = reader["ResponsiblePerson"].ToString(),
                State = (DeviceState)Convert.ToInt32(reader["State"]),
                RegistrationDate = Convert.ToDateTime(reader["RegistrationDate"])
            };
        }

        // Установка параметров устройства для команды SQLite
        private static void SetDeviceParameters(SQLiteCommand command, Device device)
        {
            command.Parameters.AddWithValue("@Name", device.Name);
            command.Parameters.AddWithValue("@InventoryNumber", device.InventoryNumber);
            command.Parameters.AddWithValue("@Type", (int)device.Type);
            command.Parameters.AddWithValue("@Location", device.Location ?? string.Empty);
            command.Parameters.AddWithValue("@ResponsiblePerson", device.ResponsiblePerson ?? string.Empty);
            command.Parameters.AddWithValue("@State", (int)device.State);
            command.Parameters.AddWithValue("@RegistrationDate", device.RegistrationDate.ToString("yyyy-MM-dd HH:mm:ss"));
        }

        #endregion
    }
}