<?php
namespace Modules\Cotacoes\Controllers;

use Gestoreasy\Core\Http\Response;
use Gestoreasy\Core\Tenant\ConnectionManager;
use Gestoreasy\Core\Audit\AuditLogger;
use Gestoreasy\Core\Mail\Mailer;
use Exception;

class PurchaseQuotesController
{
    public function index($request)
    {
        $db = ConnectionManager::forTenant();
        $page = (int)($request->query['page'] ?? 1);
        $perPage = (int)($request->query['per_page'] ?? 20);
        $offset = ($page - 1) * $perPage;
        $supplierId = $request->query['supplier_id'] ?? null;
        $status = $request->query['status'] ?? null;

        $where = [];
        $params = [];
        if ($supplierId) { $where[] = 'supplier_id = :supplier_id'; $params['supplier_id'] = $supplierId; }
        if ($status) { $where[] = 'status = :status'; $params['status'] = $status; }
        $whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';

        // Enriquecer com razão social do fornecedor
        $sql = "SELECT pq.*, s.nome AS supplier_name FROM purchase_quotes pq LEFT JOIN suppliers s ON s.id = pq.supplier_id $whereSql ORDER BY pq.created_at DESC LIMIT :limit OFFSET :offset";
        $stmt = $db->prepare($sql);
        foreach ($params as $k => $v) { $stmt->bindValue(':' . $k, $v); }
        $stmt->bindValue(':limit', $perPage, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
        $stmt->execute();
        $quotes = $stmt->fetchAll();

        $countSql = "SELECT COUNT(*) as total FROM purchase_quotes $whereSql";
        $countStmt = $db->prepare($countSql);
        foreach ($params as $k => $v) { $countStmt->bindValue(':' . $k, $v); }
        $countStmt->execute();
        $total = (int)$countStmt->fetch()['total'];

        return Response::json(['data' => $quotes, 'page' => $page, 'per_page' => $perPage, 'total' => $total]);
    }

    public function show($request)
    {
        $db = ConnectionManager::forTenant();
        $params = $request->get('params', []);
        $id = (int)($params[0] ?? 0);
        $stmt = $db->prepare('SELECT * FROM purchase_quotes WHERE id = :id');
        $stmt->execute(['id' => $id]);
        $quote = $stmt->fetch();
        if (!$quote) { return Response::json(['error' => 'Cotação não encontrada'], 404); }

        $itemsStmt = $db->prepare('SELECT * FROM purchase_quote_items WHERE quote_id = :id');
        $itemsStmt->execute(['id' => $id]);
        $items = $itemsStmt->fetchAll();
        $quote['items'] = $items;

        return Response::json(['data' => $quote]);
    }

    public function store($request)
    {
        $db = ConnectionManager::forTenant();
        $payload = $request->body;

        // Basic validations
        $supplierId = $payload['supplier_id'] ?? null;
        $validUntil = $payload['valid_until'] ?? null;
        $items = $payload['items'] ?? [];
        if (!$supplierId || !is_array($items) || count($items) === 0) {
            return Response::json(['error' => 'Fornecedor e itens são obrigatórios'], 422);
        }

        // Validate supplier
        $stmt = $db->prepare('SELECT id FROM suppliers WHERE id = :id');
        $stmt->execute(['id' => $supplierId]);
        if (!$stmt->fetch()) { return Response::json(['error' => 'Fornecedor inválido'], 422); }

        // Validate items (product existence and positive price)
        $productStmt = $db->prepare('SELECT id FROM products WHERE id = :id');
        foreach ($items as $item) {
            if (!isset($item['product_id'], $item['price']) || $item['price'] <= 0) {
                return Response::json(['error' => 'Itens devem conter produto e preço válido'], 422);
            }
            $productStmt->execute(['id' => $item['product_id']]);
            if (!$productStmt->fetch()) { return Response::json(['error' => 'Produto inválido'], 422); }
        }

        // Ensure optional columns exist (best-effort)
        try { $db->query("ALTER TABLE purchase_quotes ADD COLUMN metadata JSON NULL"); } catch (\Throwable $e) {}
        try { $db->query("ALTER TABLE purchase_quotes ADD COLUMN empresa_id INT NULL"); } catch (\Throwable $e) {}
        try { $db->query("ALTER TABLE purchase_quotes ADD COLUMN warehouse_id INT NULL"); } catch (\Throwable $e) {}

        // Insert quote
        $db->beginTransaction();
        try {
            $insert = $db->prepare('INSERT INTO purchase_quotes (supplier_id, valid_until, status, metadata, empresa_id, warehouse_id) VALUES (:supplier_id, :valid_until, :status, :metadata, :empresa_id, :warehouse_id)');
            $status = $payload['status'] ?? 'open';
            $metadata = null;
            if (isset($payload['metadata'])) {
                try { $metadata = is_string($payload['metadata']) ? $payload['metadata'] : json_encode($payload['metadata'], JSON_UNESCAPED_UNICODE); } catch (\Throwable $e) { $metadata = null; }
            }
            $empresaId = isset($payload['empresa_id']) ? (int)$payload['empresa_id'] : null;
            $warehouseId = isset($payload['warehouse_id']) ? (int)$payload['warehouse_id'] : null;
            $insert->execute([
                'supplier_id' => $supplierId,
                'valid_until' => $validUntil,
                'status' => $status,
                'metadata' => $metadata,
                'empresa_id' => $empresaId,
                'warehouse_id' => $warehouseId
            ]);
            $quoteId = (int)$db->lastInsertId();

            $itemInsert = $db->prepare('INSERT INTO purchase_quote_items (quote_id, product_id, quantity, price) VALUES (:quote_id, :product_id, :quantity, :price)');
            foreach ($items as $item) {
                $quantity = (float)($item['quantity'] ?? 1);
                $itemInsert->execute([
                    'quote_id' => $quoteId,
                    'product_id' => $item['product_id'],
                    'quantity' => $quantity,
                    'price' => $item['price'],
                ]);
            }

            AuditLogger::logCreate('purchase_quotes', $quoteId, ['supplier_id' => $supplierId]);
            $db->commit();
            return Response::json(['data' => ['id' => $quoteId]], 201);
        } catch (Exception $e) {
            $db->rollBack();
            return Response::json(['error' => 'Falha ao criar cotação', 'details' => $e->getMessage()], 500);
        }
    }

    public function update($request)
    {
        $db = ConnectionManager::forTenant();
        $params = $request->get('params', []);
        $id = (int)($params[0] ?? 0);
        $payload = $request->body;

        $stmt = $db->prepare('SELECT * FROM purchase_quotes WHERE id = :id');
        $stmt->execute(['id' => $id]);
        $quote = $stmt->fetch();
        if (!$quote) { return Response::json(['error' => 'Cotação não encontrada'], 404); }

        $fields = [];
        $params = ['id' => $id];
        // Ensure optional columns exist (best-effort)
        try { $db->query("ALTER TABLE purchase_quotes ADD COLUMN metadata JSON NULL"); } catch (\Throwable $e) {}
        try { $db->query("ALTER TABLE purchase_quotes ADD COLUMN empresa_id INT NULL"); } catch (\Throwable $e) {}
        try { $db->query("ALTER TABLE purchase_quotes ADD COLUMN warehouse_id INT NULL"); } catch (\Throwable $e) {}
        foreach (['supplier_id', 'valid_until', 'status', 'metadata', 'empresa_id', 'warehouse_id'] as $f) {
            if (isset($payload[$f])) { $fields[] = "$f = :$f"; $params[$f] = $payload[$f]; }
        }
        if ($fields) {
            $sql = 'UPDATE purchase_quotes SET ' . implode(', ', $fields) . ' WHERE id = :id';
            $upd = $db->prepare($sql);
            $upd->execute($params);
        }

        if (isset($payload['items']) && is_array($payload['items'])) {
            $db->beginTransaction();
            try {
                $db->prepare('DELETE FROM purchase_quote_items WHERE quote_id = :id')->execute(['id' => $id]);
                $itemInsert = $db->prepare('INSERT INTO purchase_quote_items (quote_id, product_id, quantity, price) VALUES (:quote_id, :product_id, :quantity, :price)');
                foreach ($payload['items'] as $item) {
                    if (!isset($item['product_id'], $item['price'])) { continue; }
                    $itemInsert->execute([
                        'quote_id' => $id,
                        'product_id' => $item['product_id'],
                        'quantity' => (float)($item['quantity'] ?? 1),
                        'price' => $item['price'],
                    ]);
                }
                $db->commit();
            } catch (Exception $e) {
                $db->rollBack();
                return Response::json(['error' => 'Falha ao atualizar itens da cotação', 'details' => $e->getMessage()], 500);
            }
        }

        AuditLogger::log('update', 'purchase_quotes', $id, $quote, $params);
        return Response::json(['data' => ['id' => $id]]);
    }

    public function destroy($request)
    {
        $db = ConnectionManager::forTenant();
        $params = $request->get('params', []);
        $id = (int)($params[0] ?? 0);
        $stmt = $db->prepare('SELECT id FROM purchase_quotes WHERE id = :id');
        $stmt->execute(['id' => $id]);
        if (!$stmt->fetch()) { return Response::json(['error' => 'Cotação não encontrada'], 404); }

        $db->beginTransaction();
        try {
            $db->prepare('DELETE FROM purchase_quote_items WHERE quote_id = :id')->execute(['id' => $id]);
            $db->prepare('DELETE FROM purchase_quotes WHERE id = :id')->execute(['id' => $id]);
            AuditLogger::logDelete('purchase_quotes', $id, ['id' => $id]);
            $db->commit();
            return Response::json(['data' => true]);
        } catch (Exception $e) {
            $db->rollBack();
            return Response::json(['error' => 'Falha ao excluir cotação', 'details' => $e->getMessage()], 500);
        }
    }

    public function bestOffers($request)
    {
        $db = ConnectionManager::forTenant();
        $productIds = $request->query['product_ids'] ?? '';
        if (!$productIds) {
            return Response::json(['error' => 'Informe product_ids separados por vírgula'], 422);
        }
        $ids = array_filter(array_map('intval', explode(',', $productIds)));
        if (count($ids) === 0) { return Response::json(['error' => 'Nenhum produto válido'], 422); }

        // Find best price per product among open/valid quotes
        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        $sql = "SELECT pqi.product_id, pq.supplier_id, MIN(pqi.price) as best_price
                FROM purchase_quote_items pqi
                INNER JOIN purchase_quotes pq ON pq.id = pqi.quote_id
                WHERE pqi.product_id IN ($placeholders) AND (pq.status = 'open' OR pq.status = 'approved')
                GROUP BY pqi.product_id, pq.supplier_id";
        $stmt = $db->prepare($sql);
        $stmt->execute($ids);
        $rows = $stmt->fetchAll();

        // Aggregate best supplier per product
        $best = [];
        foreach ($rows as $r) {
            $pid = (int)$r['product_id'];
            if (!isset($best[$pid]) || $r['best_price'] < $best[$pid]['best_price']) {
                $best[$pid] = [
                    'product_id' => $pid,
                    'supplier_id' => (int)$r['supplier_id'],
                    'best_price' => (float)$r['best_price'],
                ];
            }
        }

        return Response::json(['data' => array_values($best)]);
    }

    public function sendInvite($request)
    {
        $db = ConnectionManager::forTenant();
        $params = $request->get('params', []);
        $id = (int)($params[0] ?? 0);
        // Buscar cotação
        $stmt = $db->prepare('SELECT id, supplier_id, valid_until, status FROM purchase_quotes WHERE id = :id LIMIT 1');
        $stmt->execute(['id' => $id]);
        $quote = $stmt->fetch();
        if (!$quote) { return Response::json(['error' => 'Cotação não encontrada'], 404); }

        // Definir expiração padrão do link/cotação se ausente (7 dias)
        $validUntil = (string)($quote['valid_until'] ?? '');
        if ($validUntil === '' || $validUntil === null) {
            $defaultExpiry = date('Y-m-d', strtotime('+7 days'));
            $db->prepare('UPDATE purchase_quotes SET valid_until = :vu WHERE id = :id')->execute(['vu' => $defaultExpiry, 'id' => (int)$quote['id']]);
            $quote['valid_until'] = $defaultExpiry;
            AuditLogger::log('quote_set_default_expiry', 'purchase_quotes', (int)$quote['id'], null, null, ['valid_until' => $defaultExpiry]);
        }

        // Permitir especificar fornecedor no corpo da requisição
        $supplierOverrideId = null;
        try { $supplierOverrideId = isset($request->body['supplier_id']) ? (int)$request->body['supplier_id'] : null; } catch (\Throwable $e) { $supplierOverrideId = null; }
        $targetSupplierId = $supplierOverrideId ?: (int)$quote['supplier_id'];

        // Buscar fornecedor (email)
        $s = $db->prepare('SELECT id, nome, email FROM suppliers WHERE id = :id LIMIT 1');
        $s->execute(['id' => $targetSupplierId]);
        $sup = $s->fetch();
        if (!$sup || empty($sup['email'])) { return Response::json(['error' => 'Fornecedor sem email'], 422); }

        // Gerar token único e registrar convite
        $token = bin2hex(random_bytes(16));
        $ins = $db->prepare('INSERT INTO purchase_quote_invites (quote_id, supplier_id, email, token, status) VALUES (:q, :s, :e, :t, :st)');
        $ins->execute(['q' => $id, 's' => (int)$sup['id'], 'e' => (string)$sup['email'], 't' => $token, 'st' => 'sent']);

        $tenantParam = \Gestoreasy\Core\Tenant\TenantContext::getTenantId();
        $link = (isset($_SERVER['HTTP_HOST']) ? ( (isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on') ? 'https://' : 'http://' ) . $_SERVER['HTTP_HOST'] : 'http://localhost:8000')
            . '/cotacoes/responder?token=' . urlencode($token) . '&tenant=' . urlencode($tenantParam);
        $subject = 'Convite para responder cotação #' . $id;
        $body = "Olá,\n\nVocê foi convidado a responder a cotação #$id.\nAcesse o link para informar seus preços: $link\n\nAtenciosamente,\nGestorEasy";
        Mailer::send((string)$sup['email'], $subject, $body);

        AuditLogger::log('invite_send', 'purchase_quotes', $id, null, null, ['supplier_id' => (int)$sup['id'], 'valid_until' => (string)$quote['valid_until']]);
        return Response::json(['ok' => true, 'data' => ['token' => $token, 'link' => $link, 'valid_until' => (string)$quote['valid_until']]]);
    }

    public function getInviteByToken($request)
    {
        $db = ConnectionManager::forTenant();
        $token = (string)($request->query['token'] ?? '');
        if ($token === '') { return Response::json(['error' => 'token_required'], 422); }
        $inv = $db->prepare('SELECT id, quote_id, supplier_id, email, status, created_at, opened_at, responded_at FROM purchase_quote_invites WHERE token = :t LIMIT 1');
        $inv->execute(['t' => $token]);
        $invite = $inv->fetch();
        if (!$invite) { return Response::json(['error' => 'invalid_token'], 404); }
        // Marcar como aberto (best-effort)
        if ($invite['status'] === 'sent') {
            $db->prepare('UPDATE purchase_quote_invites SET status = "opened", opened_at = NOW() WHERE id = :id')->execute(['id' => (int)$invite['id']]);
            $invite['status'] = 'opened';
        }
        // Carregar cotação e itens
        $q = $db->prepare('SELECT id, supplier_id, valid_until, status, created_at FROM purchase_quotes WHERE id = :id LIMIT 1');
        $q->execute(['id' => (int)$invite['quote_id']]);
        $quote = $q->fetch();
        if (!$quote) { return Response::json(['error' => 'quote_not_found'], 404); }
        // Verificar expiração do link (usa valid_until da cotação)
        try {
            $vu = (string)($quote['valid_until'] ?? '');
            if ($vu !== '') {
                $expTs = strtotime($vu . ' 23:59:59');
                if ($expTs !== false && time() > $expTs) {
                    // Marcar convite como falhou se ainda não respondido
                    if ($invite['status'] !== 'responded') {
                        $db->prepare('UPDATE purchase_quote_invites SET status = "failed" WHERE id = :id')->execute(['id' => (int)$invite['id']]);
                        $invite['status'] = 'failed';
                    }
                    AuditLogger::log('invite_link_expired', 'purchase_quotes', (int)$quote['id'], null, null, ['invite_id' => (int)$invite['id'], 'valid_until' => $vu]);
                    return Response::json(['error' => 'link_expired', 'message' => 'Este link expirou. Solicite um novo convite ao emissor.', 'data' => ['valid_until' => $vu]], 410);
                }
            }
        } catch (\Throwable $e) { /* ignore */ }
        $it = $db->prepare('SELECT id, product_id, quantity FROM purchase_quote_items WHERE quote_id = :id ORDER BY id ASC');
        $it->execute(['id' => (int)$invite['quote_id']]);
        $items = $it->fetchAll();
        return Response::json(['data' => [ 'invite' => $invite, 'quote' => $quote, 'items' => $items ]]);
    }

    public function submitResponse($request)
    {
        $db = ConnectionManager::forTenant();
        $token = (string)($request->body['token'] ?? '');
        $offers = $request->body['offers'] ?? null; // [{item_id, price, quantity?}]
        if ($token === '' || !is_array($offers) || count($offers) === 0) { return Response::json(['error' => 'invalid_payload'], 422); }
        // Validar convite
        $inv = $db->prepare('SELECT id, quote_id, supplier_id, status FROM purchase_quote_invites WHERE token = :t LIMIT 1');
        $inv->execute(['t' => $token]);
        $invite = $inv->fetch();
        if (!$invite) { return Response::json(['error' => 'invalid_token'], 404); }
        // Validar itens
        $validItem = $db->prepare('SELECT id, product_id FROM purchase_quote_items WHERE id = :id AND quote_id = :qid LIMIT 1');
        $normalized = [];
        foreach ($offers as $o) {
            $iid = (int)($o['item_id'] ?? 0); $price = (float)($o['price'] ?? 0);
            if ($iid <= 0 || $price <= 0) { return Response::json(['error' => 'invalid_offer'], 422); }
            $validItem->execute(['id' => $iid, 'qid' => (int)$invite['quote_id']]);
            $item = $validItem->fetch();
            if (!$item) { return Response::json(['error' => 'item_not_found'], 404); }
            $normalized[] = ['item_id' => $iid, 'product_id' => (int)$item['product_id'], 'price' => $price, 'quantity' => (float)($o['quantity'] ?? 0)];
        }

        // Persistir resposta
        $db->beginTransaction();
        try {
            $payload = json_encode(['offers' => $normalized], JSON_UNESCAPED_UNICODE);
            $ins = $db->prepare('INSERT INTO purchase_quote_responses (invite_id, quote_id, supplier_id, response_json) VALUES (:i, :q, :s, :r)');
            $ins->execute(['i' => (int)$invite['id'], 'q' => (int)$invite['quote_id'], 's' => (int)$invite['supplier_id'], 'r' => $payload]);
            // Atualizar status do convite
            $db->prepare('UPDATE purchase_quote_invites SET status = "responded", responded_at = NOW() WHERE id = :id')->execute(['id' => (int)$invite['id']]);
            AuditLogger::log('invite_response', 'purchase_quotes', (int)$invite['quote_id'], null, null, ['invite_id' => (int)$invite['id']]);
            $db->commit();
            return Response::json(['ok' => true]);
        } catch (Exception $e) {
            $db->rollBack();
            return Response::json(['error' => 'response_save_failed', 'details' => $e->getMessage()], 500);
        }
    }

    public function listInvitesAndResponses($request)
    {
        $db = ConnectionManager::forTenant();
        $params = $request->get('params', []);
        $id = (int)($params[0] ?? 0);
        if ($id <= 0) { return Response::json(['error' => 'invalid_id'], 422); }
        // Obter validade da cotação
        $q = $db->prepare('SELECT id, valid_until FROM purchase_quotes WHERE id = :id LIMIT 1');
        $q->execute(['id' => $id]);
        $quote = $q->fetch();
        if (!$quote) { return Response::json(['error' => 'quote_not_found'], 404); }

        $inv = $db->prepare('SELECT id, supplier_id, email, status, created_at, opened_at, responded_at FROM purchase_quote_invites WHERE quote_id = :id ORDER BY id DESC');
        $inv->execute(['id' => $id]);
        $invites = $inv->fetchAll();
        // Atribuir status efetivo considerando expiração
        $vu = (string)($quote['valid_until'] ?? '');
        $isExpired = false;
        if ($vu !== '') {
            $ts = strtotime($vu . ' 23:59:59');
            $isExpired = ($ts !== false && time() > $ts);
        }
        foreach ($invites as &$row) {
            $row['effective_status'] = (string)($row['status'] ?? '');
            if ($isExpired && $row['effective_status'] !== 'responded') {
                $row['effective_status'] = 'failed';
            }
        }
        unset($row);

        // Garantir tabela de respostas existente (resiliência a migração parcial)
        try {
            $db->query('SELECT 1 FROM purchase_quote_responses LIMIT 1');
        } catch (\Throwable $e) {
            try {
                // Tentar criar com tipo JSON; se falhar, usar TEXT
                $db->exec('CREATE TABLE IF NOT EXISTS purchase_quote_responses (
                    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    invite_id BIGINT UNSIGNED NOT NULL,
                    quote_id BIGINT UNSIGNED NOT NULL,
                    supplier_id BIGINT UNSIGNED NOT NULL,
                    response_json JSON NOT NULL,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    INDEX (quote_id), INDEX (supplier_id), INDEX (invite_id)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');
            } catch (\Throwable $e2) {
                try {
                    $db->exec('CREATE TABLE IF NOT EXISTS purchase_quote_responses (
                        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                        invite_id BIGINT UNSIGNED NOT NULL,
                        quote_id BIGINT UNSIGNED NOT NULL,
                        supplier_id BIGINT UNSIGNED NOT NULL,
                        response_json TEXT NOT NULL,
                        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        INDEX (quote_id), INDEX (supplier_id), INDEX (invite_id)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');
                } catch (\Throwable $e3) { /* ignore */ }
            }
        }
        $resp = $db->prepare('SELECT id, invite_id, supplier_id, response_json, created_at FROM purchase_quote_responses WHERE quote_id = :id ORDER BY id DESC');
        $resp->execute(['id' => $id]);
        $responses = $resp->fetchAll();

        // Enriquecer com informações de fornecedor (nome/email) best-effort
        $supplierMap = [];
        try {
            $uniqueIds = [];
            foreach ($invites as $r) { $sid = (int)($r['supplier_id'] ?? 0); if ($sid>0) $uniqueIds[$sid] = true; }
            foreach ($responses as $r) { $sid = (int)($r['supplier_id'] ?? 0); if ($sid>0) $uniqueIds[$sid] = true; }
            if ($uniqueIds) {
                $placeholders = implode(',', array_fill(0, count($uniqueIds), '?'));
                $supStmt = $db->prepare('SELECT id, nome, email FROM suppliers WHERE id IN ('.$placeholders.')');
                $supStmt->execute(array_keys($uniqueIds));
                foreach ($supStmt->fetchAll() as $row) { $supplierMap[(int)$row['id']] = ['nome' => (string)($row['nome'] ?? ''), 'email' => (string)($row['email'] ?? '')]; }
            }
        } catch (\Throwable $e) {}

        return Response::json(['data' => ['invites' => $invites, 'responses' => $responses, 'suppliers' => $supplierMap, 'quote_valid_until' => (string)$vu]]);
    }
}
