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

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(120) NOT NULL UNIQUE,
    phone VARCHAR(30) DEFAULT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(80) NOT NULL,
    icon VARCHAR(80) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    duration VARCHAR(40) NOT NULL,
    price VARCHAR(20) NOT NULL,
    icon VARCHAR(80) NOT NULL,
    description TEXT NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS stylists (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    role VARCHAR(120) NOT NULL,
    rating VARCHAR(10) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS offers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(120) NOT NULL,
    subtitle VARCHAR(180) NOT NULL,
    discount VARCHAR(40) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_name VARCHAR(120) NOT NULL,
    review TEXT NOT NULL,
    rating VARCHAR(10) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS portfolio_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(120) NOT NULL,
    subtitle VARCHAR(180) NOT NULL,
    type VARCHAR(40) NOT NULL,
    icon VARCHAR(80) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS branches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(120) NOT NULL,
    hours VARCHAR(120) NOT NULL,
    address VARCHAR(180) NOT NULL,
    contact VARCHAR(180) NOT NULL,
    is_primary TINYINT(1) NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS bookings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(120) NOT NULL,
    appointment_at VARCHAR(60) NOT NULL,
    status VARCHAR(30) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(120) NOT NULL,
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO categories (name, icon, sort_order) VALUES
('Hair', 'content_cut_rounded', 1),
('Makeup', 'brush_rounded', 2),
('Spa', 'spa_rounded', 3),
('Facial', 'face_retouching_natural', 4)
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO services (name, duration, price, icon, description, sort_order, is_active) VALUES
('Signature Haircut', '50 min', '$32', 'content_cut_rounded', 'Precision cut, wash, and salon finish tailored to your face shape.', 1, 1),
('Hair Spa & Repair', '70 min', '$48', 'spa_rounded', 'Deep nourishment ritual with scalp therapy, steam, and repair mask.', 2, 1),
('Bridal Glow Makeup', '90 min', '$95', 'auto_awesome_rounded', 'Long-wear radiant makeup with lashes and personalized glow finish.', 3, 1);

INSERT INTO stylists (name, role, rating, is_active) VALUES
('Ava Martin', 'Color Specialist', '4.9', 1),
('Noah James', 'Skin & Grooming', '4.8', 1);

INSERT INTO offers (title, subtitle, discount, sort_order, is_active) VALUES
('Weekday Glow', 'Hair spa plus blow-dry combo', '20% OFF', 1, 1),
('Bridal Trial', 'Book trial session before wedding week', 'SAVE 30', 2, 1);

INSERT INTO reviews (client_name, review, rating, sort_order, is_active) VALUES
('Maya K.', 'Clean booking flow and very professional staff. Loved the hair spa experience.', '4.9', 1, 1),
('Ritika S.', 'Bridal trial was smooth and the stylist understood my look instantly.', '5.0', 2, 1);

INSERT INTO portfolio_items (title, subtitle, type, icon, sort_order, is_active) VALUES
('Bridal Transformation', 'Makeup and hairstyle reveal reel', 'Video Demo', 'play_circle_fill_rounded', 1, 1),
('Hair Color Before/After', 'Premium balayage finishing result', 'Image Showcase', 'photo_library_rounded', 2, 1);

INSERT INTO branches (title, hours, address, contact, is_primary) VALUES
('Branch & Contact', 'Open daily from 9:00 AM to 9:00 PM', '12 Lake Road, Main City', '+880 1700-000000  |  hello@salonluxe.app', 1);

INSERT INTO bookings (title, appointment_at, status) VALUES
('Hair Spa & Repair', 'Thu 28, 11:30 AM', 'Upcoming'),
('Signature Haircut', 'Mon 18, 4:00 PM', 'Completed');

INSERT INTO notifications (title, message) VALUES
('Reminder', 'Your Hair Spa & Repair visit is tomorrow at 11:30 AM.'),
('Offer unlocked', 'You have a 20% weekday glow coupon available this week.');

INSERT INTO users (full_name, email, phone, password_hash) VALUES
('Demo Client', 'demo@salonluxe.app', '01700000000', '$2y$10$svSB0m7dB5s2ePEjmvYzu.BR5CKEM5Qn2ATmNnS/xP8Gr3HdWuLQ.')
ON DUPLICATE KEY UPDATE full_name = VALUES(full_name);