<?php

namespace App\Models;

use CodeIgniter\Model;

class InvoiceModel extends Model
{
    protected $table = 'invoices';
    protected $primaryKey = 'id';
    protected $allowedFields = [
        'company_id', 'client_id', 'user_id', 'invoice_number', 'invoice_type', 'status',
        'date', 'due_date', 'reference_client', 'customs_code', 'reper_number',
        'designation_global', 'gross_weight', 'weight_unit', 'package_count',
        'currency_value', 'currency_unit', 'shipper', 'declaration_number', 'regime',
        'quittance_number', 'quittance_amount', 'subtotal', 'discount_amount',
        'discount_percent', 'vat_base', 'vat_amount', 'vat_rate', 'stamp_duty',
        'total_ht', 'total_ttc', 'advance_amount', 'net_payable', 'amount_in_words',
        'notes', 'terms', 'pdf_path', 'sent_at', 'paid_at'
    ];
    protected $returnType = 'array';
    protected $useTimestamps = true;
    protected $useSoftDeletes = true;

    public function getWithDetails(int $id): ?array
    {
        $invoice = $this->find($id);
        if (!$invoice) return null;

        $invoice['client'] = (new ClientModel())->find($invoice['client_id']);
        $invoice['lines'] = (new InvoiceLineModel())->where('invoice_id', $id)->orderBy('sort_order', 'ASC')->findAll();
        $invoice['payments'] = (new PaymentModel())->where('invoice_id', $id)->orderBy('payment_date', 'DESC')->findAll();
        $invoice['user'] = (new UserModel())->find($invoice['user_id']);

        return $invoice;
    }

    public function getDashboardStats(int $companyId): array
    {
        $currentMonth = date('Y-m');
        $lastMonth = date('Y-m', strtotime('-1 month'));

        return [
            'total_revenue' => $this->where('company_id', $companyId)->where('status', 'payee')->selectSum('total_ttc')->first()['total_ttc'] ?? 0,
            'month_revenue' => $this->where('company_id', $companyId)->where("DATE_FORMAT(date, '%Y-%m')", $currentMonth)->where('status', 'payee')->selectSum('total_ttc')->first()['total_ttc'] ?? 0,
            'unpaid_count' => $this->where('company_id', $companyId)->whereIn('status', ['envoyee', 'partielle', 'retard'])->countAllResults(),
            'unpaid_total' => $this->where('company_id', $companyId)->whereIn('status', ['envoyee', 'partielle', 'retard'])->selectSum('net_payable')->first()['net_payable'] ?? 0,
            'invoice_count_month' => $this->where('company_id', $companyId)->where("DATE_FORMAT(date, '%Y-%m')", $currentMonth)->countAllResults(),
            'overdue_count' => $this->where('company_id', $companyId)->where('status', 'retard')->countAllResults(),
        ];
    }

    public function generateNumber(int $companyId, string $prefix, int $year): string
    {
        $last = $this->where('company_id', $companyId)
            ->where("invoice_number LIKE", "%/{$year}")
            ->orderBy('id', 'DESC')
            ->first();

        $num = 1;
        if ($last) {
            preg_match('/^(\d+)\//', $last['invoice_number'], $matches);
            if (!empty($matches[1])) {
                $num = (int) $matches[1] + 1;
            }
        }

        return str_pad($num, 3, '0', STR_PAD_LEFT) . "/{$year}";
    }

    public function getMonthlyRevenue(int $companyId, int $months = 12): array
    {
        $result = [];
        for ($i = $months - 1; $i >= 0; $i--) {
            $month = date('Y-m', strtotime("-{$i} months"));
            $revenue = $this->where('company_id', $companyId)
                ->where("DATE_FORMAT(date, '%Y-%m')", $month)
                ->where('status', 'payee')
                ->selectSum('total_ttc')
                ->first()['total_ttc'] ?? 0;

            $result[] = [
                'month' => $month,
                'revenue' => (float) $revenue,
            ];
        }
        return $result;
    }
}
