-- 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 dashboard users authentication
CREATE TABLE IF NOT EXISTS dashboard_users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    role VARCHAR(20) DEFAULT 'admin', -- admin, viewer
    is_active BOOLEAN DEFAULT 1,
    last_login DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Table for session management
CREATE TABLE IF NOT EXISTS user_sessions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id VARCHAR(128) UNIQUE NOT NULL,
    user_id INTEGER NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    expires_at DATETIME NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES dashboard_users(id) ON DELETE CASCADE
);

-- 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);
CREATE INDEX IF NOT EXISTS idx_sessions_session_id ON user_sessions(session_id);
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON user_sessions(expires_at);

-- Insert default admin user (password: admin123 - should be changed in production)
INSERT OR IGNORE INTO dashboard_users (username, password_hash, email, role) 
VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@localhost', 'admin');

-- 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;

CREATE TRIGGER IF NOT EXISTS update_users_timestamp 
    AFTER UPDATE ON dashboard_users
    FOR EACH ROW
    BEGIN
        UPDATE dashboard_users SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
    END;

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