-- ============================================================================
-- RUIRU GYM AND AEROBICS - CLIENT TRIGGERS & PROCEDURES
-- File: 3_client_triggers_procedures.sql
-- Purpose: Automated business logic for client management
-- ============================================================================

USE ruiru_gym;

-- Drop existing triggers and procedures
DROP TRIGGER IF EXISTS before_client_insert;
DROP TRIGGER IF EXISTS after_client_delete;
DROP TRIGGER IF EXISTS after_attendance_insert;
DROP TRIGGER IF EXISTS before_attendance_update;
DROP TRIGGER IF EXISTS after_membership_insert;
DROP TRIGGER IF EXISTS before_membership_expire;
DROP TRIGGER IF EXISTS after_payment_insert;
DROP TRIGGER IF EXISTS after_client_status_change;

DROP PROCEDURE IF EXISTS get_client_full_profile;
DROP PROCEDURE IF EXISTS get_client_statistics;
DROP PROCEDURE IF EXISTS search_clients_advanced;
DROP PROCEDURE IF EXISTS check_membership_status;
DROP PROCEDURE IF EXISTS calculate_client_metrics;
DROP PROCEDURE IF EXISTS get_expiring_memberships;
DROP PROCEDURE IF EXISTS get_attendance_summary;
DROP PROCEDURE IF EXISTS get_payment_summary;

DELIMITER //

-- ============================================================================
-- TRIGGER: Generate referral code before client insert
-- ============================================================================
CREATE TRIGGER before_client_insert
BEFORE INSERT ON clients
FOR EACH ROW
BEGIN
    IF NEW.referral_code IS NULL THEN
        SET NEW.referral_code = CONCAT('REF', LPAD(FLOOR(RAND() * 999999), 6, '0'));
    END IF;
END//

-- ============================================================================
-- TRIGGER: Log client deletion
-- ============================================================================
CREATE TRIGGER after_client_delete
BEFORE DELETE ON clients
FOR EACH ROW
BEGIN
    INSERT INTO activity_log (user_id, action, entity_type, entity_id, description, created_at)
    VALUES (
        NULL,
        'DELETE',
        'clients',
        OLD.id,
        CONCAT('Client deleted: ', OLD.first_name, ' ', OLD.last_name, ' (', OLD.email, ')'),
        NOW()
    );
END//

-- ============================================================================
-- TRIGGER: Record attendance check-in in activity log
-- ============================================================================
CREATE TRIGGER after_attendance_insert
AFTER INSERT ON attendance
FOR EACH ROW
BEGIN
    DECLARE client_name VARCHAR(100);
    
    SELECT CONCAT(first_name, ' ', last_name) INTO client_name
    FROM clients WHERE id = NEW.client_id;
    
    INSERT INTO activity_log (user_id, action, entity_type, entity_id, description, created_at)
    VALUES (
        NEW.verified_by,
        'CHECK_IN',
        'attendance',
        NEW.id,
        CONCAT('Client check-in: ', client_name),
        NOW()
    );
END//

-- ============================================================================
-- TRIGGER: Calculate duration on attendance check-out
-- ============================================================================
CREATE TRIGGER before_attendance_update
BEFORE UPDATE ON attendance
FOR EACH ROW
BEGIN
    IF NEW.check_out_time IS NOT NULL AND OLD.check_out_time IS NULL THEN
        SET NEW.duration_minutes = TIMESTAMPDIFF(MINUTE, NEW.check_in_time, NEW.check_out_time);
        SET NEW.status = 'completed';
    END IF;
END//

-- ============================================================================
-- TRIGGER: Create notification when membership is created
-- ============================================================================
CREATE TRIGGER after_membership_insert
AFTER INSERT ON memberships
FOR EACH ROW
BEGIN
    INSERT INTO notifications (client_id, type, title, message, is_read, created_at)
    VALUES (
        NEW.client_id,
        'general',
        'Membership Activated',
        CONCAT('Your ', NEW.membership_plan, ' membership has been activated! Valid from ',
               DATE_FORMAT(NEW.start_date, '%M %d, %Y'), ' to ', 
               DATE_FORMAT(NEW.expiry_date, '%M %d, %Y'), '.'),
        FALSE,
        NOW()
    );
END//

-- ============================================================================
-- TRIGGER: Handle membership expiration and notifications
-- ============================================================================
CREATE TRIGGER before_membership_expire
BEFORE UPDATE ON memberships
FOR EACH ROW
BEGIN
    -- Auto-expire memberships that have passed their expiry date
    IF NEW.status = 'active' AND NEW.expiry_date < CURDATE() THEN
        SET NEW.status = 'expired';
        
        -- Create notification
        INSERT INTO notifications (client_id, type, title, message, is_read, created_at)
        VALUES (
            NEW.client_id,
            'membership_expiry',
            'Membership Expired',
            CONCAT('Your ', NEW.membership_plan, ' membership expired on ', 
                   DATE_FORMAT(NEW.expiry_date, '%M %d, %Y'), 
                   '. Please renew to continue accessing our facilities.'),
            FALSE,
            NOW()
        );
    END IF;
    
    -- Warn about expiring memberships (7 days before)
    IF NEW.status = 'active' AND 
       DATEDIFF(NEW.expiry_date, CURDATE()) <= 7 AND 
       DATEDIFF(NEW.expiry_date, CURDATE()) > 0 THEN
        
        -- Check if warning already sent today
        IF NOT EXISTS (
            SELECT 1 FROM notifications 
            WHERE client_id = NEW.client_id 
            AND type = 'membership_expiry' 
            AND DATE(created_at) = CURDATE()
            AND message LIKE '%expiring soon%'
        ) THEN
            INSERT INTO notifications (client_id, type, title, message, is_read, created_at)
            VALUES (
                NEW.client_id,
                'membership_expiry',
                'Membership Expiring Soon',
                CONCAT('Your ', NEW.membership_plan, ' membership is expiring on ', 
                       DATE_FORMAT(NEW.expiry_date, '%M %d, %Y'), 
                       '. Renew now to avoid interruption.'),
                FALSE,
                NOW()
            );
        END IF;
    END IF;
END//

-- ============================================================================
-- TRIGGER: Log payment transactions
-- ============================================================================
CREATE TRIGGER after_payment_insert
AFTER INSERT ON payments
FOR EACH ROW
BEGIN
    DECLARE client_name VARCHAR(100);
    
    SELECT CONCAT(first_name, ' ', last_name) INTO client_name
    FROM clients WHERE id = NEW.client_id;
    
    INSERT INTO activity_log (user_id, action, entity_type, entity_id, description, created_at)
    VALUES (
        NEW.processed_by,
        'PAYMENT',
        'payments',
        NEW.id,
        CONCAT('Payment received: KSh ', NEW.amount, ' from ', client_name, 
               ' via ', NEW.payment_method),
        NOW()
    );
END//

-- ============================================================================
-- TRIGGER: Track client status changes
-- ============================================================================
CREATE TRIGGER after_client_status_change
AFTER UPDATE ON clients
FOR EACH ROW
BEGIN
    IF OLD.status != NEW.status THEN
        INSERT INTO client_status_history (client_id, old_status, new_status, changed_by, changed_at)
        VALUES (NEW.id, OLD.status, NEW.status, NULL, NOW());
    END IF;
END//

-- ============================================================================
-- PROCEDURE: Get complete client profile with all related data
-- ============================================================================
CREATE PROCEDURE get_client_full_profile(IN p_client_id INT)
BEGIN
    -- Client basic information
    SELECT * FROM clients WHERE id = p_client_id;
    
    -- Active membership
    SELECT * FROM memberships 
    WHERE client_id = p_client_id AND status = 'active'
    ORDER BY created_at DESC LIMIT 1;
    
    -- Recent payments (last 10)
    SELECT * FROM payments 
    WHERE client_id = p_client_id 
    ORDER BY payment_date DESC LIMIT 10;
    
    -- Recent attendance (last 20)
    SELECT * FROM attendance 
    WHERE client_id = p_client_id 
    ORDER BY check_in_time DESC LIMIT 20;
    
    -- Active goals
    SELECT * FROM client_goals 
    WHERE client_id = p_client_id AND status = 'active'
    ORDER BY target_date;
    
    -- Recent notes
    SELECT n.*, u.full_name as created_by_name
    FROM client_notes n
    LEFT JOIN users u ON n.created_by = u.id
    WHERE n.client_id = p_client_id
    ORDER BY n.created_at DESC LIMIT 10;
    
    -- Latest body measurements
    SELECT * FROM client_body_measurements 
    WHERE client_id = p_client_id 
    ORDER BY measurement_date DESC LIMIT 5;
END//

-- ============================================================================
-- PROCEDURE: Get comprehensive client statistics
-- ============================================================================
CREATE PROCEDURE get_client_statistics(IN p_client_id INT)
BEGIN
    SELECT 
        c.id,
        c.first_name,
        c.last_name,
        c.email,
        c.phone,
        c.status,
        c.created_at as member_since,
        
        -- Membership info
        m.membership_plan,
        m.membership_type,
        m.start_date as membership_start,
        m.expiry_date as membership_expiry,
        m.status as membership_status,
        DATEDIFF(m.expiry_date, CURDATE()) as days_remaining,
        
        -- Payment statistics
        COALESCE(pay_stats.total_paid, 0) as total_paid,
        COALESCE(pay_stats.total_payments, 0) as total_payments,
        COALESCE(pay_stats.paid_this_year, 0) as paid_this_year,
        COALESCE(pay_stats.paid_this_month, 0) as paid_this_month,
        COALESCE(pay_stats.last_payment_date, NULL) as last_payment_date,
        
        -- Attendance statistics
        COALESCE(att_stats.total_visits, 0) as total_visits,
        COALESCE(att_stats.visits_this_month, 0) as visits_this_month,
        COALESCE(att_stats.visits_this_week, 0) as visits_this_week,
        COALESCE(att_stats.avg_duration_minutes, 0) as avg_session_duration,
        COALESCE(att_stats.last_visit, NULL) as last_visit,
        
        -- Goal statistics
        COALESCE(goal_stats.total_goals, 0) as total_goals,
        COALESCE(goal_stats.active_goals, 0) as active_goals,
        COALESCE(goal_stats.completed_goals, 0) as completed_goals,
        
        -- Engagement metrics
        DATEDIFF(CURDATE(), c.created_at) as days_as_member,
        CASE 
            WHEN att_stats.total_visits > 0 THEN 
                ROUND(att_stats.total_visits / (DATEDIFF(CURDATE(), c.created_at) / 30), 2)
            ELSE 0 
        END as avg_visits_per_month
        
    FROM clients c
    LEFT JOIN memberships m ON c.id = m.client_id AND m.status = 'active'
    LEFT JOIN (
        SELECT 
            client_id,
            SUM(amount) as total_paid,
            COUNT(*) as total_payments,
            SUM(CASE WHEN YEAR(payment_date) = YEAR(CURDATE()) THEN amount ELSE 0 END) as paid_this_year,
            SUM(CASE WHEN MONTH(payment_date) = MONTH(CURDATE()) AND YEAR(payment_date) = YEAR(CURDATE()) THEN amount ELSE 0 END) as paid_this_month,
            MAX(payment_date) as last_payment_date
        FROM payments
        WHERE status = 'completed'
        GROUP BY client_id
    ) pay_stats ON c.id = pay_stats.client_id
    LEFT JOIN (
        SELECT 
            client_id,
            COUNT(*) as total_visits,
            SUM(CASE WHEN MONTH(check_in_time) = MONTH(CURDATE()) AND YEAR(check_in_time) = YEAR(CURDATE()) THEN 1 ELSE 0 END) as visits_this_month,
            SUM(CASE WHEN WEEK(check_in_time, 1) = WEEK(CURDATE(), 1) AND YEAR(check_in_time) = YEAR(CURDATE()) THEN 1 ELSE 0 END) as visits_this_week,
            AVG(duration_minutes) as avg_duration_minutes,
            MAX(check_in_time) as last_visit
        FROM attendance
        WHERE status = 'completed'
        GROUP BY client_id
    ) att_stats ON c.id = att_stats.client_id
    LEFT JOIN (
        SELECT 
            client_id,
            COUNT(*) as total_goals,
            SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_goals,
            SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_goals
        FROM client_goals
        GROUP BY client_id
    ) goal_stats ON c.id = goal_stats.client_id
    WHERE c.id = p_client_id;
END//

-- ============================================================================
-- PROCEDURE: Advanced client search
-- ============================================================================
CREATE PROCEDURE search_clients_advanced(
    IN p_search_term VARCHAR(255),
    IN p_status VARCHAR(20),
    IN p_membership_plan VARCHAR(50),
    IN p_has_active_membership BOOLEAN,
    IN p_limit INT,
    IN p_offset INT
)
BEGIN
    SET @sql = '
        SELECT DISTINCT
            c.*,
            m.membership_plan,
            m.membership_type,
            m.expiry_date,
            m.status as membership_status,
            COALESCE(att.visit_count, 0) as total_visits,
            COALESCE(pay.total_paid, 0) as total_paid
        FROM clients c
        LEFT JOIN memberships m ON c.id = m.client_id AND m.status = "active"
        LEFT JOIN (
            SELECT client_id, COUNT(*) as visit_count 
            FROM attendance 
            GROUP BY client_id
        ) att ON c.id = att.client_id
        LEFT JOIN (
            SELECT client_id, SUM(amount) as total_paid 
            FROM payments 
            WHERE status = "completed"
            GROUP BY client_id
        ) pay ON c.id = pay.client_id
        WHERE 1=1
    ';
    
    -- Add search term filter
    IF p_search_term IS NOT NULL AND p_search_term != '' THEN
        SET @sql = CONCAT(@sql, ' AND (
            c.first_name LIKE "%', p_search_term, '%" OR
            c.last_name LIKE "%', p_search_term, '%" OR
            c.email LIKE "%', p_search_term, '%" OR
            c.phone LIKE "%', p_search_term, '%"
        )');
    END IF;
    
    -- Add status filter
    IF p_status IS NOT NULL AND p_status != '' AND p_status != 'all' THEN
        SET @sql = CONCAT(@sql, ' AND c.status = "', p_status, '"');
    END IF;
    
    -- Add membership plan filter
    IF p_membership_plan IS NOT NULL AND p_membership_plan != '' AND p_membership_plan != 'all' THEN
        SET @sql = CONCAT(@sql, ' AND m.membership_plan = "', p_membership_plan, '"');
    END IF;
    
    -- Add active membership filter
    IF p_has_active_membership = TRUE THEN
        SET @sql = CONCAT(@sql, ' AND m.id IS NOT NULL AND m.status = "active"');
    END IF;
    
    SET @sql = CONCAT(@sql, ' ORDER BY c.created_at DESC');
    
    -- Add pagination
    IF p_limit > 0 THEN
        SET @sql = CONCAT(@sql, ' LIMIT ', p_limit);
        IF p_offset > 0 THEN
            SET @sql = CONCAT(@sql, ' OFFSET ', p_offset);
        END IF;
    END IF;
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END//

-- ============================================================================
-- PROCEDURE: Check and update membership status
-- ============================================================================
CREATE PROCEDURE check_membership_status(IN p_client_id INT)
BEGIN
    DECLARE v_expiry_date DATE;
    DECLARE v_membership_id INT;
    DECLARE v_status VARCHAR(20);
    
    SELECT id, expiry_date, status INTO v_membership_id, v_expiry_date, v_status
    FROM memberships
    WHERE client_id = p_client_id AND status = 'active'
    ORDER BY created_at DESC LIMIT 1;
    
    IF v_membership_id IS NOT NULL THEN
        IF v_expiry_date < CURDATE() THEN
            -- Expire the membership
            UPDATE memberships SET status = 'expired' WHERE id = v_membership_id;
            
            -- Update client status to inactive if no other active memberships
            IF NOT EXISTS (
                SELECT 1 FROM memberships 
                WHERE client_id = p_client_id AND status = 'active' AND id != v_membership_id
            ) THEN
                UPDATE clients SET status = 'inactive' WHERE id = p_client_id;
            END IF;
            
            SELECT 'expired' as result, v_expiry_date as expiry_date;
        ELSE
            SELECT 'active' as result, v_expiry_date as expiry_date, 
                   DATEDIFF(v_expiry_date, CURDATE()) as days_remaining;
        END IF;
    ELSE
        SELECT 'no_membership' as result;
    END IF;
END//

-- ============================================================================
-- PROCEDURE: Calculate client engagement metrics
-- ============================================================================
CREATE PROCEDURE calculate_client_metrics(IN p_client_id INT)
BEGIN
    SELECT 
        -- Attendance metrics
        COUNT(DISTINCT DATE(check_in_time)) as unique_visit_days,
        COUNT(*) as total_check_ins,
        AVG(duration_minutes) as avg_session_duration,
        MAX(check_in_time) as last_visit,
        MIN(check_in_time) as first_visit,
        
        -- Frequency analysis
        CASE 
            WHEN COUNT(DISTINCT WEEK(check_in_time, 1)) > 0 THEN
                COUNT(*) / COUNT(DISTINCT WEEK(check_in_time, 1))
            ELSE 0 
        END as avg_visits_per_week,
        
        -- Recent activity
        SUM(CASE WHEN check_in_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN 1 ELSE 0 END) as visits_last_7_days,
        SUM(CASE WHEN check_in_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) as visits_last_30_days,
        
        -- Engagement level
        CASE 
            WHEN MAX(check_in_time) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN 'highly_active'
            WHEN MAX(check_in_time) >= DATE_SUB(CURDATE(), INTERVAL 14 DAY) THEN 'active'
            WHEN MAX(check_in_time) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN 'moderate'
            WHEN MAX(check_in_time) >= DATE_SUB(CURDATE(), INTERVAL 60 DAY) THEN 'low'
            ELSE 'inactive'
        END as engagement_level
        
    FROM attendance
    WHERE client_id = p_client_id AND status = 'completed';
END//

-- ============================================================================
-- PROCEDURE: Get memberships expiring in next N days
-- ============================================================================
CREATE PROCEDURE get_expiring_memberships(IN p_days INT)
BEGIN
    SELECT 
        c.id as client_id,
        CONCAT(c.first_name, ' ', c.last_name) as client_name,
        c.email,
        c.phone,
        m.membership_plan,
        m.membership_type,
        m.expiry_date,
        DATEDIFF(m.expiry_date, CURDATE()) as days_remaining,
        m.price as membership_price
    FROM memberships m
    INNER JOIN clients c ON m.client_id = c.id
    WHERE m.status = 'active'
    AND m.expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL p_days DAY)
    ORDER BY m.expiry_date ASC;
END//

-- ============================================================================
-- PROCEDURE: Get attendance summary for date range
-- ============================================================================
CREATE PROCEDURE get_attendance_summary(
    IN p_start_date DATE,
    IN p_end_date DATE
)
BEGIN
    SELECT 
        DATE(check_in_time) as date,
        COUNT(DISTINCT client_id) as unique_clients,
        COUNT(*) as total_check_ins,
        SUM(CASE WHEN check_out_time IS NOT NULL THEN 1 ELSE 0 END) as completed_sessions,
        SUM(CASE WHEN check_out_time IS NULL THEN 1 ELSE 0 END) as in_progress,
        AVG(duration_minutes) as avg_duration,
        MAX(duration_minutes) as max_duration,
        SUM(CASE WHEN visit_type = 'gym' THEN 1 ELSE 0 END) as gym_visits,
        SUM(CASE WHEN visit_type = 'class' THEN 1 ELSE 0 END) as class_visits,
        SUM(CASE WHEN visit_type = 'personal_training' THEN 1 ELSE 0 END) as pt_sessions
    FROM attendance
    WHERE DATE(check_in_time) BETWEEN p_start_date AND p_end_date
    GROUP BY DATE(check_in_time)
    ORDER BY DATE(check_in_time) DESC;
END//

-- ============================================================================
-- PROCEDURE: Get payment summary for client
-- ============================================================================
CREATE PROCEDURE get_payment_summary(
    IN p_client_id INT,
    IN p_start_date DATE,
    IN p_end_date DATE
)
BEGIN
    -- Summary statistics
    SELECT 
        COUNT(*) as total_transactions,
        SUM(amount) as total_amount,
        AVG(amount) as avg_amount,
        MIN(amount) as min_amount,
        MAX(amount) as max_amount,
        SUM(CASE WHEN payment_method = 'M-Pesa' THEN amount ELSE 0 END) as mpesa_total,
        SUM(CASE WHEN payment_method = 'Cash' THEN amount ELSE 0 END) as cash_total,
        SUM(CASE WHEN payment_method = 'Card' THEN amount ELSE 0 END) as card_total,
        SUM(CASE WHEN payment_method = 'Bank Transfer' THEN amount ELSE 0 END) as bank_total
    FROM payments
    WHERE client_id = p_client_id
    AND DATE(payment_date) BETWEEN p_start_date AND p_end_date
    AND status = 'completed';
    
    -- Detailed transactions
    SELECT *
    FROM payments
    WHERE client_id = p_client_id
    AND DATE(payment_date) BETWEEN p_start_date AND p_end_date
    ORDER BY payment_date DESC;
END//

DELIMITER ;

-- ============================================================================
-- End of Client Triggers and Procedures
-- ============================================================================