-- Email Processing Dashboard Database Schema
-- MySQL Database for logging email processing activities

-- Table for storing email processing logs
CREATE TABLE IF NOT EXISTS email_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    sender_email VARCHAR(255) NOT NULL,
    recipient_email VARCHAR(255) NOT NULL,
    subject TEXT,
    processing_status VARCHAR(20) NOT NULL DEFAULT 'pending', -- success, failure, pending
    pipe1_transfer_status VARCHAR(20) NOT NULL DEFAULT 'pending', -- success, failure, pending
    error_message TEXT,
    file_size BIGINT,
    processing_time_ms INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table for system statistics (optional for caching)
CREATE TABLE IF NOT EXISTS system_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    stat_name VARCHAR(50) UNIQUE NOT NULL,
    stat_value INT NOT NULL,
    last_updated DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table for tracking login attempts (rate limiting)
CREATE TABLE IF NOT EXISTS login_attempts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    identifier VARCHAR(255) NOT NULL,
    success TINYINT(1) DEFAULT 0,
    attempt_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Indexes for better performance
CREATE INDEX idx_email_logs_timestamp ON email_logs(timestamp);
CREATE INDEX idx_email_logs_sender ON email_logs(sender_email);
CREATE INDEX idx_email_logs_recipient ON email_logs(recipient_email);
CREATE INDEX idx_email_logs_status ON email_logs(processing_status);
CREATE INDEX idx_email_logs_pipe1_status ON email_logs(pipe1_transfer_status);

-- Insert initial system stats
INSERT IGNORE INTO system_stats (stat_name, stat_value) VALUES 
('total_processed', 0),
('successful_transfers', 0),
('failed_transfers', 0);

-- Clean up expired sessions (can be run periodically)
-- DELETE FROM user_sessions WHERE expires_at < NOW();
