<?php
declare(strict_types=1);

namespace App\Modules\SportsActivity\Controllers;

use App\Core\Controller;
use App\Core\Request;
use App\Core\Response;
use App\Core\App;

class InstitutionController extends Controller
{
    public function index(Request $request): Response
    {
        $db = App::getInstance()->db();

        $search = trim((string) $request->get('search', ''));
        $type = trim((string) $request->get('type', ''));

        $where = "is_archived = 0";
        $params = [];

        if ($search !== '') {
            $where .= " AND (name_ar LIKE ? OR name_en LIKE ? OR code LIKE ?)";
            $params[] = "%{$search}%";
            $params[] = "%{$search}%";
            $params[] = "%{$search}%";
        }
        if ($type !== '') {
            $where .= " AND institution_type = ?";
            $params[] = $type;
        }

        $institutions = $db->select(
            "SELECT *,
                    (SELECT COUNT(*) FROM sa_bookings b WHERE b.institution_id = sa_institutions.id AND b.status != 'cancelled') as booking_count
             FROM sa_institutions
             WHERE {$where}
             ORDER BY name_ar ASC",
            $params
        );

        return $this->view('SportsActivity.Views.institutions.index', [
            'institutions' => $institutions,
            'filters'      => ['search' => $search, 'type' => $type],
        ]);
    }

    public function create(Request $request): Response
    {
        return $this->view('SportsActivity.Views.institutions.form', [
            'institution' => null,
            'mode'        => 'create',
        ]);
    }

    public function store(Request $request): Response
    {
        $data = $this->validateInstitution($request);
        if ($data instanceof Response) {
            return $data;
        }

        $db = App::getInstance()->db();
        $employeeId = (int) (App::getInstance()->session()->get('employee_id') ?? 0);

        $existing = $db->selectOne(
            "SELECT id FROM sa_institutions WHERE code = ?",
            [$data['code']]
        );
        if ($existing) {
            return $this->redirect('/sa/institutions/create')->withError('كود المؤسسة مستخدم بالفعل');
        }

        $data['created_at'] = date('Y-m-d H:i:s');
        $data['updated_at'] = date('Y-m-d H:i:s');
        $data['created_by'] = $employeeId;

        $db->insert('sa_institutions', $data);

        return $this->redirect('/sa/institutions')->withSuccess('تم إضافة المؤسسة بنجاح');
    }

    public function edit(Request $request, string $id): Response
    {
        $db = App::getInstance()->db();

        $institution = $db->selectOne(
            "SELECT * FROM sa_institutions WHERE id = ? AND is_archived = 0",
            [(int) $id]
        );
        if (!$institution) {
            return $this->redirect('/sa/institutions')->withError('المؤسسة غير موجودة');
        }

        return $this->view('SportsActivity.Views.institutions.form', [
            'institution' => $institution,
            'mode'        => 'edit',
        ]);
    }

    public function update(Request $request, string $id): Response
    {
        $data = $this->validateInstitution($request);
        if ($data instanceof Response) {
            return $data;
        }

        $db = App::getInstance()->db();
        $employeeId = (int) (App::getInstance()->session()->get('employee_id') ?? 0);

        $existing = $db->selectOne(
            "SELECT id FROM sa_institutions WHERE code = ? AND id != ?",
            [$data['code'], (int) $id]
        );
        if ($existing) {
            return $this->redirect("/sa/institutions/{$id}/edit")->withError('كود المؤسسة مستخدم بالفعل');
        }

        $data['updated_at'] = date('Y-m-d H:i:s');
        $data['updated_by'] = $employeeId;

        $db->update('sa_institutions', $data, 'id = ?', [(int) $id]);

        return $this->redirect('/sa/institutions')->withSuccess('تم تحديث بيانات المؤسسة');
    }

    public function show(Request $request, string $id): Response
    {
        $db = App::getInstance()->db();

        $institution = $db->selectOne(
            "SELECT * FROM sa_institutions WHERE id = ?",
            [(int) $id]
        );
        if (!$institution) {
            return $this->redirect('/sa/institutions')->withError('المؤسسة غير موجودة');
        }

        $bookings = $db->select(
            "SELECT b.*, fu.name_ar as unit_name
             FROM sa_bookings b
             JOIN sa_facility_units fu ON fu.id = b.facility_unit_id
             WHERE b.institution_id = ?
             ORDER BY b.booking_date DESC
             LIMIT 50",
            [(int) $id]
        );

        $stats = $db->selectOne(
            "SELECT
                COUNT(*) as total_bookings,
                SUM(CASE WHEN status = 'confirmed' THEN 1 ELSE 0 END) as confirmed,
                SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
                SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled,
                SUM(total_amount) as total_revenue
             FROM sa_bookings WHERE institution_id = ?",
            [(int) $id]
        );

        return $this->view('SportsActivity.Views.institutions.show', [
            'institution' => $institution,
            'bookings'    => $bookings,
            'stats'       => $stats,
        ]);
    }

    public function archive(Request $request, string $id): Response
    {
        $db = App::getInstance()->db();
        $employeeId = (int) (App::getInstance()->session()->get('employee_id') ?? 0);

        $db->update('sa_institutions', [
            'is_archived' => 1,
            'archived_at' => date('Y-m-d H:i:s'),
            'archived_by' => $employeeId,
            'updated_at'  => date('Y-m-d H:i:s'),
        ], 'id = ?', [(int) $id]);

        return $this->redirect('/sa/institutions')->withSuccess('تم أرشفة المؤسسة');
    }

    private function validateInstitution(Request $request)
    {
        $code = trim((string) $request->post('code', ''));
        $nameAr = trim((string) $request->post('name_ar', ''));
        $type = trim((string) $request->post('institution_type', 'school'));

        if ($code === '' || $nameAr === '') {
            return $this->redirect('/sa/institutions')->withError('الكود والاسم مطلوبان');
        }

        $validTypes = ['school', 'university', 'company', 'club', 'government', 'other'];
        if (!in_array($type, $validTypes, true)) {
            $type = 'other';
        }

        return [
            'code'              => $code,
            'name_ar'           => $nameAr,
            'name_en'           => trim((string) $request->post('name_en', '')) ?: null,
            'institution_type'  => $type,
            'contact_person'    => trim((string) $request->post('contact_person', '')) ?: null,
            'phone'             => trim((string) $request->post('phone', '')) ?: null,
            'email'             => trim((string) $request->post('email', '')) ?: null,
            'address'           => trim((string) $request->post('address', '')) ?: null,
            'contract_start'    => trim((string) $request->post('contract_start', '')) ?: null,
            'contract_end'      => trim((string) $request->post('contract_end', '')) ?: null,
            'hourly_rate'       => trim((string) $request->post('hourly_rate', '')) ?: null,
            'discount_percent'  => (float) $request->post('discount_percent', 0),
            'max_participants'  => ((int) $request->post('max_participants', 0)) ?: null,
            'notes'             => trim((string) $request->post('notes', '')) ?: null,
            'is_active'         => (int) $request->post('is_active', 1),
        ];
    }
}
