<?php
declare(strict_types=1);

/**
 * modules/accounts/export/pnl_csv.php
 *
 * Export Profit & Loss (CSV) for a selected date range.
 * - Splits Income into: Chauffeur Sales (CHAUF_INCOME), Car Rental Sales (RENTAL_INCOME), Other Sales.
 * - Uses chart_of_accounts.type for grouping into INCOME / COGS / EXPENSE; with fallbacks.
 * - Totals: Income, COGS, Gross Profit, Operating Expenses, Net Profit.
 *
 * Query params:
 *   ?from=YYYY-MM-DD&to=YYYY-MM-DD
 */

require_once dirname(__DIR__, 3) . '/config/functions.php';
require_role(['Accounts','MD','Admin','Management']);

$user = current_user();
$cid  = (int)($user['company_id'] ?? 0);
if ($cid <= 0) {
  http_response_code(403);
  exit('Forbidden');
}

/* -----------------------------------------------------------
   Inputs (default: current month)
----------------------------------------------------------- */
$today      = new DateTimeImmutable('today');
$monthStart = $today->modify('first day of this month');
$monthEnd   = $today->modify('last day of this month');

$from = (string)($_GET['from'] ?? $monthStart->format('Y-m-d'));
$to   = (string)($_GET['to']   ?? $monthEnd->format('Y-m-d'));

if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $from)) $from = $monthStart->format('Y-m-d');
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $to))   $to   = $monthEnd->format('Y-m-d');

/* -----------------------------------------------------------
   Pull sums by account_code with COA group type
----------------------------------------------------------- */
$sql = "
  SELECT
    /* Group type from COA (fallback by known codes / transaction type) */
    UPPER(
      COALESCE(
        coa.type,
        CASE
          WHEN t.account_code IN ('CHAUF_INCOME','RENTAL_INCOME') THEN 'INCOME'
          WHEN t.account_code IN ('DRIVER_PAYOUT','PARTNER_COMM') THEN 'COGS'
          ELSE CASE WHEN t.type='income' THEN 'INCOME' ELSE 'EXPENSE' END
        END
      )
    )                                            AS group_type,
    t.account_code                               AS account_code,
    COALESCE(coa.name, t.account_code)           AS account_name,
    SUM(t.amount)                                AS amount
  FROM transactions t
  LEFT JOIN chart_of_accounts coa
         ON coa.company_id = t.company_id
        AND coa.account_code = t.account_code
  WHERE t.company_id = :cid
    AND t.date BETWEEN :from AND :to
  GROUP BY group_type, account_code, account_name
  ORDER BY group_type, account_code
";
$stmt = db()->prepare($sql);
$stmt->execute([':cid'=>$cid, ':from'=>$from, ':to'=>$to]);

$rows = $stmt->fetchAll();

/* Organize by type */
$income = [];
$cogs   = [];
$exp    = [];
foreach ($rows as $r) {
  $gt = strtoupper((string)$r['group_type']);
  if ($gt === 'INCOME')       $income[] = $r;
  elseif ($gt === 'COGS')     $cogs[]   = $r;
  else                        $exp[]    = $r; // EXPENSE (or anything else)
}

/* Income split */
$sumChauf  = 0.0;
$sumRental = 0.0;
$sumOtherI = 0.0;
foreach ($income as $r) {
  $code = (string)$r['account_code'];
  $amt  = (float)$r['amount'];
  if ($code === 'CHAUF_INCOME')       $sumChauf  += $amt;
  elseif ($code === 'RENTAL_INCOME')  $sumRental += $amt;
  else                                $sumOtherI += $amt;
}
$totalIncome = $sumChauf + $sumRental + $sumOtherI;

/* COGS totals */
$totalCOGS = 0.0;
foreach ($cogs as $r) $totalCOGS += (float)$r['amount'];

/* Expenses totals */
$totalOpEx = 0.0;
foreach ($exp as $r) $totalOpEx += (float)$r['amount'];

/* Derived */
$grossProfit = $totalIncome - $totalCOGS;
$netProfit   = $grossProfit   - $totalOpEx;

/* -----------------------------------------------------------
   Output CSV
----------------------------------------------------------- */
$filename = sprintf('pnl_%s_to_%s.csv', str_replace('-', '', $from), str_replace('-', '', $to));

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="'.$filename.'"');

$fh = fopen('php://output', 'w');

/* Optional UTF-8 BOM for Excel friendliness */
// fwrite($fh, "\xEF\xBB\xBF");

/* Title block */
fputcsv($fh, ['Profit & Loss']);
fputcsv($fh, ['Company ID', $cid]);
fputcsv($fh, ['Period', $from . ' to ' . $to]);
fputcsv($fh, []);

/* Income section */
fputcsv($fh, ['Income']);
fputcsv($fh, ['Account Code', 'Account Name', 'Amount']);
fputcsv($fh, ['CHAUF_INCOME', 'Chauffeur Sales', number_format($sumChauf, 2, '.', '')]);
fputcsv($fh, ['RENTAL_INCOME', 'Car Rental Sales', number_format($sumRental, 2, '.', '')]);

/* Other income lines (non CHAUF/RENTAL) for transparency */
foreach ($income as $r) {
  $code = (string)$r['account_code'];
  if ($code === 'CHAUF_INCOME' || $code === 'RENTAL_INCOME') continue;
  fputcsv($fh, [$code, (string)$r['account_name'], number_format((float)$r['amount'], 2, '.', '')]);
}
/* Income total */
fputcsv($fh, []);
fputcsv($fh, ['TOTAL INCOME', '', number_format($totalIncome, 2, '.', '')]);
fputcsv($fh, []);

/* COGS section */
fputcsv($fh, ['Cost of Goods Sold (COGS)']);
fputcsv($fh, ['Account Code', 'Account Name', 'Amount']);
foreach ($cogs as $r) {
  fputcsv($fh, [
    (string)$r['account_code'],
    (string)$r['account_name'],
    number_format((float)$r['amount'], 2, '.', '')
  ]);
}
fputcsv($fh, []);
fputcsv($fh, ['TOTAL COGS', '', number_format($totalCOGS, 2, '.', '')]);
fputcsv($fh, []);

/* Gross Profit */
fputcsv($fh, ['GROSS PROFIT', '', number_format($grossProfit, 2, '.', '')]);
fputcsv($fh, []);

/* Operating Expenses */
fputcsv($fh, ['Operating Expenses']);
fputcsv($fh, ['Account Code', 'Account Name', 'Amount']);
foreach ($exp as $r) {
  fputcsv($fh, [
    (string)$r['account_code'],
    (string)$r['account_name'],
    number_format((float)$r['amount'], 2, '.', '')
  ]);
}
fputcsv($fh, []);
fputcsv($fh, ['TOTAL EXPENSES', '', number_format($totalOpEx, 2, '.', '')]);
fputcsv($fh, []);

/* Net Profit */
fputcsv($fh, ['NET PROFIT', '', number_format($netProfit, 2, '.', '')]);

fclose($fh);
exit;
