-- ============================================================================
-- RUIRU GYM AND AEROBICS - SECURITY CONFIGURATION & USER MANAGEMENT
-- File: 5_security_config.sql
-- Purpose: Database security, user roles, and backup configuration
-- ============================================================================

USE ruiru_gym;

-- ============================================================================
-- DATABASE USERS AND ROLES
-- ============================================================================

-- Drop existing users if they exist (for clean setup)
DROP USER IF EXISTS 'gym_admin'@'localhost';
DROP USER IF EXISTS 'gym_staff'@'localhost';
DROP USER IF EXISTS 'gym_readonly'@'localhost';
DROP USER IF EXISTS 'gym_backup'@'localhost';

-- ============================================================================
-- 1. GYM ADMIN USER - Full database access
-- ============================================================================
CREATE USER 'gym_admin'@'localhost' IDENTIFIED BY 'Admin@Gym2024!';

-- Grant all privileges on the gym database
GRANT ALL PRIVILEGES ON ruiru_gym.* TO 'gym_admin'@'localhost';

-- Grant procedure execution rights
GRANT EXECUTE ON ruiru_gym.* TO 'gym_admin'@'localhost';

-- Grant event scheduler privileges
GRANT EVENT ON ruiru_gym.* TO 'gym_admin'@'localhost';

-- ============================================================================
-- 2. GYM STAFF USER - Standard operational access
-- ============================================================================
CREATE USER 'gym_staff'@'localhost' IDENTIFIED BY 'Staff@Gym2024!';

-- Grant SELECT, INSERT, UPDATE on most tables
GRANT SELECT, INSERT, UPDATE ON ruiru_gym.clients TO 'gym_staff'@'localhost';
GRANT SELECT, INSERT, UPDATE ON ruiru_gym.memberships TO 'gym_staff'@'localhost';
GRANT SELECT, INSERT ON ruiru_gym.payments TO 'gym_staff'@'localhost';
GRANT SELECT, INSERT, UPDATE ON ruiru_gym.attendance TO 'gym_staff'@'localhost';
GRANT SELECT ON ruiru_gym.classes TO 'gym_staff'@'localhost';
GRANT SELECT, INSERT, UPDATE ON ruiru_gym.class_bookings TO 'gym_staff'@'localhost';
GRANT SELECT, INSERT ON ruiru_gym.class_attendance TO 'gym_staff'@'localhost';
GRANT SELECT ON ruiru_gym.equipment TO 'gym_staff'@'localhost';
GRANT SELECT, INSERT ON ruiru_gym.notifications TO 'gym_staff'@'localhost';

-- Grant access to views
GRANT SELECT ON ruiru_gym.view_active_clients TO 'gym_staff'@'localhost';
GRANT SELECT ON ruiru_gym.view_expiring_memberships TO 'gym_staff'@'localhost';
GRANT SELECT ON ruiru_gym.view_class_schedule_today TO 'gym_staff'@'localhost';
GRANT SELECT ON ruiru_gym.view_attendance_today TO 'gym_staff'@'localhost';

-- Grant execution rights for specific procedures
GRANT EXECUTE ON PROCEDURE ruiru_gym.get_client_statistics TO 'gym_staff'@'localhost';
GRANT EXECUTE ON PROCEDURE ruiru_gym.search_clients TO 'gym_staff'@'localhost';

-- ============================================================================
-- 3. READ-ONLY USER - For reports and analytics
-- ============================================================================
CREATE USER 'gym_readonly'@'localhost' IDENTIFIED BY 'ReadOnly@Gym2024!';

-- Grant SELECT only on all tables
GRANT SELECT ON ruiru_gym.* TO 'gym_readonly'@'localhost';

-- Grant execution rights for reporting procedures
GRANT EXECUTE ON PROCEDURE ruiru_gym.get_revenue_report TO 'gym_readonly'@'localhost';
GRANT EXECUTE ON PROCEDURE ruiru_gym.get_class_attendance_report TO 'gym_readonly'@'localhost';
GRANT EXECUTE ON PROCEDURE ruiru_gym.get_dashboard_stats TO 'gym_readonly'@'localhost';

-- ============================================================================
-- 4. BACKUP USER - For automated backups
-- ============================================================================
CREATE USER 'gym_backup'@'localhost' IDENTIFIED BY 'Backup@Gym2024!';

-- Grant necessary privileges for backup
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON ruiru_gym.* TO 'gym_backup'@'localhost';

-- ============================================================================
-- APPLY ALL PRIVILEGE CHANGES
-- ============================================================================
FLUSH PRIVILEGES;

-- ============================================================================
-- ADDITIONAL SECURITY TABLE - Login Attempts Tracking
-- ============================================================================
CREATE TABLE IF NOT EXISTS login_attempts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    attempt_time DATETIME NOT NULL,
    success BOOLEAN DEFAULT FALSE,
    INDEX idx_username (username),
    INDEX idx_ip (ip_address),
    INDEX idx_time (attempt_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SECURITY AUDIT TABLE - Track sensitive operations
-- ============================================================================
CREATE TABLE IF NOT EXISTS security_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    action_type ENUM('login', 'logout', 'data_access', 'data_modify', 'data_delete', 'permission_change', 'failed_login') NOT NULL,
    table_name VARCHAR(50) NULL,
    record_id INT NULL,
    old_value TEXT NULL,
    new_value TEXT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_action (action_type),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SESSION MANAGEMENT TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS user_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    session_token VARCHAR(255) NOT NULL UNIQUE,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    last_activity DATETIME NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_token (session_token),
    INDEX idx_user (user_id),
    INDEX idx_active (is_active),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- DATA BACKUP CONFIGURATION TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS backup_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    backup_type ENUM('full', 'incremental', 'schema_only') NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_size_mb DECIMAL(10, 2) NULL,
    status ENUM('started', 'completed', 'failed') NOT NULL,
    error_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME NULL,
    INDEX idx_type (backup_type),
    INDEX idx_status (status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- STORED PROCEDURE: Clean expired sessions
-- ============================================================================
DELIMITER //

CREATE PROCEDURE clean_expired_sessions()
BEGIN
    DELETE FROM user_sessions 
    WHERE expires_at < NOW() OR last_activity < DATE_SUB(NOW(), INTERVAL 24 HOUR);
    
    SELECT CONCAT('Cleaned ', ROW_COUNT(), ' expired sessions') AS result;
END//

-- ============================================================================
-- STORED PROCEDURE: Log security event
-- ============================================================================
CREATE PROCEDURE log_security_event(
    IN p_user_id INT,
    IN p_action_type VARCHAR(20),
    IN p_table_name VARCHAR(50),
    IN p_record_id INT,
    IN p_ip_address VARCHAR(45)
)
BEGIN
    INSERT INTO security_audit (user_id, action_type, table_name, record_id, ip_address, created_at)
    VALUES (p_user_id, p_action_type, p_table_name, p_record_id, p_ip_address, NOW());
END//

-- ============================================================================
-- STORED PROCEDURE: Check login attempts and implement lockout
-- ============================================================================
CREATE PROCEDURE check_login_attempts(
    IN p_username VARCHAR(50),
    IN p_ip_address VARCHAR(45),
    OUT p_is_locked BOOLEAN
)
BEGIN
    DECLARE failed_attempts INT DEFAULT 0;
    
    -- Count failed attempts in last 15 minutes
    SELECT COUNT(*) INTO failed_attempts
    FROM login_attempts
    WHERE username = p_username
    AND ip_address = p_ip_address
    AND success = FALSE
    AND attempt_time > DATE_SUB(NOW(), INTERVAL 15 MINUTE);
    
    -- Lock account if more than 5 failed attempts
    IF failed_attempts >= 5 THEN
        SET p_is_locked = TRUE;
    ELSE
        SET p_is_locked = FALSE;
    END IF;
END//

-- ============================================================================
-- TRIGGER: Audit client data modifications
-- ============================================================================
CREATE TRIGGER audit_client_update
AFTER UPDATE ON clients
FOR EACH ROW
BEGIN
    IF OLD.email != NEW.email OR OLD.phone != NEW.phone OR OLD.status != NEW.status THEN
        INSERT INTO activity_log (user_id, action, entity_type, entity_id, description, created_at)
        VALUES (
            NULL,
            'UPDATE',
            'clients',
            NEW.id,
            CONCAT('Client updated: ', OLD.first_name, ' ', OLD.last_name),
            NOW()
        );
    END IF;
END//

-- ============================================================================
-- TRIGGER: Audit payment modifications
-- ============================================================================
CREATE TRIGGER audit_payment_insert
AFTER INSERT ON payments
FOR EACH ROW
BEGIN
    INSERT INTO activity_log (user_id, action, entity_type, entity_id, description, created_at)
    VALUES (
        NEW.processed_by,
        'INSERT',
        'payments',
        NEW.id,
        CONCAT('Payment recorded: KSh ', NEW.amount, ' - ', NEW.payment_method),
        NOW()
    );
END//

DELIMITER ;

-- ============================================================================
-- CREATE EVENT: Clean expired sessions daily
-- ============================================================================
DROP EVENT IF EXISTS daily_session_cleanup;

CREATE EVENT daily_session_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 2 HOUR
DO
  CALL clean_expired_sessions();

-- ============================================================================
-- CREATE EVENT: Clean old login attempts
-- ============================================================================
DROP EVENT IF EXISTS weekly_cleanup_login_attempts;

CREATE EVENT weekly_cleanup_login_attempts
ON SCHEDULE EVERY 1 WEEK
STARTS CURRENT_DATE + INTERVAL 1 DAY
DO
  DELETE FROM login_attempts WHERE attempt_time < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- ============================================================================
-- DATABASE CONFIGURATION SETTINGS
-- ============================================================================

-- Set default character set and collation
ALTER DATABASE ruiru_gym CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- ============================================================================
-- BACKUP SCRIPT TEMPLATE (Save as separate file: backup.sh)
-- ============================================================================
/*
#!/bin/bash
# Ruiru Gym Database Backup Script
# Usage: ./backup.sh

TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
BACKUP_DIR="/var/backups/ruiru_gym"
DB_NAME="ruiru_gym"
DB_USER="gym_backup"
DB_PASS="Backup@Gym2024!"

# Create backup directory if it doesn't exist
mkdir -p $BACKUP_DIR

# Full database backup
mysqldump -u $DB_USER -p$DB_PASS $DB_NAME > $BACKUP_DIR/ruiru_gym_full_$TIMESTAMP.sql

# Compress backup
gzip $BACKUP_DIR/ruiru_gym_full_$TIMESTAMP.sql

# Delete backups older than 30 days
find $BACKUP_DIR -name "*.sql.gz" -mtime +30 -delete

# Log backup to database
mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "INSERT INTO backup_log (backup_type, file_path, status, completed_at) VALUES ('full', '$BACKUP_DIR/ruiru_gym_full_$TIMESTAMP.sql.gz', 'completed', NOW());"

echo "Backup completed: ruiru_gym_full_$TIMESTAMP.sql.gz"
*/

-- ============================================================================
-- IMPORTANT SECURITY NOTES
-- ============================================================================
/*
1. CHANGE DEFAULT PASSWORDS immediately after setup
2. Use strong passwords with minimum 12 characters
3. Enable SSL/TLS for database connections in production
4. Regularly backup database using the backup user
5. Monitor security_audit table for suspicious activities
6. Keep MySQL server updated with latest security patches
7. Implement firewall rules to restrict database access
8. Use environment variables for credentials in application
9. Enable slow query log to identify performance issues
10. Regularly review user privileges and access

PRODUCTION CHECKLIST:
☐ Change all default passwords
☐ Configure automated backups
☐ Set up SSL certificates
☐ Configure firewall rules
☐ Enable query logging
☐ Set up monitoring alerts
☐ Document disaster recovery procedures
☐ Test backup restoration process
☐ Configure email notifications for critical events
☐ Review and audit user access regularly
*/

-- ============================================================================
-- USEFUL SECURITY QUERIES
-- ============================================================================

-- View current database users and privileges
-- SHOW GRANTS FOR 'gym_admin'@'localhost';
-- SHOW GRANTS FOR 'gym_staff'@'localhost';
-- SHOW GRANTS FOR 'gym_readonly'@'localhost';

-- Check recent security events
-- SELECT * FROM security_audit ORDER BY created_at DESC LIMIT 50;

-- Check active sessions
-- SELECT u.username, s.* FROM user_sessions s 
-- JOIN users u ON s.user_id = u.id 
-- WHERE s.is_active = TRUE;

-- Check failed login attempts
-- SELECT username, ip_address, COUNT(*) as attempts 
-- FROM login_attempts 
-- WHERE success = FALSE AND attempt_time > DATE_SUB(NOW(), INTERVAL 1 DAY)
-- GROUP BY username, ip_address 
-- ORDER BY attempts DESC;

-- View recent activity log
-- SELECT u.username, a.* FROM activity_log a
-- LEFT JOIN users u ON a.user_id = u.id
-- ORDER BY a.created_at DESC LIMIT 100;

-- Check backup status
-- SELECT * FROM backup_log ORDER BY created_at DESC LIMIT 10;

-- ============================================================================
-- End of Security Configuration
-- ============================================================================

-- Display setup completion message
SELECT '========================================' AS '';
SELECT 'DATABASE SETUP COMPLETED SUCCESSFULLY!' AS '';
SELECT '========================================' AS '';
SELECT '' AS '';
SELECT 'Database Users Created:' AS '';
SELECT '  - gym_admin (Full Access)' AS '';
SELECT '  - gym_staff (Operational Access)' AS '';
SELECT '  - gym_readonly (Reports Only)' AS '';
SELECT '  - gym_backup (Backup Access)' AS '';
SELECT '' AS '';
SELECT 'IMPORTANT: Change default passwords!' AS '';
SELECT '========================================' AS '';