-- =====================================================
-- Zen Zone Spa Database Schema
-- Optimized for MySQL 5.7+ and cPanel hosting
-- =====================================================

-- Create database (run this in cPanel MySQL)
-- CREATE DATABASE zenzone_spa_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE zenzone_spa_db;

-- =====================================================
-- USERS TABLE - Authentication and user management
-- =====================================================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    role ENUM('admin', 'manager', 'therapist', 'staff') DEFAULT 'staff',
    phone VARCHAR(20),
    is_active BOOLEAN DEFAULT TRUE,
    last_login DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_role (role),
    INDEX idx_active (is_active)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- =====================================================
-- THERAPISTS TABLE - Staff management
-- =====================================================
CREATE TABLE therapists (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL, -- Links to users table if therapist has login access
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    specialties TEXT, -- JSON array of specialties
    commission_rate DECIMAL(5,4) DEFAULT 0.3000, -- 30% default
    hire_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    bio TEXT,
    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 SET NULL,
    INDEX idx_name (name),
    INDEX idx_active (is_active)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- =====================================================
-- MASSAGE_TYPES TABLE - Service catalog
-- =====================================================
CREATE TABLE massage_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    base_duration INT DEFAULT 60, -- minutes
    base_price DECIMAL(10,2) NOT NULL,
    category VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_name (name),
    INDEX idx_category (category),
    INDEX idx_active (is_active)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- =====================================================
-- CLIENTS TABLE - Customer management
-- =====================================================
CREATE TABLE clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    date_of_birth DATE,
    gender ENUM('male', 'female', 'other'),
    address TEXT,
    medical_notes TEXT,
    preferences TEXT, -- JSON for preferences
    total_visits INT DEFAULT 0,
    total_spent DECIMAL(10,2) DEFAULT 0.00,
    last_visit DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_name (name),
    INDEX idx_phone (phone),
    INDEX idx_email (email)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- =====================================================
-- MASSAGES TABLE - Session records
-- =====================================================
CREATE TABLE massages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    massage_type_id INT NOT NULL,
    therapist_id INT NOT NULL,
    client_id INT NULL, -- Optional: can track walk-in clients
    client_name VARCHAR(100), -- For walk-ins without client record
    amount DECIMAL(10,2) NOT NULL,
    duration INT DEFAULT 60, -- minutes
    therapist_commission DECIMAL(10,2) GENERATED ALWAYS AS (amount * 0.30) STORED,
    net_income DECIMAL(10,2) GENERATED ALWAYS AS (amount * 0.70) STORED,
    session_date DATETIME NOT NULL,
    status ENUM('scheduled', 'in_progress', 'completed', 'cancelled') DEFAULT 'completed',
    payment_method ENUM('cash', 'card', 'mobile_money', 'bank_transfer') DEFAULT 'cash',
    notes TEXT,
    rating TINYINT CHECK (rating >= 1 AND rating <= 5),
    created_by INT NOT NULL, -- User who recorded this session
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (massage_type_id) REFERENCES massage_types(id),
    FOREIGN KEY (therapist_id) REFERENCES therapists(id),
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id),
    
    INDEX idx_session_date (session_date),
    INDEX idx_therapist (therapist_id),
    INDEX idx_client (client_id),
    INDEX idx_status (status),
    INDEX idx_created_date (created_at)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- =====================================================
-- EXPENSE_CATEGORIES TABLE - Expense categorization
-- =====================================================
CREATE TABLE expense_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    color VARCHAR(7) DEFAULT '#6B7280', -- Hex color for UI
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_name (name)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- =====================================================
-- EXPENSES TABLE - Business expense tracking
-- =====================================================
CREATE TABLE expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    description VARCHAR(255) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    expense_date DATE NOT NULL,
    payment_method ENUM('cash', 'card', 'bank_transfer', 'mobile_money') DEFAULT 'cash',
    receipt_number VARCHAR(100),
    notes TEXT,
    is_recurring BOOLEAN DEFAULT FALSE,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (category_id) REFERENCES expense_categories(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    
    INDEX idx_expense_date (expense_date),
    INDEX idx_category (category_id),
    INDEX idx_amount (amount)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- =====================================================
-- API_TOKENS TABLE - JWT token management
-- =====================================================
CREATE TABLE api_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_used DATETIME,
    is_revoked BOOLEAN DEFAULT FALSE,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_token_hash (token_hash),
    INDEX idx_user_id (user_id),
    INDEX idx_expires_at (expires_at)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- =====================================================
-- SYSTEM_SETTINGS TABLE - App configuration
-- =====================================================
CREATE TABLE system_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    data_type ENUM('string', 'number', 'boolean', 'json') DEFAULT 'string',
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_key (setting_key)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- =====================================================
-- CONTACT_INQUIRIES TABLE - Contact form submissions
-- =====================================================
CREATE TABLE contact_inquiries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    phone VARCHAR(20),
    service VARCHAR(50),
    message TEXT,
    status ENUM('new', 'contacted', 'converted', 'closed') DEFAULT 'new',
    priority ENUM('low', 'medium', 'high') DEFAULT 'medium',
    source VARCHAR(50) DEFAULT 'website',
    notes TEXT,
    assigned_to INT NULL,
    responded_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    
    INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_service (service)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- =====================================================
-- INSERT DEFAULT DATA
-- =====================================================

-- Default admin user (password: 'password' - change in production!)
INSERT INTO users (username, password_hash, email, name, role) VALUES 
('admin', '$2y$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj1VjPQeEUKW', 'admin@zenzone.rw', 'Admin User', 'admin');

-- Zen Zone Spa therapists - Justine and Liliane
INSERT INTO therapists (name, email, phone, specialties, is_active) VALUES 
('Justine Niwemukobwa', 'justine@zenzone.rw', '+250788123456', '["Deep Tissue", "Hot Stone", "Swedish", "Aromatherapy"]', TRUE),
('Liliane Uwimana', 'liliane@zenzone.rw', '+250788123457', '["Sports Massage", "Reflexology", "Facial", "Shiatsu"]', TRUE);

-- Sample Rwandan clients
INSERT INTO clients (name, email, phone, gender, address, total_visits, total_spent, last_visit) VALUES 
('Aline Mukamana', 'aline.mukamana@gmail.com', '+250788001234', 'female', 'Kigali, Gasabo District', 5, 75000.00, '2024-01-15 14:30:00'),
('Jean Baptiste Nzeyimana', 'jb.nzeyimana@yahoo.com', '+250788002345', 'male', 'Kigali, Nyarugenge District', 3, 60000.00, '2024-01-12 16:00:00'),
('Divine Uwimana', 'divine.uwimana@outlook.com', '+250788003456', 'female', 'Kigali, Kicukiro District', 8, 120000.00, '2024-01-18 10:00:00'),
('Emmanuel Habimana', 'emmanuel.habimana@gmail.com', '+250788004567', 'male', 'Kigali, Gasabo District', 2, 40000.00, '2024-01-10 15:30:00'),
('Grace Nyirahabimana', 'grace.nyirahabimana@gmail.com', '+250788005678', 'female', 'Kigali, Nyarugenge District', 6, 90000.00, '2024-01-16 11:30:00'),
('Claude Uwimana', 'claude.uwimana@gmail.com', '+250788006789', 'male', 'Kigali, Kicukiro District', 4, 80000.00, '2024-01-14 13:00:00'),
('Immaculée Mukamurenzi', 'immaculee.mukamurenzi@gmail.com', '+250788007890', 'female', 'Kigali, Gasabo District', 7, 105000.00, '2024-01-17 09:30:00'),
('Patrick Niyonshuti', 'patrick.niyonshuti@gmail.com', '+250788008901', 'male', 'Kigali, Nyarugenge District', 3, 45000.00, '2024-01-13 17:00:00'),
('Solange Uwamahoro', 'solange.uwamahoro@gmail.com', '+250788009012', 'female', 'Kigali, Kicukiro District', 5, 75000.00, '2024-01-19 14:00:00'),
('Fidèle Nsengimana', 'fidele.nsengimana@gmail.com', '+250788010123', 'male', 'Kigali, Gasabo District', 2, 30000.00, '2024-01-11 16:30:00');

-- Massage types with current pricing
INSERT INTO massage_types (name, description, base_duration, base_price, category) VALUES 
('Relaxation (Swedish)', 'Classic Swedish massage for relaxation', 60, 15000.00, 'Relaxation'),
('Deep Tissue Massage', 'Therapeutic deep tissue massage', 90, 20000.00, 'Therapeutic'),
('Hot Stone Massage', 'Relaxing massage with heated stones', 60, 35000.00, 'Premium'),
('Shiatsu Massage', 'Japanese pressure point massage', 90, 20000.00, 'Therapeutic'),
('Sport Massage', 'Massage for athletes and active individuals', 90, 20000.00, 'Therapeutic'),
('Candle Massage', 'Warm candle wax massage', 60, 22000.00, 'Premium'),
('Facial Treatments', 'Professional facial therapy', 60, 22000.00, 'Beauty'),
('Full Reflexology', 'Complete foot reflexology session', 60, 15000.00, 'Therapeutic'),
('Back-Neck-Shoulders', 'Targeted massage for upper body', 40, 15000.00, 'Quick'),
('Aromatherapy Massage', 'Essential oil based massage', 60, 15000.00, 'Relaxation');

-- Sample massage sessions
INSERT INTO massages (massage_type_id, therapist_id, client_id, amount, duration, session_date, status, payment_method, notes, rating, created_by) VALUES 
(1, 1, 1, 15000.00, 60, '2024-01-15 14:30:00', 'completed', 'cash', 'Client loved the Swedish massage', 5, 1),
(3, 2, 2, 35000.00, 60, '2024-01-12 16:00:00', 'completed', 'mobile_money', 'Hot stone massage for stress relief', 5, 1),
(2, 1, 3, 20000.00, 90, '2024-01-18 10:00:00', 'completed', 'cash', 'Deep tissue for back pain', 4, 1),
(8, 2, 4, 15000.00, 60, '2024-01-10 15:30:00', 'completed', 'cash', 'First-time reflexology client', 4, 1),
(6, 1, 5, 22000.00, 60, '2024-01-16 11:30:00', 'completed', 'card', 'Candle massage for relaxation', 5, 1);

-- Expense categories
INSERT INTO expense_categories (name, description, color) VALUES 
('Supplies', 'Massage oils, lotions, towels, etc.', '#10B981'),
('Utilities', 'Electricity, water, internet bills', '#F59E0B'),
('Maintenance', 'Equipment maintenance and repairs', '#EF4444'),
('Marketing', 'Advertising and promotional expenses', '#8B5CF6'),
('Staff', 'Staff salaries and benefits', '#06B6D4'),
('Rent', 'Property rent and related costs', '#84CC16'),
('Equipment', 'Purchase of new equipment', '#F97316'),
('Other', 'Miscellaneous business expenses', '#6B7280');

-- System settings
INSERT INTO system_settings (setting_key, setting_value, data_type, description) VALUES 
('spa_name', 'Zen Zone Spa', 'string', 'Business name'),
('currency', 'Frw', 'string', 'Default currency'),
('default_commission_rate', '0.30', 'number', 'Default therapist commission rate'),
('business_hours', '{"open": "08:00", "close": "20:00"}', 'json', 'Business operating hours'),
('contact_phone', '+250 730 000 016', 'string', 'Business contact phone'),
('contact_email', 'zenzonerw@gmail.com', 'string', 'Business contact email'),
('website_url', 'https://zenzone.rw', 'string', 'Official website URL');

-- =====================================================
-- CREATE VIEWS FOR REPORTING
-- =====================================================

-- Daily revenue summary
CREATE VIEW daily_revenue AS
SELECT 
    DATE(session_date) as date,
    COUNT(*) as total_sessions,
    SUM(amount) as total_revenue,
    SUM(therapist_commission) as total_commission,
    SUM(net_income) as net_income
FROM massages 
WHERE status = 'completed'
GROUP BY DATE(session_date)
ORDER BY date DESC;

-- Therapist performance
CREATE VIEW therapist_performance AS
SELECT 
    t.id,
    t.name,
    COUNT(m.id) as total_sessions,
    SUM(m.amount) as total_revenue,
    SUM(m.therapist_commission) as total_commission,
    ROUND(AVG(m.amount), 2) as avg_session_value,
    MAX(m.session_date) as last_session
FROM therapists t
LEFT JOIN massages m ON t.id = m.therapist_id AND m.status = 'completed'
WHERE t.is_active = TRUE
GROUP BY t.id, t.name
ORDER BY total_revenue DESC;

-- Monthly expense summary
CREATE VIEW monthly_expenses AS
SELECT 
    YEAR(expense_date) as year,
    MONTH(expense_date) as month,
    ec.name as category,
    SUM(e.amount) as total_amount,
    COUNT(*) as expense_count
FROM expenses e
JOIN expense_categories ec ON e.category_id = ec.id
GROUP BY YEAR(expense_date), MONTH(expense_date), ec.id, ec.name
ORDER BY year DESC, month DESC, total_amount DESC; 