<?php

declare(strict_types=1);

namespace App\Models;

use App\Services\NumberingService;
use App\Services\SubscriptionUsageService;

final class Invoice extends BaseModel
{
    protected string $table = 'invoices';

    public function all(int $companyId, string $search = '', string $status = ''): array
    {
        $where = ['i.company_id = :company_id'];
        $params = ['company_id' => $companyId];
        if ($search !== '') {
            $where[] = '(i.invoice_number LIKE :search_number OR i.title LIKE :search_title OR c.name LIKE :search_client OR c.company_name LIKE :search_company)';
            $like = '%' . $search . '%';
            $params += ['search_number' => $like, 'search_title' => $like, 'search_client' => $like, 'search_company' => $like];
        }
        if ($status !== '') {
            $where[] = 'i.status = :status';
            $params['status'] = $status;
        }
        return $this->fetchAll(
            'SELECT i.*, c.name AS client_name, c.company_name FROM invoices i LEFT JOIN clients c ON c.id = i.client_id WHERE ' . implode(' AND ', $where) . ' ORDER BY i.id DESC LIMIT 200',
            $params
        );
    }

    public function findWithItems(int $companyId, int $id): ?array
    {
        $invoice = $this->fetchOne(
            'SELECT i.*, c.name AS client_name, c.email AS client_email, c.phone AS client_phone, c.company_name, c.address AS client_address,
                    co.name AS company_name_display, co.email AS company_email, co.phone AS company_phone,
                    co.address AS company_address, co.currency, co.tax_number AS company_tax_number,
                    co.letterhead_pdf_path, co.stamp_image_path
             FROM invoices i
             LEFT JOIN clients c ON c.id = i.client_id
             LEFT JOIN companies co ON co.id = i.company_id
             WHERE i.company_id = :company_id AND i.id = :id',
            ['company_id' => $companyId, 'id' => $id]
        );
        if (! $invoice) {
            return null;
        }
        $invoice['items'] = $this->fetchAll('SELECT * FROM invoice_items WHERE invoice_id = :id ORDER BY id ASC', ['id' => $id]);
        $invoice['payments'] = $this->fetchAll('SELECT * FROM payments WHERE invoice_id = :id ORDER BY id DESC', ['id' => $id]);
        $invoice['milestones'] = (new InvoiceMilestone())->forInvoice($id);
        $invoice['payment_proofs'] = (new PaymentProof())->forInvoice($id);
        return $invoice;
    }

    public function findByToken(string $token): ?array
    {
        $invoice = $this->fetchOne(
            'SELECT i.*, c.name AS client_name, c.email AS client_email, c.phone AS client_phone, c.company_name, c.address AS client_address,
                    co.name AS company_name_display, co.email AS company_email, co.phone AS company_phone,
                    co.address AS company_address, co.currency, co.tax_number AS company_tax_number,
                    co.letterhead_pdf_path, co.stamp_image_path
             FROM invoices i
             LEFT JOIN clients c ON c.id = i.client_id
             LEFT JOIN companies co ON co.id = i.company_id
             WHERE i.public_token = :token
             LIMIT 1',
            ['token' => $token]
        );

        if (! $invoice) {
            return null;
        }

        $invoice['items'] = $this->fetchAll('SELECT * FROM invoice_items WHERE invoice_id = :id ORDER BY id ASC', ['id' => (int) $invoice['id']]);
        $invoice['payments'] = $this->fetchAll('SELECT * FROM payments WHERE invoice_id = :id ORDER BY id DESC', ['id' => (int) $invoice['id']]);
        $invoice['milestones'] = (new InvoiceMilestone())->forInvoice((int) $invoice['id']);
        $invoice['payment_proofs'] = (new PaymentProof())->forInvoice((int) $invoice['id']);
        return $invoice;
    }

    public function allForClient(int $companyId, int $clientId): array
    {
        return $this->fetchAll(
            'SELECT i.*, c.name AS client_name, c.company_name
             FROM invoices i
             LEFT JOIN clients c ON c.id = i.client_id
             WHERE i.company_id = :company_id AND i.client_id = :client_id
             ORDER BY i.id DESC
             LIMIT 100',
            ['company_id' => $companyId, 'client_id' => $clientId]
        );
    }

    public function markSent(int $companyId, int $id): bool
    {
        return $this->execute(
            'UPDATE invoices SET status = IF(status = "draft", "sent", status), updated_at = NOW()
             WHERE company_id = :company_id AND id = :id',
            ['company_id' => $companyId, 'id' => $id]
        );
    }

    public function dueForReminder(int $daysAhead = 3): array
    {
        $daysAhead = max(0, min(30, $daysAhead));

        return $this->fetchAll(
            "SELECT id, company_id
             FROM invoices
             WHERE balance_due > 0
               AND status IN (\"sent\", \"viewed\", \"partially_paid\", \"overdue\")
               AND due_date IS NOT NULL
               AND due_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL {$daysAhead} DAY)
             ORDER BY due_date ASC
             LIMIT 200"
        );
    }

    public function overdueForReminder(): array
    {
        return $this->fetchAll(
            'SELECT id, company_id
             FROM invoices
             WHERE balance_due > 0
               AND status IN ("sent", "viewed", "partially_paid", "overdue")
               AND due_date IS NOT NULL
               AND due_date < CURDATE()
             ORDER BY due_date ASC
             LIMIT 200'
        );
    }

    public function paymentsForClient(int $companyId, int $clientId): array
    {
        return $this->fetchAll(
            'SELECT p.*, i.invoice_number, i.public_token AS invoice_public_token
             FROM payments p
             INNER JOIN invoices i ON i.id = p.invoice_id
             WHERE p.company_id = :company_id AND i.client_id = :client_id
             ORDER BY p.paid_at DESC, p.id DESC
             LIMIT 100',
            ['company_id' => $companyId, 'client_id' => $clientId]
        );
    }

    public function findByQuotation(int $companyId, int $quotationId): ?array
    {
        return $this->fetchOne(
            'SELECT id FROM invoices WHERE company_id = :company_id AND quotation_id = :quotation_id LIMIT 1',
            ['company_id' => $companyId, 'quotation_id' => $quotationId]
        );
    }

    public function createFromQuotation(int $companyId, int $quotationId): int
    {
        $existing = $this->fetchOne(
            'SELECT id FROM invoices WHERE company_id = :company_id AND quotation_id = :quotation_id LIMIT 1',
            ['company_id' => $companyId, 'quotation_id' => $quotationId]
        );

        if ($existing) {
            return (int) $existing['id'];
        }

        $usage = new SubscriptionUsageService();
        if (! $usage->canCreate($companyId, 'invoices')) {
            throw new \RuntimeException($usage->limitMessage($companyId, 'invoices'));
        }

        $quotationModel = new Quotation();
        $quotation = $quotationModel->findWithItems($companyId, $quotationId);
        if (! $quotation) {
            throw new \RuntimeException('Quotation not found.');
        }

        if (($quotation['status'] ?? '') === 'rejected') {
            throw new \RuntimeException('Rejected quotations cannot be converted.');
        }

        $this->db->beginTransaction();
        try {
            $number = (new NumberingService())->nextNumber($companyId, 'invoice');
            $id = $this->insert(
                'INSERT INTO invoices (company_id, quotation_id, client_id, invoice_number, title, issue_date, due_date, subtotal, tax_total, discount_amount, deposit_amount, total, paid_amount, balance_due, payment_request_amount, status, notes, terms, public_token, created_at)
                 VALUES (:company_id, :quotation_id, :client_id, :invoice_number, :title, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 14 DAY), :subtotal, :tax_total, :discount_amount, :deposit_amount, :total, 0, :balance_due, :payment_request_amount, :status, :notes, :terms, :public_token, NOW())',
                [
                    'company_id' => $companyId,
                    'quotation_id' => $quotationId,
                    'client_id' => $quotation['client_id'],
                    'invoice_number' => $number,
                    'title' => $quotation['title'],
                    'subtotal' => $quotation['subtotal'],
                    'tax_total' => $quotation['tax_total'],
                    'discount_amount' => $quotation['discount_amount'],
                    'deposit_amount' => $quotation['deposit_amount'] ?? 0,
                    'total' => $quotation['total'],
                    'balance_due' => $quotation['total'],
                    'payment_request_amount' => $quotation['deposit_amount'] ?? 0,
                    'status' => 'sent',
                    'notes' => $quotation['notes'],
                    'terms' => $quotation['terms'],
                    'public_token' => bin2hex(random_bytes(24)),
                ]
            );
            foreach ($quotation['items'] as $item) {
                $this->insert(
                    'INSERT INTO invoice_items (invoice_id, product_id, description, quantity, unit_price, tax_rate, tax_amount, line_total, created_at)
                     VALUES (:invoice_id, :product_id, :description, :quantity, :unit_price, :tax_rate, :tax_amount, :line_total, NOW())',
                    [
                        'invoice_id' => $id,
                        'product_id' => $item['product_id'],
                        'description' => $item['description'],
                        'quantity' => $item['quantity'],
                        'unit_price' => $item['unit_price'],
                        'tax_rate' => $item['tax_rate'],
                        'tax_amount' => $item['tax_amount'],
                        'line_total' => $item['line_total'],
                    ]
                );
            }
            $quotationModel->updateStatus($companyId, $quotationId, 'converted');
            $this->db->commit();
            return $id;
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function applyPayment(int $companyId, int $invoiceId, float $amount): void
    {
        $invoice = $this->fetchOne('SELECT total, paid_amount FROM invoices WHERE company_id = :company_id AND id = :id', ['company_id' => $companyId, 'id' => $invoiceId]);
        if (! $invoice) {
            throw new \RuntimeException('Invoice not found.');
        }
        $paid = (float) $invoice['paid_amount'] + $amount;
        $balance = max(0, (float) $invoice['total'] - $paid);
        $status = $balance <= 0 ? 'paid' : 'partially_paid';
        $this->execute(
            'UPDATE invoices SET paid_amount = :paid_amount, balance_due = :balance_due, status = :status, updated_at = NOW() WHERE company_id = :company_id AND id = :id',
            ['paid_amount' => $paid, 'balance_due' => $balance, 'status' => $status, 'company_id' => $companyId, 'id' => $invoiceId]
        );
    }

    public function createFromSourceInvoice(int $companyId, int $sourceInvoiceId): int
    {
        $usage = new SubscriptionUsageService();
        if (! $usage->canCreate($companyId, 'invoices')) {
            throw new \RuntimeException($usage->limitMessage($companyId, 'invoices'));
        }

        $source = $this->findWithItems($companyId, $sourceInvoiceId);
        if (! $source) {
            throw new \RuntimeException('Source invoice not found.');
        }

        $this->db->beginTransaction();
        try {
            $number = (new NumberingService())->nextNumber($companyId, 'invoice');
            $id = $this->insert(
                'INSERT INTO invoices (company_id, quotation_id, client_id, invoice_number, title, issue_date, due_date, subtotal, tax_total, discount_amount, deposit_amount, total, paid_amount, balance_due, payment_request_amount, status, notes, terms, public_token, created_at)
                 VALUES (:company_id, NULL, :client_id, :invoice_number, :title, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 14 DAY), :subtotal, :tax_total, :discount_amount, :deposit_amount, :total, 0, :balance_due, :payment_request_amount, "sent", :notes, :terms, :public_token, NOW())',
                [
                    'company_id' => $companyId,
                    'client_id' => $source['client_id'],
                    'invoice_number' => $number,
                    'title' => $source['title'],
                    'subtotal' => $source['subtotal'],
                    'tax_total' => $source['tax_total'],
                    'discount_amount' => $source['discount_amount'],
                    'deposit_amount' => $source['deposit_amount'] ?? 0,
                    'total' => $source['total'],
                    'balance_due' => $source['total'],
                    'payment_request_amount' => $source['payment_request_amount'] ?? 0,
                    'notes' => $source['notes'],
                    'terms' => $source['terms'],
                    'public_token' => bin2hex(random_bytes(24)),
                ]
            );

            foreach ($source['items'] as $item) {
                $this->insert(
                    'INSERT INTO invoice_items (invoice_id, product_id, description, quantity, unit_price, tax_rate, tax_amount, line_total, created_at)
                     VALUES (:invoice_id, :product_id, :description, :quantity, :unit_price, :tax_rate, :tax_amount, :line_total, NOW())',
                    [
                        'invoice_id' => $id,
                        'product_id' => $item['product_id'],
                        'description' => $item['description'],
                        'quantity' => $item['quantity'],
                        'unit_price' => $item['unit_price'],
                        'tax_rate' => $item['tax_rate'],
                        'tax_amount' => $item['tax_amount'],
                        'line_total' => $item['line_total'],
                    ]
                );
            }

            $this->db->commit();
            return $id;
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }
}