<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use Exception;

class PaymentModel extends Model
{
    protected string $table = 'payments';

    public function getAll(int $tenantId, array $filters = [], int $page = 1, int $perPage = 20): array
    {
        $offset = ($page - 1) * $perPage;
        
        $sql = "SELECT p.*, c.name as customer_name 
                FROM payments p
                LEFT JOIN customers c ON p.customer_id = c.id
                WHERE p.tenant_id = :tenant_id";
        
        $params = [':tenant_id' => $tenantId];

        if (!empty($filters['type'])) {
            $sql .= " AND p.payment_type = :type";
            $params[':type'] = $filters['type'];
        }

        $sql .= " ORDER BY p.payment_date DESC, p.id DESC LIMIT $perPage OFFSET $offset";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function getById(int $id, int $tenantId): ?array
    {
        $sql = "SELECT p.*, c.name as customer_name, c.email as customer_email
                FROM payments p
                LEFT JOIN customers c ON p.customer_id = c.id
                WHERE p.id = ? AND p.tenant_id = ?";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$id, $tenantId]);
        return $stmt->fetch() ?: null;
    }

    public function createPayment(int $tenantId, array $data, int $userId): array
    {
        $this->db->beginTransaction();

        try {
            $pmtNum = 'REC/' . date('Ym') . '/' . mt_rand(1000, 9999);
            
            $sql = "INSERT INTO payments (
                tenant_id, payment_number, payment_type, reference_type, 
                customer_id, amount, payment_date, payment_method, 
                reference_number, notes, created_by
            ) VALUES (?, ?, 'received', 'advance', ?, ?, ?, ?, ?, ?, ?)";
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                $tenantId, $pmtNum, $data['customer_id'] ?? null, $data['amount'],
                $data['payment_date'] ?? date('Y-m-d'),
                $data['payment_method'] ?? 'cash',
                $data['reference_number'] ?? null,
                $data['notes'] ?? null,
                $userId
            ]);
            
            $paymentId = $this->db->lastInsertId();
            $this->db->commit();
            
            return ['id' => $paymentId, 'payment_number' => $pmtNum];
        } catch (Exception $e) {
            $this->db->rollBack();
            throw new Exception("Error creating payment: " . $e->getMessage());
        }
    }
}
