<?php
/**
 * Database migrator for handling upgrades and data migrations.
 *
 * @package Freelance_Studio
 */

class FS_Migrator {

    /**
     * Run all necessary migrations.
     */
    public static function run() {
        // Prevent multiple runs in same request
        static $migration_run = false;
        if ($migration_run) {
            return;
        }
        $migration_run = true;

        $current_version = get_option('fs_version', '0.0.0');
        
        // Check if we need to force a full schema check (set during activation)
        $force_schema_check = get_option('fs_force_schema_check', '0') === '1';
        if ($force_schema_check) {
            delete_option('fs_force_schema_check');
            error_log('Freelance Studio: Running forced schema verification after activation');
        }

        // Run migrations based on version
        if (version_compare($current_version, '1.0.0', '<')) {
            self::migrate_to_1_0_0();
        }
        
        // Always check for address_2 column (in case update was applied without migration)
        self::ensure_address_2_column();
        
        // Ensure proposals table has new columns
        self::ensure_proposals_columns();
        
        // Ensure contracts table exists and has all columns
        self::ensure_contracts_table();
        
        // Ensure contract sections table exists and has data
        self::ensure_contract_sections();
        
        // Ensure annual deductions table exists
        self::ensure_annual_deductions_table();
        
        // Ensure projects table has enhanced billing fields
        self::ensure_project_billing_fields();
        
        // Ensure forms tables exist
        self::ensure_forms_tables();
        
        // Ensure form templates table exists
        self::ensure_form_templates_table();

        // Remove deprecated hourly_rate column from projects (rate now comes from services)
        self::remove_project_hourly_rate_column();

        // Ensure enhanced invoicing system tables and columns exist (v3.2.0)
        self::ensure_enhanced_invoicing_system();

        // Check for legacy Sports Freelancing data to migrate
        self::check_legacy_migration();
        
        // Fix orphaned time entries (v3.2.16) - entries marked invoiced but invoice was deleted
        if (version_compare($current_version, '3.2.16', '<')) {
            self::fix_orphaned_time_entries();
        }
        
        // Migrate primary contacts from client record to contacts table (v3.5.0)
        // Always check if migration is needed based on actual data state
        self::migrate_primary_contacts_to_contacts_table();
        
        // Ensure retainer tracking table exists (v3.8.0)
        self::ensure_retainer_tracking_table();
        
        // Clean up orphaned tasks and milestones from deleted projects (v3.8.1)
        self::cleanup_orphaned_records();
        
        // Ensure retainer columns exist in projects table (v3.8.1)
        self::ensure_retainer_columns();
        
        // Migrate task assignee 'designer' to 'freelancer' and update contract templates (v3.10.12)
        self::migrate_designer_to_freelancer();
        
        // Ensure projects have archived column (v4.0.10)
        self::ensure_project_archived_column();
        
        // Ensure proposals have custom_sections column (v4.0.13)
        self::ensure_proposal_custom_sections_column();
        
        // Ensure clients table has all required columns including logo_url (v4.0.14)
        self::ensure_clients_columns();
        
        // Ensure project files tables exist (v5.0.15)
        self::ensure_project_files_tables();
        
        // Ensure bookings table has all columns including wants_reminder (v5.1.6)
        self::ensure_bookings_columns();
        
        // Fix projects billing_type from enum to varchar if needed (v5.2.8)
        self::fix_billing_type_enum();

        // Ensure tasks table has recurring columns (v5.4.0)
        self::ensure_task_recurring_columns();

        // Ensure tasks table has previous_status column for archiving (v6.0.0)
        self::ensure_task_previous_status_column();

        // Ensure recurring invoices table has time entry columns (v6.1.0)
        self::ensure_recurring_time_columns();

        // Ensure tasks table has hide_until_due column (v7.1.3)
        self::ensure_task_hide_until_due_column();

        // Ensure tasks table has is_non_billable column (v7.1.5)
        self::ensure_task_non_billable_column();

        // Ensure proposals and contracts have archived column (v7.4.3)
        self::ensure_proposals_archived_column();
        self::ensure_contracts_archived_column();

        // Backfill NULL hourly_rate on time entries from project service rates (v7.6.7)
        self::backfill_time_entry_hourly_rates();

        // Ensure projects table has retainer_reset_day column (v7.10.20)
        self::ensure_retainer_reset_day_column();

        // Ensure contracts table has contact override columns (v8.1.2)
        self::ensure_contract_contact_columns();

        // Ensure transactions table exists (v8.4.0)
        self::ensure_transactions_table();

        // Update version
        update_option('fs_version', JEEB_FS_VERSION);
    }
    
    /**
     * Fix projects billing_type column from enum to varchar (v5.2.8)
     * The enum type caused dbDelta conflicts leading to data corruption on MyISAM tables.
     */
    private static function fix_billing_type_enum() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_projects';
        
        // Check if table exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'");
        if (!$table_exists) {
            return;
        }
        
        // Check current column type
        $column_info = $wpdb->get_row("SHOW COLUMNS FROM {$table} LIKE 'billing_type'");
        
        if ($column_info && strpos(strtolower($column_info->Type), 'enum') !== false) {
            $wpdb->query("ALTER TABLE {$table} MODIFY COLUMN billing_type varchar(50) DEFAULT 'fixed'");
            error_log('Freelance Studio v5.2.8: Converted billing_type from enum to varchar');
        }
    }
    
    /**
     * Fix orphaned time entries (v3.2.16)
     * Time entries that were marked as invoiced but whose invoice was deleted
     * will be reset to unbilled status so they can be invoiced again.
     */
    private static function fix_orphaned_time_entries() {
        global $wpdb;
        
        $time_table = $wpdb->prefix . 'fs_time_entries';
        $invoice_table = $wpdb->prefix . 'fs_invoices';
        
        // Check if the time entries table exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$time_table}'");
        if (!$table_exists) {
            return;
        }
        
        // Find and fix time entries that are marked invoiced=1 but whose invoice_id doesn't exist
        $fixed = $wpdb->query(
            "UPDATE {$time_table} t 
             LEFT JOIN {$invoice_table} i ON t.invoice_id = i.id 
             SET t.invoiced = 0, t.invoice_id = NULL 
             WHERE t.invoiced = 1 
             AND (t.invoice_id IS NULL OR i.id IS NULL)"
        );
        
        if ($fixed > 0) {
            error_log("Freelance Studio: Fixed {$fixed} orphaned time entries during migration to 3.2.16");
        }
    }

    /**
     * Migration to version 1.0.0
     */
    private static function migrate_to_1_0_0() {
        // Ensure all tables exist
        require_once JEEB_FS_PLUGIN_DIR . 'includes/class-fs-activator.php';
        FS_Activator::activate();

        error_log('Freelance Studio: Migrated to version 1.0.0');
    }

    /**
     * Ensure address_2 column exists in clients table
     */
    private static function ensure_address_2_column() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_clients';
        
        // Check if column exists
        $column_exists = $wpdb->get_var("SHOW COLUMNS FROM {$table} LIKE 'address_2'");
        
        if (!$column_exists) {
            $wpdb->query("ALTER TABLE {$table} ADD COLUMN address_2 varchar(255) DEFAULT NULL AFTER address");
            error_log('Freelance Studio: Added address_2 column to clients table');
        }
    }

    /**
     * Ensure proposals table has all required columns
     */
    private static function ensure_proposals_columns() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_proposals';
        
        // Check if table exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'") === $table;
        if (!$table_exists) {
            return;
        }
        
        // List of columns to ensure exist - order matters, no AFTER references
        $columns = array(
            'services' => "ADD COLUMN services longtext DEFAULT NULL",
            'deposit_required' => "ADD COLUMN deposit_required tinyint(1) DEFAULT 0",
            'deposit_type' => "ADD COLUMN deposit_type varchar(20) DEFAULT 'percentage'",
            'deposit_value' => "ADD COLUMN deposit_value decimal(10,2) DEFAULT 0",
            'billing_frequency' => "ADD COLUMN billing_frequency varchar(20) DEFAULT 'once'",
            'first_invoice_date' => "ADD COLUMN first_invoice_date date DEFAULT NULL",
            'start_date' => "ADD COLUMN start_date date DEFAULT NULL",
            'internal_notes' => "ADD COLUMN internal_notes text DEFAULT NULL",
            'rejection_reason' => "ADD COLUMN rejection_reason text DEFAULT NULL",
            'signature_client_name' => "ADD COLUMN signature_client_name varchar(255) DEFAULT NULL",
            'revision_notes' => "ADD COLUMN revision_notes text DEFAULT NULL",
            'revision_requested_at' => "ADD COLUMN revision_requested_at datetime DEFAULT NULL",
            'send_to_contacts' => "ADD COLUMN send_to_contacts text DEFAULT NULL",
        );
        
        foreach ($columns as $column_name => $alter_sql) {
            $column_exists = $wpdb->get_var("SHOW COLUMNS FROM {$table} LIKE '{$column_name}'");
            
            if (!$column_exists) {
                $result = $wpdb->query("ALTER TABLE {$table} {$alter_sql}");
                if ($result !== false) {
                    error_log("Freelance Studio: Added {$column_name} column to proposals table");
                } else {
                    error_log("Freelance Studio: Failed to add {$column_name} column - " . $wpdb->last_error);
                }
            }
        }
        
        // Also ensure invoices table has send_to_contacts column
        self::ensure_invoices_columns();
    }
    
    /**
     * Ensure invoices table has all required columns
     */
    private static function ensure_invoices_columns() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_invoices';
        
        // Check if table exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'") === $table;
        if (!$table_exists) {
            return;
        }
        
        // List of columns to ensure exist
        $columns = array(
            'send_to_contacts' => "ADD COLUMN send_to_contacts text DEFAULT NULL",
            'sent_at' => "ADD COLUMN sent_at datetime DEFAULT NULL",
            'view_token' => "ADD COLUMN view_token varchar(64) DEFAULT NULL",
        );
        
        foreach ($columns as $column_name => $alter_sql) {
            $column_exists = $wpdb->get_var("SHOW COLUMNS FROM {$table} LIKE '{$column_name}'");
            
            if (!$column_exists) {
                $result = $wpdb->query("ALTER TABLE {$table} {$alter_sql}");
                if ($result !== false) {
                    error_log("Freelance Studio: Added {$column_name} column to invoices table");
                } else {
                    error_log("Freelance Studio: Failed to add {$column_name} column - " . $wpdb->last_error);
                }
            }
        }
    }

    /**
     * Ensure contracts table exists and has all columns
     */
    private static function ensure_contracts_table() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_contracts';
        
        // Check if table exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'") === $table;
        
        if (!$table_exists) {
            // Create the full table
            require_once JEEB_FS_PLUGIN_DIR . 'includes/class-fs-activator.php';
            $charset_collate = $wpdb->get_charset_collate();
            
            $sql = "CREATE TABLE IF NOT EXISTS $table (
                id bigint(20) NOT NULL AUTO_INCREMENT,
                proposal_id bigint(20) DEFAULT NULL,
                client_id bigint(20) NOT NULL,
                project_id bigint(20) DEFAULT NULL,
                contract_number varchar(50) NOT NULL,
                title varchar(255) NOT NULL,
                status varchar(50) DEFAULT 'draft',
                services longtext DEFAULT NULL,
                pricing_type varchar(20) DEFAULT 'fixed',
                hourly_rate decimal(10,2) DEFAULT NULL,
                estimated_hours decimal(10,2) DEFAULT NULL,
                total_amount decimal(10,2) DEFAULT 0.00,
                deposit_required tinyint(1) DEFAULT 0,
                deposit_type varchar(20) DEFAULT 'percentage',
                deposit_value decimal(10,2) DEFAULT 0.00,
                billing_frequency varchar(20) DEFAULT 'once',
                first_invoice_date date DEFAULT NULL,
                enabled_sections text DEFAULT NULL,
                terms_content longtext DEFAULT NULL,
                start_date date DEFAULT NULL,
                end_date date DEFAULT NULL,
                valid_until date DEFAULT NULL,
                notes text DEFAULT NULL,
                internal_notes text DEFAULT NULL,
                send_to_contacts text DEFAULT NULL,
                signature_freelancer longtext DEFAULT NULL,
                signature_freelancer_name varchar(255) DEFAULT NULL,
                signature_freelancer_date datetime DEFAULT NULL,
                signature_client longtext DEFAULT NULL,
                signature_client_name varchar(255) DEFAULT NULL,
                signature_client_date datetime DEFAULT NULL,
                signature_client_ip varchar(45) DEFAULT NULL,
                signed_pdf_path varchar(500) DEFAULT NULL,
                sent_at datetime DEFAULT NULL,
                viewed_at datetime DEFAULT NULL,
                signed_at datetime DEFAULT NULL,
                view_token varchar(64) DEFAULT NULL,
                created_at datetime DEFAULT CURRENT_TIMESTAMP,
                updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY contract_number (contract_number),
                KEY proposal_id (proposal_id),
                KEY client_id (client_id),
                KEY project_id (project_id),
                KEY status (status),
                KEY view_token (view_token)
            ) $charset_collate;";
            
            require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
            dbDelta($sql);
            error_log('Freelance Studio: Created contracts table');
            return;
        }
        
        // Table exists - ensure all columns are present (without AFTER clauses to avoid dependency issues)
        $columns = array(
            'services' => "ADD COLUMN services longtext DEFAULT NULL",
            'pricing_type' => "ADD COLUMN pricing_type varchar(20) DEFAULT 'fixed'",
            'hourly_rate' => "ADD COLUMN hourly_rate decimal(10,2) DEFAULT NULL",
            'estimated_hours' => "ADD COLUMN estimated_hours decimal(10,2) DEFAULT NULL",
            'total_amount' => "ADD COLUMN total_amount decimal(10,2) DEFAULT 0.00",
            'deposit_required' => "ADD COLUMN deposit_required tinyint(1) DEFAULT 0",
            'deposit_type' => "ADD COLUMN deposit_type varchar(20) DEFAULT 'percentage'",
            'deposit_value' => "ADD COLUMN deposit_value decimal(10,2) DEFAULT 0.00",
            'billing_frequency' => "ADD COLUMN billing_frequency varchar(20) DEFAULT 'once'",
            'first_invoice_date' => "ADD COLUMN first_invoice_date date DEFAULT NULL",
            'enabled_sections' => "ADD COLUMN enabled_sections text DEFAULT NULL",
            'terms_content' => "ADD COLUMN terms_content longtext DEFAULT NULL",
            'start_date' => "ADD COLUMN start_date date DEFAULT NULL",
            'end_date' => "ADD COLUMN end_date date DEFAULT NULL",
            'valid_until' => "ADD COLUMN valid_until date DEFAULT NULL",
            'notes' => "ADD COLUMN notes text DEFAULT NULL",
            'internal_notes' => "ADD COLUMN internal_notes text DEFAULT NULL",
            'send_to_contacts' => "ADD COLUMN send_to_contacts text DEFAULT NULL",
            'signature_freelancer' => "ADD COLUMN signature_freelancer longtext DEFAULT NULL",
            'signature_freelancer_name' => "ADD COLUMN signature_freelancer_name varchar(255) DEFAULT NULL",
            'signature_freelancer_date' => "ADD COLUMN signature_freelancer_date datetime DEFAULT NULL",
            'signature_client' => "ADD COLUMN signature_client longtext DEFAULT NULL",
            'signature_client_name' => "ADD COLUMN signature_client_name varchar(255) DEFAULT NULL",
            'signature_client_date' => "ADD COLUMN signature_client_date datetime DEFAULT NULL",
            'signature_client_ip' => "ADD COLUMN signature_client_ip varchar(45) DEFAULT NULL",
            'signed_pdf_path' => "ADD COLUMN signed_pdf_path varchar(500) DEFAULT NULL",
            'sent_at' => "ADD COLUMN sent_at datetime DEFAULT NULL",
            'viewed_at' => "ADD COLUMN viewed_at datetime DEFAULT NULL",
            'signed_at' => "ADD COLUMN signed_at datetime DEFAULT NULL",
            'view_token' => "ADD COLUMN view_token varchar(64) DEFAULT NULL",
        );
        
        foreach ($columns as $column_name => $alter_sql) {
            $column_exists = $wpdb->get_var("SHOW COLUMNS FROM {$table} LIKE '{$column_name}'");
            
            if (!$column_exists) {
                $result = $wpdb->query("ALTER TABLE {$table} {$alter_sql}");
                if ($result !== false) {
                    error_log("Freelance Studio: Added {$column_name} column to contracts table");
                } else {
                    error_log("Freelance Studio: Failed to add {$column_name} column to contracts - " . $wpdb->last_error);
                }
            }
        }
        
        // Ensure signature columns are LONGTEXT (they might have been created as TEXT)
        $signature_columns = array('signature_freelancer', 'signature_client');
        foreach ($signature_columns as $col) {
            $column_type = $wpdb->get_var("SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = '{$table}' 
                AND COLUMN_NAME = '{$col}'");
            
            if ($column_type && strtolower($column_type) === 'text') {
                $wpdb->query("ALTER TABLE {$table} MODIFY COLUMN {$col} LONGTEXT DEFAULT NULL");
                error_log("Freelance Studio: Modified {$col} column to LONGTEXT");
            }
        }
    }

    /**
     * Ensure contract sections table exists and has data
     */
    private static function ensure_contract_sections() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_contract_sections';
        
        // Check if table exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'") === $table;
        
        if (!$table_exists) {
            // Create the table
            $charset_collate = $wpdb->get_charset_collate();
            
            $sql = "CREATE TABLE IF NOT EXISTS $table (
                id bigint(20) NOT NULL AUTO_INCREMENT,
                section_key varchar(100) NOT NULL,
                title varchar(255) NOT NULL,
                content longtext NOT NULL,
                sort_order int(11) DEFAULT 0,
                is_required tinyint(1) DEFAULT 0,
                is_active tinyint(1) DEFAULT 1,
                category varchar(50) DEFAULT 'terms',
                created_at datetime DEFAULT CURRENT_TIMESTAMP,
                updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY section_key (section_key),
                KEY sort_order (sort_order),
                KEY category (category)
            ) $charset_collate;";
            
            require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
            dbDelta($sql);
            error_log('Freelance Studio: Created contract_sections table');
        }
        
        // Check if sections have been installed
        $count = $wpdb->get_var("SELECT COUNT(*) FROM $table");
        
        if ($count == 0) {
            // Install default sections
            require_once JEEB_FS_PLUGIN_DIR . 'includes/class-fs-activator.php';
            $sections = FS_Activator::get_default_contract_sections();
            
            foreach ($sections as $index => $section) {
                $wpdb->insert($table, array(
                    'section_key' => $section['key'],
                    'title' => $section['title'],
                    'content' => $section['content'],
                    'sort_order' => $index + 1,
                    'is_required' => isset($section['required']) ? $section['required'] : 0,
                    'is_active' => 1,
                    'category' => isset($section['category']) ? $section['category'] : 'terms',
                ));
            }
            error_log('Freelance Studio: Installed default contract sections');
        }
    }

    /**
     * Check for and migrate legacy Sports Freelancing Pro data
     */
    private static function check_legacy_migration() {
        global $wpdb;

        // Check if legacy tables exist
        $legacy_clients = $wpdb->prefix . 'sf_clients';
        $legacy_exists = $wpdb->get_var("SHOW TABLES LIKE '$legacy_clients'") === $legacy_clients;

        if ($legacy_exists && !get_option('fs_legacy_migrated', false)) {
            self::migrate_from_sports_freelancing();
        }
    }

    /**
     * Migrate data from Sports Freelancing Pro plugin
     */
    private static function migrate_from_sports_freelancing() {
        global $wpdb;

        error_log('Freelance Studio: Starting legacy data migration from Sports Freelancing Pro');

        // Migrate clients
        self::migrate_legacy_clients();

        // Migrate gigs to projects
        self::migrate_legacy_gigs();

        // Migrate invoices
        self::migrate_legacy_invoices();

        // Migrate expenses
        self::migrate_legacy_expenses();

        // Migrate settings
        self::migrate_legacy_settings();

        // Migrate quarterly tax payments
        self::migrate_legacy_quarterly_payments();

        // Mark migration as complete
        update_option('fs_legacy_migrated', true);
        update_option('fs_legacy_migration_date', current_time('mysql'));

        error_log('Freelance Studio: Legacy data migration completed');
    }

    /**
     * Migrate clients from legacy plugin
     */
    private static function migrate_legacy_clients() {
        global $wpdb;

        $legacy_table = $wpdb->prefix . 'sf_clients';
        $new_table = $wpdb->prefix . 'fs_clients';

        // Check if legacy table has data
        $legacy_count = $wpdb->get_var("SELECT COUNT(*) FROM $legacy_table");
        if (!$legacy_count) {
            return;
        }

        // Get all legacy clients
        $legacy_clients = $wpdb->get_results("SELECT * FROM $legacy_table");

        foreach ($legacy_clients as $client) {
            // Check if already migrated (by matching name and email)
            $exists = $wpdb->get_var($wpdb->prepare(
                "SELECT id FROM $new_table WHERE name = %s AND email = %s",
                $client->name,
                $client->email
            ));

            if ($exists) {
                continue;
            }

            // Insert into new table
            $wpdb->insert(
                $new_table,
                array(
                    'name' => $client->name,
                    'company_name' => isset($client->company_name) ? $client->company_name : null,
                    'email' => $client->email,
                    'phone' => $client->phone,
                    'address' => $client->address,
                    'city' => $client->city,
                    'state' => $client->state,
                    'zip' => $client->zip,
                    'country' => $client->country,
                    'notes' => $client->notes,
                    'created_at' => $client->created_at,
                    'updated_at' => $client->updated_at
                ),
                array('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')
            );

            $new_id = $wpdb->insert_id;

            // Store mapping for later use
            $mappings = get_option('fs_legacy_client_map', array());
            $mappings[$client->id] = $new_id;
            update_option('fs_legacy_client_map', $mappings);

            // If there's a contact_person, create a contact record
            if (!empty($client->contact_person)) {
                $contacts_table = $wpdb->prefix . 'fs_client_contacts';
                $wpdb->insert(
                    $contacts_table,
                    array(
                        'client_id' => $new_id,
                        'name' => $client->contact_person,
                        'email' => isset($client->payroll_contact_email) ? $client->payroll_contact_email : $client->email,
                        'phone' => isset($client->payroll_contact_phone) ? $client->payroll_contact_phone : $client->phone,
                        'role' => 'Primary Contact',
                        'is_primary' => 1,
                        'is_billing_contact' => 1,
                        'is_project_contact' => 1
                    ),
                    array('%d', '%s', '%s', '%s', '%s', '%d', '%d', '%d')
                );
            }
        }

        error_log('Freelance Studio: Migrated ' . count($legacy_clients) . ' clients');
    }

    /**
     * Migrate gigs to projects
     */
    private static function migrate_legacy_gigs() {
        global $wpdb;

        $legacy_table = $wpdb->prefix . 'sf_gigs';
        $new_table = $wpdb->prefix . 'fs_projects';

        // Check if legacy table exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '$legacy_table'") === $legacy_table;
        if (!$table_exists) {
            return;
        }

        $legacy_gigs = $wpdb->get_results("SELECT * FROM $legacy_table");
        $client_map = get_option('fs_legacy_client_map', array());

        foreach ($legacy_gigs as $gig) {
            // Map client ID
            $new_client_id = isset($client_map[$gig->client_id]) ? $client_map[$gig->client_id] : $gig->client_id;

            // Map status
            $status_map = array(
                'Pending' => 'pending',
                'In Progress' => 'active',
                'Invoiced' => 'completed',
                'Paid' => 'completed'
            );
            $new_status = isset($status_map[$gig->status]) ? $status_map[$gig->status] : 'pending';

            // Calculate totals from gig days if available
            $gig_days_table = $wpdb->prefix . 'sf_gig_days';
            $days_exist = $wpdb->get_var("SHOW TABLES LIKE '$gig_days_table'") === $gig_days_table;
            
            $total_hours = 0;
            $total_pay = 0;
            $hourly_rate = null;

            if ($days_exist) {
                $gig_days = $wpdb->get_results($wpdb->prepare(
                    "SELECT * FROM $gig_days_table WHERE gig_id = %d",
                    $gig->id
                ));

                foreach ($gig_days as $day) {
                    $total_hours += floatval($day->base_hours) + floatval($day->overtime_hours);
                    $total_pay += floatval($day->base_pay) + floatval($day->per_diem);
                }

                if ($total_hours > 0) {
                    $hourly_rate = $total_pay / $total_hours;
                }
            }

            // Insert as project
            $wpdb->insert(
                $new_table,
                array(
                    'client_id' => $new_client_id,
                    'name' => $gig->event_name,
                    'description' => $gig->camera_position,
                    'status' => $new_status,
                    'billing_type' => 'hourly',
                    'hourly_rate' => $hourly_rate,
                    'estimated_hours' => $total_hours > 0 ? $total_hours : null,
                    'notes' => $gig->reimbursement_note,
                    'created_at' => $gig->created_at,
                    'updated_at' => $gig->updated_at
                ),
                array('%d', '%s', '%s', '%s', '%s', '%f', '%f', '%s', '%s', '%s')
            );

            $new_id = $wpdb->insert_id;

            // Store mapping
            $mappings = get_option('fs_legacy_gig_map', array());
            $mappings[$gig->id] = $new_id;
            update_option('fs_legacy_gig_map', $mappings);
        }

        error_log('Freelance Studio: Migrated ' . count($legacy_gigs) . ' gigs to projects');
    }

    /**
     * Migrate invoices
     */
    private static function migrate_legacy_invoices() {
        global $wpdb;

        $legacy_table = $wpdb->prefix . 'sf_invoices';
        $new_table = $wpdb->prefix . 'fs_invoices';

        // Check if legacy table exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '$legacy_table'") === $legacy_table;
        if (!$table_exists) {
            return;
        }

        $legacy_invoices = $wpdb->get_results("SELECT * FROM $legacy_table");
        $client_map = get_option('fs_legacy_client_map', array());
        $gig_map = get_option('fs_legacy_gig_map', array());

        foreach ($legacy_invoices as $invoice) {
            // Check if invoice number already exists
            $exists = $wpdb->get_var($wpdb->prepare(
                "SELECT id FROM $new_table WHERE invoice_number = %s",
                $invoice->invoice_number
            ));

            if ($exists) {
                continue;
            }

            // Map client ID
            $new_client_id = isset($client_map[$invoice->client_id]) ? $client_map[$invoice->client_id] : $invoice->client_id;

            // Map project ID (from gig)
            $new_project_id = null;
            if (!empty($invoice->gig_id) && isset($gig_map[$invoice->gig_id])) {
                $new_project_id = $gig_map[$invoice->gig_id];
            }

            // Calculate balance due
            $balance_due = floatval($invoice->total);
            if ($invoice->status === 'paid') {
                $balance_due = 0;
            }

            // Insert invoice
            $wpdb->insert(
                $new_table,
                array(
                    'client_id' => $new_client_id,
                    'project_id' => $new_project_id,
                    'invoice_number' => $invoice->invoice_number,
                    'invoice_date' => $invoice->invoice_date,
                    'due_date' => $invoice->due_date,
                    'status' => $invoice->status,
                    'subtotal' => $invoice->subtotal,
                    'tax_rate' => $invoice->tax_rate,
                    'tax_amount' => $invoice->tax_amount,
                    'total' => $invoice->total,
                    'balance_due' => $balance_due,
                    'amount_paid' => $invoice->status === 'paid' ? $invoice->total : 0,
                    'notes' => $invoice->notes,
                    'items' => $invoice->items,
                    'expenses' => isset($invoice->expenses) ? $invoice->expenses : null,
                    'created_at' => $invoice->created_at,
                    'updated_at' => $invoice->updated_at
                ),
                array('%d', '%d', '%s', '%s', '%s', '%s', '%f', '%f', '%f', '%f', '%f', '%f', '%s', '%s', '%s', '%s', '%s')
            );
        }

        error_log('Freelance Studio: Migrated ' . count($legacy_invoices) . ' invoices');
    }

    /**
     * Migrate expenses
     */
    private static function migrate_legacy_expenses() {
        global $wpdb;

        $legacy_table = $wpdb->prefix . 'sf_expenses';
        $new_table = $wpdb->prefix . 'fs_expenses';

        // Check if legacy table exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '$legacy_table'") === $legacy_table;
        if (!$table_exists) {
            return;
        }

        $legacy_expenses = $wpdb->get_results("SELECT * FROM $legacy_table");

        foreach ($legacy_expenses as $expense) {
            $wpdb->insert(
                $new_table,
                array(
                    'expense_date' => $expense->expense_date,
                    'expense_name' => $expense->expense_name,
                    'category' => $expense->category,
                    'amount' => $expense->amount,
                    'notes' => $expense->notes,
                    'receipt_id' => isset($expense->receipt_id) ? $expense->receipt_id : null,
                    'receipt_filename' => isset($expense->receipt_filename) ? $expense->receipt_filename : null,
                    'receipt_path' => isset($expense->receipt_path) ? $expense->receipt_path : null,
                    'receipt_url' => isset($expense->receipt_url) ? $expense->receipt_url : null,
                    'receipt_mime_type' => isset($expense->receipt_mime_type) ? $expense->receipt_mime_type : null,
                    'receipt_file_size' => isset($expense->receipt_file_size) ? $expense->receipt_file_size : null,
                    'receipt_upload_date' => isset($expense->receipt_upload_date) ? $expense->receipt_upload_date : null,
                    'created_at' => $expense->created_at,
                    'updated_at' => $expense->updated_at
                )
            );
        }

        error_log('Freelance Studio: Migrated ' . count($legacy_expenses) . ' expenses');
    }

    /**
     * Migrate settings from legacy plugin
     */
    private static function migrate_legacy_settings() {
        // Map old option names to new ones
        $settings_map = array(
            'sf_business_name' => 'fs_business_name',
            'sf_business_email' => 'fs_business_email',
            'sf_business_phone' => 'fs_business_phone',
            'sf_business_website' => 'fs_business_website',
            'sf_business_address' => 'fs_business_address',
            'sf_business_city' => 'fs_business_city',
            'sf_business_state' => 'fs_business_state',
            'sf_business_zip' => 'fs_business_zip',
            'sf_business_country' => 'fs_business_country',
            'sf_business_logo' => 'fs_business_logo',
            'sf_invoice_primary_color' => 'fs_invoice_primary_color',
            'sf_payment_instructions' => 'fs_payment_instructions',
            'sf_tax_state' => 'fs_tax_state',
            'sf_federal_tax_bracket' => 'fs_federal_tax_bracket'
        );

        foreach ($settings_map as $old_key => $new_key) {
            $old_value = get_option($old_key);
            if ($old_value !== false && get_option($new_key) === false) {
                update_option($new_key, $old_value);
            }
        }

        error_log('Freelance Studio: Migrated settings');
    }

    /**
     * Migrate quarterly tax payments
     */
    private static function migrate_legacy_quarterly_payments() {
        global $wpdb;

        $legacy_table = $wpdb->prefix . 'sf_quarterly_tax_payments';
        $new_table = $wpdb->prefix . 'fs_quarterly_tax_payments';

        // Check if legacy table exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '$legacy_table'") === $legacy_table;
        if (!$table_exists) {
            return;
        }

        $legacy_payments = $wpdb->get_results("SELECT * FROM $legacy_table");

        foreach ($legacy_payments as $payment) {
            $wpdb->insert(
                $new_table,
                array(
                    'user_id' => $payment->user_id,
                    'year' => $payment->year,
                    'quarter' => $payment->quarter,
                    'estimated_amount' => isset($payment->estimated_amount) ? $payment->estimated_amount : 0,
                    'actual_payment' => $payment->actual_payment,
                    'payment_date' => isset($payment->payment_date) ? $payment->payment_date : null,
                    'notes' => isset($payment->notes) ? $payment->notes : null,
                    'created_at' => $payment->created_at,
                    'updated_at' => $payment->updated_at
                )
            );
        }

        error_log('Freelance Studio: Migrated quarterly tax payments');
    }

    /**
     * Ensure annual deductions table exists
     */
    private static function ensure_annual_deductions_table() {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'fs_annual_deductions';
        
        // Check if table exists
        $table_exists = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = %s AND table_name = %s",
                DB_NAME,
                $table_name
            )
        );
        
        if (!$table_exists) {
            $charset_collate = $wpdb->get_charset_collate();
            
            $sql = "CREATE TABLE IF NOT EXISTS $table_name (
                id bigint(20) NOT NULL AUTO_INCREMENT,
                user_id bigint(20) NOT NULL,
                year int(4) NOT NULL,
                name varchar(255) NOT NULL,
                category varchar(100) NOT NULL,
                annual_amount decimal(10,2) DEFAULT 0.00,
                deduction_percentage decimal(5,2) DEFAULT 100.00,
                notes text DEFAULT NULL,
                is_active tinyint(1) DEFAULT 1,
                created_at datetime DEFAULT CURRENT_TIMESTAMP,
                updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY user_id (user_id),
                KEY year (year),
                KEY category (category),
                KEY user_year (user_id, year)
            ) $charset_collate;";
            
            require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
            dbDelta($sql);
            
            error_log('Freelance Studio: Created annual deductions table');
        }
    }

    /**
     * Ensure projects table has enhanced billing fields
     */
    public static function ensure_project_billing_fields() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_projects';
        
        // Check and add billing_frequency column
        $col_exists = $wpdb->get_var("SHOW COLUMNS FROM {$table} LIKE 'billing_frequency'");
        if (!$col_exists) {
            $wpdb->query("ALTER TABLE {$table} ADD COLUMN billing_frequency varchar(20) DEFAULT 'once'");
            error_log('Freelance Studio: Added billing_frequency column to projects table');
        }
        
        // Check and add first_invoice_date column
        $col_exists = $wpdb->get_var("SHOW COLUMNS FROM {$table} LIKE 'first_invoice_date'");
        if (!$col_exists) {
            $wpdb->query("ALTER TABLE {$table} ADD COLUMN first_invoice_date date DEFAULT NULL");
            error_log('Freelance Studio: Added first_invoice_date column to projects table');
        }
        
        // Check and add deposit_type column
        $col_exists = $wpdb->get_var("SHOW COLUMNS FROM {$table} LIKE 'deposit_type'");
        if (!$col_exists) {
            $wpdb->query("ALTER TABLE {$table} ADD COLUMN deposit_type varchar(20) DEFAULT 'percentage'");
            error_log('Freelance Studio: Added deposit_type column to projects table');
        }
        
        // Check and add services column (JSON storage for line items)
        $col_exists = $wpdb->get_var("SHOW COLUMNS FROM {$table} LIKE 'services'");
        if (!$col_exists) {
            $wpdb->query("ALTER TABLE {$table} ADD COLUMN services longtext DEFAULT NULL");
            error_log('Freelance Studio: Added services column to projects table');
        }
        
        // Check and add total_amount column (calculated from services)
        $col_exists = $wpdb->get_var("SHOW COLUMNS FROM {$table} LIKE 'total_amount'");
        if (!$col_exists) {
            $wpdb->query("ALTER TABLE {$table} ADD COLUMN total_amount decimal(10,2) DEFAULT 0.00");
            error_log('Freelance Studio: Added total_amount column to projects table');
        }
    }

    /**
     * Ensure forms and form_responses tables exist
     */
    public static function ensure_forms_tables() {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        // Check if forms table exists
        $forms_table = $wpdb->prefix . 'fs_forms';
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$forms_table}'");
        
        if (!$table_exists) {
            require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
            
            $sql = "CREATE TABLE {$forms_table} (
                id bigint(20) NOT NULL AUTO_INCREMENT,
                name varchar(255) NOT NULL,
                description text,
                project_id bigint(20) DEFAULT NULL,
                client_id bigint(20) DEFAULT NULL,
                status varchar(20) DEFAULT 'draft',
                fields longtext,
                settings text,
                public_token varchar(64) NOT NULL,
                created_at datetime DEFAULT CURRENT_TIMESTAMP,
                updated_at datetime DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY public_token (public_token),
                KEY project_id (project_id),
                KEY client_id (client_id),
                KEY status (status)
            ) $charset_collate;";
            
            dbDelta($sql);
            error_log('Freelance Studio: Created fs_forms table');
        }
        
        // Check if form_responses table exists
        $responses_table = $wpdb->prefix . 'fs_form_responses';
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$responses_table}'");
        
        if (!$table_exists) {
            require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
            
            $sql = "CREATE TABLE {$responses_table} (
                id bigint(20) NOT NULL AUTO_INCREMENT,
                form_id bigint(20) NOT NULL,
                client_id bigint(20) DEFAULT NULL,
                project_id bigint(20) DEFAULT NULL,
                respondent_name varchar(255),
                respondent_email varchar(255),
                response_data longtext NOT NULL,
                ip_address varchar(45),
                user_agent varchar(500),
                submitted_at datetime DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY form_id (form_id),
                KEY client_id (client_id),
                KEY project_id (project_id),
                KEY submitted_at (submitted_at)
            ) $charset_collate;";
            
            dbDelta($sql);
            error_log('Freelance Studio: Created fs_form_responses table');
        }
    }

    /**
     * Ensure form_templates table exists
     */
    public static function ensure_form_templates_table() {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        $table = $wpdb->prefix . 'fs_form_templates';
        
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'");
        
        if (!$table_exists) {
            require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
            
            $sql = "CREATE TABLE {$table} (
                id bigint(20) NOT NULL AUTO_INCREMENT,
                name varchar(255) NOT NULL,
                description text,
                category varchar(50) DEFAULT 'other',
                fields longtext,
                settings text,
                is_default tinyint(1) DEFAULT 0,
                created_at datetime DEFAULT CURRENT_TIMESTAMP,
                updated_at datetime DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY category (category),
                KEY is_default (is_default)
            ) $charset_collate;";
            
            dbDelta($sql);
            error_log('Freelance Studio: Created fs_form_templates table');
            
            // Install default templates
            if (class_exists('FS_Form_Template')) {
                FS_Form_Template::install_defaults();
            }
        }
    }

    /**
     * Remove deprecated hourly_rate column from projects table.
     * Hourly rate is now derived from project services.
     */
    private static function remove_project_hourly_rate_column() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_projects';
        
        // Check if table exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'") === $table;
        if (!$table_exists) {
            return;
        }
        
        // Check if column exists before trying to remove
        $column_exists = $wpdb->get_var("SHOW COLUMNS FROM {$table} LIKE 'hourly_rate'");
        
        if ($column_exists) {
            $wpdb->query("ALTER TABLE {$table} DROP COLUMN hourly_rate");
            error_log('Freelance Studio: Removed deprecated hourly_rate column from projects table');
        }
    }

    /**
     * Ensure enhanced invoicing system columns and tables exist
     * Added in v3.2.0 for comprehensive invoice management
     */
    private static function ensure_enhanced_invoicing_system() {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();
        
        // 1. Add new columns to fs_invoices table
        $invoices_table = $wpdb->prefix . 'fs_invoices';
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$invoices_table}'") === $invoices_table;
        
        if ($table_exists) {
            $columns_to_add = array(
                'invoice_source' => "ADD COLUMN invoice_source VARCHAR(20) DEFAULT 'custom'",
                'time_grouping' => "ADD COLUMN time_grouping VARCHAR(20) DEFAULT NULL",
                'show_entry_dates' => "ADD COLUMN show_entry_dates TINYINT(1) DEFAULT 0",
            );
            
            foreach ($columns_to_add as $col_name => $alter_sql) {
                $col_exists = $wpdb->get_var("SHOW COLUMNS FROM {$invoices_table} LIKE '{$col_name}'");
                if (!$col_exists) {
                    $wpdb->query("ALTER TABLE {$invoices_table} {$alter_sql}");
                    error_log("Freelance Studio: Added {$col_name} column to invoices table");
                }
            }
        }
        
        // 2. Create fs_invoice_items table for detailed line item tracking
        $items_table = $wpdb->prefix . 'fs_invoice_items';
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$items_table}'");
        
        if (!$table_exists) {
            require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
            
            $sql = "CREATE TABLE {$items_table} (
                id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                invoice_id BIGINT(20) UNSIGNED NOT NULL,
                project_id BIGINT(20) UNSIGNED DEFAULT NULL,
                task_id BIGINT(20) UNSIGNED DEFAULT NULL,
                service_index INT DEFAULT NULL,
                activity VARCHAR(50) DEFAULT NULL,
                source_type VARCHAR(20) DEFAULT 'manual',
                description TEXT NOT NULL,
                quantity DECIMAL(10,2) DEFAULT 1.00,
                rate DECIMAL(10,2) DEFAULT 0.00,
                amount DECIMAL(10,2) DEFAULT 0.00,
                original_rate DECIMAL(10,2) DEFAULT NULL,
                time_entry_ids LONGTEXT DEFAULT NULL,
                entry_date DATE DEFAULT NULL,
                sort_order INT DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY invoice_id (invoice_id),
                KEY project_id (project_id),
                KEY source_type (source_type)
            ) $charset_collate;";
            
            dbDelta($sql);
            error_log('Freelance Studio: Created fs_invoice_items table');
        }
        
        // 3. Create fs_invoice_projects junction table for multi-project invoices
        $projects_table = $wpdb->prefix . 'fs_invoice_projects';
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$projects_table}'");
        
        if (!$table_exists) {
            require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
            
            $sql = "CREATE TABLE {$projects_table} (
                id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                invoice_id BIGINT(20) UNSIGNED NOT NULL,
                project_id BIGINT(20) UNSIGNED NOT NULL,
                subtotal DECIMAL(10,2) DEFAULT 0.00,
                PRIMARY KEY (id),
                KEY invoice_id (invoice_id),
                KEY project_id (project_id),
                UNIQUE KEY invoice_project (invoice_id, project_id)
            ) $charset_collate;";
            
            dbDelta($sql);
            error_log('Freelance Studio: Created fs_invoice_projects table');
        }
    }
    
    /**
     * Migrate primary contacts from client record to contacts table (v3.5.0)
     * 
     * This moves the name, email, phone fields from the fs_clients table 
     * to the fs_client_contacts table as a "Primary Contact" entry.
     * Only migrates if no primary contact already exists in contacts table.
     * 
     * This migration is idempotent - it can be run multiple times safely.
     */
    private static function migrate_primary_contacts_to_contacts_table() {
        global $wpdb;
        
        $clients_table = $wpdb->prefix . 'fs_clients';
        $contacts_table = $wpdb->prefix . 'fs_client_contacts';
        
        // Get all clients that have contact info in the legacy fields (name)
        // and don't have a PRIMARY contact in the contacts table yet
        $clients = $wpdb->get_results( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
            "SELECT c.id, c.name, c.email, c.phone 
             FROM {$clients_table} c
             WHERE c.name IS NOT NULL AND c.name != '' AND TRIM(c.name) != ''
             AND NOT EXISTS (
                 SELECT 1 FROM {$contacts_table} cc 
                 WHERE cc.client_id = c.id AND cc.is_primary = 1
             )"
        );
        
        if (empty($clients)) {
            // No clients need migration
            return;
        }
        
        error_log('Freelance Studio v3.5.0: Found ' . count($clients) . ' clients needing contact migration');
        
        $migrated = 0;
        $errors = 0;
        
        foreach ($clients as $client) {
            // Check if a contact with this exact name already exists for this client
            $existing_contact = $wpdb->get_var($wpdb->prepare(
                "SELECT id FROM {$contacts_table} WHERE client_id = %d AND name = %s",
                $client->id,
                trim($client->name)
            ));
            
            if ($existing_contact) {
                // Contact with same name exists, just mark it as primary
                $wpdb->update(
                    $contacts_table,
                    array('is_primary' => 1, 'is_billing_contact' => 1),
                    array('id' => $existing_contact),
                    array('%d', '%d'),
                    array('%d')
                );
                $migrated++;
                error_log("Freelance Studio v3.5.0: Marked existing contact as primary for client ID {$client->id}: {$client->name}");
                continue;
            }
            
            // Create new contact from the client's legacy contact fields
            $result = $wpdb->insert(
                $contacts_table,
                array(
                    'client_id' => $client->id,
                    'name' => trim($client->name),
                    'email' => $client->email ? trim($client->email) : '',
                    'phone' => $client->phone ? trim($client->phone) : '',
                    'role' => '', // No role set for migrated contacts
                    'is_primary' => 1,
                    'is_billing_contact' => 1, // Assume primary contact is also billing contact
                    'is_project_contact' => 0,
                    'notes' => '',
                ),
                array('%d', '%s', '%s', '%s', '%s', '%d', '%d', '%d', '%s')
            );
            
            if ($result) {
                $migrated++;
                error_log("Freelance Studio v3.5.0: Created new contact for client ID {$client->id}: {$client->name}");
            } else {
                $errors++;
                error_log("Freelance Studio v3.5.0: Failed to create contact for client ID {$client->id}: " . $wpdb->last_error);
            }
        }
        
        if ($migrated > 0 || $errors > 0) {
            error_log("Freelance Studio v3.5.0: Contact migration complete - migrated: {$migrated}, errors: {$errors}");
        }
    }

    /**
     * Ensure retainer tracking table exists (v3.8.0)
     * Tracks monthly retainer usage and rollover hours
     */
    private static function ensure_retainer_tracking_table() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_retainer_periods';
        $charset_collate = $wpdb->get_charset_collate();
        
        // Check if table exists
        if ($wpdb->get_var("SHOW TABLES LIKE '{$table}'") === $table) {
            return;
        }
        
        $sql = "CREATE TABLE {$table} (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            project_id bigint(20) UNSIGNED NOT NULL,
            period_start date NOT NULL,
            period_end date NOT NULL,
            hours_included decimal(10,2) DEFAULT 0,
            hours_used decimal(10,2) DEFAULT 0,
            hours_rollover_in decimal(10,2) DEFAULT 0,
            hours_rollover_out decimal(10,2) DEFAULT 0,
            amount decimal(10,2) DEFAULT 0,
            invoiced tinyint(1) DEFAULT 0,
            invoice_id bigint(20) UNSIGNED DEFAULT NULL,
            notes text,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY project_id (project_id),
            KEY period_start (period_start),
            KEY invoice_id (invoice_id)
        ) {$charset_collate};";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
        
        error_log('Freelance Studio v3.8.0: Created retainer_periods table');
    }

    /**
     * Clean up orphaned tasks and milestones from deleted projects (v3.8.1)
     */
    private static function cleanup_orphaned_records() {
        global $wpdb;
        
        $tasks_table = $wpdb->prefix . 'fs_tasks';
        $milestones_table = $wpdb->prefix . 'fs_milestones';
        $projects_table = $wpdb->prefix . 'fs_projects';
        
        // Check if tables exist
        if (!$wpdb->get_var("SHOW TABLES LIKE '{$tasks_table}'")) {
            return;
        }
        
        // Delete orphaned tasks (have a project_id but project doesn't exist)
        $orphaned_tasks = $wpdb->query(
            "DELETE t FROM {$tasks_table} t
             LEFT JOIN {$projects_table} p ON t.project_id = p.id
             WHERE t.project_id IS NOT NULL AND p.id IS NULL"
        );
        
        // Delete orphaned milestones
        if ($wpdb->get_var("SHOW TABLES LIKE '{$milestones_table}'")) {
            $orphaned_milestones = $wpdb->query(
                "DELETE m FROM {$milestones_table} m
                 LEFT JOIN {$projects_table} p ON m.project_id = p.id
                 WHERE m.project_id IS NOT NULL AND p.id IS NULL"
            );
            
            if ($orphaned_milestones > 0) {
                error_log("Freelance Studio v3.8.1: Cleaned up {$orphaned_milestones} orphaned milestones");
            }
        }
        
        if ($orphaned_tasks > 0) {
            error_log("Freelance Studio v3.8.1: Cleaned up {$orphaned_tasks} orphaned tasks");
        }
    }

    /**
     * Ensure retainer columns exist in projects table (v3.8.1, updated v3.8.2)
     */
    private static function ensure_retainer_columns() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_projects';
        
        // Check if is_retainer column exists
        $column_exists = $wpdb->get_var("SHOW COLUMNS FROM {$table} LIKE 'is_retainer'");
        
        if (!$column_exists) {
            $wpdb->query("ALTER TABLE {$table} ADD COLUMN is_retainer tinyint(1) DEFAULT 0 AFTER due_date");
            $wpdb->query("ALTER TABLE {$table} ADD COLUMN retainer_amount decimal(10,2) DEFAULT NULL AFTER is_retainer");
            $wpdb->query("ALTER TABLE {$table} ADD COLUMN retainer_hours decimal(10,2) DEFAULT NULL AFTER retainer_amount");
            $wpdb->query("ALTER TABLE {$table} ADD COLUMN retainer_rollover tinyint(1) DEFAULT 1 AFTER retainer_hours");
            
            error_log('Freelance Studio v3.8.2: Added retainer columns to projects table');
        }
        
        // v3.8.2 - Ensure rollover column exists even if other retainer columns already exist
        $rollover_exists = $wpdb->get_var("SHOW COLUMNS FROM {$table} LIKE 'retainer_rollover'");
        if (!$rollover_exists) {
            $wpdb->query("ALTER TABLE {$table} ADD COLUMN retainer_rollover tinyint(1) DEFAULT 1 AFTER retainer_hours");
            error_log('Freelance Studio v3.8.2: Added retainer_rollover column to projects table');
        }
    }
    
    /**
     * Migrate task assignee from 'designer' to 'freelancer' and update contract templates (v3.10.12)
     */
    private static function migrate_designer_to_freelancer() {
        global $wpdb;
        
        // Check if migration already done
        $migration_done = get_option('fs_designer_to_freelancer_migrated_v2', false);
        if ($migration_done) {
            return;
        }
        
        // Update tasks table
        $tasks_table = $wpdb->prefix . 'fs_tasks';
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$tasks_table}'");
        
        if ($table_exists) {
            $updated = $wpdb->query(
                $wpdb->prepare(
                    "UPDATE {$tasks_table} SET assigned_to = %s WHERE assigned_to = %s",
                    'freelancer',
                    'designer'
                )
            );
            
            if ($updated !== false) {
                error_log("Freelance Studio v3.10.12: Migrated {$updated} tasks from 'designer' to 'freelancer'");
            }
        }
        
        // Update contract sections - replace "Designer" with "Freelancer"
        $sections_table = $wpdb->prefix . 'fs_contract_sections';
        $sections_exist = $wpdb->get_var("SHOW TABLES LIKE '{$sections_table}'");
        
        if ($sections_exist) {
            // Update section_key for freelancer_tools (outside of loop, only needs to run once)
            $wpdb->query($wpdb->prepare(
                "UPDATE {$sections_table} SET section_key = %s WHERE section_key = %s",
                'freelancer_tools',
                'designer_tools'
            ));
            
            // Get all sections
            $sections = $wpdb->get_results("SELECT id, title, content FROM {$sections_table}");
            
            foreach ($sections as $section) {
                $new_title = str_replace('Designer', 'Freelancer', $section->title);
                $new_content = str_replace('Designer', 'Freelancer', $section->content);
                $new_content = str_replace('designer', 'freelancer', $new_content);
                // Also update the jurisdiction placeholder
                $new_content = str_replace("[Designer's State/Country]", "[Freelancer's State/Country]", $new_content);
                
                // Update if changed
                if ($new_title !== $section->title || $new_content !== $section->content) {
                    $wpdb->update(
                        $sections_table,
                        array(
                            'title' => $new_title,
                            'content' => $new_content,
                        ),
                        array('id' => $section->id),
                        array('%s', '%s'),
                        array('%d')
                    );
                }
            }
            
            error_log('Freelance Studio v3.10.12: Updated contract sections from Designer to Freelancer');
        }
        
        // Mark migration as done
        update_option('fs_designer_to_freelancer_migrated_v2', true);
    }
    
    /**
     * Ensure projects table has archived column (v4.0.10)
     */
    private static function ensure_project_archived_column() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_projects';
        
        // Check if table exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'");
        if (!$table_exists) {
            return;
        }
        
        // Check if archived column exists
        $column_exists = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
                 WHERE TABLE_SCHEMA = %s 
                 AND TABLE_NAME = %s 
                 AND COLUMN_NAME = 'archived'",
                DB_NAME,
                $table
            )
        );
        
        if (!$column_exists) {
            $wpdb->query("ALTER TABLE {$table} ADD COLUMN archived tinyint(1) DEFAULT 0 AFTER status");
            $wpdb->query("ALTER TABLE {$table} ADD KEY archived (archived)");
            error_log('Freelance Studio v4.0.10: Added archived column to projects table');
        }
    }
    
    /**
     * Ensure proposals table has custom_sections column (v4.0.13)
     */
    private static function ensure_proposal_custom_sections_column() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_proposals';
        
        // Check if table exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'");
        if (!$table_exists) {
            return;
        }
        
        // Check if custom_sections column exists using SHOW COLUMNS (more reliable)
        $columns = $wpdb->get_results("SHOW COLUMNS FROM {$table} LIKE 'custom_sections'");
        
        if (empty($columns)) {
            // Suppress errors for this query in case of race condition
            $wpdb->suppress_errors(true);
            $result = $wpdb->query("ALTER TABLE {$table} ADD COLUMN custom_sections longtext DEFAULT NULL AFTER services");
            $wpdb->suppress_errors(false);
            
            if ($result !== false) {
                error_log('Freelance Studio v4.0.13: Added custom_sections column to proposals table');
            }
        }
        
        // Also ensure proposal section templates table exists
        self::ensure_proposal_section_templates_table();
    }
    
    /**
     * Ensure proposal section templates table exists (v4.0.13)
     */
    private static function ensure_proposal_section_templates_table() {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'fs_proposal_section_templates';
        $charset_collate = $wpdb->get_charset_collate();
        
        // Check if table already exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table_name}'");
        if ($table_exists) {
            return;
        }
        
        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            title varchar(255) NOT NULL,
            content longtext NOT NULL,
            position varchar(20) DEFAULT 'before_pricing',
            sort_order int(11) DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY position (position),
            KEY sort_order (sort_order)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
        
        error_log('Freelance Studio v4.0.13: Created proposal_section_templates table');
    }
    
    /**
     * Ensure clients table has all required columns (v4.0.14)
     * This fixes issues where logo_url column may be missing
     */
    private static function ensure_clients_columns() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_clients';
        
        // Check if table exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'");
        if (!$table_exists) {
            return;
        }
        
        // Get existing columns
        $existing_columns = array();
        $columns = $wpdb->get_results("SHOW COLUMNS FROM {$table}");
        foreach ($columns as $column) {
            $existing_columns[] = $column->Field;
        }
        
        // Required columns with their definitions
        $required_columns = array(
            'logo_id' => 'bigint(20) DEFAULT NULL',
            'logo_url' => 'varchar(500) DEFAULT NULL',
            'is_internal' => 'tinyint(1) DEFAULT 0',
            'address_2' => 'varchar(255) DEFAULT NULL',
        );
        
        // Suppress errors in case of race conditions
        $wpdb->suppress_errors(true);
        
        foreach ($required_columns as $column_name => $column_def) {
            if (!in_array($column_name, $existing_columns)) {
                $result = $wpdb->query("ALTER TABLE {$table} ADD COLUMN {$column_name} {$column_def}");
                if ($result !== false) {
                    error_log("Freelance Studio v4.0.14: Added {$column_name} column to clients table");
                }
            }
        }
        
        $wpdb->suppress_errors(false);
    }
    
    /**
     * Ensure project files tables exist (v5.0.15)
     */
    private static function ensure_project_files_tables() {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        // Project files table
        $files_table = $wpdb->prefix . 'fs_project_files';
        if ($wpdb->get_var("SHOW TABLES LIKE '{$files_table}'") !== $files_table) {
            $sql = "CREATE TABLE {$files_table} (
                id bigint(20) NOT NULL AUTO_INCREMENT,
                project_id bigint(20) NOT NULL,
                attachment_id bigint(20) NOT NULL,
                category varchar(50) DEFAULT 'general',
                uploaded_by bigint(20) DEFAULT NULL,
                uploaded_by_client tinyint(1) DEFAULT 0,
                shared_with_client tinyint(1) DEFAULT 0,
                notes text DEFAULT NULL,
                created_at datetime DEFAULT CURRENT_TIMESTAMP,
                updated_at datetime DEFAULT NULL,
                PRIMARY KEY (id),
                KEY project_id (project_id),
                KEY attachment_id (attachment_id),
                KEY category (category),
                KEY shared_with_client (shared_with_client)
            ) $charset_collate;";
            
            require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
            dbDelta($sql);
            error_log('Freelance Studio v5.0.15: Created fs_project_files table');
        }
        
        // Upload tokens table
        $tokens_table = $wpdb->prefix . 'fs_upload_tokens';
        if ($wpdb->get_var("SHOW TABLES LIKE '{$tokens_table}'") !== $tokens_table) {
            $sql = "CREATE TABLE {$tokens_table} (
                id bigint(20) NOT NULL AUTO_INCREMENT,
                project_id bigint(20) NOT NULL,
                token varchar(64) NOT NULL,
                expires_at datetime NOT NULL,
                used_at datetime DEFAULT NULL,
                created_at datetime DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY token (token),
                KEY project_id (project_id),
                KEY expires_at (expires_at)
            ) $charset_collate;";
            
            require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
            dbDelta($sql);
            error_log('Freelance Studio v5.0.15: Created fs_upload_tokens table');
        }
    }
    
    /**
     * Ensure bookings table has all columns (v5.1.6)
     */
    private static function ensure_bookings_columns() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'fs_bookings';
        
        // Check if table exists
        if ($wpdb->get_var("SHOW TABLES LIKE '{$table_name}'") !== $table_name) {
            return; // Table will be created by activator
        }
        
        // Check for wants_reminder column
        $has_wants_reminder = $wpdb->get_var(
            "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
             WHERE TABLE_SCHEMA = DATABASE() 
             AND TABLE_NAME = '{$table_name}' 
             AND COLUMN_NAME = 'wants_reminder'"
        );
        
        if (!$has_wants_reminder) {
            $wpdb->query("ALTER TABLE {$table_name} ADD COLUMN wants_reminder tinyint(1) DEFAULT 1 AFTER reminder_sent");
            error_log('Freelance Studio v5.1.6: Added wants_reminder column to bookings table');
        }
        
        // Check for meet_link column
        $has_meet_link = $wpdb->get_var(
            "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
             WHERE TABLE_SCHEMA = DATABASE() 
             AND TABLE_NAME = '{$table_name}' 
             AND COLUMN_NAME = 'meet_link'"
        );
        
        if (!$has_meet_link) {
            $wpdb->query("ALTER TABLE {$table_name} ADD COLUMN meet_link varchar(500) DEFAULT NULL AFTER gcal_event_id");
            error_log('Freelance Studio v5.1.6: Added meet_link column to bookings table');
        }
    }

    /**
     * Ensure tasks table has recurring task columns (v5.4.0)
     */
    private static function ensure_task_recurring_columns() {
        global $wpdb;

        $table = $wpdb->prefix . 'fs_tasks';

        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'") === $table;
        if (!$table_exists) {
            return;
        }

        $columns = array(
            'is_recurring' => "ADD COLUMN is_recurring tinyint(1) DEFAULT 0",
            'recurring_frequency' => "ADD COLUMN recurring_frequency varchar(20) DEFAULT NULL",
            'recurring_end_date' => "ADD COLUMN recurring_end_date date DEFAULT NULL",
            'parent_task_id' => "ADD COLUMN parent_task_id bigint(20) DEFAULT NULL",
        );

        foreach ($columns as $column_name => $alter_sql) {
            $column_exists = $wpdb->get_var("SHOW COLUMNS FROM {$table} LIKE '{$column_name}'");

            if (!$column_exists) {
                $result = $wpdb->query("ALTER TABLE {$table} {$alter_sql}");
                if ($result !== false) {
                    error_log("Freelance Studio v5.4.0: Added {$column_name} column to tasks table");
                } else {
                    error_log("Freelance Studio v5.4.0: Failed to add {$column_name} column - " . $wpdb->last_error);
                }
            }
        }
    }

    /**
     * Ensure tasks table has previous_status column for archive/restore (v6.0.0)
     */
    private static function ensure_task_previous_status_column() {
        global $wpdb;

        $table = $wpdb->prefix . 'fs_tasks';

        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'") === $table;
        if (!$table_exists) {
            return;
        }

        $column_exists = $wpdb->get_var("SHOW COLUMNS FROM {$table} LIKE 'previous_status'");

        if (!$column_exists) {
            $result = $wpdb->query("ALTER TABLE {$table} ADD COLUMN previous_status varchar(50) DEFAULT NULL AFTER status");
            if ($result !== false) {
                error_log("Freelance Studio v6.0.0: Added previous_status column to tasks table");
            } else {
                error_log("Freelance Studio v6.0.0: Failed to add previous_status column - " . $wpdb->last_error);
            }
        }
    }

    /**
     * Ensure recurring invoices table has time entry auto-population columns (v6.1.0)
     */
    private static function ensure_recurring_time_columns() {
        global $wpdb;

        $table = $wpdb->prefix . 'fs_recurring_invoices';

        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'") === $table;
        if (!$table_exists) {
            return;
        }

        $columns = array(
            'include_time_entries' => "ADD COLUMN include_time_entries tinyint(1) DEFAULT 0 AFTER auto_send",
            'time_grouping' => "ADD COLUMN time_grouping varchar(20) DEFAULT 'task' AFTER include_time_entries",
            'mark_time_invoiced' => "ADD COLUMN mark_time_invoiced tinyint(1) DEFAULT 1 AFTER time_grouping",
        );

        // Get all existing columns in one query
        $existing = $wpdb->get_col("DESCRIBE {$table}", 0);

        foreach ($columns as $col_name => $alter_sql) {
            if (!in_array($col_name, $existing)) {
                $result = $wpdb->query("ALTER TABLE {$table} {$alter_sql}");
                if ($result !== false) {
                    error_log("Freelance Studio v6.1.0: Added {$col_name} column to recurring_invoices table");
                }
            }
        }
    }

    /**
     * Ensure tasks table has hide_until_due column (v7.1.3)
     */
    private static function ensure_task_hide_until_due_column() {
        global $wpdb;

        $table = $wpdb->prefix . 'fs_tasks';

        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'") === $table;
        if (!$table_exists) {
            return;
        }

        $column_exists = $wpdb->get_var("SHOW COLUMNS FROM {$table} LIKE 'hide_until_due'");

        if (!$column_exists) {
            $result = $wpdb->query("ALTER TABLE {$table} ADD COLUMN hide_until_due tinyint(1) DEFAULT 0 AFTER parent_task_id");
            if ($result !== false) {
                error_log("Freelance Studio v7.1.3: Added hide_until_due column to tasks table");
            } else {
                error_log("Freelance Studio v7.1.3: Failed to add hide_until_due column - " . $wpdb->last_error);
            }
        }
    }

    /**
     * Ensure tasks table has is_non_billable column (v7.1.5)
     */
    private static function ensure_task_non_billable_column() {
        global $wpdb;

        $table = $wpdb->prefix . 'fs_tasks';

        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'") === $table;
        if (!$table_exists) {
            return;
        }

        $column_exists = $wpdb->get_var("SHOW COLUMNS FROM {$table} LIKE 'is_non_billable'");

        if (!$column_exists) {
            $result = $wpdb->query("ALTER TABLE {$table} ADD COLUMN is_non_billable tinyint(1) DEFAULT 0 AFTER hide_until_due");
            if ($result !== false) {
                error_log("Freelance Studio v7.1.5: Added is_non_billable column to tasks table");
            } else {
                error_log("Freelance Studio v7.1.5: Failed to add is_non_billable column - " . $wpdb->last_error);
            }
        }
    }

    /**
     * Ensure proposals table has archived column (v7.4.3)
     */
    private static function ensure_proposals_archived_column() {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_proposals';
        
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'");
        if (!$table_exists) return;
        
        $column_exists = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = 'archived'",
            DB_NAME, $table
        ));
        
        if (!$column_exists) {
            $wpdb->query("ALTER TABLE {$table} ADD COLUMN archived tinyint(1) DEFAULT 0 AFTER status");
            $wpdb->query("ALTER TABLE {$table} ADD KEY archived (archived)");
            error_log('Freelance Studio v7.4.3: Added archived column to proposals table');
        }
    }

    /**
     * Ensure contracts table has archived column (v7.4.3)
     */
    private static function ensure_contracts_archived_column() {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_contracts';
        
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'");
        if (!$table_exists) return;
        
        $column_exists = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = 'archived'",
            DB_NAME, $table
        ));
        
        if (!$column_exists) {
            $wpdb->query("ALTER TABLE {$table} ADD COLUMN archived tinyint(1) DEFAULT 0 AFTER status");
            $wpdb->query("ALTER TABLE {$table} ADD KEY archived (archived)");
            error_log('Freelance Studio v7.4.3: Added archived column to contracts table');
        }
    }

    /**
     * Backfill NULL or zero hourly_rate on time entries (v7.6.7)
     * 
     * Earlier versions didn't auto-populate hourly_rate at entry creation time.
     * This one-time migration stamps the correct rate on those legacy entries
     * using the same priority as the current create() logic:
     *   1. Project's first hourly service rate
     *   2. Global default rate
     * 
     * Uses an option flag so it only runs once.
     */
    private static function backfill_time_entry_hourly_rates() {
        // Only run once
        if (get_option('fs_migrated_time_entry_rates', false)) {
            return;
        }

        global $wpdb;
        $time_table = $wpdb->prefix . 'fs_time_entries';
        $projects_table = $wpdb->prefix . 'fs_projects';

        // Check if time entries table exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$time_table}'");
        if (!$table_exists) {
            update_option('fs_migrated_time_entry_rates', true);
            return;
        }

        // Find entries with NULL or zero hourly_rate
        $entries = $wpdb->get_results(
            "SELECT t.id, t.project_id, p.services 
             FROM {$time_table} t 
             LEFT JOIN {$projects_table} p ON t.project_id = p.id 
             WHERE t.hourly_rate IS NULL OR t.hourly_rate = 0"
        );

        if (empty($entries)) {
            update_option('fs_migrated_time_entry_rates', true);
            error_log('Freelance Studio v7.6.7: No time entries need hourly_rate backfill');
            return;
        }

        $default_rate = floatval(get_option('fs_default_hourly_rate', 75));
        $updated = 0;
        
        // Cache project service rates to avoid re-parsing JSON for every entry
        $project_rate_cache = array();

        foreach ($entries as $entry) {
            $rate = $default_rate;

            if ($entry->project_id) {
                // Check cache first
                if (!isset($project_rate_cache[$entry->project_id])) {
                    $project_rate_cache[$entry->project_id] = 0;
                    
                    $services = $entry->services;
                    if (is_string($services) && !empty($services)) {
                        $services = json_decode($services, true);
                    }
                    if (!empty($services) && is_array($services)) {
                        foreach ($services as $svc) {
                            if (isset($svc['pricing_type']) && $svc['pricing_type'] === 'hourly' && !empty($svc['rate'])) {
                                $project_rate_cache[$entry->project_id] = floatval($svc['rate']);
                                break;
                            }
                        }
                    }
                }

                if ($project_rate_cache[$entry->project_id] > 0) {
                    $rate = $project_rate_cache[$entry->project_id];
                }
            }

            $wpdb->update(
                $time_table,
                array('hourly_rate' => $rate),
                array('id' => $entry->id),
                array('%f'),
                array('%d')
            );
            $updated++;
        }

        update_option('fs_migrated_time_entry_rates', true);
        error_log("Freelance Studio v7.6.7: Backfilled hourly_rate on {$updated} time entries");
    }

    /**
     * Ensure projects table has retainer_reset_day column (v7.10.20)
     */
    private static function ensure_retainer_reset_day_column() {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_projects';
        if (!$wpdb->get_var("SHOW TABLES LIKE '{$table}'")) {
            return;
        }
        if (!$wpdb->get_var("SHOW COLUMNS FROM {$table} LIKE 'retainer_reset_day'")) {
            $wpdb->query("ALTER TABLE {$table} ADD COLUMN retainer_reset_day tinyint(2) DEFAULT NULL");
            error_log('Freelance Studio v7.10.20: Added retainer_reset_day column to projects table');
        }
    }

    /**
     * Ensure contracts table has contact override columns (v8.1.2)
     */
    private static function ensure_contract_contact_columns() {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_contracts';
        if (!$wpdb->get_var("SHOW TABLES LIKE '{$table}'")) {
            return;
        }
        $columns = array(
            'client_contact_id' => 'bigint(20) DEFAULT NULL',
            'contact_name' => 'varchar(255) DEFAULT NULL',
            'contact_email' => 'varchar(255) DEFAULT NULL',
            'contact_phone' => 'varchar(50) DEFAULT NULL',
        );
        foreach ($columns as $col => $def) {
            if (!$wpdb->get_var("SHOW COLUMNS FROM {$table} LIKE '{$col}'")) {
                $wpdb->query("ALTER TABLE {$table} ADD COLUMN {$col} {$def}");
                error_log("Freelance Studio v8.1.2: Added {$col} column to contracts table");
            }
        }
    }

    /**
     * Ensure transactions table exists (v8.4.0)
     */
    private static function ensure_transactions_table() {
        global $wpdb;

        $table = $wpdb->prefix . 'fs_transactions';
        $charset_collate = $wpdb->get_charset_collate();

        if ($wpdb->get_var("SHOW TABLES LIKE '{$table}'") === $table) {
            return;
        }

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $sql = "CREATE TABLE {$table} (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            transaction_date date NOT NULL,
            description text NOT NULL,
            amount decimal(10,2) NOT NULL,
            type varchar(10) NOT NULL DEFAULT 'debit',
            bank_reference varchar(255) DEFAULT '',
            status varchar(20) NOT NULL DEFAULT 'unmatched',
            linked_expense_id bigint(20) UNSIGNED DEFAULT NULL,
            linked_invoice_id bigint(20) UNSIGNED DEFAULT NULL,
            category varchar(100) DEFAULT NULL,
            is_business tinyint(1) NOT NULL DEFAULT 1,
            import_batch varchar(64) NOT NULL DEFAULT '',
            source_bank varchar(100) DEFAULT '',
            raw_data longtext DEFAULT NULL,
            notes text DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY transaction_date (transaction_date),
            KEY status (status),
            KEY type (type),
            KEY import_batch (import_batch),
            KEY linked_expense_id (linked_expense_id),
            KEY linked_invoice_id (linked_invoice_id)
        ) {$charset_collate};";

        dbDelta($sql);

        if ($wpdb->get_var("SHOW TABLES LIKE '{$table}'") === $table) {
            error_log('Freelance Studio v8.4.0: Created transactions table');
        }
    }
}
