-- ═══════════════════════════════════════════════════════════════
-- Database-driven navigation for header + sidebar
-- Run this once per site database.
-- ═══════════════════════════════════════════════════════════════

CREATE TABLE `nav_menus` (
  `id` int(10) UNSIGNED NOT NULL,
  `label` varchar(100) NOT NULL COMMENT 'Display label, e.g. "Products"',
  `icon` varchar(80) NOT NULL DEFAULT 'fas fa-circle' COMMENT 'FontAwesome class',
  `icon_svg` text DEFAULT NULL,
  `url` varchar(500) NOT NULL DEFAULT '#' COMMENT 'Direct link (used when no children)',
  `target` enum('_self','_blank') NOT NULL DEFAULT '_self',
  `show_logged_in` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1 = show to logged-in users',
  `show_guest` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1 = show to guests',
  `location` set('header','sidebar') NOT NULL DEFAULT 'header,sidebar' COMMENT 'Where to show',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` smallint(6) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Top-level navigation menu items';

CREATE TABLE `nav_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `menu_id` int(10) UNSIGNED NOT NULL COMMENT 'FK → nav_menus.id',
  `label` varchar(100) NOT NULL,
  `icon` varchar(80) NOT NULL DEFAULT 'fas fa-circle',
  `icon_svg` text DEFAULT NULL,
  `url` varchar(500) NOT NULL DEFAULT '#',
  `target` enum('_self','_blank') NOT NULL DEFAULT '_self',
  `show_logged_in` tinyint(1) NOT NULL DEFAULT 1,
  `show_guest` tinyint(1) NOT NULL DEFAULT 1,
  `badge_type` enum('none','static','dynamic') NOT NULL DEFAULT 'none' COMMENT 'Badge on the item',
  `badge_static` varchar(30) DEFAULT NULL COMMENT 'Static badge text e.g. "New"',
  `badge_db_query` text DEFAULT NULL COMMENT 'SQL query returning a count for dynamic badge',
  `badge_color` varchar(30) NOT NULL DEFAULT 'indigo' COMMENT 'Tailwind color name',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` smallint(6) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Second-level navigation items (dropdown children)';

CREATE TABLE `nav_subitems` (
  `id` int(10) UNSIGNED NOT NULL,
  `item_id` int(10) UNSIGNED NOT NULL COMMENT 'FK → nav_items.id',
  `label` varchar(100) NOT NULL,
  `icon` varchar(80) NOT NULL DEFAULT 'fas fa-circle',
  `icon_svg` text DEFAULT NULL,
  `url` varchar(500) NOT NULL DEFAULT '#',
  `target` enum('_self','_blank') NOT NULL DEFAULT '_self',
  `show_logged_in` tinyint(1) NOT NULL DEFAULT 1,
  `show_guest` tinyint(1) NOT NULL DEFAULT 1,
  `badge_type` enum('none','static','dynamic') NOT NULL DEFAULT 'none',
  `badge_static` varchar(30) DEFAULT NULL,
  `badge_db_query` text DEFAULT NULL,
  `badge_color` varchar(30) NOT NULL DEFAULT 'indigo',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` smallint(6) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Third-level navigation items (sub-submenu)';

ALTER TABLE `nav_menus` ADD PRIMARY KEY (`id`), ADD KEY `idx_active_sort` (`is_active`,`sort_order`);
ALTER TABLE `nav_items` ADD PRIMARY KEY (`id`), ADD KEY `idx_menu` (`menu_id`), ADD KEY `idx_active_sort` (`is_active`,`sort_order`);
ALTER TABLE `nav_subitems` ADD PRIMARY KEY (`id`), ADD KEY `idx_item` (`item_id`), ADD KEY `idx_active_sort` (`is_active`,`sort_order`);

ALTER TABLE `nav_menus` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `nav_items` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `nav_subitems` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `nav_items` ADD CONSTRAINT `fk_nav_items_menu` FOREIGN KEY (`menu_id`) REFERENCES `nav_menus` (`id`) ON DELETE CASCADE;
ALTER TABLE `nav_subitems` ADD CONSTRAINT `fk_nav_subitems_item` FOREIGN KEY (`item_id`) REFERENCES `nav_items` (`id`) ON DELETE CASCADE;

-- ═══════════════════════════════════════════════════════════════
-- Seed data — এখনকার header.php / sidebar.php তে যা হার্ডকোড ছিল
-- সেটার সমতুল্য। badge_db_query তে :user_id placeholder ব্যবহার
-- করা হয়েছে (raw user input বসানো হয় না, PDO bound parameter)।
-- ═══════════════════════════════════════════════════════════════

INSERT INTO `nav_menus` (`id`,`label`,`icon`,`url`,`show_logged_in`,`show_guest`,`location`,`sort_order`) VALUES
(1, 'Products', 'fas fa-box', '#', 1, 1, 'header,sidebar', 10),
(2, 'Services', 'fas fa-server', '#', 1, 0, 'header,sidebar', 20),
(3, 'Domains',  'fas fa-globe', '#', 1, 1, 'header,sidebar', 30),
(4, 'Billing',  'fas fa-file-invoice-dollar', '#', 1, 0, 'header,sidebar', 40),
(5, 'Add Funds','fas fa-wallet', '/clientarea.php?action=add-funds', 1, 0, 'header,sidebar', 50),
(6, 'Support',  'fas fa-headset', '#', 1, 1, 'header,sidebar', 60);

-- Products (label differs slightly for guest vs logged-in in old code — unified here)
INSERT INTO `nav_items` (`menu_id`,`label`,`icon`,`url`,`show_logged_in`,`show_guest`,`sort_order`) VALUES
(1, 'Starter Hosting',  'fas fa-circle', '/cart.php?action=view-plans&category=starter',  1, 1, 10),
(1, 'Premium Hosting',  'fas fa-circle', '/cart.php?action=view-plans&category=premium',  1, 1, 20),
(1, 'Reseller Hosting', 'fas fa-circle', '/cart.php?action=view-plans&category=reseller', 1, 1, 30);

-- Services (logged-in only)
INSERT INTO `nav_items` (`menu_id`,`label`,`icon`,`url`,`show_logged_in`,`show_guest`,`badge_type`,`badge_db_query`,`sort_order`) VALUES
(2, 'My Services',          'fas fa-server',     '/clientarea.php?action=my-services',       1, 0, 'dynamic', 'SELECT COUNT(*) FROM my_services WHERE user_id = :user_id AND status = "active"', 10),
(2, 'Service Renewals',     'fas fa-sync-alt',   '/clientarea.php?action=renew-hosting',     1, 0, 'none', NULL, 20),
(2, 'Order New Services',   'fas fa-plus-circle','/clientarea.php?action=view-plans',        1, 0, 'none', NULL, 30),
(2, 'Pending Activations',  'fas fa-bolt',       '/clientarea.php?action=activate-services', 1, 0, 'dynamic', 'SELECT COUNT(*) FROM my_services WHERE user_id = :user_id AND status = "pending"', 40);

-- Domains
INSERT INTO `nav_items` (`menu_id`,`label`,`icon`,`url`,`show_logged_in`,`show_guest`,`badge_type`,`badge_db_query`,`sort_order`) VALUES
(3, 'My Domains',            'fas fa-globe',        '/clientarea.php?action=my-domains',       1, 0, 'dynamic', 'SELECT COUNT(*) FROM my_domains WHERE user_id = :user_id AND status = "active"', 10),
(3, 'Domain Renewals',       'fas fa-sync-alt',     '/clientarea.php?action=domain-renewals',  1, 0, 'none', NULL, 20),
(3, 'Register a New Domain', 'fas fa-plus-circle',  '/cart.php?a=add&register=domain',         1, 1, 'none', NULL, 30),
(3, 'Transfer Domain to Us', 'fas fa-exchange-alt', '/cart.php?action=transfer-domain',        1, 1, 'none', NULL, 40),
(3, 'Domain Pricing',        'fas fa-tag',          '/cart.php?action=domain-pricing',         1, 1, 'none', NULL, 50);

-- Billing (logged-in only)
INSERT INTO `nav_items` (`menu_id`,`label`,`icon`,`url`,`show_logged_in`,`show_guest`,`badge_type`,`badge_color`,`badge_db_query`,`sort_order`) VALUES
(4, 'My Invoices', 'fas fa-money-bill-wave', '/clientarea.php?action=my-invoices', 1, 0, 'dynamic', 'yellow', 'SELECT COUNT(*) FROM invoices WHERE user_id = :user_id AND payment_status = "unpaid"', 10),
(4, 'Add Funds',   'fas fa-list',            '/clientarea.php?action=add-funds',   1, 0, 'none', 'indigo', NULL, 20);

-- Support
INSERT INTO `nav_items` (`menu_id`,`label`,`icon`,`url`,`target`,`show_logged_in`,`show_guest`,`badge_type`,`badge_db_query`,`sort_order`) VALUES
(6, 'Telegram', 'fas fa-circle',     'https://t.me/D4rkC0der_x',                 '_blank', 1, 1, 'none', NULL, 10),
(6, 'Whatsapp', 'fas fa-circle',     'https://wa.me/+8801731434880',             '_blank', 1, 1, 'none', NULL, 20),
(6, 'Tickets',  'fas fa-ticket-alt', '/clientarea.php?action=support-tickets',   '_self',  1, 1, 'dynamic', 'SELECT COUNT(*) FROM support_messages sm JOIN support_tickets st ON sm.ticket_id = st.id WHERE st.user_id = :user_id AND sm.sender_type = "admin" AND sm.is_internal_note = 0 AND sm.seen_by_user IS NULL', 30);

-- nav_subitems: এখন কোনোটা লাগছে না, কিন্তু future-এ যেমন
-- "Products" → "Starter Hosting" → "Monthly / Yearly plan" এই রকম
-- তৃতীয় স্তর দরকার হলে এখানে row বসিয়ে দিলেই render function
-- automatic দেখাবে।