CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','operator') NOT NULL DEFAULT 'operator',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    failed_login_attempts INT UNSIGNED NOT NULL DEFAULT 0,
    locked_until DATETIME NULL,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_users_email (email),
    INDEX idx_users_active (is_active),
    INDEX idx_users_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_accounts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NULL,
    name VARCHAR(150) NOT NULL,
    email_address VARCHAR(190) NOT NULL,
    protocol ENUM('imap','pop3') NOT NULL DEFAULT 'imap',
    host VARCHAR(190) NOT NULL,
    port INT UNSIGNED NOT NULL,
    security_type ENUM('none','ssl','tls') NOT NULL DEFAULT 'ssl',
    username VARCHAR(190) NOT NULL,
    encrypted_password TEXT NULL,
    auth_type ENUM('password','app_password','oauth') NOT NULL DEFAULT 'password',
    oauth_provider ENUM('google','microsoft','custom') NULL,
    oauth_client_id VARCHAR(255) NULL,
    encrypted_oauth_client_secret TEXT NULL,
    oauth_auth_url VARCHAR(500) NULL,
    oauth_token_url VARCHAR(500) NULL,
    oauth_scopes TEXT NULL,
    oauth_redirect_uri VARCHAR(500) NULL,
    encrypted_oauth_access_token MEDIUMTEXT NULL,
    encrypted_oauth_refresh_token MEDIUMTEXT NULL,
    oauth_token_expires_at DATETIME NULL,
    oauth_connected_at DATETIME NULL,
    folder VARCHAR(190) NOT NULL DEFAULT 'INBOX',
    polling_interval INT UNSIGNED NOT NULL DEFAULT 5,
    import_only_unseen TINYINT(1) NOT NULL DEFAULT 1,
    mark_as_seen TINYINT(1) NOT NULL DEFAULT 1,
    move_after_import TINYINT(1) NOT NULL DEFAULT 0,
    archive_folder VARCHAR(190) NULL,
    leave_copy_on_server TINYINT(1) NOT NULL DEFAULT 1,
    delete_after_import TINYINT(1) NOT NULL DEFAULT 0,
    fallback_department VARCHAR(190) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_check_at DATETIME NULL,
    last_status ENUM('never','ok','error','paused') NOT NULL DEFAULT 'never',
    last_error TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_active (is_active),
    INDEX idx_email_address (email_address),
    INDEX idx_auth_type (auth_type),
    INDEX idx_oauth_connected (oauth_connected_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS oauth_states (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    state CHAR(64) NOT NULL,
    account_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    expires_at DATETIME NOT NULL,
    consumed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_oauth_state (state),
    CONSTRAINT fk_oauth_states_account FOREIGN KEY (account_id) REFERENCES email_accounts(id) ON DELETE CASCADE,
    CONSTRAINT fk_oauth_states_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_oauth_states_expiry (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS emails (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id INT UNSIGNED NOT NULL,
    message_id VARCHAR(255) NULL,
    provider_uid VARCHAR(100) NULL,
    from_email VARCHAR(190) NULL,
    from_name VARCHAR(190) NULL,
    to_addresses JSON NULL,
    cc_addresses JSON NULL,
    subject VARCHAR(500) NULL,
    body_text MEDIUMTEXT NULL,
    body_html MEDIUMTEXT NULL,
    raw_headers MEDIUMTEXT NULL,
    received_at DATETIME NULL,
    imported_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status ENUM('received','imported','normalized','pending_analysis','duplicate','error','quarantine','discarded') NOT NULL DEFAULT 'imported',
    language VARCHAR(20) NULL,
    thread_key VARCHAR(255) NULL,
    dedup_hash CHAR(64) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_emails_account FOREIGN KEY (account_id) REFERENCES email_accounts(id) ON DELETE CASCADE,
    UNIQUE KEY uq_account_dedup (account_id, dedup_hash),
    INDEX idx_account_status (account_id, status),
    INDEX idx_received_at (received_at),
    INDEX idx_message_id (message_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_attachments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email_id BIGINT UNSIGNED NOT NULL,
    original_filename VARCHAR(500) NOT NULL,
    mime_type VARCHAR(190) NULL,
    file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
    file_hash CHAR(64) NOT NULL,
    storage_path VARCHAR(800) NOT NULL,
    status ENUM('saved','too_large','blocked','quarantine','error') NOT NULL DEFAULT 'saved',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_attachments_email FOREIGN KEY (email_id) REFERENCES emails(id) ON DELETE CASCADE,
    INDEX idx_email_id (email_id),
    INDEX idx_file_hash (file_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_connector_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id INT UNSIGNED NULL,
    email_id BIGINT UNSIGNED NULL,
    event_type VARCHAR(100) NOT NULL,
    message TEXT NOT NULL,
    severity ENUM('debug','info','warning','error') NOT NULL DEFAULT 'info',
    context JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_logs_account FOREIGN KEY (account_id) REFERENCES email_accounts(id) ON DELETE SET NULL,
    CONSTRAINT fk_logs_email FOREIGN KEY (email_id) REFERENCES emails(id) ON DELETE SET NULL,
    INDEX idx_account_created (account_id, created_at),
    INDEX idx_severity (severity),
    INDEX idx_event_type (event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
