<?php
declare(strict_types=1);

namespace App\Models;

use Core\DB;
use Core\Model;

final class Quote extends Model
{
    protected string $table = 'quotes';
    protected string $primaryKey = 'quote_id';

    public const STATUSES = ['draft', 'sent', 'accepted', 'declined', 'converted', 'expired'];

    private static function generateNumber(int $tenantId): string
    {
        $year = date('Y');
        $prefix = "QTE-{$year}-";
        $row = DB::one(
            "SELECT MAX(CAST(SUBSTRING(quote_number, LENGTH(:pf)+1) AS UNSIGNED)) AS nxt FROM quotes WHERE tenant_id = :t AND quote_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 q.*, c.name AS customer_name
                  FROM quotes q
             LEFT JOIN customers c ON c.customer_id = q.customer_id
                 WHERE q.tenant_id = :t AND q.deleted_at IS NULL';
        $params = ['t' => $tenantId];

        if (!empty($filters['status']) && in_array($filters['status'], self::STATUSES, true)) {
            $sql .= ' AND q.status = :st';
            $params['st'] = $filters['status'];
        }
        if (!empty($filters['customer_id'])) {
            $sql .= ' AND q.customer_id = :cid';
            $params['cid'] = (int) $filters['customer_id'];
        }
        if (!empty($filters['q'])) {
            $sql .= ' AND (q.quote_number LIKE :q1 OR c.name LIKE :q2)';
            $q = '%' . $filters['q'] . '%';
            $params['q1'] = $q;
            $params['q2'] = $q;
        }

        switch ($sort) {
            case 'oldest':    $sql .= ' ORDER BY q.created_at ASC'; break;
            case 'total_asc': $sql .= ' ORDER BY q.grand_total ASC'; break;
            case 'total_desc':$sql .= ' ORDER BY q.grand_total DESC'; break;
            default:          $sql .= ' ORDER BY q.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 quotes q LEFT JOIN customers c ON c.customer_id = q.customer_id WHERE q.tenant_id = :t AND q.deleted_at IS NULL';
        $params = ['t' => $tenantId];
        if (!empty($filters['status']) && in_array($filters['status'], self::STATUSES, true)) {
            $sql .= ' AND q.status = :st';
            $params['st'] = $filters['status'];
        }
        if (!empty($filters['customer_id'])) {
            $sql .= ' AND q.customer_id = :cid';
            $params['cid'] = (int) $filters['customer_id'];
        }
        if (!empty($filters['q'])) {
            $sql .= ' AND (q.quote_number LIKE :q1 OR c.name LIKE :q2)';
            $q = '%' . $filters['q'] . '%';
            $params['q1'] = $q;
            $params['q2'] = $q;
        }
        return (int) DB::value($sql, $params);
    }

    public static function findInTenant(int $tenantId, int $id): ?array
    {
        return DB::one(
            'SELECT q.*, 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 quotes q
          LEFT JOIN customers c ON c.customer_id = q.customer_id
              WHERE q.tenant_id = :t AND q.quote_id = :id AND q.deleted_at IS NULL',
            ['t' => $tenantId, 'id' => $id]
        );
    }

    public static function itemsFor(int $quoteId): array
    {
        return DB::all(
            'SELECT qi.*, ps.name AS item_name, ps.type AS item_type, ps.unit AS item_unit
               FROM quote_items qi
          LEFT JOIN products_services ps ON ps.item_id = qi.item_id
              WHERE qi.quote_id = :q
              ORDER BY qi.sort_order, qi.quote_item_id',
            ['q' => $quoteId]
        );
    }

    public static function create(int $tenantId, array $data): int
    {
        $payload = [
            'tenant_id'        => $tenantId,
            'quote_number'     => self::generateNumber($tenantId),
            'customer_id'      => (int) $data['customer_id'],
            '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'      => !empty($data['grand_total']) ? (float) $data['grand_total'] : 0.00,
            'valid_until'      => !empty($data['valid_until']) ? $data['valid_until'] : null,
            'notes'            => $data['notes'] ?? null,
            'terms'            => $data['terms'] ?? null,
            'created_by'       => !empty($data['created_by']) ? (int) $data['created_by'] : null,
        ];
        return (int) DB::insert('quotes', $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', 'valid_until', '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 quotes SET ' . implode(', ', $set) . ' WHERE tenant_id = :t AND quote_id = :id AND deleted_at IS NULL',
            $params
        );
    }

    public static function addItem(int $quoteId, array $data): int
    {
        $payload = [
            'quote_id'         => $quoteId,
            '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('quote_items', $payload);
    }

    public static function removeItem(int $tenantId, int $quoteId, int $itemId): void
    {
        DB::run(
            'DELETE qi FROM quote_items qi JOIN quotes q ON q.quote_id = qi.quote_id WHERE q.tenant_id = :t AND q.quote_id = :qid AND qi.quote_item_id = :iid',
            ['t' => $tenantId, 'qid' => $quoteId, 'iid' => $itemId]
        );
    }

    public static function deleteAllItems(int $quoteId): void
    {
        DB::run('DELETE FROM quote_items WHERE quote_id = :q', ['q' => $quoteId]);
    }

    /**
     * Convert a quote into an invoice. Copies all line items, updates
     * the quote status to 'converted', and returns the new invoice ID.
     */
    public static function convertToInvoice(int $tenantId, int $quoteId, int $userId): int
    {
        return DB::tx(function () use ($tenantId, $quoteId, $userId): int {
            $quote = self::findInTenant($tenantId, $quoteId);
            if (!$quote) {
                throw new \RuntimeException('Quote not found.');
            }
            if ($quote['status'] === 'converted') {
                throw new \RuntimeException('Quote already converted.');
            }

            $items = self::itemsFor($quoteId);

            // Create the invoice
            $invoiceId = Invoice::create($tenantId, [
                'quote_id'         => $quoteId,
                'customer_id'      => $quote['customer_id'],
                'subtotal'         => $quote['subtotal'],
                'discount_percent' => $quote['discount_percent'] ?? 0,
                'discount_amount'  => $quote['discount_amount'] ?? 0,
                'tax_total'        => $quote['tax_total'] ?? 0,
                'grand_total'      => $quote['grand_total'],
                'notes'            => $quote['notes'],
                'terms'            => $quote['terms'],
                'created_by'       => $userId,
                'status'           => 'draft',
            ]);

            // Copy line items
            foreach ($items as $item) {
                Invoice::addItem($invoiceId, [
                    'item_id'          => $item['item_id'],
                    'description'      => $item['description'],
                    'quantity'         => $item['quantity'],
                    'unit_price'       => $item['unit_price'],
                    'discount_percent' => $item['discount_percent'],
                    'line_total'       => $item['line_total'],
                    'sort_order'       => $item['sort_order'],
                ]);
            }

            // Mark quote as converted
            DB::run(
                'UPDATE quotes SET status = :st WHERE tenant_id = :t AND quote_id = :id',
                ['st' => 'converted', 't' => $tenantId, 'id' => $quoteId]
            );

            // Initial balance = grand total
            Invoice::recalculateBalance($invoiceId);

            return $invoiceId;
        });
    }

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