-- CY Consultancy Admin Panel — Database Setup
-- Run this SQL in phpMyAdmin or MySQL CLI

CREATE DATABASE IF NOT EXISTS cy_consultancy CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE cy_consultancy;

-- Admin Users
CREATE TABLE IF NOT EXISTS admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    email VARCHAR(100),
    role ENUM('superadmin','editor') DEFAULT 'editor',
    last_login DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default admin (password: admin123)
INSERT INTO admin_users (username, password, full_name, email, role)
VALUES ('admin', '$2y$10$TKh8H1.PfQx37YgCzwiKb.KjNyWgaHb9cbcoQgdIU380madKc.TC.', 'CY Admin', 'admin@cyconsultancy.com', 'superadmin')
ON DUPLICATE KEY UPDATE id=id;

-- If admin already exists, run this to reset password to admin123:
-- UPDATE admin_users SET password='$2y$10$TKh8H1.PfQx37YgCzwiKb.KjNyWgaHb9cbcoQgdIU380madKc.TC.' WHERE username='admin';

-- Pages (for static pages like Home, About, Services, etc.)
CREATE TABLE IF NOT EXISTS pages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    meta_title VARCHAR(250),
    meta_desc TEXT,
    content LONGTEXT,
    hero_heading VARCHAR(300),
    hero_subheading TEXT,
    hero_bg_image VARCHAR(500),
    status ENUM('published','draft') DEFAULT 'published',
    created_by INT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Blog Posts
CREATE TABLE IF NOT EXISTS blog_posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(300) NOT NULL,
    slug VARCHAR(300) NOT NULL UNIQUE,
    category VARCHAR(100),
    excerpt TEXT,
    content LONGTEXT,
    featured_image VARCHAR(500),
    author VARCHAR(100) DEFAULT 'CY Consultancy',
    status ENUM('published','draft') DEFAULT 'published',
    views INT DEFAULT 0,
    published_at DATETIME,
    created_by INT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Services
CREATE TABLE IF NOT EXISTS services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    icon_class VARCHAR(100),
    short_desc TEXT,
    full_desc LONGTEXT,
    image VARCHAR(500),
    sort_order INT DEFAULT 0,
    status ENUM('active','inactive') DEFAULT 'active',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Destinations / Countries
CREATE TABLE IF NOT EXISTS destinations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    country_name VARCHAR(100) NOT NULL,
    flag_emoji VARCHAR(10),
    description TEXT,
    content LONGTEXT,
    image VARCHAR(500),
    universities_count INT DEFAULT 0,
    sort_order INT DEFAULT 0,
    status ENUM('active','inactive') DEFAULT 'active',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Universities
CREATE TABLE IF NOT EXISTS universities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    country VARCHAR(100),
    destination_id INT,
    description TEXT,
    logo VARCHAR(500),
    website VARCHAR(300),
    sort_order INT DEFAULT 0,
    status ENUM('active','inactive') DEFAULT 'active',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Success Stories
CREATE TABLE IF NOT EXISTS success_stories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100),
    destination VARCHAR(100),
    university VARCHAR(200),
    photo VARCHAR(500),
    testimonial TEXT,
    year YEAR,
    sort_order INT DEFAULT 0,
    status ENUM('active','inactive') DEFAULT 'active',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Gallery
CREATE TABLE IF NOT EXISTS gallery (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    image VARCHAR(500) NOT NULL,
    category VARCHAR(100) DEFAULT 'general',
    sort_order INT DEFAULT 0,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Contact Messages
CREATE TABLE IF NOT EXISTS contact_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100),
    email VARCHAR(150),
    phone VARCHAR(30),
    subject VARCHAR(200),
    message TEXT,
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Site Settings
CREATE TABLE IF NOT EXISTS site_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO site_settings (setting_key, setting_value) VALUES
('site_name', 'CY Consultancy Centre'),
('site_email', 'info@cyconsultancy.com'),
('site_phone', '+880 1711-351367'),
('site_address', 'Dhaka, Bangladesh'),
('whatsapp', '8801711351367'),
('facebook', 'https://facebook.com/cyconsultancy'),
('footer_text', '© 2024 CY Consultancy Centre. All rights reserved.'),
('hero_heading', 'Your Gateway to Global Education'),
('hero_subheading', 'Established in 2008, Bangladesh most trusted study abroad partner.')
ON DUPLICATE KEY UPDATE id=id;

-- Password Reset OTP Table
CREATE TABLE IF NOT EXISTS admin_password_resets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    otp VARCHAR(6) NOT NULL,
    expires_at DATETIME NOT NULL,
    used TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Student Applications (from frontend form)
CREATE TABLE IF NOT EXISTS applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    mobile VARCHAR(30) NOT NULL,
    email VARCHAR(150),
    country VARCHAR(100),
    education VARCHAR(100),
    grad_year VARCHAR(10),
    gpa VARCHAR(20),
    english_test VARCHAR(30),
    message TEXT,
    status ENUM('new','contacted','processing','completed','rejected') DEFAULT 'new',
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Testimonials (student reviews)
CREATE TABLE IF NOT EXISTS testimonials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    destination VARCHAR(100),
    university VARCHAR(200),
    review TEXT NOT NULL,
    photo VARCHAR(500),
    avatar_color VARCHAR(30) DEFAULT '#1a56db',
    flag_emoji VARCHAR(10),
    rating TINYINT DEFAULT 5,
    sort_order INT DEFAULT 0,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default testimonials
INSERT INTO testimonials (student_name,destination,university,review,flag_emoji,avatar_color,status) VALUES
('Rahim Uddin','Malta','MCAST','CY Consultancy made my Malta dream a reality! The entire process from application to visa was handled professionally.','🇲🇹','#e76f51','active'),
('Sumaiya Ahmed','UK','University of London','Excellent service! They helped me get into a top UK university. The counselors are very knowledgeable.','🇬🇧','#1a56db','active'),
('Karim Hossain','Canada','Toronto College','I was nervous about studying abroad but CY guided me every step. My visa was approved in record time.','🇨🇦','#059669','active'),
('Nadia Islam','Cyprus','University of Cyprus','From Cyprus visa to university enrollment — CY handled everything flawlessly.','🇨🇾','#7c3aed','active'),
('Tanvir Rahman','Australia','Melbourne Institute','Best decision of my life! CY got my Australian student visa within 3 months.','🇦🇺','#d97706','active'),
('Fatema Khatun','Netherlands','Amsterdam University','CY Consultancy helped me secure admission in Netherlands. Amazing Schengen visa expertise!','🇳🇱','#e76f51','active')
ON DUPLICATE KEY UPDATE id=id;

-- Team Members
CREATE TABLE IF NOT EXISTS team_members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    role VARCHAR(100),
    bio TEXT,
    photo VARCHAR(500),
    facebook VARCHAR(300),
    linkedin VARCHAR(300),
    sort_order INT DEFAULT 0,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Site Content Blocks (editable homepage sections)
CREATE TABLE IF NOT EXISTS content_blocks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    block_key VARCHAR(100) NOT NULL UNIQUE,
    title VARCHAR(300),
    subtitle VARCHAR(500),
    body LONGTEXT,
    image VARCHAR(500),
    button_text VARCHAR(100),
    button_url VARCHAR(300),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default content blocks
INSERT INTO content_blocks (block_key, title, subtitle, body) VALUES
('hero_heading','Your Gateway to Global Education','Established in 2008, Bangladesh\'s most trusted study abroad partner',''),
('about_heading','A Trusted Name in International Education','We specialize in International Education, offering fully-comprehensive consulting services','CY Consultancy Centre is a registered and licensed Bangladeshi company with head office in Dhaka. Our team of young professionals has helped 500+ Bangladeshi students gain admission to universities in Malta, UK, Canada, Australia, and beyond.'),
('cta_heading','Ready to Start Your Study Abroad Journey?','Register for a free consultation — our experts are ready',''),
('why_us_heading','Why Choose CY Consultancy?','17+ years of excellence in international education consulting','')
ON DUPLICATE KEY UPDATE id=id;
