-- Database Setup for Gym Authentication System
-- This script creates the database, tables, functions, and stored procedures.
-- It enforces the policy that the backend NEVER accesses tables directly.

CREATE DATABASE IF NOT EXISTS gym_auth_db;
USE gym_auth_db;

-- ==============================================================================
-- 1. TABLES
-- ==============================================================================

DROP TABLE IF EXISTS login_attempts;
DROP TABLE IF EXISTS user_sessions;
DROP TABLE IF EXISTS users;

-- Users Table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARBINARY(32) NOT NULL, -- SHA2-256 result is 32 bytes
    salt VARBINARY(16) NOT NULL,
    email VARCHAR(100) NOT NULL,
    full_name VARCHAR(100),
    membership_type ENUM('basic', 'premium', 'vip') DEFAULT 'basic',
    membership_expires_at DATETIME,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- User Sessions Table (for login tokens)
CREATE TABLE IF NOT EXISTS user_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    token VARCHAR(64) NOT NULL UNIQUE, -- Using a string representation of UUID or random bytes
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_seen_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    is_revoked BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Login Attempts Table (for rate limiting/auditing)
CREATE TABLE IF NOT EXISTS login_attempts (

    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    ip_address VARCHAR(45),
    success BOOLEAN DEFAULT FALSE,
    attempted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ==============================================================================
-- 2. FUNCTIONS
-- ==============================================================================

DELIMITER //

-- Function: Generate Random Bytes (Wrapper for internal RAND or specialized logic if needed)
-- Note: MySQL 8.0 has RANDOM_BYTES(). For compatibility we can just use it directly in procedures,
-- or wrap it. We'll wrap it for consistency.
DROP FUNCTION IF EXISTS fn_random_bytes //
CREATE FUNCTION fn_random_bytes(len INT) 
RETURNS VARBINARY(255)
DETERMINISTIC
BEGIN
    -- Polyfill for systems without RANDOM_BYTES (older MySQL/MariaDB)
    -- Generates bytes using MD5 of UUIDs (16 bytes per UUID)
    DECLARE res VARBINARY(255) DEFAULT '';
    DECLARE loop_count INT DEFAULT 0;
    -- Calculate how many 16-byte chunks we need (approx)
    SET loop_count = CEIL(len / 16);
    
    WHILE loop_count > 0 DO
        SET res = CONCAT(res, UNHEX(MD5( UUID() )));
        SET loop_count = loop_count - 1;
    END WHILE;
    
    RETURN SUBSTRING(res, 1, len);
END //

-- Function: Password Hash
-- Combines password and salt, then hashes with SHA2-256.
DROP FUNCTION IF EXISTS fn_pwd_hash //
CREATE FUNCTION fn_pwd_hash(salt VARBINARY(16), password VARCHAR(255)) 
RETURNS VARBINARY(32)
DETERMINISTIC
BEGIN
    RETURN UNHEX(SHA2(CONCAT(salt, password), 256));
END //

-- Function: Check User Password
-- Returns TRUE if password matches, FALSE otherwise.
DROP FUNCTION IF EXISTS fn_check_user_password //
CREATE FUNCTION fn_check_user_password(p_username VARCHAR(50), p_password VARCHAR(255)) 
RETURNS BOOLEAN
READS SQL DATA
BEGIN
    DECLARE v_salt VARBINARY(16);
    DECLARE v_hash VARBINARY(32);
    DECLARE v_stored_hash VARBINARY(32);
    
    SELECT salt, password_hash INTO v_salt, v_stored_hash
    FROM users 
    WHERE username = p_username AND is_active = 1
    LIMIT 1;
    
    IF v_salt IS NULL THEN
        RETURN FALSE;
    END IF;
    
    SET v_hash = fn_pwd_hash(v_salt, p_password);
    
    RETURN (v_hash = v_stored_hash);
END //

-- Function: Is Logged In
-- Checks if a token is valid and not expired.
DROP FUNCTION IF EXISTS fn_is_logged_in //
CREATE FUNCTION fn_is_logged_in(p_token VARCHAR(64)) 
RETURNS BOOLEAN
READS SQL DATA
BEGIN
    DECLARE v_exists INT;
    
    SELECT COUNT(*) INTO v_exists
    FROM user_sessions
    WHERE token = p_token 
      AND is_revoked = 0 
      AND expires_at > NOW();
      
    RETURN (v_exists > 0);
END //

-- Function: Get Username from Token
-- Helper to identify who is making the request
DROP FUNCTION IF EXISTS fn_get_username_from_token //
CREATE FUNCTION fn_get_username_from_token(p_token VARCHAR(64)) 
RETURNS VARCHAR(50)
READS SQL DATA
BEGIN
    DECLARE v_username VARCHAR(50);
    
    SELECT u.username INTO v_username
    FROM user_sessions s
    JOIN users u ON s.user_id = u.id
    WHERE s.token = p_token
    LIMIT 1;
    
    RETURN v_username;
END //

DELIMITER ;

-- ==============================================================================
-- 3. STORED PROCEDURES
-- ==============================================================================

DELIMITER //

-- Procedure: Create User
DROP PROCEDURE IF EXISTS sp_user_create //
CREATE PROCEDURE sp_user_create(
    IN p_username VARCHAR(50), 
    IN p_password VARCHAR(255), 
    IN p_email VARCHAR(100), 
    IN p_full_name VARCHAR(100),
    IN p_membership_days INT
)
BEGIN
    DECLARE v_salt VARBINARY(16);
    DECLARE v_hash VARBINARY(32);
    DECLARE v_exists INT;
    
    -- Check if username exists
    SELECT COUNT(*) INTO v_exists FROM users WHERE username = p_username;
    
    IF v_exists > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Username already exists';
    ELSE
        -- Generate Salt and Hash
        SET v_salt = fn_random_bytes(16);
        SET v_hash = fn_pwd_hash(v_salt, p_password);
        
        INSERT INTO users (username, password_hash, salt, email, full_name, membership_expires_at)
        VALUES (p_username, v_hash, v_salt, p_email, p_full_name, DATE_ADD(NOW(), INTERVAL p_membership_days DAY));
        
        -- Return the new ID
        SELECT LAST_INSERT_ID() as new_user_id;
    END IF;
END //

-- Procedure: Update User
DROP PROCEDURE IF EXISTS sp_user_update //
CREATE PROCEDURE sp_user_update(
    IN p_user_id INT,
    IN p_email VARCHAR(100),
    IN p_full_name VARCHAR(100)
)
BEGIN
    UPDATE users 
    SET email = p_email, 
        full_name = p_full_name 
    WHERE id = p_user_id;
END //

-- Procedure: Delete User
DROP PROCEDURE IF EXISTS sp_user_delete //
CREATE PROCEDURE sp_user_delete(IN p_user_id INT)
BEGIN
    DELETE FROM users WHERE id = p_user_id;
END //

-- Procedure: Login
-- Validates credentials, creates a session, returns the token.
DROP PROCEDURE IF EXISTS sp_login //
CREATE PROCEDURE sp_login(
    IN p_username VARCHAR(50), 
    IN p_password VARCHAR(255), 
    IN p_ip_address VARCHAR(45),
    IN p_timeout_minutes INT,
    OUT p_token VARCHAR(64)
)
BEGIN
    DECLARE v_user_id INT;
    DECLARE v_login_ok BOOLEAN;
    
    -- Check Credentials
    SET v_login_ok = fn_check_user_password(p_username, p_password);
    
    -- Log Attempt
    INSERT INTO login_attempts (username, ip_address, success) 
    VALUES (p_username, p_ip_address, v_login_ok);
    
    IF v_login_ok THEN
        -- Get User ID
        SELECT id INTO v_user_id FROM users WHERE username = p_username LIMIT 1;
        
        -- Generate Token (Using UUID v4 style or just hex string of random bytes)
        SET p_token = HEX(fn_random_bytes(32));
        
        -- Create Session
        INSERT INTO user_sessions (user_id, token, expires_at)
        VALUES (v_user_id, p_token, DATE_ADD(NOW(), INTERVAL p_timeout_minutes MINUTE));
        
        -- Return explicit success row if needed, but OUT param handles token
        SELECT 'Login Successful' as message, p_token as token;
    
    ELSE
        SET p_token = NULL;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid Username or Password';
    END IF;
END //

-- Procedure: Touch Session (Action)
-- Updates the last_seen_at and extends expires_at
DROP PROCEDURE IF EXISTS sp_touch_session //
CREATE PROCEDURE sp_touch_session(
    IN p_token VARCHAR(64),
    IN p_extend_minutes INT
)
BEGIN
    DECLARE v_count INT;
    
    UPDATE user_sessions 
    SET last_seen_at = NOW(),
        expires_at = DATE_ADD(NOW(), INTERVAL p_extend_minutes MINUTE)
    WHERE token = p_token 
      AND is_revoked = 0 
      AND expires_at > NOW();
      
    SELECT ROW_COUNT() INTO v_count;
    
    IF v_count = 0 THEN
         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Session Invalid or Expired';
    ELSE
         SELECT 'Session Updated' as message;
    END IF;
END //

-- Procedure: Logout
-- Revokes the session.
DROP PROCEDURE IF EXISTS sp_logout //
CREATE PROCEDURE sp_logout(IN p_token VARCHAR(64))
BEGIN
    UPDATE user_sessions 
    SET is_revoked = 1 
    WHERE token = p_token;
    
    SELECT 'Logged Out' as message;
END //

-- Procedure: Cleanup Sessions
-- Deletes old revoked or expired sessions to keep table small.
DROP PROCEDURE IF EXISTS sp_cleanup_sessions //
CREATE PROCEDURE sp_cleanup_sessions()
BEGIN
    DELETE FROM user_sessions 
    WHERE expires_at < DATE_SUB(NOW(), INTERVAL 7 DAY) -- Keep expired sessions for 7 days for audit, then delete
       OR (is_revoked = 1 AND created_at < DATE_SUB(NOW(), INTERVAL 7 DAY));
       
    SELECT ROW_COUNT() as deleted_sessions;
END //

DELIMITER ;

-- ==============================================================================
-- 4. EVENTS
-- ==============================================================================

SET GLOBAL event_scheduler = ON;

DROP EVENT IF EXISTS evt_cleanup_sessions;
CREATE EVENT evt_cleanup_sessions
ON SCHEDULE EVERY 1 HOUR
DO
  CALL sp_cleanup_sessions();