<?php

namespace Modules\Appointments\Controllers;

use core\Controller;
use core\Auth;
use core\Database;
use core\Session;
use core\Security;
use Modules\Appointments\Models\Appointment;

class AppointmentController extends Controller
{
    private Appointment $appointmentModel;

    public function __construct()
    {
        parent::__construct();
        $this->appointmentModel = new Appointment();
    }

    public function index(): void
    {
        $this->requirePermission('appointments_view');

        $db = Database::getInstance();
        $dateFrom = $this->request->get('date_from', date('Y-m-d'));
        $dateTo = $this->request->get('date_to', '');
        $status = $this->request->get('status', '');
        $employeeId = $this->request->get('employee_id', '');
        $customerId = $this->request->get('customer_id', '');
        $search = $this->request->get('search', '');
        $page = max(1, (int)$this->request->get('page', 1));
        $perPage = PAGINATION_LIMIT;

        $query = $db->table('appointments a')
            ->select('a.*,
                c.first_name as customer_first_name, c.last_name as customer_last_name, c.phone as customer_phone, c.email as customer_email,
                GROUP_CONCAT(DISTINCT s.name SEPARATOR \', \') as service_names,
                GROUP_CONCAT(DISTINCT CONCAT(e.first_name, \' \', e.last_name) SEPARATOR \', \') as employee_names')
            ->leftJoin('customers c', 'a.customer_id', '=', 'c.id')
            ->leftJoin('appointment_services aps', 'a.id', '=', 'aps.appointment_id')
            ->leftJoin('services s', 'aps.service_id', '=', 's.id')
            ->leftJoin('employees e', 'aps.employee_id', '=', 'e.id')
            ->where('a.deleted_at', 'IS', null);

        if (!empty($dateFrom)) {
            $query->where('a.start_time', '>=', $dateFrom . ' 00:00:00');
        }
        if (!empty($dateTo)) {
            $query->where('a.start_time', '<=', $dateTo . ' 23:59:59');
        }
        if (!empty($status)) {
            $query->where('a.status', $status);
        }
        if (!empty($employeeId)) {
            $query->where('aps.employee_id', $employeeId);
        }
        if (!empty($customerId)) {
            $query->where('a.customer_id', $customerId);
        }
        if (!empty($search)) {
            $query->where(function ($q) use ($search) {
                $searchTerm = '%' . $search . '%';
                $q->where('c.first_name', 'LIKE', $searchTerm)
                  ->orWhere('c.last_name', 'LIKE', $searchTerm)
                  ->orWhere('c.phone', 'LIKE', $searchTerm);
            });
        }

        $appointments = $query->orderBy('a.start_time', 'DESC')
            ->groupBy('a.id')
            ->paginate($perPage, $page);

        $employees = $db->table('employees')
            ->where('is_active', 1)
            ->where('deleted_at', 'IS', null)
            ->orderBy('first_name', 'ASC')
            ->get();

        $this->view('Appointments/index', [
            'title' => 'Appointments',
            'breadcrumb' => ['Appointments' => url('appointments')],
            'appointments' => $appointments,
            'dateFrom' => $dateFrom,
            'dateTo' => $dateTo,
            'status' => $status,
            'employeeId' => $employeeId,
            'customerId' => $customerId,
            'search' => $search,
            'employees' => $employees,
        ]);
    }

    public function create(): void
    {
        $this->requirePermission('appointments_create');

        $db = Database::getInstance();

        $services = $db->table('services s')
            ->select('s.*, sc.name as category_name')
            ->leftJoin('service_categories sc', 's.category_id', '=', 'sc.id')
            ->where('s.is_active', 1)
            ->where('s.deleted_at', 'IS', null)
            ->orderBy('s.name', 'ASC')
            ->get();

        $employees = $db->table('employees')
            ->where('is_active', 1)
            ->where('deleted_at', 'IS', null)
            ->orderBy('first_name', 'ASC')
            ->get();

        $selectedCustomerId = $this->request->get('customer_id', '');

        $this->view('Appointments/create', [
            'title' => 'New Appointment',
            'breadcrumb' => [
                'Appointments' => url('appointments'),
                'New' => url('appointments/create'),
            ],
            'services' => $services,
            'employees' => $employees,
            'selectedCustomerId' => $selectedCustomerId,
        ]);
    }

    private function checkTimeConflict(string $start, string $end, array $services, int $excludeId): ?string
    {
        $employeeIds = [];
        foreach ($services as $svc) {
            if (!empty($svc['employee_id'])) {
                $employeeIds[] = (int)$svc['employee_id'];
            }
        }
        $employeeIds = array_unique($employeeIds);

        if (empty($employeeIds)) {
            return null;
        }

        $db = Database::getInstance();

        $placeholders = implode(',', array_fill(0, count($employeeIds), '?'));
        $params = [...$employeeIds, $excludeId, $end, $start];
        $existing = $db->query(
            "SELECT DISTINCT a.id, a.start_time, a.end_time,
                    CONCAT(e.first_name, ' ', e.last_name) as employee_name
             FROM appointments a
             INNER JOIN appointment_services aps ON a.id = aps.appointment_id
             INNER JOIN employees e ON aps.employee_id = e.id
             WHERE aps.employee_id IN ({$placeholders})
               AND a.id != ?
               AND a.start_time < ?
               AND a.end_time > ?
               AND a.status NOT IN ('cancelled', 'completed', 'no_show')
               AND a.deleted_at IS NULL
             LIMIT 1",
            $params
        );

        if (!empty($existing)) {
            $e = $existing[0];
            return "Employee {$e->employee_name} already has an appointment from "
                 . date(TIME_FORMAT, strtotime($e->start_time)) . ' to '
                 . date(TIME_FORMAT, strtotime($e->end_time)) . ' at this time.';
        }

        return null;
    }

    public function store(): void
    {
        $this->requirePermission('appointments_create');

        $validated = $this->validate([
            'customer_id' => 'required|integer',
            'start_datetime' => 'required',
            'end_datetime' => 'required',
            'notes' => 'max_length:2000',
            'services' => 'required|array',
        ]);

        $services = $validated['services'];
        foreach ($services as $i => $svc) {
            if (empty($svc['service_id']) || !is_numeric($svc['service_id'])) {
                Session::flash('_errors', ['services' => "Service #" . ($i+1) . " is invalid."]);
                Session::flash('_old_input', $this->request->all());
                $this->back();
                exit;
            }
        }

        $conflict = $this->checkTimeConflict($validated['start_datetime'], $validated['end_datetime'], $services, 0);
        if ($conflict) {
            Session::flash('_errors', ['time' => 'Time conflict: ' . $conflict]);
            Session::flash('_old_input', $this->request->all());
            $this->back();
            exit;
        }

        $db = Database::getInstance();
        $db->beginTransaction();

        try {
            $appointmentId = $db->table('appointments')->insert([
                'customer_id' => $validated['customer_id'],
                'start_time' => $validated['start_datetime'],
                'end_time' => $validated['end_datetime'],
                'status' => 'pending',
                'notes' => $validated['notes'] ?? null,
                'created_at' => date(DATE_TIME_FORMAT),
                'updated_at' => date(DATE_TIME_FORMAT),
            ]);

            foreach ($services as $svc) {
                $db->table('appointment_services')->insert([
                    'appointment_id' => $appointmentId,
                    'service_id' => (int)$svc['service_id'],
                    'employee_id' => !empty($svc['employee_id']) ? (int)$svc['employee_id'] : null,
                    'created_at' => date(DATE_TIME_FORMAT),
                ]);
            }

            $db->commit();

            $this->logActivity('create', 'Created appointment #' . $appointmentId);
            $this->logAudit('create', 'appointments', $appointmentId, null, $validated);

            $this->redirectWith(url('appointments'), 'success', 'Appointment created successfully.');
        } catch (\Throwable $e) {
            $db->rollback();
            $this->redirectWith(url('appointments/create'), 'error', 'Failed to create appointment: ' . $e->getMessage());
        }
    }

    public function edit(int $id): void
    {
        $this->requirePermission('appointments_edit');

        $db = Database::getInstance();
        $appointment = $db->table('appointments')->find($id);
        if (!$appointment) {
            $this->redirectWith(url('appointments'), 'error', 'Appointment not found.');
        }

        $appointmentServices = $db->table('appointment_services')
            ->where('appointment_id', $id)
            ->get();

        $services = $db->table('services s')
            ->select('s.*, sc.name as category_name')
            ->leftJoin('service_categories sc', 's.category_id', '=', 'sc.id')
            ->where('s.is_active', 1)
            ->where('s.deleted_at', 'IS', null)
            ->orderBy('s.name', 'ASC')
            ->get();

        $employees = $db->table('employees')
            ->where('is_active', 1)
            ->where('deleted_at', 'IS', null)
            ->orderBy('first_name', 'ASC')
            ->get();

        $customer = $db->table('customers')
            ->where('deleted_at', 'IS', null)
            ->find($appointment->customer_id);

        $this->view('Appointments/edit', [
            'title' => 'Edit Appointment',
            'breadcrumb' => [
                'Appointments' => url('appointments'),
                'Edit' => url('appointments/edit/' . $id),
            ],
            'appointment' => $appointment,
            'appointmentServices' => $appointmentServices,
            'services' => $services,
            'employees' => $employees,
            'customer' => $customer,
        ]);
    }

    public function update(int $id): void
    {
        $this->requirePermission('appointments_edit');

        $db = Database::getInstance();
        $appointment = $db->table('appointments')->find($id);
        if (!$appointment) {
            $this->redirectWith(url('appointments'), 'error', 'Appointment not found.');
        }

        $oldValues = (array)$appointment;

        $validated = $this->validate([
            'customer_id' => 'required|integer',
            'start_datetime' => 'required',
            'end_datetime' => 'required',
            'notes' => 'max_length:2000',
            'services' => 'required|array',
        ]);

        $services = $validated['services'];
        foreach ($services as $i => $svc) {
            if (empty($svc['service_id']) || !is_numeric($svc['service_id'])) {
                Session::flash('_errors', ['services' => "Service #" . ($i+1) . " is invalid."]);
                Session::flash('_old_input', $this->request->all());
                $this->back();
                exit;
            }
        }

        $conflict = $this->checkTimeConflict($validated['start_datetime'], $validated['end_datetime'], $services, $id);
        if ($conflict) {
            Session::flash('_errors', ['time' => 'Time conflict: ' . $conflict]);
            Session::flash('_old_input', $this->request->all());
            $this->back();
            exit;
        }

        $db->beginTransaction();

        try {
            $db->table('appointments')
                ->where('id', $id)
                ->update([
                    'customer_id' => $validated['customer_id'],
                    'start_time' => $validated['start_datetime'],
                    'end_time' => $validated['end_datetime'],
                    'notes' => $validated['notes'] ?? null,
                    'updated_at' => date(DATE_TIME_FORMAT),
                ]);

            $db->table('appointment_services')
                ->where('appointment_id', $id)
                ->delete();

            foreach ($services as $svc) {
                $db->table('appointment_services')->insert([
                    'appointment_id' => $id,
                    'service_id' => (int)$svc['service_id'],
                    'employee_id' => !empty($svc['employee_id']) ? (int)$svc['employee_id'] : null,
                    'created_at' => date(DATE_TIME_FORMAT),
                ]);
            }

            $db->commit();

            $this->logActivity('update', 'Updated appointment #' . $id);
            $this->logAudit('update', 'appointments', $id, $oldValues, $validated);

            $this->redirectWith(url('appointments'), 'success', 'Appointment updated successfully.');
        } catch (\Throwable $e) {
            $db->rollback();
            $this->redirectWith(url('appointments/edit/' . $id), 'error', 'Failed to update appointment: ' . $e->getMessage());
        }
    }

    public function delete(int $id): void
    {
        $this->requirePermission('appointments_delete');

        $db = Database::getInstance();
        $appointment = $db->table('appointments')->find($id);
        if (!$appointment) {
            $this->redirectWith(url('appointments'), 'error', 'Appointment not found.');
        }

        $db->beginTransaction();
        try {
            $db->table('appointment_services')
                ->where('appointment_id', $id)
                ->delete();

            $db->table('appointments')
                ->where('id', $id)
                ->update(['deleted_at' => date(DATE_TIME_FORMAT)]);

            $db->commit();

            $this->logActivity('delete', 'Deleted appointment #' . $id);
            $this->logAudit('delete', 'appointments', $id, (array)$appointment, null);

            $this->redirectWith(url('appointments'), 'success', 'Appointment deleted successfully.');
        } catch (\Throwable $e) {
            $db->rollback();
            $this->redirectWith(url('appointments'), 'error', 'Failed to delete appointment.');
        }
    }

    public function show(int $id): void
    {
        $this->requirePermission('appointments_view');

        $db = Database::getInstance();
        $appointment = $db->query(
            "SELECT a.*, c.first_name as customer_first_name, c.last_name as customer_last_name,
                    c.email as customer_email, c.phone as customer_phone, c.photo as customer_photo
             FROM appointments a
             INNER JOIN customers c ON a.customer_id = c.id
             WHERE a.id = :id AND a.deleted_at IS NULL",
            [':id' => $id]
        );

        if (empty($appointment)) {
            $this->redirectWith(url('appointments'), 'error', 'Appointment not found.');
        }
        $appointment = $appointment[0];

        $appointmentServices = $db->query(
            "SELECT aps.*, s.name as service_name, s.duration_minutes, s.price, s.color,
                    e.first_name as employee_first_name, e.last_name as employee_last_name
             FROM appointment_services aps
             INNER JOIN services s ON aps.service_id = s.id
             LEFT JOIN employees e ON aps.employee_id = e.id
             WHERE aps.appointment_id = :id",
            [':id' => $id]
        );

        $invoices = $db->table('invoices')
            ->select('id, invoice_number, created_at, total, status')
            ->where('appointment_id', $id)
            ->orderBy('created_at', 'DESC')
            ->get();

        $this->view('Appointments/show', [
            'title' => 'Appointment #' . $id,
            'breadcrumb' => [
                'Appointments' => url('appointments'),
                '#' . $id => url('appointments/show/' . $id),
            ],
            'appointment' => $appointment,
            'appointmentServices' => $appointmentServices,
            'invoices' => $invoices,
        ]);
    }

    public function checkIn(int $id): void
    {
        $this->requirePermission('appointments_edit');

        $db = Database::getInstance();
        $appointment = $db->table('appointments')->find($id);
        if (!$appointment) {
            $this->error('Appointment not found.', 404);
        }

        if (!in_array($appointment->status, ['pending', 'confirmed'])) {
            $this->error('Appointment cannot be checked in from current status: ' . $appointment->status);
        }

        $db->table('appointments')
            ->where('id', $id)
            ->update([
                'status' => 'checked_in',
                'updated_at' => date(DATE_TIME_FORMAT),
            ]);

        $this->logActivity('check_in', 'Checked in appointment #' . $id);
        $this->logAudit('update', 'appointments', $id, (array)$appointment, ['status' => 'checked_in']);

        if ($this->isAjax()) {
            $this->success(null, 'Customer checked in successfully.');
        }
        $this->redirectWith(url('appointments'), 'success', 'Customer checked in successfully.');
    }

    public function updateStatus(int $id): void
    {
        $this->requirePermission('appointments_edit');

        $db = Database::getInstance();
        $appointment = $db->table('appointments')->find($id);
        if (!$appointment) {
            $this->error('Appointment not found.', 404);
        }

        $validated = $this->validate([
            'status' => 'required|in:pending,confirmed,checked_in,in_progress,completed,cancelled,no_show',
        ]);

        $allowedTransitions = [
            'pending' => ['confirmed', 'cancelled'],
            'confirmed' => ['checked_in', 'cancelled'],
            'checked_in' => ['in_progress', 'cancelled'],
            'in_progress' => ['completed', 'cancelled'],
            'completed' => [],
            'cancelled' => [],
            'no_show' => [],
        ];

        $currentStatus = $appointment->status;
        $newStatus = $validated['status'];

        if (!in_array($newStatus, $allowedTransitions[$currentStatus] ?? [])) {
            $this->error("Cannot transition from '{$currentStatus}' to '{$newStatus}'.");
        }

        $db->table('appointments')
            ->where('id', $id)
            ->update([
                'status' => $newStatus,
                'updated_at' => date(DATE_TIME_FORMAT),
            ]);

        $this->logActivity('update_status', "Updated appointment #{$id} status: {$currentStatus} -> {$newStatus}");
        $this->logAudit('update', 'appointments', $id, (array)$appointment, ['status' => $newStatus]);

        if ($this->isAjax()) {
            $this->success(null, 'Status updated successfully.');
        }
        $this->redirectWith(url('appointments'), 'success', 'Status updated successfully.');
    }

    public function reception(): void
    {
        $this->requirePermission('appointments_view');

        $db = Database::getInstance();
        $today = date('Y-m-d');

        $appointments = $db->query(
            "SELECT a.*, c.first_name as customer_first_name, c.last_name as customer_last_name,
                    GROUP_CONCAT(DISTINCT s.name SEPARATOR ', ') as service_names,
                    GROUP_CONCAT(DISTINCT CONCAT(e.first_name, ' ', e.last_name) SEPARATOR ', ') as employee_names,
                    GROUP_CONCAT(DISTINCT s.color SEPARATOR ',') as service_colors
             FROM appointments a
             INNER JOIN customers c ON a.customer_id = c.id
             LEFT JOIN appointment_services aps ON a.id = aps.appointment_id
             LEFT JOIN services s ON aps.service_id = s.id
             LEFT JOIN employees e ON aps.employee_id = e.id
              WHERE DATE(a.start_time) = :today AND a.deleted_at IS NULL
             GROUP BY a.id
             ORDER BY a.start_time ASC",
            [':today' => $today]
        );

        $invoiceMap = [];
        $invoices = $db->table('invoices')
            ->select('id, invoice_number, appointment_id')
            ->whereNotNull('appointment_id')
            ->where('deleted_at', 'IS', null)
            ->get();
        foreach ($invoices as $inv) {
            $invoiceMap[$inv->appointment_id] = $inv;
        }

        $grouped = [
            'pending' => [],
            'confirmed' => [],
            'checked_in' => [],
            'in_progress' => [],
            'completed' => [],
        ];

        foreach ($appointments as $apt) {
            $status = $apt->status;
            if (isset($grouped[$status])) {
                $grouped[$status][] = $apt;
            }
        }

        $this->view('Appointments/reception', [
            'title' => 'Reception Board',
            'breadcrumb' => ['Reception' => url('reception')],
            'grouped' => $grouped,
            'invoiceMap' => $invoiceMap,
        ]);
    }
}
