<?php
/**
 * Reporting Controller
 *
 * @package Freelance_Studio
 */

class FS_Reporting_Controller {

    /**
     * Display reporting page
     */
    public function index() {
        $current_year = gmdate('Y');
        $selected_year = isset($_GET['year']) ? intval($_GET['year']) : $current_year;
        
        // Get income/expense data
        $income_data = $this->get_income_by_month($selected_year);
        $expense_data = $this->get_expenses_by_month($selected_year);
        $expense_categories = FS_Expense::get_by_category(
            $selected_year . '-01-01',
            $selected_year . '-12-31'
        );
        
        // Calculate totals
        $total_income = array_sum(array_column($income_data, 'total'));
        $total_expenses = array_sum(array_column($expense_data, 'total'));
        $net_profit = $total_income - $total_expenses;
        
        // Get tax data
        $tax_data = $this->get_tax_data($selected_year);
        
        // Get top clients
        $top_clients = $this->get_top_clients($selected_year);
        
        // Get income by client by month for line chart
        $client_income_by_month = $this->get_client_income_by_month($selected_year);
        
        // Available years for filter
        $available_years = $this->get_available_years();
        
        include JEEB_FS_PLUGIN_DIR . 'includes/views/reporting/index.php';
    }

    /**
     * Get income by month for a year (includes invoice income + manual income)
     */
    private function get_income_by_month($year) {
        global $wpdb;
        
        $invoices_table = $wpdb->prefix . 'fs_invoices';
        $manual_income_table = $wpdb->prefix . 'fs_manual_income';
        
        // Get invoice income
        $invoice_results = $wpdb->get_results($wpdb->prepare(
            "SELECT MONTH(paid_at) as month, SUM(total) as total 
             FROM {$invoices_table} 
             WHERE YEAR(paid_at) = %d AND status = 'paid'
             GROUP BY MONTH(paid_at) 
             ORDER BY month ASC",
            $year
        ));
        
        // Get manual income
        $manual_results = $wpdb->get_results($wpdb->prepare( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
            "SELECT MONTH(income_date) as month, SUM(amount) as total 
             FROM {$manual_income_table} 
             WHERE YEAR(income_date) = %d
             GROUP BY MONTH(income_date) 
             ORDER BY month ASC",
            $year
        ));
        
        // Fill in missing months with zeros
        $data = array();
        for ($i = 1; $i <= 12; $i++) {
            $data[$i] = array('month' => $i, 'total' => 0);
        }
        
        // Add invoice income
        foreach ($invoice_results as $row) {
            $data[$row->month]['total'] = floatval($row->total);
        }
        
        // Add manual income
        foreach ($manual_results as $row) {
            $data[$row->month]['total'] += floatval($row->total);
        }
        
        return array_values($data);
    }

    /**
     * Get expenses by month for a year
     */
    private function get_expenses_by_month($year) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_expenses';
        
        $results = $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
        ));
        
        // Fill in missing months with zeros
        $data = array();
        for ($i = 1; $i <= 12; $i++) {
            $data[$i] = array('month' => $i, 'total' => 0);
        }
        
        foreach ($results as $row) {
            $data[$row->month]['total'] = floatval($row->total);
        }
        
        return array_values($data);
    }

    /**
     * Get tax data for reporting
     */
    /**
     * Get tax data for reporting (public accessor for finances controller)
     */
    public function get_tax_data_public($year) {
        return $this->get_tax_data($year);
    }

    /**
     * Get tax data for reporting
     */
    private function get_tax_data($year) {
        $income = FS_Tax_Calculator::get_ytd_income($year);
        $expenses = FS_Tax_Calculator::get_ytd_expenses($year);
        $annual_deductions = FS_Tax_Calculator::get_annual_deductions($year);
        $state = get_option('fs_tax_state', '');
        $federal_bracket = get_option('fs_federal_tax_bracket', 22);
        
        $tax_calculation = FS_Tax_Calculator::calculate_tax_liability(
            $income,
            $state,
            $federal_bracket,
            $expenses,
            $annual_deductions
        );
        
        $payments = FS_Tax_Calculator::get_quarterly_payments_made($year);
        
        // Get annual deduction details
        $annual_deduction_details = FS_Annual_Deduction::get_by_category($year);
        
        // Get quarterly tax estimates total (same calculation as quarterly-tax-section.php)
        $user_id = get_current_user_id();
        $quarterly_records = FS_Quarterly_Tax::get_year_records($user_id, $year);
        $quarterly_total_estimated = 0;
        foreach ($quarterly_records as $record) {
            $quarterly_total_estimated += $record->estimated_total;
        }
        
        return array(
            'calculation' => $tax_calculation,
            'payments' => $payments,
            'remaining' => $tax_calculation['tax_breakdown']['total_tax'] - $payments['total_paid'],
            'annual_deduction_details' => $annual_deduction_details,
            'quarterly_total_estimated' => $quarterly_total_estimated
        );
    }

    /**
     * Get top clients by revenue
     */
    private function get_client_income_by_month($year) {
        global $wpdb;

        $invoices_table = $wpdb->prefix . 'fs_invoices';
        $clients_table  = $wpdb->prefix . 'fs_clients';

        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT c.id, COALESCE(NULLIF(c.company_name,''), c.name) AS client_name,
                    MONTH(i.paid_at) AS month,
                    SUM(i.total) AS total
             FROM {$invoices_table} i
             JOIN {$clients_table} c ON i.client_id = c.id
             WHERE YEAR(i.paid_at) = %d AND i.status = 'paid'
               AND c.is_internal = 0
             GROUP BY c.id, MONTH(i.paid_at)
             ORDER BY c.id, month",
            $year
        ));

        // Structure: [ client_name => [ 1=>0, 2=>0, … 12=>0 ], … ]
        $result = array();
        foreach ($rows as $row) {
            if (!isset($result[$row->client_name])) {
                $result[$row->client_name] = array_fill(1, 12, 0);
            }
            $result[$row->client_name][(int)$row->month] = (float)$row->total;
        }

        // Keep only top 8 clients by annual total so the chart stays readable
        uasort($result, function($a, $b) {
            return array_sum($b) <=> array_sum($a);
        });

        return array_slice($result, 0, 8, true);
    }

    private function get_top_clients($year, $limit = 5) {
        global $wpdb;
        
        $invoices_table = $wpdb->prefix . 'fs_invoices';
        $clients_table = $wpdb->prefix . 'fs_clients';
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT c.id, c.name, c.company_name, SUM(i.total) as total_revenue, COUNT(i.id) as invoice_count
             FROM {$invoices_table} i
             JOIN {$clients_table} c ON i.client_id = c.id
             WHERE YEAR(i.paid_at) = %d AND i.status = 'paid'
             GROUP BY c.id
             ORDER BY total_revenue DESC
             LIMIT %d",
            $year,
            $limit
        ));
    }

    /**
     * Get available years for reporting
     */
    private function get_available_years() {
        global $wpdb;
        
        $invoices_table = $wpdb->prefix . 'fs_invoices';
        $expenses_table = $wpdb->prefix . 'fs_expenses';
        
        $invoice_years = $wpdb->get_col(
            "SELECT DISTINCT YEAR(invoice_date) as year FROM {$invoices_table} ORDER BY year DESC"
        );
        
        $expense_years = $wpdb->get_col(
            "SELECT DISTINCT YEAR(expense_date) as year FROM {$expenses_table} ORDER BY year DESC"
        );
        
        $years = array_unique(array_merge($invoice_years, $expense_years));
        rsort($years);
        
        // Always include current year
        if (!in_array(gmdate('Y'), $years)) {
            array_unshift($years, gmdate('Y'));
        }
        
        return $years;
    }
}
