<?php
declare(strict_types=1);

/**
 * modules/accounts/export/transactions_csv.php
 *
 * Export general-ledger transactions as CSV with the same filters as the ledger list.
 *
 * Query params (all optional):
 *   from=YYYY-MM-DD
 *   to=YYYY-MM-DD
 *   type=all|income|expense
 *   account=ACCOUNT_CODE
 *   q=free text (matches notes, account code/name, booking ref, client name)
 *   min=number   (minimum amount)
 *   max=number   (maximum amount)
 *   booking_id=ID  (exact)
 *   rental_id=ID   (exact)
 *
 * Output columns:
 *   id, date, type, account_code, account_name, amount, signed_amount, booking_id, booking_ref, client_name, rental_id, notes
 */

////////////////////////////////////////////////////////////////
// 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);
$type   = strtolower((string)($_GET['type'] ?? 'all'));       // all|income|expense
$account= trim((string)($_GET['account'] ?? ''));
$q      = trim((string)($_GET['q'] ?? ''));
$minAmt = (string)($_GET['min'] ?? '');
$maxAmt = (string)($_GET['max'] ?? '');
$bookingId = (int)($_GET['booking_id'] ?? 0);
$rentalId  = (int)($_GET['rental_id']  ?? 0);

/* 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;
if (!in_array($type, ['all','income','expense'], true)) $type = 'all';

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

////////////////////////////////////////////////////////////////
// Part 3 — Build query
////////////////////////////////////////////////////////////////
$where = [
  "t.company_id = :cid",
  "t.date BETWEEN :from AND :to",
];
$args = [
  ':cid'  => $cid,
  ':from' => $from,
  ':to'   => $to,
];

if ($type !== 'all') {
  $where[]       = "t.type = :type";
  $args[':type'] = $type; // 'income' or 'expense'
}

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

if ($bookingId > 0) {
  $where[]            = "t.booking_id = :bid";
  $args[':bid']       = $bookingId;
}

if ($rentalId > 0) {
  $where[]            = "t.rental_id = :rid";
  $args[':rid']       = $rentalId;
}

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

if ($q !== '') {
  // Search in notes / account code / account name / booking ref / client name
  $where[]        = "(t.notes LIKE :q OR t.account_code LIKE :q OR COALESCE(coa.name,'') LIKE :q OR COALESCE(b.booking_ref,'') LIKE :q OR COALESCE(b.client_name,'') LIKE :q)";
  $args[':q']     = '%'.$q.'%';
}

// Main SQL
$sql = "
  SELECT
    t.id,
    t.date,
    t.type,                           -- 'income' | 'expense'
    t.account_code,
    COALESCE(coa.name, t.account_code) AS account_name,
    t.amount,
    CASE WHEN t.type='expense' THEN -t.amount ELSE t.amount END AS signed_amount,
    t.booking_id,
    b.booking_ref,
    b.client_name,
    t.rental_id,
    t.notes
  FROM transactions t
  LEFT JOIN chart_of_accounts coa
         ON coa.company_id = t.company_id
        AND coa.account_code = t.account_code
  LEFT JOIN bookings b
         ON b.id = t.booking_id
        AND b.company_id = t.company_id
  WHERE ".implode(' AND ', $where)."
  ORDER BY t.date ASC, t.id ASC
";

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

$totalIncome = 0.0;
$totalExpense = 0.0;
foreach ($rows as $r) {
  $amt = (float)$r['amount'];
  if ((string)$r['type'] === 'income')  $totalIncome  += $amt;
  else                                   $totalExpense += $amt;
}
$net = $totalIncome - $totalExpense;

////////////////////////////////////////////////////////////////
// Part 5 — Output CSV
////////////////////////////////////////////////////////////////
$filename = sprintf(
  'transactions_%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, ['General Ledger — Transactions']);
fputcsv($fh, ['Company ID', $cid]);
fputcsv($fh, ['Period', $from.' to '.$to]);
$filtersLine = [];
if ($type !== 'all')   $filtersLine[] = 'type='.$type;
if ($account !== '')   $filtersLine[] = 'account='.$account;
if ($q !== '')         $filtersLine[] = 'q='.preg_replace('/\s+/', ' ', $q);
if ($min !== null)     $filtersLine[] = 'min='.$min;
if ($max !== null)     $filtersLine[] = 'max='.$max;
if ($bookingId > 0)    $filtersLine[] = 'booking_id='.$bookingId;
if ($rentalId  > 0)    $filtersLine[] = 'rental_id='.$rentalId;
if ($filtersLine)      fputcsv($fh, ['Filters', implode('; ', $filtersLine)]);
fputcsv($fh, []);

/* Column headers */
fputcsv($fh, [
  'ID','Date','Type','Account Code','Account Name',
  'Amount','Signed Amount',
  'Booking ID','Booking Ref','Client Name',
  'Rental ID','Notes'
]);

/* Rows */
foreach ($rows as $r) {
  fputcsv($fh, [
    (int)$r['id'],
    (string)$r['date'],
    (string)$r['type'],
    (string)$r['account_code'],
    (string)$r['account_name'],
    number_format((float)$r['amount'], 2, '.', ''),
    number_format((float)$r['signed_amount'], 2, '.', ''),
    ($r['booking_id'] !== null ? (int)$r['booking_id'] : ''),
    (string)($r['booking_ref'] ?? ''),
    (string)($r['client_name'] ?? ''),
    ($r['rental_id'] !== null ? (int)$r['rental_id'] : ''),
    // Ensure newlines/tabs in notes don't break CSV cells (fputcsv handles quoting)
    (string)($r['notes'] ?? ''),
  ]);
}

/* Totals footer */
fputcsv($fh, []);
fputcsv($fh, ['TOTAL INCOME', '', '', '', '', number_format($totalIncome, 2, '.', ''), '', '', '', '', '', '']);
fputcsv($fh, ['TOTAL EXPENSE', '', '', '', '', number_format($totalExpense, 2, '.', ''), '', '', '', '', '', '']);
fputcsv($fh, ['NET', '', '', '', '', number_format($net, 2, '.', ''), '', '', '', '', '', '']);

fclose($fh);
exit;
