<?php

namespace App\Models;

class AdvanceSalary extends BaseModel
{
    public function create(array $data): int
    {
        $stmt = $this->pdo->prepare('INSERT INTO advance_salary 
            (user_id, nama, jumlah, tujuan, kenyataan_setuju, status) 
            VALUES (:user_id, :nama, :jumlah, :tujuan, :kenyataan, :status)');
        $stmt->execute([
            'user_id' => $data['user_id'],
            'nama' => $data['nama'],
            'jumlah' => $data['jumlah'],
            'tujuan' => $data['tujuan'],
            'kenyataan' => $data['kenyataan_setuju'],
            '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 advance_salary 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' => 'a.created_at',
            'name' => 'a.nama',
            'status' => 'a.status',
            'amount' => 'a.jumlah',
        ];
        $orderBy = $sortMap[$sortKey] ?? $sortMap['date'];
        $direction = $direction === 'ASC' ? 'ASC' : 'DESC';

        [$conditions, $params] = $this->buildConditions($filters);
        $where = $conditions ? 'WHERE ' . implode(' AND ', $conditions) : '';
        $sql = "SELECT a.*, u.name AS staff_name FROM advance_salary a 
            JOIN users u ON u.id = a.user_id {$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 advance_salary a JOIN users u ON u.id = a.user_id {$where}");
        $stmt->execute($params);
        return (int) $stmt->fetchColumn();
    }

    public function pending(): array
    {
        $stmt = $this->pdo->query("SELECT a.*, u.name AS staff_name FROM advance_salary a 
            JOIN users u ON u.id = a.user_id WHERE a.status = 'pending'");
        return $stmt->fetchAll();
    }

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

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

        if (!empty($filters['status'])) {
            $conditions[] = 'a.status = :status';
            $params['status'] = $filters['status'];
        }

        if (!empty($filters['search'])) {
            $conditions[] = '(a.nama LIKE :search OR u.name LIKE :search)';
            $params['search'] = '%' . $filters['search'] . '%';
        }
        if (!empty($filters['user_id'])) {
            $conditions[] = 'a.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[] = 'a.user_id IN (' . implode(',', $placeholders) . ')';
            }
        }

        return [$conditions, $params];
    }
}

