-- =========================================================
-- FBA UK Website — CMS Menu & Pages
-- File: database/DB_changes.sql
-- Run this SQL on database: fba_uk_website
-- =========================================================

-- ---------------------------------------------------------
-- 1) Menus table (dynamic sidebar / header / footer menus)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS `menus` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `title` VARCHAR(191) NOT NULL,
  `slug` VARCHAR(191) NOT NULL,
  `icon` VARCHAR(100) NULL DEFAULT NULL,
  `route_name` VARCHAR(191) NULL DEFAULT NULL,
  `url` VARCHAR(255) NULL DEFAULT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `status` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=active,0=inactive',
  `show_in_header` TINYINT(1) NOT NULL DEFAULT 1,
  `show_in_footer` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `menus_slug_unique` (`slug`),
  KEY `menus_parent_id_index` (`parent_id`),
  KEY `menus_sort_order_index` (`sort_order`),
  CONSTRAINT `menus_parent_id_foreign`
    FOREIGN KEY (`parent_id`) REFERENCES `menus` (`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- 2) CMS pages table (page content + SEO details)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cms_pages` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `menu_id` BIGINT UNSIGNED NOT NULL,
  `page_title` VARCHAR(255) NOT NULL,
  `page_heading` VARCHAR(255) NULL DEFAULT NULL,
  `page_subheading` TEXT NULL,
  `content` LONGTEXT NULL,
  `meta_title` VARCHAR(255) NULL DEFAULT NULL,
  `meta_description` TEXT NULL,
  `meta_keywords` VARCHAR(500) NULL DEFAULT NULL,
  `meta_robots` VARCHAR(100) NOT NULL DEFAULT 'index,follow',
  `canonical_url` VARCHAR(255) NULL DEFAULT NULL,
  `og_title` VARCHAR(255) NULL DEFAULT NULL,
  `og_description` TEXT NULL,
  `og_image` VARCHAR(255) NULL DEFAULT NULL,
  `status` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=published,0=draft',
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `cms_pages_menu_id_unique` (`menu_id`),
  CONSTRAINT `cms_pages_menu_id_foreign`
    FOREIGN KEY (`menu_id`) REFERENCES `menus` (`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- 3) Seed frontend menus (as per website)
-- ---------------------------------------------------------
INSERT INTO `menus`
(`id`, `parent_id`, `title`, `slug`, `icon`, `route_name`, `url`, `sort_order`, `status`, `show_in_header`, `show_in_footer`, `created_at`, `updated_at`)
VALUES
(1, NULL, 'Home', 'home', 'ri-home-4-line', 'home', NULL, 1, 1, 1, 1, NOW(), NOW()),
(2, NULL, 'About Us', 'about-us', 'ri-information-line', 'about', NULL, 2, 1, 1, 1, NOW(), NOW()),
(3, NULL, 'Courses', 'courses', 'ri-book-open-line', 'courses', NULL, 3, 1, 1, 1, NOW(), NOW()),
(4, NULL, 'Intakes', 'intakes', 'ri-calendar-event-line', 'intakes', NULL, 4, 1, 1, 0, NOW(), NOW()),
(5, NULL, 'Campuses', 'campuses', 'ri-building-line', 'campuses', NULL, 5, 1, 1, 1, NOW(), NOW()),
(6, NULL, 'Services', 'services', 'ri-service-line', 'services', NULL, 6, 1, 0, 1, NOW(), NOW()),
(7, NULL, 'Students', 'students', 'ri-user-smile-line', 'students', NULL, 7, 1, 0, 1, NOW(), NOW()),
(8, NULL, 'International', 'international', 'ri-earth-line', 'international', NULL, 8, 1, 0, 1, NOW(), NOW()),
(9, NULL, 'Contact', 'contact', 'ri-mail-line', 'contact', NULL, 9, 1, 1, 1, NOW(), NOW()),
(10, NULL, 'Privacy Policy', 'privacy-policy', 'ri-shield-keyhole-line', 'privacy', NULL, 10, 1, 0, 1, NOW(), NOW()),
(11, NULL, 'Terms & Conditions', 'terms-and-conditions', 'ri-file-list-3-line', 'terms', NULL, 11, 1, 0, 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE
  `title` = VALUES(`title`),
  `icon` = VALUES(`icon`),
  `route_name` = VALUES(`route_name`),
  `sort_order` = VALUES(`sort_order`),
  `status` = VALUES(`status`),
  `show_in_header` = VALUES(`show_in_header`),
  `show_in_footer` = VALUES(`show_in_footer`),
  `updated_at` = NOW();

-- ---------------------------------------------------------
-- 4) Seed CMS page content + SEO for each menu
-- ---------------------------------------------------------
INSERT INTO `cms_pages`
(`menu_id`, `page_title`, `page_heading`, `page_subheading`, `content`, `meta_title`, `meta_description`, `meta_keywords`, `meta_robots`, `canonical_url`, `og_title`, `og_description`, `og_image`, `status`, `created_at`, `updated_at`)
VALUES
(1, 'Home', 'Start Your UK University Journey', 'Expert admissions guidance and student finance support.', NULL, 'FBA UK LTD | UK Education Consultancy', 'FBA UK LTD helps students secure places at leading UK universities with expert admissions and finance guidance.', 'uk university, education consultancy, student finance, admissions', 'index,follow', NULL, 'FBA UK LTD | UK Education Consultancy', 'Expert UK university admissions and student finance support.', NULL, 1, NOW(), NOW()),
(2, 'About Us', 'About FBA UK LTD', 'Empowering students to achieve their UK university dreams.', NULL, 'About Us | FBA UK LTD', 'Learn about FBA UK LTD — trusted UK education consultancy for Home and International students.', 'about fba uk, education consultancy, uk admissions', 'index,follow', NULL, 'About Us | FBA UK LTD', 'Trusted UK education consultancy for Home and International students.', NULL, 1, NOW(), NOW()),
(3, 'Courses', 'Popular Degree Programmes', 'Industry-relevant courses across the UK''s top cities.', NULL, 'Courses | FBA UK LTD', 'Browse undergraduate and postgraduate degree programmes available through FBA UK LTD.', 'uk courses, business management, computing, health and social care', 'index,follow', NULL, 'Courses | FBA UK LTD', 'Browse popular UK degree programmes.', NULL, 1, NOW(), NOW()),
(4, 'Intakes', 'Upcoming Intakes', 'Find the right intake for your study plans.', NULL, 'Intakes | FBA UK LTD', 'Check current and upcoming UK university intakes with FBA UK LTD.', 'uk intakes, february intake, june intake, october intake', 'index,follow', NULL, 'Intakes | FBA UK LTD', 'Current and upcoming UK university intakes.', NULL, 1, NOW(), NOW()),
(5, 'Campuses', 'Study in the UK''s Top Cities', 'Campuses across England and Wales with flexible study options.', NULL, 'Campuses | FBA UK LTD', 'Explore London, Birmingham, Manchester, Cardiff, Swansea and online study locations.', 'london campus, birmingham campus, manchester campus, cardiff campus', 'index,follow', NULL, 'Campuses | FBA UK LTD', 'Explore UK study locations with FBA UK LTD.', NULL, 1, NOW(), NOW()),
(6, 'Services', 'Our Services', 'End-to-end education support for Home and International students.', NULL, 'Our Services | FBA UK LTD', 'Admissions support, course advice, Student Finance guidance, transfers and international support.', 'education services, admissions support, student finance, counseling', 'index,follow', NULL, 'Our Services | FBA UK LTD', 'Comprehensive education consultancy services.', NULL, 1, NOW(), NOW()),
(7, 'Students', 'Home/EU Students', 'Home fee status and Student Finance guidance.', NULL, 'Home/EU Students | FBA UK LTD', 'Understand Home student fee status and Student Finance options with FBA UK LTD.', 'home students, student finance, sfe, maintenance loan', 'index,follow', NULL, 'Home/EU Students | FBA UK LTD', 'Guidance for Home and EU students.', NULL, 1, NOW(), NOW()),
(8, 'International', 'International Students', 'Course selection, documents, visa and pre-departure support.', NULL, 'International Students | FBA UK LTD', 'Support for international students applying to UK universities through FBA UK LTD.', 'international students, uk student visa, study in uk', 'index,follow', NULL, 'International Students | FBA UK LTD', 'Support for international students studying in the UK.', NULL, 1, NOW(), NOW()),
(9, 'Contact', 'Contact Our Advisors', 'Get in touch for free eligibility and admissions advice.', NULL, 'Contact Us | FBA UK LTD', 'Contact FBA UK LTD for course advice, admissions support and Student Finance guidance.', 'contact fba uk, admissions email, education consultancy contact', 'index,follow', NULL, 'Contact Us | FBA UK LTD', 'Contact FBA UK LTD advisors today.', NULL, 1, NOW(), NOW()),
(10, 'Privacy Policy', 'Privacy Policy', 'How FBA UK LTD collects, uses and protects your personal information.', NULL, 'Privacy Policy | FBA UK LTD', 'Read the FBA UK LTD Privacy Policy covering data collection, use and your rights.', 'privacy policy, data protection, gdpr', 'index,follow', NULL, 'Privacy Policy | FBA UK LTD', 'FBA UK LTD Privacy Policy.', NULL, 1, NOW(), NOW()),
(11, 'Terms & Conditions', 'Terms & Conditions', 'Terms governing use of the FBA UK LTD website and services.', NULL, 'Terms & Conditions | FBA UK LTD', 'Read the Terms & Conditions for using FBA UK LTD website and consultancy services.', 'terms and conditions, website terms', 'index,follow', NULL, 'Terms & Conditions | FBA UK LTD', 'FBA UK LTD Terms & Conditions.', NULL, 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE
  `page_title` = VALUES(`page_title`),
  `page_heading` = VALUES(`page_heading`),
  `page_subheading` = VALUES(`page_subheading`),
  `meta_title` = VALUES(`meta_title`),
  `meta_description` = VALUES(`meta_description`),
  `meta_keywords` = VALUES(`meta_keywords`),
  `updated_at` = NOW();

-- =========================================================
-- 5) Course Categories
-- =========================================================
CREATE TABLE IF NOT EXISTS `course_categories` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(191) NOT NULL,
  `short_name` VARCHAR(50) NULL DEFAULT NULL,
  `slug` VARCHAR(191) NOT NULL,
  `description` TEXT NULL,
  `location` VARCHAR(191) NULL DEFAULT NULL,
  `rank_label` VARCHAR(191) NULL DEFAULT NULL,
  `intake_label` VARCHAR(191) NULL DEFAULT NULL,
  `courses_label` VARCHAR(191) NULL DEFAULT NULL,
  `icon` VARCHAR(100) NULL DEFAULT NULL,
  `image` VARCHAR(255) NULL DEFAULT NULL,
  `cta_url` VARCHAR(255) NULL DEFAULT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `status` TINYINT(1) NOT NULL DEFAULT 1,
  `show_on_home` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `course_categories_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 6) Study Levels (managed like course categories)
-- =========================================================
CREATE TABLE IF NOT EXISTS `study_levels` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(191) NOT NULL,
  `slug` VARCHAR(191) NOT NULL,
  `description` TEXT NULL,
  `icon` VARCHAR(100) NULL DEFAULT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `status` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `study_levels_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `study_levels` (`name`, `slug`, `description`, `icon`, `sort_order`, `status`, `created_at`, `updated_at`) VALUES
('Foundation', 'foundation', 'Foundation courses help students get ready for university study.', 'fas fa-cubes', 1, 1, NOW(), NOW()),
('Undergraduate', 'undergraduate', 'Explore undergraduate degrees for almost any career path.', 'fas fa-graduation-cap', 2, 1, NOW(), NOW()),
('Postgraduate', 'postgraduate', 'Discover Master\'s and postgraduate programmes for career growth.', 'fas fa-user-graduate', 3, 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `description`=VALUES(`description`), `icon`=VALUES(`icon`), `updated_at`=NOW();

-- If study_levels already exists without icon:
-- ALTER TABLE `study_levels` ADD COLUMN `icon` VARCHAR(100) NULL AFTER `description`;

-- =========================================================
-- 7) Courses
-- =========================================================
CREATE TABLE IF NOT EXISTS `courses` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `course_category_id` BIGINT UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `short_title` VARCHAR(191) NULL DEFAULT NULL,
  `slug` VARCHAR(191) NOT NULL,
  `badge` VARCHAR(100) NULL DEFAULT NULL,
  `study_level_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `duration` VARCHAR(100) NULL DEFAULT NULL,
  `study_mode` VARCHAR(100) NULL DEFAULT NULL,
  `tuition_fee` VARCHAR(100) NULL DEFAULT NULL,
  `intake` VARCHAR(191) NULL DEFAULT NULL,
  `campuses_text` VARCHAR(255) NULL DEFAULT NULL,
  `image` VARCHAR(255) NULL DEFAULT NULL,
  `intro` TEXT NULL,
  `enquiry_note` TEXT NULL,
  `overview` LONGTEXT NULL,
  `content` LONGTEXT NULL,
  `meta_title` VARCHAR(255) NULL DEFAULT NULL,
  `meta_description` TEXT NULL,
  `meta_keywords` VARCHAR(500) NULL DEFAULT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `status` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `courses_slug_unique` (`slug`),
  KEY `courses_course_category_id_index` (`course_category_id`),
  KEY `courses_study_level_id_index` (`study_level_id`),
  CONSTRAINT `courses_course_category_id_foreign`
    FOREIGN KEY (`course_category_id`) REFERENCES `course_categories` (`id`)
    ON DELETE RESTRICT,
  CONSTRAINT `courses_study_level_id_foreign`
    FOREIGN KEY (`study_level_id`) REFERENCES `study_levels` (`id`)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- If courses already exist with old text column `study_level`, migrate then drop:
-- ALTER TABLE `courses` ADD COLUMN `study_level_id` BIGINT UNSIGNED NULL AFTER `badge`;
-- UPDATE courses c JOIN study_levels sl ON sl.name = c.study_level SET c.study_level_id = sl.id;
-- ALTER TABLE `courses` DROP COLUMN `study_level`;
-- ALTER TABLE `courses` ADD CONSTRAINT `courses_study_level_id_foreign` FOREIGN KEY (`study_level_id`) REFERENCES `study_levels` (`id`) ON DELETE SET NULL;

-- =========================================================
-- 8) Campus Categories
-- =========================================================
CREATE TABLE IF NOT EXISTS `campus_categories` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(191) NOT NULL,
  `slug` VARCHAR(191) NOT NULL,
  `description` TEXT NULL,
  `icon` VARCHAR(100) NULL DEFAULT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `status` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `campus_categories_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 9) Campuses
-- =========================================================
CREATE TABLE IF NOT EXISTS `campuses` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `campus_category_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(191) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(191) NOT NULL,
  `badge` VARCHAR(100) NULL DEFAULT NULL,
  `students` VARCHAR(50) NULL DEFAULT NULL,
  `courses_count` VARCHAR(50) NULL DEFAULT NULL,
  `image` VARCHAR(255) NULL DEFAULT NULL,
  `intro` TEXT NULL,
  `overview` LONGTEXT NULL,
  `address` VARCHAR(255) NULL DEFAULT NULL,
  `phone` VARCHAR(100) NULL DEFAULT NULL,
  `email` VARCHAR(191) NULL DEFAULT NULL,
  `map_embed` TEXT NULL,
  `latitude` VARCHAR(50) NULL DEFAULT NULL,
  `longitude` VARCHAR(50) NULL DEFAULT NULL,
  `meta_title` VARCHAR(255) NULL DEFAULT NULL,
  `meta_description` TEXT NULL,
  `meta_keywords` VARCHAR(500) NULL DEFAULT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `status` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `campuses_slug_unique` (`slug`),
  KEY `campuses_campus_category_id_index` (`campus_category_id`),
  CONSTRAINT `campuses_campus_category_id_foreign`
    FOREIGN KEY (`campus_category_id`) REFERENCES `campus_categories` (`id`)
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 10) Site Settings (key/value)
-- =========================================================
CREATE TABLE IF NOT EXISTS `site_settings` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` LONGTEXT NULL,
  `setting_group` VARCHAR(50) NOT NULL DEFAULT 'general',
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `site_settings_setting_key_unique` (`setting_key`),
  KEY `site_settings_setting_group_index` (`setting_group`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Seed course categories
-- ---------------------------------------------------------
INSERT INTO `course_categories` (`id`, `name`, `slug`, `description`, `icon`, `sort_order`, `status`, `created_at`, `updated_at`) VALUES
(1, 'Business & Management', 'business', 'Business, management and marketing programmes', 'ri-briefcase-line', 1, 1, NOW(), NOW()),
(2, 'Health & Social Care', 'health', 'Health, care and community support programmes', 'ri-heart-pulse-line', 2, 1, NOW(), NOW()),
(3, 'Computing & IT', 'computing', 'Computing, cyber security and data programmes', 'ri-computer-line', 3, 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `description`=VALUES(`description`), `updated_at`=NOW();

-- ---------------------------------------------------------
-- Seed courses
-- ---------------------------------------------------------
INSERT INTO `courses`
(`id`, `course_category_id`, `title`, `short_title`, `slug`, `badge`, `duration`, `study_mode`, `tuition_fee`, `intake`, `campuses_text`, `image`, `intro`, `overview`, `meta_title`, `meta_description`, `sort_order`, `status`, `created_at`, `updated_at`)
VALUES
(1, 1, 'BA (Hons) Business Management', 'Business Management', 'ba-hons-business-management', 'Bachelor''s Degree', '3 Years', 'Full-time / On Campus', '£9,790 per year', 'February, June, October', 'All Campuses', 'https://images.unsplash.com/photo-1454165804606-c3d57bc86b40?w=1200&h=600&fit=crop&q=80', 'An ideal insight into the fast-moving world of business today.', 'This programme suits those who prefer to learn a broad repertoire of business skills rather than focusing on one specialism.', 'BA (Hons) Business Management | FBA UK LTD', 'Study BA (Hons) Business Management with FBA UK LTD.', 1, 1, NOW(), NOW()),
(2, 2, 'BSc (Hons) Health & Social Care', 'Health & Social Care', 'bsc-hons-health-social-care', 'Bachelor''s Degree', '3 Years', 'Full-time / On Campus', 'From £9,790 per year', 'February, June, October', 'Birmingham, Cardiff', 'https://images.unsplash.com/photo-1576091160399-112ba8d25d1d?w=1200&h=600&fit=crop&q=80', 'Prepare for rewarding careers in healthcare and community support.', 'Practice-focused undergraduate degree for health and social care pathways.', 'BSc (Hons) Health & Social Care | FBA UK LTD', 'Study Health & Social Care with FBA UK LTD.', 2, 1, NOW(), NOW()),
(3, 3, 'BSc (Hons) Computing', 'Computing', 'bsc-hons-computing', 'Bachelor''s Degree', '3 Years', 'Full-time / On Campus', 'From £9,790 per year', 'February, June, October', 'London, Manchester', 'https://images.unsplash.com/photo-1517694712202-14dd9538aa97?w=1200&h=600&fit=crop&q=80', 'Master software development and emerging technologies.', 'Computing degree for the digital workplace.', 'BSc (Hons) Computing | FBA UK LTD', 'Study Computing with FBA UK LTD.', 3, 1, NOW(), NOW()),
(4, 3, 'BSc (Hons) Cyber Security', 'Cyber Security', 'bsc-hons-cyber-security', 'Bachelor''s Degree', '3 Years', 'Full-time / On Campus', 'From £9,790 per year', 'February, June, October', 'London, Birmingham', 'https://images.unsplash.com/photo-1550751827-4bd374c3f58b?w=1200&h=600&fit=crop&q=80', 'Protect organisations with cutting-edge security expertise.', 'Industry-aligned cyber security undergraduate programme.', 'BSc (Hons) Cyber Security | FBA UK LTD', 'Study Cyber Security with FBA UK LTD.', 4, 1, NOW(), NOW()),
(5, 3, 'BSc (Hons) Data Science', 'Data Science', 'bsc-hons-data-science', 'Bachelor''s Degree', '3 Years', 'Full-time / On Campus', 'From £9,790 per year', 'February, June, October', 'Manchester, London', 'https://images.unsplash.com/photo-1551288049-bebda4e38f71?w=1200&h=600&fit=crop&q=80', 'Harness big data, machine learning and analytics.', 'Data science programme for business intelligence careers.', 'BSc (Hons) Data Science | FBA UK LTD', 'Study Data Science with FBA UK LTD.', 5, 1, NOW(), NOW()),
(6, 1, 'BA (Hons) Digital Marketing', 'Digital Marketing', 'ba-hons-digital-marketing', 'Bachelor''s Degree', '3 Years', 'Full-time / On Campus', 'From £9,790 per year', 'February, June, October', 'All Campuses', 'https://images.unsplash.com/photo-1460925895917-afdab827c52f?w=1200&h=600&fit=crop&q=80', 'Learn SEO, social media and data-driven marketing.', 'Digital marketing degree for the modern marketplace.', 'BA (Hons) Digital Marketing | FBA UK LTD', 'Study Digital Marketing with FBA UK LTD.', 6, 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE `title`=VALUES(`title`), `intro`=VALUES(`intro`), `updated_at`=NOW();

-- ---------------------------------------------------------
-- Seed campus categories
-- ---------------------------------------------------------
INSERT INTO `campus_categories` (`id`, `name`, `slug`, `description`, `icon`, `sort_order`, `status`, `created_at`, `updated_at`) VALUES
(1, 'England', 'england', 'Campuses across England', 'ri-map-pin-line', 1, 1, NOW(), NOW()),
(2, 'Wales', 'wales', 'Campuses across Wales', 'ri-map-2-line', 2, 1, NOW(), NOW()),
(3, 'Online', 'online', 'Online and distance learning', 'ri-wifi-line', 3, 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `updated_at`=NOW();

-- ---------------------------------------------------------
-- Seed campuses
-- ---------------------------------------------------------
INSERT INTO `campuses`
(`id`, `campus_category_id`, `name`, `title`, `slug`, `badge`, `students`, `courses_count`, `image`, `intro`, `overview`, `address`, `phone`, `email`, `map_embed`, `latitude`, `longitude`, `meta_title`, `meta_description`, `sort_order`, `status`, `created_at`, `updated_at`)
VALUES
(1, 1, 'London', 'London Campus', 'london', 'Flagship Campus', '2,100+', '15 Courses', 'https://images.unsplash.com/photo-1513635269975-59663e0ac1ad?w=1200&h=600&fit=crop&q=80', 'Study in the heart of the UK capital.', 'London offers flexible study options and strong career opportunities.', '214 Whitechapel Road (2nd Floor), London, E1 1BJ', '02035811996', 'admissions@fbaukltd.com', NULL, '51.5155', '-0.0722', 'London Campus | FBA UK LTD', 'Explore the London campus with FBA UK LTD.', 1, 1, NOW(), NOW()),
(2, 1, 'Birmingham', 'Birmingham Campus', 'birmingham', 'Central England', '1,200+', '12 Courses', 'https://images.unsplash.com/photo-1605649487212-47bdab064df7?w=1200&h=600&fit=crop&q=80', 'Affordable living in a thriving student city.', 'Birmingham supports business, health and computing pathways.', '449A Stratford Road (1st Floor), Sparkhill, Birmingham, B11 4LD', '01216891425', 'admissions.bham@fbaukltd.com', NULL, '52.4560', '-1.8640', 'Birmingham Campus | FBA UK LTD', 'Explore the Birmingham campus with FBA UK LTD.', 2, 1, NOW(), NOW()),
(3, 1, 'Manchester', 'Manchester Campus', 'manchester', 'Northern Hub', '900+', '11 Courses', 'https://images.unsplash.com/photo-1570168007204-dfb528c6958f?w=1200&h=600&fit=crop&q=80', 'A dynamic northern city for computing and business.', 'Manchester offers creative and tech-focused study opportunities.', '998 Stockport Road, Levenshulme, Manchester, M19 3WN', '02039623456', 'admissions.manc@fbaukltd.com', NULL, '53.4440', '-2.1890', 'Manchester Campus | FBA UK LTD', 'Explore the Manchester campus with FBA UK LTD.', 3, 1, NOW(), NOW()),
(4, 2, 'Cardiff', 'Cardiff Campus', 'cardiff', 'Wales Capital', '800+', '10 Courses', 'https://images.unsplash.com/photo-1596464716127-f2a82984de30?w=1200&h=600&fit=crop&q=80', 'A welcoming capital-city campus experience.', 'Cardiff offers strong business and health study options.', '14 Splott Road, Cardiff, CF24 2BZ', '02922646566', 'admissions.wales@fbaukltd.com', NULL, '51.4816', '-3.1590', 'Cardiff Campus | FBA UK LTD', 'Explore the Cardiff campus with FBA UK LTD.', 4, 1, NOW(), NOW()),
(5, 2, 'Swansea', 'Swansea Campus', 'swansea', 'Coastal Campus', '450+', '8 Courses', 'https://images.unsplash.com/photo-1506905925346-21bda4d32df4?w=1200&h=600&fit=crop&q=80', 'Coastal living with accessible higher education routes.', 'Swansea offers a calm Welsh study environment.', 'Partner teaching locations across Swansea', '02039623456', 'admissions.wales@fbaukltd.com', NULL, '51.6214', '-3.9436', 'Swansea Campus | FBA UK LTD', 'Explore the Swansea campus with FBA UK LTD.', 5, 1, NOW(), NOW()),
(6, 3, 'Online / Distance', 'Online / Distance Learning', 'online-distance', 'Flexible Study', '1,500+', '10 Courses', 'https://images.unsplash.com/photo-1588196749597-9ff075ee6b5b?w=1200&h=600&fit=crop&q=80', 'Flexible online pathways for busy learners.', 'Study from anywhere with advisor-led support.', 'Supported via FBA UK national admissions team', '02039623456', 'admissions@fbaukltd.com', NULL, NULL, NULL, 'Online Learning | FBA UK LTD', 'Explore online and distance learning with FBA UK LTD.', 6, 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE `title`=VALUES(`title`), `intro`=VALUES(`intro`), `updated_at`=NOW();

-- ---------------------------------------------------------
-- Seed site settings
-- ---------------------------------------------------------
INSERT INTO `site_settings` (`setting_key`, `setting_value`, `setting_group`, `created_at`, `updated_at`) VALUES
('site_name', 'FBA UK LTD', 'general', NOW(), NOW()),
('site_tagline', 'Global Education Management', 'general', NOW(), NOW()),
('logo', 'frontend/assets/images/Fbalogo.webp', 'general', NOW(), NOW()),
('logo_dark', 'frontend/assets/images/Fbalogo.webp', 'general', NOW(), NOW()),
('favicon', 'frontend/assets/images/Fbalogo.webp', 'general', NOW(), NOW()),
('footer_about', 'FBA UK LTD is a leading UK education consultancy helping students access university degrees with expert guidance and student finance support.', 'general', NOW(), NOW()),
('company_registration', '07531804', 'general', NOW(), NOW()),
('address', '214 Whitechapel Road, E1 1BJ, London, United Kingdom', 'contact', NOW(), NOW()),
('phone_primary', '02039623456', 'contact', NOW(), NOW()),
('phone_secondary', '02035811996', 'contact', NOW(), NOW()),
('email_primary', 'info@fbaukltd.com', 'contact', NOW(), NOW()),
('email_admissions', 'admissions@fbaukltd.com', 'contact', NOW(), NOW()),
('whatsapp', '447000000000', 'contact', NOW(), NOW()),
('working_hours', 'Mon – Fri: 9:00 AM – 6:00 PM', 'contact', NOW(), NOW()),
('map_embed', '<iframe src="https://www.google.com/maps?q=214+Whitechapel+Road,+London+E1+1BJ&output=embed" width="100%" height="350" style="border:0;" allowfullscreen loading="lazy"></iframe>', 'map', NOW(), NOW()),
('map_latitude', '51.5155', 'map', NOW(), NOW()),
('map_longitude', '-0.0722', 'map', NOW(), NOW()),
('facebook_url', '#', 'social', NOW(), NOW()),
('instagram_url', '#', 'social', NOW(), NOW()),
('linkedin_url', '#', 'social', NOW(), NOW()),
('twitter_url', '#', 'social', NOW(), NOW()),
('youtube_url', '#', 'social', NOW(), NOW())
ON DUPLICATE KEY UPDATE `setting_value`=VALUES(`setting_value`), `updated_at`=NOW();

-- Contact page branches (JSON managed from Site Settings)
INSERT INTO `site_settings` (`setting_key`, `setting_value`, `setting_group`, `created_at`, `updated_at`) VALUES
('contact_branches', '[{"city":"London","country":"England","address":"214 Whitechapel Road (2nd Floor), London, E1 1BJ","phones":["m: 07404040404","m: 07404040405","t: 02039623456","t: 02035811996"],"email":"admissions@fbaukltd.com"},{"city":"Birmingham","country":"England","address":"449A Stratford Road (1st Floor), Sparkhill, Birmingham, B11 4LD","phones":["m: 07404040406","t: 01217772222"],"email":"admissions.bham@fbaukltd.com"}]', 'contact', NOW(), NOW())
ON DUPLICATE KEY UPDATE `setting_value`=VALUES(`setting_value`), `updated_at`=NOW();

-- =========================================================
-- 11) Course / Campus details content (modules, FAQ, key info)
-- Skip any column that already exists on your database.
-- =========================================================
ALTER TABLE `courses`
  ADD COLUMN `overview_json` LONGTEXT NULL AFTER `overview`,
  ADD COLUMN `modules_json` LONGTEXT NULL AFTER `overview_json` COMMENT 'HTML content for Course Modules tab (legacy structured JSON still supported on read)',
  ADD COLUMN `career_intro` TEXT NULL AFTER `modules_json`,
  ADD COLUMN `career_fields_json` LONGTEXT NULL AFTER `career_intro`,
  ADD COLUMN `key_info_json` LONGTEXT NULL AFTER `career_fields_json`,
  ADD COLUMN `faqs_json` LONGTEXT NULL AFTER `key_info_json`;

ALTER TABLE `courses`
  ADD COLUMN `enquiry_note` TEXT NULL AFTER `intro`;

ALTER TABLE `campuses`
  ADD COLUMN `overview_json` LONGTEXT NULL AFTER `overview`,
  ADD COLUMN `facilities_json` LONGTEXT NULL AFTER `overview_json`,
  ADD COLUMN `student_life` TEXT NULL AFTER `facilities_json`,
  ADD COLUMN `related_courses_json` LONGTEXT NULL AFTER `student_life`,
  ADD COLUMN `key_info_json` LONGTEXT NULL AFTER `related_courses_json`,
  ADD COLUMN `faqs_json` LONGTEXT NULL AFTER `key_info_json`;

-- =========================================================
-- 12) Breadcrumb / hero images (page + site default fallback)
-- =========================================================
ALTER TABLE `cms_pages`
  ADD COLUMN `breadcrumb_image` VARCHAR(255) NULL DEFAULT NULL AFTER `og_image`;

ALTER TABLE `courses`
  ADD COLUMN `breadcrumb_image` VARCHAR(255) NULL DEFAULT NULL AFTER `image`;

ALTER TABLE `campuses`
  ADD COLUMN `breadcrumb_image` VARCHAR(255) NULL DEFAULT NULL AFTER `image`;

INSERT INTO `site_settings` (`setting_key`, `setting_value`, `setting_group`, `created_at`, `updated_at`) VALUES
('default_breadcrumb_image', NULL, 'general', NOW(), NOW())
ON DUPLICATE KEY UPDATE `updated_at`=NOW();

-- Home page section CMS column
ALTER TABLE `cms_pages`
  ADD COLUMN `sections_json` LONGTEXT NULL AFTER `content`;

-- =========================================================
-- 13) FAQ + Blog modules (blog includes full SEO fields)
-- =========================================================
CREATE TABLE IF NOT EXISTS `faqs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `question` VARCHAR(500) NOT NULL,
  `answer` LONGTEXT NULL,
  `category` VARCHAR(100) NULL DEFAULT NULL,
  `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `status` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `blog_categories` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(191) NOT NULL,
  `slug` VARCHAR(191) NOT NULL,
  `description` TEXT NULL,
  `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `status` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `blog_categories_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `blogs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `blog_category_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `title` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(191) NOT NULL,
  `excerpt` TEXT NULL,
  `content` LONGTEXT NULL,
  `image` VARCHAR(255) NULL DEFAULT NULL,
  `breadcrumb_image` VARCHAR(255) NULL DEFAULT NULL,
  `author` VARCHAR(191) NULL DEFAULT NULL,
  `read_time` VARCHAR(50) NULL DEFAULT NULL,
  `published_at` TIMESTAMP NULL DEFAULT NULL,
  `meta_title` VARCHAR(255) NULL DEFAULT NULL,
  `meta_description` TEXT NULL,
  `meta_keywords` VARCHAR(500) NULL DEFAULT NULL,
  `meta_robots` VARCHAR(100) NOT NULL DEFAULT 'index,follow',
  `canonical_url` VARCHAR(255) NULL DEFAULT NULL,
  `og_title` VARCHAR(255) NULL DEFAULT NULL,
  `og_description` TEXT NULL,
  `og_image` VARCHAR(255) NULL DEFAULT NULL,
  `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `status` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `blogs_slug_unique` (`slug`),
  KEY `blogs_blog_category_id_foreign` (`blog_category_id`),
  CONSTRAINT `blogs_blog_category_id_foreign` FOREIGN KEY (`blog_category_id`) REFERENCES `blog_categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `testimonials` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(191) NOT NULL,
  `role` VARCHAR(191) NULL DEFAULT NULL,
  `tag` VARCHAR(100) NULL DEFAULT NULL,
  `duration` VARCHAR(20) NULL DEFAULT NULL,
  `initials` VARCHAR(10) NULL DEFAULT NULL,
  `quote` TEXT NULL,
  `video_type` VARCHAR(20) NOT NULL DEFAULT 'link',
  `video_url` VARCHAR(500) NULL DEFAULT NULL,
  `video_file` VARCHAR(255) NULL DEFAULT NULL,
  `image` VARCHAR(255) NULL DEFAULT NULL,
  `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `status` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `team_members` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(191) NOT NULL,
  `role` VARCHAR(191) NULL DEFAULT NULL,
  `bio` TEXT NULL,
  `image` VARCHAR(255) NULL DEFAULT NULL,
  `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `status` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `contact_enquiries` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(100) NOT NULL,
  `last_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(191) NOT NULL,
  `phone` VARCHAR(50) NOT NULL,
  `inquiry_type` VARCHAR(50) NOT NULL,
  `preferred_course` VARCHAR(100) NULL DEFAULT NULL,
  `message` TEXT NULL,
  `consent` TINYINT(1) NOT NULL DEFAULT 1,
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `ip_address` VARCHAR(45) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `eligibility_checks` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `answers` JSON NOT NULL,
  `answer_labels` JSON NULL,
  `score` INT NOT NULL DEFAULT 0,
  `min_score` INT NOT NULL DEFAULT 0,
  `is_eligible` TINYINT(1) NOT NULL DEFAULT 0,
  `result_key` VARCHAR(50) NOT NULL DEFAULT 'assessment',
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `ip_address` VARCHAR(45) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

