<?php

class BudgetController extends Controller
{
    public function index(Request $request): void
    {
        $fiscalYears = $this->db()->fetchAll("SELECT * FROM fiscal_years ORDER BY start_date DESC");
        $this->view('budgets/index', [
            'title' => lang('adv_finance.budgets'),
            'breadcrumbs' => [['label' => lang('adv_finance.title'), 'url' => '/advanced-finance/installments'], ['label' => lang('adv_finance.budgets')]],
            'fiscalYears' => $fiscalYears,
        ], 'app');
    }

    public function datatable(Request $request): void
    {
        $db = $this->db();
        $draw = (int)$request->get('draw', 1);
        $start = (int)$request->get('start', 0);
        $length = (int)$request->get('length', 25);
        $search = $request->get('search')['value'] ?? '';
        $yearFilter = (int)$request->get('fiscal_year_id', 0);

        $where = '1=1'; $params = [];
        if ($yearFilter) { $where .= ' AND bh.fiscal_year_id = ?'; $params[] = $yearFilter; }
        if ($search) { $where .= ' AND (ga.account_code LIKE ? OR ga.name_en LIKE ?)'; $like = "%{$search}%"; $params = array_merge($params, [$like,$like]); }

        $total = (int)$db->fetchColumn("SELECT COUNT(*) FROM budget_heads bh JOIN gl_accounts ga ON bh.account_id=ga.id JOIN fiscal_years fy ON bh.fiscal_year_id=fy.id");
        $filtered = (int)$db->fetchColumn("SELECT COUNT(*) FROM budget_heads bh JOIN gl_accounts ga ON bh.account_id=ga.id JOIN fiscal_years fy ON bh.fiscal_year_id=fy.id WHERE {$where}", $params);
        $rows = $db->fetchAll(
            "SELECT bh.*, ga.account_code, ga.name_en AS account_name, fy.year_code,
                    d.name_en AS dept_name
             FROM budget_heads bh
             JOIN gl_accounts ga ON bh.account_id = ga.id
             JOIN fiscal_years fy ON bh.fiscal_year_id = fy.id
             LEFT JOIN departments d ON bh.department_id = d.id
             WHERE {$where}
             ORDER BY bh.id DESC LIMIT {$length} OFFSET {$start}",
            $params
        );
        $data = [];
        foreach ($rows as $r) {
            $data[] = [
                'id' => $r['id'], 'year_code' => $r['year_code'],
                'account_code' => $r['account_code'], 'account_name' => $r['account_name'],
                'dept_name' => $r['dept_name'] ?? '—',
                'annual_budget' => format_money($r['annual_budget']),
                'actual_spent' => format_money($r['actual_spent']),
                'variance' => format_money($r['annual_budget'] - $r['actual_spent']),
                'status' => $r['status'], 'status_badge' => status_badge($r['status']),
            ];
        }
        $this->json(['draw' => $draw, 'recordsTotal' => $total, 'recordsFiltered' => $filtered, 'data' => $data]);
    }

    public function create(Request $request): void
    {
        $db = $this->db();
        $accounts = $db->fetchAll("SELECT id, account_code, name_en FROM gl_accounts WHERE is_active = 1 AND is_postable = 1 AND account_type = 'expense' ORDER BY account_code");
        $fiscalYears = $db->fetchAll("SELECT * FROM fiscal_years ORDER BY start_date DESC LIMIT 5");
        $departments = $db->fetchAll("SELECT * FROM departments WHERE is_active = 1 ORDER BY name_en");

        $this->view('budgets/form', [
            'title' => lang('common.create') . ' ' . lang('adv_finance.budgets'),
            'breadcrumbs' => [['label' => lang('adv_finance.budgets'), 'url' => '/advanced-finance/budgets'], ['label' => lang('common.create')]],
            'budget' => null, 'accounts' => $accounts, 'fiscalYears' => $fiscalYears, 'departments' => $departments,
        ], 'app');
    }

    public function store(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) { $this->json(['success' => false, 'message' => 'Invalid CSRF'], 403); return; }
        $validator = new Validator($request->all(), [
            'fiscal_year_id' => 'required|integer|exists:fiscal_years,id',
            'account_id'     => 'required|integer|exists:gl_accounts,id',
            'department_id'  => 'nullable|integer',
            'annual_budget'  => 'required|decimal|min:0',
            'q1_budget'      => 'required|decimal|min:0',
            'q2_budget'      => 'required|decimal|min:0',
            'q3_budget'      => 'required|decimal|min:0',
            'q4_budget'      => 'required|decimal|min:0',
            'notes'          => 'nullable|string|max:2000',
        ]);
        if ($validator->fails()) { $this->json(['success' => false, 'errors' => $validator->errors()], 422); return; }
        $d = $validator->validated();
        $d['status'] = 'draft';
        $d['variance'] = $d['annual_budget'];
        $id = $this->db()->insert('budget_heads', $d);
        $this->audit('create', 'budget_heads', $id, 'Created budget');
        $this->json(['success' => true, 'message' => lang('adv_finance.saved'), 'redirect' => '/advanced-finance/budgets']);
    }

    public function edit(Request $request): void
    {
        $id = (int)$request->routeParam('id');
        $db = $this->db();
        $budget = $db->fetchOne("SELECT * FROM budget_heads WHERE id = ?", [$id]);
        if (!$budget) { $this->abort(404, 'Not found'); return; }
        $accounts = $db->fetchAll("SELECT id, account_code, name_en FROM gl_accounts WHERE is_active = 1 AND is_postable = 1 AND account_type = 'expense' ORDER BY account_code");
        $fiscalYears = $db->fetchAll("SELECT * FROM fiscal_years ORDER BY start_date DESC LIMIT 5");
        $departments = $db->fetchAll("SELECT * FROM departments WHERE is_active = 1 ORDER BY name_en");

        $this->view('budgets/form', [
            'title' => lang('common.edit') . ' ' . lang('adv_finance.budgets'),
            'breadcrumbs' => [['label' => lang('adv_finance.budgets'), 'url' => '/advanced-finance/budgets'], ['label' => lang('common.edit')]],
            'budget' => $budget, 'accounts' => $accounts, 'fiscalYears' => $fiscalYears, 'departments' => $departments,
        ], 'app');
    }

    public function update(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) { $this->json(['success' => false, 'message' => 'Invalid CSRF'], 403); return; }
        $id = (int)$request->routeParam('id');
        $validator = new Validator($request->all(), [
            'annual_budget' => 'required|decimal|min:0',
            'q1_budget' => 'required|decimal|min:0', 'q2_budget' => 'required|decimal|min:0',
            'q3_budget' => 'required|decimal|min:0', 'q4_budget' => 'required|decimal|min:0',
            'notes' => 'nullable|string|max:2000',
        ]);
        if ($validator->fails()) { $this->json(['success' => false, 'errors' => $validator->errors()], 422); return; }
        $d = $validator->validated();
        $budget = $this->db()->fetchOne("SELECT actual_spent FROM budget_heads WHERE id = ?", [$id]);
        $d['variance'] = $d['annual_budget'] - ($budget['actual_spent'] ?? 0);
        $this->db()->update('budget_heads', $d, 'id = ?', [$id]);
        $this->audit('update', 'budget_heads', $id, 'Updated budget');
        $this->json(['success' => true, 'message' => lang('adv_finance.saved'), 'redirect' => '/advanced-finance/budgets']);
    }
}