<?php

namespace App\Models;

class LeaveRequest extends BaseModel
{
    public function create(array $data): int
    {
        $stmt = $this->pdo->prepare('INSERT INTO leave_requests 
            (user_id, nama, tempat_bekerja, alasan, tarikh_mula, tarikh_akhir, jumlah_hari, kenyataan, status) 
            VALUES (:user_id, :nama, :tempat, :alasan, :mula, :akhir, :jumlah, :kenyataan, :status)');
        $stmt->execute([
            'user_id' => $data['user_id'],
            'nama' => $data['nama'],
            'tempat' => $data['tempat_bekerja'],
            'alasan' => $data['alasan'],
            'mula' => $data['tarikh_mula'],
            'akhir' => $data['tarikh_akhir'],
            'jumlah' => $data['jumlah_hari'],
            'kenyataan' => $data['kenyataan'],
            'status' => 'pending',
        ]);

        return (int) $this->pdo->lastInsertId();
    }

    public function updateStatus(int $id, string $status, ?int $approverId, ?string $remarks = null): void
    {
        $stmt = $this->pdo->prepare('UPDATE leave_requests SET status = :status, approver_id = :approver_id, 
            approver_remarks = :remarks, updated_at = NOW() WHERE id = :id');
        $stmt->execute([
            'status' => $status,
            'approver_id' => $approverId,
            'remarks' => $remarks,
            'id' => $id,
        ]);
    }

    public function filter(array $filters, int $limit, int $offset): array
    {
        $sortKey = $filters['sort'] ?? 'tarikh_mula';
        $direction = strtoupper($filters['direction'] ?? 'DESC');
        unset($filters['sort'], $filters['direction']);

        $sortMap = [
            'tarikh_mula' => 'tarikh_mula',
            'nama' => 'nama',
            'jumlah_hari' => 'jumlah_hari',
            'status' => 'status',
            'amount' => 'jumlah_hari',
            'date' => 'tarikh_mula',
        ];
        $orderBy = $sortMap[$sortKey] ?? $sortMap['tarikh_mula'];
        $direction = $direction === 'ASC' ? 'ASC' : 'DESC';

        [$conditions, $params] = $this->buildConditions($filters);
        $where = $conditions ? 'WHERE ' . implode(' AND ', $conditions) : '';
        $sql = "SELECT * FROM leave_requests {$where} ORDER BY {$orderBy} {$direction} LIMIT :limit OFFSET :offset";
        $stmt = $this->pdo->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue(':' . $key, $value);
        }
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    public function count(array $filters): int
    {
        unset($filters['sort'], $filters['direction']);
        [$conditions, $params] = $this->buildConditions($filters);
        $where = $conditions ? 'WHERE ' . implode(' AND ', $conditions) : '';
        $stmt = $this->pdo->prepare("SELECT COUNT(*) FROM leave_requests {$where}");
        $stmt->execute($params);
        return (int) $stmt->fetchColumn();
    }

    public function pending(): array
    {
        $stmt = $this->pdo->query("SELECT * FROM leave_requests WHERE status = 'pending'");
        return $stmt->fetchAll();
    }

    public function find(int $id): ?array
    {
        $stmt = $this->pdo->prepare('SELECT lr.*, u.email FROM leave_requests lr 
            JOIN users u ON u.id = lr.user_id WHERE lr.id = :id');
        $stmt->execute(['id' => $id]);
        return $stmt->fetch() ?: null;
    }

    private function buildConditions(array $filters): array
    {
        $conditions = [];
        $params = [];

        if (!empty($filters['month'])) {
            $conditions[] = 'MONTH(tarikh_mula) = :month';
            $params['month'] = $filters['month'];
        }
        if (!empty($filters['year'])) {
            $conditions[] = 'YEAR(tarikh_mula) = :year';
            $params['year'] = $filters['year'];
        }
        if (!empty($filters['search'])) {
            $conditions[] = 'nama LIKE :search';
            $params['search'] = '%' . $filters['search'] . '%';
        }
        if (!empty($filters['status'])) {
            $conditions[] = 'status = :status';
            $params['status'] = $filters['status'];
        }
        if (!empty($filters['user_id'])) {
            $conditions[] = 'user_id = :user_id';
            $params['user_id'] = $filters['user_id'];
        }
        if (!empty($filters['user_ids']) && is_array($filters['user_ids'])) {
            $placeholders = [];
            foreach ($filters['user_ids'] as $index => $id) {
                $key = 'user_ids_' . $index;
                $placeholders[] = ':' . $key;
                $params[$key] = (int) $id;
            }
            if ($placeholders) {
                $conditions[] = 'user_id IN (' . implode(',', $placeholders) . ')';
            }
        }

        return [$conditions, $params];
    }
}

