<?php
declare(strict_types=1);

/**
 * modules/accounts/export/expenses_csv.php
 *
 * Export operating expenses as CSV with filters similar to the Expenses list.
 *
 * Query params (all optional):
 *   from=YYYY-MM-DD
 *   to=YYYY-MM-DD
 *   account=ACCOUNT_CODE           (filter by COA account_code)
 *   q=free text                    (matches vendor, notes, milestone, account name/code)
 *   min=number                     (minimum amount)
 *   max=number                     (maximum amount)
 *   file=yes|no                    (has attachment or not)
 *
 * Output columns:
 *   id, date, account_code, account_name, amount, vendor, milestone, notes, attachment_url
 */

////////////////////////////////////////////////////////////////
// Part 1 — Bootstrap & guards
////////////////////////////////////////////////////////////////
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');
}

////////////////////////////////////////////////////////////////
// Part 2 — Inputs (defaults = current month)
////////////////////////////////////////////////////////////////
$today      = new DateTimeImmutable('today');
$monthStart = $today->modify('first day of this month')->format('Y-m-d');
$monthEnd   = $today->modify('last day of this month')->format('Y-m-d');

$from     = (string)($_GET['from'] ?? $monthStart);
$to       = (string)($_GET['to']   ?? $monthEnd);
$account  = trim((string)($_GET['account'] ?? ''));
$q        = trim((string)($_GET['q'] ?? ''));
$minAmt   = (string)($_GET['min'] ?? '');
$maxAmt   = (string)($_GET['max'] ?? '');
$fileFlag = strtolower(trim((string)($_GET['file'] ?? ''))); // yes|no|''

/* Normalize */
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $from)) $from = $monthStart;
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $to))   $to   = $monthEnd;

$min = is_numeric($minAmt) ? (float)$minAmt : null;
$max = is_numeric($maxAmt) ? (float)$maxAmt : null;

////////////////////////////////////////////////////////////////
// Part 3 — Build query
////////////////////////////////////////////////////////////////
/* We try to be resilient to column naming for attachments:
   - Some installs use 'attachment_url', others 'receipt_url' or 'file_url'.
   We export a single alias 'attachment_url'. */
$attachExpr = "NULLIF(COALESCE(e.attachment_url, e.receipt_url, e.file_url, ''), '')";

$where = [
  "e.company_id = :cid",
  "e.date BETWEEN :from AND :to",
];
$args = [
  ':cid'  => $cid,
  ':from' => $from,
  ':to'   => $to,
];

if ($account !== '') {
  $where[]          = "e.account_code = :account";
  $args[':account'] = $account;
}

if ($min !== null) {
  $where[]         = "e.amount >= :minamt";
  $args[':minamt'] = $min;
}
if ($max !== null) {
  $where[]         = "e.amount <= :maxamt";
  $args[':maxamt'] = $max;
}

if ($q !== '') {
  $where[]        = "(e.vendor LIKE :q OR e.notes LIKE :q OR e.milestone LIKE :q OR e.account_code LIKE :q OR COALESCE(coa.name,'') LIKE :q)";
  $args[':q']     = '%'.$q.'%';
}

if ($fileFlag === 'yes') {
  $where[] = "$attachExpr IS NOT NULL";
} elseif ($fileFlag === 'no') {
  $where[] = "$attachExpr IS NULL";
}

// Main SQL
$sql = "
  SELECT
    e.id,
    e.date,
    e.account_code,
    COALESCE(coa.name, e.account_code) AS account_name,
    e.amount,
    e.vendor,
    e.milestone,
    e.notes,
    $attachExpr AS attachment_url
  FROM expenses e
  LEFT JOIN chart_of_accounts coa
         ON coa.company_id = e.company_id
        AND coa.account_code = e.account_code
  WHERE ".implode(' AND ', $where)."
  ORDER BY e.date ASC, e.id ASC
";

////////////////////////////////////////////////////////////////
// Part 4 — Fetch rows & totals
////////////////////////////////////////////////////////////////
$stmt = db()->prepare($sql);
$stmt->execute($args);
$rows = $stmt->fetchAll();

$total = 0.0;
foreach ($rows as $r) {
  $total += (float)$r['amount'];
}

////////////////////////////////////////////////////////////////
// Part 5 — Output CSV
////////////////////////////////////////////////////////////////
$filename = sprintf(
  'expenses_%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 BOM for Excel:
// fwrite($fh, "\xEF\xBB\xBF");

/* Header block */
fputcsv($fh, ['Expenses Export']);
fputcsv($fh, ['Company ID', $cid]);
fputcsv($fh, ['Period', $from.' to '.$to]);

$filters = [];
if ($account !== '') $filters[] = 'account='.$account;
if ($q !== '')       $filters[] = 'q='.preg_replace('/\s+/', ' ', $q);
if ($min !== null)   $filters[] = 'min='.$min;
if ($max !== null)   $filters[] = 'max='.$max;
if ($fileFlag !== '')$filters[] = 'file='.$fileFlag;
if ($filters)        fputcsv($fh, ['Filters', implode('; ', $filters)]);
fputcsv($fh, []);

/* Column headers */
fputcsv($fh, [
  'ID','Date','Account Code','Account Name','Amount',
  'Vendor','Milestone','Notes','Attachment URL'
]);

/* Rows */
foreach ($rows as $r) {
  fputcsv($fh, [
    (int)$r['id'],
    (string)$r['date'],
    (string)$r['account_code'],
    (string)$r['account_name'],
    number_format((float)$r['amount'], 2, '.', ''),
    (string)($r['vendor'] ?? ''),
    (string)($r['milestone'] ?? ''),
    (string)($r['notes'] ?? ''),
    (string)($r['attachment_url'] ?? ''),
  ]);
}

/* Totals footer */
fputcsv($fh, []);
fputcsv($fh, ['TOTAL', '', '', '', number_format($total, 2, '.', ''), '', '', '', '']);

fclose($fh);
exit;
