-- Email Parser Tables for Travel ERP
-- Run this SQL to create the required tables

CREATE TABLE IF NOT EXISTS email_parser_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agency_id INT NOT NULL,
    is_enabled TINYINT(1) DEFAULT 0,
    imap_host VARCHAR(255) NOT NULL,
    imap_port INT DEFAULT 993,
    imap_username VARCHAR(255) NOT NULL,
    imap_password VARCHAR(255),
    imap_encryption VARCHAR(10) DEFAULT 'ssl',
    imap_folder VARCHAR(50) DEFAULT 'INBOX',
    fetch_limit INT DEFAULT 50,
    fetch_interval INT DEFAULT 300,
    email_subject_pattern VARCHAR(500),
    email_sender_pattern VARCHAR(500),
    parsing_rules JSON,
    workflow_actions JSON,
    default_agent_id INT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_agency (agency_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS email_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agency_id INT NOT NULL,
    settings_id INT,
    message_id VARCHAR(255) UNIQUE,
    subject VARCHAR(500),
    sender VARCHAR(500),
    sender_email VARCHAR(255),
    text_content LONGTEXT,
    parsed_content LONGTEXT,
    status ENUM('pending', 'parsed', 'processed', 'failed', 'duplicate') DEFAULT 'pending',
    error_message TEXT,
    parsed_data JSON,
    client_id INT,
    booking_id INT,
    ticket_id INT,
    invoice_id INT,
    agent_id INT,
    processed_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_agency_status (agency_id, status),
    INDEX idx_message_id (message_id),
    INDEX idx_sender_email (sender_email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS email_parsing_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    settings_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    field VARCHAR(50) NOT NULL,
    pattern_type VARCHAR(20) DEFAULT 'regex',
    text_pattern TEXT NOT NULL,
    priority INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_settings (settings_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;