<?php

if ( ! defined( 'ABSPATH' ) ) {
	exit; // Exit if accessed directly.
}

/**
 * Dental Database Tables
 * Handles all dental-related database table creation and management
 */

function create_dental_database_tables() {
    global $wpdb;
    $charset_collate = $wpdb->get_charset_collate();
    
    // Main dental orders table with new name
    $dental_table = $wpdb->prefix . 'dentals';
    $dental_sql = "CREATE TABLE IF NOT EXISTS $dental_table (
        id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        dental_id VARCHAR(50) UNIQUE NOT NULL,
        secret_key VARCHAR(255) NOT NULL,
        dental_status VARCHAR(50) NOT NULL DEFAULT 'pending',
        patient_name VARCHAR(255) NOT NULL,
        author_id BIGINT UNSIGNED NOT NULL,
        author_info JSON NOT NULL,
        attachment_ids TEXT NOT NULL DEFAULT '', -- Comma-separated attachment IDs
        links JSON NOT NULL DEFAULT '[]',
        service_type VARCHAR(50) NOT NULL,
        sub_services JSON NULL,
        extras JSON NULL,
        order_note TEXT,
        invoice_id JSON NOT NULL,
        assignee JSON NOT NULL DEFAULT '[]',
        priority VARCHAR(20) NOT NULL DEFAULT 'normal',
        start_date DATETIME,
        due_date DATETIME,
        dental_parent BIGINT UNSIGNED NULL,
        aligners JSON,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        
        -- Performance indexes
        INDEX idx_dental_status (dental_status),
        INDEX idx_author (author_id),
        INDEX idx_patient (patient_name),
        INDEX idx_dental_id_lookup (dental_id),
        INDEX idx_service_type (service_type)
    ) $charset_collate;";
    
    // Dental attachments table
    $dental_attachments_table = $wpdb->prefix . 'dental_attachments';
    $dental_attachments_sql = "CREATE TABLE IF NOT EXISTS $dental_attachments_table (
        id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        original_name VARCHAR(255) NOT NULL,
        stored_name VARCHAR(255) NOT NULL,
        file_size BIGINT UNSIGNED NOT NULL,
        mime_type VARCHAR(100) NOT NULL,
        file_extension VARCHAR(20) NOT NULL,
        file_path VARCHAR(500) NOT NULL,
        file_url VARCHAR(1000) NOT NULL,
        uploaded_by BIGINT UNSIGNED NOT NULL,
        is_temp TINYINT(1) NOT NULL DEFAULT 1,
        dental_id BIGINT UNSIGNED NULL,
        file_hash VARCHAR(64) NOT NULL,
        file_status ENUM('active', 'deleted') DEFAULT 'active',
        deleted_at TIMESTAMP NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        
        -- Performance indexes
        INDEX idx_dental_id (dental_id),
        INDEX idx_uploaded_by (uploaded_by),
        INDEX idx_is_temp (is_temp),
        INDEX idx_created_at (created_at),
        INDEX idx_temp_cleanup (is_temp, created_at),
        INDEX idx_file_hash (file_hash),
        INDEX idx_file_status (file_status),
        INDEX idx_mime_type (mime_type),
        INDEX idx_file_extension (file_extension),
        INDEX idx_deleted_at (deleted_at),
        
        -- Foreign key constraint
        FOREIGN KEY (dental_id) REFERENCES $dental_table(id) ON DELETE SET NULL
    ) $charset_collate;";
    
    // Dental meta table (bulletproof like WordPress meta)
    $dental_meta_table = $wpdb->prefix . 'dental_meta';
    $dental_meta_sql = "CREATE TABLE IF NOT EXISTS $dental_meta_table (
        meta_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        dental_id BIGINT UNSIGNED NOT NULL,
        meta_key VARCHAR(255) NOT NULL,
        meta_value LONGTEXT,
        
        -- Performance indexes for meta queries
        INDEX idx_dental_meta_lookup (dental_id, meta_key),
        INDEX idx_meta_key_value (meta_key, meta_value(100)),
        
        -- Foreign key constraint for data integrity
        FOREIGN KEY (dental_id) REFERENCES $dental_table(id) ON DELETE CASCADE
    ) $charset_collate;";
    
    // Dental chat table
    $dental_chat_table = $wpdb->prefix . 'dental_chats';
    $dental_chat_sql = "CREATE TABLE IF NOT EXISTS $dental_chat_table (
        id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        dental_id BIGINT UNSIGNED NOT NULL,
        
        -- Message Content
        message_type ENUM('text', 'file', 'image', 'voice', 'mixed', 'system') NOT NULL DEFAULT 'text',
        message_content TEXT,
        attachments JSON,
        reply_to_message_id BIGINT UNSIGNED DEFAULT NULL,
        reactions JSON,
        
        -- Sender & Threading
        sender_id BIGINT UNSIGNED NOT NULL,
        sender VARCHAR(50) NOT NULL,
        
        -- Message Purpose & Status
        message_purpose VARCHAR(50) DEFAULT NULL,
        message_status ENUM('sending', 'failed', 'sent', 'read') DEFAULT 'sending',
        
        -- Timestamps
        sent_at TIMESTAMP NULL,
        edited_at TIMESTAMP NULL,
        read_at TIMESTAMP NULL,
        deleted_at TIMESTAMP NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        
        -- Performance Indexes
        INDEX idx_dental_id (dental_id),
        FULLTEXT INDEX idx_message_search (message_content),
        
        -- Foreign Keys
        FOREIGN KEY (dental_id) REFERENCES $dental_table(id) ON DELETE CASCADE,
        FOREIGN KEY (reply_to_message_id) REFERENCES $dental_chat_table(id) ON DELETE SET NULL
    ) $charset_collate;";
    
    // Dental logs table
    $dental_logs_table = $wpdb->prefix . 'dental_logs';
    $dental_logs_sql = "CREATE TABLE IF NOT EXISTS $dental_logs_table (
        id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        dental_id BIGINT UNSIGNED NOT NULL,
        log_format ENUM('user_visible', 'documentation') NOT NULL DEFAULT 'user_visible',
        log_type VARCHAR(50) NOT NULL,
        action_data JSON NOT NULL,
        user_id BIGINT UNSIGNED NOT NULL,
        user_agent TEXT,
        
        -- Performance indexes
        INDEX idx_dental_id (dental_id),
        INDEX idx_user_id (user_id),
        INDEX idx_log_type (log_type),
        INDEX idx_log_format (log_format),
        INDEX idx_created_at (created_at),
        
        -- Foreign key constraint
        FOREIGN KEY (dental_id) REFERENCES $dental_table(id) ON DELETE CASCADE
    ) $charset_collate;";
    
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    
    // Create tables
    dbDelta($dental_sql);
    dbDelta($dental_attachments_sql);
    dbDelta($dental_meta_sql);
    dbDelta($dental_chat_sql);
    dbDelta($dental_logs_sql);
    
    // Add version tracking
    update_option('etiuse_dental_db_version', '1.6');
    
    // Check if we need to migrate from old structure
    $current_version = get_option('etiuse_dental_db_version', '1.0');
    if (version_compare($current_version, '1.5', '<')) {
        migrate_dental_table_structure();
    }
    
    // Check if we need to migrate logs table structure
    if (version_compare($current_version, '1.6', '<')) {
        migrate_dental_logs_table_structure();
    }
}
// Run on theme activation
add_action('after_switch_theme', 'create_dental_database_tables');

// Temporary: Trigger migration manually (remove this after migration is complete)
// add_action('init', 'migrate_dental_table_structure');

/**
 * Migrate dental table structure from old to new format
 */
function migrate_dental_table_structure() {
    global $wpdb;
    
    $table_name = $wpdb->prefix . 'dentals';
    
    // Check if attachments column exists (old structure)
    $attachments_column_exists = $wpdb->get_results("SHOW COLUMNS FROM $table_name LIKE 'attachments'");
    
    if (!empty($attachments_column_exists)) {
        // Add new attachment_ids column if it doesn't exist
        $wpdb->query("ALTER TABLE $table_name ADD COLUMN IF NOT EXISTS attachment_ids TEXT NOT NULL DEFAULT ''");
        
        // Migrate existing attachments data to attachment_ids
        $orders = $wpdb->get_results("SELECT id, attachments FROM $table_name WHERE attachments IS NOT NULL AND attachments != '[]' AND attachments != ''");
        
        foreach ($orders as $order) {
            $attachments = json_decode($order->attachments, true);
            if (is_array($attachments) && !empty($attachments)) {
                // Extract attachment IDs if they exist
                $attachment_ids = [];
                foreach ($attachments as $attachment) {
                    if (isset($attachment['attachment_id'])) {
                        $attachment_ids[] = $attachment['attachment_id'];
                    }
                }
                
                // Update with comma-separated attachment IDs
                $wpdb->update(
                    $table_name,
                    ['attachment_ids' => implode(',', $attachment_ids)],
                    ['id' => $order->id]
                );
            }
        }
        
        // Drop the old attachments column
        $wpdb->query("ALTER TABLE $table_name DROP COLUMN attachments");
    }
    
    // Check if services column exists (old structure)
    $services_column_exists = $wpdb->get_results("SHOW COLUMNS FROM $table_name LIKE 'services'");
    
    if (!empty($services_column_exists)) {
        // Add new columns if they don't exist
        $wpdb->query("ALTER TABLE $table_name ADD COLUMN IF NOT EXISTS service_type VARCHAR(50) NOT NULL DEFAULT ''");
        $wpdb->query("ALTER TABLE $table_name ADD COLUMN IF NOT EXISTS sub_services JSON NULL");
        $wpdb->query("ALTER TABLE $table_name ADD COLUMN IF NOT EXISTS extras JSON NULL");
        
        // Migrate existing data
        $orders = $wpdb->get_results("SELECT id, services FROM $table_name WHERE services IS NOT NULL AND services != '[]'");
        
        foreach ($orders as $order) {
            $services_data = json_decode($order->services, true);
            if ($services_data && is_array($services_data)) {
                $update_data = [];
                
                if (!empty($services_data['service_type'])) {
                    $update_data['service_type'] = $services_data['service_type'];
                }
                
                if (!empty($services_data['sub_services'])) {
                    $update_data['sub_services'] = json_encode($services_data['sub_services']);
                }
                
                if (!empty($services_data['extras'])) {
                    $update_data['extras'] = json_encode($services_data['extras']);
                }
                
                if (!empty($update_data)) {
                    $wpdb->update($table_name, $update_data, ['id' => $order->id]);
                }
            }
        }
        
        // Drop the old services column
        $wpdb->query("ALTER TABLE $table_name DROP COLUMN services");
        
        // Add index for service_type
        $wpdb->query("ALTER TABLE $table_name ADD INDEX idx_service_type (service_type)");
    }
}

/**
 * Migrate dental logs table structure
 */
function migrate_dental_logs_table_structure() {
    global $wpdb;
    
    $table_name = $wpdb->prefix . 'dental_logs';
    
    // Check if action column exists (old structure)
    $action_column_exists = $wpdb->get_results("SHOW COLUMNS FROM $table_name LIKE 'action'");
    
    if (!empty($action_column_exists)) {
        // Migrate action text to action_data if action_data doesn't exist or is empty
        $logs_with_action = $wpdb->get_results("SELECT id, action, action_data FROM $table_name WHERE action IS NOT NULL AND action != ''");
        
        foreach ($logs_with_action as $log) {
            $action_data = [];
            
            // If action_data exists, decode it, otherwise create new structure
            if ($log->action_data) {
                $action_data = json_decode($log->action_data, true);
                if (!is_array($action_data)) {
                    $action_data = [];
                }
            }
            
            // Add action text to action_data
            $action_data['action'] = $log->action;
            
            // Update the row
            $wpdb->update(
                $table_name,
                ['action_data' => json_encode($action_data, JSON_UNESCAPED_UNICODE)],
                ['id' => $log->id]
            );
        }
        
        // Drop the old action index if it exists
        $indexes = $wpdb->get_results("SHOW INDEXES FROM $table_name WHERE Key_name = 'idx_action'");
        if (!empty($indexes)) {
            $wpdb->query("ALTER TABLE $table_name DROP INDEX idx_action");
        }
        
        // Drop the old action column
        $wpdb->query("ALTER TABLE $table_name DROP COLUMN action");
    }
    
    // Check if action_data column exists, add if not
    $action_data_column_exists = $wpdb->get_results("SHOW COLUMNS FROM $table_name LIKE 'action_data'");
    
    if (empty($action_data_column_exists)) {
        // Add action_data column
        $wpdb->query("ALTER TABLE $table_name ADD COLUMN action_data JSON NOT NULL AFTER log_type");
    } else {
        // Make sure action_data is NOT NULL
        $action_data_column = $wpdb->get_row("SHOW COLUMNS FROM $table_name WHERE Field = 'action_data'");
        if ($action_data_column && strpos($action_data_column->Null, 'YES') !== false) {
            $wpdb->query("ALTER TABLE $table_name MODIFY COLUMN action_data JSON NOT NULL");
        }
    }
    
    // Check if log_format is ENUM, if not, we need to update it
    $log_format_column = $wpdb->get_row("SHOW COLUMNS FROM $table_name WHERE Field = 'log_format'");
    
    if ($log_format_column && strpos($log_format_column->Type, 'enum') === false) {
        // Update log_format to ENUM if it's not already
        $wpdb->query("ALTER TABLE $table_name MODIFY COLUMN log_format ENUM('user_visible', 'documentation') NOT NULL DEFAULT 'user_visible'");
    }
    
    // Add index for log_format if it doesn't exist
    $indexes = $wpdb->get_results("SHOW INDEXES FROM $table_name WHERE Key_name = 'idx_log_format'");
    if (empty($indexes)) {
        $wpdb->query("ALTER TABLE $table_name ADD INDEX idx_log_format (log_format)");
    }
}

/**
 * Dental Meta Functions
 * Core functions for managing dental meta data (following WordPress patterns)
 */

/**
 * Add or update dental meta data
 * 
 * @param int $dental_id Dental order ID
 * @param string $meta_key Meta key
 * @param mixed $meta_value Meta value
 * @param mixed $prev_value Previous value (optional)
 * @return int|bool Meta ID if the key didn't exist, true on successful update, false on failure
 */
function update_dental_meta($dental_id, $meta_key, $meta_value, $prev_value = '') {
    global $wpdb;
    
    if (!$dental_id || !$meta_key) {
        return false;
    }
    
    $dental_id = (int) $dental_id;
    $meta_key = wp_unslash($meta_key);
    $meta_value = wp_unslash($meta_value);
    $meta_value = maybe_serialize($meta_value);
    
    // Check if meta key exists
    $meta_id = $wpdb->get_var($wpdb->prepare(
        "SELECT meta_id FROM {$wpdb->prefix}dental_meta WHERE dental_id = %d AND meta_key = %s",
        $dental_id, $meta_key
    ));
    
    // If meta doesn't exist, add it
    if (!$meta_id) {
        $result = $wpdb->insert(
            $wpdb->prefix . 'dental_meta',
            array(
                'dental_id' => $dental_id,
                'meta_key' => $meta_key,
                'meta_value' => $meta_value,
            ),
            array('%d', '%s', '%s')
        );
        
        if (!$result) {
            return false;
        }
        
        $mid = (int) $wpdb->insert_id;
        
        // Clear cache
        wp_cache_delete($dental_id, 'dental_meta');
        
        return $mid;
    }
    
    // Check if value is different (if prev_value provided)
    if ($prev_value !== '') {
        $old_value = $wpdb->get_var($wpdb->prepare(
            "SELECT meta_value FROM {$wpdb->prefix}dental_meta WHERE meta_id = %d",
            $meta_id
        ));
        if ($old_value === maybe_serialize($prev_value)) {
            return false;
        }
    }
    
    // Update existing meta
    $result = $wpdb->update(
        $wpdb->prefix . 'dental_meta',
        array('meta_value' => $meta_value),
        array('meta_id' => $meta_id),
        array('%s'),
        array('%d')
    );
    
    if (!$result) {
        return false;
    }
    
    // Clear cache
    wp_cache_delete($dental_id, 'dental_meta');
    
    return true;
}

/**
 * Get dental meta data
 * 
 * @param int $dental_id Dental order ID
 * @param string $meta_key Meta key (optional)
 * @param bool $single Whether to return a single value
 * @return mixed Meta value(s)
 */
function get_dental_meta($dental_id, $meta_key = '', $single = false) {
    global $wpdb;
    
    if (!$dental_id) {
        return false;
    }
    
    $dental_id = (int) $dental_id;
    
    if ($meta_key) {
        $meta_key = wp_unslash($meta_key);
        $meta_value = $wpdb->get_var($wpdb->prepare(
            "SELECT meta_value FROM {$wpdb->prefix}dental_meta WHERE dental_id = %d AND meta_key = %s",
            $dental_id, $meta_key
        ));
        
        if ($meta_value) {
            return maybe_unserialize($meta_value);
        }
        
        return $single ? '' : array();
    }
    
    // Get all meta for dental order
    $meta_values = $wpdb->get_results($wpdb->prepare(
        "SELECT meta_key, meta_value FROM {$wpdb->prefix}dental_meta WHERE dental_id = %d",
        $dental_id
    ));
    
    $meta = array();
    foreach ($meta_values as $meta_value) {
        $meta[$meta_value->meta_key][] = maybe_unserialize($meta_value->meta_value);
    }
    
    if ($single) {
        return array_map('_get_single_meta_value', $meta);
    }
    
    return $meta;
}

/**
 * Delete dental meta data
 * 
 * @param int $dental_id Dental order ID
 * @param string $meta_key Meta key (optional)
 * @param mixed $meta_value Meta value (optional)
 * @return bool True on successful delete, false on failure
 */
function delete_dental_meta($dental_id, $meta_key = '', $meta_value = '') {
    global $wpdb;
    
    if (!$dental_id) {
        return false;
    }
    
    $dental_id = (int) $dental_id;
    
    if ($meta_key) {
        $meta_key = wp_unslash($meta_key);
        
        if ($meta_value !== '') {
            $meta_value = wp_unslash($meta_value);
            $meta_value = maybe_serialize($meta_value);
            
            $result = $wpdb->delete(
                $wpdb->prefix . 'dental_meta',
                array(
                    'dental_id' => $dental_id,
                    'meta_key' => $meta_key,
                    'meta_value' => $meta_value,
                ),
                array('%d', '%s', '%s')
            );
        } else {
            $result = $wpdb->delete(
                $wpdb->prefix . 'dental_meta',
                array(
                    'dental_id' => $dental_id,
                    'meta_key' => $meta_key,
                ),
                array('%d', '%s')
            );
        }
    } else {
        $result = $wpdb->delete(
            $wpdb->prefix . 'dental_meta',
            array('dental_id' => $dental_id),
            array('%d')
        );
    }
    
    if (!$result) {
        return false;
    }
    
    // Clear cache
    wp_cache_delete($dental_id, 'dental_meta');
    
    return true;
}

/**
 * Check if dental meta key exists
 * 
 * @param int $dental_id Dental order ID
 * @param string $meta_key Meta key
 * @return bool True if meta key exists, false otherwise
 */
function dental_meta_exists($dental_id, $meta_key) {
    global $wpdb;
    
    if (!$dental_id || !$meta_key) {
        return false;
    }
    
    $dental_id = (int) $dental_id;
    $meta_key = wp_unslash($meta_key);
    
    return (bool) $wpdb->get_var($wpdb->prepare(
        "SELECT meta_id FROM {$wpdb->prefix}dental_meta WHERE dental_id = %d AND meta_key = %s",
        $dental_id, $meta_key
    ));
}

/**
 * Helper function to get single meta value
 * 
 * @param array $meta_array Meta array
 * @return mixed Single meta value
 */
function _get_single_meta_value($meta_array) {
    return is_array($meta_array) ? $meta_array[0] : $meta_array;
}

/**
 * Dental Attachments Functions
 * Core functions for managing dental attachments
 */

/**
 * Add attachment to database
 * 
 * @param array $attachment_data Attachment data
 * @return int|bool Attachment ID on success, false on failure
 */
function add_dental_attachment($attachment_data) {
    global $wpdb;
    
    $table_name = $wpdb->prefix . 'dental_attachments';
    
    $result = $wpdb->insert($table_name, $attachment_data);
    
    if (!$result) {
        return false;
    }
    
    return $wpdb->insert_id;
}

/**
 * Get attachments for a dental order
 * 
 * @param int $dental_id Dental order ID
 * @param bool $include_temp Whether to include temporary files
 * @return array Array of attachments
 */
function get_dental_attachments($dental_id, $include_temp = false) {
    global $wpdb;
    
    $table_name = $wpdb->prefix . 'dental_attachments';
    
    $where_clause = "dental_id = %d AND file_status = 'active'";
    $params = [$dental_id];
    
    if (!$include_temp) {
        $where_clause .= " AND is_temp = 0";
    }
    
    $sql = $wpdb->prepare(
        "SELECT * FROM $table_name WHERE $where_clause ORDER BY created_at ASC",
        $params
    );
    
    return $wpdb->get_results($sql);
}

/**
 * Get attachment by ID
 * 
 * @param int $attachment_id Attachment ID
 * @return object|bool Attachment object or false
 */
function get_dental_attachment_by_id($attachment_id) {
    global $wpdb;
    
    $table_name = $wpdb->prefix . 'dental_attachments';
    
    return $wpdb->get_row($wpdb->prepare(
        "SELECT * FROM $table_name WHERE id = %d AND file_status = 'active'",
        $attachment_id
    ));
}

/**
 * Update attachment status from temp to permanent
 * 
 * @param array $attachment_ids Array of attachment IDs
 * @param int $dental_id Dental order ID
 * @return bool Success status
 */
function update_attachments_to_permanent($attachment_ids, $dental_id) {
    global $wpdb;
    
    if (empty($attachment_ids)) {
        return true;
    }
    
    $table_name = $wpdb->prefix . 'dental_attachments';
    
    // Update file paths and URLs for each attachment
    foreach ($attachment_ids as $attachment_id) {
        // Get attachment without file_status filter to ensure we find it
        $attachment = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM $table_name WHERE id = %d",
            $attachment_id
        ));
        
        if (!$attachment) {
            continue;
        }
        
        // Keep files in original username folder - no need to move them
        $result = $wpdb->update(
            $table_name,
            [
                'is_temp' => 0,
                'dental_id' => $dental_id
            ],
            ['id' => $attachment_id]
        );
        
        // Update completed
    }
    
    return true;
}

/**
 * Get attachment IDs string for dental order
 * 
 * @param int $dental_id Dental order ID
 * @return string Comma-separated attachment IDs
 */
function get_dental_attachment_ids($dental_id) {
    global $wpdb;
    
    $table_name = $wpdb->prefix . 'dental_attachments';
    
    $attachment_ids = $wpdb->get_col($wpdb->prepare(
        "SELECT id FROM $table_name WHERE dental_id = %d AND is_temp = 0 AND file_status = 'active' ORDER BY created_at ASC",
        $dental_id
    ));
    
    return implode(',', $attachment_ids);
}

/**
 * Update dental order attachment IDs
 * 
 * @param int $dental_id Dental order ID
 * @return bool Success status
 */
function update_dental_attachment_ids($dental_id) {
    global $wpdb;
    
    $attachment_ids = get_dental_attachment_ids($dental_id);
    
    $table_name = $wpdb->prefix . 'dentals';
    
    return $wpdb->update(
        $table_name,
        ['attachment_ids' => $attachment_ids],
        ['id' => $dental_id]
    );
}

/**
 * Restore a deleted attachment
 * 
 * @param int $attachment_id Attachment ID
 * @return bool Success status
 */
function restore_dental_attachment($attachment_id) {
    global $wpdb;
    
    $table_name = $wpdb->prefix . 'dental_attachments';
    
    $result = $wpdb->update(
        $table_name,
        [
            'file_status' => 'active',
            'deleted_at' => null
        ],
        [
            'id' => $attachment_id,
            'file_status' => 'deleted'
        ]
    );
    
    return $result !== false;
}
