<?php
class StudentPayment
{
    public static function ensureTable(): void
    {
        $pdo = Database::pdo();
        if (Database::driver() === 'mysql') {
            $pdo->exec("CREATE TABLE IF NOT EXISTS student_payments (
                id INT AUTO_INCREMENT PRIMARY KEY,
                student_id INT NOT NULL,
                title VARCHAR(180) NOT NULL,
                amount DECIMAL(10,2) NOT NULL DEFAULT 0,
                due_date DATE NULL,
                payment_link TEXT NULL,
                pix_code TEXT NULL,
                status VARCHAR(20) NOT NULL DEFAULT 'pending',
                is_active TINYINT(1) NOT NULL DEFAULT 1,
                notes TEXT NULL,
                created_at DATETIME NOT NULL,
                paid_at DATETIME NULL,
                INDEX idx_student_payments_student (student_id),
                INDEX idx_student_payments_status (status, is_active)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        } else {
            $pdo->exec("CREATE TABLE IF NOT EXISTS student_payments (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                student_id INTEGER NOT NULL,
                title TEXT NOT NULL,
                amount REAL NOT NULL DEFAULT 0,
                due_date TEXT NULL,
                payment_link TEXT NULL,
                pix_code TEXT NULL,
                status TEXT NOT NULL DEFAULT 'pending',
                is_active INTEGER NOT NULL DEFAULT 1,
                notes TEXT NULL,
                created_at TEXT NOT NULL,
                paid_at TEXT NULL,
                FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE
            )");
            $pdo->exec('CREATE INDEX IF NOT EXISTS idx_student_payments_student ON student_payments(student_id)');
            $pdo->exec('CREATE INDEX IF NOT EXISTS idx_student_payments_status ON student_payments(status, is_active)');
        }
    }

    public static function moneyToFloat($value): float
    {
        $value = trim((string)$value);
        if ($value === '') return 0.0;
        $value = str_replace(['R$', ' '], '', $value);
        if (str_contains($value, ',') && str_contains($value, '.')) {
            $value = str_replace('.', '', $value);
            $value = str_replace(',', '.', $value);
        } else {
            $value = str_replace(',', '.', $value);
        }
        return max(0, (float)$value);
    }

    public static function statusLabel(string $status): string
    {
        return match ($status) {
            'paid' => 'Pago',
            'canceled' => 'Cancelado',
            default => 'Pendente',
        };
    }

    public static function save(array $data): array
    {
        Database::migrate();
        self::ensureTable();
        $id = (int)($data['payment_id'] ?? 0);
        $studentId = (int)($data['student_id'] ?? 0);
        $title = trim($data['title'] ?? '');
        $amount = self::moneyToFloat($data['amount'] ?? 0);
        $due = trim($data['due_date'] ?? '');
        $link = trim($data['payment_link'] ?? '');
        $pix = trim($data['pix_code'] ?? '');
        $status = trim($data['status'] ?? 'pending');
        $notes = trim($data['notes'] ?? '');
        $active = !empty($data['is_active']) ? 1 : 0;

        if ($studentId <= 0) {
            return ['ok' => false, 'message' => 'Selecione o aluno.'];
        }
        if ($title === '') {
            return ['ok' => false, 'message' => 'Informe o título do pagamento.'];
        }
        if (!in_array($status, ['pending','paid','canceled'], true)) {
            $status = 'pending';
        }
        if ($due !== '' && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $due)) {
            return ['ok' => false, 'message' => 'Data de vencimento inválida.'];
        }

        $pdo = Database::pdo();
        if ($id > 0) {
            $stmt = $pdo->prepare('UPDATE student_payments SET student_id=?, title=?, amount=?, due_date=?, payment_link=?, pix_code=?, status=?, is_active=?, notes=?, paid_at=? WHERE id=?');
            $stmt->execute([$studentId, $title, $amount, $due ?: null, $link, $pix, $status, $active, $notes, $status === 'paid' ? date('Y-m-d H:i:s') : null, $id]);
            return ['ok' => true, 'message' => 'Pagamento atualizado com sucesso.'];
        }

        $stmt = $pdo->prepare('INSERT INTO student_payments (student_id, title, amount, due_date, payment_link, pix_code, status, is_active, notes, created_at, paid_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');
        $stmt->execute([$studentId, $title, $amount, $due ?: null, $link, $pix, $status, $active, $notes, date('Y-m-d H:i:s'), $status === 'paid' ? date('Y-m-d H:i:s') : null]);
        return ['ok' => true, 'message' => 'Pagamento cadastrado com sucesso.'];
    }

    public static function all(): array
    {
        Database::migrate();
        self::ensureTable();
        $stmt = Database::pdo()->query("SELECT sp.*, u.name AS student_name, u.email AS student_email, u.cpf AS student_cpf
            FROM student_payments sp
            INNER JOIN users u ON u.id = sp.student_id
            WHERE u.deleted_at IS NULL
            ORDER BY sp.id DESC LIMIT 500");
        return $stmt->fetchAll();
    }

    public static function forStudent(int $studentId, bool $onlyActive = true): array
    {
        Database::migrate();
        self::ensureTable();
        $sql = 'SELECT * FROM student_payments WHERE student_id = ?';
        if ($onlyActive) {
            $sql .= " AND is_active = 1 AND status != 'canceled'";
        }
        $sql .= ' ORDER BY CASE status WHEN "pending" THEN 1 WHEN "paid" THEN 2 ELSE 3 END, due_date ASC, id DESC';
        $stmt = Database::pdo()->prepare($sql);
        $stmt->execute([$studentId]);
        return $stmt->fetchAll();
    }

    public static function pendingForStudent(int $studentId): array
    {
        Database::migrate();
        self::ensureTable();
        $stmt = Database::pdo()->prepare("SELECT * FROM student_payments WHERE student_id=? AND is_active=1 AND status='pending' ORDER BY due_date ASC, id DESC");
        $stmt->execute([$studentId]);
        return $stmt->fetchAll();
    }

    public static function delete(int $id): bool
    {
        self::ensureTable();
        $stmt = Database::pdo()->prepare('DELETE FROM student_payments WHERE id=?');
        $stmt->execute([$id]);
        return $stmt->rowCount() > 0;
    }
}
