<?php
declare(strict_types=1);

namespace App\Models;

use Core\DB;
use Core\Model;

final class Invoice extends Model
{
    protected string $table = 'invoices';
    protected string $primaryKey = 'invoice_id';

    public const STATUSES        = ['draft', 'sent', 'partial', 'paid', 'overdue', 'cancelled'];
    public const PAYMENT_METHODS = ['cash', 'bank_transfer', 'cheque', 'mobile_money', 'card'];

    private static function generateNumber(int $tenantId): string
    {
        $year = date('Y');
        $prefix = "INV-{$year}-";
        $row = DB::one(
            "SELECT MAX(CAST(SUBSTRING(invoice_number, LENGTH(:pf)+1) AS UNSIGNED)) AS nxt FROM invoices WHERE tenant_id = :t AND invoice_number LIKE :lk",
            ['pf' => $prefix, 't' => $tenantId, 'lk' => $prefix . '%']
        );
        $next = ($row['nxt'] ?? 0) + 1;
        return $prefix . str_pad((string) $next, 5, '0', STR_PAD_LEFT);
    }

    public static function listForTenant(int $tenantId, array $filters = [], ?int $limit = null, int $offset = 0, string $sort = 'newest'): array
    {
        $sql = 'SELECT i.*, c.name AS customer_name
                  FROM invoices i
             LEFT JOIN customers c ON c.customer_id = i.customer_id
                 WHERE i.tenant_id = :t AND i.deleted_at IS NULL';
        $params = ['t' => $tenantId];

        if (!empty($filters['status']) && in_array($filters['status'], self::STATUSES, true)) {
            $sql .= ' AND i.status = :st';
            $params['st'] = $filters['status'];
        }
        if (!empty($filters['customer_id'])) {
            $sql .= ' AND i.customer_id = :cid';
            $params['cid'] = (int) $filters['customer_id'];
        }
        if (!empty($filters['q'])) {
            $sql .= ' AND (i.invoice_number LIKE :q1 OR c.name LIKE :q2)';
            $q = '%' . $filters['q'] . '%';
            $params['q1'] = $q;
            $params['q2'] = $q;
        }
        // Auto-detect overdue: items past due_date with unpaid balance
        if (!empty($filters['overdue'])) {
            $sql .= ' AND i.due_date < CURDATE() AND i.balance_due > 0';
        }

        switch ($sort) {
            case 'oldest':     $sql .= ' ORDER BY i.created_at ASC'; break;
            case 'due_asc':    $sql .= ' ORDER BY i.due_date IS NULL, i.due_date ASC, i.created_at DESC'; break;
            case 'total_asc':  $sql .= ' ORDER BY i.grand_total ASC'; break;
            case 'total_desc': $sql .= ' ORDER BY i.grand_total DESC'; break;
            default:           $sql .= ' ORDER BY i.created_at DESC'; break;
        }

        if ($limit !== null) {
            $sql .= sprintf(' LIMIT %d OFFSET %d', max(1, $limit), max(0, $offset));
        }
        return DB::all($sql, $params);
    }

    public static function countForTenant(int $tenantId, array $filters = []): int
    {
        $sql = 'SELECT COUNT(*) FROM invoices i LEFT JOIN customers c ON c.customer_id = i.customer_id WHERE i.tenant_id = :t AND i.deleted_at IS NULL';
        $params = ['t' => $tenantId];
        if (!empty($filters['status']) && in_array($filters['status'], self::STATUSES, true)) {
            $sql .= ' AND i.status = :st';
            $params['st'] = $filters['status'];
        }
        if (!empty($filters['customer_id'])) {
            $sql .= ' AND i.customer_id = :cid';
            $params['cid'] = (int) $filters['customer_id'];
        }
        if (!empty($filters['q'])) {
            $sql .= ' AND (i.invoice_number LIKE :q1 OR c.name LIKE :q2)';
            $q = '%' . $filters['q'] . '%';
            $params['q1'] = $q;
            $params['q2'] = $q;
        }
        if (!empty($filters['overdue'])) {
            $sql .= ' AND i.due_date < CURDATE() AND i.balance_due > 0';
        }
        return (int) DB::value($sql, $params);
    }

    public static function findInTenant(int $tenantId, int $id): ?array
    {
        return DB::one(
            'SELECT i.*, c.name AS customer_name, c.contact_person AS customer_contact,
                    c.email AS customer_email, c.phone AS customer_phone, c.address AS customer_address, c.tax_id AS customer_tax_id
               FROM invoices i
          LEFT JOIN customers c ON c.customer_id = i.customer_id
              WHERE i.tenant_id = :t AND i.invoice_id = :id AND i.deleted_at IS NULL',
            ['t' => $tenantId, 'id' => $id]
        );
    }

    public static function itemsFor(int $invoiceId): array
    {
        return DB::all(
            'SELECT ii.*, ps.name AS item_name, ps.type AS item_type, ps.unit AS item_unit
               FROM invoice_items ii
          LEFT JOIN products_services ps ON ps.item_id = ii.item_id
              WHERE ii.invoice_id = :iid
              ORDER BY ii.sort_order, ii.invoice_item_id',
            ['iid' => $invoiceId]
        );
    }

    public static function paymentsFor(int $invoiceId): array
    {
        return DB::all(
            'SELECT p.*, u.username AS created_by_name
               FROM payments p
          LEFT JOIN users u ON u.user_id = p.created_by
              WHERE p.invoice_id = :iid
              ORDER BY p.payment_date DESC, p.payment_id DESC',
            ['iid' => $invoiceId]
        );
    }

    public static function create(int $tenantId, array $data): int
    {
        $grandTotal = !empty($data['grand_total']) ? (float) $data['grand_total'] : 0.00;
        $payload = [
            'tenant_id'        => $tenantId,
            'quote_id'         => !empty($data['quote_id']) ? (int) $data['quote_id'] : null,
            'customer_id'      => (int) $data['customer_id'],
            'invoice_number'   => self::generateNumber($tenantId),
            'status'           => in_array($data['status'] ?? '', self::STATUSES, true) ? $data['status'] : 'draft',
            'subtotal'         => !empty($data['subtotal']) ? (float) $data['subtotal'] : 0.00,
            'discount_percent' => !empty($data['discount_percent']) ? (float) $data['discount_percent'] : 0.00,
            'discount_amount'  => !empty($data['discount_amount']) ? (float) $data['discount_amount'] : 0.00,
            'tax_total'        => !empty($data['tax_total']) ? (float) $data['tax_total'] : 0.00,
            'grand_total'      => $grandTotal,
            'amount_paid'      => 0.00,
            'balance_due'      => $grandTotal,
            'due_date'         => !empty($data['due_date']) ? $data['due_date'] : null,
            'notes'            => $data['notes'] ?? null,
            'terms'            => $data['terms'] ?? null,
            'created_by'       => !empty($data['created_by']) ? (int) $data['created_by'] : null,
        ];
        return (int) DB::insert('invoices', $payload);
    }

    public static function update(int $tenantId, int $id, array $data): void
    {
        $allowed = ['customer_id', 'status', 'subtotal', 'discount_percent', 'discount_amount', 'tax_total', 'grand_total', 'due_date', 'notes', 'terms'];
        $set = [];
        $params = ['t' => $tenantId, 'id' => $id];
        foreach ($allowed as $col) {
            if (!array_key_exists($col, $data)) continue;
            $val = $data[$col];
            if ($col === 'status' && !in_array($val, self::STATUSES, true)) continue;
            if (in_array($col, ['customer_id'], true)) {
                $val = !empty($val) ? (int) $val : null;
            }
            if (in_array($col, ['subtotal', 'discount_percent', 'discount_amount', 'tax_total', 'grand_total'], true)) {
                $val = ($val === '' || $val === null) ? 0.00 : (float) $val;
            }
            $set[] = "`{$col}` = :{$col}";
            $params[$col] = $val;
        }
        if (!$set) return;
        DB::run(
            'UPDATE invoices SET ' . implode(', ', $set) . ' WHERE tenant_id = :t AND invoice_id = :id AND deleted_at IS NULL',
            $params
        );
    }

    public static function addItem(int $invoiceId, array $data): int
    {
        $payload = [
            'invoice_id'       => $invoiceId,
            'item_id'          => !empty($data['item_id']) ? (int) $data['item_id'] : null,
            'description'      => $data['description'],
            'quantity'         => !empty($data['quantity']) ? (float) $data['quantity'] : 1.00,
            'unit_price'       => !empty($data['unit_price']) ? (float) $data['unit_price'] : 0.00,
            'discount_percent' => !empty($data['discount_percent']) ? (float) $data['discount_percent'] : 0.00,
            'line_total'       => !empty($data['line_total']) ? (float) $data['line_total'] : 0.00,
            'sort_order'       => (int) ($data['sort_order'] ?? 0),
        ];
        return (int) DB::insert('invoice_items', $payload);
    }

    public static function removeItem(int $tenantId, int $invoiceId, int $itemId): void
    {
        DB::run(
            'DELETE ii FROM invoice_items ii JOIN invoices i ON i.invoice_id = ii.invoice_id WHERE i.tenant_id = :t AND i.invoice_id = :iid AND ii.invoice_item_id = :itid',
            ['t' => $tenantId, 'iid' => $invoiceId, 'itid' => $itemId]
        );
    }

    public static function deleteAllItems(int $invoiceId): void
    {
        DB::run('DELETE FROM invoice_items WHERE invoice_id = :iid', ['iid' => $invoiceId]);
    }

    /**
     * Record a payment against an invoice. Updates the invoice's amount_paid,
     * balance_due, and status automatically.
     */
    public static function addPayment(int $tenantId, int $invoiceId, array $data, int $userId): int
    {
        $paymentMethod = in_array($data['payment_method'] ?? '', self::PAYMENT_METHODS, true)
            ? $data['payment_method'] : 'bank_transfer';
        $paymentId = (int) DB::insert('payments', [
            'tenant_id'        => $tenantId,
            'invoice_id'       => $invoiceId,
            'amount'           => (float) $data['amount'],
            'payment_method'   => $paymentMethod,
            'reference_number' => $data['reference_number'] ?? null,
            'payment_date'     => $data['payment_date'] ?? date('Y-m-d'),
            'notes'            => $data['notes'] ?? null,
            'created_by'       => $userId > 0 ? $userId : null,
        ]);

        self::recalculateBalance($invoiceId);

        return $paymentId;
    }

    /**
     * Recalculate amount_paid and balance_due from the payments table,
     * then update the invoice status accordingly.
     */
    public static function recalculateBalance(int $invoiceId): void
    {
        $totals = DB::one(
            'SELECT i.grand_total, COALESCE(SUM(p.amount), 0) AS total_paid
               FROM invoices i
          LEFT JOIN payments p ON p.invoice_id = i.invoice_id
              WHERE i.invoice_id = :id
              GROUP BY i.invoice_id',
            ['id' => $invoiceId]
        );
        if (!$totals) return;

        $grandTotal = (float) $totals['grand_total'];
        $totalPaid  = (float) $totals['total_paid'];
        $balance    = round($grandTotal - $totalPaid, 2);
        if ($balance < 0) $balance = 0.00;

        // Determine status based on payment
        $status = 'sent';
        if ($balance <= 0) {
            $status = 'paid';
        } elseif ($totalPaid > 0) {
            $status = 'partial';
        }

        DB::run(
            'UPDATE invoices SET amount_paid = :ap, balance_due = :bd, status = :st WHERE invoice_id = :id',
            ['ap' => $totalPaid, 'bd' => $balance, 'st' => $status, 'id' => $invoiceId]
        );
    }

    /**
     * Check for overdue invoices and update their status.
     * Can be called from a cron job or dashboard.
     */
    public static function markOverdue(int $tenantId): int
    {
        return DB::run(
            "UPDATE invoices SET status = 'overdue' WHERE tenant_id = :t AND due_date < CURDATE() AND balance_due > 0 AND status IN ('sent', 'partial') AND deleted_at IS NULL",
            ['t' => $tenantId]
        )->rowCount();
    }

    public static function softDelete(int $tenantId, int $id): void
    {
        DB::run(
            'UPDATE invoices SET deleted_at = NOW() WHERE tenant_id = :t AND invoice_id = :id AND deleted_at IS NULL',
            ['t' => $tenantId, 'id' => $id]
        );
    }

    public static function countsForTenant(int $tenantId): array
    {
        return DB::one(
            'SELECT COUNT(*) AS total,
                    SUM(status IN ("sent","partial","draft")) AS open_count,
                    SUM(status = "paid") AS paid,
                    SUM(status = "overdue") AS overdue,
                    COALESCE(SUM(balance_due), 0) AS total_outstanding
               FROM invoices
              WHERE tenant_id = :t AND deleted_at IS NULL',
            ['t' => $tenantId]
        ) ?? ['total' => 0, 'open_count' => 0, 'paid' => 0, 'overdue' => 0, 'total_outstanding' => 0];
    }
}