<?php
namespace App\Http\Controllers\Api\V1;

use App\Http\Controllers\Controller;
use App\Models\Attendance;
use App\Models\Department;
use App\Models\Employee;
use App\Models\LeaveApplication;
use App\Models\PayrollLine;
use App\Models\PayrollRun;
use App\Models\StaffAdvance;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

/** Live data for the Command Centre — HR & Payroll module. */
class HrController extends Controller
{
    use Concerns\DashboardHelpers;

    public function show(Request $r)
    {
        $this->need('hr.view');
        $qry = $r->get('qry', 'all');
        [$cf, $ct, $pf, $pt, $grain] = $this->windows((string) $r->get('period', 'This Month'));
        $empIds = $this->empIds($qry);

        $empQ = fn() => Employee::where('status', 'active')->when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry));
        $head = (clone $empQ())->count();
        $joiners = (clone $empQ())->whereBetween('date_employed', [$cf, $ct])->count();
        $joinersP = (clone $empQ())->whereBetween('date_employed', [$pf, $pt])->count();
        $exits = Employee::when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))
            ->whereNotNull('date_exited')->whereBetween('date_exited', [$cf, $ct])->count();
        $exitsP = Employee::when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))
            ->whereNotNull('date_exited')->whereBetween('date_exited', [$pf, $pt])->count();

        $runIds = fn($f, $t) => PayrollRun::whereBetween('payroll_month', [$f, $t])->pluck('id');
        $pay = function ($f, $t) use ($qry, $runIds) {
            return PayrollLine::whereIn('payroll_run_id', $runIds($f, $t))
                ->when($qry !== 'all', fn($q) => $q->whereHas('employee', fn($w) => $w->where('quarry_id', $qry)));
        };
        $gross = (float) (clone $pay($cf, $ct))->sum('gross_pay');
        $grossP = (float) (clone $pay($pf, $pt))->sum('gross_pay');
        $net = (float) (clone $pay($cf, $ct))->sum('net_pay');
        $netP = (float) (clone $pay($pf, $pt))->sum('net_pay');
        $paye = (float) (clone $pay($cf, $ct))->sum('paye');
        $payeP = (float) (clone $pay($pf, $pt))->sum('paye');
        $ssnit = (float) (clone $pay($cf, $ct))->selectRaw('sum(ssnit_employee+ssnit_employer) v')->value('v');
        $ssnitP = (float) (clone $pay($pf, $pt))->selectRaw('sum(ssnit_employee+ssnit_employer) v')->value('v');
        $ercost = (float) (clone $pay($cf, $ct))->selectRaw('sum(gross_pay+ssnit_employer) v')->value('v');
        $ercostP = (float) (clone $pay($pf, $pt))->selectRaw('sum(gross_pay+ssnit_employer) v')->value('v');

        $advOut = (float) StaffAdvance::when($qry !== 'all', fn($q) => $q->whereHas('employee', fn($w) => $w->where('quarry_id', $qry)))
            ->where('balance', '>', 0)->sum('balance');
        $leavePend = LeaveApplication::when($qry !== 'all', fn($q) => $q->whereHas('employee', fn($w) => $w->where('quarry_id', $qry)))
            ->where('status', 'pending')->count();
        $leaveDays = (float) LeaveApplication::when($qry !== 'all', fn($q) => $q->whereHas('employee', fn($w) => $w->where('quarry_id', $qry)))
            ->whereBetween('from_date', [$cf, $ct])->sum('days_requested');
        $leaveDaysP = (float) LeaveApplication::when($qry !== 'all', fn($q) => $q->whereHas('employee', fn($w) => $w->where('quarry_id', $qry)))
            ->whereBetween('from_date', [$pf, $pt])->sum('days_requested');
        $attRate = $this->attRate($qry, $cf, $ct);
        $attRateP = $this->attRate($qry, $pf, $pt);

        $cards = [
            $this->card('Headcount', $head, '#', $head, [], ['st' => 'blue']),
            $this->card('New Joiners', $joiners, '#', $joinersP),
            $this->card('Exits', $exits, '#', $exitsP, [], ['good' => 'down']),
            $this->card('Gross Payroll (' . $r->get('period', 'This Month') . ')', round($gross), 'GHS', round($grossP)),
            $this->card('Net Pay', round($net), 'GHS', round($netP)),
            $this->card('PAYE Due', round($paye), 'GHS', round($payeP), [], ['st' => 'blue', 'info' => 'Ghana income tax']),
            $this->card('SSNIT (ee + er)', round($ssnit), 'GHS', round($ssnitP), [], ['st' => 'blue']),
            $this->card('Employer Cost', round($ercost), 'GHS', round($ercostP), [], ['good' => 'down']),
            $this->card('Advances Outstanding', round($advOut), 'GHS', round($advOut), [], ['good' => 'down']),
            $this->card('Leave Requests Pending', $leavePend, '#', $leavePend, [], ['st' => $leavePend > 0 ? 'amber' : 'green']),
            $this->card('Leave Days Booked', round($leaveDays, 1), 'num', round($leaveDaysP, 1)),
            $this->card('Attendance Rate', $attRate, '%', $attRateP, [], ['tgt' => 95]),
        ];

        return response()->json([
            'sub' => 'Headcount · payroll · PAYE/SSNIT · leave · attendance · ' . $r->get('period', 'This Month'),
            'asAt' => Carbon::today()->format('d M Y'),
            'cards' => $cards,
            'sections' => [
                $this->secAtt($qry),
                $this->secOt($qry, $cf, $ct),
                $this->secDept($qry),
                $this->secComp($qry),
                $this->secEmp($qry),
                $this->secRuns($qry),
                $this->secLines($qry),
                $this->secLeave($qry),
                $this->secAdv($qry),
            ],
        ]);
    }

    private function empIds($qry): array
    {
        return Employee::when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))->pluck('id')->all();
    }

    private function attRate($qry, $f, $t): float
    {
        $rows = Attendance::when($qry !== 'all', fn($q) => $q->whereHas('employee', fn($w) => $w->where('quarry_id', $qry)))
            ->whereBetween('attendance_date', [$f, $t])
            ->select('status', DB::raw('count(*) n'))->groupBy('status')->pluck('n', 'status');
        $tot = $rows->sum();
        if (!$tot) return 0;
        return round((($rows['present'] ?? 0) + ($rows['late'] ?? 0)) / $tot * 100, 1);
    }

    private function secAtt($qry): array
    {
        $from = Carbon::today()->subDays(29)->format('Y-m-d');
        $rows = Attendance::when($qry !== 'all', fn($q) => $q->whereHas('employee', fn($w) => $w->where('quarry_id', $qry)))
            ->whereDate('attendance_date', '>=', $from)
            ->selectRaw('date(attendance_date) dd, status, count(*) n')->groupBy('dd', 'status')->get();
        $byDay = [];
        foreach ($rows as $r) $byDay[$r->dd][$r->status] = (int) $r->n;
        $present = [];
        $late = [];
        $absent = [];
        for ($i = 29; $i >= 0; $i--) {
            $d = Carbon::today()->subDays($i)->format('Y-m-d');
            $present[] = $byDay[$d]['present'] ?? 0;
            $late[] = $byDay[$d]['late'] ?? 0;
            $absent[] = ($byDay[$d]['absent'] ?? 0) + ($byDay[$d]['on-leave'] ?? 0);
        }
        return ['k' => 'chart', 'id' => 'hr_att', 'ti' => 'Daily Attendance — headcount (30 days)', 'cls' => 'wide', 'spec' => [
            'type' => 'line', 'fmt' => 'num', 'zoom' => true, 'height' => 280,
            'labels' => $this->dailyLabels(30),
            'series' => [
                ['name' => 'Present', 'data' => $present, 'color' => '#22c55e', 'area' => true],
                ['name' => 'Late', 'data' => $late, 'color' => '#f8c200'],
                ['name' => 'Absent/Leave', 'data' => $absent, 'color' => '#ef4444'],
            ],
        ]];
    }

    private function secOt($qry, $cf, $ct): array
    {
        $rows = Attendance::when($qry !== 'all', fn($q) => $q->whereHas('employee', fn($w) => $w->where('quarry_id', $qry)))
            ->whereBetween('attendance_date', [$cf, $ct])->where('overtime_hours', '>', 0)
            ->select('employee_id', DB::raw('sum(overtime_hours) t'))->groupBy('employee_id')->orderByDesc('t')->take(8)->get();
        $names = Employee::whereIn('id', $rows->pluck('employee_id'))->get()->mapWithKeys(fn($e) => [$e->id => $e->employee_no]);
        return ['k' => 'chart', 'id' => 'hr_ot', 'ti' => 'Top Overtime — hours (period)', 'spec' => [
            'type' => 'hbar', 'fmt' => 'h',
            'labels' => $rows->map(fn($r) => $names[$r->employee_id] ?? ('#' . $r->employee_id))->all(),
            'series' => [['name' => 'Hours', 'data' => $rows->map(fn($r) => round((float) $r->t, 1))->all(), 'color' => '#a855f7']],
        ]];
    }

    private function secDept($qry): array
    {
        $rows = Employee::where('status', 'active')->when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))
            ->select('department_id', DB::raw('count(*) n'))->groupBy('department_id')->orderByDesc('n')->get();
        $names = Department::whereIn('id', $rows->pluck('department_id'))->pluck('name', 'id');
        return ['k' => 'chart', 'id' => 'hr_dept', 'ti' => 'Headcount by Department — staff (live)', 'spec' => [
            'type' => 'hbar', 'fmt' => 'num',
            'labels' => $rows->map(fn($r) => mb_strimwidth($names[$r->department_id] ?? 'Unassigned', 0, 26, '…'))->all(),
            'series' => [['name' => 'Staff', 'data' => $rows->map(fn($r) => (int) $r->n)->all(), 'color' => '#3b82f6']],
        ]];
    }

    private function secComp($qry): array
    {
        $run = PayrollRun::orderByDesc('payroll_month')->first();
        $items = [];
        if ($run) {
            $q = PayrollLine::where('payroll_run_id', $run->id)
                ->when($qry !== 'all', fn($w) => $w->whereHas('employee', fn($x) => $x->where('quarry_id', $qry)));
            $net = (float) (clone $q)->sum('net_pay');
            $paye = (float) (clone $q)->sum('paye');
            $ssnit = (float) (clone $q)->selectRaw('sum(ssnit_employee+ssnit_employer) v')->value('v');
            $other = (float) (clone $q)->selectRaw('sum(advances_deduction+other_deductions) v')->value('v');
            $pal = $this->palette();
            $raw = [['Net pay', $net], ['PAYE', $paye], ['SSNIT', $ssnit], ['Other deductions', $other]];
            foreach (array_values($raw) as $i => [$l, $v]) {
                if ($v > 0) $items[] = ['label' => $l, 'value' => round($v), 'color' => $pal[$i % count($pal)]];
            }
        }
        return ['k' => 'chart', 'id' => 'hr_comp', 'ti' => 'Latest Payroll Cost Split — GHS (' . ($run ? substr($run->payroll_month, 0, 7) : '—') . ')', 'spec' => [
            'type' => 'doughnut', 'fmt' => 'money', 'center' => 'Payroll', 'items' => $items,
        ]];
    }

    private function secEmp($qry): array
    {
        $rows = Employee::with(['department', 'designation', 'quarry'])->where('status', 'active')
            ->when($qry !== 'all', fn($q) => $q->where('quarry_id', $qry))->orderBy('employee_no')->get()
            ->map(fn($e) => ['no' => $e->employee_no, 'name' => $e->full_name, 'gen' => ucfirst((string) $e->gender),
                'dept' => $e->department->name ?? '', 'des' => $e->designation->name ?? '',
                'type' => ucfirst((string) $e->employment_type), 'site' => $e->quarry->name ?? 'HQ',
                'since' => $e->date_employed ? substr((string) $e->date_employed, 0, 10) : '',
                'basic' => round((float) $e->basic_salary), 'allow' => round($e->allowancesTotal()),
                'st' => 'green', 'stL' => 'Active'])->all();
        return ['k' => 'table', 'id' => 'hr_emp', 'ti' => 'Employee Register — active staff', 'cls' => 'wide', 'cfg' => [
            'id' => 'hr_emp', 'page' => 14, 'search' => true,
            'cols' => [
                ['k' => 'no', 'l' => 'Staff No'], ['k' => 'name', 'l' => 'Name', 'wide' => 1], ['k' => 'gen', 'l' => 'M/F'],
                ['k' => 'dept', 'l' => 'Department'], ['k' => 'des', 'l' => 'Designation'], ['k' => 'type', 'l' => 'Type'],
                ['k' => 'site', 'l' => 'Station'], ['k' => 'since', 'l' => 'Employed'],
                ['k' => 'basic', 'l' => 'Basic', 'y' => 'num'], ['k' => 'allow', 'l' => 'Allowances', 'y' => 'num'],
                ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => ['basic', 'allow'],
        ]];
    }

    private function secRuns($qry): array
    {
        $runs = PayrollRun::orderByDesc('payroll_month')->take(12)->get();
        $rows = $runs->map(function ($r) use ($qry) {
            $q = PayrollLine::where('payroll_run_id', $r->id)
                ->when($qry !== 'all', fn($w) => $w->whereHas('employee', fn($x) => $x->where('quarry_id', $qry)));
            $agg = (clone $q)->selectRaw('count(*) n, sum(gross_pay) g, sum(paye) p, sum(ssnit_employee+ssnit_employer) s, sum(total_deductions) d, sum(net_pay) np')->first();
            return ['no' => $r->payroll_no, 'month' => substr((string) $r->payroll_month, 0, 7),
                'staff' => (int) $agg->n, 'gross' => round((float) $agg->g), 'paye' => round((float) $agg->p),
                'ssnit' => round((float) $agg->s), 'ded' => round((float) $agg->d), 'net' => round((float) $agg->np),
                'paid' => $r->paid_date ? substr((string) $r->paid_date, 0, 10) : '',
                'st' => $r->status === 'paid' ? 'green' : 'amber', 'stL' => ucfirst((string) $r->status)];
        })->all();
        return ['k' => 'table', 'id' => 'hr_runs', 'ti' => 'Payroll Runs — latest 12', 'cls' => 'wide', 'cfg' => [
            'id' => 'hr_runs', 'page' => 12, 'search' => true,
            'cols' => [
                ['k' => 'no', 'l' => 'Run'], ['k' => 'month', 'l' => 'Month'], ['k' => 'staff', 'l' => 'Staff', 'y' => 'num'],
                ['k' => 'gross', 'l' => 'Gross', 'y' => 'num'], ['k' => 'paye', 'l' => 'PAYE', 'y' => 'num'],
                ['k' => 'ssnit', 'l' => 'SSNIT', 'y' => 'num'], ['k' => 'ded', 'l' => 'Deductions', 'y' => 'num'],
                ['k' => 'net', 'l' => 'Net pay', 'y' => 'num'], ['k' => 'paid', 'l' => 'Paid'],
                ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => ['gross', 'paye', 'ssnit', 'ded', 'net'],
        ]];
    }

    private function secLines($qry): array
    {
        $run = PayrollRun::orderByDesc('payroll_month')->first();
        $rows = $run ? PayrollLine::with('employee')->where('payroll_run_id', $run->id)
            ->when($qry !== 'all', fn($q) => $q->whereHas('employee', fn($w) => $w->where('quarry_id', $qry)))
            ->get()->map(fn($l) => ['emp' => $l->employee->employee_no ?? '', 'name' => $l->employee->full_name ?? '',
                'basic' => round((float) $l->basic_salary), 'allow' => round((float) $l->allowances_total),
                'ot' => round((float) $l->overtime_pay), 'gross' => round((float) $l->gross_pay),
                'ssnit' => round((float) $l->ssnit_employee), 'paye' => round((float) $l->paye),
                'adv' => round((float) $l->advances_deduction), 'oth' => round((float) $l->other_deductions),
                'net' => round((float) $l->net_pay)])->all() : [];
        return ['k' => 'table', 'id' => 'hr_lines', 'ti' => 'Payslips — latest run' . ($run ? ' (' . $run->payroll_no . ')' : ''), 'cls' => 'wide', 'cfg' => [
            'id' => 'hr_lines', 'page' => 14, 'search' => true,
            'cols' => [
                ['k' => 'emp', 'l' => 'Staff No'], ['k' => 'name', 'l' => 'Name', 'wide' => 1],
                ['k' => 'basic', 'l' => 'Basic', 'y' => 'num'], ['k' => 'allow', 'l' => 'Allow.', 'y' => 'num'],
                ['k' => 'ot', 'l' => 'OT', 'y' => 'num'], ['k' => 'gross', 'l' => 'Gross', 'y' => 'num'],
                ['k' => 'ssnit', 'l' => 'SSNIT', 'y' => 'num'], ['k' => 'paye', 'l' => 'PAYE', 'y' => 'num'],
                ['k' => 'adv', 'l' => 'Adv', 'y' => 'num'], ['k' => 'oth', 'l' => 'Other', 'y' => 'num'],
                ['k' => 'net', 'l' => 'Net', 'y' => 'num'],
            ], 'rows' => $rows, 'totals' => ['basic', 'allow', 'ot', 'gross', 'ssnit', 'paye', 'adv', 'oth', 'net'],
        ]];
    }

    private function secLeave($qry): array
    {
        $rows = LeaveApplication::with(['employee', 'leaveType'])
            ->when($qry !== 'all', fn($q) => $q->whereHas('employee', fn($w) => $w->where('quarry_id', $qry)))
            ->orderByDesc('from_date')->take(30)->get()
            ->map(fn($l) => ['no' => $l->leave_no, 'emp' => $l->employee->full_name ?? '',
                'type' => $l->leaveType->name ?? '', 'from' => substr((string) $l->from_date, 0, 10),
                'to' => substr((string) $l->to_date, 0, 10), 'days' => round((float) $l->days_requested, 1),
                'cover' => $l->covering_officer ?? '',
                'st' => $l->status === 'approved' ? 'green' : ($l->status === 'rejected' ? 'red' : 'amber'),
                'stL' => ucfirst((string) $l->status)])->all();
        return ['k' => 'table', 'id' => 'hr_leave', 'ti' => 'Leave Applications — latest 30', 'cls' => 'wide', 'cfg' => [
            'id' => 'hr_leave', 'page' => 10, 'search' => true,
            'cols' => [
                ['k' => 'no', 'l' => 'Leave No'], ['k' => 'emp', 'l' => 'Employee', 'wide' => 1], ['k' => 'type', 'l' => 'Type'],
                ['k' => 'from', 'l' => 'From'], ['k' => 'to', 'l' => 'To'], ['k' => 'days', 'l' => 'Days', 'y' => 'num'],
                ['k' => 'cover', 'l' => 'Cover'], ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => [],
        ]];
    }

    private function secAdv($qry): array
    {
        $rows = StaffAdvance::with('employee')
            ->when($qry !== 'all', fn($q) => $q->whereHas('employee', fn($w) => $w->where('quarry_id', $qry)))
            ->orderByDesc('advance_date')->take(30)->get()
            ->map(fn($a) => ['no' => $a->advance_no, 'emp' => $a->employee->full_name ?? '',
                'date' => substr((string) $a->advance_date, 0, 10), 'amt' => round((float) $a->amount),
                'repaid' => round((float) $a->amount_repaid), 'bal' => round((float) $a->balance),
                'months' => (int) $a->repayment_months, 'ded' => round((float) $a->monthly_deduction),
                'st' => $a->status === 'repaid' ? 'green' : ($a->status === 'pending' ? 'amber' : 'blue'),
                'stL' => ucfirst((string) $a->status)])->all();
        return ['k' => 'table', 'id' => 'hr_adv', 'ti' => 'Staff Advances — latest 30', 'cls' => 'wide', 'cfg' => [
            'id' => 'hr_adv', 'page' => 10, 'search' => true,
            'cols' => [
                ['k' => 'no', 'l' => 'Advance'], ['k' => 'emp', 'l' => 'Employee', 'wide' => 1], ['k' => 'date', 'l' => 'Date'],
                ['k' => 'amt', 'l' => 'Amount', 'y' => 'num'], ['k' => 'repaid', 'l' => 'Repaid', 'y' => 'num'],
                ['k' => 'bal', 'l' => 'Balance', 'y' => 'num'], ['k' => 'months', 'l' => 'Months', 'y' => 'num'],
                ['k' => 'ded', 'l' => 'Monthly', 'y' => 'num'], ['k' => 'st', 'l' => 'Status', 'y' => 'badge'],
            ], 'rows' => $rows, 'totals' => ['amt', 'repaid', 'bal'],
        ]];
    }
}
