-- ============================================
-- GYM MANAGEMENT SYSTEM - COMPLETE DATABASE SCHEMA
-- ============================================

-- Create database
CREATE DATABASE IF NOT EXISTS gym_management;
USE gym_management;

-- ============================================
-- USERS TABLE
-- Stores admin, receptionist, and other staff accounts
-- ============================================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'receptionist', 'trainer') NOT NULL DEFAULT 'receptionist',
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_role (role),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- CLIENTS TABLE
-- Stores gym member/client information
-- ============================================
CREATE TABLE clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20) NOT NULL,
    date_of_birth DATE,
    gender ENUM('male', 'female', 'other'),
    address TEXT,
    emergency_contact_name VARCHAR(100),
    emergency_contact_phone VARCHAR(20),
    medical_notes TEXT,
    profile_photo VARCHAR(255),
    status ENUM('active', 'inactive', 'suspended') NOT NULL DEFAULT 'active',
    registration_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT,
    INDEX idx_name (first_name, last_name),
    INDEX idx_email (email),
    INDEX idx_phone (phone),
    INDEX idx_status (status),
    INDEX idx_registration_date (registration_date),
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- MEMBERSHIPS TABLE
-- Stores membership plan types and pricing
-- ============================================
CREATE TABLE memberships (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    duration_months INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    features TEXT,
    max_classes_per_month INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_price (price),
    INDEX idx_duration (duration_months)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- SUBSCRIPTIONS TABLE
-- Stores client membership subscriptions
-- ============================================
CREATE TABLE subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    membership_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status ENUM('active', 'expired', 'cancelled', 'pending') NOT NULL DEFAULT 'active',
    payment_amount DECIMAL(10, 2),
    payment_method ENUM('cash', 'mpesa', 'card', 'bank_transfer') DEFAULT 'cash',
    payment_reference VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT,
    INDEX idx_client (client_id),
    INDEX idx_membership (membership_id),
    INDEX idx_dates (start_date, end_date),
    INDEX idx_status (status),
    INDEX idx_end_date (end_date),
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (membership_id) REFERENCES memberships(id) ON DELETE RESTRICT,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- CLASSES TABLE
-- Stores fitness class schedules
-- ============================================
CREATE TABLE classes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    instructor VARCHAR(100) NOT NULL,
    capacity INT NOT NULL DEFAULT 20,
    class_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    room VARCHAR(50),
    difficulty_level ENUM('beginner', 'intermediate', 'advanced') DEFAULT 'beginner',
    status ENUM('scheduled', 'completed', 'cancelled') NOT NULL DEFAULT 'scheduled',
    recurring ENUM('none', 'daily', 'weekly', 'monthly') DEFAULT 'none',
    recurring_end_date DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT,
    INDEX idx_date (class_date),
    INDEX idx_instructor (instructor),
    INDEX idx_status (status),
    INDEX idx_datetime (class_date, start_time),
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- CLASS_BOOKINGS TABLE
-- Stores client registrations for classes
-- ============================================
CREATE TABLE class_bookings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    class_id INT NOT NULL,
    client_id INT NOT NULL,
    booking_status ENUM('confirmed', 'cancelled', 'attended', 'no_show') NOT NULL DEFAULT 'confirmed',
    booked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    cancelled_at TIMESTAMP NULL,
    notes TEXT,
    created_by INT,
    INDEX idx_class (class_id),
    INDEX idx_client (client_id),
    INDEX idx_status (booking_status),
    INDEX idx_booking_date (booked_at),
    UNIQUE KEY unique_booking (class_id, client_id),
    FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- PAYMENTS TABLE
-- Stores all payment transactions
-- ============================================
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    subscription_id INT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_method ENUM('cash', 'mpesa', 'card', 'bank_transfer') NOT NULL DEFAULT 'cash',
    payment_reference VARCHAR(100),
    payment_date DATE NOT NULL,
    status ENUM('completed', 'pending', 'failed', 'refunded') NOT NULL DEFAULT 'completed',
    description TEXT,
    receipt_number VARCHAR(50) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    processed_by INT,
    INDEX idx_client (client_id),
    INDEX idx_subscription (subscription_id),
    INDEX idx_payment_date (payment_date),
    INDEX idx_status (status),
    INDEX idx_receipt (receipt_number),
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE SET NULL,
    FOREIGN KEY (processed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- ATTENDANCE TABLE
-- Tracks gym check-ins
-- ============================================
CREATE TABLE attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    check_in_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    check_out_time TIMESTAMP NULL,
    visit_date DATE NOT NULL,
    notes TEXT,
    checked_in_by INT,
    INDEX idx_client (client_id),
    INDEX idx_visit_date (visit_date),
    INDEX idx_check_in (check_in_time),
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (checked_in_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- EQUIPMENT TABLE
-- Tracks gym equipment inventory
-- ============================================
CREATE TABLE equipment (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    brand VARCHAR(50),
    model VARCHAR(50),
    serial_number VARCHAR(100) UNIQUE,
    purchase_date DATE,
    purchase_price DECIMAL(10, 2),
    condition_status ENUM('excellent', 'good', 'fair', 'poor', 'broken') DEFAULT 'good',
    location VARCHAR(100),
    maintenance_schedule VARCHAR(100),
    last_maintenance_date DATE,
    next_maintenance_date DATE,
    status ENUM('active', 'maintenance', 'retired') NOT NULL DEFAULT 'active',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category (category),
    INDEX idx_status (status),
    INDEX idx_condition (condition_status),
    INDEX idx_maintenance_date (next_maintenance_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TRAINERS TABLE
-- Stores personal trainer information
-- ============================================
CREATE TABLE trainers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20) NOT NULL,
    specialization VARCHAR(100),
    certifications TEXT,
    bio TEXT,
    hourly_rate DECIMAL(10, 2),
    photo VARCHAR(255),
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    hire_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (first_name, last_name),
    INDEX idx_status (status),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TRAINING_SESSIONS TABLE
-- Stores personal training session bookings
-- ============================================
CREATE TABLE training_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    trainer_id INT NOT NULL,
    session_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    session_type VARCHAR(50),
    status ENUM('scheduled', 'completed', 'cancelled', 'no_show') NOT NULL DEFAULT 'scheduled',
    cost DECIMAL(10, 2),
    payment_status ENUM('unpaid', 'paid', 'partial') DEFAULT 'unpaid',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_client (client_id),
    INDEX idx_trainer (trainer_id),
    INDEX idx_session_date (session_date),
    INDEX idx_status (status),
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (trainer_id) REFERENCES trainers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- NOTIFICATIONS TABLE
-- Stores system notifications and alerts
-- ============================================
CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    client_id INT NULL,
    type VARCHAR(50) NOT NULL,
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    link VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    read_at TIMESTAMP NULL,
    INDEX idx_user (user_id),
    INDEX idx_client (client_id),
    INDEX idx_read (is_read),
    INDEX idx_created (created_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- AUDIT_LOGS TABLE
-- Tracks all system activities for security
-- ============================================
CREATE TABLE audit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    action VARCHAR(100) NOT NULL,
    table_name VARCHAR(50),
    record_id INT,
    old_values JSON,
    new_values JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_action (action),
    INDEX idx_table (table_name),
    INDEX idx_created (created_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- SAMPLE DATA INSERTIONS
-- ============================================

-- Insert default admin user (password: admin123 - should be changed!)
INSERT INTO users (username, email, password, role, first_name, last_name, phone, status) 
VALUES ('admin', 'admin@gym.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'System', 'Administrator', '+254700000000', 'active');

-- Insert sample membership plans
INSERT INTO memberships (name, description, duration_months, price, status, features) VALUES
('Basic Monthly', 'Access to gym facilities during standard hours', 1, 2500.00, 'active', 'Gym access, Locker room, Free WiFi'),
('Premium Monthly', 'Full access with classes and amenities', 1, 4500.00, 'active', 'Gym access, All classes, Locker room, Towel service, Free WiFi'),
('Basic Quarterly', '3-month basic membership with discount', 3, 6500.00, 'active', 'Gym access, Locker room, Free WiFi'),
('Premium Quarterly', '3-month premium with all benefits', 3, 12000.00, 'active', 'Gym access, All classes, Locker room, Towel service, Free WiFi, 1 free PT session'),
('Basic Annual', '12-month basic membership - best value', 12, 24000.00, 'active', 'Gym access, Locker room, Free WiFi'),
('Premium Annual', '12-month premium - complete package', 12, 45000.00, 'active', 'Gym access, All classes, Locker room, Towel service, Free WiFi, 4 free PT sessions, Guest passes');

-- ============================================
-- USEFUL VIEWS
-- ============================================

-- View for active subscriptions with client details
CREATE VIEW active_subscriptions_view AS
SELECT 
    s.id AS subscription_id,
    c.id AS client_id,
    CONCAT(c.first_name, ' ', c.last_name) AS client_name,
    c.email,
    c.phone,
    m.name AS membership_plan,
    m.duration_months,
    m.price,
    s.start_date,
    s.end_date,
    DATEDIFF(s.end_date, CURDATE()) AS days_remaining,
    s.status,
    s.payment_amount,
    s.payment_method
FROM subscriptions s
JOIN clients c ON s.client_id = c.id
JOIN memberships m ON s.membership_id = m.id
WHERE s.status = 'active';

-- View for upcoming classes with booking count
CREATE VIEW upcoming_classes_view AS
SELECT 
    cl.id,
    cl.name,
    cl.instructor,
    cl.class_date,
    cl.start_time,
    cl.end_time,
    cl.capacity,
    COUNT(cb.id) AS current_bookings,
    (cl.capacity - COUNT(cb.id)) AS available_spots,
    cl.room,
    cl.difficulty_level,
    cl.status
FROM classes cl
LEFT JOIN class_bookings cb ON cl.id = cb.class_id AND cb.booking_status = 'confirmed'
WHERE cl.class_date >= CURDATE() AND cl.status = 'scheduled'
GROUP BY cl.id
ORDER BY cl.class_date, cl.start_time;

-- View for revenue summary
CREATE VIEW revenue_summary_view AS
SELECT 
    DATE_FORMAT(payment_date, '%Y-%m') AS month,
    COUNT(*) AS total_transactions,
    SUM(amount) AS total_revenue,
    AVG(amount) AS average_transaction,
    payment_method,
    status
FROM payments
GROUP BY DATE_FORMAT(payment_date, '%Y-%m'), payment_method, status
ORDER BY month DESC;

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

DELIMITER //

-- Procedure to expire old subscriptions
CREATE PROCEDURE expire_subscriptions()
BEGIN
    UPDATE subscriptions 
    SET status = 'expired', 
        updated_at = NOW() 
    WHERE end_date < CURDATE() 
    AND status = 'active';
END //

-- Procedure to get client statistics
CREATE PROCEDURE get_client_stats(IN client_id_param INT)
BEGIN
    SELECT 
        c.first_name,
        c.last_name,
        COUNT(DISTINCT s.id) AS total_subscriptions,
        COUNT(DISTINCT cb.id) AS total_class_bookings,
        COUNT(DISTINCT a.id) AS total_visits,
        SUM(p.amount) AS total_spent
    FROM clients c
    LEFT JOIN subscriptions s ON c.id = s.client_id
    LEFT JOIN class_bookings cb ON c.id = cb.client_id
    LEFT JOIN attendance a ON c.id = a.client_id
    LEFT JOIN payments p ON c.id = p.client_id
    WHERE c.id = client_id_param
    GROUP BY c.id;
END //

DELIMITER ;

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

DELIMITER //

-- Trigger to create notification when subscription is about to expire
CREATE TRIGGER subscription_expiry_notification
AFTER UPDATE ON subscriptions
FOR EACH ROW
BEGIN
    IF NEW.status = 'expired' AND OLD.status = 'active' THEN
        INSERT INTO notifications (client_id, type, title, message)
        VALUES (NEW.client_id, 'subscription_expired', 'Membership Expired', 
                'Your gym membership has expired. Please renew to continue enjoying our facilities.');
    END IF;
END //

-- Trigger to log subscription changes
CREATE TRIGGER audit_subscription_changes
AFTER UPDATE ON subscriptions
FOR EACH ROW
BEGIN
    INSERT INTO audit_logs (table_name, record_id, action, old_values, new_values)
    VALUES ('subscriptions', NEW.id, 'UPDATE',
            JSON_OBJECT('status', OLD.status, 'end_date', OLD.end_date),
            JSON_OBJECT('status', NEW.status, 'end_date', NEW.end_date));
END //

DELIMITER ;

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