<?php
/**
 * CronSweeps — Slice 8Z-v
 *
 * Three idempotent daily sweeps that turn dormant schema fields into live behaviour:
 *
 *   1. receiptAutoConfirm  — After finance_settings.receipt_auto_confirm_days elapse from
 *      disbursement, auto-confirm receipt on PAID/ISSUED LPOs the requester never confirmed.
 *      Uses Lpos::confirmReceipt($auto=true) so the retirement clock starts and the requester
 *      still gets a notification (RECEIPT_AUTO_CONFIRMED).
 *
 *   2. retentionTimerElapsed — For every RETENTION-triggered term on a FULLY_RECEIVED LPO
 *      whose retention_days have passed since the last GRN, notify Procurement once
 *      (RETENTION_CLAIMABLE). Marks lpo_payment_terms.retention_notified_at.
 *
 *   3. retirementOverdueSweep — For every internal-cash LPO whose retirement_due_date has
 *      passed without an APPROVED retirement, nudge the raiser (RETIREMENT_OVERDUE). First
 *      nudge is immediate on day 1 overdue; re-nudges throttle per
 *      finance_settings.retirement_overdue_reminder_days.
 *
 * Each method returns a summary array so scripts/cron_supervisor.php can log what happened.
 * All catch \Throwable per-row so one bad LPO doesn't abort the batch.
 */
class CronSweeps
{
    /* ── 1. Receipt auto-confirm ─────────────────────────────────────── */

    public function receiptAutoConfirm(): array
    {
        $db   = new DB();
        $days = $this->settingInt('receipt_auto_confirm_days', 5);
        $rows = $db->mySelect(
            "SELECT c0d, lpo_no, requested_by, disbursed_at
               FROM lpos
              WHERE deleted = 0
                AND lpo_type <> 'SUPPLIER'
                AND cash_out_status IN ('PAID','ISSUED')
                AND receipt_confirmed_at IS NULL
                AND disbursed_at IS NOT NULL
                AND disbursed_at <= DATE_SUB(NOW(), INTERVAL :d DAY)",
            [':d' => $days]
        ) ?: [];

        $done  = [];
        $errs  = [];
        if (!class_exists('Lpos')) require_once ROOT . '/app/models/Lpos.php';
        $lpos = new Lpos();
        foreach ($rows as $r) {
            try {
                $res = $lpos->confirmReceipt(
                    (int)$r['c0d'],
                    'Auto-confirmed after ' . $days . ' days without manual confirmation.',
                    0, 'System (cron)', true
                );
                if (!empty($res['ok'])) $done[] = ['lpo_id' => (int)$r['c0d'], 'lpo_no' => $r['lpo_no'], 'due' => $res['retirement_due_date']];
                else                    $errs[] = ['lpo_id' => (int)$r['c0d'], 'lpo_no' => $r['lpo_no'], 'msg' => $res['msg'] ?? 'unknown'];
            } catch (\Throwable $e) {
                $errs[] = ['lpo_id' => (int)$r['c0d'], 'lpo_no' => $r['lpo_no'], 'msg' => $e->getMessage()];
            }
        }
        return ['scanned' => count($rows), 'confirmed' => count($done), 'errors' => count($errs), 'done' => $done, 'error_rows' => $errs];
    }

    /* ── 2. Retention timer elapsed ──────────────────────────────────── */

    public function retentionTimerElapsed(): array
    {
        $db = new DB();
        // Candidates: RETENTION terms whose LPO is FULLY_RECEIVED, whose last GRN + retention_days
        // has passed, that still have remaining amount to claim, and haven't been pinged yet.
        $rows = $db->mySelect(
            "SELECT t.c0d AS term_id, t.lpo_id, t.label, t.retention_days,
                    (t.computed_amount - t.claimed_amount) AS remaining,
                    l.lpo_no, l.currency, l.payee_name,
                    (SELECT MAX(g.received_on) FROM goods_grn g WHERE g.lpo_id = l.c0d AND g.deleted = 0) AS last_grn_on
               FROM lpo_payment_terms t
               JOIN lpos l ON l.c0d = t.lpo_id AND l.deleted = 0
              WHERE t.trigger_type = 'RETENTION'
                AND t.retention_notified_at IS NULL
                AND l.receipt_status = 'FULLY_RECEIVED'
                AND l.lpo_type = 'SUPPLIER'
                AND (t.computed_amount - t.claimed_amount) > 0.005",
            []
        ) ?: [];

        $done = []; $skipped = 0;
        $now = time();
        foreach ($rows as $r) {
            try {
                $lastGrnTs = !empty($r['last_grn_on']) ? strtotime($r['last_grn_on']) : 0;
                if ($lastGrnTs === 0) { $skipped++; continue; }
                $eligibleTs = $lastGrnTs + ((int)$r['retention_days']) * 86400;
                if ($now < $eligibleTs) { $skipped++; continue; }

                // Ping Procurement (raise-PR audience) once
                if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
                (new Notifications())->notifyGroup(
                    'Procurement', 'RETENTION_CLAIMABLE',
                    'Retention claimable: ' . $r['lpo_no'],
                    'Term "' . $r['label'] . '" · ' . $r['currency'] . ' ' . number_format((float)$r['remaining']) . ' can now be raised as a Payment Request.',
                    'Lpos/view/' . (int)$r['lpo_id'], 'lpo_payment_term', (int)$r['term_id'], 0, 'System (cron)'
                );
                $db->insertUpdate(
                    "UPDATE lpo_payment_terms SET retention_notified_at = NOW() WHERE c0d = :id",
                    [':id' => (int)$r['term_id']]
                );
                $done[] = ['term_id' => (int)$r['term_id'], 'lpo_no' => $r['lpo_no'], 'amount' => (float)$r['remaining']];
            } catch (\Throwable $e) { $skipped++; }
        }
        return ['scanned' => count($rows), 'notified' => count($done), 'skipped' => $skipped, 'done' => $done];
    }

    /* ── 3. Retirement overdue sweep ─────────────────────────────────── */

    public function retirementOverdueSweep(): array
    {
        $db = new DB();
        $reminderDays = $this->settingInt('retirement_overdue_reminder_days', 7);
        // Candidates: internal-cash LPOs past their retirement_due_date with no APPROVED retirement
        // AND either never nudged, OR last nudged more than $reminderDays ago.
        $rows = $db->mySelect(
            "SELECT l.c0d, l.lpo_no, l.currency, l.amount, l.requested_by, l.requested_by_name,
                    l.retirement_due_date, l.retirement_overdue_notified_at, l.retirement_overdue_last_notified_at,
                    r.status AS retirement_status
               FROM lpos l
          LEFT JOIN lpo_retirements r ON r.lpo_id = l.c0d AND r.deleted = 0
              WHERE l.deleted = 0
                AND l.lpo_type <> 'SUPPLIER'
                AND l.cash_out_status IN ('PAID','ISSUED')
                AND l.retirement_due_date IS NOT NULL
                AND l.retirement_due_date <> '0000-00-00'
                AND l.retirement_due_date < CURDATE()
                AND (r.status IS NULL OR r.status <> 'APPROVED')",
            []
        ) ?: [];

        $done = []; $skipped = 0;
        $now = time();
        foreach ($rows as $r) {
            try {
                $isFirst = empty($r['retirement_overdue_notified_at']);
                if (!$isFirst) {
                    $lastTs = !empty($r['retirement_overdue_last_notified_at']) ? strtotime($r['retirement_overdue_last_notified_at']) : 0;
                    if ($lastTs > 0 && ($now - $lastTs) < ($reminderDays * 86400)) { $skipped++; continue; }
                }
                $daysOverdue = (int)floor((strtotime(date('Y-m-d')) - strtotime($r['retirement_due_date'])) / 86400);
                if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
                (new Notifications())->notify(
                    (int)$r['requested_by'], 'RETIREMENT_OVERDUE',
                    'Retirement overdue: ' . $r['lpo_no'],
                    ($isFirst ? 'First reminder — ' : 'Reminder — ')
                    . 'retirement was due ' . $r['retirement_due_date'] . ' (' . $daysOverdue . 'd overdue). '
                    . 'File spend lines + receipts to close this advance.',
                    'Retirements/new/' . (int)$r['c0d'], 'lpo', (int)$r['c0d'], 0, 'System (cron)'
                );
                $sql = $isFirst
                    ? "UPDATE lpos SET retirement_overdue_notified_at = NOW(), retirement_overdue_last_notified_at = NOW() WHERE c0d = :id"
                    : "UPDATE lpos SET retirement_overdue_last_notified_at = NOW() WHERE c0d = :id";
                $db->insertUpdate($sql, [':id' => (int)$r['c0d']]);
                $done[] = ['lpo_id' => (int)$r['c0d'], 'lpo_no' => $r['lpo_no'], 'days_overdue' => $daysOverdue, 'first_ping' => $isFirst];
            } catch (\Throwable $e) { $skipped++; }
        }
        return ['scanned' => count($rows), 'notified' => count($done), 'skipped' => $skipped, 'done' => $done];
    }

    /* ── Helpers ──────────────────────────────────────────────────────── */

    /* ── 6. PM daily spawn (Slice 8S-4) ──────────────────────────────── */

    /**
     * For every schedule row whose next_due_on is within N days AND no in-flight PM exists
     * for that (asset, template), spawn a DRAFT PM ready for the FS to pick up.
     * Notifies the Field Supervisor group.
     */
    public function pmDailySpawn(int $lookaheadDays = 7): array
    {
        $db = new DB();
        $rows = $db->mySelect(
            "SELECT sc.c0d AS schedule_id, sc.contract_id, sc.contract_asset_id, sc.template_id, sc.next_due_on
               FROM service_contract_pm_schedule sc
               JOIN service_contracts c ON c.c0d = sc.contract_id AND c.status = 'ACTIVE'
              WHERE sc.is_active = 1
                AND sc.next_due_on IS NOT NULL
                AND sc.next_due_on <= DATE_ADD(CURDATE(), INTERVAL :d DAY)
                AND NOT EXISTS (
                    SELECT 1 FROM pm_services ps
                     WHERE ps.contract_asset_id = sc.contract_asset_id
                       AND ps.template_id = sc.template_id
                       AND ps.status IN ('DRAFT','IN_PROGRESS','DONE','REVIEWED')
                )",
            [':d' => $lookaheadDays]
        ) ?: [];

        $done = 0; $errs = 0;
        if (!class_exists('PmServices')) require_once ROOT . '/app/models/PmServices.php';
        $psm = new PmServices();
        foreach ($rows as $r) {
            try {
                $psm->spawn((int)$r['contract_asset_id'], (int)$r['template_id'], (int)$r['schedule_id'], (string)$r['next_due_on']);
                $done++;
            } catch (\Throwable $e) { $errs++; }
        }
        // Nudge FS once if anything was spawned
        if ($done > 0) {
            try {
                if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
                (new Notifications())->notifyGroup(
                    'Field Supervisor', 'PM_DUE_SPAWNED',
                    $done . ' PM' . ($done === 1 ? '' : 's') . ' spawned',
                    'Preventive maintenances are due within ' . $lookaheadDays . ' days. Open PM Executions to plan the visit.',
                    'PmServices?status=DRAFT', 'pm_service', 0, 0, 'System (cron)'
                );
            } catch (\Throwable $e) { /* best-effort */ }
        }
        return ['scanned' => count($rows), 'spawned' => $done, 'errors' => $errs];
    }

    /* ── 7. Monthly service invoice draft (Slice 8S-4) ────────────────── */

    /**
     * On the 1st (or configured day) of each month, for each ACTIVE contract with
     * monthly retainer, draft a service invoice in the sales table. Kept simple:
     * one invoice per contract per month.
     *
     * Idempotent — checks for an existing draft-or-later invoice for this contract this month.
     */
    public function monthlyServiceInvoiceDraft(): array
    {
        $db = new DB();
        // Grab all ACTIVE contracts that bill on a monthly cadence — retainer or consumption both trigger.
        $rows = $db->mySelect(
            "SELECT c0d, invoice_cadence
               FROM service_contracts
              WHERE deleted = 0 AND status = 'ACTIVE'
                AND invoice_cadence IN ('MONTHLY_RETAINER','CONSUMPTION')",
            []
        ) ?: [];

        if (!class_exists('ServiceContracts')) require_once ROOT . '/app/models/ServiceContracts.php';
        $sc  = new ServiceContracts();
        $done = 0; $skipped = 0; $errs = 0;
        // Bill the month that just finished — safe both when cron runs on the 1st and if run mid-month.
        $prevMonth = strtotime('first day of last month');
        $year = (int)date('Y', $prevMonth);
        $mo   = (int)date('n', $prevMonth);
        foreach ($rows as $r) {
            try {
                $res = $sc->draftConsumptionInvoice((int)$r['c0d'], $year, $mo, 0, 'System (cron)');
                if (!empty($res['ok'])) $done++;
                else                    $skipped++;
            } catch (\Throwable $e) { $errs++; }
        }
        return ['scanned' => count($rows), 'drafted' => $done, 'skipped' => $skipped, 'errors' => $errs,
                'period' => sprintf('%04d-%02d', $year, $mo)];
    }

    private function settingInt(string $key, int $default): int
    {
        try {
            $db = new DB();
            $r  = $db->mySelect("SELECT setting_value FROM finance_settings WHERE setting_key = :k", [':k' => $key]);
            $v  = (int)($r[0]['setting_value'] ?? 0);
            return $v > 0 ? $v : $default;
        } catch (\Throwable $e) { return $default; }
    }

    /* ── 4. Refresh sales outstanding + days_overdue (daily) ─────────── */

    public function refreshSalesAging(): array
    {
        $db = new DB();
        $rows = $db->mySelect(
            "SELECT c0d FROM sales
              WHERE deleted = 0
                AND invoice_workflow_status IN ('ISSUED','PART_PAID','OVERDUE','PAID_LATE')",
            []
        ) ?: [];
        $done = 0;
        if (!class_exists('Sales')) require_once ROOT . '/app/models/Sales.php';
        $sm = new Sales();
        foreach ($rows as $r) {
            try { $sm->recomputeOutstanding((int)$r['c0d']); $done++; }
            catch (\Throwable $e) { /* soldier on */ }
        }
        return ['scanned' => count($rows), 'refreshed' => $done];
    }

    /* ── 5. Dunning nudges (soft / firm / FM / Exec / legal) ─────────── */

    public function dunningNudges(): array
    {
        $db = new DB();
        $soft   = $this->settingInt('dunning_soft_days',     3);
        $firm   = $this->settingInt('dunning_firm_days',     7);
        $fmDays = $this->settingInt('dunning_fm_escalate',  14);
        $exDays = $this->settingInt('dunning_exec_escalate',30);
        $legal  = $this->settingInt('dunning_legal_flag',   60);

        $rows = $db->mySelect(
            "SELECT s.c0d, s.sale_no, s.customer_id, s.customer_name, s.currency,
                    s.total, s.outstanding_amount, s.days_overdue,
                    s.first_reminder_sent_at, s.last_reminder_sent_at, s.reminder_count,
                    s.drafted_by, s.acknowledged_by
               FROM sales s
              WHERE s.deleted = 0
                AND s.outstanding_amount > 0
                AND s.invoice_workflow_status IN ('ISSUED','PART_PAID','OVERDUE')",
            []
        ) ?: [];

        $done = ['soft'=>0, 'firm'=>0, 'fm'=>0, 'exec'=>0, 'legal'=>0];
        $skipped = 0;
        foreach ($rows as $r) {
            try {
                $daysOver = (int)$r['days_overdue'];
                // Decide which tier applies (highest tier the invoice qualifies for)
                $tier = null;
                if      ($daysOver >= $legal)  $tier = 'legal';
                elseif  ($daysOver >= $exDays) $tier = 'exec';
                elseif  ($daysOver >= $fmDays) $tier = 'fm';
                elseif  ($daysOver >= $firm)   $tier = 'firm';
                elseif  ($daysOver >= -$soft && $daysOver < 0)  $tier = 'soft'; // upcoming
                else { $skipped++; continue; }

                // Throttle: don't re-nudge same tier if the last reminder is fresh (24h)
                $lastTs = !empty($r['last_reminder_sent_at']) ? strtotime((string)$r['last_reminder_sent_at']) : 0;
                if ($lastTs > 0 && (time() - $lastTs) < 86400) { $skipped++; continue; }

                if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
                $noti = new Notifications();
                $body = 'Invoice ' . $r['sale_no'] . ' · ' . $r['customer_name']
                      . ' · ' . $r['currency'] . ' ' . number_format((float)$r['outstanding_amount']) . ' outstanding · '
                      . ($daysOver > 0 ? $daysOver . 'd overdue' : abs($daysOver) . 'd until due');

                if ($tier === 'soft' || $tier === 'firm') {
                    // Notify the internal actor(s) who own the receivable — Accountant + drafter
                    $noti->notifyGroup('Accountant', 'INVOICE_DUE_' . strtoupper($tier),
                        ($tier === 'soft' ? 'Invoice due soon: ' : 'Invoice overdue: ') . $r['sale_no'],
                        $body, 'Sales/view/' . (int)$r['c0d'], 'sale', (int)$r['c0d'], 0, 'System (cron)');
                } elseif ($tier === 'fm') {
                    $noti->notifyGroup('Finance Manager', 'INVOICE_ESCALATED_FM',
                        'Invoice escalated (FM): ' . $r['sale_no'], $body,
                        'Sales/view/' . (int)$r['c0d'], 'sale', (int)$r['c0d'], 0, 'System (cron)');
                } elseif ($tier === 'exec') {
                    $noti->notifyGroup('Executive', 'INVOICE_ESCALATED_EXEC',
                        'Invoice escalated (Exec): ' . $r['sale_no'], $body,
                        'Sales/view/' . (int)$r['c0d'], 'sale', (int)$r['c0d'], 0, 'System (cron)');
                } elseif ($tier === 'legal') {
                    $noti->notifyGroup('Executive', 'INVOICE_LEGAL_FLAG',
                        'Legal flag — invoice > ' . $legal . 'd overdue: ' . $r['sale_no'], $body,
                        'Sales/view/' . (int)$r['c0d'], 'sale', (int)$r['c0d'], 0, 'System (cron)');
                }

                $isFirst = empty($r['first_reminder_sent_at']);
                $sql = $isFirst
                    ? "UPDATE sales SET first_reminder_sent_at = NOW(), last_reminder_sent_at = NOW(), reminder_count = reminder_count + 1 WHERE c0d = :id"
                    : "UPDATE sales SET last_reminder_sent_at = NOW(), reminder_count = reminder_count + 1 WHERE c0d = :id";
                $db->insertUpdate($sql, [':id' => (int)$r['c0d']]);
                $done[$tier]++;
            } catch (\Throwable $e) { $skipped++; }
        }
        return ['scanned' => count($rows), 'skipped' => $skipped, 'done' => $done];
    }
}
