<?php
class ExpenseController extends Controller
{
    public function index(): void
    {
        Auth::requirePermission('expenses.view');

        $from   = $this->get('from', date('Y-m-d'));
        $to     = $this->get('to',   date('Y-m-d'));
        $catId  = (int) $this->get('category', 0);
        $search = trim($this->get('search', ''));

        $where  = ['e.expense_date BETWEEN ? AND ?'];
        $params = [$from, $to];

        if ($catId)  { $where[] = 'e.expense_category_id = ?'; $params[] = $catId; }
        if ($search) { $where[] = '(e.title LIKE ? OR e.reference LIKE ?)'; $params[] = "%$search%"; $params[] = "%$search%"; }

        $sql = 'SELECT e.*, ec.category_name, u.name AS created_by_name
                FROM expenses e
                JOIN expense_categories ec ON ec.id = e.expense_category_id
                LEFT JOIN users u ON u.id = e.created_by
                WHERE ' . implode(' AND ', $where) . '
                ORDER BY e.expense_date DESC, e.id DESC';

        $pagination = Database::paginate($sql, $params, max(1, (int)$this->get('page', 1)));

        $categories = Database::fetchAll("SELECT id, category_name FROM expense_categories WHERE status='active' ORDER BY category_name");

        // Period totals
        $totalAmt   = (float) Database::fetchColumn(
            'SELECT COALESCE(SUM(e.amount),0) FROM expenses e WHERE ' . implode(' AND ', $where),
            $params
        );
        $totalCount = (int) Database::fetchColumn(
            'SELECT COUNT(*) FROM expenses e WHERE ' . implode(' AND ', $where),
            $params
        );

        // Category breakdown for the period (unfiltered by category)
        $breakWhere  = ['e.expense_date BETWEEN ? AND ?'];
        $breakParams = [$from, $to];
        if ($search) { $breakWhere[] = '(e.title LIKE ? OR e.reference LIKE ?)'; $breakParams[] = "%$search%"; $breakParams[] = "%$search%"; }

        $breakdown = Database::fetchAll(
            'SELECT ec.category_name,
                    COALESCE(SUM(e.amount),0) AS total,
                    COUNT(*) AS count
             FROM expenses e
             JOIN expense_categories ec ON ec.id = e.expense_category_id
             WHERE ' . implode(' AND ', $breakWhere) . '
             GROUP BY ec.id, ec.category_name
             ORDER BY total DESC',
            $breakParams
        );

        // Days in period for avg
        $days = max(1, (int)((strtotime($to) - strtotime($from)) / 86400) + 1);

        $pageTitle  = 'Expenses';
        $breadcrumb = [['label' => 'Expenses']];
        ob_start();
        require APP_PATH . '/views/expenses/index.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }
    public function create(): void
    {
        Auth::requirePermission('expenses.view');
        $categories = Database::fetchAll("SELECT id,category_name FROM expense_categories WHERE status='active' ORDER BY category_name");
        $pageTitle = 'Add Expense';
        ob_start(); require APP_PATH.'/views/expenses/form.php'; $content = ob_get_clean();
        require APP_PATH.'/views/layouts/main.php';
    }
    public function store(): void
    {
        Auth::requirePermission('expenses.create');
        Auth::requireCsrf();
        $catId  = (int)$this->post('expense_category_id');
        $title  = trim($this->post('title',''));
        $amount = (float)$this->post('amount',0);
        $date   = $this->post('expense_date', date('Y-m-d'));
        $ref    = trim($this->post('reference',''));
        $note   = trim($this->post('note',''));
        if (!$catId || !$title || $amount <= 0) {
            redirectWith(APP_URL.'/expenses','error','Category, title and amount are required.');
            return;
        }
        Database::insert(
            'INSERT INTO expenses (expense_category_id,title,amount,expense_date,reference,note,created_by)
             VALUES (?,?,?,?,?,?,?)',
            [$catId,$title,round($amount,2),$date,$ref ?: null,$note ?: null,Auth::id()]
        );
        Auth::logAudit(Auth::id(),'expense_create','expenses',"Expense: {$title} – {$amount}");
        redirectWith(APP_URL.'/expenses','success','Expense recorded successfully.');
    }

    public function delete(string $id): void
    {
        Auth::requirePermission('expenses.create');
        Auth::requireCsrf();
        $exp = Database::fetchOne('SELECT title FROM expenses WHERE id=?', [(int)$id]);
        if (!$exp) {
            redirectWith(APP_URL.'/expenses','error','Expense not found.');
            return;
        }
        Database::execute('DELETE FROM expenses WHERE id=?', [(int)$id]);
        Auth::logAudit(Auth::id(),'expense_delete','expenses',"Deleted: {$exp['title']}");
        redirectWith(APP_URL.'/expenses','success','Expense deleted.');
    }
}