-- DATABASE: ruiru_gym
-- Ruiru Gym & Aerobics Management System
-- Created: 2026-01-15

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

-- Table: clients (gym members)
CREATE TABLE IF NOT EXISTS clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    address TEXT,
    date_of_birth DATE,
    gender ENUM('Male', 'Female', 'Other'),
    membership_type ENUM('Basic', 'Premium', 'VIP') DEFAULT 'Basic',
    membership_status ENUM('Active', 'Inactive', 'Suspended') DEFAULT 'Active',
    join_date DATE NOT NULL,
    expiry_date DATE,
    emergency_contact VARCHAR(100),
    emergency_phone VARCHAR(20),
    medical_conditions TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: classes (aerobics/fitness classes)
CREATE TABLE IF NOT EXISTS classes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    class_name VARCHAR(100) NOT NULL,
    description TEXT,
    instructor_id INT,
    capacity INT DEFAULT 20,
    duration INT COMMENT 'Duration in minutes',
    class_type ENUM('Aerobics', 'Yoga', 'Zumba', 'Spinning', 'CrossFit', 'Other') DEFAULT 'Aerobics',
    difficulty_level ENUM('Beginner', 'Intermediate', 'Advanced') DEFAULT 'Beginner',
    status ENUM('Active', 'Cancelled', 'Full') DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (instructor_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: class_schedule
CREATE TABLE IF NOT EXISTS class_schedule (
    id INT AUTO_INCREMENT PRIMARY KEY,
    class_id INT NOT NULL,
    day_of_week ENUM('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday') NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    room VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: bookings (class reservations)
CREATE TABLE IF NOT EXISTS bookings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    class_id INT NOT NULL,
    schedule_id INT NOT NULL,
    booking_date DATE NOT NULL,
    status ENUM('Confirmed', 'Cancelled', 'Completed', 'No-Show') DEFAULT 'Confirmed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
    FOREIGN KEY (schedule_id) REFERENCES class_schedule(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: payments
CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_method ENUM('Cash', 'M-Pesa', 'Card', 'Bank Transfer') DEFAULT 'Cash',
    payment_type ENUM('Membership', 'Class', 'Personal Training', 'Other') DEFAULT 'Membership',
    payment_date DATE NOT NULL,
    reference_number VARCHAR(100),
    status ENUM('Completed', 'Pending', 'Failed', 'Refunded') DEFAULT 'Completed',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: attendance
CREATE TABLE IF NOT EXISTS attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    check_in_time DATETIME NOT NULL,
    check_out_time DATETIME,
    attendance_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: equipment
CREATE TABLE IF NOT EXISTS equipment (
    id INT AUTO_INCREMENT PRIMARY KEY,
    equipment_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    quantity INT DEFAULT 1,
    condition_status ENUM('Excellent', 'Good', 'Fair', 'Needs Repair', 'Out of Service') DEFAULT 'Good',
    purchase_date DATE,
    last_maintenance_date DATE,
    next_maintenance_date DATE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert sample data for testing
INSERT INTO clients (first_name, last_name, email, phone, membership_type, membership_status, join_date, expiry_date) VALUES
('John', 'Doe', 'john.doe@email.com', '0712345678', 'Premium', 'Active', '2024-01-15', '2025-01-15'),
('Jane', 'Smith', 'jane.smith@email.com', '0723456789', 'Basic', 'Active', '2024-03-20', '2025-03-20'),
('Mike', 'Johnson', 'mike.j@email.com', '0734567890', 'VIP', 'Active', '2024-02-10', '2025-02-10');

INSERT INTO classes (class_name, description, capacity, duration, class_type, difficulty_level) VALUES
('Morning Aerobics', 'High-energy aerobic workout to start your day', 25, 60, 'Aerobics', 'Intermediate'),
('Yoga Flow', 'Relaxing yoga session for flexibility and mindfulness', 20, 75, 'Yoga', 'Beginner'),
('Zumba Party', 'Dance fitness workout with Latin rhythms', 30, 60, 'Zumba', 'Beginner'),
('Advanced CrossFit', 'Intense functional fitness training', 15, 90, 'CrossFit', 'Advanced');

-- Insert sample class schedules
INSERT INTO class_schedule (class_id, day_of_week, start_time, end_time, room) VALUES
(1, 'Monday', '06:00:00', '07:00:00', 'Studio A'),
(1, 'Wednesday', '06:00:00', '07:00:00', 'Studio A'),
(1, 'Friday', '06:00:00', '07:00:00', 'Studio A'),
(2, 'Tuesday', '18:00:00', '19:15:00', 'Studio B'),
(2, 'Thursday', '18:00:00', '19:15:00', 'Studio B'),
(3, 'Saturday', '10:00:00', '11:00:00', 'Main Hall'),
(4, 'Monday', '17:00:00', '18:30:00', 'CrossFit Zone'),
(4, 'Friday', '17:00:00', '18:30:00', 'CrossFit Zone');

-- Create indexes for better performance
CREATE INDEX idx_clients_email ON clients(email);
CREATE INDEX idx_clients_membership_status ON clients(membership_status);
CREATE INDEX idx_bookings_client ON bookings(client_id);
CREATE INDEX idx_bookings_class ON bookings(class_id);
CREATE INDEX idx_bookings_date ON bookings(booking_date);
CREATE INDEX idx_payments_client ON payments(client_id);
CREATE INDEX idx_payments_date ON payments(payment_date);
CREATE INDEX idx_attendance_client ON attendance(client_id);
CREATE INDEX idx_attendance_date ON attendance(attendance_date);