-- =====================================================
-- Ilmwave Backend Database Schema
-- =====================================================
-- This file contains the complete database schema for the Ilmwave backend
-- Import this file into phpMyAdmin to create all tables and initial data
-- 
-- Database: ilmwtbwr_maindb
-- Version: 1.0.0
-- Created: 2024
-- =====================================================

-- Create database (uncomment if needed)
-- CREATE DATABASE IF NOT EXISTS `ilmwtbwr_maindb` 
-- CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- USE ilmwtbwr_maindb;

-- =====================================================
-- Table: users
-- =====================================================
CREATE TABLE IF NOT EXISTS `users` (
  `id` varchar(36) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` varchar(255) NOT NULL DEFAULT 'admin',
  `firstName` varchar(255) DEFAULT NULL,
  `lastName` varchar(255) DEFAULT NULL,
  `isActive` tinyint(1) NOT NULL DEFAULT 1,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_user_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Table: home_page_content
-- =====================================================
CREATE TABLE IF NOT EXISTS `home_page_content` (
  `id` varchar(36) NOT NULL,
  `heroHeadline` text NOT NULL,
  `heroSubtitle` text NOT NULL,
  `heroPrimaryCta` json NOT NULL,
  `heroSecondaryCta` json NOT NULL,
  `heroBackgroundImage` varchar(500) DEFAULT NULL,
  `heroMetrics` json NOT NULL,
  `foundationTitle` varchar(255) NOT NULL,
  `foundationSubtitle` text NOT NULL,
  `foundationPillars` json NOT NULL,
  `seoMetaTitle` varchar(255) NOT NULL,
  `seoMetaDescription` text NOT NULL,
  `isPublished` tinyint(1) NOT NULL DEFAULT 0,
  `publishedAt` datetime DEFAULT NULL,
  `publishedBy` varchar(255) DEFAULT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Table: services_page_content
-- =====================================================
CREATE TABLE IF NOT EXISTS `services_page_content` (
  `id` varchar(36) NOT NULL,
  `heroHeadline` text NOT NULL,
  `heroSubtitle` text NOT NULL,
  `heroPrimaryCta` json NOT NULL,
  `heroSecondaryCta` json NOT NULL,
  `heroBackgroundImage` varchar(500) DEFAULT NULL,
  `heroStatistics` json NOT NULL,
  `educationHubsEyebrow` varchar(255) NOT NULL,
  `educationHubsHeadline` varchar(255) NOT NULL,
  `educationHubsSubtitle` text NOT NULL,
  `processEyebrow` varchar(255) NOT NULL,
  `processHeadline` varchar(255) NOT NULL,
  `processSubtitle` text NOT NULL,
  `processSteps` json NOT NULL,
  `mentorshipEyebrow` varchar(255) NOT NULL,
  `mentorshipHeadline` varchar(255) NOT NULL,
  `mentorshipDescription` text NOT NULL,
  `mentorshipImage` varchar(500) DEFAULT NULL,
  `mentorshipFeatures` json NOT NULL,
  `mentorshipStats` json NOT NULL,
  `mentorshipCta` json NOT NULL,
  `seoMetaTitle` varchar(255) NOT NULL,
  `seoMetaDescription` text NOT NULL,
  `isPublished` tinyint(1) NOT NULL DEFAULT 0,
  `publishedAt` datetime DEFAULT NULL,
  `publishedBy` varchar(255) DEFAULT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Table: destinations
-- =====================================================
CREATE TABLE IF NOT EXISTS `destinations` (
  `id` varchar(36) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `image` varchar(500) DEFAULT NULL,
  `features` json NOT NULL,
  `ctaText` varchar(255) NOT NULL,
  `isActive` tinyint(1) NOT NULL DEFAULT 1,
  `sortOrder` int(11) NOT NULL DEFAULT 0,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_destination_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Table: publish_history
-- =====================================================
CREATE TABLE IF NOT EXISTS `publish_history` (
  `id` varchar(36) NOT NULL,
  `pages` json NOT NULL,
  `environment` varchar(255) NOT NULL,
  `status` varchar(255) NOT NULL,
  `errorMessage` text DEFAULT NULL,
  `publishedBy` varchar(255) NOT NULL,
  `publishedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Default Data Insertion
-- =====================================================

-- Insert default admin user
-- Password: admin123456 (hashed with bcrypt)
INSERT INTO `users` (`id`, `email`, `password`, `firstName`, `lastName`, `role`, `isActive`) VALUES
('admin-user-uuid-1234567890', 'admin@ilmwave.com', '$2a$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/DbGWGNFz.wxETqWyS', 'Admin', 'User', 'admin', 1)
ON DUPLICATE KEY UPDATE email = email;

-- Insert default destinations
INSERT INTO `destinations` (`id`, `slug`, `title`, `description`, `features`, `ctaText`, `isActive`, `sortOrder`) VALUES
('dest-uk-uuid-1234567890', 'uk', 'Study in UK', 'The UK remains a top choice for international students offering world-renowned universities and post-study work opportunities.', 
 JSON_ARRAY('University Admissions', 'Tier 4 Student Visa', 'Graduate Route Guidance', 'Pre-Departure Briefings'), 
 'Inquire for UK →', 1, 1),

('dest-schengen-uuid-1234567890', 'schengen', 'Schengen Study', 'Access high-quality education across Europe with the flexibility of the Schengen Area, including Germany, France, and Italy.', 
 JSON_ARRAY('Schengen Student Visa Filing', 'Document Apostille Assistance', 'Language Certification Support', 'Accommodation & Health Cover'), 
 'Explore Schengen Options →', 1, 2),

('dest-canada-uuid-1234567890', 'canada', 'Study in Canada', 'World-class education with excellent post-study work opportunities and pathways to permanent residence.', 
 JSON_ARRAY('Study Permit Applications', 'Post-Graduation Work Permit', 'Provincial Nominee Programs', 'Express Entry Guidance'), 
 'Discover Canada →', 1, 3)

ON DUPLICATE KEY UPDATE slug = slug;

-- Insert default home page content
INSERT INTO `home_page_content` (`id`, `heroHeadline`, `heroSubtitle`, `heroPrimaryCta`, `heroSecondaryCta`, `heroMetrics`, `foundationTitle`, `foundationSubtitle`, `foundationPillars`, `seoMetaTitle`, `seoMetaDescription`, `isPublished`) VALUES
('home-page-uuid-1234567890',
'Empowering Your Global Educational Journey',
'Ilmwave provides institutional-grade consultancy for students and professionals seeking international opportunities in the UK, Schengen, and Canada.',
JSON_OBJECT('label', 'Start Assessment', 'link', '/assessment'),
JSON_OBJECT('label', 'View Services', 'link', '/services'),
JSON_ARRAY(
  JSON_OBJECT('value', '98%', 'label', 'Visa Success Rate'),
  JSON_OBJECT('value', '500+', 'label', 'Partner Institutions'),
  JSON_OBJECT('value', '12,000+', 'label', 'Students Placed')
),
'A Foundation of Professional Excellence',
'Ilmwave is built on the principles of transparency, integrity, and global academic success. We provide end-to-end guidance for your international journey.',
JSON_ARRAY(
  JSON_OBJECT('id', 1, 'icon', 'Shield', 'title', 'Regulated Advisory', 'description', 'Licensed consultants with deep knowledge of international immigration laws and university requirements.'),
  JSON_OBJECT('id', 2, 'icon', 'FastForward', 'title', 'Streamlined Process', 'description', 'Optimized application workflows designed to reduce waiting times and maximize your chances of approval.'),
  JSON_OBJECT('id', 3, 'icon', 'School', 'title', 'Academic Precision', 'description', 'Personalized university selection based on your profile, career goals, and financial preferences.'),
  JSON_OBJECT('id', 4, 'icon', 'Headphones', 'title', 'Ongoing Support', 'description', 'We do not just stop at visas; we assist with accommodation, travel, and post-arrival integration.')
),
'Ilmwave | Education & Visa Consultancy',
'Leading education consultancy for UK and Schengen visas, personalized admissions, and career mentoring.',
1)
ON DUPLICATE KEY UPDATE id = id;

-- Insert default services page content  
INSERT INTO `services_page_content` (`id`, `heroHeadline`, `heroSubtitle`, `heroPrimaryCta`, `heroSecondaryCta`, `heroStatistics`, `educationHubsEyebrow`, `educationHubsHeadline`, `educationHubsSubtitle`, `processEyebrow`, `processHeadline`, `processSubtitle`, `processSteps`, `mentorshipEyebrow`, `mentorshipHeadline`, `mentorshipDescription`, `mentorshipFeatures`, `mentorshipStats`, `mentorshipCta`, `seoMetaTitle`, `seoMetaDescription`, `isPublished`) VALUES
('services-page-uuid-1234567890',
'Navigate Your Global Education Journey',
'Expert consultancy services tailored for ambitious students seeking world-class education in the UK and Schengen countries. We simplify the complex visa and admission processes.',
JSON_OBJECT('label', 'Get a Free Assessment', 'link', '#assessment'),
JSON_OBJECT('label', 'View Success Stories', 'link', '#stories'),
JSON_ARRAY('15K+ Students', '95% Success Rate', '50+ Countries'),
'Our Destinations',
'Global Education Hubs',
'Explore our specialized services for the most prestigious educational destinations in the world.',
'Our Methodology',
'A Seamless 5-Step Process',
'We follow a rigorous, transparent methodology to ensure your application has the highest chance of success.',
JSON_ARRAY(
  JSON_OBJECT('step', '01', 'title', 'Profile Evaluation', 'description', 'Deep dive into your academic history and goals to find the best fit.'),
  JSON_OBJECT('step', '02', 'title', 'Strategic Planning', 'description', 'Creating a timeline for tests (IELTS/GRE), applications, and documentation.'),
  JSON_OBJECT('step', '03', 'title', 'Documentation', 'description', 'Expert drafting of SOPs, LORs, and meticulous review of all financial files.'),
  JSON_OBJECT('step', '04', 'title', 'Visa Lodgment', 'description', 'Careful submission of your visa application with thorough interview prep.'),
  JSON_OBJECT('step', '05', 'title', 'Post-Arrival Support', 'description', 'On-ground guidance including airport pick-up and initial settling assistance in your host country.')
),
'Personalized Mentorship',
'Beyond Just Applications',
'We do not just process papers; we build futures. Our counseling services are designed to empower students with long-term career insights.',
JSON_ARRAY(
  'One-on-one counseling sessions',
  'Document preparation assistance',
  '24/7 support throughout the process',
  'Post-arrival guidance and support'
),
JSON_ARRAY(
  JSON_OBJECT('title', '98% Visa Success Rate', 'description', 'Our meticulous review process ensures that every detail is perfect before submission, minimizing the risk of rejection.'),
  JSON_OBJECT('title', 'Lifetime Community Access', 'description', 'Join a network of successful Ilmwave alumni across Europe and the UK for networking and peer support.')
),
JSON_OBJECT(
  'headline', 'Ready to Start Your International Journey?',
  'subtitle', 'Do not leave your dreams to chance. Partner with Ilmwave for expert guidance that delivers results. Our advisors are ready to help you take the first step.',
  'primaryButton', 'Book Initial Consultation',
  'secondaryButton', 'Return to Home'
),
'Study Abroad & Visa Services | Ilmwave UK & Europe',
'Comprehensive guidance for Tier 4 UK visas, Schengen university admissions, and international career mentorship tailored for ambitious students.',
1)
ON DUPLICATE KEY UPDATE id = id;

-- =====================================================
-- Indexes for Performance
-- =====================================================

-- Additional indexes for better query performance
CREATE INDEX `idx_users_email_active` ON `users` (`email`, `isActive`);
CREATE INDEX `idx_destinations_active_sort` ON `destinations` (`isActive`, `sortOrder`);
CREATE INDEX `idx_home_published` ON `home_page_content` (`isPublished`, `updatedAt`);
CREATE INDEX `idx_services_published` ON `services_page_content` (`isPublished`, `updatedAt`);
CREATE INDEX `idx_publish_history_date` ON `publish_history` (`publishedAt` DESC);
CREATE INDEX `idx_publish_history_status` ON `publish_history` (`status`, `publishedAt`);

-- =====================================================
-- Database Setup Complete
-- =====================================================
-- 
-- 🎉 Database schema created successfully!
-- 
-- Default Admin Credentials:
--   Email: admin@ilmwave.com
--   Password: admin123456
-- 
-- Default Destinations Added:
--   - UK (Study in UK)
--   - Schengen (Schengen Study)  
--   - Canada (Study in Canada)
-- 
-- Default Content Pages:
--   - Home Page (with sample content)
--   - Services Page (with sample content)
-- 
-- =====================================================