<?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: Business Checkup (FREE feature, Mike 2026-07-23)
 *
 * One central "what's falling through the cracks" page for the admin:
 * a set of read-only scans over the whole business, each rendered as a
 * collapsed accordion whose summary meta says either how many items
 * need attention (red) or All clear (green). Every item links straight
 * to the record so it can be fixed in two clicks.
 *
 * Deliberately FREE and deliberately read-only: it only surfaces what
 * the free data model already contains (jobs, quotes, expenses, hours,
 * payments-via-fallback), and never writes anything. Routed at
 * ?checkup=1 (see paintops_fetch_csjobs in manage-jobs.php), gated to
 * financial viewers (Administrators) because balances show here.
 *
 * Scans (keep each one a single indexed-ish query; the whole page
 * should stay cheap even on a big install):
 *   1. Completed jobs with a balance due (reuses the dashboard helper)
 *   2. Jobs past their scheduled end but not marked Complete
 *   3. Won jobs with no start date (won but never scheduled)
 *   4. Started jobs with no expenses logged
 *   5. Started jobs with no labor hours logged
 *   6. Requests pending a decision for more than 14 days
 *   7. Requests with no quote document created
 *   8. This year's expenses missing a receipt image
 *   9. Open records with no customer phone OR email
 */

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

// Wrapped in a named function (2026-07-27, wp.org hosted Plugin Check run):
// this file only ever runs via require from inside paintops_fetch_csjobs()
// in manage-jobs.php, so every variable below is function-local at runtime.
// PHPCS reads the file in isolation, saw code at file scope, and warned that
// every variable here was an unprefixed global. The wrapper makes the real
// scope explicit. function_exists() guard because the file is require'd (not
// require_once) and some SEO plugins run do_shortcode() early, which can pull
// it in twice in one request.
if (!function_exists('paintops_render_business_checkup_screen')) {
function paintops_render_business_checkup_screen() {
global $wpdb;


if (!function_exists('paintops_user_can_view_financials') || !paintops_user_can_view_financials()) {
    echo '<p>You do not have permission to view this page.</p>';
    return;
}

require_once __DIR__ . '/dashboard-alerts.php'; // paintops_get_unpaid_completed_jobs()

global $wpdb;

$ck_jobs   = paintops_table('jobs');
$ck_est    = paintops_table('estimates');
$ck_exp    = paintops_table('job_expenses');
$ck_hrs    = paintops_table('job_worker_hours');
$ck_photos = paintops_table('job_photos');

$ck_today = current_time('Y-m-d');
$ck_year  = (int) gmdate('Y', current_time('timestamp'));
$ck_stale_cutoff = gmdate('Y-m-d H:i:s', current_time('timestamp') - 14 * DAY_IN_SECONDS);

// Reusable SQL fragments. A "valid start" needs real Y/M/D parts; old or
// hand-imported rows can carry zeros. exclude_from_reports rows are the
// owner's own "don't count this" flag, honor it here too.

$ck_cur = function_exists('paintops_currency_symbol') ? paintops_currency_symbol() : '$';

/**
 * One scan section: collapsed accordion, count meta, capped item list.
 * $items: array of ['url' => ..., 'label' => ..., 'detail' => ...].
 */
$ck_render_section = function (string $title, string $explain, array $items, string $all_clear = 'All clear') {
    $count = count($items);
    $meta = $count > 0
        ? '<span class="neg">' . (int) $count . ' item' . ($count === 1 ? '' : 's') . '</span>'
        : '<span class="pos">' . esc_html($all_clear) . '</span>';
    echo '<details class="cs-fin-acc">';
    echo wp_kses('<summary><span class="cs-fin-acc-title">' . esc_html($title) . '</span><span class="cs-fin-acc-meta">' . $meta . '</span></summary>', paintops_kses_allowed_html(), paintops_kses_protocols());
    echo '<div class="cs-fin-acc-body">';
    echo '<p class="description" style="margin-top:0;">' . esc_html($explain) . '</p>';
    if (!$items) {
        echo '<p style="color:var(--po-text-muted,#777);margin:0;">Nothing found. Nice and tidy.</p>';
    } else {
        echo '<ul style="margin:0;padding-left:18px;">';
        foreach (array_slice($items, 0, 25) as $it) {
            echo '<li style="margin:0 0 7px;"><a href="' . esc_url($it['url']) . '">' . esc_html($it['label']) . '</a>';
            if (!empty($it['detail'])) {
                echo ' <span style="color:var(--po-text-muted,#777);font-size:13px;">' . esc_html($it['detail']) . '</span>';
            }
            // Optional "do the thing" jump (Mike 2026-07-27): a second,
            // action-shaped link so a finding is fixable in one click
            // instead of just viewable.
            if (!empty($it['action_url']) && !empty($it['action_label'])) {
                echo ' <span style="color:var(--po-text-muted,#777);font-size:13px;">&middot;</span> <a href="' . esc_url($it['action_url']) . '" style="font-size:13px;font-weight:600;">' . esc_html($it['action_label']) . '</a>';
            }
            echo '</li>';
        }
        echo '</ul>';
        if ($count > 25) {
            echo '<p class="description" style="margin-bottom:0;">&hellip;and ' . (int) ($count - 25) . ' more.</p>';
        }
    }
    echo '</div></details>';
    return $count;
};

$ck_job_label = function ($row) {
    $name = trim(($row->custFirst ?? '') . ' ' . ($row->custLast ?? ''));
    return '#' . (int) $row->id . ' ' . ($name !== '' ? $name . ' - ' : '') . (string) $row->jobTitle;
};

// ---- 1. Completed jobs with a balance due -------------------------------
$ck_balance_items = [];
if (function_exists('paintops_get_unpaid_completed_jobs')) {
    foreach (paintops_get_unpaid_completed_jobs() as $bj) {
        // $bj is a job ROW OBJECT with po_balance stamped on (see
        // paintops_get_unpaid_completed_jobs() in dashboard-alerts.php);
        // this loop used array access and the wrong key, a latent fatal
        // that only fired once a site actually had a completed job with
        // a balance due (surfaced by the 1.0.249 upgrade test data).
        $ck_balance_items[] = [
            'action_url'   => paintops_admin_url(['jobInvoice' => (int) $bj->id]),
            'action_label' => 'Create invoice',
            'url'    => paintops_admin_url(['viewEid' => (int) $bj->id]),
            'label'  => '#' . (int) $bj->id . ' ' . trim(((string) ($bj->custFirst ?? '')) . ' ' . ((string) ($bj->custLast ?? ''))) . ' - ' . (string) ($bj->jobTitle ?? ''),
            'detail' => 'Due ' . $ck_cur . number_format((float) ($bj->po_balance ?? 0), 2),
        ];
    }
}

// ---- 2. Past scheduled end, not marked Complete -------------------------
$ck_overdue = $wpdb->get_results($wpdb->prepare(
    "SELECT id, jobTitle, custFirst, custLast, DATE_ADD(CONCAT(startYear, '-', LPAD(startMonth, 2, '0'), '-', LPAD(startDay, 2, '0')), INTERVAL GREATEST(reqDays, 1) - 1 DAY) AS end_date FROM %i
     WHERE jobType = 2 AND completed != 1 AND not_won != 1 AND COALESCE(exclude_from_reports, 0) = 0
       AND (startYear > 2000 AND startMonth BETWEEN 1 AND 12 AND startDay BETWEEN 1 AND 31) AND TO_DAYS(DATE_ADD(CONCAT(startYear, '-', LPAD(startMonth, 2, '0'), '-', LPAD(startDay, 2, '0')), INTERVAL GREATEST(reqDays, 1) - 1 DAY)) < TO_DAYS(%s)
     ORDER BY end_date ASC", $ck_jobs, $ck_today)) ?: [];
$ck_overdue_items = [];
foreach ($ck_overdue as $r) {
    $ck_overdue_items[] = [
        'url'    => paintops_admin_url(['viewEid' => (int) $r->id]),
        'label'  => $ck_job_label($r),
        'detail' => 'Scheduled days ended ' . date('M j, Y', strtotime((string) $r->end_date)),
    ];
}

// ---- 3. Won but never scheduled -----------------------------------------
$ck_unsched = $wpdb->get_results($wpdb->prepare(
    "SELECT id, jobTitle, custFirst, custLast FROM %i
     WHERE jobType = 2 AND completed != 1 AND not_won != 1 AND COALESCE(exclude_from_reports, 0) = 0
       AND NOT (startYear > 2000 AND startMonth BETWEEN 1 AND 12 AND startDay BETWEEN 1 AND 31) ORDER BY id DESC", $ck_jobs
)) ?: [];
$ck_unsched_items = [];
foreach ($ck_unsched as $r) {
    $ck_unsched_items[] = [
        'url'    => paintops_admin_url(['editEid' => (int) $r->id]),
        'label'  => $ck_job_label($r),
        'detail' => 'No start date set',
    ];
}

// ---- 4 + 5. Started jobs missing expenses / hours -----------------------
$ck_no_expense = $wpdb->get_results($wpdb->prepare(
    "SELECT j.id, j.jobTitle, j.custFirst, j.custLast FROM %i j
     WHERE j.jobType = 2 AND j.not_won != 1 AND COALESCE(exclude_from_reports, 0) = 0
       AND COALESCE(j.visit_type, 'original') = 'original'
       AND j.no_materials_needed != 1
       AND (startYear > 2000 AND startMonth BETWEEN 1 AND 12 AND startDay BETWEEN 1 AND 31) AND TO_DAYS(CONCAT(startYear, '-', LPAD(startMonth, 2, '0'), '-', LPAD(startDay, 2, '0'))) <= TO_DAYS(%s)
       AND NOT EXISTS (SELECT 1 FROM %i e WHERE e.job_id = j.id)
     ORDER BY j.id DESC", $ck_jobs, $ck_today, $ck_exp)) ?: [];
$ck_no_expense_items = [];
foreach ($ck_no_expense as $r) {
    $ck_no_expense_items[] = [
        'url'    => paintops_admin_url(['jobAddReceipt' => (int) $r->id]),
        'label'  => $ck_job_label($r),
        'detail' => 'No expenses logged',
    ];
}

$ck_no_hours = $wpdb->get_results($wpdb->prepare(
    "SELECT j.id, j.jobTitle, j.custFirst, j.custLast FROM %i j
     WHERE j.jobType = 2 AND j.not_won != 1 AND COALESCE(exclude_from_reports, 0) = 0
       AND (startYear > 2000 AND startMonth BETWEEN 1 AND 12 AND startDay BETWEEN 1 AND 31) AND TO_DAYS(CONCAT(startYear, '-', LPAD(startMonth, 2, '0'), '-', LPAD(startDay, 2, '0'))) <= TO_DAYS(%s)
       AND NOT EXISTS (SELECT 1 FROM %i h WHERE h.job_id = j.id)
     ORDER BY j.id DESC", $ck_jobs, $ck_today, $ck_hrs)) ?: [];
$ck_no_hours_items = [];
foreach ($ck_no_hours as $r) {
    $ck_no_hours_items[] = [
        'action_url'   => paintops_admin_url(['jobFinancials' => (int) $r->id]),
        'action_label' => 'Log hours',
        'url'    => paintops_admin_url(['viewEid' => (int) $r->id]),
        'label'  => $ck_job_label($r),
        'detail' => 'No labor hours logged',
    ];
}

// ---- 6. Requests pending a decision for 14+ days ------------------------
$ck_stale = $wpdb->get_results($wpdb->prepare(
    "SELECT id, jobTitle, custFirst, custLast, created_on FROM %i
     WHERE jobType != 2 AND completed != 1 AND not_won != 1 AND COALESCE(exclude_from_reports, 0) = 0
       AND created_on < %s ORDER BY created_on ASC", $ck_jobs, $ck_stale_cutoff)) ?: [];
$ck_stale_items = [];
foreach ($ck_stale as $r) {
    $ck_stale_items[] = [
        'url'    => paintops_admin_url(['viewEid' => (int) $r->id]),
        'label'  => $ck_job_label($r),
        'detail' => 'Waiting since ' . date('M j, Y', strtotime((string) $r->created_on)) . ', follow up or mark it Won / Not Won',
    ];
}

// ---- 7. Requests with no quote document ---------------------------------
$ck_noquote = $wpdb->get_results($wpdb->prepare(
    "SELECT j.id, j.jobTitle, j.custFirst, j.custLast FROM %i j
     WHERE j.jobType != 2 AND j.completed != 1 AND j.not_won != 1 AND COALESCE(exclude_from_reports, 0) = 0
       AND NOT EXISTS (SELECT 1 FROM %i e WHERE e.jobID = j.id)
     ORDER BY j.id DESC", $ck_jobs, $ck_est
)) ?: [];
$ck_noquote_items = [];
foreach ($ck_noquote as $r) {
    $ck_noquote_items[] = [
        'url'    => paintops_admin_url(['estimates' => 1, 'csaction' => 'create', 'jobID' => (int) $r->id]),
        'label'  => $ck_job_label($r),
        'detail' => 'Lead entered, no quote document yet',
    ];
}

// ---- 8. This year's expenses missing a receipt image --------------------
$ck_noreceipt = $wpdb->get_results($wpdb->prepare(
    "SELECT e.id AS expense_id, e.job_id, e.vendor, e.amount, e.expense_date, j.jobTitle, j.custFirst, j.custLast, j.id
     FROM %i e INNER JOIN %i j ON j.id = e.job_id
     WHERE YEAR(e.expense_date) = %d
       AND NOT EXISTS (SELECT 1 FROM %i p WHERE p.expense_id = e.id AND p.context = 'receipt')
     ORDER BY e.expense_date DESC", $ck_exp, $ck_jobs, $ck_year, $ck_photos)) ?: [];
$ck_noreceipt_items = [];
foreach ($ck_noreceipt as $r) {
    $ck_noreceipt_items[] = [
        'url'    => paintops_admin_url(['jobAddReceipt' => (int) $r->job_id]),
        'label'  => '#' . (int) $r->job_id . ' ' . ((string) $r->vendor !== '' ? $r->vendor : 'Expense') . ' - ' . $ck_cur . number_format((float) $r->amount, 2),
        'detail' => date('M j', strtotime((string) $r->expense_date)) . ', no receipt image attached',
    ];
}

// ---- 9. Open records with no way to reach the customer ------------------
$ck_nocontact = $wpdb->get_results($wpdb->prepare(
    "SELECT id, jobTitle, custFirst, custLast FROM %i
     WHERE completed != 1 AND not_won != 1 AND COALESCE(exclude_from_reports, 0) = 0
       AND TRIM(COALESCE(custPhone, '')) = '' AND TRIM(COALESCE(custEmail, '')) = ''
     ORDER BY id DESC", $ck_jobs
)) ?: [];
$ck_nocontact_items = [];
foreach ($ck_nocontact as $r) {
    $ck_nocontact_items[] = [
        'url'    => paintops_admin_url(['editEid' => (int) $r->id]),
        'label'  => $ck_job_label($r),
        'detail' => 'No phone or email on file',
    ];
}

// ---- Render -------------------------------------------------------------
echo '<div class="paintops-financials-page">';
echo '<p class="description" style="margin-top:0;">Nine automatic scans for the things that slip through the cracks on a busy week: money not collected, jobs not closed out, leads going cold, and records missing the data your reports depend on. Everything links straight to the record so it can be fixed on the spot.</p>';

ob_start();
$ck_total = 0;
$ck_total += $ck_render_section('Money to collect', 'Completed jobs where the payments logged are less than the billable amount.', $ck_balance_items, 'Everything collected');
$ck_total += $ck_render_section('Past end date, not marked Complete', 'The scheduled days are over but the job was never closed out. Completing a job is what moves it to reports and balance-due tracking.', $ck_overdue_items);
$ck_total += $ck_render_section('Won but not scheduled', 'These are real Jobs with no start date, so they never show on the calendar and the crew can never see them.', $ck_unsched_items);
$ck_total += $ck_render_section('Started jobs with no expenses', 'Work has begun but not a single expense is logged. Fine for a labor-only job, worth a look otherwise, missing costs inflate profit. Callback, punch list, and add-on visits are exempt, those usually run on leftover materials, and so are jobs marked "No materials needed" on the job form.', $ck_no_expense_items);
$ck_total += $ck_render_section('Started jobs with no hours', 'Work has begun but no labor hours are logged, so profit-per-hour has nothing to work with. Check-ins or a manual entry on the Financials page both count.', $ck_no_hours_items);
$ck_total += $ck_render_section('Leads going cold (14+ days)', 'Requests still waiting on a decision after two weeks. Follow up, or mark them Won or Not Won so your acceptance rate stays honest.', $ck_stale_items);
$ck_total += $ck_render_section('Leads with no quote yet', 'The request was entered but no quote document was ever created. These customers are waiting on a price.', $ck_noquote_items);
$ck_total += $ck_render_section('Expenses missing receipt images (' . $ck_year . ')', 'Logged expenses with no receipt photo or PDF attached. The amount still counts, but at tax time the paper trail is what matters.', $ck_noreceipt_items);
$ck_total += $ck_render_section('No way to reach the customer', 'Open records with neither a phone number nor an email on file.', $ck_nocontact_items);
$ck_sections_html = ob_get_clean();

if ($ck_total > 0) {
    echo '<div class="cs-financial-box" style="border-left:4px solid #c0392b;"><p style="margin:0;font-weight:600;">' . (int) $ck_total . ' item' . ($ck_total === 1 ? '' : 's') . ' could use your attention.</p></div>';
} else {
    echo '<div class="cs-financial-box" style="border-left:4px solid var(--po-positive,#1e7e34);"><p style="margin:0;font-weight:600;">All nine scans came back clean. Tight ship.</p></div>';
}
echo wp_kses($ck_sections_html, paintops_kses_allowed_html(), paintops_kses_protocols());
echo '</div>';
} // end paintops_render_business_checkup_screen()
} // end function_exists() guard
paintops_render_business_checkup_screen();
