<?php
// Plugin Check / PHPCS: category-level exclusions with reasons.
// Line-level findings in this file are handled individually below.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- PaintOps keeps its records in its own custom tables (built by paintops_table()); $wpdb is the supported API for plugin-owned tables, there is no core CRUD alternative, and results are request-scoped.
/**
 * Included File: Quote Stats Widget
 *
 * A small "how's the quoting side of the business doing" card on the
 * main Jobs page, next to Today's Crew / Needs Your Attention. Two
 * figures:
 *
 *   1. Quote Acceptance Rate, all-time, computed per distinct JOB
 *      quoted, not per estimate row. Each save on the New/Edit Quote
 *      screen creates a new cs_estimates revision (see estimates.php),
 *      so a job that took 3 rounds of edits before the customer said
 *      yes would otherwise look like "2 rejected, 1 accepted" and drag
 *      the rate down for no real reason, it's one accepted quote, not
 *      three quotes with a 33% success rate. Counting DISTINCT jobID
 *      avoids that.
 *
 *   2. # of Quotes, this year vs last year, same DISTINCT-jobID logic,
 *      grouped by the year the quote was created (cs_estimates.createdOn),
 *      so it reads as "how many jobs did I quote" rather than "how many
 *      times did I hit save."
 *
 * Gated behind paintops_user_can_view_financials(), same as the
 * Financials/Profits pages and the "missing expenses" alert in
 * dashboard-alerts.php, since win-rate is business performance data,
 * not day-to-day operational info a painter/helper needs.
 */

if (!defined('ABSPATH')) exit;

/**
 * Raw numbers behind the widget, exposed as its own function in case
 * another report wants these later (e.g. a future Profits page tile)
 * without duplicating the queries.
 */
function paintops_get_quote_stats(): array {
    global $wpdb;
    $estimates_table = paintops_table('estimates');

    $this_year = (int) current_time('Y');
    $last_year = $this_year - 1;

    $total_quoted_jobs = (int) $wpdb->get_var( $wpdb->prepare("SELECT COUNT(DISTINCT jobID) FROM %i", $estimates_table) );
    // A request counts as accepted when its document was accepted online
    // OR the record was won into a real Job (jobType 2), a phone-call or
    // handshake win never touches the online Accept button but is still a
    // win (Mike 2026-07-22: two won, completed, paid-in-full jobs read as
    // 0% acceptance). The win actions now also stamp the estimate row
    // (paintops_mark_estimate_accepted_on_win), the join keeps history
    // from before that stamping honest.
    $jobs_table = paintops_table('jobs');
    $accepted_jobs = (int) $wpdb->get_var( $wpdb->prepare("SELECT COUNT(DISTINCT e.jobID)
         FROM %i e
         LEFT JOIN %i j ON j.id = e.jobID
         WHERE e.accepted = 1 OR j.jobType = 2", $estimates_table, $jobs_table) );
    $acceptance_rate = $total_quoted_jobs > 0 ? round(($accepted_jobs / $total_quoted_jobs) * 100, 1) : null;

    // Full per-year breakdown (one row per year the business has quoted in),
    // newest first: jobs quoted that year, how many were accepted, and the
    // per-year acceptance rate. Same DISTINCT-jobID logic as the totals so a
    // multi-revision quote counts once. Grouped by the year the quote was
    // created (createdOn).
    $by_year = [];
    // Year basis is the request's SCHEDULED date (jobs.startYear), NOT
    // when its quote document was created (Mike 2026-07-22: bulk-loading
    // 2025 history made every request read as 2026, while the Profits
    // page, which buckets by schedule, was right; new users loading old
    // jobs will hit the same thing). Falls back to the quote's createdOn
    // year only when the request has no scheduled date.
    $rows = $wpdb->get_results( $wpdb->prepare("SELECT (CASE WHEN COALESCE(j.startYear, 0) BETWEEN 2000 AND 2100 THEN j.startYear ELSE YEAR(e.createdOn) END) AS yr,
                COUNT(DISTINCT e.jobID) AS quotes,
                COUNT(DISTINCT CASE WHEN e.accepted = 1 OR j.jobType = 2 THEN e.jobID END) AS accepted
         FROM %i e
         LEFT JOIN %i j ON j.id = e.jobID
         GROUP BY yr
         HAVING yr > 0
         ORDER BY yr DESC", $estimates_table, $jobs_table), ARRAY_A ) ?: [];
    foreach ($rows as $r) {
        $yr_quotes   = (int) $r['quotes'];
        $yr_accepted = (int) $r['accepted'];
        $by_year[] = [
            'year'     => (int) $r['yr'],
            'quotes'   => $yr_quotes,
            'accepted' => $yr_accepted,
            'rate'     => $yr_quotes > 0 ? round(($yr_accepted / $yr_quotes) * 100, 1) : null,
        ];
    }

    // Derived from the by-year rows so they can never use a different
    // year basis than the table shows.
    $quotes_this_year = 0;
    $quotes_last_year = 0;
    foreach ($by_year as $byr) {
        if ($byr['year'] === $this_year) { $quotes_this_year = $byr['quotes']; }
        if ($byr['year'] === $last_year) { $quotes_last_year = $byr['quotes']; }
    }

    return [
        'this_year'         => $this_year,
        'last_year'         => $last_year,
        'quotes_this_year'  => $quotes_this_year,
        'quotes_last_year'  => $quotes_last_year,
        'total_quoted_jobs' => $total_quoted_jobs,
        'accepted_jobs'     => $accepted_jobs,
        'acceptance_rate'   => $acceptance_rate, // null when nothing has been quoted yet
        'by_year'           => $by_year,
    ];
}

function paintops_render_quote_stats_widget(): string {
    if (!function_exists('paintops_user_can_view_financials') || !paintops_user_can_view_financials()) {
        return '';
    }

    $stats = paintops_get_quote_stats();

    // Nothing quoted at all yet (brand-new install), don't show an
    // empty/zero card, matching the "invisible until there's something
    // to show" convention used by the other dashboard widgets.
    if ($stats['total_quoted_jobs'] === 0) {
        return '';
    }

    $rate_display = $stats['acceptance_rate'] !== null
        ? number_format((float) $stats['acceptance_rate'], 1) . '%'
        : '-';
    $rate_class = '';
    if ($stats['acceptance_rate'] !== null) {
        $rate_class = $stats['acceptance_rate'] >= 50 ? 'cs-fin-positive' : 'cs-fin-negative';
    }

    ob_start();
    ?>
    <div class="cs-financial-box">
        <div class="job-form-section" style="margin-bottom:12px;">Request Performance</div>
        <div class="cs-fin-summary-grid">
            <div class="cs-fin-stat cs-fin-primary <?php echo esc_attr($rate_class); ?>">
                <span>Request Acceptance Rate (all-time)</span>
                <strong><?php echo esc_html($rate_display); ?></strong>
            </div>
            <div class="cs-fin-stat">
                <span>Requests Quoted (all-time)</span>
                <strong><?php echo esc_html((string) $stats['total_quoted_jobs']); ?></strong>
            </div>
            <div class="cs-fin-stat">
                <span>Accepted (all-time)</span>
                <strong><?php echo esc_html((string) $stats['accepted_jobs']); ?></strong>
            </div>
        </div>

        <?php if (!empty($stats['by_year'])): ?>
        <div class="cs-fin-tablewrap" style="margin-top:14px;">
        <table class="cs-fin-table">
            <thead>
                <tr>
                    <th>Year</th>
                    <th style="text-align:right;">Requests</th>
                    <th style="text-align:right;">Accepted</th>
                    <th style="text-align:right;">Rate</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($stats['by_year'] as $yr): ?>
                    <tr>
                        <td><?php echo esc_html((string) $yr['year']); ?></td>
                        <td style="text-align:right;"><?php echo esc_html((string) $yr['quotes']); ?></td>
                        <td style="text-align:right;"><?php echo esc_html((string) $yr['accepted']); ?></td>
                        <td style="text-align:right;"><?php echo wp_kses($yr['rate'] !== null ? esc_html(number_format((float) $yr['rate'], 1) . '%') : '-', paintops_kses_allowed_html(), paintops_kses_protocols()); ?></td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
        </div>
        <?php endif; ?>

        <p class="description" style="margin:10px 0 0;">Counted once per request (a request with several document revisions before it was accepted still counts once). Each year uses the request&rsquo;s scheduled date (or, if it was never scheduled, when its quote was created).</p>
    </div>
    <?php
    return ob_get_clean();
}
