-- ============================================
-- RASHWAN'S BARBER POS SYSTEM
-- Database Schema - Version 1.0
-- Date: 2025-11-17
-- ============================================

-- Drop existing database if exists (CAUTION: Use only in development)
-- DROP DATABASE IF EXISTS rashwans_pos;

-- Create Database
CREATE DATABASE IF NOT EXISTS rashwans_pos 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE rashwans_pos;

-- ============================================
-- 1. ROLES TABLE
-- ============================================
CREATE TABLE roles (
    role_id INT PRIMARY KEY AUTO_INCREMENT,
    role_name VARCHAR(50) UNIQUE NOT NULL,
    role_description TEXT,
    permissions JSON COMMENT 'Store permissions as JSON array',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_role_name (role_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='User roles and permissions';

-- Insert default roles
INSERT INTO roles (role_name, role_description, permissions) VALUES
('admin', 'System Administrator - Full Access', '["all"]'),
('cashier', 'Cashier/Front Desk - POS and basic reporting', '["pos", "view_reports_location", "cash_up", "process_refund"]'),
('barber', 'Barber/Stylist - View own performance only', '["view_own_reports"]');

-- ============================================
-- 2. LOCATIONS TABLE
-- ============================================
CREATE TABLE locations (
    location_id INT PRIMARY KEY AUTO_INCREMENT,
    location_name VARCHAR(100) NOT NULL,
    address TEXT,
    city VARCHAR(50),
    province VARCHAR(50),
    postal_code VARCHAR(10),
    contact_number VARCHAR(20),
    email VARCHAR(100),
    manager_name VARCHAR(100),
    opening_hours JSON COMMENT 'Store as JSON: {"mon":"08:00-18:00", ...}',
    opening_float DECIMAL(10,2) DEFAULT 500.00 COMMENT 'Daily opening cash float',
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_city (city)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Salon locations/branches';

-- Insert sample locations
INSERT INTO locations (location_name, address, city, province, postal_code, contact_number, manager_name, status) VALUES
('Rashwan\'s Barber - Johannesburg CBD', '123 Main Street, CBD', 'Johannesburg', 'Gauteng', '2000', '011-123-4567', 'Ahmed Rashwan', 'active'),
('Rashwan\'s Barber - Sandton', '45 Rivonia Road, Sandton', 'Johannesburg', 'Gauteng', '2196', '011-234-5678', 'Sarah Maluleke', 'active'),
('Rashwan\'s Barber - Pretoria', '78 Church Street, Pretoria Central', 'Pretoria', 'Gauteng', '0002', '012-345-6789', 'Thabo Nkosi', 'active');

-- ============================================
-- 3. USERS TABLE
-- ============================================
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role_id INT NOT NULL,
    location_id INT COMMENT 'NULL for admin, assigned for cashier',
    contact_number VARCHAR(20),
    status ENUM('active', 'inactive', 'locked') DEFAULT 'active',
    failed_login_attempts INT DEFAULT 0,
    last_login DATETIME,
    last_login_ip VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(role_id),
    FOREIGN KEY (location_id) REFERENCES locations(location_id),
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_role_location (role_id, location_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='System users (admin, cashiers)';

-- Insert default admin user (password: Admin@2025)
INSERT INTO users (username, email, password_hash, full_name, role_id, status) VALUES
('admin', 'admin@rashwansbarber.co.za', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System Administrator', 1, 'active');

-- Insert sample cashiers
INSERT INTO users (username, email, password_hash, full_name, role_id, location_id, contact_number, status) VALUES
('cashier_jhb', 'cashier.jhb@rashwansbarber.co.za', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Lindiwe Khumalo', 2, 1, '078-123-4567', 'active'),
('cashier_sandton', 'cashier.sandton@rashwansbarber.co.za', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Nomsa Dlamini', 2, 2, '078-234-5678', 'active'),
('cashier_pta', 'cashier.pta@rashwansbarber.co.za', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Zanele Mokoena', 2, 3, '078-345-6789', 'active');

-- ============================================
-- 4. BARBERS TABLE
-- ============================================
CREATE TABLE barbers (
    barber_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id VARCHAR(20) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    contact_number VARCHAR(20),
    email VARCHAR(100),
    date_of_birth DATE,
    hire_date DATE NOT NULL,
    commission_rate DECIMAL(5,2) DEFAULT 15.00 COMMENT 'Commission percentage (e.g., 15.00 for 15%)',
    photo_url VARCHAR(255),
    notes TEXT COMMENT 'Additional notes about the barber',
    status ENUM('active', 'inactive', 'on_leave') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_employee_id (employee_id),
    INDEX idx_status (status),
    INDEX idx_full_name (full_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Barbers/Stylists information';

-- Insert sample barbers
INSERT INTO barbers (employee_id, full_name, contact_number, email, hire_date, commission_rate, status) VALUES
('RB001', 'Ahmed Rashwan', '082-111-2222', 'ahmed@rashwansbarber.co.za', '2020-01-15', 20.00, 'active'),
('RB002', 'Thabo Mbeki', '083-222-3333', 'thabo.m@rashwansbarber.co.za', '2020-03-20', 15.00, 'active'),
('RB003', 'Sarah Johnson', '084-333-4444', 'sarah.j@rashwansbarber.co.za', '2021-06-10', 15.00, 'active'),
('RB004', 'Kabelo Mokoena', '082-444-5555', 'kabelo.m@rashwansbarber.co.za', '2021-09-05', 15.00, 'active'),
('RB005', 'Michael van der Merwe', '083-555-6666', 'michael.v@rashwansbarber.co.za', '2022-02-14', 12.00, 'active'),
('RB006', 'Lerato Ndlovu', '084-666-7777', 'lerato.n@rashwansbarber.co.za', '2022-07-20', 12.00, 'active');

-- ============================================
-- 5. BARBER_LOCATIONS (Many-to-Many)
-- ============================================
CREATE TABLE barber_locations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    barber_id INT NOT NULL,
    location_id INT NOT NULL,
    is_primary BOOLEAN DEFAULT 0 COMMENT 'Is this the barber primary location?',
    assigned_date DATE NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (barber_id) REFERENCES barbers(barber_id) ON DELETE CASCADE,
    FOREIGN KEY (location_id) REFERENCES locations(location_id) ON DELETE CASCADE,
    UNIQUE KEY unique_barber_location (barber_id, location_id),
    INDEX idx_barber (barber_id),
    INDEX idx_location (location_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Barber location assignments (many-to-many relationship)';

-- Assign barbers to locations
INSERT INTO barber_locations (barber_id, location_id, is_primary, assigned_date) VALUES
-- Ahmed works at all locations (primary: JHB CBD)
(1, 1, 1, '2020-01-15'),
(1, 2, 0, '2020-01-15'),
(1, 3, 0, '2020-01-15'),
-- Thabo: JHB CBD and Sandton
(2, 1, 1, '2020-03-20'),
(2, 2, 0, '2021-01-10'),
-- Sarah: Sandton only
(3, 2, 1, '2021-06-10'),
-- Kabelo: JHB CBD and Pretoria
(4, 1, 1, '2021-09-05'),
(4, 3, 0, '2022-01-15'),
-- Michael: Pretoria only
(5, 3, 1, '2022-02-14'),
-- Lerato: Sandton and Pretoria
(6, 2, 1, '2022-07-20'),
(6, 3, 0, '2022-11-01');

-- ============================================
-- 6. SERVICES TABLE
-- ============================================
CREATE TABLE services (
    service_id INT PRIMARY KEY AUTO_INCREMENT,
    service_name VARCHAR(100) NOT NULL,
    service_category VARCHAR(50) NOT NULL COMMENT 'Haircut, Shave, Massage, Color, Package, etc.',
    description TEXT,
    base_price DECIMAL(10,2) NOT NULL,
    duration_minutes INT COMMENT 'Average service duration in minutes',
    commission_eligible BOOLEAN DEFAULT 1 COMMENT 'Is commission paid on this service?',
    status ENUM('active', 'inactive') DEFAULT 'active',
    display_order INT DEFAULT 0 COMMENT 'Order to display in POS interface',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category_status (service_category, status),
    INDEX idx_display_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Services offered by the salon';

-- Insert services
INSERT INTO services (service_name, service_category, description, base_price, duration_minutes, display_order, status) VALUES
-- HAIRCUTS
('Basic Haircut', 'Haircut', 'Standard men\'s haircut', 80.00, 30, 1, 'active'),
('Premium Haircut', 'Haircut', 'Premium styling with consultation', 120.00, 45, 2, 'active'),
('Kids Haircut', 'Haircut', 'Haircut for children under 12', 60.00, 20, 3, 'active'),
('Buzz Cut', 'Haircut', 'Simple buzz cut all over', 50.00, 15, 4, 'active'),

-- SHAVING
('Beard Trim', 'Shave', 'Professional beard trimming and shaping', 50.00, 20, 5, 'active'),
('Full Shave', 'Shave', 'Traditional hot towel shave', 70.00, 30, 6, 'active'),
('Beard Shaping', 'Shave', 'Detailed beard sculpting', 60.00, 25, 7, 'active'),
('Neck Shave', 'Shave', 'Quick neck cleanup', 30.00, 10, 8, 'active'),

-- MASSAGE
('Head Massage', 'Massage', 'Relaxing scalp massage', 50.00, 15, 9, 'active'),
('Full Scalp Treatment', 'Massage', 'Complete scalp treatment with oils', 100.00, 30, 10, 'active'),

-- COLORING
('Basic Hair Color', 'Color', 'Single color application', 150.00, 60, 11, 'active'),
('Premium Hair Color', 'Color', 'Premium color with treatment', 250.00, 90, 12, 'active'),
('Highlights', 'Color', 'Hair highlights', 200.00, 75, 13, 'active'),
('Beard Color', 'Color', 'Beard coloring service', 80.00, 30, 14, 'active'),

-- PACKAGES
('Full Grooming Package', 'Package', 'Haircut + Beard Trim + Head Massage', 180.00, 70, 15, 'active'),
('Premium Experience', 'Package', 'Premium Haircut + Full Shave + Scalp Treatment', 250.00, 105, 16, 'active'),
('Quick Clean Up', 'Package', 'Basic Haircut + Neck Shave', 100.00, 40, 17, 'active');

-- ============================================
-- 7. TRANSACTIONS TABLE
-- ============================================
CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY AUTO_INCREMENT,
    receipt_number VARCHAR(50) UNIQUE NOT NULL,
    location_id INT NOT NULL,
    cashier_id INT NOT NULL,
    barber_id INT NOT NULL,
    transaction_date DATETIME NOT NULL,
    subtotal DECIMAL(10,2) NOT NULL,
    vat_rate DECIMAL(5,2) NOT NULL COMMENT 'VAT rate at time of transaction (e.g., 15.00)',
    vat_amount DECIMAL(10,2) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    discount_amount DECIMAL(10,2) DEFAULT 0.00,
    discount_reason VARCHAR(255),
    status ENUM('completed', 'void', 'refunded') DEFAULT 'completed',
    void_reason TEXT,
    void_by INT COMMENT 'User ID who voided the transaction',
    void_date DATETIME,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (location_id) REFERENCES locations(location_id),
    FOREIGN KEY (cashier_id) REFERENCES users(user_id),
    FOREIGN KEY (barber_id) REFERENCES barbers(barber_id),
    FOREIGN KEY (void_by) REFERENCES users(user_id),
    INDEX idx_receipt_number (receipt_number),
    INDEX idx_transaction_date (transaction_date),
    INDEX idx_location_date (location_id, transaction_date),
    INDEX idx_barber_date (barber_id, transaction_date),
    INDEX idx_cashier_date (cashier_id, transaction_date),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Transaction header records';

-- ============================================
-- 8. TRANSACTION_ITEMS TABLE
-- ============================================
CREATE TABLE transaction_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_id INT NOT NULL,
    service_id INT NOT NULL,
    service_name VARCHAR(100) NOT NULL COMMENT 'Store service name for historical record',
    quantity INT DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    line_total DECIMAL(10,2) NOT NULL,
    commission_rate DECIMAL(5,2) COMMENT 'Commission rate at time of sale',
    commission_amount DECIMAL(10,2) COMMENT 'Calculated commission amount',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(transaction_id) ON DELETE CASCADE,
    FOREIGN KEY (service_id) REFERENCES services(service_id),
    INDEX idx_transaction (transaction_id),
    INDEX idx_service (service_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Transaction line items (services purchased)';

-- ============================================
-- 9. PAYMENTS TABLE
-- ============================================
CREATE TABLE payments (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_id INT NOT NULL,
    payment_method ENUM('cash', 'card', 'mobile_payment', 'other') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    amount_tendered DECIMAL(10,2) COMMENT 'Amount given by customer (cash only)',
    change_given DECIMAL(10,2) COMMENT 'Change returned to customer (cash only)',
    card_type VARCHAR(20) COMMENT 'Visa, Mastercard, etc.',
    card_last_four VARCHAR(4) COMMENT 'Last 4 digits of card number',
    payment_reference VARCHAR(100) COMMENT 'External payment reference/approval code',
    payment_date DATETIME NOT NULL,
    processed_by INT COMMENT 'Cashier who processed payment',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(transaction_id) ON DELETE CASCADE,
    FOREIGN KEY (processed_by) REFERENCES users(user_id),
    INDEX idx_transaction (transaction_id),
    INDEX idx_payment_date (payment_date),
    INDEX idx_payment_method (payment_method)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Payment records for transactions';

-- ============================================
-- 10. CASH_UPS TABLE
-- ============================================
CREATE TABLE cash_ups (
    cash_up_id INT PRIMARY KEY AUTO_INCREMENT,
    location_id INT NOT NULL,
    cashier_id INT NOT NULL,
    cash_up_date DATE NOT NULL,
    shift_start DATETIME,
    shift_end DATETIME,
    opening_float DECIMAL(10,2) NOT NULL,
    
    -- Expected amounts
    expected_cash DECIMAL(10,2) NOT NULL,
    expected_card DECIMAL(10,2) NOT NULL,
    expected_mobile DECIMAL(10,2) NOT NULL,
    expected_total DECIMAL(10,2) NOT NULL,
    
    -- Actual amounts
    actual_cash DECIMAL(10,2) NOT NULL,
    actual_card DECIMAL(10,2) NOT NULL,
    actual_mobile DECIMAL(10,2) NOT NULL,
    actual_total DECIMAL(10,2) NOT NULL,
    
    -- Variance
    cash_variance DECIMAL(10,2) NOT NULL COMMENT 'actual_cash - expected_cash',
    total_variance DECIMAL(10,2) NOT NULL COMMENT 'actual_total - expected_total',
    
    -- Cash breakdown (denominations)
    r200_notes INT DEFAULT 0,
    r100_notes INT DEFAULT 0,
    r50_notes INT DEFAULT 0,
    r20_notes INT DEFAULT 0,
    r10_notes INT DEFAULT 0,
    r5_coins DECIMAL(10,2) DEFAULT 0,
    coins DECIMAL(10,2) DEFAULT 0,
    
    transaction_count INT NOT NULL,
    notes TEXT,
    status ENUM('pending', 'approved', 'discrepancy') DEFAULT 'pending',
    approved_by INT COMMENT 'Admin who approved cash-up',
    approved_date DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (location_id) REFERENCES locations(location_id),
    FOREIGN KEY (cashier_id) REFERENCES users(user_id),
    FOREIGN KEY (approved_by) REFERENCES users(user_id),
    UNIQUE KEY unique_location_date_cashier (location_id, cash_up_date, cashier_id),
    INDEX idx_location_date (location_id, cash_up_date),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='End-of-day cash reconciliation records';

-- ============================================
-- 11. AUDIT_LOGS TABLE
-- ============================================
CREATE TABLE audit_logs (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(100) NOT NULL COMMENT 'login, logout, create_user, void_transaction, etc.',
    table_name VARCHAR(50) COMMENT 'Table affected by the action',
    record_id INT COMMENT 'ID of the affected record',
    old_values JSON COMMENT 'Previous values (for updates)',
    new_values JSON COMMENT 'New values (for creates/updates)',
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL,
    INDEX idx_user_action (user_id, action),
    INDEX idx_table_record (table_name, record_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Audit trail for all system actions';

-- ============================================
-- 12. SYSTEM_SETTINGS TABLE
-- ============================================
CREATE TABLE system_settings (
    setting_id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(50) UNIQUE NOT NULL,
    setting_value TEXT,
    setting_type VARCHAR(20) COMMENT 'string, number, boolean, json',
    description TEXT,
    is_public BOOLEAN DEFAULT 0 COMMENT 'Can non-admin users view this setting?',
    updated_by INT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (updated_by) REFERENCES users(user_id),
    INDEX idx_setting_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='System configuration settings';

-- Insert default system settings
INSERT INTO system_settings (setting_key, setting_value, setting_type, description, is_public) VALUES
('business_name', 'Rashwan\'s Barber', 'string', 'Business Name', 1),
('business_tagline', 'Your Style, Our Passion', 'string', 'Business Tagline', 1),
('vat_number', '1234567890', 'string', 'VAT Registration Number', 1),
('vat_rate', '15.00', 'number', 'VAT Rate Percentage', 1),
('vat_inclusive', '1', 'boolean', 'Prices include VAT (1) or exclude VAT (0)', 1),
('currency', 'ZAR', 'string', 'Currency Code', 1),
('currency_symbol', 'R', 'string', 'Currency Symbol', 1),
('decimal_places', '2', 'number', 'Number of decimal places for currency', 1),
('date_format', 'd/m/Y', 'string', 'Date format (PHP date format)', 1),
('time_format', 'H:i', 'string', 'Time format (PHP time format)', 1),
('receipt_header', 'Thank you for visiting Rashwan\'s Barber!', 'string', 'Receipt Header Text', 1),
('receipt_footer', 'We appreciate your business!\nFollow us @rashwansbarber', 'string', 'Receipt Footer Text', 1),
('receipt_show_barber', '1', 'boolean', 'Show barber name on receipt', 1),
('receipt_show_address', '1', 'boolean', 'Show location address on receipt', 1),
('session_timeout', '30', 'number', 'Session Timeout in Minutes', 0),
('max_login_attempts', '5', 'number', 'Maximum failed login attempts before lockout', 0),
('lockout_duration', '30', 'number', 'Account lockout duration in minutes', 0),
('default_opening_float', '500.00', 'number', 'Default Opening Cash Float', 0),
('enable_discounts', '1', 'boolean', 'Allow cashiers to apply discounts', 0),
('max_discount_percent', '20', 'number', 'Maximum discount percentage allowed', 0),
('low_stock_threshold', '10', 'number', 'Low stock alert threshold (future use)', 0),
('backup_enabled', '1', 'boolean', 'Enable automated backups', 0),
('backup_frequency', 'daily', 'string', 'Backup frequency: daily, weekly', 0),
('system_email', 'system@rashwansbarber.co.za', 'string', 'System notification email', 0),
('support_phone', '011-123-4567', 'string', 'Support contact number', 1),
('app_version', '1.0.0', 'string', 'Application version', 1),
('maintenance_mode', '0', 'boolean', 'Enable maintenance mode', 0);

-- ============================================
-- 13. SESSIONS TABLE (Optional - for database session storage)
-- ============================================
CREATE TABLE sessions (
    session_id VARCHAR(128) PRIMARY KEY,
    user_id INT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    payload TEXT NOT NULL,
    last_activity INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_last_activity (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Session storage (alternative to file-based sessions)';

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

-- View: Daily Sales Summary by Location
CREATE VIEW view_daily_sales_summary AS
SELECT 
    l.location_name,
    DATE(t.transaction_date) as sale_date,
    COUNT(t.transaction_id) as transaction_count,
    SUM(t.subtotal) as subtotal,
    SUM(t.vat_amount) as vat_amount,
    SUM(t.total_amount) as total_amount,
    SUM(CASE WHEN p.payment_method = 'cash' THEN p.amount ELSE 0 END) as cash_sales,
    SUM(CASE WHEN p.payment_method = 'card' THEN p.amount ELSE 0 END) as card_sales,
    SUM(CASE WHEN p.payment_method = 'mobile_payment' THEN p.amount ELSE 0 END) as mobile_sales
FROM transactions t
JOIN locations l ON t.location_id = l.location_id
LEFT JOIN payments p ON t.transaction_id = p.transaction_id
WHERE t.status = 'completed'
GROUP BY l.location_name, DATE(t.transaction_date);

-- View: Barber Performance Summary
CREATE VIEW view_barber_performance AS
SELECT 
    b.barber_id,
    b.employee_id,
    b.full_name,
    DATE(t.transaction_date) as sale_date,
    COUNT(t.transaction_id) as transaction_count,
    SUM(t.total_amount) as total_sales,
    SUM(ti.commission_amount) as total_commission
FROM barbers b
JOIN transactions t ON b.barber_id = t.barber_id
JOIN transaction_items ti ON t.transaction_id = ti.transaction_id
WHERE t.status = 'completed'
GROUP BY b.barber_id, b.employee_id, b.full_name, DATE(t.transaction_date);

-- View: Service Popularity
CREATE VIEW view_service_popularity AS
SELECT 
    s.service_id,
    s.service_name,
    s.service_category,
    COUNT(ti.item_id) as times_sold,
    SUM(ti.quantity) as total_quantity,
    SUM(ti.line_total) as total_revenue
FROM services s
JOIN transaction_items ti ON s.service_id = ti.service_id
JOIN transactions t ON ti.transaction_id = t.transaction_id
WHERE t.status = 'completed'
GROUP BY s.service_id, s.service_name, s.service_category
ORDER BY times_sold DESC;

-- ============================================
-- STORED PROCEDURES
-- ============================================

-- Procedure: Generate Receipt Number
DELIMITER $$
CREATE PROCEDURE generate_receipt_number(
    IN p_location_id INT,
    OUT p_receipt_number VARCHAR(50)
)
BEGIN
    DECLARE location_code VARCHAR(10);
    DECLARE sequence_num INT;
    DECLARE today_date VARCHAR(8);
    
    -- Get location code (first 3 letters of location name)
    SELECT UPPER(LEFT(REPLACE(location_name, ' ', ''), 3)) 
    INTO location_code
    FROM locations 
    WHERE location_id = p_location_id;
    
    -- Get today's date in YYYYMMDD format
    SET today_date = DATE_FORMAT(NOW(), '%Y%m%d');
    
    -- Get next sequence number for today
    SELECT COALESCE(MAX(CAST(SUBSTRING(receipt_number, -4) AS UNSIGNED)), 0) + 1
    INTO sequence_num
    FROM transactions
    WHERE receipt_number LIKE CONCAT(location_code, '-', today_date, '-%');
    
    -- Generate receipt number: LOC-YYYYMMDD-NNNN
    SET p_receipt_number = CONCAT(location_code, '-', today_date, '-', LPAD(sequence_num, 4, '0'));
END$$
DELIMITER ;

-- Procedure: Calculate Transaction Totals
DELIMITER $
CREATE PROCEDURE calculate_transaction_totals(
    IN p_transaction_id INT,
    IN p_vat_rate DECIMAL(5,2),
    OUT p_subtotal DECIMAL(10,2),
    OUT p_vat_amount DECIMAL(10,2),
    OUT p_total DECIMAL(10,2)
)
BEGIN
    -- Calculate subtotal from transaction items
    SELECT COALESCE(SUM(line_total), 0) 
    INTO p_subtotal
    FROM transaction_items
    WHERE transaction_id = p_transaction_id;
    
    -- Calculate VAT amount
    SET p_vat_amount = ROUND(p_subtotal * (p_vat_rate / 100), 2);
    
    -- Calculate total
    SET p_total = p_subtotal + p_vat_amount;
END$
DELIMITER ;

-- Procedure: Get Barbers by Location
DELIMITER $
CREATE PROCEDURE get_barbers_by_location(IN p_location_id INT)
BEGIN
    SELECT 
        b.barber_id,
        b.employee_id,
        b.full_name,
        b.commission_rate,
        b.status,
        bl.is_primary
    FROM barbers b
    JOIN barber_locations bl ON b.barber_id = bl.barber_id
    WHERE bl.location_id = p_location_id
    AND b.status = 'active'
    ORDER BY bl.is_primary DESC, b.full_name ASC;
END$
DELIMITER ;

-- ============================================
-- TRIGGERS
-- ============================================

-- Trigger: Update transaction totals after item insert
DELIMITER $
CREATE TRIGGER trg_update_transaction_after_item_insert
AFTER INSERT ON transaction_items
FOR EACH ROW
BEGIN
    UPDATE transactions t
    SET 
        subtotal = (SELECT SUM(line_total) FROM transaction_items WHERE transaction_id = NEW.transaction_id),
        vat_amount = ROUND((SELECT SUM(line_total) FROM transaction_items WHERE transaction_id = NEW.transaction_id) * (t.vat_rate / 100), 2)
    WHERE transaction_id = NEW.transaction_id;
    
    UPDATE transactions
    SET total_amount = subtotal + vat_amount
    WHERE transaction_id = NEW.transaction_id;
END$
DELIMITER ;

-- Trigger: Calculate commission on transaction item insert
DELIMITER $
CREATE TRIGGER trg_calculate_commission_on_insert
BEFORE INSERT ON transaction_items
FOR EACH ROW
BEGIN
    DECLARE v_commission_rate DECIMAL(5,2);
    DECLARE v_barber_id INT;
    
    -- Get barber commission rate from transaction
    SELECT t.barber_id INTO v_barber_id
    FROM transactions t
    WHERE t.transaction_id = NEW.transaction_id;
    
    SELECT commission_rate INTO v_commission_rate
    FROM barbers
    WHERE barber_id = v_barber_id;
    
    -- Set commission rate and amount if service is commission eligible
    IF NEW.commission_rate IS NULL THEN
        SET NEW.commission_rate = v_commission_rate;
    END IF;
    
    -- Calculate commission amount
    SET NEW.commission_amount = ROUND(NEW.line_total * (NEW.commission_rate / 100), 2);
END$
DELIMITER ;

-- Trigger: Audit log on user login
DELIMITER $
CREATE TRIGGER trg_audit_user_login
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF OLD.last_login != NEW.last_login OR (OLD.last_login IS NULL AND NEW.last_login IS NOT NULL) THEN
        INSERT INTO audit_logs (user_id, action, table_name, record_id, ip_address)
        VALUES (NEW.user_id, 'login', 'users', NEW.user_id, NEW.last_login_ip);
    END IF;
END$
DELIMITER ;

-- ============================================
-- INDEXES FOR PERFORMANCE
-- ============================================

-- Additional composite indexes for complex queries
CREATE INDEX idx_trans_location_date_status ON transactions(location_id, transaction_date, status);
CREATE INDEX idx_trans_barber_date_status ON transactions(barber_id, transaction_date, status);
CREATE INDEX idx_payment_method_date ON payments(payment_method, payment_date);
CREATE INDEX idx_items_service_trans ON transaction_items(service_id, transaction_id);

-- ============================================
-- SAMPLE DATA FOR TESTING
-- ============================================

-- Insert sample transactions (for testing)
INSERT INTO transactions (receipt_number, location_id, cashier_id, barber_id, transaction_date, subtotal, vat_rate, vat_amount, total_amount, status)
VALUES 
('RAS-20251117-0001', 1, 2, 1, '2025-11-17 09:30:00', 130.00, 15.00, 19.50, 149.50, 'completed'),
('RAS-20251117-0002', 1, 2, 2, '2025-11-17 10:15:00', 80.00, 15.00, 12.00, 92.00, 'completed'),
('RAS-20251117-0003', 2, 3, 3, '2025-11-17 11:00:00', 200.00, 15.00, 30.00, 230.00, 'completed');

-- Insert transaction items
INSERT INTO transaction_items (transaction_id, service_id, service_name, quantity, unit_price, line_total, commission_rate, commission_amount)
VALUES 
-- Transaction 1 items
(1, 1, 'Basic Haircut', 1, 80.00, 80.00, 20.00, 16.00),
(1, 5, 'Beard Trim', 1, 50.00, 50.00, 20.00, 10.00),
-- Transaction 2 items
(2, 1, 'Basic Haircut', 1, 80.00, 80.00, 15.00, 12.00),
-- Transaction 3 items
(3, 2, 'Premium Haircut', 1, 120.00, 120.00, 15.00, 18.00),
(3, 6, 'Full Shave', 1, 70.00, 70.00, 15.00, 10.50),
(3, 9, 'Head Massage', 1, 50.00, 50.00, 15.00, 7.50);

-- Insert payment records
INSERT INTO payments (transaction_id, payment_method, amount, amount_tendered, change_given, payment_date, processed_by)
VALUES 
(1, 'cash', 149.50, 200.00, 50.50, '2025-11-17 09:30:00', 2),
(2, 'card', 92.00, NULL, NULL, '2025-11-17 10:15:00', 2),
(3, 'cash', 230.00, 250.00, 20.00, '2025-11-17 11:00:00', 3);

-- ============================================
-- GRANTS AND PERMISSIONS
-- ============================================

-- Create application database user
-- Note: Run these commands separately after creating the database
/*
CREATE USER 'rashwans_user'@'localhost' IDENTIFIED BY 'SecurePassword123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON rashwans_pos.* TO 'rashwans_user'@'localhost';
GRANT EXECUTE ON rashwans_pos.* TO 'rashwans_user'@'localhost';
FLUSH PRIVILEGES;
*/

-- ============================================
-- MAINTENANCE QUERIES
-- ============================================

-- Query to find old audit logs (for cleanup)
-- DELETE FROM audit_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- Query to find inactive users
-- SELECT * FROM users WHERE status = 'inactive' AND updated_at < DATE_SUB(NOW(), INTERVAL 6 MONTH);

-- Query to optimize tables (run monthly)
-- OPTIMIZE TABLE transactions, transaction_items, payments, audit_logs;

-- ============================================
-- VERIFICATION QUERIES
-- ============================================

-- Verify all tables created
SELECT TABLE_NAME, TABLE_ROWS, AUTO_INCREMENT 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'rashwans_pos'
ORDER BY TABLE_NAME;

-- Verify all indexes
SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME 
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = 'rashwans_pos'
ORDER BY TABLE_NAME, INDEX_NAME;

-- Verify foreign keys
SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'rashwans_pos'
AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY TABLE_NAME;

-- Test data verification
SELECT 
    'Roles' as entity, COUNT(*) as count FROM roles
UNION ALL
SELECT 'Locations', COUNT(*) FROM locations
UNION ALL
SELECT 'Users', COUNT(*) FROM users
UNION ALL
SELECT 'Barbers', COUNT(*) FROM barbers
UNION ALL
SELECT 'Services', COUNT(*) FROM services
UNION ALL
SELECT 'Transactions', COUNT(*) FROM transactions;

-- ============================================
-- BACKUP COMMAND
-- ============================================
/*
To backup the database, run:
mysqldump -u root -p rashwans_pos > rashwans_pos_backup_$(date +%Y%m%d).sql

To restore:
mysql -u root -p rashwans_pos < rashwans_pos_backup_YYYYMMDD.sql
*/

-- ============================================
-- END OF SCHEMA
-- ============================================

-- Display success message
SELECT 'Database schema created successfully!' as Status,
       'Total Tables: 13' as Info1,
       'Total Views: 3' as Info2,
       'Total Procedures: 3' as Info3,
       'Default admin user: admin / Admin@2025' as Info4,
       'Database ready for application deployment' as Info5;