<?php

namespace Modules\POS\Models;

use core\Model;
use core\Database;

class Invoice extends Model
{
    protected string $table = 'invoices';
    protected string $primaryKey = 'id';
    protected array $fillable = [
        'invoice_number', 'customer_id', 'created_by', 'branch_id', 'appointment_id',
        'subtotal', 'discount_total', 'tax_total', 'grand_total', 'net_total', 'paid_amount', 'balance_amount',
        'payment_status', 'notes', 'created_at', 'updated_at',
    ];
    protected array $guarded = ['id'];
    protected array $casts = [
        'subtotal' => 'float',
        'discount_total' => 'float',
        'tax_total' => 'float',
        'grand_total' => 'float',
        'net_total' => 'float',
        'paid_amount' => 'float',
        'balance_amount' => 'float',
        'created_at' => 'datetime',
        'updated_at' => 'datetime',
    ];

    public function generateInvoiceNumber(): string
    {
        $prefix = 'INV-';
        $year = date('Y');
        $month = date('m');
        $db = Database::getInstance();
        $count = $db->table($this->table)
            ->where('invoice_number', 'LIKE', $prefix . $year . $month . '%')
            ->count();
        return $prefix . $year . $month . str_pad($count + 1, 4, '0', STR_PAD_LEFT);
    }

    public function getWithRelations(int $perPage = PAGINATION_LIMIT): object
    {
        $db = Database::getInstance();
        return $db->table($this->table . ' i')
            ->select('i.*, c.first_name as customer_first_name, c.last_name as customer_last_name,
                      c.phone as customer_phone, u.first_name as user_first_name, u.last_name as user_last_name')
            ->leftJoin('customers c', 'i.customer_id', '=', 'c.id')
            ->leftJoin('users u', 'i.created_by', '=', 'u.id')
            ->where('i.deleted_at', 'IS', null)
            ->orderBy('i.created_at', 'DESC')
            ->paginate($perPage);
    }

    public function getCustomerInvoices(int $customerId): array
    {
        $db = Database::getInstance();
        return $db->table($this->table)
            ->where('customer_id', $customerId)
            ->where('deleted_at', 'IS', null)
            ->orderBy('created_at', 'DESC')
            ->get();
    }

    public function getTodayInvoices(): array
    {
        $db = Database::getInstance();
        $today = date('Y-m-d');
        return $db->table($this->table)
            ->where('created_at', '>=', $today . ' 00:00:00')
            ->where('created_at', '<=', $today . ' 23:59:59')
            ->where('deleted_at', 'IS', null)
            ->orderBy('created_at', 'DESC')
            ->get();
    }

    public function getPaymentStatusCounts(): array
    {
        $db = Database::getInstance();
        $results = $db->table($this->table)
            ->select('payment_status, COUNT(*) as count')
            ->where('deleted_at', 'IS', null)
            ->groupBy('payment_status')
            ->get();

        $counts = ['paid' => 0, 'partial' => 0, 'pending' => 0, 'refunded' => 0, 'cancelled' => 0];
        foreach ($results as $row) {
            if (isset($counts[$row->payment_status])) {
                $counts[$row->payment_status] = (int)$row->count;
            }
        }
        return $counts;
    }
}
