<?php
if ( ! defined( 'ABSPATH' ) ) exit;

/**
 * VMO Email Storage Handler
 * 
 * Handles database operations for the internal Webmail system.
 * Stores sent and received emails for the Dashboard UI.
 * 
 * @package VMO_AI_Builder_Manager
 */

if (!defined('ABSPATH')) {
    exit;
}

class VMO_Email_Storage {
    
    private $table_name;
    private $schema_version = 3;
    
    public function __construct() {
        global $wpdb;
        $this->table_name = $wpdb->prefix . 'vmo_emails';
        $this->check_table();
    }
    
    /**
     * Ensure the email table exists
     */
    private function check_table() {
        global $wpdb;

        $stored_version = intval(get_option('vmo_email_storage_schema_version', 0));
        $table_exists = ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->table_name)) === $this->table_name);
        if ($table_exists && $stored_version >= $this->schema_version) {
            return;
        }

        $charset_collate = $wpdb->get_charset_collate();
        $sql = "CREATE TABLE $this->table_name (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            user_id bigint(20) unsigned NOT NULL DEFAULT 0,
            mailbox_id bigint(20) unsigned DEFAULT NULL,
            folder varchar(20) NOT NULL DEFAULT 'inbox',
            type varchar(20) NOT NULL DEFAULT 'transactional',
            provider varchar(30) NOT NULL DEFAULT 'local',
            external_id varchar(191) DEFAULT NULL,
            from_email varchar(255) NOT NULL,
            to_email varchar(255) NOT NULL,
            subject varchar(255) NOT NULL,
            message longtext NOT NULL,
            is_read boolean DEFAULT FALSE,
            is_ai_generated boolean DEFAULT FALSE,
            ai_auto_reply_enabled boolean DEFAULT FALSE,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY external_id (external_id),
            KEY user_id (user_id),
            KEY mailbox_id (mailbox_id),
            KEY folder (folder),
            KEY type (type),
            KEY provider (provider),
            KEY created_at (created_at)
        ) $charset_collate;";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
        update_option('vmo_email_storage_schema_version', $this->schema_version);
    }
    
    /**
     * Save an email to the database
     */
    public function save_email($data) {
        global $wpdb;
        
        $defaults = [
            'user_id' => is_user_logged_in() ? get_current_user_id() : 0,
            'mailbox_id' => null,
            'folder' => 'sent',
            'type' => 'transactional',
            'is_read' => 1,
            'is_ai_generated' => 0,
            'ai_auto_reply_enabled' => 0,
            'created_at' => current_time('mysql')
        ];
        
        $data = wp_parse_args($data, $defaults);
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->insert($this->table_name, $data);
    }
    
    /**
     * Get emails for a specific folder and type
     */
    public function get_emails($folder = 'inbox', $type = 'transactional', $limit = 50, $offset = 0, $user_id = null, $mailbox_id = null) {
        global $wpdb;
        
        // If folder is 'all', ignore folder check
        $folder_sql = $folder === 'all' ? "" : $wpdb->prepare("AND folder = %s", $folder);
        
        // If type is 'all', ignore type check
        $type_sql = $type === 'all' ? "" : $wpdb->prepare("AND type = %s", $type);

        $user_sql = '';
        if ($user_id !== null) {
            $user_sql = $wpdb->prepare('AND user_id = %d', intval($user_id));
        }

        $mailbox_sql = '';
        if ($mailbox_id !== null) {
            $mailbox_sql = $wpdb->prepare('AND mailbox_id = %d', intval($mailbox_id));
        }
        
        $sql = "SELECT * FROM $this->table_name WHERE 1=1 $user_sql $mailbox_sql $folder_sql $type_sql ORDER BY created_at DESC LIMIT %d OFFSET %d";
        
        return $wpdb->get_results($wpdb->prepare($sql, $limit, $offset));
    }
    
    /**
     * Get unread count
     */
    public function get_unread_count($folder = 'inbox', $user_id = null, $mailbox_id = null) {
        global $wpdb;
        $user_sql = '';
        if ($user_id !== null) {
            $user_sql = $wpdb->prepare('AND user_id = %d', intval($user_id));
        }
        $mailbox_sql = '';
        if ($mailbox_id !== null) {
            $mailbox_sql = $wpdb->prepare('AND mailbox_id = %d', intval($mailbox_id));
        }
        return $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM $this->table_name WHERE folder = %s AND is_read = 0 $user_sql $mailbox_sql", $folder));
    }
    
    /**
     * Mark email as read
     */
    public function mark_read($id) {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->update($this->table_name, ['is_read' => 1], ['id' => $id]);
    }

    /**
     * Delete email (move to trash or delete permanently)
     */
    public function delete_email($id) {
        global $wpdb;
        // First move to trash if not already there
        $current_folder = $wpdb->get_var($wpdb->prepare("SELECT folder FROM $this->table_name WHERE id = %d", $id));
        
        if ($current_folder !== 'trash') {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            return $wpdb->update($this->table_name, ['folder' => 'trash'], ['id' => $id]);
        } else {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            return $wpdb->delete($this->table_name, ['id' => $id]);
        }
    }
}
