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

class FS_Expense {

    /**
     * Get all expenses
     *
     * @param array $args Query arguments
     * @return array
     */
    public static function get_all($args = array()) {
        global $wpdb;
        
        $defaults = array(
            'project_id' => null,
            'category' => null,
            'start_date' => null,
            'end_date' => null,
            'is_billable' => null,
            'orderby' => 'expense_date',
            'order' => 'DESC',
            'limit' => null,
            'offset' => 0
        );
        
        $args = wp_parse_args($args, $defaults);
        
        $table = $wpdb->prefix . 'fs_expenses';
        $projects_table = $wpdb->prefix . 'fs_projects';
        
        $where = array('1=1');
        
        if ($args['project_id']) {
            $where[] = $wpdb->prepare('e.project_id = %d', $args['project_id']);
        }
        
        if ($args['category']) {
            $where[] = $wpdb->prepare('e.category = %s', $args['category']);
        }
        
        if ($args['start_date']) {
            $where[] = $wpdb->prepare('e.expense_date >= %s', $args['start_date']);
        }
        
        if ($args['end_date']) {
            $where[] = $wpdb->prepare('e.expense_date <= %s', $args['end_date']);
        }
        
        if ($args['is_billable'] !== null) {
            $where[] = $wpdb->prepare('e.is_billable = %d', $args['is_billable']);
        }
        
        $where_clause = implode(' AND ', $where);
        
        $allowed_orderby = array('expense_date', 'amount', 'category', 'expense_name');
        $orderby = in_array($args['orderby'], $allowed_orderby) ? $args['orderby'] : 'expense_date';
        $order = strtoupper($args['order']) === 'ASC' ? 'ASC' : 'DESC';
        
        $sql = "SELECT e.*, p.name as project_name 
                FROM {$table} e 
                LEFT JOIN {$projects_table} p ON e.project_id = p.id 
                WHERE {$where_clause} 
                ORDER BY e.{$orderby} {$order}";
        
        if ($args['limit']) {
            $sql .= $wpdb->prepare(' LIMIT %d OFFSET %d', $args['limit'], $args['offset']);
        }
        
        return $wpdb->get_results($sql); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
    }

    /**
     * Get a single expense
     *
     * @param int $id Expense ID
     * @return object|null
     */
    public static function get($id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_expenses';
        
        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} WHERE id = %d",
            $id
        ));
    }

    /**
     * Create a new expense
     *
     * @param array $data Expense data
     * @return int|false Insert ID or false on failure
     */
    public static function create($data) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_expenses';
        
        $insert_data = array(
            'project_id' => isset($data['project_id']) && $data['project_id'] ? intval($data['project_id']) : null,
            'invoice_id' => isset($data['invoice_id']) && $data['invoice_id'] ? intval($data['invoice_id']) : null,
            'expense_date' => sanitize_text_field($data['expense_date']),
            'expense_name' => sanitize_text_field($data['expense_name']),
            'category' => sanitize_text_field($data['category']),
            'amount' => floatval($data['amount']),
            'is_billable' => isset($data['is_billable']) ? intval($data['is_billable']) : 0,
            'is_reimbursable' => isset($data['is_reimbursable']) ? intval($data['is_reimbursable']) : 0,
            'is_recurring' => isset($data['is_recurring']) ? intval($data['is_recurring']) : 0,
            'recurring_frequency' => isset($data['recurring_frequency']) ? sanitize_text_field($data['recurring_frequency']) : null,
            'recurring_day' => isset($data['recurring_day']) && $data['recurring_day'] ? intval($data['recurring_day']) : null,
            'recurring_end_date' => !empty($data['recurring_end_date']) ? sanitize_text_field($data['recurring_end_date']) : null,
            'parent_expense_id' => isset($data['parent_expense_id']) && $data['parent_expense_id'] ? intval($data['parent_expense_id']) : null,
            'notes' => isset($data['notes']) ? sanitize_textarea_field($data['notes']) : '',
        );
        
        $result = $wpdb->insert($table, $insert_data);
        
        if ($result) {
            $new_id = $wpdb->insert_id;
            FS_DB_Helper::fix_null_fields($table, $new_id, array(
                'project_id' => $insert_data['project_id'],
                'invoice_id' => $insert_data['invoice_id'],
                'parent_expense_id' => $insert_data['parent_expense_id'],
            ));
            return $new_id;
        }
        
        return false;
    }

    /**
     * Update an expense
     *
     * @param int   $id   Expense ID
     * @param array $data Expense data
     * @return int|false
     */
    public static function update($id, $data) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_expenses';
        
        $update_data = array(
            'project_id' => isset($data['project_id']) && $data['project_id'] ? intval($data['project_id']) : null,
            'expense_date' => sanitize_text_field($data['expense_date']),
            'expense_name' => sanitize_text_field($data['expense_name']),
            'category' => sanitize_text_field($data['category']),
            'amount' => floatval($data['amount']),
            'is_billable' => isset($data['is_billable']) ? intval($data['is_billable']) : 0,
            'is_reimbursable' => isset($data['is_reimbursable']) ? intval($data['is_reimbursable']) : 0,
            'notes' => isset($data['notes']) ? sanitize_textarea_field($data['notes']) : '',
        );
        
        // Handle recurring expense fields
        if (isset($data['is_recurring'])) {
            $update_data['is_recurring'] = intval($data['is_recurring']);
        }
        if (array_key_exists('recurring_frequency', $data)) {
            $update_data['recurring_frequency'] = $data['recurring_frequency'] ? sanitize_text_field($data['recurring_frequency']) : null;
        }
        if (array_key_exists('recurring_day', $data)) {
            $update_data['recurring_day'] = $data['recurring_day'] ? intval($data['recurring_day']) : null;
        }
        if (array_key_exists('recurring_end_date', $data)) {
            $update_data['recurring_end_date'] = !empty($data['recurring_end_date']) ? sanitize_text_field($data['recurring_end_date']) : null;
        }
        
        // Handle receipt data if provided
        if (isset($data['receipt_id'])) {
            $update_data['receipt_id'] = intval($data['receipt_id']);
        }
        if (isset($data['receipt_filename'])) {
            $update_data['receipt_filename'] = sanitize_text_field($data['receipt_filename']);
        }
        if (isset($data['receipt_path'])) {
            $update_data['receipt_path'] = sanitize_text_field($data['receipt_path']);
        }
        if (isset($data['receipt_url'])) {
            $update_data['receipt_url'] = esc_url_raw($data['receipt_url']);
        }
        if (isset($data['receipt_mime_type'])) {
            $update_data['receipt_mime_type'] = sanitize_text_field($data['receipt_mime_type']);
        }
        if (isset($data['receipt_file_size'])) {
            $update_data['receipt_file_size'] = intval($data['receipt_file_size']);
        }
        if (isset($data['receipt_upload_date'])) {
            $update_data['receipt_upload_date'] = sanitize_text_field($data['receipt_upload_date']);
        }
        
        // 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;
            unset($update_data['project_id']);
        }
        
        $result = $wpdb->update(
            $table,
            $update_data,
            array('id' => $id),
            null,
            array('%d')
        );
        
        // Fix NULL foreign keys
        if (!empty($nullable_fks)) {
            FS_DB_Helper::fix_null_fields($table, $id, $nullable_fks);
        }
        
        return $result;
    }

    /**
     * Delete an expense
     *
     * @param int $id Expense ID
     * @return int|false
     */
    public static function delete($id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_expenses';
        
        return $wpdb->delete($table, array('id' => $id), array('%d'));
    }

    /**
     * Get total expenses
     *
     * @param string $start_date Optional start date
     * @param string $end_date   Optional end date
     * @return float
     */
    public static function get_total($start_date = null, $end_date = null) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_expenses';
        $where = array('1=1');
        
        if ($start_date) {
            $where[] = $wpdb->prepare('expense_date >= %s', $start_date);
        }
        if ($end_date) {
            $where[] = $wpdb->prepare('expense_date <= %s', $end_date);
        }
        
        $where_clause = implode(' AND ', $where);
        
        return (float) $wpdb->get_var("SELECT SUM(amount) FROM {$table} WHERE {$where_clause}");
    }

    /**
     * Get expenses by category
     *
     * @param string $start_date Optional start date
     * @param string $end_date   Optional end date
     * @return array
     */
    public static function get_by_category($start_date = null, $end_date = null) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_expenses';
        $where = array('1=1');
        
        if ($start_date) {
            $where[] = $wpdb->prepare('expense_date >= %s', $start_date);
        }
        if ($end_date) {
            $where[] = $wpdb->prepare('expense_date <= %s', $end_date);
        }
        
        $where_clause = implode(' AND ', $where);
        
        return $wpdb->get_results(
            "SELECT category, SUM(amount) as total, COUNT(*) as count 
             FROM {$table} 
             WHERE {$where_clause} 
             GROUP BY category 
             ORDER BY total DESC"
        );
    }

    /**
     * Get expense categories (tax deduction categories)
     *
     * @return array
     */
    public static function get_categories() {
        return array(
            'Advertising & Marketing' => __('Advertising & Marketing', 'freelance-studio'),
            'Bank Fees' => __('Bank Fees', 'freelance-studio'),
            'Car & Truck Expenses' => __('Car & Truck Expenses', 'freelance-studio'),
            'Commissions & Fees' => __('Commissions & Fees', 'freelance-studio'),
            'Contract Labor' => __('Contract Labor', 'freelance-studio'),
            'Depreciation' => __('Depreciation', 'freelance-studio'),
            'Education & Training' => __('Education & Training', 'freelance-studio'),
            'Equipment' => __('Equipment', 'freelance-studio'),
            'Insurance' => __('Insurance', 'freelance-studio'),
            'Interest' => __('Interest', 'freelance-studio'),
            'Legal & Professional Services' => __('Legal & Professional Services', 'freelance-studio'),
            'Meals & Entertainment' => __('Meals & Entertainment', 'freelance-studio'),
            'Office Expenses' => __('Office Expenses', 'freelance-studio'),
            'Rent or Lease' => __('Rent or Lease', 'freelance-studio'),
            'Repairs & Maintenance' => __('Repairs & Maintenance', 'freelance-studio'),
            'Software & Subscriptions' => __('Software & Subscriptions', 'freelance-studio'),
            'Supplies' => __('Supplies', 'freelance-studio'),
            'Taxes & Licenses' => __('Taxes & Licenses', 'freelance-studio'),
            'Travel' => __('Travel', 'freelance-studio'),
            'Utilities' => __('Utilities', 'freelance-studio'),
            'Other Expenses' => __('Other Expenses', 'freelance-studio'),
        );
    }

    /**
     * Get receipt info for expense
     *
     * @param int $id Expense ID
     * @return array|false
     */
    public static function get_receipt_info($id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_expenses';
        
        $receipt = $wpdb->get_row($wpdb->prepare(
            "SELECT receipt_id, receipt_filename, receipt_path, receipt_url, 
                    receipt_mime_type, receipt_file_size, receipt_upload_date 
             FROM {$table} 
             WHERE id = %d AND receipt_id IS NOT NULL",
            $id
        ));
        
        if (!$receipt) {
            return false;
        }
        
        return array(
            'attachment_id' => $receipt->receipt_id,
            'filename' => $receipt->receipt_filename,
            'file_path' => $receipt->receipt_path,
            'file_url' => $receipt->receipt_url,
            'mime_type' => $receipt->receipt_mime_type,
            'file_size' => $receipt->receipt_file_size,
            'upload_date' => $receipt->receipt_upload_date
        );
    }

    /**
     * Update expense receipt
     *
     * @param int   $id           Expense ID
     * @param array $receipt_data Receipt data
     * @return bool
     */
    public static function update_receipt($id, $receipt_data) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_expenses';
        
        return $wpdb->update(
            $table,
            array(
                'receipt_id' => intval($receipt_data['attachment_id']),
                'receipt_filename' => sanitize_text_field($receipt_data['filename']),
                'receipt_path' => sanitize_text_field($receipt_data['file_path']),
                'receipt_url' => esc_url_raw($receipt_data['file_url']),
                'receipt_mime_type' => sanitize_text_field($receipt_data['mime_type']),
                'receipt_file_size' => intval($receipt_data['file_size']),
                'receipt_upload_date' => current_time('mysql')
            ),
            array('id' => $id),
            array('%d', '%s', '%s', '%s', '%s', '%d', '%s'),
            array('%d')
        ) !== false;
    }

    /**
     * Remove receipt from expense
     *
     * @param int $id Expense ID
     * @return bool
     */
    public static function remove_receipt($id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_expenses';
        
        return $wpdb->update(
            $table,
            array(
                'receipt_id' => null,
                'receipt_filename' => null,
                'receipt_path' => null,
                'receipt_url' => null,
                'receipt_mime_type' => null,
                'receipt_file_size' => null,
                'receipt_upload_date' => null
            ),
            array('id' => $id)
        ) !== false;
    }

    /**
     * Get unbilled expenses for a project
     *
     * @param int $project_id Project ID
     * @return array
     */
    public static function get_unbilled_by_project($project_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_expenses';
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$table} 
             WHERE project_id = %d 
               AND is_billable = 1 
               AND invoice_id IS NULL 
             ORDER BY expense_date ASC",
            $project_id
        ));
    }

    /**
     * Mark expenses as invoiced
     *
     * @param array $expense_ids Array of expense IDs
     * @param int   $invoice_id  Invoice ID
     * @return bool
     */
    public static function mark_invoiced($expense_ids, $invoice_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_expenses';
        $ids = array_map('intval', $expense_ids);
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        
        return $wpdb->query($wpdb->prepare(
            "UPDATE {$table} SET invoice_id = %d WHERE id IN ($placeholders)",
            array_merge(array($invoice_id), $ids)
        )) !== false;
    }

    /**
     * Unmark expenses as invoiced (remove invoice_id)
     *
     * @param array $expense_ids Array of expense IDs
     * @return bool
     */
    public static function unmark_invoiced($expense_ids) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_expenses';
        $ids = array_map('intval', $expense_ids);
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        
        return $wpdb->query($wpdb->prepare(
            "UPDATE {$table} SET invoice_id = NULL WHERE id IN ($placeholders)",
            $ids
        )) !== false;
    }

    /**
     * Get monthly expense totals
     *
     * @param int $year Year
     * @return array
     */
    public static function get_monthly_totals($year) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_expenses';
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT MONTH(expense_date) as month, SUM(amount) as total 
             FROM {$table} 
             WHERE YEAR(expense_date) = %d 
             GROUP BY MONTH(expense_date) 
             ORDER BY month ASC",
            $year
        ));
    }

    /**
     * Count expenses
     *
     * @return int
     */
    public static function count($args = array()) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_expenses';
        $where = array('1=1');
        
        if (!empty($args['project_id'])) {
            $where[] = $wpdb->prepare('project_id = %d', $args['project_id']);
        }
        
        if (!empty($args['client_id'])) {
            $where[] = $wpdb->prepare('client_id = %d', $args['client_id']);
        }
        
        if (!empty($args['category'])) {
            $where[] = $wpdb->prepare('category = %s', $args['category']);
        }
        
        if (!empty($args['start_date'])) {
            $where[] = $wpdb->prepare('expense_date >= %s', $args['start_date']);
        }
        
        if (!empty($args['end_date'])) {
            $where[] = $wpdb->prepare('expense_date <= %s', $args['end_date']);
        }
        
        $where_clause = implode(' AND ', $where);
        
        return (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table} WHERE {$where_clause}"); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
    }

    /**
     * Get recurring expense frequencies
     *
     * @return array
     */
    public static function get_recurring_frequencies() {
        return array(
            'monthly' => __('Monthly', 'freelance-studio'),
            'quarterly' => __('Quarterly', 'freelance-studio'),
            'yearly' => __('Yearly', 'freelance-studio'),
        );
    }

    /**
     * Get all recurring expenses (parent records only)
     *
     * @return array
     */
    public static function get_recurring_expenses() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_expenses';
        $projects_table = $wpdb->prefix . 'fs_projects';
        
        return $wpdb->get_results(
            "SELECT e.*, p.name as project_name 
             FROM {$table} e 
             LEFT JOIN {$projects_table} p ON e.project_id = p.id 
             WHERE e.is_recurring = 1 AND e.parent_expense_id IS NULL 
             ORDER BY e.expense_name ASC"
        );
    }

    /**
     * Get child expenses from a recurring parent
     *
     * @param int $parent_id Parent expense ID
     * @return array
     */
    public static function get_recurring_children($parent_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_expenses';
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$table} WHERE parent_expense_id = %d ORDER BY expense_date DESC",
            $parent_id
        ));
    }

    /**
     * Generate recurring expense entries
     * Called by cron to create new expense entries for recurring expenses
     *
     * @return int Number of expenses created
     */
    public static function generate_recurring_entries() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_expenses';
        $today = current_time('Y-m-d');
        $created = 0;
        
        // Get all active recurring expenses
        $recurring = $wpdb->get_results(
            "SELECT * FROM {$table} 
             WHERE is_recurring = 1 
               AND parent_expense_id IS NULL 
               AND (recurring_end_date IS NULL OR recurring_end_date >= '{$today}')"
        );
        
        foreach ($recurring as $expense) {
            // Determine if we need to create a new entry
            $last_child = $wpdb->get_row($wpdb->prepare(
                "SELECT expense_date FROM {$table} 
                 WHERE parent_expense_id = %d 
                 ORDER BY expense_date DESC LIMIT 1",
                $expense->id
            ));
            
            // Calculate next due date based on frequency
            $start_date = $last_child ? $last_child->expense_date : $expense->expense_date;
            $next_date = self::calculate_next_recurring_date($start_date, $expense->recurring_frequency, $expense->recurring_day);
            
            // If next date is today or past, create the entry
            if ($next_date && $next_date <= $today) {
                $new_expense = array(
                    'project_id' => $expense->project_id,
                    'expense_date' => $next_date,
                    'expense_name' => $expense->expense_name,
                    'category' => $expense->category,
                    'amount' => $expense->amount,
                    'is_billable' => $expense->is_billable,
                    'is_reimbursable' => $expense->is_reimbursable,
                    'is_recurring' => 0,
                    'parent_expense_id' => $expense->id,
                    'notes' => $expense->notes,
                );
                
                if (self::create($new_expense)) {
                    $created++;
                }
            }
        }
        
        return $created;
    }

    /**
     * Calculate the next recurring date
     *
     * @param string $from_date Starting date
     * @param string $frequency Frequency (monthly, quarterly, yearly)
     * @param int|null $day_of_month Specific day of month (null = same as original)
     * @return string|null Next date in Y-m-d format
     */
    public static function calculate_next_recurring_date($from_date, $frequency, $day_of_month = null) {
        if (!$from_date || !$frequency) {
            return null;
        }
        
        $date = new DateTime($from_date);
        
        switch ($frequency) {
            case 'monthly':
                $date->modify('+1 month');
                break;
            case 'quarterly':
                $date->modify('+3 months');
                break;
            case 'yearly':
                $date->modify('+1 year');
                break;
            default:
                return null;
        }
        
        // If specific day is set, adjust to that day
        if ($day_of_month) {
            $last_day_of_month = (int) $date->format('t');
            $target_day = min($day_of_month, $last_day_of_month);
            $date->setDate($date->format('Y'), $date->format('m'), $target_day);
        }
        
        return $date->format('Y-m-d');
    }

    /**
     * Stop recurring expense
     *
     * @param int $id Expense ID
     * @return bool
     */
    public static function stop_recurring($id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_expenses';
        
        return $wpdb->update(
            $table,
            array(
                'is_recurring' => 0,
                'recurring_end_date' => current_time('Y-m-d'),
            ),
            array('id' => $id),
            array('%d', '%s'),
            array('%d')
        ) !== false;
    }
}
