-- Add Telegram user info to users table
ALTER TABLE users 
ADD COLUMN telegram_id VARCHAR(100) UNIQUE,
ADD COLUMN telegram_username VARCHAR(100),
ADD COLUMN notification_preference ENUM('email', 'telegram', 'both') DEFAULT 'both';

-- Add email import batch tracking
CREATE TABLE email_import_batches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    batch_name VARCHAR(100),
    filename VARCHAR(255),
    total_emails INT,
    imported_count INT,
    failed_count INT,
    imported_by INT,
    import_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    FOREIGN KEY (imported_by) REFERENCES users(id)
);

-- Add Telegram bot sessions
CREATE TABLE telegram_sessions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    telegram_id VARCHAR(100) UNIQUE NOT NULL,
    chat_id BIGINT,
    step VARCHAR(50),
    temp_data TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Add Telegram message logs
CREATE TABLE telegram_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    telegram_id VARCHAR(100),
    chat_id BIGINT,
    message_type VARCHAR(50),
    message TEXT,
    direction ENUM('incoming', 'outgoing'),
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add file uploads table
CREATE TABLE file_uploads (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    filename VARCHAR(255),
    original_name VARCHAR(255),
    file_path VARCHAR(500),
    file_size INT,
    file_type VARCHAR(50),
    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    FOREIGN KEY (user_id) REFERENCES users(id)
);