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

class FS_Invoice {

    /**
     * Invoice status constants
     */
    const STATUS_DRAFT = 'draft';
    const STATUS_SENT = 'sent';
    const STATUS_VIEWED = 'viewed';
    const STATUS_PAID = 'paid';
    const STATUS_PARTIAL = 'partial';
    const STATUS_OVERDUE = 'overdue';
    const STATUS_CANCELLED = 'cancelled';

    /**
     * Invoice source constants (v3.2.0)
     */
    const SOURCE_CUSTOM = 'custom';
    const SOURCE_TIME_ENTRIES = 'time_entries';
    const SOURCE_PROJECT_SERVICES = 'project_services';
    const SOURCE_COMBINED = 'combined';

    /**
     * Time entry grouping constants (v3.2.0)
     */
    const GROUP_BY_ENTRY = 'entry';
    const GROUP_BY_TASK = 'task';
    const GROUP_BY_ACTIVITY = 'activity';
    const GROUP_BY_PROJECT = 'project';
    const GROUP_BY_DATE = 'date';

    /**
     * Create a new invoice
     *
     * @param array $data Invoice data
     * @return int|false Insert ID or false on failure
     */
    public static function create($data) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_invoices';
        
        // Calculate totals
        $subtotal = floatval($data['subtotal']);
        $tax_rate = isset($data['tax_rate']) ? floatval($data['tax_rate']) : 0;
        $tax_amount = $subtotal * ($tax_rate / 100);
        
        // Handle discount
        $discount_amount = 0;
        if (!empty($data['discount_value'])) {
            if ($data['discount_type'] === 'percentage') {
                $discount_amount = $subtotal * (floatval($data['discount_value']) / 100);
            } else {
                $discount_amount = floatval($data['discount_value']);
            }
        }
        
        $total = $subtotal + $tax_amount - $discount_amount;
        $balance_due = $total - (isset($data['amount_paid']) ? floatval($data['amount_paid']) : 0);
        
        // Generate view token
        $view_token = wp_generate_password(32, false);
        
        // Determine status - auto-set to paid if paid_date is provided
        $status = isset($data['status']) ? sanitize_text_field($data['status']) : self::STATUS_DRAFT;
        $amount_paid = isset($data['amount_paid']) ? floatval($data['amount_paid']) : 0;
        
        if (!empty($data['paid_date'])) {
            $status = self::STATUS_PAID;
            // If no amount_paid specified but status is paid, assume full payment
            if ($amount_paid <= 0) {
                $amount_paid = $total;
                $balance_due = 0;
            }
        }
        
        // Determine paid_at timestamp
        $paid_at = null;
        if ($status === self::STATUS_PAID) {
            // Use the user-provided paid_date if available, otherwise use current time
            if (!empty($data['paid_date'])) {
                $paid_at = sanitize_text_field($data['paid_date']) . ' 00:00:00';
            } else {
                $paid_at = current_time('mysql');
            }
        }
        
        $insert_data = array(
            'client_id' => intval($data['client_id']),
            'project_id' => !empty($data['project_id']) ? intval($data['project_id']) : null,
            'contract_id' => !empty($data['contract_id']) ? intval($data['contract_id']) : null,
            'proposal_id' => !empty($data['proposal_id']) ? intval($data['proposal_id']) : null,
            'invoice_number' => sanitize_text_field($data['invoice_number']),
            'invoice_date' => !empty($data['invoice_date']) ? sanitize_text_field($data['invoice_date']) : null,
            'due_date' => sanitize_text_field($data['due_date']),
            'paid_date' => !empty($data['paid_date']) ? sanitize_text_field($data['paid_date']) : null,
            'status' => $status,
            'subtotal' => $subtotal,
            'tax_rate' => $tax_rate,
            'tax_amount' => $tax_amount,
            'discount_type' => isset($data['discount_type']) ? sanitize_text_field($data['discount_type']) : null,
            'discount_value' => isset($data['discount_value']) ? floatval($data['discount_value']) : 0,
            'discount_amount' => $discount_amount,
            'total' => $total,
            'amount_paid' => $amount_paid,
            'balance_due' => $balance_due,
            'notes' => isset($data['notes']) ? sanitize_textarea_field($data['notes']) : '',
            'items' => isset($data['items']) ? wp_json_encode($data['items']) : '[]',
            'expenses' => isset($data['expenses']) ? wp_json_encode($data['expenses']) : '[]',
            'is_deposit' => isset($data['is_deposit']) ? intval($data['is_deposit']) : 0,
            'payment_link' => !empty($data['payment_link']) ? esc_url_raw($data['payment_link']) : null,
            'view_token' => $view_token,
        );
        
        if ($paid_at) {
            $insert_data['paid_at'] = $paid_at;
        }
        
        // Track if project_id should be NULL (wpdb->insert converts null to empty string/0)
        $nullable_fks = array(
            'project_id' => $insert_data['project_id'],
            'contract_id' => $insert_data['contract_id'],
            'proposal_id' => $insert_data['proposal_id'],
        );
        
        $result = $wpdb->insert($table, $insert_data);
        
        if ($result === false) {
            error_log('FS_Invoice::create failed: ' . $wpdb->last_error);
            return false;
        }
        
        $new_id = $wpdb->insert_id;
        
        // Fix NULL foreign keys that wpdb->insert may have converted to 0
        FS_DB_Helper::fix_null_fields($table, $new_id, $nullable_fks);
        
        return $new_id;
    }

    /**
     * Get all invoices
     *
     * @param array $args Query arguments
     * @return array
     */
    public static function get_all($args = array()) {
        global $wpdb;
        
        $defaults = array(
            'client_id' => null,
            'project_id' => null,
            'status' => null,
            'orderby' => 'invoice_date',
            'order' => 'DESC',
            'limit' => null,
            'offset' => 0
        );
        
        $args = wp_parse_args($args, $defaults);
        
        $invoices_table = $wpdb->prefix . 'fs_invoices';
        $clients_table = $wpdb->prefix . 'fs_clients';
        $projects_table = $wpdb->prefix . 'fs_projects';
        
        $where = array('1=1');
        
        if ($args['client_id']) {
            $where[] = $wpdb->prepare('i.client_id = %d', $args['client_id']);
        }
        
        if ($args['project_id']) {
            $where[] = $wpdb->prepare('i.project_id = %d', $args['project_id']);
        }
        
        if ($args['status']) {
            if (is_array($args['status'])) {
                $placeholders = implode(',', array_fill(0, count($args['status']), '%s'));
                $where[] = $wpdb->prepare("i.status IN ($placeholders)", $args['status']);
            } else {
                $where[] = $wpdb->prepare('i.status = %s', $args['status']);
            }
        }
        
        $where_clause = implode(' AND ', $where);
        
        $allowed_orderby = array('invoice_date', 'due_date', 'total', 'status', 'invoice_number');
        $orderby = in_array($args['orderby'], $allowed_orderby) ? $args['orderby'] : 'invoice_date';
        $order = strtoupper($args['order']) === 'ASC' ? 'ASC' : 'DESC';
        
        $sql = "SELECT i.*, c.name as client_name, c.company_name as client_company, p.name as project_name
                FROM {$invoices_table} i 
                LEFT JOIN {$clients_table} c ON i.client_id = c.id 
                LEFT JOIN {$projects_table} p ON i.project_id = p.id
                WHERE {$where_clause} 
                ORDER BY i.{$orderby} {$order}";
        
        if ($args['limit']) {
            $sql .= $wpdb->prepare(' LIMIT %d OFFSET %d', $args['limit'], $args['offset']);
        }
        
        $invoices = $wpdb->get_results($sql); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
        
        // Decode JSON fields
        foreach ($invoices as $invoice) {
            $invoice->items = json_decode($invoice->items ?? '', true) ?: array();
            $invoice->expenses = json_decode($invoice->expenses ?? '', true) ?: array();
        }
        
        return $invoices;
    }

    /**
     * Get a single invoice by ID
     *
     * @param int $id Invoice ID
     * @return object|null
     */
    public static function get($id) {
        global $wpdb;
        
        $invoices_table = $wpdb->prefix . 'fs_invoices';
        $clients_table = $wpdb->prefix . 'fs_clients';
        $projects_table = $wpdb->prefix . 'fs_projects';
        
        $invoice = $wpdb->get_row($wpdb->prepare(
            "SELECT i.*, c.name as client_name, c.company_name as client_company, c.email as client_email,
                    c.address as client_address, c.city as client_city, c.state as client_state, 
                    c.zip as client_zip, c.country as client_country, c.phone as client_phone,
                    p.name as project_name
             FROM {$invoices_table} i 
             LEFT JOIN {$clients_table} c ON i.client_id = c.id 
             LEFT JOIN {$projects_table} p ON i.project_id = p.id
             WHERE i.id = %d",
            $id
        ));
        
        if ($invoice) {
            $invoice->items = json_decode($invoice->items ?? '', true) ?: array();
            $invoice->expenses = json_decode($invoice->expenses ?? '', true) ?: array();
        }
        
        return $invoice;
    }

    /**
     * Get invoice by view token
     *
     * @param string $token View token
     * @return object|null
     */
    public static function get_by_token($token) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_invoices';
        
        $invoice = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} WHERE view_token = %s",
            $token
        ));
        
        if ($invoice) {
            $invoice->items = json_decode($invoice->items ?? '', true) ?: array();
            $invoice->expenses = json_decode($invoice->expenses ?? '', true) ?: array();
        }
        
        return $invoice;
    }

    /**
     * Get invoices 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));
    }

    /**
     * Get invoices by project
     *
     * @param int $project_id Project ID
     * @return array
     */
    public static function get_by_project($project_id) {
        return self::get_all(array('project_id' => $project_id));
    }

    /**
     * Update an invoice
     *
     * @param int   $id   Invoice ID
     * @param array $data Invoice data
     * @return int|false
     */
    public static function update($id, $data) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_invoices';
        
        // Recalculate totals
        $subtotal = floatval($data['subtotal']);
        $tax_rate = isset($data['tax_rate']) ? floatval($data['tax_rate']) : 0;
        $tax_amount = $subtotal * ($tax_rate / 100);
        
        // Handle discount
        $discount_amount = 0;
        if (!empty($data['discount_value'])) {
            if (isset($data['discount_type']) && $data['discount_type'] === 'percentage') {
                $discount_amount = $subtotal * (floatval($data['discount_value']) / 100);
            } else {
                $discount_amount = floatval($data['discount_value']);
            }
        }
        
        $total = $subtotal + $tax_amount - $discount_amount;
        $amount_paid = isset($data['amount_paid']) ? floatval($data['amount_paid']) : 0;
        $balance_due = $total - $amount_paid;
        
        // Auto-update status based on payment
        $status = isset($data['status']) ? sanitize_text_field($data['status']) : self::STATUS_DRAFT;
        $user_chose_status = $status; // Remember what the user explicitly chose
        
        // Only auto-adjust status if user hasn't explicitly chosen a non-paid status
        // If the user set a paid_date but also chose e.g. "draft" or "pending", respect their choice
        if (!empty($data['paid_date'])) {
            if ($user_chose_status === self::STATUS_PAID || $user_chose_status === self::STATUS_DRAFT) {
                // User either chose Paid explicitly or left as Draft — auto-set to Paid
                if ($amount_paid <= 0) {
                    $amount_paid = $total;
                    $balance_due = 0;
                }
                $status = self::STATUS_PAID;
            }
            // If user explicitly chose another status (sent, pending, etc.) while clearing payment
            // data is still present, respect their choice
        } elseif ($balance_due <= 0 && $amount_paid > 0) {
            $status = self::STATUS_PAID;
        } elseif ($amount_paid > 0 && $balance_due > 0) {
            $status = self::STATUS_PARTIAL;
        }
        
        $update_data = array(
            'client_id' => intval($data['client_id']),
            'invoice_number' => sanitize_text_field($data['invoice_number']),
            'invoice_date' => !empty($data['invoice_date']) ? sanitize_text_field($data['invoice_date']) : null,
            'due_date' => sanitize_text_field($data['due_date']),
            'paid_date' => !empty($data['paid_date']) ? sanitize_text_field($data['paid_date']) : null,
            'status' => $status,
            'subtotal' => $subtotal,
            'tax_rate' => $tax_rate,
            'tax_amount' => $tax_amount,
            'discount_type' => isset($data['discount_type']) ? sanitize_text_field($data['discount_type']) : null,
            'discount_value' => isset($data['discount_value']) ? floatval($data['discount_value']) : 0,
            'discount_amount' => $discount_amount,
            'total' => $total,
            'amount_paid' => $amount_paid,
            'balance_due' => $balance_due,
            'notes' => isset($data['notes']) ? sanitize_textarea_field($data['notes']) : '',
            'items' => isset($data['items']) ? wp_json_encode($data['items']) : '[]',
            'expenses' => isset($data['expenses']) ? wp_json_encode($data['expenses']) : '[]',
        );
        
        // Handle payment link (can be set or cleared per invoice)
        if (array_key_exists('payment_link', $data)) {
            $update_data['payment_link'] = !empty($data['payment_link']) ? esc_url_raw($data['payment_link']) : null;
        }
        
        // Handle optional foreign keys
        // Note: wpdb->update() converts PHP null to empty string '' which becomes 0
        // for bigint columns. We track nullable FKs and fix them after the main update.
        $nullable_fks = array();
        if (array_key_exists('project_id', $data)) {
            if (!empty($data['project_id'])) {
                $update_data['project_id'] = intval($data['project_id']);
            } else {
                $nullable_fks['project_id'] = null;
            }
        }
        
        // Set paid_at timestamp if status is paid
        if ($status === self::STATUS_PAID) {
            $existing = self::get($id);
            // Use user-provided paid_date for paid_at, or current time as fallback
            if (!empty($data['paid_date'])) {
                $update_data['paid_at'] = sanitize_text_field($data['paid_date']) . ' 00:00:00';
            } elseif ($existing && !$existing->paid_at) {
                $update_data['paid_at'] = current_time('mysql');
            }
        } else {
            // If status is no longer paid, clear paid_at
            $nullable_fks['paid_at'] = null;
        }
        
        $result = $wpdb->update(
            $table,
            $update_data,
            array('id' => $id),
            null,
            array('%d')
        );
        
        // Fix NULL fields that wpdb->update can't handle properly
        if (!empty($nullable_fks)) {
            FS_DB_Helper::fix_null_fields($table, $id, $nullable_fks);
        }
        
        if ($result === false) {
            error_log('FS_Invoice::update failed: ' . $wpdb->last_error);
        }
        
        return $result;
    }

    /**
     * Delete an invoice
     *
     * @param int $id Invoice ID
     * @return int|false
     */
    public static function delete($id) {
        global $wpdb;
        
        // First, reset any time entries linked to this invoice
        FS_Time_Entry::mark_uninvoiced_by_invoice($id);
        
        // Delete invoice items (table may not exist in older installations)
        $items_table = $wpdb->prefix . 'fs_invoice_items';
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$items_table}'");
        if ($table_exists) {
            $wpdb->delete($items_table, array('invoice_id' => $id), array('%d'));
        }
        
        // Delete the invoice
        $table = $wpdb->prefix . 'fs_invoices';
        
        return $wpdb->delete($table, array('id' => $id), array('%d'));
    }

    /**
     * Update invoice status only
     *
     * @param int    $id     Invoice ID
     * @param string $status New status
     * @return int|false
     */
    public static function update_status($id, $status) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_invoices';
        
        $update_data = array('status' => $status);
        
        $existing = self::get($id);
        
        // Set paid_at timestamp if status changed to paid
        if ($status === self::STATUS_PAID) {
            if ($existing && $existing->status !== self::STATUS_PAID) {
                $update_data['paid_at'] = current_time('mysql');
                $update_data['paid_date'] = current_time('Y-m-d');
                // Also update amount_paid and balance_due
                $update_data['amount_paid'] = $existing->total;
                $update_data['balance_due'] = 0;
            }
        } elseif ($existing && $existing->status === self::STATUS_PAID) {
            // Changing AWAY from paid — clear payment fields
            $update_data['paid_at'] = null;
            $update_data['paid_date'] = null;
            $update_data['amount_paid'] = 0;
            $update_data['balance_due'] = $existing->total;
        }
        
        // Set sent_at timestamp if marking as sent
        if ($status === self::STATUS_SENT) {
            if ($existing && !$existing->sent_at) {
                $update_data['sent_at'] = current_time('mysql');
            }
        }
        
        $result = $wpdb->update(
            $table,
            $update_data,
            array('id' => $id),
            null,
            array('%d')
        );
        
        // If this is a deposit invoice being marked as paid, update the project
        if ($status === self::STATUS_PAID) {
            $invoice = self::get($id);
            if ($invoice && $invoice->is_deposit && $invoice->project_id) {
                $projects_table = $wpdb->prefix . 'fs_projects';
                $wpdb->update(
                    $projects_table,
                    array('deposit_paid' => 1),
                    array('id' => $invoice->project_id),
                    array('%d'),
                    array('%d')
                );
            }
        }
        
        return $result;
    }

    /**
     * Generate next invoice number
     *
     * @return string
     */
    public static function generate_invoice_number() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_invoices';
        $prefix = get_option('fs_invoice_prefix', 'INV');
        $current_year = gmdate('Y');
        
        // Find the highest invoice number for the current year
        $pattern = $prefix . '-' . $current_year . '-%';
        $last_number = $wpdb->get_var($wpdb->prepare(
            "SELECT invoice_number FROM {$table} WHERE invoice_number LIKE %s ORDER BY id DESC LIMIT 1",
            $pattern
        ));
        
        if ($last_number) {
            // Extract the sequence number
            $parts = explode('-', $last_number);
            $sequence = intval(end($parts)) + 1;
        } else {
            $sequence = 1;
        }
        
        return $prefix . '-' . $current_year . '-' . str_pad($sequence, 4, '0', STR_PAD_LEFT);
    }

    /**
     * Mark invoice as sent
     *
     * @param int $id Invoice ID
     * @return bool
     */
    public static function mark_sent($id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_invoices';
        
        return $wpdb->update(
            $table,
            array(
                'status' => self::STATUS_SENT,
                'sent_at' => current_time('mysql')
            ),
            array('id' => $id),
            array('%s', '%s'),
            array('%d')
        ) !== false;
    }

    /**
     * Mark invoice as viewed
     *
     * @param int $id Invoice ID
     * @return bool
     */
    public static function mark_viewed($id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_invoices';
        $invoice = self::get($id);
        
        // Only update if not already viewed and not paid
        if ($invoice && !$invoice->viewed_at && $invoice->status !== self::STATUS_PAID) {
            return $wpdb->update(
                $table,
                array(
                    'viewed_at' => current_time('mysql'),
                    'status' => self::STATUS_VIEWED
                ),
                array('id' => $id),
                array('%s', '%s'),
                array('%d')
            ) !== false;
        }
        
        return true;
    }

    /**
     * Record a payment
     *
     * @param int   $id     Invoice ID
     * @param float $amount Payment amount
     * @return bool
     */
    public static function record_payment($id, $amount) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_invoices';
        $invoice = self::get($id);
        
        if (!$invoice) {
            return false;
        }
        
        $new_amount_paid = floatval($invoice->amount_paid) + floatval($amount);
        $new_balance = floatval($invoice->total) - $new_amount_paid;
        
        $status = self::STATUS_PARTIAL;
        $paid_at = null;
        $paid_date = null;
        
        if ($new_balance <= 0) {
            $status = self::STATUS_PAID;
            $paid_at = current_time('mysql');
            $paid_date = current_time('Y-m-d');
            $new_balance = 0;
        }
        
        $update = array(
            'amount_paid' => $new_amount_paid,
            'balance_due' => $new_balance,
            'status' => $status,
        );
        
        if ($paid_at) {
            $update['paid_at'] = $paid_at;
            $update['paid_date'] = $paid_date;
        }
        
        return $wpdb->update(
            $table,
            $update,
            array('id' => $id)
        ) !== false;
    }

    /**
     * Count invoices
     *
     * @param array $args Filter arguments
     * @return int
     */
    public static function count($args = array()) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_invoices';
        $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['project_id'])) {
            $where[] = $wpdb->prepare('project_id = %d', $args['project_id']);
        }
        
        $where_clause = implode(' AND ', $where);
        
        return (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table} WHERE {$where_clause}");
    }

    /**
     * Get total revenue (paid invoices)
     *
     * @param string $start_date Optional start date
     * @param string $end_date   Optional end date
     * @return float
     */
    public static function get_total_revenue($start_date = null, $end_date = null) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_invoices';
        $where = array("status = 'paid'");
        
        if ($start_date) {
            $where[] = $wpdb->prepare('paid_at >= %s', $start_date);
        }
        if ($end_date) {
            // Add time component to ensure full day is included
            $end_date_with_time = $end_date . ' 23:59:59';
            $where[] = $wpdb->prepare('paid_at <= %s', $end_date_with_time);
        }
        
        $where_clause = implode(' AND ', $where);
        
        return (float) $wpdb->get_var("SELECT SUM(total) FROM {$table} WHERE {$where_clause}");
    }

    /**
     * Get outstanding balance (unpaid invoices)
     *
     * @return float
     */
    public static function get_outstanding_total() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_invoices';
        
        return (float) $wpdb->get_var(
            "SELECT SUM(balance_due) FROM {$table} WHERE status NOT IN ('paid', 'cancelled')"
        );
    }

    /**
     * Get overdue invoices
     *
     * @return array
     */
    public static function get_overdue() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_invoices';
        $clients_table = $wpdb->prefix . 'fs_clients';
        $today = current_time('Y-m-d');
        
        $invoices = $wpdb->get_results($wpdb->prepare(
            "SELECT i.*, c.name as client_name, c.company_name as client_company, c.email as client_email
             FROM {$table} i 
             LEFT JOIN {$clients_table} c ON i.client_id = c.id 
             WHERE i.due_date < %s 
               AND i.status NOT IN ('paid', 'cancelled') 
             ORDER BY i.due_date ASC",
            $today
        ));
        
        foreach ($invoices as $invoice) {
            $invoice->items = json_decode($invoice->items ?? '', true) ?: array();
        }
        
        return $invoices;
    }

    /**
     * Get status options
     *
     * @return array
     */
    public static function get_statuses() {
        return array(
            self::STATUS_DRAFT => __('Draft', 'freelance-studio'),
            self::STATUS_SENT => __('Sent', 'freelance-studio'),
            self::STATUS_VIEWED => __('Viewed', 'freelance-studio'),
            self::STATUS_PARTIAL => __('Partially Paid', 'freelance-studio'),
            self::STATUS_PAID => __('Paid', 'freelance-studio'),
            self::STATUS_OVERDUE => __('Overdue', 'freelance-studio'),
            self::STATUS_CANCELLED => __('Cancelled', 'freelance-studio'),
        );
    }

    /**
     * Update overdue statuses (called by cron)
     *
     * @return int Number of invoices updated
     */
    public static function update_overdue_statuses() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_invoices';
        $today = current_time('Y-m-d');
        
        return $wpdb->query($wpdb->prepare(
            "UPDATE {$table} 
             SET status = %s 
             WHERE due_date < %s 
               AND status IN ('sent', 'viewed', 'partial')",
            self::STATUS_OVERDUE,
            $today
        ));
    }

    /**
     * Create invoice from contract
     *
     * @param object $contract Contract object
     * @param string $invoice_type 'deposit', 'full', or 'balance'
     * @param array $options Additional options (invoice_date, due_date, notes)
     * @return int|false Invoice ID or false on failure
     */
    public static function create_from_contract($contract, $invoice_type = 'full', $options = array()) {
        $services = is_string($contract->services) ? json_decode($contract->services ?? '', true) : $contract->services;
        if (!is_array($services)) {
            $services = array();
        }
        
        // Calculate amounts based on invoice type
        $items = array();
        $subtotal = 0;
        
        if ($invoice_type === 'deposit') {
            // Create deposit invoice
            $deposit_amount = FS_Contract::calculate_deposit($contract);
            if ($deposit_amount <= 0) {
                return false;
            }
            
            $items[] = array(
                /* translators: 1. deposit for value */
                'description' => sprintf(__('Deposit for: %s', 'freelance-studio'), $contract->title),
                'quantity' => 1,
                'rate' => $deposit_amount,
                'amount' => $deposit_amount,
            );
            $subtotal = $deposit_amount;
            
        } elseif ($invoice_type === 'balance') {
            // Create balance invoice (total minus deposit)
            $deposit_amount = FS_Contract::calculate_deposit($contract);
            $balance = floatval($contract->total_amount) - $deposit_amount;
            
            if ($balance <= 0) {
                return false;
            }
            
            // Add services as line items
            foreach ($services as $service) {
                $items[] = array(
                    'description' => $service['name'] . (!empty($service['description']) ? ' - ' . $service['description'] : ''),
                    'quantity' => floatval($service['quantity'] ?? 1),
                    'rate' => floatval($service['rate'] ?? $service['amount'] ?? 0),
                    'amount' => floatval($service['amount'] ?? 0),
                );
            }
            
            // Add line item for deposit already paid
            if ($deposit_amount > 0) {
                $items[] = array(
                    'description' => __('Less: Deposit already paid', 'freelance-studio'),
                    'quantity' => 1,
                    'rate' => -$deposit_amount,
                    'amount' => -$deposit_amount,
                );
            }
            
            $subtotal = $balance;
            
        } else {
            // Full invoice - include all services
            foreach ($services as $service) {
                $items[] = array(
                    'description' => $service['name'] . (!empty($service['description']) ? ' - ' . $service['description'] : ''),
                    'quantity' => floatval($service['quantity'] ?? 1),
                    'rate' => floatval($service['rate'] ?? $service['amount'] ?? 0),
                    'amount' => floatval($service['amount'] ?? 0),
                );
                $subtotal += floatval($service['amount'] ?? 0);
            }
        }
        
        // Set dates
        $invoice_date = isset($options['invoice_date']) ? $options['invoice_date'] : current_time('Y-m-d');
        $payment_terms_days = intval(get_option('fs_payment_terms', 30));
        $due_date = isset($options['due_date']) ? $options['due_date'] : gmdate('Y-m-d', strtotime($invoice_date . ' + ' . $payment_terms_days . ' days'));
        
        // Get default sales tax rate if auto-apply is enabled
        $tax_rate = 0;
        if (get_option('fs_auto_apply_sales_tax', '0') === '1') {
            $tax_rate = floatval(get_option('fs_default_sales_tax_rate', 0));
        }
        
        // Create invoice data
        $invoice_data = array(
            'client_id' => $contract->client_id,
            'project_id' => $contract->project_id,
            'contract_id' => $contract->id,
            'invoice_number' => self::generate_invoice_number(),
            'invoice_date' => $invoice_date,
            'due_date' => $due_date,
            'status' => self::STATUS_DRAFT,
            'subtotal' => $subtotal,
            'tax_rate' => $tax_rate,
            'items' => $items,
            'is_deposit' => ($invoice_type === 'deposit') ? 1 : 0,
            'notes' => isset($options['notes']) ? $options['notes'] : '',
        );
        
        return self::create($invoice_data);
    }

    /**
     * Get invoices linked to a contract
     *
     * @param int $contract_id Contract ID
     * @return array Array of invoice objects
     */
    public static function get_by_contract($contract_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_invoices';
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$table} WHERE contract_id = %d ORDER BY invoice_date DESC",
            $contract_id
        ));
    }

    /**
     * Create an invoice from time entries
     *
     * @param int $project_id Project ID
     * @param array $entry_ids Optional specific entry IDs (if empty, uses all unbilled)
     * @param array $options Additional options (notes, due_date, etc.)
     * @return int|false Invoice ID or false on failure
     */
    public static function create_from_time_entries($project_id, $entry_ids = array(), $options = array()) {
        // Get the project
        $project = FS_Project::get($project_id);
        if (!$project) {
            return false;
        }

        // Get time entries
        if (empty($entry_ids)) {
            $entries = FS_Time_Entry::get_unbilled($project_id);
        } else {
            $entries = array();
            foreach ($entry_ids as $id) {
                $entry = FS_Time_Entry::get($id);
                if ($entry && $entry->project_id == $project_id && !$entry->invoiced) {
                    $entries[] = $entry;
                }
            }
        }

        if (empty($entries)) {
            return false;
        }

        // Calculate totals and build line items
        $items = array();
        $subtotal = 0;
        $total_hours = 0;

        // Group entries by activity type for cleaner line items
        $grouped = array();
        foreach ($entries as $entry) {
            $activity = $entry->activity ?: 'other';
            if (!isset($grouped[$activity])) {
                $grouped[$activity] = array(
                    'duration' => 0,
                    'rate' => $entry->hourly_rate,
                    'entries' => array(),
                );
            }
            $grouped[$activity]['duration'] += $entry->duration;
            $grouped[$activity]['entries'][] = $entry->id;
        }

        $activity_types = FS_Time_Entry::get_activity_types();
        
        foreach ($grouped as $activity => $data) {
            $hours = FS_Time_Entry::get_rounded_hours($data['duration']);
            $rate = $data['rate'] ?: get_option('fs_default_hourly_rate', 75);
            $amount = $hours * $rate;
            
            $items[] = array(
                'description' => ($activity_types[$activity] ?? ucfirst($activity)) . ' - ' . number_format($hours, 2) . ' hours',
                'quantity' => number_format($hours, 2),
                'rate' => $rate,
                'amount' => $amount,
            );
            
            $subtotal += $amount;
            $total_hours += $hours;
        }

        // Generate invoice number
        $invoice_number = self::generate_invoice_number();

        // Set due date
        $payment_terms = get_option('fs_default_payment_terms', 30);
        $due_date = gmdate('Y-m-d', strtotime("+{$payment_terms} days"));
        if (!empty($options['due_date'])) {
            $due_date = $options['due_date'];
        }
        
        // Get default sales tax rate if auto-apply is enabled
        $tax_rate = 0;
        if (get_option('fs_auto_apply_sales_tax', '0') === '1') {
            $tax_rate = floatval(get_option('fs_default_sales_tax_rate', 0));
        }

        // Create the invoice
        $invoice_data = array(
            'client_id' => $project->client_id,
            'project_id' => $project_id,
            'invoice_number' => $invoice_number,
            'invoice_date' => gmdate('Y-m-d'),
            'due_date' => $due_date,
            'status' => self::STATUS_DRAFT,
            'items' => $items,
            'subtotal' => $subtotal,
            'tax_rate' => $tax_rate,
            'notes' => isset($options['notes']) ? $options['notes'] : sprintf(
                /* translators: 1. number of hours, 2. project name */
                __('Invoice for %1$s hours of work on %2$s', 'freelance-studio'),
                number_format($total_hours, 2),
                $project->name
            ),
        );

        $invoice_id = self::create($invoice_data);

        if ($invoice_id) {
            // Mark time entries as invoiced
            $all_entry_ids = array();
            foreach ($grouped as $data) {
                $all_entry_ids = array_merge($all_entry_ids, $data['entries']);
            }
            FS_Time_Entry::mark_invoiced($all_entry_ids, $invoice_id);
        }

        return $invoice_id;
    }

    /**
     * Create invoice from the wizard (v3.2.0)
     *
     * @param array $data Wizard data
     * @return int|false Invoice ID or false on failure
     */
    public static function create_from_wizard($data) {
        global $wpdb;
        
        $items = isset($data['items']) ? $data['items'] : array();
        $project_ids = isset($data['project_ids']) ? $data['project_ids'] : array();
        $invoice_source = isset($data['invoice_source']) ? $data['invoice_source'] : self::SOURCE_CUSTOM;
        $time_grouping = isset($data['time_grouping']) ? $data['time_grouping'] : null;
        $show_entry_dates = isset($data['show_entry_dates']) ? intval($data['show_entry_dates']) : 0;
        
        // Calculate totals
        $subtotal = 0;
        foreach ($items as $item) {
            $subtotal += floatval($item['amount']);
        }
        
        // Get the primary project (first one) for the invoice
        $primary_project_id = !empty($project_ids) ? $project_ids[0] : null;
        $client_id = isset($data['client_id']) ? intval($data['client_id']) : null;
        
        // If no client_id provided, get from primary project
        if (!$client_id && $primary_project_id) {
            $project = FS_Project::get($primary_project_id);
            if ($project) {
                $client_id = $project->client_id;
            }
        }
        
        // Generate invoice number
        $invoice_number = self::generate_invoice_number();
        
        // Set due date
        $payment_terms = get_option('fs_default_payment_terms', 30);
        $due_date = isset($data['due_date']) && !empty($data['due_date']) 
            ? $data['due_date'] 
            : gmdate('Y-m-d', strtotime("+{$payment_terms} days"));
        
        // Get default sales tax rate if auto-apply is enabled
        $tax_rate = 0;
        $tax_amount = 0;
        $total = $subtotal;
        
        if (get_option('fs_auto_apply_sales_tax', '0') === '1') {
            $tax_rate = floatval(get_option('fs_default_sales_tax_rate', 0));
            if ($tax_rate > 0) {
                $tax_amount = $subtotal * ($tax_rate / 100);
                $total = $subtotal + $tax_amount;
            }
        }
        
        // Create the base invoice
        $table = $wpdb->prefix . 'fs_invoices';
        $view_token = wp_generate_password(32, false);
        
        $result = $wpdb->insert(
            $table,
            array(
                'client_id' => $client_id,
                'project_id' => $primary_project_id,
                'invoice_number' => $invoice_number,
                'invoice_date' => gmdate('Y-m-d'),
                'due_date' => $due_date,
                'status' => self::STATUS_DRAFT,
                'subtotal' => $subtotal,
                'tax_rate' => $tax_rate,
                'tax_amount' => $tax_amount,
                'discount_type' => null,
                'discount_value' => 0,
                'discount_amount' => 0,
                'total' => $total,
                'amount_paid' => 0,
                'balance_due' => $total,
                'notes' => isset($data['notes']) ? sanitize_textarea_field($data['notes']) : '',
                'items' => wp_json_encode($items),
                'expenses' => '[]',
                'is_deposit' => 0,
                'view_token' => $view_token,
                'invoice_source' => $invoice_source,
                'time_grouping' => $time_grouping,
                'show_entry_dates' => $show_entry_dates,
            )
        );
        
        if ($result === false) {
            error_log('FS_Invoice::create_from_wizard failed: ' . $wpdb->last_error);
            return false;
        }
        
        $invoice_id = $wpdb->insert_id;
        
        // Link projects to invoice
        if (!empty($project_ids)) {
            self::link_projects($invoice_id, $project_ids, $items);
        }
        
        // Save detailed line items
        self::save_invoice_items($invoice_id, $items);
        
        // Mark time entries as invoiced
        $time_entry_ids = array();
        foreach ($items as $item) {
            if (!empty($item['time_entry_ids'])) {
                $entry_ids = is_array($item['time_entry_ids']) ? $item['time_entry_ids'] : json_decode($item['time_entry_ids'] ?? '', true);
                if (is_array($entry_ids)) {
                    $time_entry_ids = array_merge($time_entry_ids, $entry_ids);
                }
            }
        }
        if (!empty($time_entry_ids)) {
            FS_Time_Entry::mark_invoiced(array_unique($time_entry_ids), $invoice_id);
        }
        
        return $invoice_id;
    }

    /**
     * Build line items from time entries with grouping (v3.2.0, updated v3.6.22)
     *
     * @param int $project_id Project ID
     * @param array $entry_ids Time entry IDs to include
     * @param string $grouping Grouping mode
     * @param bool $show_dates Whether to show dates
     * @param array $options Additional options (show_descriptions, show_date_activity, show_date_task, show_date_descriptions)
     * @return array Line items
     */
    public static function build_time_entry_items($project_id, $entry_ids, $grouping = 'activity', $show_dates = false, $options = array()) {
        global $wpdb;
        
        if (empty($entry_ids)) {
            return array();
        }
        
        // Extract options with defaults
        $show_descriptions = isset($options['show_descriptions']) ? $options['show_descriptions'] : false;
        $show_date_activity = isset($options['show_date_activity']) ? $options['show_date_activity'] : true;
        $show_date_task = isset($options['show_date_task']) ? $options['show_date_task'] : false;
        $show_date_descriptions = isset($options['show_date_descriptions']) ? $options['show_date_descriptions'] : false;
        
        $table = $wpdb->prefix . 'fs_time_entries';
        $placeholders = implode(',', array_fill(0, count($entry_ids), '%d'));
        
        $entries = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$table} WHERE id IN ({$placeholders}) ORDER BY start_time ASC",
            $entry_ids
        ));
        
        if (empty($entries)) {
            return array();
        }
        
        // Derive date from start_time for each entry (for backwards compatibility)
        foreach ($entries as $entry) {
            $entry->date = $entry->start_time ? gmdate('Y-m-d', strtotime($entry->start_time)) : null;
        }
        
        // Get rate fallback: entry's own rate (set at creation) → global default
        // This matches the reports SQL: COALESCE(NULLIF(t.hourly_rate, 0), default_rate)
        // Entry hourly_rate is populated at creation from project service rate or global default
        $global_default_rate = floatval(get_option('fs_default_hourly_rate', 75));
        
        $items = array();
        
        switch ($grouping) {
            case self::GROUP_BY_ENTRY:
                // Each entry is a separate line item
                foreach ($entries as $entry) {
                    $hours = FS_Time_Entry::get_rounded_hours($entry->duration);
                    // Use entry's hourly_rate if set, otherwise global default
                    $entry_rate = !empty($entry->hourly_rate) ? floatval($entry->hourly_rate) : $global_default_rate;
                    $amount = round($hours * $entry_rate, 2);
                    
                    $description = '';
                    if ($show_dates && $entry->date) {
                        $description .= gmdate('m/d', strtotime($entry->date)) . ' - ';
                    }
                    
                    // Show activity type first, then description if different
                    // Capitalize first letter of activity
                    $activity_name = !empty($entry->activity) ? ucfirst($entry->activity) : 'Work';
                    if (!empty($entry->description) && $entry->description !== $entry->activity) {
                        // Show both activity and description
                        $description .= $activity_name . ' - ' . $entry->description;
                    } else {
                        // Just show activity (or description if it's the same)
                        $description .= !empty($entry->description) ? $entry->description : $activity_name;
                    }
                    
                    $items[] = array(
                        'project_id' => $project_id,
                        'task_id' => $entry->task_id,
                        'activity' => $entry->activity,
                        'source_type' => 'time_entry',
                        'description' => $description,
                        'quantity' => $hours,
                        'rate' => $entry_rate,
                        'amount' => $amount,
                        'time_entry_ids' => array($entry->id),
                        'entry_date' => $entry->date,
                    );
                }
                break;
                
            case self::GROUP_BY_TASK:
                // Group by task_id (linked task records)
                // For recurring task instances, group by parent_task_id so all instances merge
                $grouped = array();
                foreach ($entries as $entry) {
                    $group_key = $entry->task_id ?: 'no_task';
                    $group_task_id = $entry->task_id;
                    
                    // Resolve parent for recurring task instances
                    if ($entry->task_id) {
                        $task_check = FS_Task::get($entry->task_id);
                        if ($task_check && $task_check->parent_task_id) {
                            $group_key = $task_check->parent_task_id;
                            $group_task_id = $task_check->parent_task_id;
                        }
                    }
                    
                    if (!isset($grouped[$group_key])) {
                        $grouped[$group_key] = array(
                            'task_id' => $group_task_id,
                            'entries' => array(),
                            'hours' => 0,
                            'total_amount' => 0,
                            'dates' => array(),
                            'descriptions' => array(),
                        );
                    }
                    $grouped[$group_key]['entries'][] = $entry;
                    $entry_hours = FS_Time_Entry::get_rounded_hours($entry->duration);
                    $entry_rate = !empty($entry->hourly_rate) ? floatval($entry->hourly_rate) : $global_default_rate;
                    $grouped[$group_key]['hours'] += $entry_hours;
                    $grouped[$group_key]['total_amount'] += round($entry_hours * $entry_rate, 2);
                    if ($entry->date) {
                        $grouped[$group_key]['dates'][] = $entry->date;
                    }
                    if (!empty($entry->description)) {
                        $grouped[$group_key]['descriptions'][] = $entry->description;
                    }
                }
                
                foreach ($grouped as $key => $data) {
                    $task_name = 'General Work';
                    if ($data['task_id']) {
                        $task = FS_Task::get($data['task_id']);
                        if ($task) {
                            $task_name = $task->title;
                        }
                    }
                    
                    $rounded_hours = round($data['hours'], 2);
                    // Calculate effective rate from total amount / hours
                    $effective_rate = $rounded_hours > 0 ? round($data['total_amount'] / $rounded_hours, 2) : $global_default_rate;
                    $description = '';
                    
                    if ($show_dates && !empty($data['dates'])) {
                        sort($data['dates']);
                        $first_date = gmdate('m/d', strtotime(reset($data['dates'])));
                        $last_date = gmdate('m/d', strtotime(end($data['dates'])));
                        if ($first_date !== $last_date) {
                            $description .= $first_date . '-' . $last_date . ' - ';
                        } else {
                            $description .= $first_date . ' - ';
                        }
                    }
                    $description .= $task_name . ' (' . $rounded_hours . ' hrs)';
                    
                    // Add descriptions if enabled
                    if ($show_descriptions && !empty($data['descriptions'])) {
                        $unique_descs = array_unique($data['descriptions']);
                        $description .= ' - ' . implode(', ', $unique_descs);
                    }
                    
                    $items[] = array(
                        'project_id' => $project_id,
                        'task_id' => $data['task_id'],
                        'activity' => null,
                        'source_type' => 'time_entry',
                        'description' => $description,
                        'quantity' => $rounded_hours,
                        'rate' => $effective_rate,
                        'amount' => round($rounded_hours * $effective_rate, 2),
                        'time_entry_ids' => wp_list_pluck($data['entries'], 'id'),
                        'entry_date' => !empty($data['dates']) ? reset($data['dates']) : null,
                    );
                }
                break;
                
            case self::GROUP_BY_ACTIVITY:
                // Group by activity type (default)
                $grouped = array();
                foreach ($entries as $entry) {
                    $key = $entry->activity ?: 'Other';
                    if (!isset($grouped[$key])) {
                        $grouped[$key] = array(
                            'activity' => $entry->activity ?: 'Other',
                            'entries' => array(),
                            'hours' => 0,
                            'total_amount' => 0,
                            'dates' => array(),
                            'descriptions' => array(),
                        );
                    }
                    $grouped[$key]['entries'][] = $entry;
                    $entry_hours = FS_Time_Entry::get_rounded_hours($entry->duration);
                    $entry_rate = !empty($entry->hourly_rate) ? floatval($entry->hourly_rate) : $global_default_rate;
                    $grouped[$key]['hours'] += $entry_hours;
                    $grouped[$key]['total_amount'] += round($entry_hours * $entry_rate, 2);
                    if ($entry->date) {
                        $grouped[$key]['dates'][] = $entry->date;
                    }
                    if (!empty($entry->description) && $entry->description !== $entry->activity) {
                        $grouped[$key]['descriptions'][] = $entry->description;
                    }
                }
                
                foreach ($grouped as $key => $data) {
                    $rounded_hours = round($data['hours'], 2);
                    // Calculate effective rate from total amount / hours
                    $effective_rate = $rounded_hours > 0 ? round($data['total_amount'] / $rounded_hours, 2) : $global_default_rate;
                    $description = '';
                    
                    if ($show_dates && !empty($data['dates'])) {
                        sort($data['dates']);
                        $first_date = gmdate('m/d', strtotime(reset($data['dates'])));
                        $last_date = gmdate('m/d', strtotime(end($data['dates'])));
                        if ($first_date !== $last_date) {
                            $description .= $first_date . '-' . $last_date . ' - ';
                        } else {
                            $description .= $first_date . ' - ';
                        }
                    }
                    // Capitalize first letter of activity
                    $description .= ucfirst($data['activity']) . ' (' . $rounded_hours . ' hrs)';
                    
                    // Add descriptions if enabled
                    if ($show_descriptions && !empty($data['descriptions'])) {
                        $unique_descs = array_unique($data['descriptions']);
                        $description .= ' - ' . implode(', ', $unique_descs);
                    }
                    
                    $items[] = array(
                        'project_id' => $project_id,
                        'task_id' => null,
                        'activity' => $data['activity'],
                        'source_type' => 'time_entry',
                        'description' => $description,
                        'quantity' => $rounded_hours,
                        'rate' => $effective_rate,
                        'amount' => round($rounded_hours * $effective_rate, 2),
                        'time_entry_ids' => wp_list_pluck($data['entries'], 'id'),
                        'entry_date' => !empty($data['dates']) ? reset($data['dates']) : null,
                    );
                }
                break;
                
            case self::GROUP_BY_PROJECT:
                // Single line item for entire project
                $total_hours = 0;
                $total_amount = 0;
                $all_entry_ids = array();
                $all_dates = array();
                
                foreach ($entries as $entry) {
                    $entry_hours = FS_Time_Entry::get_rounded_hours($entry->duration);
                    $entry_rate = !empty($entry->hourly_rate) ? floatval($entry->hourly_rate) : $global_default_rate;
                    $total_hours += $entry_hours;
                    $total_amount += round($entry_hours * $entry_rate, 2);
                    $all_entry_ids[] = $entry->id;
                    if ($entry->date) {
                        $all_dates[] = $entry->date;
                    }
                }
                
                // Calculate effective rate from total amount / hours
                $rounded_hours = round($total_hours, 2);
                $effective_rate = $rounded_hours > 0 ? round($total_amount / $rounded_hours, 2) : $global_default_rate;
                $description = '';
                
                if ($show_dates && !empty($all_dates)) {
                    sort($all_dates);
                    $first_date = gmdate('m/d', strtotime(reset($all_dates)));
                    $last_date = gmdate('m/d', strtotime(end($all_dates)));
                    if ($first_date !== $last_date) {
                        $description .= $first_date . '-' . $last_date . ' - ';
                    } else {
                        $description .= $first_date . ' - ';
                    }
                }
                /* translators: 1. number of hours */
                $description .= sprintf(__('%s hours of work', 'freelance-studio'), $rounded_hours);
                
                $items[] = array(
                    'project_id' => $project_id,
                    'task_id' => null,
                    'activity' => null,
                    'source_type' => 'time_entry',
                    'description' => $description,
                    'quantity' => $rounded_hours,
                    'rate' => $effective_rate,
                    'amount' => round($rounded_hours * $effective_rate, 2),
                    'time_entry_ids' => $all_entry_ids,
                    'entry_date' => !empty($all_dates) ? reset($all_dates) : null,
                );
                break;
                
            case self::GROUP_BY_DATE:
                // Group by date
                $grouped = array();
                foreach ($entries as $entry) {
                    $key = $entry->date ?: 'no_date';
                    if (!isset($grouped[$key])) {
                        $grouped[$key] = array(
                            'date' => $entry->date,
                            'entries' => array(),
                            'hours' => 0,
                            'total_amount' => 0,
                            'activities' => array(),
                            'tasks' => array(),
                            'task_ids' => array(),
                            'descriptions' => array(),
                        );
                    }
                    $grouped[$key]['entries'][] = $entry;
                    $entry_hours = FS_Time_Entry::get_rounded_hours($entry->duration);
                    $entry_rate = !empty($entry->hourly_rate) ? floatval($entry->hourly_rate) : $global_default_rate;
                    $grouped[$key]['hours'] += $entry_hours;
                    $grouped[$key]['total_amount'] += round($entry_hours * $entry_rate, 2);
                    if (!empty($entry->activity)) {
                        $grouped[$key]['activities'][] = $entry->activity;
                    }
                    if (!empty($entry->task_id)) {
                        $grouped[$key]['task_ids'][] = $entry->task_id;
                    }
                    if (!empty($entry->description)) {
                        $grouped[$key]['descriptions'][] = $entry->description;
                    }
                }
                
                // Sort by date
                ksort($grouped);
                
                foreach ($grouped as $key => $data) {
                    $rounded_hours = round($data['hours'], 2);
                    // Calculate effective rate from total amount / hours
                    $effective_rate = $rounded_hours > 0 ? round($data['total_amount'] / $rounded_hours, 2) : $global_default_rate;
                    
                    // Build description parts
                    $desc_parts = array();
                    
                    // Format date
                    if ($data['date']) {
                        $desc_parts[] = gmdate('m/d/Y', strtotime($data['date']));
                    } else {
                        $desc_parts[] = 'No Date';
                    }
                    
                    // Add activity if enabled
                    if ($show_date_activity && !empty($data['activities'])) {
                        $unique_activities = array_unique($data['activities']);
                        // Capitalize first letter of each activity
                        $capitalized_activities = array_map('ucfirst', $unique_activities);
                        $desc_parts[] = implode(', ', $capitalized_activities);
                    }
                    
                    // Add task names if enabled
                    if ($show_date_task && !empty($data['task_ids'])) {
                        $task_names = array();
                        foreach (array_unique($data['task_ids']) as $task_id) {
                            $task = FS_Task::get($task_id);
                            if ($task) {
                                $task_names[] = $task->title;
                            }
                        }
                        if (!empty($task_names)) {
                            $desc_parts[] = '[' . implode(', ', $task_names) . ']';
                        }
                    }
                    
                    // Add descriptions if enabled
                    if ($show_date_descriptions && !empty($data['descriptions'])) {
                        $unique_descs = array_unique($data['descriptions']);
                        $desc_parts[] = implode(', ', $unique_descs);
                    }
                    
                    // Add hours
                    $desc_parts[] = '(' . $rounded_hours . ' hrs)';
                    
                    $description = implode(' - ', $desc_parts);
                    
                    $items[] = array(
                        'project_id' => $project_id,
                        'task_id' => null,
                        'activity' => null,
                        'source_type' => 'time_entry',
                        'description' => $description,
                        'quantity' => $rounded_hours,
                        'rate' => $effective_rate,
                        'amount' => round($rounded_hours * $effective_rate, 2),
                        'time_entry_ids' => wp_list_pluck($data['entries'], 'id'),
                        'entry_date' => $data['date'],
                    );
                }
                break;
        }
        
        return $items;
    }

    /**
     * Build line items from project services (v3.2.0)
     *
     * @param int $project_id Project ID
     * @param array $service_indices Service indices to include
     * @param string $billing_option Billing option (full, percentage, custom)
     * @param float $percentage Percentage to bill (for percentage option)
     * @param float $custom_amount Custom amount to bill
     * @param string $milestone_label Label for partial billing
     * @return array Line items
     */
    public static function build_service_items($project_id, $service_indices, $billing_option = 'full', $percentage = 100, $custom_amount = 0, $milestone_label = '') {
        $project = FS_Project::get($project_id);
        if (!$project) {
            return array();
        }
        
        $services = is_array($project->services) ? $project->services : json_decode($project->services ?? '', true);
        if (empty($services)) {
            return array();
        }
        
        $items = array();
        
        foreach ($service_indices as $index) {
            if (!isset($services[$index])) {
                continue;
            }
            
            $service = $services[$index];
            $original_amount = self::calculate_service_amount($service);
            
            // Calculate billing amount based on option
            $amount = $original_amount;
            $description = $service['name'];
            
            switch ($billing_option) {
                case 'percentage':
                    $amount = $original_amount * ($percentage / 100);
                    if (!empty($milestone_label)) {
                        $description = $milestone_label;
                    } else {
                        $description = sprintf('%d%% - %s', intval($percentage), $service['name']);
                    }
                    break;
                    
                case 'custom':
                    $amount = floatval($custom_amount);
                    if (!empty($milestone_label)) {
                        $description = $milestone_label;
                    }
                    break;
            }
            
            $items[] = array(
                'project_id' => $project_id,
                'service_index' => $index,
                'source_type' => 'service',
                'description' => $description,
                'quantity' => 1,
                'rate' => round($amount, 2),
                'amount' => round($amount, 2),
                'original_rate' => $original_amount,
                'time_entry_ids' => null,
            );
        }
        
        return $items;
    }

    /**
     * Calculate service amount
     */
    private static function calculate_service_amount($service) {
        $type = isset($service['type']) ? $service['type'] : 'flat';
        $rate = floatval($service['rate'] ?? 0);
        $quantity = floatval($service['quantity'] ?? 1);
        
        if ($type === 'hourly') {
            return $rate * $quantity;
        }
        return $rate;
    }

    /**
     * Link projects to invoice (v3.2.0)
     *
     * @param int $invoice_id Invoice ID
     * @param array $project_ids Project IDs
     * @param array $items Items to calculate subtotals
     */
    public static function link_projects($invoice_id, $project_ids, $items) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_invoice_projects';
        
        // Calculate subtotals per project
        $subtotals = array();
        foreach ($project_ids as $pid) {
            $subtotals[$pid] = 0;
        }
        
        foreach ($items as $item) {
            $pid = isset($item['project_id']) ? intval($item['project_id']) : 0;
            if ($pid && isset($subtotals[$pid])) {
                $subtotals[$pid] += floatval($item['amount']);
            }
        }
        
        // Insert project links
        foreach ($project_ids as $project_id) {
            $wpdb->insert(
                $table,
                array(
                    'invoice_id' => $invoice_id,
                    'project_id' => $project_id,
                    'subtotal' => isset($subtotals[$project_id]) ? $subtotals[$project_id] : 0,
                ),
                array('%d', '%d', '%f')
            );
        }
    }

    /**
     * Save detailed invoice items (v3.2.0)
     *
     * @param int $invoice_id Invoice ID
     * @param array $items Items to save
     */
    public static function save_invoice_items($invoice_id, $items) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_invoice_items';
        
        $sort_order = 0;
        foreach ($items as $item) {
            $wpdb->insert(
                $table,
                array(
                    'invoice_id' => $invoice_id,
                    'project_id' => isset($item['project_id']) ? intval($item['project_id']) : null,
                    'task_id' => isset($item['task_id']) ? intval($item['task_id']) : null,
                    'service_index' => isset($item['service_index']) ? intval($item['service_index']) : null,
                    'activity' => isset($item['activity']) ? sanitize_text_field($item['activity']) : null,
                    'source_type' => isset($item['source_type']) ? sanitize_text_field($item['source_type']) : 'manual',
                    'description' => sanitize_text_field($item['description']),
                    'quantity' => floatval($item['quantity']),
                    'rate' => floatval($item['rate']),
                    'amount' => floatval($item['amount']),
                    'original_rate' => isset($item['original_rate']) ? floatval($item['original_rate']) : null,
                    'time_entry_ids' => isset($item['time_entry_ids']) ? wp_json_encode($item['time_entry_ids']) : null,
                    'entry_date' => isset($item['entry_date']) ? $item['entry_date'] : null,
                    'sort_order' => $sort_order++,
                )
            );
        }
    }

    /**
     * Check if an invoice can be edited (v3.2.0)
     *
     * @param object|int $invoice Invoice object or ID
     * @return array Array with can_edit, reason, locked, prompt_resend
     */
    public static function can_edit($invoice) {
        if (is_numeric($invoice)) {
            $invoice = self::get($invoice);
        }
        
        if (!$invoice) {
            return array(
                'can_edit' => false,
                'reason' => __('Invoice not found.', 'freelance-studio'),
                'locked' => true,
                'prompt_resend' => false,
            );
        }
        
        // Paid invoices are locked
        if ($invoice->status === self::STATUS_PAID) {
            return array(
                'can_edit' => false,
                'reason' => __('This invoice has been paid and cannot be edited.', 'freelance-studio'),
                'locked' => true,
                'prompt_resend' => false,
            );
        }
        
        // Sent or viewed invoices can be edited but require resend prompt
        if (in_array($invoice->status, array(self::STATUS_SENT, self::STATUS_VIEWED))) {
            return array(
                'can_edit' => true,
                'reason' => __('This invoice has been sent. After editing, you will need to resend it to the client.', 'freelance-studio'),
                'locked' => false,
                'prompt_resend' => true,
            );
        }
        
        // Draft and other statuses can be freely edited
        return array(
            'can_edit' => true,
            'reason' => '',
            'locked' => false,
            'prompt_resend' => false,
        );
    }

    /**
     * Get linked projects for an invoice (v3.2.0)
     *
     * @param int $invoice_id Invoice ID
     * @return array Projects with subtotals
     */
    public static function get_linked_projects($invoice_id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_invoice_projects';
        $projects_table = $wpdb->prefix . 'fs_projects';
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT ip.*, p.name as project_name 
             FROM {$table} ip 
             LEFT JOIN {$projects_table} p ON ip.project_id = p.id 
             WHERE ip.invoice_id = %d 
             ORDER BY ip.id ASC",
            $invoice_id
        ));
    }

    /**
     * Get detailed invoice items (v3.2.0)
     *
     * @param int $invoice_id Invoice ID
     * @return array Items
     */
    public static function get_invoice_items($invoice_id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_invoice_items';
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$table} WHERE invoice_id = %d ORDER BY sort_order ASC",
            $invoice_id
        ));
    }

    /**
     * Get unbilled time summary for a project (v3.2.0)
     *
     * @param int $project_id Project ID
     * @return array Summary with hours and amount
     */
    public static function get_unbilled_time_summary($project_id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_time_entries';
        
        // Match Time Tracking summary: billable = 1 AND invoiced = 0
        $result = $wpdb->get_row($wpdb->prepare(
            "SELECT COUNT(*) as count, COALESCE(SUM(duration), 0) as total_seconds 
             FROM {$table} 
             WHERE project_id = %d 
             AND billable = 1
             AND invoiced = 0",
            $project_id
        ));
        
        $hours = $result ? floatval($result->total_seconds) / 3600 : 0;
        
        // Get project hourly rate (property, not method)
        $project = FS_Project::get($project_id);
        $rate = $project && isset($project->hourly_rate) ? floatval($project->hourly_rate) : 0;
        
        return array(
            'count' => $result ? intval($result->count) : 0,
            'hours' => round($hours, 2),
            'rate' => $rate,
            'amount' => round($hours * $rate, 2),
        );
    }

    /**
     * Get unbilled services for a project (v3.2.0)
     *
     * @param int $project_id Project ID
     * @return array Services with billing status
     */
    public static function get_unbilled_services($project_id) {
        $project = FS_Project::get($project_id);
        if (!$project) {
            return array();
        }
        
        $services = is_array($project->services) ? $project->services : json_decode($project->services ?? '', true);
        if (empty($services)) {
            return array();
        }
        
        $result = array();
        foreach ($services as $index => $service) {
            $amount = self::calculate_service_amount($service);
            $result[] = array(
                'index' => $index,
                'name' => $service['name'],
                'type' => isset($service['type']) ? $service['type'] : 'flat',
                'rate' => floatval($service['rate'] ?? 0),
                'quantity' => floatval($service['quantity'] ?? 1),
                'amount' => $amount,
            );
        }
        
        return $result;
    }

    /**
     * Get unbilled time entries for a project (v3.2.0)
     *
     * @param int $project_id Project ID
     * @return array Time entries
     */
    public static function get_unbilled_time_entries($project_id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_time_entries';
        
        // Match Time Tracking summary: billable = 1 AND invoiced = 0
        return $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$table} 
             WHERE project_id = %d 
             AND billable = 1
             AND invoiced = 0
             ORDER BY start_time DESC",
            $project_id
        ));
    }
}
