<?php
/**
 * ProjectPnl — per-project profitability computed from journal_lines
 * analytical tagging (project_id).
 *
 * Revenue = Σ credit on REVENUE-type accounts (4110/4120/4130 + any others)
 * COGS   = Σ debit on COST OF SALES accounts (5xxx)
 * OpEx    = Σ debit on EXPENSE (non-COGS) accounts (6xxx)
 * Gross margin = Revenue − COGS
 * Net margin  = Revenue − COGS − OpEx
 *
 * Compared against planned figures from the Client PO(s) — subtotal_sell
 * (planned revenue) and subtotal_cog (planned direct cost).
 */
class ProjectPnl extends Model
{
    /** All-projects overview: 1 row per project with revenue/expense/margin. */
    public function overviewAllProjects(string $from = '', string $to = ''): array
    {
        $db = new DB();
        $params = [];
        $dateClause = '';
        if ($from !== '') { $dateClause .= ' AND je.entry_date >= :f'; $params[':f'] = $from; }
        if ($to   !== '') { $dateClause .= ' AND je.entry_date <= :t'; $params[':t'] = $to;   }

        $sql = "
            SELECT p.c0d AS project_id, p.project_no, p.project_name, p.project_type, p.project_customer,
                   p.currency, p.crm_status, p.migrated_flag, p.project_status, p.default_margin_pct,
                   -- Actuals from GL --------------------------------------
                   IFNULL(SUM(CASE WHEN coa.account_type = 'REVENUE'                      THEN jl.credit ELSE 0 END), 0) AS revenue,
                   IFNULL(SUM(CASE WHEN coa.account_type = 'EXPENSE' AND coa.account_code LIKE '5%' THEN jl.debit ELSE 0 END), 0) AS cogs,
                   IFNULL(SUM(CASE WHEN coa.account_type = 'EXPENSE' AND coa.account_code LIKE '6%' THEN jl.debit ELSE 0 END), 0) AS opex,
                   -- Planned figures from Client POs -----------------------
                   IFNULL((SELECT SUM(cpo.subtotal_sell) FROM goods_client_pos cpo WHERE cpo.project_id = p.c0d AND cpo.deleted = 0), 0) AS planned_revenue,
                   IFNULL((SELECT SUM(cpo.subtotal_cog)  FROM goods_client_pos cpo WHERE cpo.project_id = p.c0d AND cpo.deleted = 0), 0) AS planned_cog
              FROM c_projects p
         LEFT JOIN journal_lines jl    ON jl.project_id = p.c0d
         LEFT JOIN journal_entries je  ON je.c0d = jl.journal_entry_id AND je.deleted = 0 AND je.status = 'POSTED' $dateClause
         LEFT JOIN chart_of_accounts coa ON coa.c0d = jl.account_id
             WHERE p.deleted = 0
             GROUP BY p.c0d
             ORDER BY revenue DESC, p.c0d DESC
        ";
        return $db->mySelect($sql, $params) ?: [];
    }

    public function forProject(int $project_id, string $from = '', string $to = ''): array
    {
        $db = new DB();
        $prj = $db->mySelect(
            "SELECT c0d, project_no, project_name, project_type, project_customer, currency,
                    default_margin_pct, crm_status, migrated_flag, project_status
               FROM c_projects WHERE c0d = :id AND deleted = 0",
            [':id' => $project_id]
        );
        if (empty($prj)) return [];
        $p = $prj[0];

        $params = [':pid' => $project_id];
        $dateClause = '';
        if ($from !== '') { $dateClause .= ' AND je.entry_date >= :f'; $params[':f'] = $from; }
        if ($to   !== '') { $dateClause .= ' AND je.entry_date <= :t'; $params[':t'] = $to;   }

        // Aggregate revenue / cogs / opex
        $agg = $db->mySelect(
            "SELECT
                IFNULL(SUM(CASE WHEN coa.account_type = 'REVENUE'                      THEN jl.credit ELSE 0 END), 0) AS revenue,
                IFNULL(SUM(CASE WHEN coa.account_type = 'EXPENSE' AND coa.account_code LIKE '5%' THEN jl.debit ELSE 0 END), 0) AS cogs,
                IFNULL(SUM(CASE WHEN coa.account_type = 'EXPENSE' AND coa.account_code LIKE '6%' THEN jl.debit ELSE 0 END), 0) AS opex
               FROM journal_lines jl
               JOIN journal_entries je ON je.c0d = jl.journal_entry_id AND je.deleted = 0 AND je.status = 'POSTED' $dateClause
               JOIN chart_of_accounts coa ON coa.c0d = jl.account_id
              WHERE jl.project_id = :pid",
            $params
        );
        $totals = $agg[0] ?? ['revenue' => 0, 'cogs' => 0, 'opex' => 0];

        // Per-account breakdown
        $byAccount = $db->mySelect(
            "SELECT coa.account_code, coa.account_name, coa.account_type,
                    IFNULL(SUM(jl.debit),  0) AS debits,
                    IFNULL(SUM(jl.credit), 0) AS credits
               FROM journal_lines jl
               JOIN journal_entries je   ON je.c0d = jl.journal_entry_id AND je.deleted = 0 AND je.status = 'POSTED' $dateClause
               JOIN chart_of_accounts coa ON coa.c0d = jl.account_id
              WHERE jl.project_id = :pid
                AND coa.account_type IN ('REVENUE','EXPENSE')
              GROUP BY coa.c0d, coa.account_code, coa.account_name, coa.account_type
              ORDER BY coa.account_code ASC",
            $params
        ) ?: [];

        // Recent journal lines for this project
        $lines = $db->mySelect(
            "SELECT je.je_no, je.entry_date, je.source_type, je.source_ref, je.narrative AS je_narr,
                    jl.account_code, jl.account_name, jl.debit, jl.credit, jl.narrative AS line_narr, jl.lpo_id
               FROM journal_lines jl
               JOIN journal_entries je   ON je.c0d = jl.journal_entry_id AND je.deleted = 0 AND je.status = 'POSTED' $dateClause
               JOIN chart_of_accounts coa ON coa.c0d = jl.account_id
              WHERE jl.project_id = :pid
                AND coa.account_type IN ('REVENUE','EXPENSE')
              ORDER BY je.entry_date DESC, je.c0d DESC, jl.line_no ASC",
            $params
        ) ?: [];

        // Planned figures from Client POs
        $planRs = $db->mySelect(
            "SELECT COUNT(*) AS cpo_count,
                    IFNULL(SUM(subtotal_sell), 0) AS planned_revenue,
                    IFNULL(SUM(subtotal_cog),  0) AS planned_cog,
                    IFNULL(SUM(total),         0) AS planned_total_incl_vat
               FROM goods_client_pos WHERE project_id = :pid AND deleted = 0",
            [':pid' => $project_id]
        );
        $planned = $planRs[0] ?? ['cpo_count' => 0, 'planned_revenue' => 0, 'planned_cog' => 0, 'planned_total_incl_vat' => 0];

        return [
            'project' => $p,
            'totals'  => [
                'revenue'      => (float)$totals['revenue'],
                'cogs'         => (float)$totals['cogs'],
                'opex'         => (float)$totals['opex'],
                'gross_margin' => (float)$totals['revenue'] - (float)$totals['cogs'],
                'net_margin'   => (float)$totals['revenue'] - (float)$totals['cogs'] - (float)$totals['opex'],
            ],
            'planned' => [
                'cpo_count'       => (int)$planned['cpo_count'],
                'revenue'         => (float)$planned['planned_revenue'],
                'cog'             => (float)$planned['planned_cog'],
                'total_incl_vat'  => (float)$planned['planned_total_incl_vat'],
                'gross_margin'    => (float)$planned['planned_revenue'] - (float)$planned['planned_cog'],
            ],
            'by_account' => $byAccount,
            'lines'      => $lines,
        ];
    }
}
