<?php

namespace App\Models;

class Claim extends BaseModel
{
    public function create(array $data): int
    {
        $stmt = $this->pdo->prepare('INSERT INTO claims 
            (user_id, nama_staff, jabatan, tujuan, amount, file_path, catatan, status) 
            VALUES (:user_id, :nama, :jabatan, :tujuan, :amount, :file, :catatan, :status)');
        $stmt->execute([
            'user_id' => $data['user_id'],
            'nama' => $data['nama_staff'],
            'jabatan' => $data['jabatan'],
            'tujuan' => $data['tujuan'],
            'amount' => $data['amount'],
            'file' => $data['file_path'],
            'catatan' => $data['catatan'],
            '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 claims 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 paginate(array $filters, int $limit, int $offset): array
    {
        $sortKey = $filters['sort'] ?? 'date';
        $direction = strtoupper($filters['direction'] ?? 'DESC');
        unset($filters['sort'], $filters['direction']);

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

        [$conditions, $params] = $this->buildConditions($filters);
        $where = $conditions ? 'WHERE ' . implode(' AND ', $conditions) : '';
        $sql = "SELECT * FROM claims {$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 claims {$where}");
        $stmt->execute($params);
        return (int) $stmt->fetchColumn();
    }

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

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

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

        if (!empty($filters['status'])) {
            $conditions[] = 'status = :status';
            $params['status'] = $filters['status'];
        }
        if (!empty($filters['search'])) {
            $conditions[] = '(nama_staff LIKE :search OR jabatan LIKE :search)';
            $params['search'] = '%' . $filters['search'] . '%';
        }
        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];
    }
}

