<?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 = 5;
    
    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) {
            $this->ensure_required_columns();
            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,
            cc_email text DEFAULT NULL,
            bcc_email text DEFAULT NULL,
            subject varchar(255) NOT NULL,
            message longtext NOT NULL,
            attachments longtext DEFAULT NULL,
            status varchar(40) NOT NULL DEFAULT 'sent',
            reply_to_email_id bigint(20) unsigned DEFAULT 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,
            updated_at datetime DEFAULT NULL,
            sent_at datetime DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY external_id (external_id),
            KEY user_id (user_id),
            KEY mailbox_id (mailbox_id),
            KEY reply_to_email_id (reply_to_email_id),
            KEY folder (folder),
            KEY type (type),
            KEY status (status),
            KEY provider (provider),
            KEY created_at (created_at)
        ) $charset_collate;";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
        $this->ensure_required_columns();
        update_option('vmo_email_storage_schema_version', $this->schema_version);
    }

    private function ensure_required_columns() {
        global $wpdb;

        if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->table_name)) !== $this->table_name) {
            return;
        }

        $columns = $wpdb->get_col("SHOW COLUMNS FROM {$this->table_name}", 0);
        $columns = is_array($columns) ? array_map('strval', $columns) : array();
        $missing = function($column) use ($columns) {
            return !in_array($column, $columns, true);
        };

        $alterations = array(
            'user_id' => "ADD COLUMN user_id bigint(20) unsigned NOT NULL DEFAULT 0 AFTER id",
            'mailbox_id' => "ADD COLUMN mailbox_id bigint(20) unsigned DEFAULT NULL AFTER user_id",
            'folder' => "ADD COLUMN folder varchar(20) NOT NULL DEFAULT 'inbox' AFTER mailbox_id",
            'type' => "ADD COLUMN type varchar(20) NOT NULL DEFAULT 'transactional' AFTER folder",
            'provider' => "ADD COLUMN provider varchar(30) NOT NULL DEFAULT 'local' AFTER type",
            'external_id' => "ADD COLUMN external_id varchar(191) DEFAULT NULL AFTER provider",
            'from_email' => "ADD COLUMN from_email varchar(255) NOT NULL DEFAULT '' AFTER external_id",
            'to_email' => "ADD COLUMN to_email varchar(255) NOT NULL DEFAULT '' AFTER from_email",
            'cc_email' => "ADD COLUMN cc_email text DEFAULT NULL AFTER to_email",
            'bcc_email' => "ADD COLUMN bcc_email text DEFAULT NULL AFTER cc_email",
            'subject' => "ADD COLUMN subject varchar(255) NOT NULL DEFAULT '' AFTER bcc_email",
            'message' => "ADD COLUMN message longtext NULL AFTER subject",
            'attachments' => "ADD COLUMN attachments longtext DEFAULT NULL AFTER message",
            'status' => "ADD COLUMN status varchar(40) NOT NULL DEFAULT 'sent' AFTER attachments",
            'reply_to_email_id' => "ADD COLUMN reply_to_email_id bigint(20) unsigned DEFAULT NULL AFTER status",
            'is_read' => "ADD COLUMN is_read boolean DEFAULT FALSE AFTER reply_to_email_id",
            'is_ai_generated' => "ADD COLUMN is_ai_generated boolean DEFAULT FALSE AFTER is_read",
            'ai_auto_reply_enabled' => "ADD COLUMN ai_auto_reply_enabled boolean DEFAULT FALSE AFTER is_ai_generated",
            'created_at' => "ADD COLUMN created_at datetime DEFAULT CURRENT_TIMESTAMP AFTER ai_auto_reply_enabled",
            'updated_at' => "ADD COLUMN updated_at datetime DEFAULT NULL AFTER created_at",
            'sent_at' => "ADD COLUMN sent_at datetime DEFAULT NULL AFTER updated_at",
        );

        foreach ($alterations as $column => $alter_sql) {
            if ($missing($column)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $wpdb->query("ALTER TABLE {$this->table_name} {$alter_sql}");
            }
        }
    }
    
    /**
     * 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',
            'status' => '',
            'is_read' => 1,
            'is_ai_generated' => 0,
            'ai_auto_reply_enabled' => 0,
            'created_at' => current_time('mysql'),
            'updated_at' => current_time('mysql'),
            'attachments' => null,
        ];
        
        $data = wp_parse_args($data, $defaults);
        $owner_id = intval($data['user_id'] ?? 0);
        if ($owner_id > 0 && function_exists('vmo_is_user_service_locked') && vmo_is_user_service_locked($owner_id)) {
            error_log('VMO Email Storage: Blocked email storage for service-locked user ' . $owner_id . ' folder=' . sanitize_key((string) ($data['folder'] ?? '')));
            return 0;
        }

        $data['folder'] = sanitize_key((string) $data['folder']);
        $data['type'] = sanitize_key((string) $data['type']);
        if (empty($data['status'])) {
            $data['status'] = ($data['folder'] === 'drafts') ? 'draft' : (($data['folder'] === 'sent') ? 'sent' : 'received');
        }
        $data['status'] = sanitize_key((string) $data['status']);
        if ($data['folder'] === 'sent' && empty($data['sent_at'])) {
            $data['sent_at'] = current_time('mysql');
        }
        if (isset($data['attachments']) && is_array($data['attachments'])) {
            $data['attachments'] = wp_json_encode($data['attachments']);
        }
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->insert($this->table_name, $data);
        $email_id = $result ? intval($wpdb->insert_id) : 0;

        // Auto-create CRM contact from email interactions
        if ( $result && function_exists( 'vmo_upsert_contact' ) ) {
            $to = $data['to_email'] ?? '';
            $from = $data['from_email'] ?? '';
            $uid = intval( $data['user_id'] ?? 0 );
            $contact_id = '';
            $direction = '';
            $contact_email = '';
            $subtype = '';
            $outcome = 'pending';
            if ( $uid && $data['folder'] === 'sent' && $to ) {
                $contact_id = vmo_upsert_contact( $uid, [
                    'email'  => $to,
                    'source' => 'email_marketing',
                    'primary_category' => 'prospect',
                    'lifecycle_stage'  => 'engaged',
                ] );
                $direction = 'outbound';
                $contact_email = $to;
                $subtype = !empty($data['is_ai_generated']) ? 'ai_sent_email' : 'sent_email';
                $outcome = 'successful';
            } elseif ( $uid && $data['folder'] === 'inbox' && $from ) {
                $contact_id = vmo_upsert_contact( $uid, [
                    'email'  => $from,
                    'source' => 'email_marketing',
                    'primary_category' => 'inbound_lead',
                    'lifecycle_stage'  => 'new',
                ] );
                $direction = 'inbound';
                $contact_email = $from;
                $subtype = 'inbound_email';
                $outcome = 'pending';
            } elseif ( $uid && $data['folder'] === 'drafts' && $to ) {
                $contact_id = vmo_upsert_contact( $uid, [
                    'email'  => $to,
                    'source' => 'email_marketing',
                    'primary_category' => 'prospect',
                    'lifecycle_stage'  => 'engaged',
                ] );
                $direction = 'outbound';
                $contact_email = $to;
                $subtype = !empty($data['is_ai_generated']) ? 'ai_draft_email' : 'draft_email';
                $outcome = 'pending';
            }

            if ( $contact_id && function_exists( 'vmo_log_interaction' ) ) {
                $summary = trim( wp_strip_all_tags( (string) ( $data['message'] ?? '' ) ) );
                $summary = function_exists('mb_substr') ? mb_substr($summary, 0, 1800) : substr($summary, 0, 1800);
                vmo_log_interaction( $uid, $contact_id, [
                    'interaction_type' => 'email',
                    'subtype' => $subtype,
                    'direction' => $direction,
                    'subject' => $data['subject'] ?? '',
                    'summary' => $summary,
                    'outcome' => $outcome,
                    'full_details' => [
                        'email_id' => $email_id,
                        'mailbox_id' => $data['mailbox_id'] ?? null,
                        'folder' => $data['folder'] ?? '',
                        'status' => $data['status'] ?? '',
                        'contact_email' => $contact_email,
                        'from_email' => $from,
                        'to_email' => $to,
                        'external_id' => $data['external_id'] ?? '',
                        'reply_to_email_id' => $data['reply_to_email_id'] ?? null,
                        'is_ai_generated' => !empty($data['is_ai_generated']) ? 1 : 0,
                    ],
                    'metadata' => [
                        'source' => 'vmo_email_storage',
                        'created_at' => $data['created_at'] ?? current_time('mysql'),
                    ],
                ] );
            }
        }

        return $result;
    }
    
    /**
     * 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]);
        }
    }
}
