<?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.
// phpcs:disable WordPress.DateTime.RestrictedFunctions.date_date -- date() here is deliberately paired with current_time() timestamps: crews are scheduled on the site's local calendar day, and gmdate() would shift jobs across midnight UTC.
/**
 * Included File: Job Pipeline
 *
 * Lightweight lead/job pipeline (Kanban-style) view.
 *
 * The jobs table has record_stage/job_status/quote_type/awarded_date
 * columns from earlier settings-infrastructure work, but nothing in the
 * plugin ever reads or writes them, every row just sits at its default
 * value forever. The fields the rest of the plugin actually uses to
 * track a record's lifecycle are jobType (1 = Quote, 2 = Job, set on
 * every insert/edit in manage-jobs.php) and completed (0/1, also set on
 * every insert/edit). This pipeline is built on those two real fields
 * instead, so it can never drift out of sync with the existing "Manage
 * Jobs" list or job edit form, moving a card just flips the same
 * columns those already read.
 *
 * Three columns:
 *   - Quotes:      jobType = 1, completed = 0
 *   - Active Jobs: jobType = 2, completed = 0
 *   - Completed:   completed = 1 (regardless of jobType)
 */

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

/**
 * Handle a pipeline "move" action (change jobType and/or completed on a
 * single job). Runs before any HTML is echoed so the board that renders
 * right after always reflects the change.
 */
function paintops_handle_pipeline_action(): void {
    if (!isset($_POST['paintops_pipeline_move']) || !paintops_user_can_manage()) {
        return;
    }

    if (!wp_verify_nonce(sanitize_text_field(wp_unslash($_POST['paintops_pipeline_nonce'] ?? '')), 'paintops_pipeline_move_action')) {
        return;
    }

    global $wpdb;

    $job_id = (int) ($_POST['job_id'] ?? 0); // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized, WordPress.Security.ValidatedSanitizedInput.MissingUnslash -- value is cast to a number, sanitization not applicable
    $target = sanitize_key(wp_unslash($_POST['target_stage'] ?? ''));

    if ($job_id <= 0) {
        return;
    }

    switch ($target) {
        case 'quote':
            // Moving back to Quote also clears a prior "Not Won" flag, so a
            // reconsidered lead returns to the open Quotes column.
            $updates = ['jobType' => 1, 'completed' => 0, 'not_won' => 0];
            $update_formats = ['%d', '%d', '%d'];
            break;
        case 'active':
            // Winning/starting a job clears any "Not Won" flag too.
            $updates = ['jobType' => 2, 'completed' => 0, 'not_won' => 0];
            $update_formats = ['%d', '%d', '%d'];
            break;
        case 'completed':
            $updates = ['completed' => 1];
            $update_formats = ['%d'];
            break;
        case 'not_won':
            // Lost quote: flag it so it drops off the board (still kept in the
            // main job list and win-rate stats).
            $updates = ['not_won' => 1];
            $update_formats = ['%d'];
            break;
        default:
            return;
    }

    $wpdb->update(paintops_table('jobs'), $updates, ['id' => $job_id], $update_formats, ['%d']);

    // Winning a request (moved to Active Jobs) also stamps its estimate
    // accepted, see paintops_mark_estimate_accepted_on_win(); demoting it
    // (Back to Request) or losing it (Not Won) removes a manual-win stamp
    // again (real e-signature acceptances are never cleared), see
    // paintops_clear_estimate_win_stamp(). Together the two keep Request
    // Performance in step with the record's actual state in BOTH
    // directions.
    if ($target === 'active' && function_exists('paintops_mark_estimate_accepted_on_win')) {
        paintops_mark_estimate_accepted_on_win($job_id);
    } elseif (($target === 'quote' || $target === 'not_won') && function_exists('paintops_clear_estimate_win_stamp')) {
        paintops_clear_estimate_win_stamp($job_id);
    }
}

/**
 * Which "move to" buttons should show on a card, depending on which
 * column it's currently sitting in.
 */
function paintops_pipeline_move_targets(string $current_stage): array {
    switch ($current_stage) {
        case 'quote':
            return ['active' => 'Mark as Job', 'not_won' => 'Mark as Not Won'];
        case 'active':
            return ['quote' => 'Back to Request', 'completed' => 'Mark Completed'];
        case 'completed':
            return ['active' => 'Reopen as Job'];
    }
    return [];
}

/**
 * The "View Job" icon: a soft-filled accent circle with a bold north-east
 * arrow. More substantial than a bare arrow glyph, and drawn in currentColor
 * so it inherits the link's accent color. Inline SVG (no external asset).
 */
function paintops_pipeline_view_icon(): string {
    return '<svg class="paintops-pipeline-view-icon" width="18" height="18" viewBox="0 0 24 24" fill="none" aria-hidden="true" focusable="false">'
        . '<circle cx="12" cy="12" r="11" fill="currentColor" opacity="0.15"></circle>'
        . '<path d="M9.25 14.75 L14.75 9.25 M14.75 9.25 H10.4 M14.75 9.25 V13.6" stroke="currentColor" stroke-width="2.1" stroke-linecap="round" stroke-linejoin="round"></path>'
        . '</svg>';
}

/**
 * Render one pipeline card for a job/quote row.
 */
function paintops_render_pipeline_card($row, string $current_stage): string {
    global $wpdb;

    $categories = paintops_job_categories();
    $cat_name = $categories[(int) $row->jobCat]['name'] ?? 'Uncategorized';

    $name = trim($row->custFirst . ' ' . $row->custLast);
    $address = trim($row->custServiceAddress);

    $has_start = ((int) $row->startYear) > 0;
    $start_text = '';
    $completed_text = '';
    if ($has_start) {
        $start_text = date('M jS, Y', mktime(0, 0, 0, (int) $row->startMonth, (int) $row->startDay, (int) $row->startYear));

        // A job's finish date is its start date plus its # of days minus
        // one (a 1-day job starts and finishes the same day). reqDays is
        // optional, so anything less than 1 is treated as a single day.
        $req_days = (int) ($row->reqDays ?? 0);
        $span_days = max($req_days, 1) - 1;
        $completed_text = date('M jS, Y', strtotime("+{$span_days} days", mktime(0, 0, 0, (int) $row->startMonth, (int) $row->startDay, (int) $row->startYear)));
    }

    $view_url = esc_url(paintops_admin_url(['viewEid' => (int) $row->id]));

    // Day difference between the start date and today (naive local-date
    // math, round() guards DST boundaries), shared by the Request cards'
    // relative "Scheduled ..." line, the Active/Upcoming card stripe,
    // and the Starts/Started wording below.
    $start_day_diff = null;
    if ($has_start) {
        $pipe_today = function_exists('paintops_today_local') ? paintops_today_local() : (function_exists('current_time') ? current_time('Y-m-d') : date('Y-m-d'));
        $pipe_parts = array_map('intval', explode('-', $pipe_today));
        $pipe_today_ts = mktime(0, 0, 0, $pipe_parts[1], $pipe_parts[2], $pipe_parts[0]);
        $pipe_start_ts = mktime(0, 0, 0, (int) $row->startMonth, (int) $row->startDay, (int) $row->startYear);
        $start_day_diff = (int) round(($pipe_start_ts - $pipe_today_ts) / DAY_IN_SECONDS);
    }

    // ONE stripe across the top of EVERY card (Mike 2026-07-23, round 2:
    // the date folded INTO the stripe, e.g. "Upcoming - Starts Tomorrow
    // Jul 24th", and the separate "Starts ..." meta line dropped). The
    // year only shows when it isn't this year; the full date stays as a
    // hover title. Colors: green = crew is (or should be) on it, blue =
    // ahead (upcoming job / scheduled visit), amber = a visit date that
    // slipped past, gray = completed / not scheduled.
    $pipe_short_date = '';
    if ($has_start) {
        $pipe_year_now = (int) date('Y', $pipe_today_ts);
        $pipe_short_date = date('M jS' . (((int) $row->startYear !== $pipe_year_now) ? ', Y' : ''), $pipe_start_ts);
    }

    $stripe_text  = '';
    $stripe_class = 'po-stripe-muted';
    $stripe_title = $start_text;
    $is_upcoming  = ($current_stage === 'active' && $start_day_diff !== null && $start_day_diff > 0);
    if ($current_stage === 'active') {
        if ($is_upcoming) {
            $stripe_text  = 'Upcoming - ' . ($start_day_diff === 1 ? 'Starts Tomorrow' : 'Starts in ' . $start_day_diff . ' Days') . ' ' . $pipe_short_date;
            $stripe_class = 'po-stripe-upcoming';
        } else {
            if ($start_day_diff === null) {
                $stripe_text = 'Active';
            } elseif ($start_day_diff === 0) {
                $stripe_text = 'Active - Started Today';
            } elseif ($start_day_diff === -1) {
                $stripe_text = 'Active - Started Yesterday ' . $pipe_short_date;
            } else {
                $stripe_text = 'Active - Started ' . abs($start_day_diff) . ' Days Ago ' . $pipe_short_date;
            }
            $stripe_class = 'po-stripe-active';
        }
    } elseif ($current_stage === 'quote') {
        if ($start_day_diff === null) {
            $stripe_text = 'Not Scheduled Yet';
        } elseif ($start_day_diff === 0) {
            $stripe_text  = 'Scheduled Today';
            $stripe_class = 'po-stripe-upcoming';
        } elseif ($start_day_diff === 1) {
            $stripe_text  = 'Scheduled Tomorrow ' . $pipe_short_date;
            $stripe_class = 'po-stripe-upcoming';
        } elseif ($start_day_diff > 1) {
            $stripe_text  = 'Scheduled in ' . $start_day_diff . ' Days ' . $pipe_short_date;
            $stripe_class = 'po-stripe-upcoming';
        } elseif ($start_day_diff === -1) {
            $stripe_text  = 'Scheduled Yesterday ' . $pipe_short_date;
            $stripe_class = 'po-stripe-overdue';
        } else {
            $stripe_text  = 'Scheduled ' . abs($start_day_diff) . ' Days Ago ' . $pipe_short_date;
            $stripe_class = 'po-stripe-overdue';
        }
    } else {
        // Completed column: the finish date (start + reqDays - 1, same
        // math as $completed_text above) in short form.
        if ($completed_text !== '') {
            $pipe_completed_ts = strtotime('+' . (max((int) ($row->reqDays ?? 0), 1) - 1) . ' days', $pipe_start_ts);
            $stripe_text  = 'Completed ' . date('M jS' . (((int) date('Y', $pipe_completed_ts) !== (int) date('Y', $pipe_today_ts)) ? ', Y' : ''), $pipe_completed_ts);
            $stripe_title = $completed_text;
        } else {
            $stripe_text = 'Completed';
        }
    }
    $stripe_html = '<div class="paintops-pipeline-stripe ' . $stripe_class . '"' . ($stripe_title !== '' ? ' title="' . esc_attr($stripe_title) . '"' : '') . '>' . esc_html($stripe_text) . '</div>';

    // The record's own document word for the status pills (Mike
    // 2026-07-23: his pending BIDS read "Quote Created"), Quote /
    // Estimate / Bid via the same precedence the rest of the app uses.
    $card_doc_word = function_exists('paintops_quote_doc_word')
        ? ucfirst(paintops_quote_doc_word((int) ($row->isBid ?? 0), (string) ($row->quote_label ?? 'quote')))
        : 'Quote';

    // "Emailed Jul 20th &middot; Texted Jul 21st" glance line on request
    // cards (Mike 2026-07-23): latest quote_email / quote_text entries
    // from the communications log (emails logged on send since 1.0.1xx,
    // text-link taps logged as of this version, see
    // paintops_ajax_log_quote_text in paintops.php).
    $card_sent_line = '';
    if ($current_stage === 'quote') {
        $card_sends = $wpdb->get_results($wpdb->prepare(
            "SELECT comm_type, MAX(sent_at) AS last_at FROM %i
             WHERE job_id = %d AND comm_type IN ('quote_email', 'quote_text')
             GROUP BY comm_type", paintops_table('comm_log'), (int) $row->id));
        $card_send_parts = [];
        foreach ((array) $card_sends as $card_cs) {
            $card_cs_ts = strtotime((string) $card_cs->last_at);
            if ($card_cs_ts <= 0) {
                continue;
            }
            $card_send_parts[($card_cs->comm_type === 'quote_text') ? 1 : 0] =
                ((string) $card_cs->comm_type === 'quote_text' ? 'Texted ' : 'Emailed ') . date('M jS', $card_cs_ts);
        }
        ksort($card_send_parts);
        $card_sent_line = implode(' &middot; ', $card_send_parts);
    }

    ob_start();
    ?>
    <div class="paintops-pipeline-card">
        <?php // The whole upper card is ONE link to the job view (Mike
              // 2026-07-23): stripe, name, address, and category all
              // click through, replacing the old name link and the
              // footer "View Job" link. Actions menu, move buttons, and
              // the status pill stay OUTSIDE the link (nested
              // interactive elements are invalid HTML anyway). The
              // separate date meta line is gone, the stripe carries it. ?>
        <a class="paintops-pipeline-cardlink" href="<?php echo esc_url($view_url); ?>">
            <?php echo wp_kses($stripe_html, paintops_kses_allowed_html(), paintops_kses_protocols()); ?>
            <div class="paintops-pipeline-card-title">
                <span><?php echo esc_html($name !== '' ? $name : 'Job #' . $row->id); ?></span><?php
                // Callback/Punch List/Add-On pill on board cards too (Mike
                // 2026-07-23), same badge as the job view header.
                echo wp_kses(function_exists('paintops_visit_type_badge_html') ? paintops_visit_type_badge_html((string) ($row->visit_type ?? 'original')) : '', paintops_kses_allowed_html(), paintops_kses_protocols()); ?>
            </div>
            <?php if ($address !== '') : ?>
                <div class="paintops-pipeline-card-meta"><?php echo esc_html($address); ?></div>
            <?php endif; ?>
            <div class="paintops-pipeline-card-meta"><?php echo esc_html($cat_name); ?></div>
            <?php if ($card_sent_line !== '') : ?>
                <div class="paintops-pipeline-card-meta" style="color:#1a7a43;"><?php echo wp_kses($card_sent_line, paintops_kses_allowed_html(), paintops_kses_protocols()); ?></div>
            <?php endif; ?>
        </a>
        <div class="paintops-pipeline-card-body">
            <?php echo wp_kses(function_exists('paintops_render_job_actions_menu') ? paintops_render_job_actions_menu($row, 'pipeline-actions-' . (int) $row->id) : '', paintops_kses_allowed_html(), paintops_kses_protocols()); ?>

            <div class="paintops-pipeline-card-actions">
                <?php foreach (paintops_pipeline_move_targets($current_stage) as $target => $label) : ?>
                    <form method="post" style="display:inline;">
                        <?php wp_nonce_field('paintops_pipeline_move_action', 'paintops_pipeline_nonce'); ?>
                        <input type="hidden" name="job_id" value="<?php echo esc_attr((string) $row->id); ?>">
                        <input type="hidden" name="target_stage" value="<?php echo esc_attr($target); ?>">
                        <button type="submit" name="paintops_pipeline_move" value="1" class="paintops-btn paintops-btn-secondary"><?php echo esc_html($label); ?></button>
                    </form>
                <?php endforeach; ?>
            </div>
        </div>

        <?php
        // Card footer: an optional status pill on the left and a subtle "View
        // Job" link on the right. Keeping the pill here rather than in the body
        // means every card body holds the same lines, so the footers (and the
        // View Job link) line up across the three columns instead of one card
        // sitting a row higher than another.
        $tag_html = '';
        if ($current_stage === 'quote') {
            // Quote workflow status pill. A quote should never sit on the board
            // with no status, so it always resolves to one of:
            //   Quote already sent (latest estimate exists):
            //     - accepted            -> green "Quote Accepted"
            //     - sent <= 3 days ago  -> green "Quote Created"
            //     - sent  > 3 days ago  -> red   "Quote Pending" (needs a nudge)
            //   No quote sent yet, keyed off the scheduled visit date:
            //     - visit today/future  -> green "Pending Visit"
            //     - visit already past  -> red "Write a Quote" link (create page)
            //     - no scheduled visit  -> no pill
            // Everyone who can see the Board can create quotes, so the Write a
            // Quote link needs no extra gate; the create page checks again.
            $est = $wpdb->get_row($wpdb->prepare(
                "SELECT accepted, createdOn FROM %i WHERE jobID = %d ORDER BY estID DESC LIMIT 1", paintops_table('estimates'), (int) $row->id));
            if ($est) {
                if ((int) $est->accepted === 1) {
                    $tag_html = '<span class="paintops-pipeline-tag po-tag-pending">' . esc_html($card_doc_word) . ' Accepted</span>';
                } else {
                    $now        = function_exists('current_time') ? current_time('timestamp') : time();
                    $created_ts = !empty($est->createdOn) ? strtotime((string) $est->createdOn) : 0;
                    $is_fresh   = $created_ts > 0 && $created_ts >= ($now - 3 * DAY_IN_SECONDS);
                    if ($is_fresh) {
                        $tag_html = '<span class="paintops-pipeline-tag po-tag-pending">' . esc_html($card_doc_word) . ' Created</span>';
                    } else {
                        $tag_html = '<span class="paintops-pipeline-tag po-tag-due">' . esc_html($card_doc_word) . ' Pending</span>';
                    }
                }
            } elseif ($has_start) {
                $visit_date = sprintf('%04d-%02d-%02d', (int) $row->startYear, (int) $row->startMonth, (int) $row->startDay);
                $today = function_exists('paintops_today_local') ? paintops_today_local() : (function_exists('current_time') ? current_time('Y-m-d') : date('Y-m-d'));
                if ($visit_date >= $today) {
                    $tag_html = '<span class="paintops-pipeline-tag po-tag-pending">Pending Visit</span>';
                } else {
                    $write_url = esc_url(paintops_admin_url(['estimates' => 1, 'csaction' => 'create', 'jobID' => (int) $row->id]));
                    $tag_html = '<a class="paintops-pipeline-tag po-tag-due paintops-pipeline-tag-link" href="' . $write_url . '">Write a ' . esc_html($card_doc_word) . '</a>';
                }
            }
        } elseif (function_exists('paintops_user_can_view_financials') && paintops_user_can_view_financials()
            && function_exists('paintops_job_financial_summary')) {
            // Balance/paid tag: active or completed jobs only (a quote has no
            // balance yet), and only for users who can view financials (it
            // exposes a dollar amount). Uses the canonical remaining_balance.
            $card_fin     = paintops_job_financial_summary((int) $row->id);
            $card_gross   = (float) ($card_fin['gross'] ?? 0);
            $card_balance = (float) ($card_fin['remaining_balance'] ?? 0);
            $card_cur     = function_exists('paintops_currency_symbol') ? paintops_currency_symbol() : '$';
            if ($card_gross > 0) {
                if ($card_balance > 0.005) {
                    // An UPCOMING job's balance isn't due yet (Mike
                    // 2026-07-23: the red pill on a not-yet-started job
                    // with no deposit required read like an invoice or
                    // payment reminder needed to go out). The stripe
                    // already says Upcoming; the pill stays quiet until
                    // work actually starts. Paid-in-full still shows,
                    // money in hand is good news at any stage.
                    if (!$is_upcoming) {
                        $tag_html = '<span class="paintops-pipeline-tag po-tag-due">' . esc_html($card_cur . number_format($card_balance, 2)) . ' Balance Due</span>';
                    }
                } else {
                    $tag_html = '<span class="paintops-pipeline-tag po-tag-paid">Paid in full</span>';
                }
            }
        }
        ?>
        <?php if ($tag_html !== '') : ?>
        <div class="paintops-pipeline-foot">
            <span class="paintops-pipeline-foot-tag"><?php echo wp_kses($tag_html, paintops_kses_allowed_html(), paintops_kses_protocols()); ?></span>
        </div>
        <?php endif; ?>
    </div>
    <?php
    return ob_get_clean();
}

/**
 * Render the full pipeline board.
 */
function paintops_render_job_pipeline(): string {
    if (!paintops_user_can_manage()) {
        return '<p>You do not have permission to view the pipeline.</p>';
    }

    paintops_handle_pipeline_action();

    global $wpdb;
    $jobs_table = paintops_table('jobs');

    // WHERE/ORDER fragments below are fixed literals defined in this file,
    // never derived from request input, so building the query strings this
    // way (rather than through $wpdb->prepare(), which can't parameterize
    // column/clause fragments anyway) is safe.
    // A "Not Won" quote (not_won = 1) is a lost lead: it drops off every column
    // of the board (the record stays in the main job list and win-rate stats).
    // Ordering is by REAL dates, not row ids (Mike 2026-07-23: importing
    // old quotes/jobs gave them the newest ids, which scrambled the board).
    // Requests: newest lead first (created_on). Active: next-up first, the
    // soonest scheduled start at the top, undated jobs sink to the bottom
    // (the CASE guard, zero/garbage start parts would otherwise sort first
    // ascending). Completed: most recently WORKED first, by the scheduled
    // date rather than updated_on, an old job imported today is still an
    // old job, not the latest completion.
    $columns = [
        'quote' => [
            'label' => 'Requests',
            'where' => 'jobType = 1 AND completed != 1 AND not_won != 1',
            'order' => 'created_on DESC, id DESC',
        ],
        'active' => [
            'label' => 'Active Jobs',
            'where' => 'jobType = 2 AND completed != 1 AND not_won != 1',
            'order' => 'CASE WHEN startYear > 2000 THEN 0 ELSE 1 END ASC, startYear ASC, startMonth ASC, startDay ASC, id ASC',
        ],
        'completed' => [
            'label' => 'Completed',
            'where' => 'completed = 1 AND not_won != 1',
            'order' => 'CASE WHEN startYear > 2000 THEN 0 ELSE 1 END ASC, startYear DESC, startMonth DESC, startDay DESC, id DESC',
        ],
    ];

    // 50 (was 25, Mike 2026-07-23): with the per-column show-more
    // drawers below, a busy shop's overflow now collapses instead of
    // silently falling off; past 50 the search note still applies.
    $per_column_limit = 50;

    ob_start();
    ?>
    <?php paintops_enqueue_style('job-pipeline-board'); ?>
    <!-- Search in its own box above the board, like the Customers page. -->
    <form method="get" class="paintops-jobsearch-box">
        <input type="hidden" name="jobSearch" value="1">
        <input type="text" name="q" placeholder="Search jobs by name, email, phone, address, or job #">
        <button type="submit" class="paintops-btn paintops-btn-primary">Search</button>
    </form>
    <div class="cs-financial-box">
        <div class="job-form-section" style="margin-bottom:12px;">
            <span>Job Board</span>
        </div>
        <div class="paintops-pipeline-board">
            <?php
            $board_today = function_exists('current_time') ? current_time('Y-m-d') : date('Y-m-d');
            $board_now = function_exists('current_time') ? current_time('timestamp') : time();
            $board_can_fin = function_exists('paintops_user_can_view_financials') && paintops_user_can_view_financials()
                && function_exists('paintops_job_financial_summary');
            $board_cur = function_exists('paintops_currency_symbol') ? paintops_currency_symbol() : '$';
            foreach ($columns as $stage => $config) :
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- where/order fragments are fixed literals from the $columns config above, never request input
                $rows = $wpdb->get_results($wpdb->prepare("SELECT * FROM %i WHERE {$config['where']} ORDER BY {$config['order']} LIMIT %d", $jobs_table, $per_column_limit));
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- where fragment is a fixed literal from the $columns config above, never request input
                $total = (int) $wpdb->get_var( $wpdb->prepare("SELECT COUNT(*) FROM %i WHERE {$config['where']}", $jobs_table) );

                // Column glance line (Mike 2026-07-23: read the whole board
                // before scrolling a card) + needs-attention ordering for
                // Requests. Dollar figures only for financial viewers.
                $col_meta_parts = [];
                if ($stage === 'quote' && $rows) {
                    // ONE query for every request's latest estimate (the
                    // same state the card pills read row-by-row), so the
                    // urgency sort and the glance counts don't add a
                    // per-row query here.
                    $rq_ids = array_map(static function ($r) { return (int) $r->id; }, $rows);
                    $rq_in  = implode(',', array_map('intval', $rq_ids));
                    // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- the IN list is intval-cast ids imploded in this function, never raw input
                    $rq_est_rows = $wpdb->get_results( $wpdb->prepare(
                        "SELECT e.jobID, e.accepted, e.createdOn
                         FROM %i e
                         INNER JOIN (SELECT jobID, MAX(estID) AS mx FROM %i GROUP BY jobID) m ON m.mx = e.estID
                         WHERE e.jobID IN ({$rq_in})", paintops_table('estimates'), paintops_table('estimates')) );
                    // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                    $rq_latest = [];
                    foreach ((array) $rq_est_rows as $rq_e) {
                        $rq_latest[(int) $rq_e->jobID] = $rq_e;
                    }
                    // Urgency tiers (Mike 2026-07-23: an overdue visit was
                    // buried under fresh leads): 0 = visit date slipped
                    // with no quote written yet, 1 = quote sent > 3 days
                    // ago and still unaccepted (the red Quote Pending
                    // pill), 2 = everything else. Newest-first preserved
                    // WITHIN each tier via the original index.
                    $rq_overdue = 0;
                    $rq_pending = 0;
                    foreach ($rows as $rq_i => $rq_row) {
                        $rq_dated = ((int) $rq_row->startYear) > 2000;
                        $rq_start = $rq_dated ? sprintf('%04d-%02d-%02d', (int) $rq_row->startYear, (int) $rq_row->startMonth, (int) $rq_row->startDay) : '';
                        $rq_est   = $rq_latest[(int) $rq_row->id] ?? null;
                        $rq_tier  = 2;
                        if ($rq_est) {
                            $rq_created = !empty($rq_est->createdOn) ? strtotime((string) $rq_est->createdOn) : 0;
                            if ((int) $rq_est->accepted !== 1 && $rq_created > 0 && $rq_created < ($board_now - 3 * DAY_IN_SECONDS)) {
                                $rq_tier = 1;
                                $rq_pending++;
                            }
                        } elseif ($rq_dated && $rq_start < $board_today) {
                            $rq_tier = 0;
                            $rq_overdue++;
                        }
                        $rq_row->paintops_rq_tier = $rq_tier;
                        $rq_row->paintops_rq_idx  = $rq_i;
                    }
                    usort($rows, static function ($a, $b) {
                        return ($a->paintops_rq_tier <=> $b->paintops_rq_tier) ?: ($a->paintops_rq_idx <=> $b->paintops_rq_idx);
                    });
                    if ($rq_overdue > 0) {
                        $col_meta_parts[] = $rq_overdue . ' overdue visit' . ($rq_overdue === 1 ? '' : 's');
                    }
                    if ($rq_pending > 0) {
                        $col_meta_parts[] = $rq_pending . ' quote' . ($rq_pending === 1 ? '' : 's') . ' pending';
                    }
                } elseif ($stage === 'active' && $rows) {
                    $ac_upcoming = 0;
                    $ac_outstanding = 0.0;
                    foreach ($rows as $ac_row) {
                        $ac_dated = ((int) $ac_row->startYear) > 2000;
                        $ac_start = $ac_dated ? sprintf('%04d-%02d-%02d', (int) $ac_row->startYear, (int) $ac_row->startMonth, (int) $ac_row->startDay) : '';
                        if ($ac_dated && $ac_start > $board_today) {
                            $ac_upcoming++;
                        } elseif ($board_can_fin) {
                            // Outstanding = started jobs only, matching the
                            // card pill's "nothing is due before work
                            // starts" rule from 1.0.197.
                            $ac_fin = paintops_job_financial_summary((int) $ac_row->id);
                            if ((float) ($ac_fin['gross'] ?? 0) > 0 && (float) ($ac_fin['remaining_balance'] ?? 0) > 0.005) {
                                $ac_outstanding += (float) $ac_fin['remaining_balance'];
                            }
                        }
                    }
                    if ($ac_upcoming > 0) {
                        $col_meta_parts[] = $ac_upcoming . ' upcoming';
                    }
                    if ($ac_outstanding > 0.005) {
                        $col_meta_parts[] = esc_html($board_cur . number_format($ac_outstanding, 2)) . ' outstanding';
                    }
                } elseif ($stage === 'completed' && $rows && $board_can_fin) {
                    // Chasing money is this column's main action; counts
                    // cover the fetched rows (up to the column limit).
                    $cp_unpaid = 0;
                    $cp_outstanding = 0.0;
                    foreach ($rows as $cp_row) {
                        $cp_fin = paintops_job_financial_summary((int) $cp_row->id);
                        if ((float) ($cp_fin['gross'] ?? 0) > 0 && (float) ($cp_fin['remaining_balance'] ?? 0) > 0.005) {
                            $cp_unpaid++;
                            $cp_outstanding += (float) $cp_fin['remaining_balance'];
                        }
                    }
                    if ($cp_unpaid > 0) {
                        $col_meta_parts[] = $cp_unpaid . ' unpaid &middot; ' . esc_html($board_cur . number_format($cp_outstanding, 2)) . ' outstanding';
                    }
                }
                $col_meta = implode(' &middot; ', $col_meta_parts);
            ?>
                <details class="paintops-pipeline-column paintops-pipeline-column-<?php echo esc_attr($stage); ?>" open>
                    <summary>
                        <h3><?php echo esc_html($config['label']); ?> <span class="paintops-pipeline-count"><?php echo esc_html((string) $total); ?></span></h3>
                        <?php // Always rendered, empty or not (Mike 2026-07-23:
                              // "3 upcoming" pushed one column's cards down);
                              // the fixed-height slot keeps the three columns'
                              // first cards on the same line. ?>
                        <span class="paintops-pipeline-colmeta"><?php echo wp_kses($col_meta !== '' ? $col_meta : '&nbsp;', paintops_kses_allowed_html(), paintops_kses_protocols()); ?></span>
                    </summary>
                    <?php if (empty($rows)) : ?>
                        <?php // Teaching empty states (Mike 2026-07-23), not "Nothing here". ?>
                        <?php if ($stage === 'quote') : ?>
                            <p style="color:#777;">No open requests.</p>
                            <p><a class="paintops-btn paintops-btn-primary po-btn-compact" href="<?php echo esc_url(paintops_admin_url(['newJob' => 1])); ?>">+ New Request</a></p>
                        <?php elseif ($stage === 'active') : ?>
                            <p style="color:#777;">No active jobs yet. Mark a request as Job (or win a quote) and it lands here.</p>
                        <?php else : ?>
                            <p style="color:#777;">Completed jobs will show up here.</p>
                        <?php endif; ?>
                    <?php else : ?>
                        <?php
                        // Every column collapses its tail behind a drawer of
                        // the SAME cards (Completed since 2026-07-22; Requests
                        // and Active since 2026-07-23, replacing the silent
                        // fetch-limit cutoff): the working set stays visible,
                        // the long tail is one tap away.
                        $stage_visible_cap = ['quote' => 8, 'active' => 8, 'completed' => 3][$stage];
                        $stage_drawer_label = [
                            'quote' => 'Show more requests',
                            'active' => 'Show more scheduled jobs',
                            'completed' => 'Show older completed jobs',
                        ][$stage];
                        $visible_rows = $rows;
                        $older_rows = [];
                        if (count($rows) > $stage_visible_cap) {
                            $visible_rows = array_slice($rows, 0, $stage_visible_cap);
                            $older_rows = array_slice($rows, $stage_visible_cap);
                        }
                        ?>
                        <?php foreach ($visible_rows as $row) : ?>
                            <?php echo wp_kses(paintops_render_pipeline_card($row, $stage), paintops_kses_allowed_html(), paintops_kses_protocols()); ?>
                        <?php endforeach; ?>
                        <?php if (!empty($older_rows)) : ?>
                            <details class="paintops-pipeline-older">
                                <summary><?php echo esc_html($stage_drawer_label); ?> (<?php echo esc_html((string) max(0, $total - count($visible_rows))); ?>)</summary>
                                <div style="margin-top:10px;">
                                    <?php foreach ($older_rows as $row) : ?>
                                        <?php echo wp_kses(paintops_render_pipeline_card($row, $stage), paintops_kses_allowed_html(), paintops_kses_protocols()); ?>
                                    <?php endforeach; ?>
                                    <?php if ($total > $per_column_limit) : ?>
                                        <p style="color:#777;font-size:12px;">+ <?php echo esc_html((string) ($total - $per_column_limit)); ?> more still not shown here, use Search above to find any job.</p>
                                    <?php endif; ?>
                                </div>
                            </details>
                        <?php elseif ($total > $per_column_limit) : ?>
                            <p style="color:#777;font-size:12px;">+ <?php echo esc_html((string) ($total - $per_column_limit)); ?> more not shown, use Search above to find any job.</p>
                        <?php endif; ?>
                    <?php endif; ?>
                </details>
            <?php endforeach; ?>
        </div>
        <?php paintops_enqueue_script('job-pipeline-board'); ?>

        <?php // Calendar hand-off (Mike 2026-07-23): the board answers
              // "what state is everything in", the calendar answers
              // "when", so a one-line pointer under the columns closes
              // the loop when coordinating dates. ?>
        <p style="margin:14px 2px 0;font-size:13px;color:var(--po-text-muted,#667581);">
            Coordinating dates? <a href="<?php echo esc_url(paintops_admin_url(['calendar' => 1])); ?>" style="font-weight:600;text-decoration:none;">Open the Calendar</a> to see these jobs laid out on real days.
        </p>

        <?php
        // Not Won review (2026-07-19, audit polish): lost leads dropped
        // off the board with no way to see or revive them in-app. A
        // collapsed drawer below the columns lists them newest-first with
        // the customer's decline reason where one was captured, and a
        // "Back to Quote" move (the existing pipeline action, which
        // clears not_won) revives a lead worth re-working.
        $not_won_rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT j.*, e.declined_reason FROM %i j
             LEFT JOIN %i e ON e.estID = j.estimateID
             WHERE j.not_won = 1 ORDER BY j.created_on DESC, j.id DESC LIMIT 50", $jobs_table, paintops_table('estimates')) );
        if ($not_won_rows) : ?>
        <details style="margin-top:16px;border:1px solid var(--po-card-border,#ddd);border-radius:var(--po-radius,8px);padding:10px 14px;background:var(--po-tile-bg,#fafbfc);">
            <summary style="cursor:pointer;font-weight:600;color:var(--po-text-muted,#667581);">Not Won (<?php echo (int) count($not_won_rows); ?>) - lost leads, click to review</summary>
            <div style="margin-top:10px;">
                <?php foreach ($not_won_rows as $nw) :
                    $nw_name = trim((string) $nw->custFirst . ' ' . (string) $nw->custLast);
                    $nw_reason = trim((string) ($nw->declined_reason ?? ''));
                ?>
                <div style="padding:8px 0;border-top:1px solid var(--po-line,#eee);font-size:14px;">
                <div style="display:flex;justify-content:space-between;gap:10px;align-items:center;flex-wrap:wrap;">
                    <span>
                        <a href="<?php echo esc_url(paintops_admin_url(['viewEid' => (int) $nw->id])); ?>" style="font-weight:600;text-decoration:none;">#<?php echo (int) $nw->id; ?> <?php echo esc_html((string) $nw->jobTitle); ?></a>
                        <?php echo wp_kses($nw_name !== '' ? '&middot; ' . esc_html($nw_name) : '', paintops_kses_allowed_html(), paintops_kses_protocols()); ?>
                        <?php if ($nw_reason !== '') : ?>
                            <span style="display:block;font-size:12px;color:var(--po-text-muted,#667581);">Their reason: <?php echo esc_html($nw_reason); ?></span>
                        <?php endif; ?>
                    </span>
                    <span style="display:inline-flex;gap:8px;">
                    <form method="post" style="margin:0;">
                        <?php wp_nonce_field('paintops_pipeline_move_action', 'paintops_pipeline_nonce'); ?>
                        <input type="hidden" name="job_id" value="<?php echo (int) $nw->id; ?>">
                        <input type="hidden" name="target_stage" value="quote">
                        <button type="submit" name="paintops_pipeline_move" value="1" class="paintops-btn paintops-btn-secondary" style="padding:4px 12px;font-size:13px;">Back to Request</button>
                    </form>
                    <?php // Direct win from the drawer (Mike 2026-07-22: "toggling
                          // from not awarded back to a job" had no one-click path,
                          // Back to Request only revives it as a request). Same
                          // 'active' pipeline move the board uses: jobType=2,
                          // clears not_won, and stamps the estimate accepted so
                          // Request Performance counts it. ?>
                    <form method="post" style="margin:0;">
                        <?php wp_nonce_field('paintops_pipeline_move_action', 'paintops_pipeline_nonce'); ?>
                        <input type="hidden" name="job_id" value="<?php echo (int) $nw->id; ?>">
                        <input type="hidden" name="target_stage" value="active">
                        <button type="submit" name="paintops_pipeline_move" value="1" class="paintops-btn paintops-btn-primary" style="padding:4px 12px;font-size:13px;">Mark as Job</button>
                    </form>
                    </span>
                </div>
                <?php
                // Second row line (Mike 2026-07-23): reviving a lost lead
                // usually starts with a call or a look at what was already
                // sent, so the contact links and the communications
                // History live right here instead of a trip to the job
                // page. History renders even when empty on purpose (his
                // older imported quotes have no log yet), so the button
                // is always where you expect it.
                $nw_phone_digits = preg_replace('/[^0-9+]/', '', (string) ($nw->custPhone ?? ''));
                $nw_email_addr   = trim((string) ($nw->custEmail ?? ''));
                $nw_comms        = function_exists('paintops_get_job_communications') ? paintops_get_job_communications((int) $nw->id) : [];
                ?>
                <div style="margin-top:6px;display:flex;gap:14px;flex-wrap:wrap;align-items:baseline;font-size:13px;">
                    <?php // Each tap logs to the communications log via the
                          // app-wide data-po-comm listener (Mike 2026-07-23),
                          // so working a lost lead builds its History below. ?>
                    <?php if ($nw_phone_digits !== '' || $nw_email_addr !== '') : ?>
                    <span class="po-contact-seg">
                    <?php if ($nw_phone_digits !== '') : ?>
                        <a href="tel:<?php echo esc_attr($nw_phone_digits); ?>" data-po-comm="call" data-po-job="<?php echo (int) $nw->id; ?>" data-po-recipient="<?php echo esc_attr($nw_phone_digits); ?>" data-po-src="Not Won drawer"><span class="dashicons dashicons-phone" aria-hidden="true"></span>Call</a>
                        <a href="sms:<?php echo esc_attr($nw_phone_digits); ?>" data-po-comm="text" data-po-job="<?php echo (int) $nw->id; ?>" data-po-recipient="<?php echo esc_attr($nw_phone_digits); ?>" data-po-src="Not Won drawer"><span class="dashicons dashicons-format-chat" aria-hidden="true"></span>Text</a>
                    <?php endif; ?>
                    <?php if ($nw_email_addr !== '') : ?>
                        <a href="mailto:<?php echo esc_attr($nw_email_addr); ?>" data-po-comm="email" data-po-job="<?php echo (int) $nw->id; ?>" data-po-recipient="<?php echo esc_attr($nw_email_addr); ?>" data-po-src="Not Won drawer"><span class="dashicons dashicons-email-alt" aria-hidden="true"></span>Email</a>
                    <?php endif; ?>
                    </span>
                    <?php endif; ?>
                    <details style="flex:1 1 auto;min-width:200px;">
                        <summary style="cursor:pointer;font-weight:500;font-size:13px;color:var(--po-text-muted,#667581);display:inline;">History (<?php echo (int) count($nw_comms); ?>)</summary>
                        <div style="margin-top:6px;">
                            <?php if (empty($nw_comms)) : ?>
                                <p style="margin:0;color:var(--po-text-muted,#667581);font-size:12px;">No communications logged for this record yet. Quote emails and Text-link taps land here automatically.</p>
                            <?php else : ?>
                                <?php foreach ($nw_comms as $nw_cm) : ?>
                                    <p style="margin:0 0 4px;color:var(--po-text-muted,#667581);font-size:12px;">
                                        <?php echo esc_html(date('M j, Y g:i a', strtotime((string) $nw_cm->sent_at))); ?> -
                                        <strong><?php echo esc_html(function_exists('paintops_comm_type_label') ? paintops_comm_type_label((string) $nw_cm->comm_type) : (string) $nw_cm->comm_type); ?></strong>
                                        to <?php echo esc_html((string) $nw_cm->recipient); ?>
                                    </p>
                                <?php endforeach; ?>
                            <?php endif; ?>
                        </div>
                    </details>
                </div>
                </div>
                <?php endforeach; ?>
            </div>
        </details>
        <?php endif; ?>
    </div>
    <?php
    return ob_get_clean();
}
