<?php
/**
 * Dashboard Controller Class
 *
 * @package Easy_Invoice
 * @subpackage Controllers
 */

namespace EasyInvoice\Controllers;

use EasyInvoice\Constants\PagesSlugs;
use EasyInvoice\Providers\InvoiceServiceProvider;
use EasyInvoice\Providers\ClientServiceProvider;

/**
 * DashboardController handles dashboard functionality
 */
class DashboardController extends BaseController {

    /**
     * Initialize the controller
     */
    public function init() {
        // Add necessary initialization here
    }
    
    /**
     * Display method implementation
     * 
     * @param array $args Display arguments
     */
    public function display(array $args = []) {
        $page = isset($args['page']) ? $args['page'] : '';
        
        switch ($page) {
            case PagesSlugs::DASHBOARD:
                $this->displayDashboardPage();
                break;
                
            default:
                $this->displayDashboardPage();
                break;
        }
    }
    
    /**
     * Display dashboard page
     */
    protected function displayDashboardPage() {
        // Check user capability
        $error = $this->checkCapability('ei_view_dashboard');
        if (is_wp_error($error)) {
            wp_die(esc_html($error->get_error_message()));
        }
        
        // Get data for dashboard
        $invoice_repository = InvoiceServiceProvider::getInvoiceRepository();
        $client_repository = ClientServiceProvider::getClientRepository();
        
        // Get counts
        // Counts are SQL counts; only the invoices that can still carry a
        // balance are loaded as models. Loading every invoice (three times)
        // put a 3,000-invoice site at ~40 seconds per dashboard view.
        $total_invoices  = (int) $invoice_repository->count();
        $paid_invoices   = (int) $invoice_repository->count(['meta_key' => '_easy_invoice_status', 'meta_value' => 'paid']); // phpcs:ignore WordPress.DB.SlowDBQuery
        // "Unpaid" is every issued invoice still carrying a balance — a sent
        // invoice is 'available' (or 'partial') until paid, so counting the
        // literal 'unpaid' / 'overdue' statuses showed 0 on nearly every site.
        // Outstanding balances come from SQL over the persisted totals; loading
        // every open invoice as a model does not scale past a few thousand.
        $outstanding      = \EasyInvoice\Services\InvoiceTotalsCache::outstanding();
        $unpaid_invoices  = $outstanding['count'];
        $overdue_invoices = $outstanding['overdue_count'];
        
        $total_clients = (int) (new \WP_User_Query(['role__not_in' => ['Administrator'], 'fields' => 'ID', 'number' => 1, 'count_total' => true]))->get_total();
        $active_clients = $this->getActiveClientCount($client_repository, $invoice_repository);
        
        // Get recent invoices
        $recent_invoices = $this->getRecentInvoices($invoice_repository);
        
        // Get revenue data
        $total_revenue = $this->getTotalRevenue($invoice_repository);
        $monthly_revenue = $this->getMonthlyRevenue($invoice_repository);
        
        // Display the template
        $this->displayTemplate(
            EASY_INVOICE_PLUGIN_DIR . 'templates/dashboard-page.php',
            [
                'total_invoices' => $total_invoices,
                'paid_invoices' => $paid_invoices,
                'unpaid_invoices' => $unpaid_invoices,
                'overdue_invoices' => $overdue_invoices,
                'unpaid_amount' => $outstanding['amount'],
                'overdue_amount' => $outstanding['overdue_amount'],
                'total_clients' => $total_clients,
                'active_clients' => $active_clients,
                'recent_invoices' => $recent_invoices,
                'total_revenue' => $total_revenue,
                'monthly_revenue' => $monthly_revenue
            ]
        );
    }
    
    /**
     * Get active client count
     *
     * @param object $client_repository
     * @param object $invoice_repository
     * @return int Count of active clients
     */
    private function getActiveClientCount($client_repository, $invoice_repository) {
        // Distinct clients billed in the last 90 days — one query instead of
        // one full invoice load per client.
        global $wpdb;
        $since = wp_date('Y-m-d', strtotime('-90 days'));
        $count = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT c.meta_value)
             FROM {$wpdb->posts} p
             INNER JOIN {$wpdb->postmeta} c ON c.post_id = p.ID AND c.meta_key = '_easy_invoice_client_id'
             INNER JOIN {$wpdb->postmeta} d ON d.post_id = p.ID AND d.meta_key = '_easy_invoice_issue_date'
             WHERE p.post_type = %s AND p.post_status = 'publish'
               AND c.meta_value <> '' AND c.meta_value <> '0' AND d.meta_value >= %s",
            \EasyInvoice\Constants\PostTypes::EASY_INVOICE_POST_TYPE,
            $since
        ));
        return (int) $count;
    }

    /**
     * Get recent invoices
     *
     * @param object $invoice_repository
     * @return array Recent invoices
     */
    private function getRecentInvoices($invoice_repository) {
        try {
            // Five most recently issued: let the database sort and limit.
            return $invoice_repository->all([
                'posts_per_page' => 5,
                'meta_key'       => '_easy_invoice_issue_date', // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key
                'orderby'        => ['meta_value' => 'DESC', 'ID' => 'DESC'],
            ]);
        } catch (\Exception $e) {
            // Log the error and return an empty array
            return [];
        }
    }
    
    /**
     * Get total revenue from paid invoices
     *
     * @param object $invoice_repository
     * @return array Total revenue by currency
     */
    private function getTotalRevenue($invoice_repository) {
        // SQL-aggregate path. Replaces the previous "load every payment post +
        // call get_post_meta() 2× per row in PHP" approach with a single
        // GROUP BY query. On a site with 50K payments this cuts ~150K
        // postmeta queries to 1 SQL aggregate.
        //
        // Returns the SAME data structure as the legacy path:
        //   [ 'USD' => ['amount' => 12345.67, 'symbol' => '$'], ... ]
        //
        // Filter `easy_invoice_dashboard_use_sql_aggregates` lets admins
        // revert to the legacy PHP path if any production-data edge case
        // surfaces unexpected numbers.
        $use_sql = (bool) apply_filters('easy_invoice_dashboard_use_sql_aggregates', true);
        if ($use_sql) {
            $sql_result = $this->getTotalRevenueViaSql();
            if ($sql_result !== null) {
                return $sql_result;
            }
        }
        return $this->getTotalRevenueViaPhpFallback();
    }

    /**
     * Single-query revenue aggregation. Returns null on hard DB failure so
     * the caller can fall back to the PHP path; returns an empty array
     * legitimately when there are zero matching payments.
     *
     * @return array<string,array{amount:float,symbol:string}>|null
     */
    private function getTotalRevenueViaSql(): ?array {
        global $wpdb;
        $global_currency = strtoupper((string) get_option('easy_invoice_currency_code', 'USD'));

        // SQL inputs:
        //   m_stat → payment status (must be completed / approved / paid)
        //   m_amt  → payment amount (CAST to DECIMAL so the SUM ignores junk)
        //   m_cur  → payment currency (LEFT JOIN — older payments may not have it)
        //
        // The IFNULL/NULLIF chain collapses empty-string and the literal
        // 'global' sentinel to the site default, matching the PHP path's
        // `empty($currency_code) || $currency_code === 'global'` check.
        $sql = $wpdb->prepare(
            "SELECT
                UPPER(IFNULL(NULLIF(NULLIF(m_cur.meta_value, ''), 'global'), %s)) AS currency_code,
                SUM(CAST(m_amt.meta_value AS DECIMAL(20,4)))                       AS total_amount
             FROM {$wpdb->posts} p
             INNER JOIN {$wpdb->postmeta} m_stat ON m_stat.post_id = p.ID AND m_stat.meta_key = '_status'
             INNER JOIN {$wpdb->postmeta} m_amt  ON m_amt.post_id  = p.ID AND m_amt.meta_key  = '_amount'
             LEFT  JOIN {$wpdb->postmeta} m_cur  ON m_cur.post_id  = p.ID AND m_cur.meta_key  = '_currency'
             WHERE p.post_type   = 'easy_invoice_payment'
               AND p.post_status = 'publish'
               AND m_stat.meta_value IN ('completed','approved','paid')
               AND CAST(m_amt.meta_value AS DECIMAL(20,4)) > 0
             GROUP BY currency_code",
            $global_currency
        );
        $rows = $wpdb->get_results($sql); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is built with $wpdb->prepare() above.
        if ($rows === null) {
            return null; // hard DB error → caller falls back to PHP path
        }

        $out = [];
        foreach ($rows as $row) {
            $code = (string) $row->currency_code;
            $out[$code] = [
                'amount' => (float) $row->total_amount,
                'symbol' => \EasyInvoice\Helpers\CurrencyHelper::getCurrencySymbol($code),
            ];
        }
        return $out;
    }

    /**
     * Legacy PHP aggregation path. Kept verbatim from the original
     * implementation so the filter-off fallback returns identical numbers
     * to what the dashboard always rendered before the SQL refactor.
     *
     * @return array<string,array{amount:float,symbol:string}>
     */
    private function getTotalRevenueViaPhpFallback() {
        try {
            $payments = get_posts([
                'post_type' => 'easy_invoice_payment',
                'post_status' => 'publish',
                'meta_query' => [
                    [
                        'key' => '_status',
                        'value' => ['completed', 'approved', 'paid'],
                        'compare' => 'IN'
                    ]
                ],
                'numberposts' => -1
            ]);

            $revenue_by_currency = [];
            $global_currency = get_option('easy_invoice_currency_code', 'USD');

            foreach ($payments as $payment) {
                try {
                    $payment_amount = get_post_meta($payment->ID, '_amount', true);
                    if (!is_numeric($payment_amount) || $payment_amount <= 0) {
                        continue;
                    }

                    $currency_code = get_post_meta($payment->ID, '_currency', true);
                    if (empty($currency_code) || $currency_code === 'global') {
                        $currency_code = $global_currency;
                    }
                    $currency_code = strtoupper($currency_code);

                    $currency_symbol = \EasyInvoice\Helpers\CurrencyHelper::getCurrencySymbol($currency_code);

                    if (!isset($revenue_by_currency[$currency_code])) {
                        $revenue_by_currency[$currency_code] = [
                            'amount' => 0,
                            'symbol' => $currency_symbol
                        ];
                    }

                    $revenue_by_currency[$currency_code]['amount'] += $payment_amount;
                } catch (\Exception $e) {
                    continue;
                }
            }

            return $revenue_by_currency;
        } catch (\Exception $e) {
            return [];
        }
    }
    
    /**
     * Get monthly revenue data for charts
     *
     * @param object $invoice_repository
     * @return array Monthly revenue data
     */
    private function getMonthlyRevenue($invoice_repository) {
        // Initialize months for the last 12 months (rolling period)
        $monthly_revenue = array();

        // Get the current date and go back 11 months to create a 12-month period
        $current_date = new \DateTime();
        $start_date = clone $current_date;
        $start_date->modify('-11 months');

        // Initialize all 12 months
        for ($i = 0; $i < 12; $i++) {
            $month_date = clone $start_date;
            $month_date->modify("+{$i} months");
            $month_name = $month_date->format('M Y');
            $monthly_revenue[$month_name] = [];
        }

        // SQL-aggregate path. Same logic as the chart's PHP loop but executed
        // as a single GROUP BY in MySQL. Filterable via
        // `easy_invoice_dashboard_use_sql_aggregates` (shared with
        // getTotalRevenue — flip both at once).
        if (apply_filters('easy_invoice_dashboard_use_sql_aggregates', true)) {
            $sql_buckets = $this->getMonthlyRevenueViaSql($start_date, $current_date);
            if ($sql_buckets !== null) {
                // Merge SQL aggregates into the pre-initialized 12-month skeleton
                // so empty months stay empty (instead of disappearing from the chart).
                foreach ($sql_buckets as $month_label => $by_currency) {
                    if (isset($monthly_revenue[$month_label])) {
                        $monthly_revenue[$month_label] = $by_currency;
                    }
                }
                return $monthly_revenue;
            }
        }

        try {
            // Get all completed payments (including different statuses that might be considered completed)
            $payments = get_posts([
                'post_type' => 'easy_invoice_payment',
                'post_status' => 'publish',
                'meta_query' => [
                    [
                        'key' => '_status',
                        'value' => ['completed', 'approved', 'paid'],
                        'compare' => 'IN'
                    ]
                ],
                'numberposts' => -1
            ]);

            // If no completed payments found, try to get any payments with amounts
            if (empty($payments)) {
                $payments = get_posts([
                    'post_type' => 'easy_invoice_payment',
                    'post_status' => 'publish',
                    'meta_query' => [
                        [
                            'key' => '_amount',
                            'value' => '0',
                            'compare' => '>'
                        ]
                    ],
                    'numberposts' => -1
                ]);
            }

            $global_currency = get_option('easy_invoice_currency_code', 'USD');
            
            // Calculate revenue for each month by currency
            foreach ($payments as $payment) {
                try {
                    $payment_date = get_post_meta($payment->ID, '_payment_date', true);
                    if (!$payment_date) {
                        continue;
                    }
                    
                    $payment_date_obj = new \DateTime($payment_date);
                    if ($payment_date_obj >= $start_date && $payment_date_obj <= $current_date) {
                        $month = $payment_date_obj->format('M Y');
                        $payment_amount = get_post_meta($payment->ID, '_amount', true);
                        
                        if (!is_numeric($payment_amount)) {
                            continue;
                        }
                        
                        // Get currency information from payment
                        $currency_code = get_post_meta($payment->ID, '_currency', true);
                        if (empty($currency_code) || $currency_code === 'global') {
                            $currency_code = $global_currency;
                        }
                        $currency_code = strtoupper($currency_code);
                        
                        $currency_symbol = \EasyInvoice\Helpers\CurrencyHelper::getCurrencySymbol($currency_code);
                            
                        // Initialize currency for this month if not exists
                        if (!isset($monthly_revenue[$month][$currency_code])) {
                            $monthly_revenue[$month][$currency_code] = [
                                'amount' => 0,
                                'symbol' => $currency_symbol
                            ];
                            }
                        
                        $monthly_revenue[$month][$currency_code]['amount'] += $payment_amount;
                    }
                } catch (\Exception $e) {
                    // Log the error and continue with the next payment
                    continue;
                }
            }
            return $monthly_revenue;
        } catch (\Exception $e) {
            // Log the error and return empty monthly revenue
            return $monthly_revenue;
        }
    }

    /**
     * Single-query monthly revenue aggregation. Returns null on hard DB
     * failure so the caller falls back to the PHP path; returns an array
     * keyed by 'Mon YYYY' month label (matching the PHP path's format).
     *
     * The query mirrors getTotalRevenueViaSql but adds DATE_FORMAT
     * grouping on the _payment_date meta. Date range is enforced in SQL
     * via lexicographic comparison on the ISO date string, which works
     * because _payment_date is stored as 'YYYY-MM-DD' (sortable).
     *
     * @param \DateTime $start_date
     * @param \DateTime $current_date
     * @return array<string,array<string,array{amount:float,symbol:string}>>|null
     */
    private function getMonthlyRevenueViaSql(\DateTime $start_date, \DateTime $current_date): ?array {
        global $wpdb;
        $global_currency = strtoupper((string) get_option('easy_invoice_currency_code', 'USD'));
        $start_iso       = $start_date->format('Y-m-01');
        $end_iso         = $current_date->format('Y-m-d');

        $sql = $wpdb->prepare(
            "SELECT
                DATE_FORMAT(m_date.meta_value, '%%b %%Y')                              AS month_label,
                MIN(m_date.meta_value)                                                  AS month_sort,
                UPPER(IFNULL(NULLIF(NULLIF(m_cur.meta_value, ''), 'global'), %s))       AS currency_code,
                SUM(CAST(m_amt.meta_value AS DECIMAL(20,4)))                            AS total_amount
             FROM {$wpdb->posts} p
             INNER JOIN {$wpdb->postmeta} m_stat ON m_stat.post_id = p.ID AND m_stat.meta_key = '_status'
             INNER JOIN {$wpdb->postmeta} m_amt  ON m_amt.post_id  = p.ID AND m_amt.meta_key  = '_amount'
             INNER JOIN {$wpdb->postmeta} m_date ON m_date.post_id = p.ID AND m_date.meta_key = '_payment_date'
             LEFT  JOIN {$wpdb->postmeta} m_cur  ON m_cur.post_id  = p.ID AND m_cur.meta_key  = '_currency'
             WHERE p.post_type   = 'easy_invoice_payment'
               AND p.post_status = 'publish'
               AND m_stat.meta_value IN ('completed','approved','paid')
               AND CAST(m_amt.meta_value AS DECIMAL(20,4)) > 0
               AND m_date.meta_value <> ''
               AND m_date.meta_value >= %s
               AND m_date.meta_value <= %s
             GROUP BY month_label, currency_code
             ORDER BY month_sort ASC",
            $global_currency,
            $start_iso,
            $end_iso
        );
        $rows = $wpdb->get_results($sql); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is built with $wpdb->prepare() above.
        if ($rows === null) {
            return null; // hard DB error → caller falls back to PHP path
        }

        $buckets = [];
        foreach ($rows as $row) {
            $month = (string) $row->month_label;
            $code  = (string) $row->currency_code;
            if (!isset($buckets[$month])) {
                $buckets[$month] = [];
            }
            $buckets[$month][$code] = [
                'amount' => (float) $row->total_amount,
                'symbol' => \EasyInvoice\Helpers\CurrencyHelper::getCurrencySymbol($code),
            ];
        }
        return $buckets;
    }
}