-- ============================================================================
-- RUIRU GYM AND AEROBICS - CLIENT MANAGEMENT TABLES
-- File: 1_client_tables.sql
-- Purpose: Tables specifically for client management system
-- ============================================================================

USE ruiru_gym;

-- ============================================================================
-- DROP EXISTING TABLES IF THEY EXIST (for clean installation)
-- ============================================================================
-- Note: Uncomment the following lines only if you want to recreate tables
-- DROP TABLE IF EXISTS client_notes;
-- DROP TABLE IF EXISTS client_goals;
-- DROP TABLE IF EXISTS client_body_measurements;
-- DROP TABLE IF EXISTS attendance;
-- DROP TABLE IF EXISTS payments;
-- DROP TABLE IF EXISTS memberships;
-- DROP TABLE IF EXISTS clients;

-- ============================================================================
-- CLIENTS TABLE - Main client information
-- ============================================================================
CREATE TABLE IF NOT EXISTS clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    full_name VARCHAR(100) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20) NOT NULL,
    date_of_birth DATE NULL,
    gender ENUM('Male', 'Female', 'Other') NULL,
    address TEXT NULL,
    
    -- Emergency Contact Information
    emergency_contact VARCHAR(100) NULL,
    emergency_phone VARCHAR(20) NULL,
    emergency_relationship VARCHAR(50) NULL,
    
    -- Medical Information
    medical_conditions TEXT NULL,
    allergies TEXT NULL,
    medications TEXT NULL,
    blood_type VARCHAR(10) NULL,
    
    -- Profile and Documents
    profile_photo VARCHAR(255) NULL,
    id_number VARCHAR(50) NULL,
    
    -- Account Status
    status ENUM('active', 'inactive', 'suspended', 'pending') DEFAULT 'active',
    
    -- Referral Information
    referred_by VARCHAR(100) NULL,
    referral_code VARCHAR(20) UNIQUE NULL,
    
    -- Preferences
    preferred_contact_method ENUM('email', 'phone', 'sms', 'whatsapp') DEFAULT 'phone',
    newsletter_subscription BOOLEAN DEFAULT TRUE,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login DATETIME NULL,
    
    -- Indexes for performance
    INDEX idx_email (email),
    INDEX idx_phone (phone),
    INDEX idx_status (status),
    INDEX idx_name (first_name, last_name),
    INDEX idx_created (created_at),
    INDEX idx_referral (referral_code),
    
    -- Full-text search index
    FULLTEXT INDEX ft_client_search (first_name, last_name, email, phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- MEMBERSHIPS TABLE - Client membership subscriptions
-- ============================================================================
CREATE TABLE IF NOT EXISTS memberships (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    membership_type VARCHAR(50) NOT NULL,
    membership_plan ENUM('Basic', 'Standard', 'Premium', 'Elite', 'VIP', 'Student', 'Corporate') NOT NULL,
    
    -- Date Information
    start_date DATE NOT NULL,
    expiry_date DATE NOT NULL,
    renewal_date DATE NULL,
    
    -- Status and Settings
    status ENUM('active', 'expired', 'cancelled', 'suspended', 'pending') DEFAULT 'active',
    auto_renew BOOLEAN DEFAULT FALSE,
    freeze_count INT DEFAULT 0,
    freeze_days_remaining INT DEFAULT 0,
    
    -- Pricing Information
    price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(10, 2) DEFAULT 0.00,
    discount_reason VARCHAR(255) NULL,
    
    -- Access and Benefits
    access_level ENUM('basic', 'standard', 'premium', 'vip') DEFAULT 'basic',
    gym_access BOOLEAN DEFAULT TRUE,
    class_access BOOLEAN DEFAULT TRUE,
    personal_training_sessions INT DEFAULT 0,
    guest_passes INT DEFAULT 0,
    
    -- Additional Information
    notes TEXT NULL,
    cancellation_reason TEXT NULL,
    cancelled_date DATETIME NULL,
    cancelled_by INT NULL,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Foreign Keys
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (cancelled_by) REFERENCES users(id) ON DELETE SET NULL,
    
    -- Indexes
    INDEX idx_client (client_id),
    INDEX idx_status (status),
    INDEX idx_dates (start_date, expiry_date),
    INDEX idx_type (membership_type),
    INDEX idx_plan (membership_plan),
    INDEX idx_expiry (expiry_date),
    INDEX idx_active_memberships (client_id, status, expiry_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- PAYMENTS TABLE - Financial transactions for clients
-- ============================================================================
CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    
    -- Payment Details
    amount DECIMAL(10, 2) NOT NULL,
    payment_method ENUM('Cash', 'M-Pesa', 'Bank Transfer', 'Card', 'Cheque', 'Other') NOT NULL,
    payment_date DATETIME NOT NULL,
    
    -- Transaction Information
    description TEXT NULL,
    transaction_reference VARCHAR(100) NULL,
    receipt_number VARCHAR(50) UNIQUE NULL,
    invoice_number VARCHAR(50) NULL,
    
    -- Status and Processing
    status ENUM('completed', 'pending', 'failed', 'refunded', 'cancelled') DEFAULT 'completed',
    processed_by INT NULL,
    approved_by INT NULL,
    
    -- Refund Information
    refund_amount DECIMAL(10, 2) NULL,
    refund_date DATETIME NULL,
    refund_reason TEXT NULL,
    
    -- Related Records
    membership_id INT NULL,
    invoice_id INT NULL,
    
    -- Additional Information
    notes TEXT NULL,
    metadata JSON NULL,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Foreign Keys
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (processed_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (membership_id) REFERENCES memberships(id) ON DELETE SET NULL,
    
    -- Indexes
    INDEX idx_client (client_id),
    INDEX idx_payment_date (payment_date),
    INDEX idx_method (payment_method),
    INDEX idx_status (status),
    INDEX idx_amount (amount),
    INDEX idx_receipt (receipt_number),
    INDEX idx_transaction (transaction_reference),
    INDEX idx_client_date (client_id, payment_date),
    INDEX idx_membership (membership_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- ATTENDANCE TABLE - Client gym check-ins and visits
-- ============================================================================
CREATE TABLE IF NOT EXISTS attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    
    -- Check-in/Check-out Information
    check_in_time DATETIME NOT NULL,
    check_out_time DATETIME NULL,
    duration_minutes INT NULL,
    
    -- Visit Details
    visit_type ENUM('gym', 'class', 'personal_training', 'other') DEFAULT 'gym',
    location VARCHAR(100) NULL,
    area_accessed VARCHAR(100) NULL,
    
    -- Status and Verification
    status ENUM('active', 'completed', 'cancelled') DEFAULT 'active',
    verified_by INT NULL,
    temperature_check DECIMAL(4, 1) NULL,
    
    -- Additional Information
    notes TEXT NULL,
    activity_details TEXT NULL,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Foreign Keys
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL,
    
    -- Indexes
    INDEX idx_client (client_id),
    INDEX idx_check_in (check_in_time),
    INDEX idx_check_out (check_out_time),
    INDEX idx_date (check_in_time, check_out_time),
    INDEX idx_status (status),
    INDEX idx_visit_type (visit_type),
    INDEX idx_client_date (client_id, check_in_time),
    INDEX idx_active_sessions (client_id, check_out_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- CLIENT_NOTES TABLE - Staff notes about clients
-- ============================================================================
CREATE TABLE IF NOT EXISTS client_notes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    
    -- Note Information
    note_type ENUM('general', 'medical', 'complaint', 'compliment', 'reminder', 'warning') NOT NULL,
    title VARCHAR(200) NOT NULL,
    note_text TEXT NOT NULL,
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    
    -- Visibility and Status
    is_private BOOLEAN DEFAULT FALSE,
    is_alert BOOLEAN DEFAULT FALSE,
    status ENUM('active', 'resolved', 'archived') DEFAULT 'active',
    
    -- User Information
    created_by INT NOT NULL,
    updated_by INT NULL,
    
    -- Follow-up
    follow_up_date DATE NULL,
    followed_up BOOLEAN DEFAULT FALSE,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Foreign Keys
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
    
    -- Indexes
    INDEX idx_client (client_id),
    INDEX idx_type (note_type),
    INDEX idx_priority (priority),
    INDEX idx_status (status),
    INDEX idx_created (created_at),
    INDEX idx_follow_up (follow_up_date, followed_up)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- CLIENT_GOALS TABLE - Fitness goals and tracking
-- ============================================================================
CREATE TABLE IF NOT EXISTS client_goals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    
    -- Goal Information
    goal_type ENUM('weight_loss', 'muscle_gain', 'endurance', 'flexibility', 'general_fitness', 'other') NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT NULL,
    
    -- Target Information
    target_value DECIMAL(10, 2) NULL,
    current_value DECIMAL(10, 2) NULL,
    unit VARCHAR(20) NULL,
    
    -- Timeline
    start_date DATE NOT NULL,
    target_date DATE NOT NULL,
    completion_date DATE NULL,
    
    -- Status and Progress
    status ENUM('active', 'completed', 'abandoned', 'postponed') DEFAULT 'active',
    progress_percentage DECIMAL(5, 2) DEFAULT 0.00,
    
    -- Notes and Updates
    notes TEXT NULL,
    milestones JSON NULL,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Foreign Keys
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    
    -- Indexes
    INDEX idx_client (client_id),
    INDEX idx_type (goal_type),
    INDEX idx_status (status),
    INDEX idx_dates (start_date, target_date),
    INDEX idx_active_goals (client_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- CLIENT_BODY_MEASUREMENTS TABLE - Track physical measurements
-- ============================================================================
CREATE TABLE IF NOT EXISTS client_body_measurements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    
    -- Measurement Date
    measurement_date DATE NOT NULL,
    
    -- Weight and Body Composition
    weight_kg DECIMAL(5, 2) NULL,
    body_fat_percentage DECIMAL(5, 2) NULL,
    muscle_mass_kg DECIMAL(5, 2) NULL,
    bmi DECIMAL(5, 2) NULL,
    
    -- Body Measurements (in cm)
    chest_cm DECIMAL(5, 2) NULL,
    waist_cm DECIMAL(5, 2) NULL,
    hips_cm DECIMAL(5, 2) NULL,
    thigh_cm DECIMAL(5, 2) NULL,
    arm_cm DECIMAL(5, 2) NULL,
    calf_cm DECIMAL(5, 2) NULL,
    neck_cm DECIMAL(5, 2) NULL,
    
    -- Additional Metrics
    visceral_fat INT NULL,
    water_percentage DECIMAL(5, 2) NULL,
    bone_mass_kg DECIMAL(5, 2) NULL,
    metabolic_age INT NULL,
    
    -- Notes
    notes TEXT NULL,
    measured_by INT NULL,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Foreign Keys
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (measured_by) REFERENCES users(id) ON DELETE SET NULL,
    
    -- Indexes
    INDEX idx_client (client_id),
    INDEX idx_date (measurement_date),
    INDEX idx_client_date (client_id, measurement_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- CLIENT_SEARCH_HISTORY TABLE - Track client searches for analytics
-- ============================================================================
CREATE TABLE IF NOT EXISTS client_search_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    searched_by INT NOT NULL,
    search_term VARCHAR(255) NOT NULL,
    results_count INT DEFAULT 0,
    filters_applied JSON NULL,
    search_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (searched_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (searched_by),
    INDEX idx_timestamp (search_timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- CLIENT_STATUS_HISTORY TABLE - Track status changes
-- ============================================================================
CREATE TABLE IF NOT EXISTS client_status_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    old_status VARCHAR(20) NOT NULL,
    new_status VARCHAR(20) NOT NULL,
    reason TEXT NULL,
    changed_by INT NOT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_client (client_id),
    INDEX idx_date (changed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- End of Client Management Tables
-- ============================================================================