<?php

class Accounting extends BaseModel
{
    public function dashboard(): array
    {
        return [
            'receivables' => (float) $this->db->query("SELECT COALESCE(SUM(GREATEST(balance_due, total - COALESCE((SELECT SUM(amount) FROM payments WHERE payments.invoice_id = invoices.id),0))),0) FROM invoices WHERE status NOT IN ('paid','void','cancelled','written_off')")->fetchColumn(),
            'payables' => (float) $this->db->query("SELECT COALESCE(SUM(balance_due),0) FROM vendor_bills WHERE status NOT IN ('paid','void')")->fetchColumn(),
            'month_expenses' => (float) $this->db->query("SELECT COALESCE(SUM(amount + tax_amount),0) FROM expenses WHERE status IN ('approved','reimbursed','posted') AND MONTH(expense_date)=MONTH(CURDATE()) AND YEAR(expense_date)=YEAR(CURDATE())")->fetchColumn(),
            'cash' => (float) $this->db->query("SELECT COALESCE(SUM(current_balance),0) FROM bank_accounts WHERE active=1")->fetchColumn(),
            'pending_expenses' => (int) $this->db->query("SELECT COUNT(*) FROM expenses WHERE status IN ('submitted','under_review')")->fetchColumn(),
            'overdue_bills' => (int) $this->db->query("SELECT COUNT(*) FROM vendor_bills WHERE due_date<CURDATE() AND status NOT IN ('paid','void')")->fetchColumn(),
        ];
    }

    public function expenseCategories(): array
    {
        return $this->db->query('SELECT * FROM expense_categories WHERE active=1 ORDER BY name')->fetchAll();
    }

    public function expenses(?string $search = null, ?int $driverId = null): array
    {
        $sql = 'SELECT expenses.*, expense_categories.name AS category_name, vendors.company_name AS vendor_name, loads.load_number, drivers.name AS driver_name, trucks.unit_number
                FROM expenses
                LEFT JOIN expense_categories ON expense_categories.id=expenses.category_id
                LEFT JOIN vendors ON vendors.id=expenses.vendor_id
                LEFT JOIN loads ON loads.id=expenses.load_id
                LEFT JOIN drivers ON drivers.id=expenses.driver_id
                LEFT JOIN trucks ON trucks.id=expenses.truck_id WHERE 1=1';
        $params=[];
        if ($driverId) { $sql .= ' AND expenses.driver_id=?'; $params[]=$driverId; }
        if ($search) { $sql .= ' AND (expenses.expense_number LIKE ? OR expense_categories.name LIKE ? OR vendors.company_name LIKE ? OR loads.load_number LIKE ?)'; $t="%{$search}%"; array_push($params,$t,$t,$t,$t); }
        $sql .= ' ORDER BY expenses.id DESC';
        $stmt=$this->db->prepare($sql); $stmt->execute($params); return $stmt->fetchAll();
    }

    public function createExpense(array $data): int
    {
        $number=trim((string)($data['expense_number']??'')) ?: next_reference('EXP-','expenses','expense_number');
        $stmt=$this->db->prepare('INSERT INTO expenses (expense_number,category_id,vendor_id,load_id,driver_id,truck_id,trailer_id,border_crossing_id,expense_date,amount,tax_amount,payment_method,status,latitude,longitude,location_address,description,submitted_by) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)');
        $stmt->execute([$number,$data['category_id']?:null,$data['vendor_id']?:null,$data['load_id']?:null,$data['driver_id']?:null,$data['truck_id']?:null,$data['trailer_id']?:null,$data['border_crossing_id']?:null,$data['expense_date']?:date('Y-m-d'),(float)($data['amount']??0),(float)($data['tax_amount']??0),trim((string)($data['payment_method']??''))?:null,$data['status']??'submitted',$data['latitude']!==''?($data['latitude']??null):null,$data['longitude']!==''?($data['longitude']??null):null,trim((string)($data['location_address']??''))?:null,trim((string)($data['description']??''))?:null,$_SESSION['user_id']??null]);
        return (int)$this->db->lastInsertId();
    }

    public function attachExpenseDocument(int $expenseId, int $documentId): void
    {
        $this->db->prepare('UPDATE expenses SET receipt_document_id=? WHERE id=?')->execute([$documentId,$expenseId]);
    }

    public function setExpenseStatus(int $id, string $status): void
    {
        $allowed=['submitted','under_review','approved','rejected','reimbursed','posted'];
        if(!in_array($status,$allowed,true)) return;
        $sql='UPDATE expenses SET status=?'; $params=[$status];
        if($status==='approved'){ $sql.=', approved_by=?, approved_at=NOW()'; $params[]=$_SESSION['user_id']??null; }
        $sql.=' WHERE id=?'; $params[]=$id; $this->db->prepare($sql)->execute($params);
        if($status==='approved') $this->postExpense($id);
    }

    public function vendors(): array { return $this->db->query('SELECT * FROM vendors ORDER BY company_name')->fetchAll(); }
    public function createVendor(array $d): int
    {
        $s=$this->db->prepare('INSERT INTO vendors (company_name,contact_name,email,phone,tax_id,address,payment_terms,status) VALUES (?,?,?,?,?,?,?,?)');
        $s->execute([trim($d['company_name']),trim((string)($d['contact_name']??''))?:null,trim((string)($d['email']??''))?:null,trim((string)($d['phone']??''))?:null,trim((string)($d['tax_id']??''))?:null,trim((string)($d['address']??''))?:null,trim((string)($d['payment_terms']??''))?:null,$d['status']??'active']);
        return (int)$this->db->lastInsertId();
    }

    public function bills(): array
    {
        return $this->db->query('SELECT vendor_bills.*,vendors.company_name FROM vendor_bills JOIN vendors ON vendors.id=vendor_bills.vendor_id ORDER BY vendor_bills.id DESC')->fetchAll();
    }
    public function createBill(array $d): int
    {
        $total=(float)($d['subtotal']??0)+(float)($d['tax']??0); $n=trim((string)($d['bill_number']??''))?:next_reference('BILL-','vendor_bills','bill_number');
        $s=$this->db->prepare('INSERT INTO vendor_bills (bill_number,vendor_id,bill_date,due_date,subtotal,tax,total,balance_due,status,notes,created_by) VALUES (?,?,?,?,?,?,?,?,?,?,?)');
        $s->execute([$n,(int)$d['vendor_id'],$d['bill_date']?:date('Y-m-d'),$d['due_date']?:null,(float)$d['subtotal'],(float)($d['tax']??0),$total,$total,$d['status']??'approved',trim((string)($d['notes']??''))?:null,$_SESSION['user_id']??null]);
        return (int)$this->db->lastInsertId();
    }
    public function payBill(int $id,array $d): void
    {
        $amount=(float)($d['amount']??0); if($amount<=0)return;
        $this->db->beginTransaction();
        try{
            $s=$this->db->prepare('INSERT INTO vendor_bill_payments (vendor_bill_id,bank_account_id,amount,payment_date,payment_method,reference_number,notes,created_by) VALUES (?,?,?,?,?,?,?,?)');
            $s->execute([$id,$d['bank_account_id']?:null,$amount,$d['payment_date']?:date('Y-m-d'),trim((string)($d['payment_method']??''))?:null,trim((string)($d['reference_number']??''))?:null,trim((string)($d['notes']??''))?:null,$_SESSION['user_id']??null]);
            $this->db->prepare("UPDATE vendor_bills SET balance_due=GREATEST(0,balance_due-?), status=CASE WHEN balance_due-?<=0 THEN 'paid' ELSE 'partially_paid' END WHERE id=?")->execute([$amount,$amount,$id]);
            if(!empty($d['bank_account_id'])) $this->recordBankTransaction((int)$d['bank_account_id'],'withdrawal',$amount,$d['payment_date']?:date('Y-m-d'),'Vendor bill payment',trim((string)($d['reference_number']??''))?:null);
            $this->db->commit();
            $this->postBillPayment($id,$amount,$d['payment_date']?:date('Y-m-d'));
        }catch(Throwable $e){$this->db->rollBack();throw $e;}
    }

    public function bankAccounts(): array { return $this->db->query('SELECT * FROM bank_accounts ORDER BY active DESC, account_name')->fetchAll(); }
    public function createBankAccount(array $d): int
    {
        $opening=(float)($d['opening_balance']??0);$s=$this->db->prepare('INSERT INTO bank_accounts (account_name,account_type,institution,last_four,opening_balance,current_balance,active) VALUES (?,?,?,?,?,?,?)');
        $s->execute([trim($d['account_name']),$d['account_type']??'bank',trim((string)($d['institution']??''))?:null,trim((string)($d['last_four']??''))?:null,$opening,$opening,1]);return(int)$this->db->lastInsertId();
    }
    public function transactions(): array { return $this->db->query('SELECT bank_transactions.*,bank_accounts.account_name FROM bank_transactions JOIN bank_accounts ON bank_accounts.id=bank_transactions.bank_account_id ORDER BY transaction_date DESC,id DESC LIMIT 300')->fetchAll(); }
    public function recordBankTransaction(int $accountId,string $type,float $amount,string $date,string $description='',?string $reference=null): int
    {
        $s=$this->db->prepare('INSERT INTO bank_transactions (bank_account_id,transaction_date,transaction_type,amount,reference_number,description,created_by) VALUES (?,?,?,?,?,?,?)');$s->execute([$accountId,$date,$type,$amount,$reference,$description?:null,$_SESSION['user_id']??null]);
        $sign=in_array($type,['deposit','transfer_in'],true)?1:-1;$this->db->prepare('UPDATE bank_accounts SET current_balance=current_balance+? WHERE id=?')->execute([$amount*$sign,$accountId]);return(int)$this->db->lastInsertId();
    }

    public function chartAccounts(): array { return $this->db->query('SELECT * FROM chart_accounts WHERE active=1 ORDER BY code')->fetchAll(); }
    public function journals(): array
    {
        return $this->db->query('SELECT journal_entries.*,COALESCE(SUM(journal_lines.debit),0) total_debit,COALESCE(SUM(journal_lines.credit),0) total_credit FROM journal_entries LEFT JOIN journal_lines ON journal_lines.journal_entry_id=journal_entries.id GROUP BY journal_entries.id ORDER BY entry_date DESC,journal_entries.id DESC LIMIT 300')->fetchAll();
    }
    public function createJournal(array $d): int
    {
        $debits=$d['debit']??[];$credits=$d['credit']??[];$accounts=$d['account_id']??[];$descs=$d['line_description']??[];
        $td=0;$tc=0;foreach($accounts as $i=>$a){$td+=(float)($debits[$i]??0);$tc+=(float)($credits[$i]??0);}if(abs($td-$tc)>0.009)throw new RuntimeException('Journal entry must balance.');
        $this->db->beginTransaction();try{$n=trim((string)($d['entry_number']??''))?:next_reference('JE-','journal_entries','entry_number');$s=$this->db->prepare('INSERT INTO journal_entries (entry_number,entry_date,source_type,memo,status,created_by,posted_at) VALUES (?,?,?,?,\'posted\',?,NOW())');$s->execute([$n,$d['entry_date']?:date('Y-m-d'),'manual',trim((string)($d['memo']??''))?:null,$_SESSION['user_id']??null]);$id=(int)$this->db->lastInsertId();$l=$this->db->prepare('INSERT INTO journal_lines (journal_entry_id,account_id,description,debit,credit) VALUES (?,?,?,?,?)');foreach($accounts as $i=>$a){if(!(int)$a)continue;$l->execute([$id,(int)$a,trim((string)($descs[$i]??''))?:null,(float)($debits[$i]??0),(float)($credits[$i]??0)]);} $this->db->commit();return$id;}catch(Throwable$e){$this->db->rollBack();throw$e;}
    }

    public function profitLoss(?string $from=null,?string $to=null): array
    {
        $from=$from?:date('Y-m-01');$to=$to?:date('Y-m-t');$s=$this->db->prepare("SELECT chart_accounts.code,chart_accounts.name,chart_accounts.account_type,SUM(journal_lines.credit-journal_lines.debit) amount FROM journal_lines JOIN journal_entries ON journal_entries.id=journal_lines.journal_entry_id JOIN chart_accounts ON chart_accounts.id=journal_lines.account_id WHERE journal_entries.status='posted' AND journal_entries.entry_date BETWEEN ? AND ? AND chart_accounts.account_type IN ('revenue','expense') GROUP BY chart_accounts.id ORDER BY chart_accounts.code");$s->execute([$from,$to]);return$s->fetchAll();
    }

    public function loadProfitability(): array
    {
        return $this->db->query("SELECT loads.id,loads.load_number,loads.rate revenue,loads.miles,COALESCE(SUM(expenses.amount+expenses.tax_amount),0) expenses,COALESCE(ds.net_amount,0) driver_pay,(loads.rate-COALESCE(SUM(expenses.amount+expenses.tax_amount),0)-COALESCE(ds.net_amount,0)) net_profit FROM loads LEFT JOIN expenses ON expenses.load_id=loads.id AND expenses.status IN ('approved','reimbursed','posted') LEFT JOIN driver_settlements ds ON ds.load_id=loads.id GROUP BY loads.id,ds.net_amount ORDER BY loads.id DESC LIMIT 200")->fetchAll();
    }

    public function postInvoice(int $invoiceId): void
    {
        $s=$this->db->prepare('SELECT * FROM invoices WHERE id=?');$s->execute([$invoiceId]);$inv=$s->fetch();if(!$inv||in_array($inv['status'],['draft','cancelled','void','written_off'],true))return;
        if($this->sourcePosted('invoice',$invoiceId))return;
        $ar=$this->accountId('1100');$rev=$this->accountId('4000');if(!$ar||!$rev)return;
        $this->automaticJournal('invoice',$invoiceId,$inv['invoice_date']?:date('Y-m-d'),'Invoice '.$inv['invoice_number'],[[$ar,'Accounts receivable',(float)$inv['total'],0],[$rev,'Freight revenue',0,(float)$inv['total']]]);
    }
    public function postPayment(int $invoiceId,float $amount,string $date): void
    {
        $cash=$this->accountId('1000');$ar=$this->accountId('1100');if(!$cash||!$ar||$amount<=0)return;$this->automaticJournal('invoice_payment',$invoiceId,$date,'Customer payment',[[$cash,'Cash received',$amount,0],[$ar,'Reduce receivable',0,$amount]],true);
    }
    public function postExpense(int $expenseId): void
    {
        $s=$this->db->prepare('SELECT expenses.*,expense_categories.account_code FROM expenses LEFT JOIN expense_categories ON expense_categories.id=expenses.category_id WHERE expenses.id=?');$s->execute([$expenseId]);$e=$s->fetch();if(!$e||$this->sourcePosted('expense',$expenseId))return;$exp=$this->accountId($e['account_code']?:'5990')?:$this->accountId('5990');$cash=$this->accountId('1000');$amount=(float)$e['amount']+(float)$e['tax_amount'];if(!$exp||!$cash||$amount<=0)return;$this->automaticJournal('expense',$expenseId,$e['expense_date'],'Expense '.$e['expense_number'],[[$exp,'Expense',$amount,0],[$cash,'Cash / payable',0,$amount]]);
    }
    private function automaticJournal(string$type,int$sourceId,string$date,string$memo,array$lines,bool$allowMultiple=false):void{if(!$allowMultiple&&$this->sourcePosted($type,$sourceId))return;$this->db->beginTransaction();try{$n=next_reference('JE-','journal_entries','entry_number');$s=$this->db->prepare("INSERT INTO journal_entries (entry_number,entry_date,source_type,source_id,memo,status,created_by,posted_at) VALUES (?,?,?,?,?,'posted',?,NOW())");$s->execute([$n,$date,$type,$sourceId,$memo,$_SESSION['user_id']??null]);$id=(int)$this->db->lastInsertId();$l=$this->db->prepare('INSERT INTO journal_lines (journal_entry_id,account_id,description,debit,credit) VALUES (?,?,?,?,?)');foreach($lines as$line)$l->execute([$id,$line[0],$line[1],$line[2],$line[3]]);$this->db->commit();}catch(Throwable$e){$this->db->rollBack();}}
    private function sourcePosted(string$type,int$id):bool{$s=$this->db->prepare("SELECT COUNT(*) FROM journal_entries WHERE source_type=? AND source_id=? AND status='posted'");$s->execute([$type,$id]);return(int)$s->fetchColumn()>0;}
    private function accountId(string$code):?int{$s=$this->db->prepare('SELECT id FROM chart_accounts WHERE code=? LIMIT 1');$s->execute([$code]);$id=$s->fetchColumn();return$id?(int)$id:null;}

    public function factoring(): array
    {
        return $this->db->query("SELECT factoring_records.*,invoices.invoice_number,invoices.total,brokers.company_name broker_name,vendors.company_name factoring_company FROM factoring_records JOIN invoices ON invoices.id=factoring_records.invoice_id LEFT JOIN brokers ON brokers.id=invoices.broker_id LEFT JOIN vendors ON vendors.id=factoring_records.vendor_id ORDER BY factoring_records.id DESC")->fetchAll();
    }
    public function saveFactoring(array $d): void
    {
        $s=$this->db->prepare("INSERT INTO factoring_records (invoice_id,vendor_id,status,submitted_at,advance_percent,fee_amount,reserve_amount,advance_received,reserve_released,chargeback_amount,notes) VALUES (?,?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE vendor_id=VALUES(vendor_id),status=VALUES(status),submitted_at=VALUES(submitted_at),advance_percent=VALUES(advance_percent),fee_amount=VALUES(fee_amount),reserve_amount=VALUES(reserve_amount),advance_received=VALUES(advance_received),reserve_released=VALUES(reserve_released),chargeback_amount=VALUES(chargeback_amount),notes=VALUES(notes)");
        $s->execute([(int)$d['invoice_id'],$d['vendor_id']?:null,$d['status']??'submitted',$d['submitted_at']?:date('Y-m-d H:i:s'),(float)($d['advance_percent']??0),(float)($d['fee_amount']??0),(float)($d['reserve_amount']??0),(float)($d['advance_received']??0),(float)($d['reserve_released']??0),(float)($d['chargeback_amount']??0),trim((string)($d['notes']??''))?:null]);
    }
    public function openInvoices(): array { return $this->db->query("SELECT invoices.id,invoices.invoice_number,invoices.total,brokers.company_name broker_name FROM invoices LEFT JOIN brokers ON brokers.id=invoices.broker_id WHERE invoices.status NOT IN ('paid','void','cancelled','written_off') ORDER BY invoices.id DESC")->fetchAll(); }

    public function trialBalance(?string $to=null): array
    {
        $to=$to?:date('Y-m-d');$s=$this->db->prepare("SELECT chart_accounts.code,chart_accounts.name,chart_accounts.account_type,COALESCE(SUM(journal_lines.debit),0) debit,COALESCE(SUM(journal_lines.credit),0) credit,COALESCE(SUM(journal_lines.debit-journal_lines.credit),0) balance FROM chart_accounts LEFT JOIN journal_lines ON journal_lines.account_id=chart_accounts.id LEFT JOIN journal_entries ON journal_entries.id=journal_lines.journal_entry_id AND journal_entries.status='posted' AND journal_entries.entry_date<=? WHERE chart_accounts.active=1 GROUP BY chart_accounts.id ORDER BY chart_accounts.code");$s->execute([$to]);return$s->fetchAll();
    }
    public function receivableAging(): array
    {
        return $this->db->query("SELECT invoices.invoice_number,brokers.company_name broker_name,invoices.due_date,invoices.total,GREATEST(0,invoices.total-COALESCE((SELECT SUM(amount) FROM payments WHERE payments.invoice_id=invoices.id),0)) balance,CASE WHEN invoices.due_date IS NULL OR invoices.due_date>=CURDATE() THEN 'Current' WHEN DATEDIFF(CURDATE(),invoices.due_date)<=15 THEN '1-15 Days' WHEN DATEDIFF(CURDATE(),invoices.due_date)<=30 THEN '16-30 Days' WHEN DATEDIFF(CURDATE(),invoices.due_date)<=60 THEN '31-60 Days' ELSE '60+ Days' END aging_bucket FROM invoices LEFT JOIN brokers ON brokers.id=invoices.broker_id WHERE invoices.status NOT IN ('paid','void','cancelled','written_off') HAVING balance>0 ORDER BY invoices.due_date")->fetchAll();
    }
    public function reconcileTransaction(int $id): void { $this->db->prepare('UPDATE bank_transactions SET reconciled_at=NOW() WHERE id=?')->execute([$id]); }

    public function postVendorBill(int $billId): void
    {
        $s=$this->db->prepare('SELECT * FROM vendor_bills WHERE id=?');$s->execute([$billId]);$b=$s->fetch();if(!$b||$this->sourcePosted('vendor_bill',$billId))return;$expense=$this->accountId('5990');$ap=$this->accountId('2000');if(!$expense||!$ap)return;$this->automaticJournal('vendor_bill',$billId,$b['bill_date'],'Vendor bill '.$b['bill_number'],[[$expense,'Vendor expense',(float)$b['total'],0],[$ap,'Accounts payable',0,(float)$b['total']]]);
    }
    public function postBillPayment(int $billId,float $amount,string $date): void
    {
        $ap=$this->accountId('2000');$cash=$this->accountId('1000');if(!$ap||!$cash||$amount<=0)return;$this->automaticJournal('vendor_bill_payment',$billId,$date,'Vendor payment',[[$ap,'Reduce payable',$amount,0],[$cash,'Cash paid',0,$amount]],true);
    }
    public function postSettlement(int $settlementId): void
    {
        $s=$this->db->prepare('SELECT * FROM driver_settlements WHERE id=?');$s->execute([$settlementId]);$r=$s->fetch();if(!$r||$this->sourcePosted('driver_settlement',$settlementId))return;$driverPay=$this->accountId('5000');$ap=$this->accountId('2000');if(!$driverPay||!$ap)return;$this->automaticJournal('driver_settlement',$settlementId,date('Y-m-d'),'Driver settlement '.$r['settlement_number'],[[$driverPay,'Driver pay',(float)$r['net_amount'],0],[$ap,'Settlement payable',0,(float)$r['net_amount']]]);
    }
    public function postSettlementPayment(int $settlementId,float $amount,string $date): void
    {
        $ap=$this->accountId('2000');$cash=$this->accountId('1000');if(!$ap||!$cash||$amount<=0)return;$this->automaticJournal('settlement_payment',$settlementId,$date,'Settlement payment',[[$ap,'Reduce settlement payable',$amount,0],[$cash,'Cash paid',0,$amount]],true);
    }

    public function payableAging(): array
    {
        return $this->db->query("SELECT vendor_bills.bill_number,vendors.company_name vendor_name,vendor_bills.due_date,vendor_bills.total,vendor_bills.balance_due balance,CASE WHEN vendor_bills.due_date IS NULL OR vendor_bills.due_date>=CURDATE() THEN 'Current' WHEN DATEDIFF(CURDATE(),vendor_bills.due_date)<=15 THEN '1-15 Days' WHEN DATEDIFF(CURDATE(),vendor_bills.due_date)<=30 THEN '16-30 Days' WHEN DATEDIFF(CURDATE(),vendor_bills.due_date)<=60 THEN '31-60 Days' ELSE '60+ Days' END aging_bucket FROM vendor_bills JOIN vendors ON vendors.id=vendor_bills.vendor_id WHERE vendor_bills.status NOT IN ('paid','void') AND vendor_bills.balance_due>0 ORDER BY vendor_bills.due_date")->fetchAll();
    }

    public function balanceSheet(?string $to=null): array
    {
        $to=$to?:date('Y-m-d');
        $s=$this->db->prepare("SELECT chart_accounts.code,chart_accounts.name,chart_accounts.account_type,CASE WHEN chart_accounts.account_type='asset' THEN COALESCE(SUM(journal_lines.debit-journal_lines.credit),0) ELSE COALESCE(SUM(journal_lines.credit-journal_lines.debit),0) END balance FROM chart_accounts LEFT JOIN journal_lines ON journal_lines.account_id=chart_accounts.id LEFT JOIN journal_entries ON journal_entries.id=journal_lines.journal_entry_id AND journal_entries.status='posted' AND journal_entries.entry_date<=? WHERE chart_accounts.active=1 AND chart_accounts.account_type IN ('asset','liability','equity') GROUP BY chart_accounts.id ORDER BY chart_accounts.code");
        $s->execute([$to]);return $s->fetchAll();
    }

    public function cashFlow(string $from,string $to): array
    {
        $s=$this->db->prepare("SELECT transaction_type,COALESCE(SUM(amount),0) amount FROM bank_transactions WHERE transaction_date BETWEEN ? AND ? GROUP BY transaction_type ORDER BY transaction_type");
        $s->execute([$from,$to]);return $s->fetchAll();
    }

    public function expenseSummary(string $from,string $to): array
    {
        $s=$this->db->prepare("SELECT COALESCE(expense_categories.name,'Uncategorized') category,COALESCE(SUM(expenses.amount+expenses.tax_amount),0) total FROM expenses LEFT JOIN expense_categories ON expense_categories.id=expenses.category_id WHERE expenses.status IN ('approved','reimbursed','posted') AND expenses.expense_date BETWEEN ? AND ? GROUP BY expense_categories.id ORDER BY total DESC");
        $s->execute([$from,$to]);return $s->fetchAll();
    }

}
