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

-- Table for storing email processing logs
CREATE TABLE IF NOT EXISTS email_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    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 INTEGER,
    processing_time_ms INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Table for system statistics (optional for caching)
CREATE TABLE IF NOT EXISTS system_stats (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    stat_name VARCHAR(50) UNIQUE NOT NULL,
    stat_value INTEGER NOT NULL,
    last_updated DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Table for tracking login attempts (rate limiting)
CREATE TABLE IF NOT EXISTS login_attempts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    identifier VARCHAR(255) NOT NULL,
    success BOOLEAN DEFAULT 0,
    attempt_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45)
);

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

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

-- Trigger to update timestamps
CREATE TRIGGER IF NOT EXISTS update_email_logs_timestamp 
    AFTER UPDATE ON email_logs
    FOR EACH ROW
    BEGIN
        UPDATE email_logs SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
    END;
    END;

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