<?php
/**
 * ExecutiveDashboard — aggregation for the Executive landing screen.
 *
 * Pure reads over posted journal_lines / sales / lpos / fin_approvals.
 * No writes, no schema of its own.
 */
class ExecutiveDashboard extends Model
{
    /** Bank + AR + AP + projected closing + trajectory chip. */
    public function financialSummary(): array
    {
        $db = new DB();

        $bank = (float)($db->mySelect(
            "SELECT IFNULL(SUM(jl.debit) - SUM(jl.credit), 0) AS bal
               FROM journal_lines jl
               JOIN journal_entries je   ON je.c0d = jl.journal_entry_id AND je.deleted = 0 AND je.status = 'POSTED'
               JOIN chart_of_accounts coa ON coa.c0d = jl.account_id
              WHERE coa.is_bank = 1",
            []
        )[0]['bal'] ?? 0);

        $ar = (float)($db->mySelect(
            "SELECT IFNULL(SUM(GREATEST(s.total - s.amount_paid, 0)), 0) AS ar
               FROM sales s
              WHERE s.deleted = 0 AND s.invoice_workflow_status = 'ISSUED' AND s.total > s.amount_paid",
            []
        )[0]['ar'] ?? 0);

        $ap = (float)($db->mySelect(
            "SELECT IFNULL(SUM(GREATEST(l.amount - IFNULL(pa.paid, 0), 0)), 0) AS ap
               FROM lpos l
          LEFT JOIN (SELECT pa.lpo_id, SUM(pa.amount) AS paid
                       FROM payment_allocations pa
                       JOIN payments p ON p.c0d = pa.payment_id AND p.deleted = 0
                      GROUP BY pa.lpo_id) pa ON pa.lpo_id = l.c0d
              WHERE l.deleted = 0 AND l.cash_out_status IN ('APPROVED','ISSUED')
                AND l.amount > IFNULL(pa.paid, 0)",
            []
        )[0]['ap'] ?? 0);

        $closing = $bank + $ar - $ap;

        if ($closing < 0)                 { $chip = 'danger';  $label = 'Liquidity risk'; }
        elseif (($closing - $bank) < 0)   { $chip = 'warning'; $label = 'Cash declining'; }
        else                              { $chip = 'success'; $label = 'Healthy trajectory'; }

        return [
            'bank'     => $bank,
            'ar'       => $ar,
            'ap'       => $ap,
            'closing'  => $closing,
            'chip'     => $chip,
            'chip_label' => $label,
        ];
    }

    /** Top overdue open invoices (days past effective_due, largest first). */
    public function topOverdueAr(int $limit = 5): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT s.c0d, s.sale_no, s.invoice_ref, s.customer_name,
                    IFNULL(s.due_date, DATE_ADD(s.invoice_date, INTERVAL 30 DAY)) AS effective_due,
                    DATEDIFF(CURDATE(), IFNULL(s.due_date, DATE_ADD(s.invoice_date, INTERVAL 30 DAY))) AS days_overdue,
                    (s.total - s.amount_paid) AS outstanding
               FROM sales s
              WHERE s.deleted = 0 AND s.invoice_workflow_status = 'ISSUED' AND s.total > s.amount_paid
                AND DATEDIFF(CURDATE(), IFNULL(s.due_date, DATE_ADD(s.invoice_date, INTERVAL 30 DAY))) > 0
              ORDER BY days_overdue DESC, outstanding DESC
              LIMIT " . (int)$limit,
            []
        ) ?: [];
    }

    /** Top upcoming supplier LPO payments (closest effective_due first). */
    public function topDueSoonAp(int $limit = 5): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT l.c0d, l.lpo_no, l.payee_name,
                    GREATEST(l.amount - IFNULL(pa.paid, 0), 0) AS outstanding,
                    DATE_ADD(DATE(IFNULL(l.requested_on, l.created_on)), INTERVAL 30 DAY) AS effective_due,
                    DATEDIFF(DATE_ADD(DATE(IFNULL(l.requested_on, l.created_on)), INTERVAL 30 DAY), CURDATE()) AS days_until_due
               FROM lpos l
          LEFT JOIN (SELECT pa.lpo_id, SUM(pa.amount) AS paid
                       FROM payment_allocations pa
                       JOIN payments p ON p.c0d = pa.payment_id AND p.deleted = 0
                      GROUP BY pa.lpo_id) pa ON pa.lpo_id = l.c0d
              WHERE l.deleted = 0 AND l.cash_out_status IN ('APPROVED','ISSUED')
                AND l.amount > IFNULL(pa.paid, 0)
              ORDER BY effective_due ASC, outstanding DESC
              LIMIT " . (int)$limit,
            []
        ) ?: [];
    }

    /** Approval items where Executive is the current gate (any chain). */
    public function awaitingExecApproval(int $limit = 8): array
    {
        return $this->awaitingByRole('EXECUTIVE', $limit);
    }

    /**
     * Generic: approval items where the given role is the current gate.
     * Used by role-specific dashboards (FM, PM, Accountant …).
     */
    public function awaitingByRole(string $role, int $limit = 8): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT fa.c0d, fa.entity_type, fa.entity_id, fa.ref_label, fa.submitted_on, fa.submitted_by_name,
                    cs.step_label, cd.chain_key
               FROM fin_approvals fa
               JOIN fin_chain_steps cs ON cs.c0d = fa.current_step_id
               JOIN fin_chain_defs  cd ON cd.c0d = fa.chain_id
              WHERE fa.deleted = 0 AND fa.status = 'IN_PROGRESS'
                AND UPPER(cs.role_required) = :r
              ORDER BY fa.submitted_on ASC
              LIMIT " . (int)$limit,
            [':r' => strtoupper($role)]
        ) ?: [];
    }

    /** Last N posted journal entries — Exec activity feed. */
    public function recentJournalActivity(int $limit = 8): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT je.c0d, je.je_no, je.entry_date, je.source_type, je.source_ref, je.narrative,
                    je.total_debit AS amount, je.posted_by_name, je.posted_on
               FROM journal_entries je
              WHERE je.deleted = 0 AND je.status = 'POSTED'
              ORDER BY je.posted_on DESC
              LIMIT " . (int)$limit,
            []
        ) ?: [];
    }

    /** Simple project count for the top strip. */
    public function projectCount(): int
    {
        $db = new DB();
        $r = $db->mySelect("SELECT COUNT(*) AS n FROM c_projects WHERE deleted = 0", []);
        return (int)($r[0]['n'] ?? 0);
    }
}
