-- =============================================
-- Run this ENTIRE file in phpMyAdmin SQL tab
-- cy_consultancy database
-- =============================================

USE cy_consultancy;

-- 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
);

-- applications
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
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
);

-- 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
);

-- Add views to blog_posts if missing
ALTER TABLE blog_posts ADD COLUMN IF NOT EXISTS views INT DEFAULT 0;

-- Fix admin password (password: admin123)
-- The hash below is generated by PHP password_hash() for 'admin123'
UPDATE admin_users SET 
    email='cyconsultancybd@gmail.com'
WHERE username='admin';

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

SELECT 'All tables created successfully!' as Status;
