<?php
class Menu {
    private $db;

    public function __construct() {
        $database = new Database();
        $this->db = $database->getConnection();

        // Create menus and menu_items tables if they don't exist
        $this->createMenuTables();
    }

    private function createMenuTables() {
        // Check database type
        $dbType = $_ENV['DB_TYPE'] ?? 'sqlite';

        if ($dbType === 'mysql') {
            // MySQL syntax
            // Create menus table
            $sql = "CREATE TABLE IF NOT EXISTS menus (
                id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                location VARCHAR(255),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

            $this->db->exec($sql);

            // Check if menu_items table exists and has icon column
            $hasIconColumn = false;
            try {
                $stmt = $this->db->query("SHOW COLUMNS FROM menu_items LIKE 'icon'");
                $hasIconColumn = $stmt->rowCount() > 0;
            } catch (PDOException $e) {
                // Table doesn't exist
            }

            // If table exists but doesn't have icon column, we need to recreate it
            if (!$hasIconColumn) {
                // Backup existing data if table exists
                $existingData = [];
                try {
                    $stmt = $this->db->query("SELECT * FROM menu_items");
                    $existingData = $stmt->fetchAll(PDO::FETCH_ASSOC);

                    // Drop the existing table
                    $this->db->exec("DROP TABLE IF EXISTS menu_items");
                } catch (PDOException $e) {
                    // Table doesn't exist, no need to backup or drop
                }

                // Create menu_items table with new schema
                $sql = "CREATE TABLE menu_items (
                    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    menu_id INT UNSIGNED NOT NULL,
                    parent_id INT UNSIGNED DEFAULT 0,
                    title VARCHAR(255) NOT NULL,
                    url VARCHAR(255) NOT NULL,
                    target VARCHAR(50) DEFAULT '_self',
                    order_position INT DEFAULT 0,
                    type VARCHAR(50) DEFAULT 'custom',
                    object_id INT UNSIGNED DEFAULT NULL,
                    icon VARCHAR(255) DEFAULT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    FOREIGN KEY (menu_id) REFERENCES menus(id) ON DELETE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

                $this->db->exec($sql);

                // Restore existing data if any
                if (!empty($existingData)) {
                    $stmt = $this->db->prepare("
                        INSERT INTO menu_items (
                            id, menu_id, parent_id, title, url, target,
                            order_position, type, object_id, created_at, updated_at
                        ) VALUES (
                            :id, :menu_id, :parent_id, :title, :url, :target,
                            :order_position, :type, :object_id, :created_at, :updated_at
                        )
                    ");

                    foreach ($existingData as $item) {
                        $stmt->execute([
                            ':id' => $item['id'],
                            ':menu_id' => $item['menu_id'],
                            ':parent_id' => $item['parent_id'],
                            ':title' => $item['title'],
                            ':url' => $item['url'],
                            ':target' => $item['target'],
                            ':order_position' => $item['order_position'],
                            ':type' => $item['type'],
                            ':object_id' => $item['object_id'],
                            ':created_at' => $item['created_at'],
                            ':updated_at' => $item['updated_at']
                        ]);
                    }
                }
            }
        } else {
            // SQLite syntax
            // Create menus table
            $sql = "CREATE TABLE IF NOT EXISTS menus (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                location TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )";

            $this->db->exec($sql);

            // Check if menu_items table exists and has icon column
            $hasIconColumn = false;
            try {
                $stmt = $this->db->query("PRAGMA table_info(menu_items)");
                $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
                foreach ($columns as $column) {
                    if ($column['name'] === 'icon') {
                        $hasIconColumn = true;
                        break;
                    }
                }
            } catch (PDOException $e) {
                // Table doesn't exist
            }

            // If table exists but doesn't have icon column, we need to recreate it
            if (!$hasIconColumn) {
                // Backup existing data if table exists
                $existingData = [];
                try {
                    $stmt = $this->db->query("SELECT * FROM menu_items");
                    $existingData = $stmt->fetchAll(PDO::FETCH_ASSOC);

                    // Drop the existing table
                    $this->db->exec("DROP TABLE IF EXISTS menu_items");
                } catch (PDOException $e) {
                    // Table doesn't exist, no need to backup or drop
                }

                // Create menu_items table with new schema
                $sql = "CREATE TABLE menu_items (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    menu_id INTEGER NOT NULL,
                    parent_id INTEGER DEFAULT 0,
                    title TEXT NOT NULL,
                    url TEXT NOT NULL,
                    target TEXT DEFAULT '_self',
                    order_position INTEGER DEFAULT 0,
                    type TEXT DEFAULT 'custom',
                    object_id INTEGER DEFAULT NULL,
                    icon TEXT DEFAULT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (menu_id) REFERENCES menus(id) ON DELETE CASCADE
                )";

                $this->db->exec($sql);

                // Restore existing data if any
                if (!empty($existingData)) {
                    $stmt = $this->db->prepare("
                        INSERT INTO menu_items (
                            id, menu_id, parent_id, title, url, target,
                            order_position, type, object_id, created_at, updated_at
                        ) VALUES (
                            :id, :menu_id, :parent_id, :title, :url, :target,
                            :order_position, :type, :object_id, :created_at, :updated_at
                        )
                    ");

                    foreach ($existingData as $item) {
                        $stmt->execute([
                            ':id' => $item['id'],
                            ':menu_id' => $item['menu_id'],
                            ':parent_id' => $item['parent_id'],
                            ':title' => $item['title'],
                            ':url' => $item['url'],
                            ':target' => $item['target'],
                            ':order_position' => $item['order_position'],
                            ':type' => $item['type'],
                            ':object_id' => $item['object_id'],
                            ':created_at' => $item['created_at'],
                            ':updated_at' => $item['updated_at']
                        ]);
                    }
                }
            }
        }
    }

    public function getAllMenus() {
        $stmt = $this->db->prepare("SELECT * FROM menus ORDER BY name ASC");
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getMenuById($id) {
        $stmt = $this->db->prepare("SELECT * FROM menus WHERE id = :id");
        $stmt->bindParam(':id', $id);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function getMenuByLocation($location) {
        $stmt = $this->db->prepare("SELECT * FROM menus WHERE location = :location");
        $stmt->bindParam(':location', $location);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function createMenu($name, $location = null) {
        $sql = "INSERT INTO menus (name, location, updated_at) VALUES (:name, :location, CURRENT_TIMESTAMP)";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':location', $location);

        if ($stmt->execute()) {
            return $this->db->lastInsertId();
        }

        return false;
    }

    public function updateMenu($id, $name, $location = null) {
        $sql = "UPDATE menus SET name = :name, location = :location, updated_at = CURRENT_TIMESTAMP WHERE id = :id";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':location', $location);

        return $stmt->execute();
    }

    public function deleteMenu($id) {
        // Delete menu items first (should cascade, but just to be safe)
        $stmt = $this->db->prepare("DELETE FROM menu_items WHERE menu_id = :id");
        $stmt->bindParam(':id', $id);
        $stmt->execute();

        // Delete the menu
        $stmt = $this->db->prepare("DELETE FROM menus WHERE id = :id");
        $stmt->bindParam(':id', $id);
        return $stmt->execute();
    }

    public function setMenuLocation($id, $location) {
        // First, clear the location from any other menu
        if ($location) {
            $stmt = $this->db->prepare("UPDATE menus SET location = NULL WHERE location = :location");
            $stmt->bindParam(':location', $location);
            $stmt->execute();
        }

        // Then set the location for this menu
        $sql = "UPDATE menus SET location = :location, updated_at = CURRENT_TIMESTAMP WHERE id = :id";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->bindParam(':location', $location);

        return $stmt->execute();
    }

    // Menu Items Methods

    public function getMenuItems($menuId, $parentId = 0) {
        $stmt = $this->db->prepare("
            SELECT * FROM menu_items
            WHERE menu_id = :menu_id AND parent_id = :parent_id
            ORDER BY order_position ASC
        ");
        $stmt->bindParam(':menu_id', $menuId);
        $stmt->bindParam(':parent_id', $parentId);
        $stmt->execute();

        $items = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Get children for each item
        foreach ($items as &$item) {
            $item['children'] = $this->getMenuItems($menuId, $item['id']);
        }

        return $items;
    }

    public function getAllMenuItemsFlat($menuId) {
        $stmt = $this->db->prepare("
            SELECT * FROM menu_items
            WHERE menu_id = :menu_id
            ORDER BY parent_id ASC, order_position ASC
        ");
        $stmt->bindParam(':menu_id', $menuId);
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getMenuItem($id) {
        $stmt = $this->db->prepare("SELECT * FROM menu_items WHERE id = :id");
        $stmt->bindParam(':id', $id);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function addMenuItem($menuId, $title, $url, $parentId = 0, $target = '_self', $orderPosition = 0, $type = 'custom', $objectId = null, $icon = null) {
        $stmt = $this->db->prepare("
            INSERT INTO menu_items (menu_id, title, url, parent_id, target, order_position, type, object_id, icon)
            VALUES (:menu_id, :title, :url, :parent_id, :target, :order_position, :type, :object_id, :icon)
        ");

        $stmt->bindParam(':menu_id', $menuId);
        $stmt->bindParam(':title', $title);
        $stmt->bindParam(':url', $url);
        $stmt->bindParam(':parent_id', $parentId);
        $stmt->bindParam(':target', $target);
        $stmt->bindParam(':order_position', $orderPosition);
        $stmt->bindParam(':type', $type);
        $stmt->bindParam(':object_id', $objectId);
        $stmt->bindParam(':icon', $icon);

        return $stmt->execute() ? $this->db->lastInsertId() : false;
    }

    public function updateMenuItem($id, $title, $url, $parentId = 0, $target = '_self', $orderPosition = 0, $type = 'custom', $objectId = null, $icon = null) {
        $stmt = $this->db->prepare("
            UPDATE menu_items
            SET title = :title,
                url = :url,
                parent_id = :parent_id,
                target = :target,
                order_position = :order_position,
                type = :type,
                object_id = :object_id,
                icon = :icon,
                updated_at = CURRENT_TIMESTAMP
            WHERE id = :id
        ");

        $stmt->bindParam(':id', $id);
        $stmt->bindParam(':title', $title);
        $stmt->bindParam(':url', $url);
        $stmt->bindParam(':parent_id', $parentId);
        $stmt->bindParam(':target', $target);
        $stmt->bindParam(':order_position', $orderPosition);
        $stmt->bindParam(':type', $type);
        $stmt->bindParam(':object_id', $objectId);
        $stmt->bindParam(':icon', $icon);

        return $stmt->execute();
    }

    public function deleteMenuItem($id) {
        // Get all children
        $stmt = $this->db->prepare("SELECT id FROM menu_items WHERE parent_id = :id");
        $stmt->bindParam(':id', $id);
        $stmt->execute();
        $children = $stmt->fetchAll(PDO::FETCH_COLUMN);

        // Recursively delete all children
        foreach ($children as $childId) {
            $this->deleteMenuItem($childId);
        }

        // Delete the item itself
        $stmt = $this->db->prepare("DELETE FROM menu_items WHERE id = :id");
        $stmt->bindParam(':id', $id);
        return $stmt->execute();
    }

    public function updateMenuItemsOrder($items) {
        $success = true;

        foreach ($items as $item) {
            $stmt = $this->db->prepare("
                UPDATE menu_items
                SET parent_id = :parent_id,
                    order_position = :order_position,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = :id
            ");
            $stmt->bindParam(':id', $item['id']);
            $stmt->bindParam(':parent_id', $item['parent_id']);
            $stmt->bindParam(':order_position', $item['order_position']);

            if (!$stmt->execute()) {
                $success = false;
            }
        }

        return $success;
    }

    // Helper method to render menu HTML
    public function renderMenu($location, $options = []) {
        $menu = $this->getMenuByLocation($location);

        if (!$menu) {
            return '';
        }

        $items = $this->getMenuItems($menu['id']);

        if (empty($items)) {
            return '';
        }

        $defaults = [
            'container' => 'nav',
            'container_class' => '',
            'menu_class' => '',
            'item_class' => '',
            'link_class' => '',
            'submenu_class' => '',
            'submenu_item_class' => '',
            'submenu_link_class' => ''
        ];

        $options = array_merge($defaults, $options);

        $html = '';

        if ($options['container']) {
            $html .= '<' . $options['container'];
            if ($options['container_class']) {
                $html .= ' class="' . $options['container_class'] . '"';
            }
            $html .= '>';
        }

        $html .= '<ul';
        if ($options['menu_class']) {
            $html .= ' class="' . $options['menu_class'] . '"';
        }
        $html .= '>';

        foreach ($items as $item) {
            $html .= $this->renderMenuItem($item, $options);
        }

        $html .= '</ul>';

        if ($options['container']) {
            $html .= '</' . $options['container'] . '>';
        }

        return $html;
    }

    private function renderMenuItem($item, $options, $isSubmenu = false) {
        $itemClass = $isSubmenu ? $options['submenu_item_class'] : $options['item_class'];
        $linkClass = $isSubmenu ? $options['submenu_link_class'] : $options['link_class'];

        $html = '<li';
        if ($itemClass) {
            $html .= ' class="' . $itemClass . '"';
        }
        $html .= '>';

        $html .= '<a href="' . htmlspecialchars($item['url']) . '"';
        if ($linkClass) {
            $html .= ' class="' . $linkClass . '"';
        }
        if ($item['target'] && $item['target'] !== '_self') {
            $html .= ' target="' . htmlspecialchars($item['target']) . '"';
        }
        $html .= '>';

        // Add icon if present
        if (!empty($item['icon'])) {
            $html .= '<i class="' . htmlspecialchars($item['icon']) . ' mr-2"></i>';
        }

        $html .= htmlspecialchars($item['title']) . '</a>';

        if (!empty($item['children'])) {
            $html .= '<ul';
            if ($options['submenu_class']) {
                $html .= ' class="' . $options['submenu_class'] . '"';
            }
            $html .= '>';

            foreach ($item['children'] as $child) {
                $html .= $this->renderMenuItem($child, $options, true);
            }

            $html .= '</ul>';
        }

        $html .= '</li>';

        return $html;
    }
}