-- Salon/Spa/Beauty Parlor Booking & Management System Database Schema
-- Multi-tenant system supporting companies, branches, agents, and customers

-- IMPORTANT: Create the database manually first, or ensure your MySQL user has CREATE DATABASE privileges
-- Run this SQL in your existing database (replace 'salon_system' with your actual database name)
-- Example commands:
-- MySQL Command Line: CREATE DATABASE salon_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Or use phpMyAdmin/MySQL Workbench to create the database first

-- If you have CREATE DATABASE privileges, uncomment the lines below:
-- CREATE DATABASE IF NOT EXISTS salon_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE salon_system;

-- ===========================================
-- CORE SYSTEM TABLES
-- ===========================================

-- Companies (Salon Brands)
CREATE TABLE companies (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    description TEXT,
    logo VARCHAR(500),
    favicon VARCHAR(500),
    primary_color VARCHAR(7) DEFAULT '#C9A86A',
    secondary_color VARCHAR(7) DEFAULT '#2D3748',
    website VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(50),
    whatsapp_number VARCHAR(50),
    address TEXT,
    timezone VARCHAR(50) DEFAULT 'UTC',
    currency VARCHAR(3) DEFAULT 'USD',
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_slug (slug),
    INDEX idx_status (status)
);

-- Branches (Locations)
CREATE TABLE branches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    address TEXT NOT NULL,
    city VARCHAR(100),
    state VARCHAR(100),
    country VARCHAR(100),
    postal_code VARCHAR(20),
    phone VARCHAR(50),
    email VARCHAR(255),
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    opening_hours JSON, -- Store as JSON: {"monday": {"open": "09:00", "close": "18:00"}, ...}
    timezone VARCHAR(50) DEFAULT 'UTC',
    is_main BOOLEAN DEFAULT FALSE,
    status ENUM('active', 'inactive', 'maintenance') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    UNIQUE KEY unique_branch_slug (company_id, slug),
    INDEX idx_company_status (company_id, status),
    INDEX idx_location (latitude, longitude)
);

-- Users (Customers, Staff, Admins, Agents)
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NULL, -- NULL for super admins and agents
    branch_id INT NULL, -- NULL for company admins and agents
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone VARCHAR(50),
    avatar VARCHAR(500),
    role ENUM('super_admin', 'company_admin', 'manager', 'staff', 'agent', 'customer') DEFAULT 'customer',
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    email_verified BOOLEAN DEFAULT FALSE,
    phone_verified BOOLEAN DEFAULT FALSE,
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_role_status (role, status),
    INDEX idx_company_branch (company_id, branch_id),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL
);

-- Roles and Permissions
CREATE TABLE roles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    company_id INT NULL, -- NULL for global roles
    permissions JSON, -- Store permissions as JSON array
    is_system BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company (company_id),
    INDEX idx_slug (slug)
);

-- User Roles (Many-to-many relationship)
CREATE TABLE user_roles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    assigned_by INT NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_by) REFERENCES users(id),
    UNIQUE KEY unique_user_role (user_id, role_id)
);

-- ===========================================
-- SERVICES AND BOOKING SYSTEM
-- ===========================================

-- Service Categories
CREATE TABLE service_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NULL, -- NULL for global categories
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    description TEXT,
    image VARCHAR(500),
    icon VARCHAR(100),
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    UNIQUE KEY unique_category_slug (company_id, slug),
    INDEX idx_company_active (company_id, is_active),
    INDEX idx_sort_order (sort_order)
);

-- Services
CREATE TABLE services (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    category_id INT,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    description LONGTEXT,
    short_description TEXT, -- SEO excerpt, ~20+ words
    price DECIMAL(10, 2) NOT NULL,
    duration_minutes INT NOT NULL, -- Service duration
    buffer_before_minutes INT DEFAULT 0, -- Prep time before service
    buffer_after_minutes INT DEFAULT 0, -- Cleanup time after service
    max_bookings_per_slot INT DEFAULT 1, -- For group services
    is_active BOOLEAN DEFAULT TRUE,
    is_featured BOOLEAN DEFAULT FALSE,
    requires_staff BOOLEAN DEFAULT TRUE,
    images JSON, -- Gallery images as JSON array
    seo_title VARCHAR(255),
    seo_description TEXT,
    seo_keywords VARCHAR(255),
    schema_data JSON, -- Schema.org structured data
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES service_categories(id) ON DELETE SET NULL,
    INDEX idx_company_active (company_id, is_active),
    INDEX idx_category (category_id),
    INDEX idx_featured (is_featured),
    INDEX idx_slug (slug)
);

-- Service Variants/Add-ons
CREATE TABLE service_variants (
    id INT PRIMARY KEY AUTO_INCREMENT,
    service_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price_modifier DECIMAL(10, 2) DEFAULT 0, -- Additional cost
    duration_modifier INT DEFAULT 0, -- Additional time in minutes
    is_required BOOLEAN DEFAULT FALSE,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE,
    INDEX idx_service (service_id),
    INDEX idx_required (is_required)
);

-- Staff Services (Many-to-many: which staff can perform which services)
CREATE TABLE staff_services (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    service_id INT NOT NULL,
    commission_rate DECIMAL(5, 2) DEFAULT 0, -- Percentage commission
    is_primary BOOLEAN DEFAULT FALSE, -- Primary service for this staff
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE,
    UNIQUE KEY unique_staff_service (user_id, service_id),
    INDEX idx_service (service_id)
);

-- Staff Availability Schedule
CREATE TABLE staff_availability (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    branch_id INT NOT NULL,
    day_of_week TINYINT NOT NULL, -- 0=Sunday, 1=Monday, ..., 6=Saturday
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    is_available BOOLEAN DEFAULT TRUE,
    break_start TIME NULL,
    break_end TIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE,
    UNIQUE KEY unique_staff_branch_day (user_id, branch_id, day_of_week),
    INDEX idx_day_available (day_of_week, is_available)
);

-- Staff Time Off / Leave
CREATE TABLE staff_leave (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    branch_id INT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    reason TEXT,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    approved_by INT NULL,
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE,
    FOREIGN KEY (approved_by) REFERENCES users(id),
    INDEX idx_user_dates (user_id, start_date, end_date),
    INDEX idx_status (status)
);

-- ===========================================
-- BOOKING SYSTEM
-- ===========================================

-- Bookings
CREATE TABLE bookings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    booking_number VARCHAR(20) UNIQUE NOT NULL, -- Human-readable booking ID
    company_id INT NOT NULL,
    branch_id INT NOT NULL,
    customer_id INT NULL, -- NULL for guest bookings
    service_id INT NOT NULL,
    staff_id INT NULL, -- Assigned staff
    start_at DATETIME NOT NULL,
    end_at DATETIME NOT NULL,
    status ENUM('pending', 'confirmed', 'completed', 'cancelled', 'no_show', 'rescheduled') DEFAULT 'pending',
    total_amount DECIMAL(10, 2) NOT NULL,
    notes TEXT,
    customer_notes TEXT,
    cancellation_reason TEXT,
    cancelled_at TIMESTAMP NULL,
    cancelled_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (service_id) REFERENCES services(id),
    FOREIGN KEY (staff_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (cancelled_by) REFERENCES users(id),
    INDEX idx_company_branch (company_id, branch_id),
    INDEX idx_customer (customer_id),
    INDEX idx_staff (staff_id),
    INDEX idx_service (service_id),
    INDEX idx_status (status),
    INDEX idx_start_at (start_at),
    INDEX idx_booking_number (booking_number)
);

-- Booking Variants (Selected add-ons)
CREATE TABLE booking_variants (
    id INT PRIMARY KEY AUTO_INCREMENT,
    booking_id INT NOT NULL,
    variant_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
    FOREIGN KEY (variant_id) REFERENCES service_variants(id) ON DELETE CASCADE,
    UNIQUE KEY unique_booking_variant (booking_id, variant_id)
);

-- Guest Customer Details (for non-registered bookings)
CREATE TABLE guest_customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    booking_id INT NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
    INDEX idx_email (email),
    INDEX idx_booking (booking_id)
);

-- ===========================================
-- PAYMENTS AND FINANCE
-- ===========================================

-- Payment Methods
CREATE TABLE payment_methods (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    type ENUM('cash', 'mpesa', 'card', 'bank_transfer', 'other') NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    config JSON, -- Payment gateway config
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_company_active (company_id, is_active)
);


-- Payments
CREATE TABLE payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    booking_id INT NULL,
    order_id INT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_method_id INT NOT NULL,
    transaction_id VARCHAR(255), -- External transaction reference
    status ENUM('pending', 'completed', 'failed', 'refunded', 'cancelled') DEFAULT 'pending',
    payment_date TIMESTAMP NULL,
    notes TEXT,
    processed_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE SET NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
    FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id),
    FOREIGN KEY (processed_by) REFERENCES users(id),
    INDEX idx_booking (booking_id),
    INDEX idx_order (order_id),
    INDEX idx_status (status),
    INDEX idx_transaction (transaction_id)
);

-- Expenses
CREATE TABLE expenses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    branch_id INT NULL,
    category VARCHAR(100) NOT NULL,
    description TEXT,
    amount DECIMAL(10, 2) NOT NULL,
    expense_date DATE NOT NULL,
    receipt VARCHAR(500), -- Receipt image path
    recorded_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL,
    FOREIGN KEY (recorded_by) REFERENCES users(id),
    INDEX idx_company_branch (company_id, branch_id),
    INDEX idx_date (expense_date),
    INDEX idx_category (category)
);

-- ===========================================
-- SHOP/E-COMMERCE MODULE
-- ===========================================

-- Product Categories
CREATE TABLE product_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NULL,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    description TEXT,
    image VARCHAR(500),
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    UNIQUE KEY unique_category_slug (company_id, slug),
    INDEX idx_company_active (company_id, is_active)
);

-- Products
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    category_id INT,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    description LONGTEXT,
    short_description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    sale_price DECIMAL(10, 2) NULL,
    stock_quantity INT DEFAULT 0,
    sku VARCHAR(100) UNIQUE,
    images JSON,
    is_active BOOLEAN DEFAULT TRUE,
    is_featured BOOLEAN DEFAULT FALSE,
    weight DECIMAL(8, 2) NULL,
    dimensions JSON, -- Width, height, depth
    seo_title VARCHAR(255),
    seo_description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES product_categories(id) ON DELETE SET NULL,
    INDEX idx_company_active (company_id, is_active),
    INDEX idx_category (category_id),
    INDEX idx_featured (is_featured),
    INDEX idx_slug (slug)
);

-- Orders
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(20) UNIQUE NOT NULL,
    company_id INT NOT NULL,
    customer_id INT NULL,
    guest_email VARCHAR(255),
    guest_first_name VARCHAR(100),
    guest_last_name VARCHAR(100),
    guest_phone VARCHAR(50),
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    subtotal DECIMAL(10, 2) NOT NULL,
    tax_amount DECIMAL(10, 2) DEFAULT 0,
    shipping_amount DECIMAL(10, 2) DEFAULT 0,
    total_amount DECIMAL(10, 2) NOT NULL,
    shipping_address JSON,
    billing_address JSON,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_company (company_id),
    INDEX idx_customer (customer_id),
    INDEX idx_status (status),
    INDEX idx_order_number (order_number)
);

-- Order Items
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL, -- Price at time of order
    total DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id),
    INDEX idx_order (order_id),
    INDEX idx_product (product_id)
);

-- ===========================================
-- MESSAGING AND NOTIFICATIONS
-- ===========================================

-- Notification Templates
CREATE TABLE notification_templates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NULL,
    name VARCHAR(255) NOT NULL,
    type ENUM('email', 'sms', 'whatsapp', 'push') NOT NULL,
    event VARCHAR(100) NOT NULL, -- booking_confirmed, booking_reminder, etc.
    subject VARCHAR(255),
    content LONGTEXT NOT NULL,
    variables JSON, -- Available template variables
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_company_type (company_id, type),
    INDEX idx_event (event)
);

-- Notifications
CREATE TABLE notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    recipient_id INT NULL, -- User ID
    recipient_email VARCHAR(255),
    recipient_phone VARCHAR(50),
    type ENUM('email', 'sms', 'whatsapp', 'push', 'in_app') NOT NULL,
    template_id INT NULL,
    subject VARCHAR(255),
    content LONGTEXT NOT NULL,
    status ENUM('queued', 'sent', 'delivered', 'failed') DEFAULT 'queued',
    sent_at TIMESTAMP NULL,
    error_message TEXT,
    related_booking_id INT NULL,
    related_order_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (recipient_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (template_id) REFERENCES notification_templates(id),
    FOREIGN KEY (related_booking_id) REFERENCES bookings(id) ON DELETE SET NULL,
    FOREIGN KEY (related_order_id) REFERENCES orders(id) ON DELETE SET NULL,
    INDEX idx_recipient (recipient_id),
    INDEX idx_status (status),
    INDEX idx_type (type),
    INDEX idx_related_booking (related_booking_id)
);

-- ===========================================
-- SUPPORT CHAT SYSTEM
-- ===========================================

-- Support Tickets
CREATE TABLE support_tickets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ticket_number VARCHAR(20) UNIQUE NOT NULL,
    company_id INT NOT NULL,
    customer_id INT NULL,
    guest_name VARCHAR(255),
    guest_email VARCHAR(255),
    subject VARCHAR(255) NOT NULL,
    status ENUM('open', 'in_progress', 'waiting_customer', 'resolved', 'closed') DEFAULT 'open',
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    department VARCHAR(100),
    assigned_to INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_company (company_id),
    INDEX idx_customer (customer_id),
    INDEX idx_assigned (assigned_to),
    INDEX idx_status (status)
);

-- Chat Messages
CREATE TABLE chat_messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ticket_id INT NOT NULL,
    sender_id INT NULL, -- NULL for customer/guest messages
    sender_name VARCHAR(255), -- For guest messages
    message LONGTEXT NOT NULL,
    message_type ENUM('text', 'image', 'file') DEFAULT 'text',
    attachment VARCHAR(500),
    is_read BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_ticket (ticket_id),
    INDEX idx_sender (sender_id),
    INDEX idx_read (is_read)
);

-- ===========================================
-- ANALYTICS AND REPORTS
-- ===========================================

-- Page Views and Analytics
CREATE TABLE page_views (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NULL,
    session_id VARCHAR(255),
    user_id INT NULL,
    page_url VARCHAR(500) NOT NULL,
    page_title VARCHAR(255),
    referrer VARCHAR(500),
    user_agent TEXT,
    ip_address VARCHAR(45),
    viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_company_date (company_id, viewed_at),
    INDEX idx_user (user_id),
    INDEX idx_page (page_url(255))
);

-- Settings (Key-Value store for various configurations)
CREATE TABLE settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NULL, -- NULL for global settings
    branch_id INT NULL, -- NULL for company-wide settings
    setting_key VARCHAR(255) NOT NULL,
    setting_value LONGTEXT,
    setting_type ENUM('string', 'number', 'boolean', 'json') DEFAULT 'string',
    is_public BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE,
    UNIQUE KEY unique_setting (company_id, branch_id, setting_key),
    INDEX idx_key (setting_key),
    INDEX idx_public (is_public)
);

-- ===========================================
-- SEED DATA
-- ===========================================

-- Insert system roles
INSERT INTO roles (name, slug, description, permissions, is_system) VALUES
('Super Administrator', 'super_admin', 'Full system access', '["*"]', TRUE),
('Company Administrator', 'company_admin', 'Company-wide administration', '["manage_company", "manage_branches", "manage_staff", "manage_services", "manage_bookings", "view_reports", "manage_settings"]', TRUE),
('Manager', 'manager', 'Branch management', '["manage_branch_staff", "manage_bookings", "view_branch_reports", "manage_inventory"]', TRUE),
('Staff Member', 'staff', 'Service provider', '["view_schedule", "update_booking_status", "view_assigned_bookings"]', TRUE),
('Agent', 'agent', 'Multi-company agent', '["manage_assigned_companies", "view_reports"]', TRUE),
('Customer', 'customer', 'End customer', '["book_services", "view_own_bookings", "manage_profile"]', TRUE);

-- Sample company
INSERT INTO companies (name, slug, description, primary_color, secondary_color, email, phone, whatsapp_number, address, timezone, currency) VALUES
('Luxury Beauty Spa', 'luxury-beauty-spa', 'Premium beauty and wellness services', '#C9A86A', '#2D3748', 'info@luxurybeautyspa.com', '+1234567890', '+1234567890', '123 Beauty Street, Wellness City, WC 12345', 'America/New_York', 'USD');

-- Sample branches
INSERT INTO branches (company_id, name, slug, address, city, state, country, phone, email, opening_hours, timezone, is_main) VALUES
(1, 'Downtown Salon', 'downtown-salon', '123 Beauty Street', 'Wellness City', 'NY', 'USA', '+1234567890', 'downtown@luxurybeautyspa.com', '{"monday": {"open": "09:00", "close": "19:00"}, "tuesday": {"open": "09:00", "close": "19:00"}, "wednesday": {"open": "09:00", "close": "19:00"}, "thursday": {"open": "09:00", "close": "19:00"}, "friday": {"open": "09:00", "close": "20:00"}, "saturday": {"open": "08:00", "close": "18:00"}, "sunday": {"open": "10:00", "close": "16:00"}}', 'America/New_York', TRUE),
(1, 'Uptown Branch', 'uptown-branch', '456 Wellness Avenue', 'Wellness City', 'NY', 'USA', '+1234567891', 'uptown@luxurybeautyspa.com', '{"monday": {"open": "10:00", "close": "20:00"}, "tuesday": {"open": "10:00", "close": "20:00"}, "wednesday": {"open": "10:00", "close": "20:00"}, "thursday": {"open": "10:00", "close": "20:00"}, "friday": {"open": "10:00", "close": "21:00"}, "saturday": {"open": "09:00", "close": "19:00"}, "sunday": {"open": "11:00", "close": "17:00"}}', 'America/New_York', FALSE);



-- Sample service categories
INSERT INTO service_categories (company_id, name, slug, description, sort_order) VALUES
(1, 'Hair Services', 'hair-services', 'Professional hair styling and treatment services', 1),
(1, 'Facial Treatments', 'facial-treatments', 'Rejuvenating facial and skincare treatments', 2),
(1, 'Body Treatments', 'body-treatments', 'Relaxing body massages and treatments', 3),
(1, 'Nail Services', 'nail-services', 'Manicure and pedicure services', 4);

-- Sample services
INSERT INTO services (company_id, category_id, name, slug, description, short_description, price, duration_minutes, is_featured, images, seo_description) VALUES
(1, 1, 'Luxury Hair Cut & Style', 'luxury-hair-cut-style', 'Experience our premium hair cutting and styling service using the finest techniques and products. Our skilled stylists will consult with you to create the perfect look that complements your face shape and personal style.', 'Indulge in a luxurious hair transformation with our expert stylists who combine precision cutting techniques with premium styling products to create your perfect look.', 85.00, 60, TRUE, '["/assets/images/services/hair-cut-1.jpg", "/assets/images/services/hair-cut-2.jpg"]', 'Professional hair cutting and styling service at Luxury Beauty Spa. Expert stylists, premium products, perfect results. Book your appointment today.'),
(1, 1, 'Hair Coloring', 'hair-coloring', 'Transform your look with our professional hair coloring services. We use only the highest quality, ammonia-free color products that nourish and protect your hair while delivering vibrant, long-lasting results.', 'Professional hair coloring service using premium, ammonia-free products that nourish your hair while delivering vibrant, long-lasting color results.', 120.00, 120, TRUE, '["/assets/images/services/coloring-1.jpg"]', 'Expert hair coloring services at Luxury Beauty Spa. Ammonia-free products, nourishing formulas, vibrant results. Schedule your color appointment now.'),
(1, 2, 'Hydrating Facial', 'hydrating-facial', 'Rejuvenate your skin with our signature hydrating facial treatment. This comprehensive treatment deeply cleanses, exfoliates, and nourishes your skin, leaving it glowing and refreshed.', 'Deeply cleanse, exfoliate, and nourish your skin with our signature hydrating facial treatment designed to restore moisture and radiance.', 75.00, 60, FALSE, '["/assets/images/services/facial-1.jpg"]', 'Luxury hydrating facial treatment at Luxury Beauty Spa. Deep cleansing, exfoliation, nourishment for glowing, refreshed skin. Book your facial today.'),
(1, 3, 'Swedish Massage', 'swedish-massage', 'Relax and unwind with our therapeutic Swedish massage. This classic massage technique uses gentle, flowing strokes to ease muscle tension, improve circulation, and promote deep relaxation.', 'Therapeutic Swedish massage using gentle flowing strokes to ease muscle tension, improve circulation, and promote deep relaxation and wellness.', 90.00, 60, TRUE, '["/assets/images/services/massage-1.jpg"]', 'Therapeutic Swedish massage at Luxury Beauty Spa. Gentle flowing strokes, improved circulation, deep relaxation. Schedule your massage appointment.'),
(1, 4, 'Gel Manicure', 'gel-manicure', 'Enhance your nails with our professional gel manicure service. Long-lasting, chip-resistant color with a natural, healthy look that lasts up to 14 days.', 'Professional gel manicure with long-lasting, chip-resistant color that provides a natural, healthy look lasting up to 14 days.', 45.00, 45, FALSE, '["/assets/images/services/manicure-1.jpg"]', 'Professional gel manicure service at Luxury Beauty Spa. Long-lasting color, chip-resistant finish, natural healthy look. Book your nail appointment.');

-- Assign staff to services
INSERT INTO staff_services (user_id, service_id, commission_rate, is_primary) VALUES
(2, 1, 15.00, TRUE), -- Sarah does hair services
(2, 2, 15.00, TRUE),
(3, 3, 12.00, TRUE), -- Mike does facials
(3, 4, 12.00, TRUE),
(2, 5, 10.00, FALSE); -- Sarah also does manicures

-- Staff availability (Monday-Friday 9AM-5PM)
INSERT INTO staff_availability (user_id, branch_id, day_of_week, start_time, end_time, is_available) VALUES
(2, 1, 1, '09:00', '17:00', TRUE), (2, 1, 2, '09:00', '17:00', TRUE), (2, 1, 3, '09:00', '17:00', TRUE), (2, 1, 4, '09:00', '17:00', TRUE), (2, 1, 5, '09:00', '17:00', TRUE),
(3, 2, 1, '10:00', '18:00', TRUE), (3, 2, 2, '10:00', '18:00', TRUE), (3, 2, 3, '10:00', '18:00', TRUE), (3, 2, 4, '10:00', '18:00', TRUE), (3, 2, 5, '10:00', '18:00', TRUE);

-- Sample products
INSERT INTO products (company_id, category_id, name, slug, description, short_description, price, stock_quantity, is_featured, images) VALUES
(1, NULL, 'Luxury Shampoo', 'luxury-shampoo', 'Premium nourishing shampoo formulated with natural ingredients to cleanse and revitalize your hair.', 'Premium nourishing shampoo with natural ingredients for clean, revitalized hair.', 25.00, 50, TRUE, '["/assets/images/products/shampoo-1.jpg"]'),
(1, NULL, 'Hair Treatment Oil', 'hair-treatment-oil', 'Intensive hair treatment oil that penetrates deep into the hair shaft to repair and strengthen damaged hair.', 'Intensive treatment oil that repairs and strengthens damaged hair from root to tip.', 35.00, 30, FALSE, '["/assets/images/products/oil-1.jpg"]');

-- Sample settings
INSERT INTO settings (company_id, setting_key, setting_value, setting_type) VALUES
(1, 'booking_cancellation_policy', 'Bookings can be cancelled up to 24 hours in advance for a full refund.', 'string'),
(1, 'booking_minimum_advance', '2', 'number'), -- hours
(1, 'booking_buffer_time', '15', 'number'), -- minutes
(1, 'smtp_host', 'smtp.example.com', 'string'),
(1, 'smtp_port', '587', 'number'),
(1, 'whatsapp_business_number', '+1234567890', 'string');

-- Sample notification templates
INSERT INTO notification_templates (company_id, name, type, event, subject, content, variables) VALUES
(1, 'Booking Confirmation', 'email', 'booking_confirmed', 'Your Booking is Confirmed - {{booking_number}}', 'Dear {{customer_name}},\n\nYour booking has been confirmed!\n\nService: {{service_name}}\nDate & Time: {{booking_date}} at {{booking_time}}\nStaff: {{staff_name}}\nBranch: {{branch_name}}\nTotal: {{currency_symbol}}{{total_amount}}\n\nBooking ID: {{booking_number}}\n\nThank you for choosing Luxury Beauty Spa!', '["customer_name", "service_name", "booking_date", "booking_time", "staff_name", "branch_name", "total_amount", "booking_number", "currency_symbol"]'),
(1, 'Appointment Reminder', 'whatsapp', 'booking_reminder', NULL, 'Hi {{customer_name}}! This is a friendly reminder of your upcoming appointment at Luxury Beauty Spa.\n\n📅 {{booking_date}} at {{booking_time}}\n💇 {{service_name}}\n👨‍💼 {{staff_name}}\n📍 {{branch_name}}\n\nSee you soon! 💅', '["customer_name", "booking_date", "booking_time", "service_name", "staff_name", "branch_name"]');

-- Sample booking
INSERT INTO bookings (booking_number, company_id, branch_id, customer_id, service_id, staff_id, start_at, end_at, status, total_amount) VALUES
('BK-2025-0001', 1, 1, 4, 1, 2, '2025-12-10 14:00:00', '2025-12-10 15:00:00', 'confirmed', 85.00);

-- Sample payment
INSERT INTO payments (booking_id, amount, payment_method_id, status, payment_date) VALUES
(1, 85.00, 1, 'completed', '2025-12-05 10:00:00');

-- Payment method (Cash)
INSERT INTO payment_methods (company_id, name, type, is_active) VALUES
(1, 'Cash', 'cash', TRUE);
