---

using System;
using System.Data;
using System.Data.SqlClient;

namespace bred
{
    public static class DatabaseManager
    {
        private static readonly string MasterConnectionString = @"Server=(localdb)\MSSQLLocalDB;Integrated Security=true;";
        public static readonly string LiriDBConnectionString = @"Server=(localdb)\MSSQLLocalDB;Integrated Security=true;Database=LiriDB;";

        public static void InitializeDatabase()
        {
            CreateDatabaseIfNotExists("LiriDB");
            CreateTablesAndFillData();
        }

        private static void CreateDatabaseIfNotExists(string dbName)
        {
            using (SqlConnection connection = new SqlConnection(MasterConnectionString))
            {
                connection.Open();
                string checkDbQuery = $"SELECT database_id FROM sys.databases WHERE Name = '{dbName}'";
                using (SqlCommand cmd = new SqlCommand(checkDbQuery, connection))
                {
                    object result = cmd.ExecuteScalar();
                    if (result == null)
                    {
                        string createDbQuery = $"CREATE DATABASE [{dbName}]";
                        using (SqlCommand createCmd = new SqlCommand(createDbQuery, connection))
                            createCmd.ExecuteNonQuery();
                    }
                }
            }
        }

        private static void CreateTablesAndFillData()
        {
            using (SqlConnection conn = new SqlConnection(LiriDBConnectionString))
            {
                conn.Open();

                // Таблица Employees
                string createEmployees = @"
                    IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Employees' AND xtype='U')
                    CREATE TABLE Employees (
                        ID INT IDENTITY(1,1) PRIMARY KEY,
                        Name NVARCHAR(50) NOT NULL,
                        Position NVARCHAR(50) NOT NULL,
                        Salary DECIMAL(18,2) NOT NULL,
                        City NVARCHAR(50) NOT NULL
                    )";
                using (SqlCommand cmd = new SqlCommand(createEmployees, conn))
                    cmd.ExecuteNonQuery();

                // Таблица Products
                string createProducts = @"
                    IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Products' AND xtype='U')
                    CREATE TABLE Products (
                        ID INT IDENTITY(1,1) PRIMARY KEY,
                        ProductName NVARCHAR(50) NOT NULL,
                        Price DECIMAL(18,2) NOT NULL,
                        Quantity INT NOT NULL,
                        Category NVARCHAR(50) NOT NULL
                    )";
                using (SqlCommand cmd = new SqlCommand(createProducts, conn))
                    cmd.ExecuteNonQuery();

                // Таблица Books
                string createBooks = @"
                    IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Books' AND xtype='U')
                    CREATE TABLE Books (
                        ID INT IDENTITY(1,1) PRIMARY KEY,
                        Title NVARCHAR(100) NOT NULL,
                        Author NVARCHAR(50) NOT NULL,
                        Year INT NOT NULL,
                        Pages INT NOT NULL
                    )";
                using (SqlCommand cmd = new SqlCommand(createBooks, conn))
                    cmd.ExecuteNonQuery();

                // Таблица Customers
                string createCustomers = @"
                    IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Customers' AND xtype='U')
                    CREATE TABLE Customers (
                        ID INT IDENTITY(1,1) PRIMARY KEY,
                        CustomerName NVARCHAR(50) NOT NULL,
                        Address NVARCHAR(100) NOT NULL,
                        Phone NVARCHAR(20) NOT NULL,
                        Email NVARCHAR(50) NOT NULL
                    )";
                using (SqlCommand cmd = new SqlCommand(createCustomers, conn))
                    cmd.ExecuteNonQuery();

                // Полная перезапись данных (очищаем и заливаем заново)
                using (SqlCommand clear = new SqlCommand("DELETE FROM Employees; DELETE FROM Products; DELETE FROM Books; DELETE FROM Customers;", conn))
                    clear.ExecuteNonQuery();

                // Заполнение Employees
                for (int i = 1; i <= 10; i++)
                {
                    string insert = $"INSERT INTO Employees (Name, Position, Salary, City) VALUES (N'Сотрудник{i}', N'Должность{i}', {1000 + i * 100}, N'Город{i}')";
                    using (SqlCommand ins = new SqlCommand(insert, conn))
                        ins.ExecuteNonQuery();
                }

                // Заполнение Products
                for (int i = 1; i <= 10; i++)
                {
                    string insert = $"INSERT INTO Products (ProductName, Price, Quantity, Category) VALUES (N'Товар{i}', {i * 100}, {i * 5}, N'Категория{i}')";
                    using (SqlCommand ins = new SqlCommand(insert, conn))
                        ins.ExecuteNonQuery();
                }

                // Заполнение Books
                for (int i = 1; i <= 10; i++)
                {
                    string insert = $"INSERT INTO Books (Title, Author, Year, Pages) VALUES (N'Книга{i}', N'Автор{i}', {2000 + i}, {200 + i * 10})";
                    using (SqlCommand ins = new SqlCommand(insert, conn))
                        ins.ExecuteNonQuery();
                }

                // Заполнение Customers
                for (int i = 1; i <= 10; i++)
                {
                    string insert = $"INSERT INTO Customers (CustomerName, Address, Phone, Email) VALUES (N'Клиент{i}', N'Адрес{i}', N'+7-000-000-00{i}', N'client{i}@example.com')";
                    using (SqlCommand ins = new SqlCommand(insert, conn))
                        ins.ExecuteNonQuery();
                }
            }
        }

        public static DataTable GetDataTable(string tableName)
        {
            using (SqlConnection conn = new SqlConnection(LiriDBConnectionString))
            {
                conn.Open();
                string query = $"SELECT * FROM {tableName}";
                using (SqlDataAdapter adapter = new SqlDataAdapter(query, conn))
                {
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    return dt;
                }
            }
        }
    }
}