<?php
require_once __DIR__ . '/../config.php';
require_once __DIR__ . '/../includes/auth.php';
require_once __DIR__ . '/../includes/db.php';
require_login();
header('Content-Type: application/json');
$mysqli = db();
$action = $_GET['action'] ?? $_POST['action'] ?? 'list';

// Finance permission: admins, account users, and sales users can manage expenditures (branch-scoped)
function ensure_fin_role() { if (!has_role(['admin','super_admin','account_user','sales_user'])) json_response(['message'=>'Forbidden'], 403); }

function sess_branch_id(){ return $_SESSION['branch_id'] ?? null; }
function is_admin_user(){ return has_role(['admin','super_admin']); }

function save_upload($field) {
  if (!isset($_FILES[$field]) || !is_uploaded_file($_FILES[$field]['tmp_name'])) return null;
  $dir = __DIR__ . '/../uploads/receipts';
  if (!is_dir($dir)) @mkdir($dir, 0775, true);
  $name = time() . '_' . preg_replace('/[^a-zA-Z0-9._-]/','_', $_FILES[$field]['name']);
  $target = $dir . '/' . $name;
  if (!move_uploaded_file($_FILES[$field]['tmp_name'], $target)) return null;
  return 'uploads/receipts/' . $name;
}

function excel_csv_output($filename, $headers, $rows){
  header('Content-Type: application/vnd.ms-excel; charset=UTF-8');
  header('Content-Disposition: attachment; filename="'.$filename.'"');
  header('Pragma: no-cache');
  header('Expires: 0');
  echo "\xEF\xBB\xBF"; // UTF-8 BOM for Excel
  $out = fopen('php://output', 'w');
  fputcsv($out, $headers);
  foreach ($rows as $r){ fputcsv($out, $r); }
  fclose($out);
  exit;
}

switch ($action) {
  case 'list':
    $q = trim($_GET['q'] ?? '');
    $category_id = (int)($_GET['category_id'] ?? 0);
    $from = $_GET['from'] ?? '';
    $to = $_GET['to'] ?? '';
    $branch_id = isset($_GET['branch_id']) ? (int)$_GET['branch_id'] : null;
    $where = [];$types='';$params=[];
    if ($q !== '') { $where[] = '(e.description LIKE CONCAT("%", ?, "%") OR e.vendor LIKE CONCAT("%", ?, "%") OR e.invoice_no LIKE CONCAT("%", ?, "%"))'; $types.='sss'; $params[]=$q; $params[]=$q; $params[]=$q; }
    if ($category_id) { $where[] = 'e.category_id = ?'; $types.='i'; $params[]=$category_id; }
    if ($from !== '') { $where[] = 'e.expense_date >= ?'; $types.='s'; $params[]=$from; }
    if ($to !== '') { $where[] = 'e.expense_date <= ?'; $types.='s'; $params[]=$to; }
    // Branch scope
    if (!is_admin_user()){
      $bid = (int)(sess_branch_id() ?? 0); $where[]='e.branch_id = ?'; $types.='i'; $params[]=$bid;
    } else if (!empty($branch_id)) { $where[]='e.branch_id = ?'; $types.='i'; $params[]=$branch_id; }
    $sql = 'SELECT e.id, e.expense_date, c.name AS category_name, e.description, e.vendor, e.amount, e.payment_method, e.invoice_no, e.receipt_path, e.branch_id FROM expenditures e LEFT JOIN expenditure_categories c ON c.id=e.category_id';
    if ($where) $sql .= ' WHERE ' . implode(' AND ', $where);
    $sql .= ' ORDER BY e.expense_date DESC, e.id DESC';
    if ($where) { $stmt = $mysqli->prepare($sql); $stmt->bind_param($types, ...$params); $stmt->execute(); $res=$stmt->get_result(); }
    else { $res = $mysqli->query($sql); }
    $data=[]; while($r=$res->fetch_assoc()){ $data[]=$r; }
    json_response(['data'=>$data]);
    break;

  case 'export_report_category_excel':
    // Same as report_category but outputs Excel-readable CSV
    $period = strtolower(trim($_GET['period'] ?? 'day'));
    if (!in_array($period, ['day','week','month','year'])) $period = 'day';
    $from = $_GET['from'] ?? '';
    $to = $_GET['to'] ?? '';
    $category_id = (int)($_GET['category_id'] ?? 0);
    switch ($period) {
      case 'week':
        $bucketExpr = "DATE_FORMAT(e.expense_date, '%x-W%v')";
        break;
      case 'month':
        $bucketExpr = "DATE_FORMAT(e.expense_date, '%Y-%m')";
        break;
      case 'year':
        $bucketExpr = "DATE_FORMAT(e.expense_date, '%Y')";
        break;
      case 'day':
      default:
        $bucketExpr = 'DATE(e.expense_date)';
        break;
    }
    $where=[]; $types=''; $params=[];
    if ($from !== '') { $where[] = 'e.expense_date >= ?'; $types.='s'; $params[]=$from; }
    if ($to !== '') { $where[] = 'e.expense_date <= ?'; $types.='s'; $params[]=$to; }
    if ($category_id) { $where[] = 'e.category_id = ?'; $types.='i'; $params[]=$category_id; }
    if (!is_admin_user()){
      $bid = (int)(sess_branch_id() ?? 0); $where[]='e.branch_id = ?'; $types.='i'; $params[]=$bid;
    } else if (!empty($_GET['branch_id'])) { $where[]='e.branch_id = ?'; $types.='i'; $params[]=(int)$_GET['branch_id']; }
    $sql = "SELECT $bucketExpr AS bucket, c.name AS category_name, COALESCE(SUM(e.amount),0) AS total
            FROM expenditures e LEFT JOIN expenditure_categories c ON c.id=e.category_id";
    if ($where) $sql .= ' WHERE ' . implode(' AND ', $where);
    $sql .= ' GROUP BY bucket, c.name ORDER BY bucket ASC, category_name ASC';
    if ($where) { $stmt = $mysqli->prepare($sql); $stmt->bind_param($types, ...$params); $stmt->execute(); $res=$stmt->get_result(); }
    else { $res = $mysqli->query($sql); }
    $rows=[];
    while($r=$res->fetch_assoc()){
      $rows[] = [$r['bucket'], $r['category_name'], $r['total']];
    }
    $fn = 'expenditure_report_'.$period.'_'.date('Ymd_His').'.xls';
    excel_csv_output($fn, ['Bucket','Category','Total (LKR)'], $rows);
    break;

  case 'stats':
    $today = date('Y-m-d');
    $monthFirst = date('Y-m-01');
    $yearFirst = date('Y-01-01');
    $whereToday = "expense_date = '$today'";
    $whereMonth = "expense_date >= '$monthFirst' AND expense_date <= '$today'";
    $whereYear = "expense_date >= '$yearFirst' AND expense_date <= '$today'";
    if (!is_admin_user()){
      $bid = (int)(sess_branch_id() ?? 0);
      $whereToday .= " AND branch_id = $bid";
      $whereMonth .= " AND branch_id = $bid";
      $whereYear  .= " AND branch_id = $bid";
    } else if (!empty($_GET['branch_id'])){
      $bid = (int)$_GET['branch_id'];
      $whereToday .= " AND branch_id = $bid";
      $whereMonth .= " AND branch_id = $bid";
      $whereYear  .= " AND branch_id = $bid";
    }
    $res1 = $mysqli->query("SELECT COALESCE(SUM(amount),0) v FROM expenditures WHERE $whereToday");
    $res2 = $mysqli->query("SELECT COALESCE(SUM(amount),0) v FROM expenditures WHERE $whereMonth");
    $res3 = $mysqli->query("SELECT COALESCE(SUM(amount),0) v FROM expenditures WHERE $whereYear");
    $days = (int)date('z') + 1; // day of year index + 1
    $yearTotal = (float)($res3->fetch_assoc()['v'] ?? 0);
    $avg = $days ? ($yearTotal / $days) : 0;
    json_response(['today'=>(float)($res1->fetch_assoc()['v'] ?? 0),'month'=>(float)($res2->fetch_assoc()['v'] ?? 0),'year'=>$yearTotal,'daily_avg'=>$avg]);
    break;

  case 'report_category':
    // Params: period = day|week|month|year, optional from, to, optional category_id
    $period = strtolower(trim($_GET['period'] ?? 'day'));
    if (!in_array($period, ['day','week','month','year'])) $period = 'day';
    $from = $_GET['from'] ?? '';
    $to = $_GET['to'] ?? '';
    $category_id = (int)($_GET['category_id'] ?? 0);
    // Build bucket expression
    switch ($period) {
      case 'week':
        $bucketExpr = "DATE_FORMAT(e.expense_date, '%x-W%v')"; // ISO week
        break;
      case 'month':
        $bucketExpr = "DATE_FORMAT(e.expense_date, '%Y-%m')";
        break;
      case 'year':
        $bucketExpr = "DATE_FORMAT(e.expense_date, '%Y')";
        break;
      case 'day':
      default:
        $bucketExpr = 'DATE(e.expense_date)';
        break;
    }
    $where=[]; $types=''; $params=[];
    if ($from !== '') { $where[] = 'e.expense_date >= ?'; $types.='s'; $params[]=$from; }
    if ($to !== '') { $where[] = 'e.expense_date <= ?'; $types.='s'; $params[]=$to; }
    if ($category_id) { $where[] = 'e.category_id = ?'; $types.='i'; $params[]=$category_id; }
    if (!is_admin_user()){
      $bid = (int)(sess_branch_id() ?? 0); $where[]='e.branch_id = ?'; $types.='i'; $params[]=$bid;
    } else if (!empty($_GET['branch_id'])) { $where[]='e.branch_id = ?'; $types.='i'; $params[]=(int)$_GET['branch_id']; }
    $sql = "SELECT c.name AS category_name, $bucketExpr AS bucket, COALESCE(SUM(e.amount),0) AS total
            FROM expenditures e LEFT JOIN expenditure_categories c ON c.id=e.category_id";
    if ($where) $sql .= ' WHERE ' . implode(' AND ', $where);
    $sql .= ' GROUP BY c.name, bucket ORDER BY bucket ASC, category_name ASC';
    if ($where) { $stmt = $mysqli->prepare($sql); $stmt->bind_param($types, ...$params); $stmt->execute(); $res=$stmt->get_result(); }
    else { $res = $mysqli->query($sql); }
    $data=[]; while($r=$res->fetch_assoc()){ $data[]=$r; }
    json_response(['data'=>$data]);
    break;

  case 'get':
    $id = (int)($_GET['id'] ?? 0);
    $stmt = $mysqli->prepare('SELECT * FROM expenditures WHERE id = ?');
    $stmt->bind_param('i', $id); $stmt->execute(); $row = $stmt->get_result()->fetch_assoc();
    if (!$row) json_response(['message'=>'Not found'],404);
    if (!is_admin_user()){
      $bid = (int)(sess_branch_id() ?? 0);
      if ((int)($row['branch_id'] ?? 0) !== $bid) json_response(['message'=>'Forbidden'],403);
    }
    json_response(['data'=>$row]);
    break;

  case 'create':
    ensure_fin_role();
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message'=>'Invalid CSRF'], 400);
    $category_id = (int)($_POST['category_id'] ?? 0);
    $expense_date = $_POST['expense_date'] ?? '';
    $description = trim($_POST['description'] ?? '');
    $amount = (float)($_POST['amount'] ?? 0);
    $payment_method = trim($_POST['payment_method'] ?? '');
    $vendor = trim($_POST['vendor'] ?? '');
    $invoice_no = trim($_POST['invoice_no'] ?? '');
    $is_recurring = isset($_POST['is_recurring']) ? 1 : 0;
    $notes = trim($_POST['notes'] ?? '');
    if (!$category_id || !$expense_date || $description==='' || !$amount || $payment_method==='') json_response(['message'=>'Missing fields'], 400);
    $receipt_path = save_upload('receipt');
    $uid = current_user()['id'] ?? null;
    $branchId = (int)(sess_branch_id() ?? 0);
    $stmt = $mysqli->prepare('INSERT INTO expenditures (category_id, expense_date, description, amount, payment_method, vendor, invoice_no, receipt_path, is_recurring, notes, created_by, branch_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)');
    $stmt->bind_param('issdssssisii', $category_id, $expense_date, $description, $amount, $payment_method, $vendor, $invoice_no, $receipt_path, $is_recurring, $notes, $uid, $branchId);
    if (!$stmt->execute()) json_response(['message'=>$stmt->error], 500);
    json_response(['message'=>'Created']);
    break;

  case 'update':
    ensure_fin_role();
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message'=>'Invalid CSRF'], 400);
    $id = (int)($_POST['id'] ?? 0);
    // enforce same-branch update unless admin (admins already enforced but keep check if needed later)
    if (!is_admin_user()){
      $chk=$mysqli->query('SELECT branch_id FROM expenditures WHERE id='.(int)$id)->fetch_assoc();
      if(!$chk) json_response(['message'=>'Not found'],404);
      if ((int)$chk['branch_id'] !== (int)(sess_branch_id() ?? 0)) json_response(['message'=>'Forbidden'],403);
    }
    $category_id = (int)($_POST['category_id'] ?? 0);
    $expense_date = $_POST['expense_date'] ?? '';
    $description = trim($_POST['description'] ?? '');
    $amount = (float)($_POST['amount'] ?? 0);
    $payment_method = trim($_POST['payment_method'] ?? '');
    $vendor = trim($_POST['vendor'] ?? '');
    $invoice_no = trim($_POST['invoice_no'] ?? '');
    $is_recurring = isset($_POST['is_recurring']) ? 1 : 0;
    $notes = trim($_POST['notes'] ?? '');
    if (!$id || !$category_id || !$expense_date || $description==='' || !$amount || $payment_method==='') json_response(['message'=>'Missing fields'], 400);
    $path = save_upload('receipt');
    if ($path) {
      $stmt = $mysqli->prepare('UPDATE expenditures SET category_id=?, expense_date=?, description=?, amount=?, payment_method=?, vendor=?, invoice_no=?, receipt_path=?, is_recurring=?, notes=? WHERE id=?');
      $stmt->bind_param('issdssssisi', $category_id, $expense_date, $description, $amount, $payment_method, $vendor, $invoice_no, $path, $is_recurring, $notes, $id);
    } else {
      $stmt = $mysqli->prepare('UPDATE expenditures SET category_id=?, expense_date=?, description=?, amount=?, payment_method=?, vendor=?, invoice_no=?, is_recurring=?, notes=? WHERE id=?');
      $stmt->bind_param('issdsssisi', $category_id, $expense_date, $description, $amount, $payment_method, $vendor, $invoice_no, $is_recurring, $notes, $id);
    }
    if (!$stmt->execute()) json_response(['message'=>$stmt->error], 500);
    json_response(['message'=>'Updated']);
    break;

  case 'delete':
    ensure_fin_role();
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message'=>'Invalid CSRF'], 400);
    $id = (int)($_POST['id'] ?? 0);
    if (!is_admin_user()){
      $chk=$mysqli->query('SELECT branch_id FROM expenditures WHERE id='.(int)$id)->fetch_assoc();
      if(!$chk) json_response(['message'=>'Not found'],404);
      if ((int)$chk['branch_id'] !== (int)(sess_branch_id() ?? 0)) json_response(['message'=>'Forbidden'],403);
    }
    $stmt = $mysqli->prepare('DELETE FROM expenditures WHERE id=?');
    $stmt->bind_param('i', $id);
    if (!$stmt->execute()) json_response(['message'=>$stmt->error], 500);
    json_response(['message'=>'Deleted']);
    break;

  default:
    json_response(['message'=>'Invalid action'], 400);
}
