

-- Computers table
CREATE TABLE IF NOT EXISTS computers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    computer_name VARCHAR(255) NOT NULL,
    first_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_seen TIMESTAMP NULL,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_computer (user_id, computer_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Activity logs table
CREATE TABLE IF NOT EXISTS activity_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    computer_id INT NOT NULL,
    timestamp DATETIME NOT NULL,
    window_title VARCHAR(512),
    process_name VARCHAR(255),
    duration_seconds INT,
    work_status ENUM('Active Work', 'Light Activity', 'Passive', 'Idle') DEFAULT 'Passive',
    work_intensity DECIMAL(3,2),
    key_presses INT DEFAULT 0,
    mouse_clicks INT DEFAULT 0,
    is_active_coding BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (computer_id) REFERENCES computers(id) ON DELETE CASCADE,
    INDEX idx_user_date (user_id, timestamp),
    INDEX idx_computer (computer_id),
    INDEX idx_status (work_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Daily summaries table (for quick reporting)
CREATE TABLE IF NOT EXISTS daily_summaries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    computer_id INT NOT NULL,
    log_date DATE NOT NULL,
    total_time_minutes INT DEFAULT 0,
    active_coding_minutes INT DEFAULT 0,
    idle_minutes INT DEFAULT 0,
    total_key_presses INT DEFAULT 0,
    total_mouse_clicks INT DEFAULT 0,
    avg_intensity DECIMAL(3,2),
    session_count INT DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_daily (user_id, computer_id, log_date),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (computer_id) REFERENCES computers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create indexes for better performance
CREATE INDEX idx_logs_comprehensive ON activity_logs(user_id, computer_id, timestamp);
CREATE INDEX idx_logs_work_status ON activity_logs(work_status, timestamp);

-- Stored procedure for updating daily summaries
DELIMITER //

CREATE PROCEDURE UpdateDailySummary(
    IN p_user_id INT,
    IN p_computer_id INT,
    IN p_log_date DATE
)
BEGIN
    DECLARE total_minutes INT;
    DECLARE active_minutes INT;
    DECLARE idle_minutes INT;
    DECLARE total_keys INT;
    DECLARE total_clicks INT;
    DECLARE avg_intensity DECIMAL(3,2);
    DECLARE session_count INT;
    
    -- Insert or update summary
    INSERT INTO daily_summaries 
        (user_id, computer_id, log_date, total_time_minutes, active_coding_minutes, 
         idle_minutes, total_key_presses, total_mouse_clicks, avg_intensity, session_count)
    VALUES 
        (p_user_id, p_computer_id, p_log_date, total_minutes, active_minutes,
         idle_minutes, total_keys, total_clicks, avg_intensity, session_count)
    ON DUPLICATE KEY UPDATE
        total_time_minutes = VALUES(total_time_minutes),
        active_coding_minutes = VALUES(active_coding_minutes),
        idle_minutes = VALUES(idle_minutes),
        total_key_presses = VALUES(total_key_presses),
        total_mouse_clicks = VALUES(total_mouse_clicks),
        avg_intensity = VALUES(avg_intensity),
        session_count = VALUES(session_count);
END//

DELIMITER ;

-- Event to run summary update daily (optional)
SET GLOBAL event_scheduler = ON;

CREATE EVENT IF NOT EXISTS daily_summary_event
ON SCHEDULE EVERY 1 DAY
STARTS CURDATE() + INTERVAL 1 DAY
DO
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_user_id INT;
    DECLARE v_computer_id INT;
    DECLARE v_log_date DATE;
    
    DECLARE cur CURSOR FOR 
        SELECT DISTINCT user_id, computer_id, DATE(timestamp)
        FROM activity_logs
        WHERE DATE(timestamp) = CURDATE() - INTERVAL 1 DAY;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_user_id, v_computer_id, v_log_date;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        CALL UpdateDailySummary(v_user_id, v_computer_id, v_log_date);
    END LOOP;
    
    CLOSE cur;
END//