using System.Text.Json;
using DX86;
using DX86.Modules;
using Library;

namespace Server;

    class Program
    {
        public static MessageSender messageSender;
        public static MessageBox messageBox;
        public static InputBox inputBox;
        public static ItemSelector itemSelector;
        public static MySQL mySql;
        private static List<TcpServer> servers;

        static void Main(string[] args)
        {
            Console.WriteLine("Initializing server...");
            // Initialize UI and logging components
            messageSender = new MessageSender("log.txt");
            messageBox    = new MessageBox(messageSender);
            inputBox      = new InputBox(messageSender);
            itemSelector  = new ItemSelector(messageSender);
            servers       = new List<TcpServer>();

            // 1) Load existing DB config or prompt user + initialize
            mySql = LoadOrInitDatabase();

            // 2) Start the rest of your server loop
            ServerLoop();
        }

        #region MySQL Initialization
                private static MySQL LoadOrInitDatabase()
        {
            var cfg = DbConfig.Load();
            if (cfg != null)
            {
                messageSender.Log("[Program] Loaded existing database configuration.");
                // Use existing credentials to connect (including cfg.Port)
                return InitializeDatabase(cfg.Username, cfg.Password, cfg.Server, cfg.Port, cfg.Database);
            }
            else
            {
                messageSender.Warn("[Program] No existing DB configuration found. Starting init…");

                // REPLACE THE FOUR PROMPTS WITH YOUR FIVE ShowAsync(...) CALLS:
                string username = inputBox.ShowAsync("MySQL Configuration [1/5]", ["Username for MySQL Connections"]);
                string password = inputBox.ShowAsync("MySQL Configuration [2/5]", ["Password for MySQL Connections"]);
                string server   = inputBox.ShowAsync("MySQL Configuration [3/5]", ["Server Address for MySQL Connections"]);
                string port     = inputBox.ShowAsync("MySQL Configuration [4/5]", ["Port for MySQL Connections"]);
                string database = inputBox.ShowAsync("MySQL Configuration [5/5]", ["Database Name for Database Connections"]);

                return InitializeDatabase(username, password, server, port, database);
            }
        }
                /// <summary>
                /// Creates a new MySQL connection, runs the CREATE TABLE DDL,
                /// saves credentials in config.json, and returns the MySQL instance.
                /// </summary>
                private static MySQL InitializeDatabase(
                    string username,
                    string password,
                    string server,
                    string port,
                    string database
                )
        {
            // 1) Instantiate MySQL (will exit on failure)
            var db = new MySQL(username, password, server, port, database, messageSender);
            string createEmployeesTable = @"
                CREATE TABLE IF NOT EXISTS `employees` (
                    `Id`            INT             NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    `Code`          VARCHAR(50)     NOT NULL,
                    `Surname`       VARCHAR(100)    NOT NULL,
                    `Forename`      VARCHAR(100)    NOT NULL,
                    `Email`         VARCHAR(150)    NOT NULL,
                    `Phone`         VARCHAR(50),
                    `Street`        VARCHAR(200),
                    `City`          VARCHAR(100),
                    `Postcode`      VARCHAR(20),
                    `Country`       VARCHAR(100),
                    `Department`    VARCHAR(100),
                    `Position`      VARCHAR(100),
                    `EmployeeState` INT             NOT NULL
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
            ";
        
            messageSender.Log("[Program] Creating or verifying 'employees' table…");
            var employeesResult = db.ExecuteNonQuery(createEmployeesTable);
            try
            {
                using var doc = JsonDocument.Parse(employeesResult);
                bool empError = doc.RootElement.GetProperty("error").GetBoolean();
                if (empError)
                {
                    messageSender.Error("[Program] Failed to create/verify 'employees' table: " + employeesResult);
                    Environment.Exit(1);
                }
            }
            catch (Exception ex)
            {
                messageSender.Error("[Program] Could not parse employees CREATE TABLE response: " + ex.Message);
                messageSender.Error("[Program] Raw response: " + employeesResult);
                Environment.Exit(1);
            }
            messageSender.Log("[Program] 'employees' table is ready.");
        
            // 2b) Create a new table for storing 4-digit PINs linked to EmployeeId
            string createPinsTable = @"
                CREATE TABLE IF NOT EXISTS `employee_pins` (
                    `PinId`       INT    NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    `EmployeeId`  INT    NOT NULL,
                    `PinCode`     CHAR(4) NOT NULL,
                    FOREIGN KEY (`EmployeeId`) REFERENCES `employees`(`Id`) 
                        ON DELETE CASCADE 
                        ON UPDATE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
            ";
        
            messageSender.Log("[Program] Creating or verifying 'employee_pins' table…");
            var pinsResult = db.ExecuteNonQuery(createPinsTable);
            try
            {
                using var doc2 = JsonDocument.Parse(pinsResult);
                bool pinError = doc2.RootElement.GetProperty("error").GetBoolean();
                if (pinError)
                {
                    messageSender.Error("[Program] Failed to create/verify 'employee_pins' table: " + pinsResult);
                    Environment.Exit(1);
                }
            }
            catch (Exception ex)
            {
                messageSender.Error("[Program] Could not parse employee_pins CREATE TABLE response: " + ex.Message);
                messageSender.Error("[Program] Raw response: " + pinsResult);
                Environment.Exit(1);
            }
            messageSender.Log("[Program] 'employee_pins' table is ready.");
            
            // 3) Neue Tabelle für Status‐Historie anlegen:
            string createStateHistoryTable = @"
                CREATE TABLE IF NOT EXISTS `employee_state_history` (
                    `HistoryId`   INT       NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    `EmployeeId`  INT       NOT NULL,
                    `NewState`    INT       NOT NULL,
                    `ChangeTime`  DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (`EmployeeId`) 
                        REFERENCES `employees`(`Id`) 
                        ON DELETE CASCADE 
                        ON UPDATE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
            ";
        
            Program.messageSender.Log("[Program] Ensuring 'employee_state_history' table exists…");
            var historyResultJson = db.ExecuteNonQuery(createStateHistoryTable);
            try
            {
                using var doc = JsonDocument.Parse(historyResultJson);
                bool histError = doc.RootElement.GetProperty("error").GetBoolean();
                if (histError)
                {
                    string dbMsg = doc.RootElement.GetProperty("data").GetString() ?? "Unknown DB error";
                    Program.messageSender.Error("[Program] Failed to create 'employee_state_history' table: " + dbMsg);
                    Environment.Exit(1);
                }
            }
            catch (Exception ex)
            {
                Program.messageSender.Error("[Program] Could not parse state history CREATE TABLE response: " + ex.Message);
                Program.messageSender.Error("[Program] Raw response: " + historyResultJson);
                Environment.Exit(1);
            }
            Program.messageSender.Log("[Program] 'employee_state_history' table is ready.");
        
            // … (any further tables) …
        
            // 3) Save the new credentials (including port) into config.json:
            var newCfg = new DbConfig
            {
                Username = username,
                Password = password,
                Server   = server,
                Port     = port,
                Database = database
            };
            newCfg.Save();
            messageSender.Log("[Program] Saved DB configuration to config.json.");
        
            return db;
        }

        #endregion

        private static void ServerLoop()
        {
            bool running = true;
            string[] menuItems;
            string selectedItem;
            servers.Add(new BackendServer("0.0.0.0", 3767, messageSender));
            // Main server loop
            while (running)
            {
                menuItems = new[] { "Benutzerverwaltung", "Einstellungen", "Beenden" };
                itemSelector.SetTitle("Hauptmenü");
                //selectedItem = itemSelector.SelectItemFromList(menuItems);
            }
        }
    }