<?php
namespace App\Controllers;

class DashboardController extends BaseController
{
    public function index()
    {
        if (!$this->can('view_dashboard')) return redirect()->to('/')->with('error','Access denied');

        $db  = db_connect();
        $cid = $this->companyId;

        // KPIs
        $completedIds = array_column(
            $db->table('job_statuses')->where('company_id',$cid)->where('is_complete',1)->select('id')->get()->getResultArray(),
            'id'
        ) ?: [0];

        $kpis = [
            'active_jobs'      => $db->table('jobs')->where('company_id',$cid)->where('deleted_at IS NULL',null,false)->whereNotIn('status_id',$completedIds)->countAllResults(),
            'overdue_invoices' => $db->table('invoices')->where('company_id',$cid)->where('deleted_at IS NULL',null,false)->where('status','overdue')->countAllResults(),
            'revenue_month'    => (float)($db->table('payments')->where('company_id',$cid)->where('deleted_at IS NULL',null,false)->where('paid_at >=',date('Y-m-01'))->selectSum('amount')->get()->getRow()->amount ?? 0),
            'customers_total'  => $db->table('customers')->where('company_id',$cid)->where('deleted_at IS NULL',null,false)->countAllResults(),
            'quotes_pending'   => $db->table('quotes')->where('company_id',$cid)->where('deleted_at IS NULL',null,false)->where('status','sent')->countAllResults(),
        ];

        // Recent activity
        $activity = model('ActivityLogModel')->getFeedForUser($cid, $this->userId, $this->permissions, 15);

        // Overdue jobs
        $overdueJobs = $db->table('jobs j')
            ->select('j.*, c.first_name, c.last_name, c.company_name, js.name as status_name, js.color as status_color')
            ->join('customers c','c.id = j.customer_id')
            ->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',0)
            ->where('js.is_cancelled',0)
            ->where('j.scheduled_date <',date('Y-m-d'))
            ->orderBy('j.scheduled_date','ASC')
            ->get()->getResultArray();

        // Upcoming visits (next 7 days)
        $upcomingVisits = $db->table('visits v')
            ->select('v.*, j.number as job_number, j.title as job_title, c.first_name, c.last_name, c.company_name')
            ->join('jobs j','j.id = v.job_id')
            ->join('customers c','c.id = j.customer_id')
            ->where('v.company_id',$cid)
            ->where('v.scheduled_start >=',date('Y-m-d H:i:s'))
            ->where('v.scheduled_start <=',date('Y-m-d H:i:s', strtotime('+7 days')))
            ->where('v.status','scheduled')
            ->orderBy('v.scheduled_start','ASC')
            ->limit(5)
            ->get()->getResultArray();

        // Revenue chart (last 6 months)
        $revenueChart = $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 6 MONTH)
            GROUP BY month, sort_key ORDER BY sort_key ASC
        ",[$cid])->getResultArray();

        return $this->view('dashboard/index', compact('kpis','activity','overdueJobs','upcomingVisits','revenueChart'));
    }
}
