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

class FS_Recurring_Invoice {

    /**
     * Status constants
     */
    const STATUS_ACTIVE = 'active';
    const STATUS_PAUSED = 'paused';
    const STATUS_COMPLETED = 'completed';
    const STATUS_CANCELLED = 'cancelled';

    /**
     * Frequency constants
     */
    const FREQ_WEEKLY = 'weekly';
    const FREQ_BIWEEKLY = 'biweekly';
    const FREQ_MONTHLY = 'monthly';
    const FREQ_QUARTERLY = 'quarterly';
    const FREQ_YEARLY = 'yearly';

    /**
     * Get frequency labels
     */
    public static function get_frequencies() {
        return array(
            self::FREQ_WEEKLY => __('Weekly', 'freelance-studio'),
            self::FREQ_BIWEEKLY => __('Bi-weekly', 'freelance-studio'),
            self::FREQ_MONTHLY => __('Monthly', 'freelance-studio'),
            self::FREQ_QUARTERLY => __('Quarterly', 'freelance-studio'),
            self::FREQ_YEARLY => __('Yearly', 'freelance-studio'),
        );
    }

    /**
     * Get status labels
     */
    public static function get_statuses() {
        return array(
            self::STATUS_ACTIVE => __('Active', 'freelance-studio'),
            self::STATUS_PAUSED => __('Paused', 'freelance-studio'),
            self::STATUS_COMPLETED => __('Completed', 'freelance-studio'),
            self::STATUS_CANCELLED => __('Cancelled', 'freelance-studio'),
        );
    }

    /**
     * Get a single recurring invoice by ID
     */
    public static function get($id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_recurring_invoices';
        
        return $wpdb->get_row($wpdb->prepare(
            "SELECT r.*, 
                    c.name as client_name, c.company_name,
                    p.name as project_name
             FROM {$table} r
             LEFT JOIN {$wpdb->prefix}fs_clients c ON r.client_id = c.id
             LEFT JOIN {$wpdb->prefix}fs_projects p ON r.project_id = p.id
             WHERE r.id = %d",
            $id
        ));
    }

    /**
     * Get all recurring invoices with filters
     */
    public static function get_all($args = array()) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_recurring_invoices';
        
        $defaults = array(
            'client_id' => null,
            'project_id' => null,
            'status' => null,
            'orderby' => 'created_at',
            'order' => 'DESC',
            'limit' => null,
            'offset' => 0,
        );
        
        $args = wp_parse_args($args, $defaults);
        $where = array('1=1');
        
        if ($args['client_id']) {
            $where[] = $wpdb->prepare('r.client_id = %d', $args['client_id']);
        }
        
        if ($args['project_id']) {
            $where[] = $wpdb->prepare('r.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("r.status IN ($placeholders)", $args['status']);
            } else {
                $where[] = $wpdb->prepare('r.status = %s', $args['status']);
            }
        }
        
        $where_clause = implode(' AND ', $where);
        
        $allowed_orderby = array('created_at', 'name', 'next_invoice_date', 'amount', 'status');
        $orderby = in_array($args['orderby'], $allowed_orderby) ? $args['orderby'] : 'created_at';
        $order = strtoupper($args['order']) === 'ASC' ? 'ASC' : 'DESC';
        
        $limit_clause = '';
        if ($args['limit']) {
            $limit_clause = $wpdb->prepare('LIMIT %d OFFSET %d', $args['limit'], $args['offset']);
        }
        
        return $wpdb->get_results(
            "SELECT r.*, 
                    c.name as client_name, c.company_name,
                    p.name as project_name
             FROM {$table} r
             LEFT JOIN {$wpdb->prefix}fs_clients c ON r.client_id = c.id
             LEFT JOIN {$wpdb->prefix}fs_projects p ON r.project_id = p.id
             WHERE {$where_clause}
             ORDER BY r.{$orderby} {$order}
             {$limit_clause}"
        );
    }

    /**
     * Get recurring invoices due for generation
     */
    public static function get_due() {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_recurring_invoices';
        $today = current_time('Y-m-d');
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT r.*, 
                    c.name as client_name, c.company_name, c.email as client_email,
                    p.name as project_name
             FROM {$table} r
             LEFT JOIN {$wpdb->prefix}fs_clients c ON r.client_id = c.id
             LEFT JOIN {$wpdb->prefix}fs_projects p ON r.project_id = p.id
             WHERE r.status = 'active'
               AND r.next_invoice_date <= %s
               AND (r.end_date IS NULL OR r.end_date >= %s)",
            $today,
            $today
        ));
    }

    /**
     * Create a new recurring invoice
     */
    public static function create($data) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_recurring_invoices';
        
        // Serialize items if array
        $items = isset($data['items']) ? $data['items'] : array();
        if (is_array($items)) {
            $items = json_encode($items);
        }
        
        $insert_data = array(
                'client_id' => intval($data['client_id']),
                'project_id' => !empty($data['project_id']) ? intval($data['project_id']) : null,
                'name' => sanitize_text_field($data['name']),
                'frequency' => sanitize_text_field($data['frequency']),
                'amount' => floatval($data['amount']),
                'items' => $items,
                'next_invoice_date' => !empty($data['next_invoice_date']) ? $data['next_invoice_date'] : null,
                'end_date' => !empty($data['end_date']) ? $data['end_date'] : null,
                'status' => isset($data['status']) ? sanitize_text_field($data['status']) : self::STATUS_ACTIVE,
                'auto_send' => isset($data['auto_send']) ? intval($data['auto_send']) : 0,
                'include_time_entries' => isset($data['include_time_entries']) ? intval($data['include_time_entries']) : 0,
                'time_grouping' => isset($data['time_grouping']) ? sanitize_text_field($data['time_grouping']) : 'task',
                'mark_time_invoiced' => isset($data['mark_time_invoiced']) ? intval($data['mark_time_invoiced']) : 1,
        );
        
        $result = $wpdb->insert(
            $table,
            $insert_data,
            array('%d', '%d', '%s', '%s', '%f', '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%d')
        );
        
        if ($result) {
            $new_id = $wpdb->insert_id;
            FS_DB_Helper::fix_null_fields($table, $new_id, array(
                'project_id' => $insert_data['project_id'],
            ));
            return $new_id;
        }
        
        return false;
    }

    /**
     * Update a recurring invoice
     */
    public static function update($id, $data) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_recurring_invoices';
        
        $update_data = array();
        $format = array();
        
        if (isset($data['client_id'])) {
            $update_data['client_id'] = intval($data['client_id']);
            $format[] = '%d';
        }
        
        if (array_key_exists('project_id', $data)) {
            $update_data['project_id'] = !empty($data['project_id']) ? intval($data['project_id']) : null;
            $format[] = '%d';
        }
        
        if (isset($data['name'])) {
            $update_data['name'] = sanitize_text_field($data['name']);
            $format[] = '%s';
        }
        
        if (isset($data['frequency'])) {
            $update_data['frequency'] = sanitize_text_field($data['frequency']);
            $format[] = '%s';
        }
        
        if (isset($data['amount'])) {
            $update_data['amount'] = floatval($data['amount']);
            $format[] = '%f';
        }
        
        if (isset($data['items'])) {
            $items = is_array($data['items']) ? json_encode($data['items']) : $data['items'];
            $update_data['items'] = $items;
            $format[] = '%s';
        }
        
        if (array_key_exists('next_invoice_date', $data)) {
            $update_data['next_invoice_date'] = !empty($data['next_invoice_date']) ? $data['next_invoice_date'] : null;
            $format[] = '%s';
        }
        
        if (array_key_exists('last_invoice_date', $data)) {
            $update_data['last_invoice_date'] = !empty($data['last_invoice_date']) ? $data['last_invoice_date'] : null;
            $format[] = '%s';
        }
        
        if (array_key_exists('end_date', $data)) {
            $update_data['end_date'] = !empty($data['end_date']) ? $data['end_date'] : null;
            $format[] = '%s';
        }
        
        if (isset($data['status'])) {
            $update_data['status'] = sanitize_text_field($data['status']);
            $format[] = '%s';
        }
        
        if (isset($data['auto_send'])) {
            $update_data['auto_send'] = intval($data['auto_send']);
            $format[] = '%d';
        }
        
        if (isset($data['include_time_entries'])) {
            $update_data['include_time_entries'] = intval($data['include_time_entries']);
            $format[] = '%d';
        }
        
        if (isset($data['time_grouping'])) {
            $update_data['time_grouping'] = sanitize_text_field($data['time_grouping']);
            $format[] = '%s';
        }
        
        if (isset($data['mark_time_invoiced'])) {
            $update_data['mark_time_invoiced'] = intval($data['mark_time_invoiced']);
            $format[] = '%d';
        }
        
        if (isset($data['invoices_generated'])) {
            $update_data['invoices_generated'] = intval($data['invoices_generated']);
            $format[] = '%d';
        }
        
        if (empty($update_data)) {
            return false;
        }
        
        // Track nullable foreign keys
        $nullable_fks = array();
        if (array_key_exists('project_id', $update_data) && $update_data['project_id'] === null) {
            $nullable_fks['project_id'] = null;
            // Remove from update_data and format
            $idx = array_search('project_id', array_keys($update_data));
            unset($update_data['project_id']);
            $format = array_values(array_diff_key($format, array($idx => true)));
        }
        
        $result = false;
        if (!empty($update_data)) {
            $result = $wpdb->update($table, $update_data, array('id' => $id), $format, array('%d'));
        }
        
        if (!empty($nullable_fks)) {
            FS_DB_Helper::fix_null_fields($table, $id, $nullable_fks);
        }
        
        return $result;
    }

    /**
     * Delete a recurring invoice
     */
    public static function delete($id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_recurring_invoices';
        
        return $wpdb->delete($table, array('id' => $id), array('%d'));
    }

    /**
     * Calculate next invoice date based on frequency
     */
    public static function calculate_next_date($current_date, $frequency) {
        $date = new DateTime($current_date);
        
        switch ($frequency) {
            case self::FREQ_WEEKLY:
                $date->modify('+1 week');
                break;
            case self::FREQ_BIWEEKLY:
                $date->modify('+2 weeks');
                break;
            case self::FREQ_MONTHLY:
                $date->modify('+1 month');
                break;
            case self::FREQ_QUARTERLY:
                $date->modify('+3 months');
                break;
            case self::FREQ_YEARLY:
                $date->modify('+1 year');
                break;
        }
        
        return $date->format('Y-m-d');
    }

    /**
     * Generate an invoice from a recurring invoice
     */
    public static function generate_invoice($recurring_id) {
        $recurring = self::get($recurring_id);
        
        if (!$recurring || $recurring->status !== self::STATUS_ACTIVE) {
            return false;
        }
        
        // Parse items - recurring->items is JSON string from DB
        $items = json_decode($recurring->items ?? '', true) ?: array();
        
        // If no static items, create a single line item from amount (unless time entries will be added)
        $include_time = !empty($recurring->include_time_entries) && !empty($recurring->project_id);
        
        if (empty($items) && !$include_time) {
            $items = array(
                array(
                    'description' => $recurring->name,
                    'quantity' => 1,
                    'rate' => $recurring->amount,
                    'amount' => $recurring->amount,
                ),
            );
        }
        
        // Auto-populate time entries if enabled
        $time_entry_ids = array();
        if ($include_time) {
            $time_items = self::get_time_entry_line_items($recurring, $time_entry_ids);
            $items = array_merge($items, $time_items);
        }
        
        // Failsafe: don't generate an empty invoice with nothing to bill
        if (empty($items)) {
            // Still advance the next_invoice_date so we don't retry every cron run
            $next_date = self::calculate_next_date($recurring->next_invoice_date, $recurring->frequency);
            $new_status = $recurring->status;
            if ($recurring->end_date && $next_date > $recurring->end_date) {
                $new_status = self::STATUS_COMPLETED;
            }
            self::update($recurring_id, array(
                'next_invoice_date' => $next_date,
                'status' => $new_status,
            ));
            error_log("Freelance Studio: Skipped recurring invoice #{$recurring_id} ({$recurring->name}) — no billable items for this period.");
            return false;
        }
        
        // Calculate total from all items
        $total = 0;
        foreach ($items as $item) {
            $total += floatval($item['amount'] ?? 0);
        }
        
        // Failsafe: don't generate a $0 invoice
        if ($total <= 0) {
            $next_date = self::calculate_next_date($recurring->next_invoice_date, $recurring->frequency);
            $new_status = $recurring->status;
            if ($recurring->end_date && $next_date > $recurring->end_date) {
                $new_status = self::STATUS_COMPLETED;
            }
            self::update($recurring_id, array(
                'next_invoice_date' => $next_date,
                'status' => $new_status,
            ));
            error_log("Freelance Studio: Skipped recurring invoice #{$recurring_id} ({$recurring->name}) — total is \$0.");
            return false;
        }
        
        // Calculate payment terms - use client-specific if available
        $payment_terms = get_option('fs_default_payment_terms', 30);
        if ($recurring->client_id) {
            $ri_client = FS_Client::get($recurring->client_id);
            if ($ri_client && isset($ri_client->default_payment_terms) && $ri_client->default_payment_terms > 0) {
                $payment_terms = intval($ri_client->default_payment_terms);
            }
        }
        $due_date = gmdate('Y-m-d', strtotime("+{$payment_terms} days"));
        
        // Create invoice
        $invoice_data = array(
            'client_id' => $recurring->client_id,
            'project_id' => $recurring->project_id,
            'invoice_number' => FS_Invoice::generate_invoice_number(),
            'invoice_date' => current_time('Y-m-d'),
            'due_date' => $due_date,
            'status' => FS_Invoice::STATUS_DRAFT,
            'items' => $items,
            'subtotal' => $total,
            'tax_rate' => 0,
            /* translators: 1. recurring invoice value */
            'notes' => sprintf(__('Recurring invoice: %s', 'freelance-studio'), $recurring->name),
        );
        
        $invoice_id = FS_Invoice::create($invoice_data);
        
        if ($invoice_id) {
            // Update recurring invoice
            $next_date = self::calculate_next_date($recurring->next_invoice_date, $recurring->frequency);
            
            // Check if we've reached the end date
            $new_status = $recurring->status;
            if ($recurring->end_date && $next_date > $recurring->end_date) {
                $new_status = self::STATUS_COMPLETED;
            }
            
            self::update($recurring_id, array(
                'last_invoice_date' => current_time('Y-m-d'),
                'next_invoice_date' => $next_date,
                'invoices_generated' => $recurring->invoices_generated + 1,
                'status' => $new_status,
            ));
            
            // Mark time entries as invoiced if enabled
            if (!empty($time_entry_ids) && !empty($recurring->mark_time_invoiced)) {
                FS_Time_Entry::mark_invoiced($time_entry_ids, $invoice_id);
            }
            
            // Auto-send if enabled
            if ($recurring->auto_send) {
                FS_Invoice::update_status($invoice_id, FS_Invoice::STATUS_SENT);
                // Send the invoice email to the client
                FS_Email_Service::send_invoice($invoice_id);
            }
            
            return $invoice_id;
        }
        
        return false;
    }

    /**
     * Get time entry line items for a recurring invoice generation
     *
     * @param object $recurring The recurring invoice object
     * @param array &$time_entry_ids Populated with IDs of time entries included
     * @return array Line items array
     */
    private static function get_time_entry_line_items($recurring, &$time_entry_ids) {
        $items = array();
        $time_entry_ids = array();
        
        if (empty($recurring->project_id)) {
            return $items;
        }
        
        // Calculate date range based on frequency
        $period_days = array(
            'weekly' => 7,
            'biweekly' => 14,
            'monthly' => 30,
            'quarterly' => 90,
            'yearly' => 365,
        );
        $days = $period_days[$recurring->frequency] ?? 30;
        $date_from = gmdate('Y-m-d', strtotime("-{$days} days"));
        $date_to = current_time('Y-m-d');
        
        // Get unbilled, billable time entries for this project in the period
        $entries = FS_Time_Entry::get_all(array(
            'project_id' => $recurring->project_id,
            'billable' => true,
            'invoiced' => false,
            'running' => false,
            'date_from' => $date_from,
            'date_to' => $date_to,
            'orderby' => 'start_time',
            'order' => 'ASC',
        ));
        
        if (empty($entries)) {
            return $items;
        }
        
        // Collect all entry IDs
        foreach ($entries as $entry) {
            $time_entry_ids[] = $entry->id;
        }
        
        $grouping = $recurring->time_grouping ?? 'task';
        
        // Get rate fallback: entry's own rate → global default
        $global_default_rate = floatval(get_option('fs_default_hourly_rate', 75));
        
        if ($grouping === 'entry') {
            // Individual line items per entry
            foreach ($entries as $entry) {
                $hours = FS_Time_Entry::get_rounded_hours($entry->duration);
                $rate = !empty($entry->hourly_rate) ? floatval($entry->hourly_rate) : $global_default_rate;
                $desc = '';
                if (!empty($entry->task_name)) {
                    $desc = $entry->task_name;
                }
                if (!empty($entry->description)) {
                    $desc .= $desc ? ' — ' . $entry->description : $entry->description;
                }
                if (empty($desc)) {
                    $desc = !empty($entry->activity) ? ucfirst($entry->activity) : __('Time entry', 'freelance-studio');
                }
                $desc .= ' (' . gmdate('M j', strtotime($entry->start_time)) . ')';
                
                $items[] = array(
                    'description' => $desc,
                    'quantity' => $hours,
                    'rate' => $rate,
                    'amount' => round($hours * $rate, 2),
                );
            }
        } elseif ($grouping === 'activity') {
            // Group by activity type - round each entry individually, then sum
            $grouped = array();
            foreach ($entries as $entry) {
                $key = $entry->activity ?: 'other';
                if (!isset($grouped[$key])) {
                    $grouped[$key] = array('hours' => 0, 'total_amount' => 0, 'rate' => 0);
                }
                $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);
                // Track highest rate for effective rate calculation
                if ($entry_rate > $grouped[$key]['rate']) {
                    $grouped[$key]['rate'] = $entry_rate;
                }
            }
            foreach ($grouped as $activity => $data) {
                $rounded_hours = round($data['hours'], 2);
                $effective_rate = $rounded_hours > 0 ? round($data['total_amount'] / $rounded_hours, 2) : $global_default_rate;
                $items[] = array(
                    'description' => ucfirst($activity) . ' (' . $date_from . ' – ' . $date_to . ')',
                    'quantity' => $rounded_hours,
                    'rate' => $effective_rate,
                    'amount' => round($rounded_hours * $effective_rate, 2),
                );
            }
        } else {
            // Default: group by task - round each entry individually, then sum
            $grouped = array();
            foreach ($entries as $entry) {
                $key = $entry->task_id ?: 0;
                if (!isset($grouped[$key])) {
                    $grouped[$key] = array(
                        'name' => $entry->task_name ?: __('General', 'freelance-studio'),
                        'hours' => 0,
                        'total_amount' => 0,
                        'rate' => 0,
                    );
                }
                $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_rate > $grouped[$key]['rate']) {
                    $grouped[$key]['rate'] = $entry_rate;
                }
            }
            foreach ($grouped as $task_id => $data) {
                $rounded_hours = round($data['hours'], 2);
                $effective_rate = $rounded_hours > 0 ? round($data['total_amount'] / $rounded_hours, 2) : $global_default_rate;
                $items[] = array(
                    'description' => $data['name'] . ' (' . $date_from . ' – ' . $date_to . ')',
                    'quantity' => $rounded_hours,
                    'rate' => $effective_rate,
                    'amount' => round($rounded_hours * $effective_rate, 2),
                );
            }
        }
        
        return $items;
    }

    /**
     * Process all due recurring invoices
     * Called by cron job
     */
    public static function process_due_invoices() {
        $due = self::get_due();
        $generated = array();
        
        foreach ($due as $recurring) {
            $invoice_id = self::generate_invoice($recurring->id);
            if ($invoice_id) {
                $generated[] = array(
                    'recurring_id' => $recurring->id,
                    'invoice_id' => $invoice_id,
                );
            }
        }
        
        return $generated;
    }

    /**
     * Count recurring invoices
     */
    public static function count($args = array()) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_recurring_invoices';
        
        $where = array('1=1');
        
        if (!empty($args['status'])) {
            $where[] = $wpdb->prepare('status = %s', $args['status']);
        }
        
        if (!empty($args['client_id'])) {
            $where[] = $wpdb->prepare('client_id = %d', $args['client_id']);
        }
        
        $where_clause = implode(' AND ', $where);
        
        return (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table} WHERE {$where_clause}");
    }

    /**
     * Get invoices generated by this recurring invoice
     */
    public static function get_generated_invoices($recurring_id) {
        global $wpdb;
        
        $recurring = self::get($recurring_id);
        if (!$recurring) {
            return array();
        }
        
        // Find invoices matching client/project with the recurring note
        $table = $wpdb->prefix . 'fs_invoices';
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$table} 
             WHERE client_id = %d 
               AND notes LIKE %s
             ORDER BY invoice_date DESC",
            $recurring->client_id,
            '%Recurring invoice: ' . $wpdb->esc_like($recurring->name) . '%'
        ));
    }
}
