<?php
/**
 * Project Model
 *
 * @package Freelance_Studio
 */

class FS_Project {

    /**
     * Project status constants
     */
    const STATUS_PENDING = 'pending';
    const STATUS_ACTIVE = 'active';
    const STATUS_ON_HOLD = 'on_hold';
    const STATUS_COMPLETED = 'completed';
    const STATUS_CANCELLED = 'cancelled';

    /**
     * Billing type constants
     */
    const BILLING_FIXED = 'fixed';
    const BILLING_HOURLY = 'hourly';
    const BILLING_RETAINER = 'retainer';

    /**
     * Create a new project
     *
     * @param array $data Project data
     * @return int|false Insert ID or false on failure
     */
    public static function create($data) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_projects';
        
        // Process services JSON
        $services = null;
        $total_amount = 0;
        if (!empty($data['services'])) {
            if (is_array($data['services'])) {
                $services = json_encode($data['services']);
                // Calculate total from services
                foreach ($data['services'] as $service) {
                    $total_amount += floatval($service['amount'] ?? 0);
                }
            } else {
                $services = $data['services'];
            }
        }
        
        // Calculate deposit amount based on deposit_type
        $deposit_amount = 0;
        $deposit_type = isset($data['deposit_type']) ? $data['deposit_type'] : 'percentage';
        if ($deposit_type === 'percentage' && !empty($data['deposit_percentage'])) {
            $base_amount = $total_amount > 0 ? $total_amount : floatval($data['fixed_price'] ?? 0);
            $deposit_amount = $base_amount * (floatval($data['deposit_percentage']) / 100);
        } elseif ($deposit_type === 'fixed' && !empty($data['deposit_amount'])) {
            $deposit_amount = floatval($data['deposit_amount']);
        }
        
        $insert_data = array(
                'client_id' => intval($data['client_id']),
                'contract_id' => !empty($data['contract_id']) ? intval($data['contract_id']) : null,
                'proposal_id' => !empty($data['proposal_id']) ? intval($data['proposal_id']) : null,
                'project_type_id' => !empty($data['project_type_id']) ? intval($data['project_type_id']) : null,
                'name' => sanitize_text_field($data['name']),
                'description' => isset($data['description']) ? sanitize_textarea_field($data['description']) : '',
                'status' => isset($data['status']) ? sanitize_text_field($data['status']) : self::STATUS_PENDING,
                'billing_type' => isset($data['billing_type']) ? sanitize_text_field($data['billing_type']) : self::BILLING_HOURLY,
                'billing_frequency' => isset($data['billing_frequency']) ? sanitize_text_field($data['billing_frequency']) : 'once',
                'estimated_hours' => isset($data['estimated_hours']) ? floatval($data['estimated_hours']) : null,
                'fixed_price' => isset($data['fixed_price']) ? floatval($data['fixed_price']) : null,
                'budget' => isset($data['budget']) ? floatval($data['budget']) : null,
                'services' => $services,
                'total_amount' => $total_amount,
                'deposit_type' => $deposit_type,
                'deposit_percentage' => isset($data['deposit_percentage']) ? floatval($data['deposit_percentage']) : 0,
                'deposit_amount' => $deposit_amount,
                'deposit_paid' => isset($data['deposit_paid']) ? intval($data['deposit_paid']) : 0,
                'first_invoice_date' => !empty($data['first_invoice_date']) ? sanitize_text_field($data['first_invoice_date']) : null,
                'start_date' => !empty($data['start_date']) ? sanitize_text_field($data['start_date']) : null,
                'due_date' => !empty($data['due_date']) ? sanitize_text_field($data['due_date']) : null,
                'completed_date' => !empty($data['completed_date']) ? sanitize_text_field($data['completed_date']) : null,
                'is_retainer' => isset($data['is_retainer']) ? intval($data['is_retainer']) : 0,
                'retainer_amount' => isset($data['retainer_amount']) ? floatval($data['retainer_amount']) : null,
                'retainer_hours' => isset($data['retainer_hours']) ? floatval($data['retainer_hours']) : null,
                'retainer_rollover' => isset($data['retainer_rollover']) ? intval($data['retainer_rollover']) : 1,
                'notes' => isset($data['notes']) ? sanitize_textarea_field($data['notes']) : '',
        );
        
        $result = $wpdb->insert($table, $insert_data);
        
        if ($result === false) {
            error_log('FS_Project::create failed: ' . $wpdb->last_error);
            return false;
        }
        
        $new_id = $wpdb->insert_id;
        
        // Fix NULL foreign keys that wpdb->insert converts to 0
        FS_DB_Helper::fix_null_fields($table, $new_id, array(
            'contract_id' => $insert_data['contract_id'],
            'proposal_id' => $insert_data['proposal_id'],
            'project_type_id' => $insert_data['project_type_id'],
        ));
        
        return $wpdb->insert_id;
    }

    /**
     * Get all projects
     *
     * @param array $args Query arguments
     * @return array
     */
    public static function get_all($args = array()) {
        global $wpdb;
        
        $defaults = array(
            'client_id' => null,
            'status' => null,
            'billing_type' => null,
            'archived' => false,  // Default to not showing archived
            'include_archived' => false,  // Set true to include all regardless of archived status
            'orderby' => 'created_at',
            'order' => 'DESC',
            'limit' => null,
            'offset' => 0
        );
        
        $args = wp_parse_args($args, $defaults);
        
        $projects_table = $wpdb->prefix . 'fs_projects';
        $clients_table = $wpdb->prefix . 'fs_clients';
        
        $where = array('1=1');
        
        if ($args['client_id']) {
            $where[] = $wpdb->prepare('p.client_id = %d', $args['client_id']);
        }
        
        if ($args['status']) {
            if (is_array($args['status'])) {
                $placeholders = implode(',', array_fill(0, count($args['status']), '%s'));
                $where[] = $wpdb->prepare("p.status IN ($placeholders)", $args['status']);
            } else {
                $where[] = $wpdb->prepare('p.status = %s', $args['status']);
            }
        }
        
        if ($args['billing_type']) {
            $where[] = $wpdb->prepare('p.billing_type = %s', $args['billing_type']);
        }
        
        // Handle archived filtering - default is to exclude archived
        if (!$args['include_archived']) {
            if ($args['archived'] === true || $args['archived'] === 'only') {
                // Show only archived projects
                $where[] = 'p.archived = 1';
            } else {
                // Default: exclude archived projects
                $where[] = '(p.archived = 0 OR p.archived IS NULL)';
            }
        }
        // If include_archived is true, no archived filter is applied
        
        $where_clause = implode(' AND ', $where);
        
        $allowed_orderby = array('created_at', 'name', 'due_date', 'start_date', 'status');
        $orderby = in_array($args['orderby'], $allowed_orderby) ? $args['orderby'] : 'created_at';
        $order = strtoupper($args['order']) === 'ASC' ? 'ASC' : 'DESC';
        
        $sql = "SELECT p.*, c.name as client_name, c.company_name as client_company, c.logo_id as client_logo_id, c.logo_url as client_logo_url 
                FROM {$projects_table} p 
                LEFT JOIN {$clients_table} c ON p.client_id = c.id 
                WHERE {$where_clause} 
                ORDER BY p.{$orderby} {$order}";
        
        if ($args['limit']) {
            $sql .= $wpdb->prepare(' LIMIT %d OFFSET %d', $args['limit'], $args['offset']);
        }
        
        $projects = $wpdb->get_results($sql); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
        
        // Add calculated totals to each project
        foreach ($projects as $project) {
            $totals = self::get_totals($project->id);
            $project->total_hours = $totals['total_hours'];
            $project->total_billed = $totals['total_billed'];
            $project->total_paid = $totals['total_paid'];
            $project->outstanding = $totals['outstanding'];
        }
        
        return $projects;
    }

    /**
     * Get a single project by ID
     *
     * @param int $id Project ID
     * @return object|null
     */
    public static function get($id) {
        global $wpdb;
        
        $projects_table = $wpdb->prefix . 'fs_projects';
        $clients_table = $wpdb->prefix . 'fs_clients';
        
        $project = $wpdb->get_row($wpdb->prepare(
            "SELECT p.*, c.name as client_name, c.company_name as client_company, c.email as client_email
             FROM {$projects_table} p 
             LEFT JOIN {$clients_table} c ON p.client_id = c.id 
             WHERE p.id = %d",
            $id
        ));
        
        if ($project) {
            $totals = self::get_totals($id);
            $project->total_hours = $totals['total_hours'];
            $project->total_billed = $totals['total_billed'];
            $project->total_paid = $totals['total_paid'];
            $project->outstanding = $totals['outstanding'];
        }
        
        return $project;
    }

    /**
     * Get projects by client
     *
     * @param int $client_id Client ID
     * @return array
     */
    public static function get_by_client($client_id) {
        return self::get_all(array('client_id' => $client_id));
    }

    /**
     * Update a project
     *
     * @param int   $id   Project ID
     * @param array $data Project data
     * @return int|false
     */
    public static function update($id, $data) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_projects';
        
        // Process services JSON
        $services = null;
        $total_amount = 0;
        if (isset($data['services'])) {
            if (is_array($data['services'])) {
                $services = json_encode($data['services']);
                // Calculate total from services
                foreach ($data['services'] as $service) {
                    $total_amount += floatval($service['amount'] ?? 0);
                }
            } else {
                $services = $data['services'];
            }
        }
        
        // Calculate deposit amount based on deposit_type
        $deposit_amount = 0;
        $deposit_type = isset($data['deposit_type']) ? $data['deposit_type'] : 'percentage';
        if ($deposit_type === 'percentage' && !empty($data['deposit_percentage'])) {
            $base_amount = $total_amount > 0 ? $total_amount : floatval($data['fixed_price'] ?? 0);
            $deposit_amount = $base_amount * (floatval($data['deposit_percentage']) / 100);
        } elseif ($deposit_type === 'fixed' && !empty($data['deposit_amount'])) {
            $deposit_amount = floatval($data['deposit_amount']);
        }
        
        // If status changed to completed, set completed_date
        $completed_date = null;
        if (isset($data['status']) && $data['status'] === self::STATUS_COMPLETED) {
            $existing = self::get($id);
            if ($existing && $existing->status !== self::STATUS_COMPLETED) {
                $completed_date = current_time('Y-m-d');
            } elseif (!empty($data['completed_date'])) {
                $completed_date = sanitize_text_field($data['completed_date']);
            }
        }
        
        $update_data = array(
            'client_id' => intval($data['client_id']),
            'name' => sanitize_text_field($data['name']),
            'description' => isset($data['description']) ? sanitize_textarea_field($data['description']) : '',
            'status' => isset($data['status']) ? sanitize_text_field($data['status']) : self::STATUS_PENDING,
            'billing_type' => isset($data['billing_type']) ? sanitize_text_field($data['billing_type']) : self::BILLING_HOURLY,
            'billing_frequency' => isset($data['billing_frequency']) ? sanitize_text_field($data['billing_frequency']) : 'once',
            'estimated_hours' => isset($data['estimated_hours']) ? floatval($data['estimated_hours']) : null,
            'fixed_price' => isset($data['fixed_price']) ? floatval($data['fixed_price']) : null,
            'budget' => isset($data['budget']) ? floatval($data['budget']) : null,
            'deposit_type' => $deposit_type,
            'deposit_percentage' => isset($data['deposit_percentage']) ? floatval($data['deposit_percentage']) : 0,
            'deposit_amount' => $deposit_amount,
            'deposit_paid' => isset($data['deposit_paid']) ? intval($data['deposit_paid']) : 0,
            'first_invoice_date' => !empty($data['first_invoice_date']) ? sanitize_text_field($data['first_invoice_date']) : null,
            'start_date' => !empty($data['start_date']) ? sanitize_text_field($data['start_date']) : null,
            'due_date' => !empty($data['due_date']) ? sanitize_text_field($data['due_date']) : null,
            'is_retainer' => isset($data['is_retainer']) ? intval($data['is_retainer']) : 0,
            'retainer_amount' => isset($data['retainer_amount']) ? floatval($data['retainer_amount']) : null,
            'retainer_hours' => isset($data['retainer_hours']) ? floatval($data['retainer_hours']) : null,
            'retainer_rollover' => isset($data['retainer_rollover']) ? intval($data['retainer_rollover']) : 1,
            'notes' => isset($data['notes']) ? sanitize_textarea_field($data['notes']) : '',
        );
        
        // Add services and total if provided
        if ($services !== null) {
            $update_data['services'] = $services;
            $update_data['total_amount'] = $total_amount;
        }
        
        if ($completed_date) {
            $update_data['completed_date'] = $completed_date;
        }
        
        // Handle optional foreign keys
        $nullable_fks = array();
        if (isset($data['contract_id'])) {
            if ($data['contract_id']) {
                $update_data['contract_id'] = intval($data['contract_id']);
            } else {
                $nullable_fks['contract_id'] = null;
            }
        }
        if (isset($data['proposal_id'])) {
            if ($data['proposal_id']) {
                $update_data['proposal_id'] = intval($data['proposal_id']);
            } else {
                $nullable_fks['proposal_id'] = null;
            }
        }
        if (isset($data['project_type_id'])) {
            if ($data['project_type_id']) {
                $update_data['project_type_id'] = intval($data['project_type_id']);
            } else {
                $nullable_fks['project_type_id'] = null;
            }
        }
        
        $result = $wpdb->update(
            $table,
            $update_data,
            array('id' => $id)
        );
        
        // Fix NULL foreign keys
        if (!empty($nullable_fks)) {
            FS_DB_Helper::fix_null_fields($table, $id, $nullable_fks);
        }
        
        if ($result === false) {
            error_log('FS_Project::update failed: ' . $wpdb->last_error);
        }
        
        return $result;
    }

    /**
     * Delete a project
     *
     * @param int $id Project ID
     * @return int|false
     */
    public static function delete($id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_projects';
        
        // Note: Consider what to do with related records (milestones, tasks, time entries)
        // For now, we'll leave them orphaned - could add cascade delete later
        
        return $wpdb->delete($table, array('id' => $id), array('%d'));
    }

    /**
     * Get project totals (hours, billed, paid)
     *
     * @param int $id Project ID
     * @return array
     */
    public static function get_totals($id) {
        global $wpdb;
        
        $time_table = $wpdb->prefix . 'fs_time_entries';
        $invoices_table = $wpdb->prefix . 'fs_invoices';
        
        // Apply user's time rounding setting in SQL
        $rounding_minutes = intval(get_option('fs_time_rounding', 0));
        $minimum_minutes = intval(get_option('fs_minimum_billable', 0));
        $dur_expr = 'duration';
        if ($minimum_minutes > 0) {
            $min_sec = $minimum_minutes * 60;
            $dur_expr = "GREATEST(duration, {$min_sec})";
        }
        if ($rounding_minutes > 0) {
            $rnd_sec = $rounding_minutes * 60;
            $dur_expr = "CEIL({$dur_expr} / {$rnd_sec}) * {$rnd_sec}";
        }
        
        // Total hours from time entries (rounded)
        $total_hours = (float) $wpdb->get_var($wpdb->prepare( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
            "SELECT SUM({$dur_expr}) / 3600 FROM {$time_table} WHERE project_id = %d",
            $id
        ));
        
        // Billable hours (rounded)
        $billable_hours = (float) $wpdb->get_var($wpdb->prepare( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
            "SELECT SUM({$dur_expr}) / 3600 FROM {$time_table} WHERE project_id = %d AND billable = 1",
            $id
        ));
        
        // Invoice totals - account for multi-project invoices via junction table
        $junction_table = $wpdb->prefix . 'fs_invoice_projects';
        $junction_exists = $wpdb->get_var("SHOW TABLES LIKE '{$junction_table}'");
        
        if ($junction_exists) {
            // Single-project invoices NOT in junction table (legacy)
            $legacy_billed = (float) $wpdb->get_var($wpdb->prepare(
                "SELECT COALESCE(SUM(total), 0) FROM {$invoices_table} 
                 WHERE project_id = %d AND status != 'cancelled'
                 AND id NOT IN (SELECT DISTINCT invoice_id FROM {$junction_table})",
                $id
            ));
            
            // Multi-project invoices via junction table - proportional share of total
            $junction_billed = (float) $wpdb->get_var($wpdb->prepare(
                "SELECT COALESCE(SUM(
                    CASE 
                        WHEN i.total = i.subtotal THEN jp.subtotal
                        WHEN i.subtotal > 0 THEN jp.subtotal * (i.total / i.subtotal)
                        ELSE jp.subtotal
                    END
                ), 0)
                FROM {$junction_table} jp
                JOIN {$invoices_table} i ON jp.invoice_id = i.id
                WHERE jp.project_id = %d AND i.status != 'cancelled'",
                $id
            ));
            $total_billed = $legacy_billed + $junction_billed;
            
            // Paid - same split
            $legacy_paid = (float) $wpdb->get_var($wpdb->prepare(
                "SELECT COALESCE(SUM(amount_paid), 0) FROM {$invoices_table} 
                 WHERE project_id = %d
                 AND id NOT IN (SELECT DISTINCT invoice_id FROM {$junction_table})",
                $id
            ));
            $junction_paid = (float) $wpdb->get_var($wpdb->prepare(
                "SELECT COALESCE(SUM(
                    CASE 
                        WHEN i.subtotal > 0 THEN i.amount_paid * (jp.subtotal / i.subtotal)
                        ELSE 0
                    END
                ), 0)
                FROM {$junction_table} jp
                JOIN {$invoices_table} i ON jp.invoice_id = i.id
                WHERE jp.project_id = %d",
                $id
            ));
            $total_paid = $legacy_paid + $junction_paid;
            
            // Outstanding - same split
            $legacy_outstanding = (float) $wpdb->get_var($wpdb->prepare(
                "SELECT COALESCE(SUM(balance_due), 0) FROM {$invoices_table} 
                 WHERE project_id = %d AND status NOT IN ('paid', 'cancelled')
                 AND id NOT IN (SELECT DISTINCT invoice_id FROM {$junction_table})",
                $id
            ));
            $junction_outstanding = (float) $wpdb->get_var($wpdb->prepare(
                "SELECT COALESCE(SUM(
                    CASE 
                        WHEN i.subtotal > 0 THEN i.balance_due * (jp.subtotal / i.subtotal)
                        ELSE 0
                    END
                ), 0)
                FROM {$junction_table} jp
                JOIN {$invoices_table} i ON jp.invoice_id = i.id
                WHERE jp.project_id = %d AND i.status NOT IN ('paid', 'cancelled')",
                $id
            ));
            $outstanding = $legacy_outstanding + $junction_outstanding;
        } else {
            // Fallback if junction table doesn't exist
            $total_billed = (float) $wpdb->get_var($wpdb->prepare(
                "SELECT COALESCE(SUM(total), 0) FROM {$invoices_table} WHERE project_id = %d AND status != 'cancelled'",
                $id
            ));
            $total_paid = (float) $wpdb->get_var($wpdb->prepare(
                "SELECT COALESCE(SUM(amount_paid), 0) FROM {$invoices_table} WHERE project_id = %d",
                $id
            ));
            $outstanding = (float) $wpdb->get_var($wpdb->prepare(
                "SELECT COALESCE(SUM(balance_due), 0) FROM {$invoices_table} WHERE project_id = %d AND status NOT IN ('paid', 'cancelled')",
                $id
            ));
        }
        
        return array(
            'total_hours' => $total_hours ?: 0,
            'billable_hours' => $billable_hours ?: 0,
            'total_billed' => $total_billed ?: 0,
            'total_paid' => $total_paid ?: 0,
            'outstanding' => $outstanding ?: 0
        );
    }

    /**
     * Count projects
     *
     * @param array $args Filter arguments
     * @return int
     */
    public static function count($args = array()) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_projects';
        $where = array('1=1');
        
        if (!empty($args['status'])) {
            if (is_array($args['status'])) {
                $placeholders = implode(',', array_fill(0, count($args['status']), '%s'));
                $where[] = $wpdb->prepare("status IN ($placeholders)", $args['status']);
            } else {
                $where[] = $wpdb->prepare('status = %s', $args['status']);
            }
        }
        
        if (!empty($args['client_id'])) {
            $where[] = $wpdb->prepare('client_id = %d', $args['client_id']);
        }
        
        if (!empty($args['billing_type'])) {
            $where[] = $wpdb->prepare('billing_type = %s', $args['billing_type']);
        }
        
        // Handle archived filtering
        if (isset($args['include_archived']) && $args['include_archived']) {
            // No filter — include everything
        } elseif (isset($args['archived']) && $args['archived']) {
            $where[] = 'archived = 1';
        } else {
            $where[] = '(archived = 0 OR archived IS NULL)';
        }
        
        $where_clause = implode(' AND ', $where);
        
        return (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table} WHERE {$where_clause}");
    }

    /**
     * Get status options
     *
     * @return array
     */
    public static function get_statuses() {
        return array(
            self::STATUS_PENDING => __('Pending', 'freelance-studio'),
            self::STATUS_ACTIVE => __('Active', 'freelance-studio'),
            self::STATUS_ON_HOLD => __('On Hold', 'freelance-studio'),
            self::STATUS_COMPLETED => __('Completed', 'freelance-studio'),
            self::STATUS_CANCELLED => __('Cancelled', 'freelance-studio'),
        );
    }

    /**
     * Get billing type options
     *
     * @return array
     */
    public static function get_billing_types() {
        return array(
            self::BILLING_FIXED => __('Fixed Price', 'freelance-studio'),
            self::BILLING_HOURLY => __('Hourly', 'freelance-studio'),
            self::BILLING_RETAINER => __('Retainer', 'freelance-studio'),
        );
    }

    /**
     * Get billing frequency options
     *
     * @return array
     */
    public static function get_billing_frequencies() {
        return array(
            'once' => __('Once', 'freelance-studio'),
            'weekly' => __('Weekly', 'freelance-studio'),
            'biweekly' => __('Bi-weekly', 'freelance-studio'),
            'monthly' => __('Monthly', 'freelance-studio'),
            'quarterly' => __('Quarterly', 'freelance-studio'),
            'milestone' => __('Per Milestone', 'freelance-studio'),
        );
    }

    /**
     * Get project services as array
     *
     * @param int $id Project ID
     * @return array
     */
    public static function get_services($id) {
        $project = self::get($id);
        if (!$project || empty($project->services)) {
            return array();
        }
        
        $services = json_decode($project->services ?? '', true);
        return is_array($services) ? $services : array();
    }

    /**
     * Update project services
     *
     * @param int   $id       Project ID
     * @param array $services Services array
     * @return bool
     */
    public static function update_services($id, $services) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_projects';
        
        // Calculate total
        $total_amount = 0;
        foreach ($services as $service) {
            $total_amount += floatval($service['amount'] ?? 0);
        }
        
        return $wpdb->update(
            $table,
            array(
                'services' => json_encode($services),
                'total_amount' => $total_amount
            ),
            array('id' => $id),
            array('%s', '%f'),
            array('%d')
        ) !== false;
    }

    /**
     * Update project notes only
     *
     * @param int    $id    Project ID
     * @param string $notes Notes content (HTML allowed)
     * @return bool
     */
    public static function update_notes($id, $notes) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_projects';
        
        return $wpdb->update(
            $table,
            array('notes' => wp_kses_post($notes)),
            array('id' => $id),
            array('%s'),
            array('%d')
        ) !== false;
    }

    /**
     * Update billing schedule
     *
     * @param int   $id   Project ID
     * @param array $data Billing data
     * @return bool
     */
    public static function update_billing_schedule($id, $data) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_projects';
        
        $update_data = array();
        
        if (isset($data['billing_frequency'])) {
            $update_data['billing_frequency'] = sanitize_text_field($data['billing_frequency']);
        }
        if (isset($data['start_date'])) {
            $update_data['start_date'] = !empty($data['start_date']) ? sanitize_text_field($data['start_date']) : null;
        }
        if (isset($data['due_date'])) {
            $update_data['due_date'] = !empty($data['due_date']) ? sanitize_text_field($data['due_date']) : null;
        }
        if (isset($data['first_invoice_date'])) {
            $update_data['first_invoice_date'] = !empty($data['first_invoice_date']) ? sanitize_text_field($data['first_invoice_date']) : null;
        }
        
        if (empty($update_data)) {
            return false;
        }
        
        return $wpdb->update($table, $update_data, array('id' => $id)) !== false;
    }

    /**
     * Update deposit settings
     *
     * @param int   $id   Project ID
     * @param array $data Deposit data
     * @return bool
     */
    public static function update_deposit($id, $data) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_projects';
        $project = self::get($id);
        
        if (!$project) {
            return false;
        }
        
        $deposit_type = isset($data['deposit_type']) ? sanitize_text_field($data['deposit_type']) : 'percentage';
        $deposit_percentage = isset($data['deposit_percentage']) ? floatval($data['deposit_percentage']) : 0;
        $deposit_amount = 0;
        
        if ($deposit_type === 'percentage' && $deposit_percentage > 0) {
            $base_amount = $project->total_amount > 0 ? $project->total_amount : $project->fixed_price;
            $deposit_amount = floatval($base_amount) * ($deposit_percentage / 100);
        } elseif ($deposit_type === 'fixed') {
            $deposit_amount = isset($data['deposit_amount']) ? floatval($data['deposit_amount']) : 0;
        }
        
        return $wpdb->update(
            $table,
            array(
                'deposit_type' => $deposit_type,
                'deposit_percentage' => $deposit_percentage,
                'deposit_amount' => $deposit_amount,
                'deposit_paid' => isset($data['deposit_paid']) ? intval($data['deposit_paid']) : $project->deposit_paid,
            ),
            array('id' => $id)
        ) !== false;
    }

    /**
     * Update a single date field (safe partial update)
     * Used by Google Calendar sync to avoid corrupting other project data
     *
     * @param int $id Project ID
     * @param string $field Field name (start_date or due_date)
     * @param string|null $value Date value (YYYY-MM-DD format or null)
     * @return bool
     */
    public static function update_date_field($id, $field, $value) {
        global $wpdb;
        
        // Only allow specific date fields
        $allowed_fields = array('start_date', 'due_date', 'first_invoice_date', 'completed_date');
        if (!in_array($field, $allowed_fields)) {
            error_log('FS_Project::update_date_field - Invalid field: ' . $field);
            return false;
        }
        
        $table = $wpdb->prefix . 'fs_projects';
        
        $sanitized_value = !empty($value) ? sanitize_text_field($value) : null;
        
        return $wpdb->update(
            $table,
            array($field => $sanitized_value),
            array('id' => $id),
            array($sanitized_value ? '%s' : null),
            array('%d')
        ) !== false;
    }

    /**
     * Get projects for dropdown/select
     *
     * @param int $client_id Optional client filter
     * @return array
     */
    public static function get_for_dropdown($client_id = null) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_projects';
        
        // Exclude archived projects from dropdowns
        $sql = "SELECT id, name, client_id FROM {$table} WHERE status IN ('pending', 'active') AND (archived = 0 OR archived IS NULL)";
        
        if ($client_id) {
            $sql .= $wpdb->prepare(' AND client_id = %d', $client_id);
        }
        
        $sql .= ' ORDER BY name ASC';
        
        return $wpdb->get_results($sql); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
    }

    /**
     * Get active projects count for dashboard
     *
     * @return int
     */
    public static function get_active_count() {
        return self::count(array('status' => array(self::STATUS_PENDING, self::STATUS_ACTIVE)));
    }

    /**
     * Get overdue projects
     *
     * @return array
     */
    public static function get_overdue() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_projects';
        $clients_table = $wpdb->prefix . 'fs_clients';
        $today = current_time('Y-m-d');
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT p.*, c.name as client_name 
             FROM {$table} p 
             LEFT JOIN {$clients_table} c ON p.client_id = c.id 
             WHERE p.due_date < %s 
               AND p.status IN ('pending', 'active')
               AND (p.archived = 0 OR p.archived IS NULL)
             ORDER BY p.due_date ASC",
            $today
        ));
    }

    /**
     * Get projects due soon
     *
     * @param int $days Number of days to look ahead
     * @return array
     */
    public static function get_due_soon($days = 7) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_projects';
        $clients_table = $wpdb->prefix . 'fs_clients';
        $today = current_time('Y-m-d');
        $future = gmdate('Y-m-d', strtotime("+{$days} days"));
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT p.*, c.name as client_name 
             FROM {$table} p 
             LEFT JOIN {$clients_table} c ON p.client_id = c.id 
             WHERE p.due_date BETWEEN %s AND %s 
               AND p.status IN ('pending', 'active')
               AND (p.archived = 0 OR p.archived IS NULL)
             ORDER BY p.due_date ASC",
            $today,
            $future
        ));
    }

    /**
     * Get retainer service from project (v3.9.0)
     * Returns the first service with pricing_type = 'retainer', or null if none
     *
     * @param object|int $project Project object or ID
     * @return array|null Retainer service data or null
     */
    public static function get_retainer_service($project) {
        // If passed an ID, get the project
        if (is_numeric($project)) {
            $project = self::get($project);
        }
        
        if (!$project || empty($project->services)) {
            return null;
        }
        
        $services = json_decode($project->services ?? '', true);
        if (!is_array($services)) {
            return null;
        }
        
        foreach ($services as $service) {
            if (isset($service['pricing_type']) && $service['pricing_type'] === 'retainer') {
                // Ensure all retainer fields have defaults
                return array_merge(array(
                    'name' => '',
                    'description' => '',
                    'pricing_type' => 'retainer',
                    'retainer_amount' => 0,
                    'retainer_hours' => 0,
                    'retainer_rollover' => true,
                    'retainer_overage_rate' => 0,
                ), $service);
            }
        }
        
        return null;
    }

    /**
     * Check if project has a retainer service (v3.9.0)
     *
     * @param object|int $project Project object or ID
     * @return bool
     */
    public static function has_retainer_service($project) {
        return self::get_retainer_service($project) !== null;
    }

    /**
     * Get the first capped hourly service from a project (v7.9.0)
     *
     * @param object|int $project Project object or ID
     * @return array|null Service data array or null
     */
    public static function get_capped_hourly_service($project) {
        if (is_numeric($project)) {
            $project = self::get($project);
        }
        
        if (!$project || empty($project->services)) {
            return null;
        }
        
        $services = json_decode($project->services ?? '', true);
        if (!is_array($services)) {
            return null;
        }
        
        foreach ($services as $service) {
            if (isset($service['pricing_type']) && $service['pricing_type'] === 'hourly'
                && isset($service['hourly_source']) && $service['hourly_source'] === 'tracked'
                && !empty($service['hour_cap']) && floatval($service['hour_cap']) > 0) {
                return array_merge(array(
                    'name' => '',
                    'rate' => 0,
                    'hour_cap' => 0,
                    'hour_cap_overage_rate' => 0,
                    'hour_cap_notify_client' => 0,
                    'hour_cap_notify_freelancer' => 0,
                    'hour_cap_warn_threshold' => 0,
                    'activity' => '',
                ), $service);
            }
        }
        
        return null;
    }

    /**
     * Check if project is a retainer project (v3.9.0)
     * Checks both legacy is_retainer flag and new service-based approach
     *
     * @param object|int $project Project object or ID
     * @return bool
     */
    public static function is_retainer_project($project) {
        // If passed an ID, get the project
        if (is_numeric($project)) {
            $project = self::get($project);
        }
        
        if (!$project) {
            return false;
        }
        
        // Check new service-based approach first
        if (self::has_retainer_service($project)) {
            return true;
        }
        
        // Fall back to legacy is_retainer flag for backwards compatibility
        return !empty($project->is_retainer);
    }
    
    /**
     * Archive a project (v4.0.10)
     *
     * @param int $project_id Project ID
     * @return bool Success
     */
    public static function archive($project_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_projects';
        
        $result = $wpdb->update(
            $table,
            array('archived' => 1),
            array('id' => $project_id),
            array('%d'),
            array('%d')
        );

        // Archive all non-archived tasks belonging to this project
        if ( $result !== false ) {
            $tasks_table = $wpdb->prefix . 'fs_tasks';
            $wpdb->query( $wpdb->prepare(
                "UPDATE {$tasks_table}
                 SET previous_status = status, status = 'archived'
                 WHERE project_id = %d AND status != 'archived'",
                $project_id
            ) );
        }
        
        return $result !== false;
    }
    
    /**
     * Unarchive a project (v4.0.10)
     *
     * @param int $project_id Project ID
     * @return bool Success
     */
    public static function unarchive($project_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_projects';
        
        $result = $wpdb->update(
            $table,
            array('archived' => 0),
            array('id' => $project_id),
            array('%d'),
            array('%d')
        );
        
        return $result !== false;
    }
    
    /**
     * Check if a project is archived (v4.0.10)
     *
     * @param object|int $project Project object or ID
     * @return bool
     */
    public static function is_archived($project) {
        if (is_numeric($project)) {
            $project = self::get($project);
        }
        
        return $project && !empty($project->archived);
    }
    
    /**
     * Get count of projects by archived status (v4.0.10)
     *
     * @param bool $archived Whether to count archived (true) or active (false)
     * @return int
     */
    public static function get_count($archived = false) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_projects';
        
        if ($archived) {
            return (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table} WHERE archived = 1");
        } else {
            return (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table} WHERE archived = 0 OR archived IS NULL");
        }
    }
    
    /**
     * Get count of projects by status (v4.0.10)
     *
     * @param string $status Status to count
     * @param bool $include_archived Whether to include archived projects
     * @return int
     */
    public static function get_status_count($status, $include_archived = false) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_projects';
        
        $sql = $wpdb->prepare("SELECT COUNT(*) FROM {$table} WHERE status = %s", $status);
        
        if (!$include_archived) {
            $sql .= " AND (archived = 0 OR archived IS NULL)";
        }
        
        return (int) $wpdb->get_var($sql); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
    }
}
