<?php
declare(strict_types=1);

namespace App\Modules\HR\Controllers;

use App\Core\Controller;
use App\Core\Request;
use App\Core\Response;
use App\Core\App;
use App\Modules\HR\Models\HrDepartment;
use App\Modules\HR\Services\WorkforceReportService;

class HrReportController extends Controller
{
    public function index(Request $request): Response
    {
        return $this->view('HR.Views.reports.index', []);
    }

    public function headcount(Request $request): Response
    {
        $db = App::getInstance()->db();

        $byDepartment = $db->select(
            "SELECT d.name_ar as department_name, COALESCE(ida.name_ar, d.name_ar) as idara_name,
                    COUNT(hp.id) as total,
                    SUM(CASE WHEN hp.gender = 'male' THEN 1 ELSE 0 END) as male_count,
                    SUM(CASE WHEN hp.gender = 'female' THEN 1 ELSE 0 END) as female_count,
                    SUM(CASE WHEN hp.employment_type = 'full_time' THEN 1 ELSE 0 END) as full_time,
                    SUM(CASE WHEN hp.employment_type = 'part_time' THEN 1 ELSE 0 END) as part_time
             FROM hr_employee_profiles hp
             LEFT JOIN hr_departments d ON d.id = hp.department_id
             LEFT JOIN hr_departments ida ON ida.id = d.parent_id AND ida.department_type = 'idara'
             WHERE hp.employment_status = 'active' AND hp.is_archived = 0
             GROUP BY hp.department_id, d.name_ar, ida.name_ar
             ORDER BY idara_name ASC, total DESC"
        );

        $totals = $db->selectOne(
            "SELECT COUNT(*) as total,
                    SUM(CASE WHEN gender = 'male' THEN 1 ELSE 0 END) as male_count,
                    SUM(CASE WHEN gender = 'female' THEN 1 ELSE 0 END) as female_count,
                    SUM(CASE WHEN employment_type = 'full_time' THEN 1 ELSE 0 END) as full_time,
                    SUM(CASE WHEN employment_type = 'part_time' THEN 1 ELSE 0 END) as part_time
             FROM hr_employee_profiles
             WHERE employment_status = 'active' AND is_archived = 0"
        );

        return $this->view('HR.Views.reports.headcount', [
            'byDepartment' => $byDepartment,
            'totals'       => $totals,
        ]);
    }

    public function payrollSummary(Request $request): Response
    {
        $year = (int) $request->get('year', (int) date('Y'));

        $db = App::getInstance()->db();

        $monthlySummary = $db->select(
            "SELECT pp.month,
                    pp.status as period_status,
                    COUNT(pr.id) as employee_count,
                    SUM(pr.gross_earnings) as total_gross,
                    SUM(pr.total_deductions) as total_deductions,
                    SUM(pr.net_salary) as total_net,
                    SUM(pr.overtime_amount) as total_overtime,
                    SUM(pr.insurance_employee) as total_insurance_employee,
                    SUM(pr.insurance_employer) as total_insurance_employer,
                    SUM(pr.tax_amount) as total_tax
             FROM hr_payroll_periods pp
             LEFT JOIN hr_payroll_runs pr ON pr.period_id = pp.id AND pr.is_archived = 0
             WHERE pp.year = ? AND pp.is_archived = 0
             GROUP BY pp.month, pp.status
             ORDER BY pp.month ASC",
            [$year]
        );

        return $this->view('HR.Views.reports.payroll_summary', [
            'monthlySummary' => $monthlySummary,
            'year'           => $year,
        ]);
    }

    public function attendanceSummary(Request $request): Response
    {
        $year = (int) $request->get('year', (int) date('Y'));
        $month = (int) $request->get('month', (int) date('m'));
        $departmentId = (int) $request->get('department_id', 0);

        $db = App::getInstance()->db();

        $query = "SELECT hp.id, hp.employee_number, hp.first_name_ar, hp.last_name_ar,
                         d.name_ar as department_name,
                         SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) as present_days,
                         SUM(CASE WHEN a.status = 'absent' THEN 1 ELSE 0 END) as absent_days,
                         SUM(CASE WHEN a.status = 'late' THEN 1 ELSE 0 END) as late_days,
                         SUM(COALESCE(a.actual_hours, 0)) as total_hours,
                         SUM(COALESCE(a.overtime_hours, 0)) as total_overtime
                  FROM hr_employee_profiles hp
                  LEFT JOIN hr_departments d ON d.id = hp.department_id
                  LEFT JOIN hr_attendance a ON a.employee_profile_id = hp.id
                       AND YEAR(a.attendance_date) = ? AND MONTH(a.attendance_date) = ?
                       AND a.is_archived = 0
                  WHERE hp.employment_status = 'active' AND hp.is_archived = 0";
        $params = [$year, $month];

        if ($departmentId > 0) {
            $query .= " AND hp.department_id = ?";
            $params[] = $departmentId;
        }

        $query .= " GROUP BY hp.id, hp.employee_number, hp.first_name_ar, hp.last_name_ar, d.name_ar
                     ORDER BY hp.first_name_ar ASC";

        $records = $db->select($query, $params);

        return $this->view('HR.Views.reports.attendance_summary', [
            'records'      => $records,
            'year'         => $year,
            'month'        => $month,
            'departmentId' => $departmentId,
            'departments'  => HrDepartment::allActive(),
        ]);
    }

    public function leaveSummary(Request $request): Response
    {
        $year = (int) $request->get('year', (int) date('Y'));

        $db = App::getInstance()->db();

        $summary = $db->select(
            "SELECT lt.name_ar as leave_type_name,
                    COUNT(lr.id) as request_count,
                    SUM(lr.total_days) as total_days,
                    SUM(CASE WHEN lr.status = 'approved' THEN 1 ELSE 0 END) as approved_count,
                    SUM(CASE WHEN lr.status = 'rejected' THEN 1 ELSE 0 END) as rejected_count,
                    SUM(CASE WHEN lr.status = 'pending' THEN 1 ELSE 0 END) as pending_count
             FROM hr_leave_requests lr
             JOIN hr_leave_types lt ON lt.id = lr.leave_type_id
             WHERE YEAR(lr.start_date) = ? AND lr.is_archived = 0
             GROUP BY lr.leave_type_id, lt.name_ar
             ORDER BY total_days DESC",
            [$year]
        );

        return $this->view('HR.Views.reports.leave_summary', [
            'summary' => $summary,
            'year'    => $year,
        ]);
    }

    public function insuranceSummary(Request $request): Response
    {
        $year = (int) $request->get('year', (int) date('Y'));

        $db = App::getInstance()->db();

        $monthlySummary = $db->select(
            "SELECT pp.month,
                    COUNT(ir.id) as employee_count,
                    SUM(ir.total_employee_share) as total_employee_share,
                    SUM(ir.total_employer_share) as total_employer_share,
                    SUM(ir.total_employee_share + ir.total_employer_share) as total_combined
             FROM hr_payroll_periods pp
             LEFT JOIN hr_insurance_records ir ON ir.period_id = pp.id AND ir.is_archived = 0
             WHERE pp.year = ? AND pp.is_archived = 0
             GROUP BY pp.month
             ORDER BY pp.month ASC",
            [$year]
        );

        return $this->view('HR.Views.reports.insurance_summary', [
            'monthlySummary' => $monthlySummary,
            'year'           => $year,
        ]);
    }

    public function taxSummary(Request $request): Response
    {
        $year = (int) $request->get('year', (int) date('Y'));

        $db = App::getInstance()->db();

        $monthlySummary = $db->select(
            "SELECT pp.month,
                    COUNT(tr.id) as employee_count,
                    SUM(tr.gross_taxable_monthly) as total_taxable,
                    SUM(tr.personal_exemption + tr.insurance_exemption) as total_exemptions,
                    SUM(tr.monthly_tax) as total_tax
             FROM hr_payroll_periods pp
             LEFT JOIN hr_tax_records tr ON tr.period_id = pp.id AND tr.is_archived = 0
             WHERE pp.year = ? AND pp.is_archived = 0
             GROUP BY pp.month
             ORDER BY pp.month ASC",
            [$year]
        );

        $ytdTotal = $db->selectOne(
            "SELECT SUM(tr.monthly_tax) as ytd_tax
             FROM hr_tax_records tr
             JOIN hr_payroll_periods pp ON pp.id = tr.period_id
             WHERE pp.year = ? AND tr.is_archived = 0",
            [$year]
        );

        return $this->view('HR.Views.reports.tax_summary', [
            'monthlySummary' => $monthlySummary,
            'ytdTotal'       => $ytdTotal,
            'year'           => $year,
        ]);
    }

    public function loanSummary(Request $request): Response
    {
        $db = App::getInstance()->db();

        $statusSummary = $db->select(
            "SELECT status,
                    COUNT(*) as count,
                    SUM(loan_amount) as total_amount,
                    SUM(remaining_amount) as total_remaining
             FROM hr_employee_loans
             WHERE is_archived = 0
             GROUP BY status
             ORDER BY FIELD(status, 'pending','approved','disbursed','repaying','settled','rejected')"
        );

        $activeLoans = $db->select(
            "SELECT l.*, hp.first_name_ar, hp.last_name_ar, hp.employee_number
             FROM hr_employee_loans l
             JOIN hr_employee_profiles hp ON hp.id = l.employee_profile_id
             WHERE l.status IN ('disbursed','repaying') AND l.is_archived = 0
             ORDER BY l.remaining_amount DESC"
        );

        return $this->view('HR.Views.reports.loan_summary', [
            'statusSummary' => $statusSummary,
            'activeLoans'   => $activeLoans,
        ]);
    }

    public function contractExpiry(Request $request): Response
    {
        $days = (int) $request->get('days', 30);

        $db = App::getInstance()->db();

        $expiringContracts = $db->select(
            "SELECT c.*, hp.first_name_ar, hp.last_name_ar, hp.employee_number,
                    d.name_ar as department_name,
                    DATEDIFF(c.end_date, CURDATE()) as days_remaining
             FROM hr_contracts c
             JOIN hr_employee_profiles hp ON hp.id = c.employee_profile_id
             LEFT JOIN hr_departments d ON d.id = hp.department_id
             WHERE c.status = 'active'
               AND c.end_date IS NOT NULL
               AND c.end_date <= DATE_ADD(CURDATE(), INTERVAL ? DAY)
               AND c.is_archived = 0
             ORDER BY c.end_date ASC",
            [$days]
        );

        return $this->view('HR.Views.reports.contract_expiry', [
            'contracts' => $expiringContracts,
            'days'      => $days,
        ]);
    }

    public function workforceStatement(Request $request): Response
    {
        $this->authorize('hr.report.view');

        $data = WorkforceReportService::getWorkforceStatement();

        return $this->view('HR.Views.reports.workforce_statement', $data);
    }
}
