-- ===============================================
-- MIGRATION: Add activity logging tables
-- ===============================================

USE halohost_ndps;

-- ===============================================
-- TABLE: login_activities
-- Tracks all login attempts and sessions
-- ===============================================

CREATE TABLE IF NOT EXISTS login_activities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_type VARCHAR(20) NOT NULL COMMENT 'admin or form_user',
    username VARCHAR(100),
    ip_address VARCHAR(45),
    user_agent TEXT,
    login_status VARCHAR(20) DEFAULT 'success' COMMENT 'success, failed, logout',
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    logout_time TIMESTAMP NULL,
    session_duration INT COMMENT 'Duration in seconds',
    INDEX idx_user_type (user_type),
    INDEX idx_username (username),
    INDEX idx_login_time (login_time),
    INDEX idx_login_status (login_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===============================================
-- TABLE: activity_logs
-- Tracks all system activities
-- ===============================================

CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_type VARCHAR(20) COMMENT 'admin, form_user, telegram',
    user_id VARCHAR(100) COMMENT 'username, chat_id, or user identifier',
    activity_type VARCHAR(50) NOT NULL COMMENT 'login, logout, form_submit, report_view, report_update, report_delete, etc.',
    activity_description TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    related_id INT COMMENT 'Related report_id or other entity ID',
    metadata TEXT COMMENT 'JSON data for additional info',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_type (user_type),
    INDEX idx_user_id (user_id),
    INDEX idx_activity_type (activity_type),
    INDEX idx_created_at (created_at),
    INDEX idx_related_id (related_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===============================================
-- Add indexes for better performance
-- ===============================================

-- Additional indexes for activity logs
ALTER TABLE activity_logs ADD INDEX IF NOT EXISTS idx_user_activity (user_type, activity_type);
ALTER TABLE activity_logs ADD INDEX IF NOT EXISTS idx_date_activity (DATE(created_at), activity_type);