<?php
namespace App\Controllers;

class ReportsController extends BaseController
{
    public function index()
    {
        if (!$this->can('view_reports')) return redirect()->to('/')->with('error','Access denied');
        $db=$db=db_connect(); $cid=$this->companyId;
        $currency=$this->settings['regional']['currency']??'GBP';

        $revenue=$db->query("SELECT DATE_FORMAT(paid_at,'%b %Y') as month, DATE_FORMAT(paid_at,'%Y-%m') as sort_key, SUM(amount) as total FROM payments WHERE company_id=? AND deleted_at IS NULL AND paid_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH) GROUP BY month, sort_key ORDER BY sort_key ASC",[$cid])->getResultArray();

        $utilisation=$db->query("SELECT u.id, u.first_name, u.last_name, COALESCE(SUM(te.duration_mins),0) as total_mins, COALESCE(SUM(CASE WHEN te.billable=1 THEN te.duration_mins ELSE 0 END),0) as billable_mins FROM users u LEFT JOIN time_entries te ON te.user_id=u.id AND te.company_id=? AND te.deleted_at IS NULL AND te.date >= DATE_SUB(NOW(), INTERVAL 30 DAY) WHERE u.company_id=? AND u.status='active' AND u.deleted_at IS NULL GROUP BY u.id ORDER BY billable_mins DESC",[$cid,$cid])->getResultArray();

        $agedDebtors=$db->query("SELECT i.id, i.number, i.total, i.balance_due, i.due_date, c.first_name, c.last_name, c.company_name, DATEDIFF(NOW(),i.due_date) as days_overdue FROM invoices i JOIN customers c ON c.id=i.customer_id WHERE i.company_id=? AND i.deleted_at IS NULL AND i.status NOT IN ('paid','void','draft') AND i.balance_due>0 ORDER BY days_overdue DESC",[$cid])->getResultArray();

        $summary=[
            'total_invoiced'  => (float)($db->table('invoices')->where('company_id',$cid)->where('deleted_at IS NULL',null,false)->where('status !=','void')->selectSum('total')->get()->getRow()->total??0),
            'total_collected' => (float)($db->table('payments')->where('company_id',$cid)->where('deleted_at IS NULL',null,false)->selectSum('amount')->get()->getRow()->amount??0),
            'total_outstanding'=> (float)($db->table('invoices')->where('company_id',$cid)->where('deleted_at IS NULL',null,false)->whereIn('status',['sent','part_paid','overdue'])->selectSum('balance_due')->get()->getRow()->balance_due??0),
            'jobs_completed'  => $db->table('jobs j')->join('job_statuses js','js.id=j.status_id')->where('j.company_id',$cid)->where('j.deleted_at IS NULL',null,false)->where('js.is_complete',1)->countAllResults(),
        ];

        return $this->view('reports/index', compact('revenue','utilisation','agedDebtors','summary','currency'));
    }

    public function exportInvoices()
    {
        if (!$this->can('view_reports')) return redirect()->to('/')->with('error','Access denied');
        $rows=model('InvoiceModel')->where('company_id',$this->companyId)->where('deleted_at IS NULL',null,false)->orderBy('created_at','DESC')->findAll();
        $csv="Number,Title,Status,Subtotal,Tax,Total,Amount Paid,Balance Due,Due Date,Created\n";
        foreach ($rows as $i) $csv.=implode(',',[$i['number'],'"'.addslashes($i['title']??'').'"',$i['status'],$i['subtotal'],$i['tax_total'],$i['total'],$i['amount_paid'],$i['balance_due'],$i['due_date']??'',$i['created_at']])."\n";
        return $this->response->setHeader('Content-Type','text/csv')->setHeader('Content-Disposition','attachment; filename="invoices-'.date('Y-m-d').'.csv"')->setBody($csv);
    }

    public function exportTime()
    {
        if (!$this->can('view_reports')) return redirect()->to('/')->with('error','Access denied');
        $rows=db_connect()->table('time_entries te')->select('te.*, u.first_name, u.last_name, j.number as job_number, j.title as job_title')->join('users u','u.id=te.user_id')->join('jobs j','j.id=te.job_id','left')->where('te.company_id',$this->companyId)->where('te.deleted_at IS NULL',null,false)->orderBy('te.date','DESC')->get()->getResultArray();
        $csv="Date,Engineer,Job Number,Job Title,Duration (mins),Billable,Rate,Cost,Notes\n";
        foreach ($rows as $e) $csv.=implode(',',[$e['date'],$e['first_name'].' '.$e['last_name'],$e['job_number']??'','"'.addslashes($e['job_title']??'').'"',$e['duration_mins'],$e['billable']?'Yes':'No',$e['rate'],$e['cost']??'','"'.addslashes($e['notes']??'').'"'])."\n";
        return $this->response->setHeader('Content-Type','text/csv')->setHeader('Content-Disposition','attachment; filename="time-'.date('Y-m-d').'.csv"')->setBody($csv);
    }
}
