<?php
class Customer extends Model
{
    public function findByEmail(string $email): ?array
    {
        return $this->fetch('SELECT * FROM customers WHERE email = :email LIMIT 1', ['email' => $email]);
    }

    public function create(array $data): ?string
    {
        $sql = 'INSERT INTO customers (name, email, phone, company, address, notes, created_at) VALUES (:name, :email, :phone, :company, :address, :notes, NOW())';
        $this->execute($sql, [
            'name' => $data['name'],
            'email' => $data['email'] ?? null,
            'phone' => $data['phone'] ?? null,
            'company' => $data['company'] ?? null,
            'address' => $data['address'] ?? null,
            'notes' => $data['notes'] ?? null,
        ]);
        return $this->lastInsertId();
    }

    public function updateContact(int $customerId, array $data): bool
    {
        return $this->execute('UPDATE customers SET name = :name, email = :email, phone = :phone, company = :company, address = :address, notes = :notes WHERE id = :id', [
            'name' => $data['name'],
            'email' => $data['email'] ?? null,
            'phone' => $data['phone'] ?? null,
            'company' => $data['company'] ?? null,
            'address' => $data['address'] ?? null,
            'notes' => $data['notes'] ?? null,
            'id' => $customerId,
        ]);
    }

    public function update(int $customerId, array $data): bool
    {
        $sql = 'UPDATE customers SET name = :name, email = :email, phone = :phone, company = :company, address = :address, notes = :notes WHERE id = :id';
        return $this->execute($sql, [
            'name' => $data['name'],
            'email' => $data['email'] ?? null,
            'phone' => $data['phone'] ?? null,
            'company' => $data['company'] ?? null,
            'address' => $data['address'] ?? null,
            'notes' => $data['notes'] ?? null,
            'id' => $customerId,
        ]);
    }

    public function count(): int
    {
        $result = $this->fetch('SELECT COUNT(*) AS total FROM customers');
        return (int)($result['total'] ?? 0);
    }

    public function all(): array
    {
        return $this->fetchAll('SELECT * FROM customers ORDER BY name ASC');
    }

    public function allWithBillingSummary(): array
    {
        return $this->fetchAll(
            'SELECT c.*, COALESCE(SUM(i.total_amount), 0.00) AS billed_amount, COALESCE(SUM(i.paid_amount), 0.00) AS paid_amount, COALESCE(SUM(i.total_amount - i.paid_amount), 0.00) AS outstanding_amount FROM customers c LEFT JOIN invoices i ON i.customer_id = c.id GROUP BY c.id ORDER BY c.created_at DESC'
        );
    }

    public function findById(int $id): ?array
    {
        return $this->fetch('SELECT * FROM customers WHERE id = :id LIMIT 1', ['id' => $id]);
    }

    public function delete(int $id): bool
    {
        return $this->execute('DELETE FROM customers WHERE id = :id', ['id' => $id]);
    }
}
