<?php

declare(strict_types=1);

namespace GalpaoImperial\Api;

use PDO;

final class SiteRepository
{
    private PDO $pdo;

    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    public function bootstrap(): array
    {
        $wallet = [
            'organizer' => [
                'balance' => 0.0,
                'availableForManualWithdraw' => 0.0,
                'autoWithdrawThreshold' => 200.0,
                'manualWithdrawFee' => 0.0,
                'autoWithdrawFee' => 0.0,
                'pixKey' => null,
                'pixKeyType' => 'CPF',
                'recipientName' => null,
                'transactions' => [],
            ],
            'advancedUnlocked' => false,
        ];

        try {
            $wallet = $this->walletOverview(false);
        } catch (\Throwable) {
            // Em hospedagens com banco antigo, o restante do painel precisa continuar carregando.
        }

        return [
            'settings' => $this->settings(),
            'wallet' => $wallet,
            'heroBanners' => $this->heroBanners(),
            'membershipPlans' => $this->membershipPlans(),
            'leisureActivities' => $this->leisureActivities(),
            'events' => $this->events(),
            'partners' => $this->partners(),
            'sales' => $this->sales(),
            'giftClaims' => $this->giftClaims(),
            'galleryImages' => $this->galleryImages(),
        ];
    }

    public function settings(): array
    {
        $this->ensureFinancialSchemaCompatibility();
        $row = $this->pdo->query('SELECT * FROM site_settings ORDER BY id ASC LIMIT 1')->fetch();

        if (!$row) {
            return [];
        }

        return [
            'name' => $row['site_name'],
            'logoUrl' => $row['logo_url'] ?? null,
            'whatsapp' => $row['whatsapp'],
            'instagram' => $row['instagram'],
            'email' => $row['contact_email'],
            'address' => trim(sprintf('%s, %s - %s', $row['address_line'], $row['city'], $row['state_code']), ', -'),
            'googleMapsLink' => $row['google_maps_link'],
            'wazeLink' => $row['waze_link'],
            'pixKey' => $row['pix_key'],
            'serviceTax' => (float) $row['service_tax'] > 0 ? (float) $row['service_tax'] : 2.0,
            'asaasFeePerOrder' => (float) ($row['asaas_fee_per_order'] ?? 0.99),
            'organizerPixKey' => $row['organizer_pix_key'] ?? null,
            'organizerPixKeyType' => $row['organizer_pix_key_type'] ?? 'CPF',
            'organizerRecipientName' => $row['organizer_recipient_name'] ?? null,
            'platformPixKey' => $row['platform_pix_key'] ?? null,
            'platformPixKeyType' => $row['platform_pix_key_type'] ?? 'CPF',
            'platformRecipientName' => $row['platform_recipient_name'] ?? null,
            'organizerAutoWithdrawThreshold' => (float) ($row['organizer_auto_withdraw_threshold'] ?? 200),
            'organizerManualWithdrawFee' => 0.0,
            'organizerAutoWithdrawFee' => 0.0,
            'confirmationMessage' => $row['confirmation_message'],
            'whatsappTemplate' => $row['whatsapp_template'],
            'asaasEnabled' => env('ASAAS_API_KEY') !== null && env('ASAAS_API_KEY') !== '',
            'asaasEnvironment' => env('ASAAS_ENVIRONMENT', $row['asaas_environment'] ?: 'sandbox'),
        ];
    }

    public function updateSettings(array $payload): array
    {
        $this->ensureFinancialSchemaCompatibility();
        $currentSettings = $this->settings();

        $baseParams = [
            'name' => $payload['name'] ?? '',
            'logoUrl' => $payload['logoUrl'] ?? null,
            'whatsapp' => $payload['whatsapp'] ?? '',
            'instagram' => $payload['instagram'] ?? null,
            'email' => $payload['email'] ?? null,
            'addressLine' => $payload['address'] ?? null,
            'googleMapsLink' => $payload['googleMapsLink'] ?? null,
            'wazeLink' => $payload['wazeLink'] ?? null,
            'pixKey' => $payload['pixKey'] ?? null,
            'serviceTax' => (float) ($currentSettings['serviceTax'] ?? 2.0),
            'confirmationMessage' => $payload['confirmationMessage'] ?? null,
            'whatsappTemplate' => $payload['whatsappTemplate'] ?? null,
        ];

        try {
            $stmt = $this->pdo->prepare(
                'UPDATE site_settings
                 SET site_name = :name,
                     logo_url = :logoUrl,
                     whatsapp = :whatsapp,
                     instagram = :instagram,
                     contact_email = :email,
                     address_line = :addressLine,
                     google_maps_link = :googleMapsLink,
                     waze_link = :wazeLink,
                     pix_key = :pixKey,
                     service_tax = :serviceTax,
                     asaas_fee_per_order = :asaasFeePerOrder,
                     organizer_pix_key = :organizerPixKey,
                     organizer_pix_key_type = :organizerPixKeyType,
                     organizer_recipient_name = :organizerRecipientName,
                     platform_pix_key = :platformPixKey,
                     platform_pix_key_type = :platformPixKeyType,
                     platform_recipient_name = :platformRecipientName,
                     organizer_auto_withdraw_threshold = :organizerAutoWithdrawThreshold,
                     organizer_manual_withdraw_fee = :organizerManualWithdrawFee,
                     organizer_auto_withdraw_fee = :organizerAutoWithdrawFee,
                     confirmation_message = :confirmationMessage,
                     whatsapp_template = :whatsappTemplate,
                     updated_at = NOW()
                 WHERE id = 1'
            );
            $stmt->execute($baseParams + [
                'asaasFeePerOrder' => (float) ($currentSettings['asaasFeePerOrder'] ?? 0.99),
                'organizerPixKey' => $payload['organizerPixKey'] ?? null,
                'organizerPixKeyType' => $payload['organizerPixKeyType'] ?? 'CPF',
                'organizerRecipientName' => $payload['organizerRecipientName'] ?? null,
                'platformPixKey' => $payload['platformPixKey'] ?? ($currentSettings['platformPixKey'] ?? null),
                'platformPixKeyType' => $payload['platformPixKeyType'] ?? ($currentSettings['platformPixKeyType'] ?? 'CPF'),
                'platformRecipientName' => $payload['platformRecipientName'] ?? ($currentSettings['platformRecipientName'] ?? null),
                'organizerAutoWithdrawThreshold' => (float) ($payload['organizerAutoWithdrawThreshold'] ?? 200),
                'organizerManualWithdrawFee' => 0.0,
                'organizerAutoWithdrawFee' => 0.0,
            ]);
        } catch (\Throwable) {
            $stmt = $this->pdo->prepare(
                'UPDATE site_settings
                 SET site_name = :name,
                     logo_url = :logoUrl,
                     whatsapp = :whatsapp,
                     instagram = :instagram,
                     contact_email = :email,
                     address_line = :addressLine,
                     google_maps_link = :googleMapsLink,
                     waze_link = :wazeLink,
                     pix_key = :pixKey,
                     service_tax = :serviceTax,
                     confirmation_message = :confirmationMessage,
                     whatsapp_template = :whatsappTemplate,
                     updated_at = NOW()
                 WHERE id = 1'
            );
            $stmt->execute($baseParams);
        }

        return $this->settings();
    }

    public function updateOrganizerPaymentSettings(array $payload): array
    {
        $this->ensureFinancialSchemaCompatibility();

        $row = $this->pdo->query('SELECT id FROM site_settings ORDER BY id ASC LIMIT 1')->fetch();
        if (!$row) {
            throw new HttpException(500, 'Configuracao principal do site nao encontrada.');
        }

        $setClauses = [];
        $params = ['id' => (int) $row['id']];

        if ($this->columnExistsSafe('site_settings', 'organizer_recipient_name')) {
            $setClauses[] = 'organizer_recipient_name = :organizerRecipientName';
            $params['organizerRecipientName'] = $payload['organizerRecipientName'] ?? null;
        }

        if ($this->columnExistsSafe('site_settings', 'organizer_pix_key_type')) {
            $setClauses[] = 'organizer_pix_key_type = :organizerPixKeyType';
            $params['organizerPixKeyType'] = $payload['organizerPixKeyType'] ?? 'CPF';
        }

        if ($this->columnExistsSafe('site_settings', 'organizer_pix_key')) {
            $setClauses[] = 'organizer_pix_key = :organizerPixKey';
            $params['organizerPixKey'] = $payload['organizerPixKey'] ?? null;
        } elseif ($this->columnExistsSafe('site_settings', 'pix_key')) {
            $setClauses[] = 'pix_key = :organizerPixKey';
            $params['organizerPixKey'] = $payload['organizerPixKey'] ?? null;
        }

        if ($this->columnExistsSafe('site_settings', 'updated_at')) {
            $setClauses[] = 'updated_at = NOW()';
        }

        if ($setClauses === []) {
            throw new HttpException(500, 'Colunas de pagamento nao encontradas em site_settings.');
        }

        $stmt = $this->pdo->prepare(
            'UPDATE site_settings
             SET ' . implode(', ', $setClauses) . '
             WHERE id = :id'
        );
        $stmt->execute($params);

        return $this->settings();
    }

    public function updatePlatformPaymentSettings(array $payload): array
    {
        $this->ensureFinancialSchemaCompatibility();
        $settings = $this->settings();

        $stmt = $this->pdo->prepare(
            'UPDATE site_settings
             SET platform_pix_key = :platformPixKey,
                 platform_pix_key_type = :platformPixKeyType,
                 platform_recipient_name = :platformRecipientName,
                 service_tax = :serviceTax,
                 asaas_fee_per_order = :asaasFeePerOrder,
                 updated_at = NOW()
             WHERE id = 1'
        );
        $stmt->execute([
            'platformPixKey' => $payload['platformPixKey'] ?? ($settings['platformPixKey'] ?? null),
            'platformPixKeyType' => $payload['platformPixKeyType'] ?? ($settings['platformPixKeyType'] ?? 'CPF'),
            'platformRecipientName' => $payload['platformRecipientName'] ?? ($settings['platformRecipientName'] ?? null),
            'serviceTax' => max(0.0, (float) ($payload['serviceTax'] ?? ($settings['serviceTax'] ?? 2.0))),
            'asaasFeePerOrder' => max(0.0, (float) ($payload['asaasFeePerOrder'] ?? ($settings['asaasFeePerOrder'] ?? 0.99))),
        ]);

        return $this->settings();
    }

    public function heroBanners(): array
    {
        return $this->pdo->query('SELECT * FROM hero_banners WHERE is_active = 1 ORDER BY display_order ASC, id ASC')->fetchAll() ?: [];
    }

    public function membershipPlans(): array
    {
        $rows = $this->pdo->query('SELECT * FROM membership_plans WHERE is_active = 1 ORDER BY id ASC')->fetchAll() ?: [];

        return array_map(static fn (array $row): array => [
            'id' => $row['code'],
            'dbId' => (int) $row['id'],
            'name' => $row['name'],
            'price' => (float) $row['price'],
            'validityDays' => (int) $row['validity_days'],
            'benefits' => json_decode($row['benefits_json'], true) ?: [],
        ], $rows);
    }

    public function leisureActivities(): array
    {
        $rows = $this->pdo->query('SELECT * FROM leisure_activities WHERE is_active = 1 ORDER BY activity_date ASC')->fetchAll() ?: [];

        return array_map(static fn (array $row): array => [
            'id' => (string) $row['id'],
            'title' => $row['title'],
            'date' => $row['activity_date'],
            'time' => $row['time_label'],
            'description' => $row['description'],
            'imageUrl' => $row['image_url'],
            'acceptsPartner' => (bool) $row['accepts_partner'],
            'guestPrice' => $row['guest_price'] !== null ? (float) $row['guest_price'] : null,
        ], $rows);
    }

    public function events(): array
    {
        $rows = $this->pdo->query('SELECT * FROM events ORDER BY featured DESC, event_date ASC')->fetchAll() ?: [];
        $batchStmt = $this->pdo->prepare('SELECT * FROM event_batches WHERE event_id = :eventId ORDER BY id ASC');
        $events = [];

        foreach ($rows as $row) {
            $batchStmt->execute(['eventId' => $row['id']]);
            $batchRows = $batchStmt->fetchAll() ?: [];
            $batches = [];
            $soldTickets = 0;

            foreach ($batchRows as $batch) {
                $quantity = $batch['quantity'] !== null ? (int) $batch['quantity'] : null;
                $sold = (int) $batch['sold'];
                $isUnlimited = $quantity === null;
                $remaining = $isUnlimited ? null : max($quantity - $sold, 0);
                $status = $batch['status'];
                $available = (bool) $batch['is_available'];

                if (!$isUnlimited && $remaining <= 0) {
                    $status = 'sold_out';
                    $available = false;
                } elseif ($status === 'paused') {
                    $available = false;
                } elseif ($status === 'active') {
                    $available = true;
                }

                $soldTickets += $sold;
                $batches[] = [
                    'id' => (string) $batch['id'],
                    'name' => $batch['name'],
                    'description' => $batch['description'],
                    'price' => (float) $batch['price'],
                    'ticketsPerUnit' => max(1, (int) ($batch['tickets_per_unit'] ?? 1)),
                    'available' => $available,
                    'quantity' => $quantity,
                    'isUnlimited' => $isUnlimited,
                    'remaining' => $remaining,
                    'sold' => $sold,
                    'status' => $status,
                ];
            }

            if ($batches === []) {
                continue;
            }

            $totalTickets = $row['total_tickets'] !== null ? (int) $row['total_tickets'] : null;
            $isUnlimited = $totalTickets === null;
            $remainingTickets = $isUnlimited ? null : max($totalTickets - $soldTickets, 0);

            $events[] = [
                'id' => (string) $row['id'],
                'title' => $row['title'],
                'date' => $row['event_date'],
                'location' => $row['location_label'],
                'attractions' => json_decode($row['attractions_json'], true) ?: [],
                'imageUrl' => $row['image_url'],
                'description' => $row['description'],
                'status' => $row['status'],
                'featured' => (bool) $row['featured'],
                'category' => $row['category'],
                'slug' => $row['slug'],
                'horario' => $row['time_label'],
                'totalTickets' => $totalTickets,
                'isUnlimited' => $isUnlimited,
                'remainingTickets' => $remainingTickets,
                'internalNotes' => $row['internal_notes'],
                'batches' => $batches,
            ];
        }

        return $events;
    }

    public function saveEvent(array $payload, ?int $eventId = null): array
    {
        $date = trim((string) ($payload['date'] ?? ''));
        $time = trim((string) ($payload['horario'] ?? '00:00'));
        if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
            throw new HttpException(422, 'Informe a data no formato correto: AAAA-MM-DD.');
        }
        if (!preg_match('/^\d{2}:\d{2}$/', $time)) {
            throw new HttpException(422, 'Informe o horario no formato correto: HH:MM.');
        }

        $dateTime = $date . ' ' . $time;
        $slug = !empty($payload['slug']) ? (string) $payload['slug'] : slugify((string) $payload['title']);
        $attractions = array_values(array_filter(array_map('trim', $payload['attractions'] ?? [])));
        if ($attractions === [] && isset($payload['attractionsText'])) {
            $attractions = array_values(array_filter(array_map('trim', explode(',', (string) $payload['attractionsText']))));
        }

        $isUnlimited = !empty($payload['isUnlimited']);
        $totalTickets = $isUnlimited ? null : (($payload['totalTickets'] ?? '') === '' ? null : max(0, (int) $payload['totalTickets']));
        $batches = is_array($payload['batches'] ?? null) ? $payload['batches'] : [];
        if ($batches === []) {
            $batches[] = [
                'name' => 'Lote Unico',
                'description' => 'Lote vigente',
                'price' => 0,
                'quantity' => '',
                'sold' => 0,
                'status' => 'paused',
                'available' => false,
                'isUnlimited' => true,
            ];
        }

        $this->pdo->beginTransaction();
        try {
            if ($eventId) {
                $stmt = $this->pdo->prepare(
                    'UPDATE events
                     SET slug = :slug, title = :title, description = :description, category = :category,
                         event_date = :eventDate, time_label = :timeLabel, location_label = :locationLabel,
                         image_url = :imageUrl, status = :status, featured = :featured, total_tickets = :totalTickets,
                         attractions_json = :attractionsJson, internal_notes = :internalNotes, updated_at = NOW()
                     WHERE id = :id'
                );
                $stmt->execute([
                    'id' => $eventId,
                    'slug' => $slug,
                    'title' => $payload['title'],
                    'description' => $payload['description'] ?? null,
                    'category' => $payload['category'] ?? null,
                    'eventDate' => $dateTime,
                    'timeLabel' => $payload['horario'] ?? null,
                    'locationLabel' => $payload['location'] ?? null,
                    'imageUrl' => $payload['imageUrl'] ?? null,
                    'status' => $payload['status'] ?? 'draft',
                    'featured' => !empty($payload['featured']) ? 1 : 0,
                    'totalTickets' => $totalTickets,
                    'attractionsJson' => json_encode($attractions, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
                    'internalNotes' => $payload['internalNotes'] ?? null,
                ]);
            } else {
                $stmt = $this->pdo->prepare(
                    'INSERT INTO events (
                        slug, title, description, category, event_date, time_label, location_label,
                        image_url, status, featured, total_tickets, attractions_json, internal_notes
                     ) VALUES (
                        :slug, :title, :description, :category, :eventDate, :timeLabel, :locationLabel,
                        :imageUrl, :status, :featured, :totalTickets, :attractionsJson, :internalNotes
                     )'
                );
                $stmt->execute([
                    'slug' => $slug,
                    'title' => $payload['title'],
                    'description' => $payload['description'] ?? null,
                    'category' => $payload['category'] ?? null,
                    'eventDate' => $dateTime,
                    'timeLabel' => $payload['horario'] ?? null,
                    'locationLabel' => $payload['location'] ?? null,
                    'imageUrl' => $payload['imageUrl'] ?? null,
                    'status' => $payload['status'] ?? 'draft',
                    'featured' => !empty($payload['featured']) ? 1 : 0,
                    'totalTickets' => $totalTickets,
                    'attractionsJson' => json_encode($attractions, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
                    'internalNotes' => $payload['internalNotes'] ?? null,
                ]);
                $eventId = (int) $this->pdo->lastInsertId();
            }

            $existingRows = $this->pdo->prepare('SELECT id FROM event_batches WHERE event_id = :eventId');
            $existingRows->execute(['eventId' => $eventId]);
            $existingIds = array_map('intval', array_column($existingRows->fetchAll() ?: [], 'id'));
            $keptIds = [];

            foreach ($batches as $batch) {
                if (empty(trim((string) ($batch['name'] ?? '')))) {
                    continue;
                }

                $batchId = isset($batch['id']) && ctype_digit((string) $batch['id']) ? (int) $batch['id'] : null;
                $batchIsUnlimited = !empty($batch['isUnlimited']) || (($batch['quantity'] ?? '') === '');
                $quantity = $batchIsUnlimited ? null : max(0, (int) $batch['quantity']);
                $ticketsPerUnit = max(1, min(20, (int) ($batch['ticketsPerUnit'] ?? $batch['tickets_per_unit'] ?? 1)));
                $sold = max(0, (int) ($batch['sold'] ?? 0));
                $status = (string) ($batch['status'] ?? 'active');
                $isAvailable = !empty($batch['available']);

                if (!$batchIsUnlimited && $sold >= $quantity) {
                    $status = 'sold_out';
                    $isAvailable = false;
                } elseif ($status === 'paused') {
                    $isAvailable = false;
                } elseif ($status === 'active') {
                    $isAvailable = true;
                }

                $params = [
                    'eventId' => $eventId,
                    'code' => $batch['code'] ?? slugify((string) $batch['name']),
                    'name' => $batch['name'],
                    'description' => $batch['description'] ?? null,
                    'price' => (float) ($batch['price'] ?? 0),
                    'ticketsPerUnit' => $ticketsPerUnit,
                    'quantity' => $quantity,
                    'sold' => $sold,
                    'isAvailable' => $isAvailable ? 1 : 0,
                    'status' => $status,
                ];

                if ($batchId) {
                    $stmt = $this->pdo->prepare(
                        'UPDATE event_batches
                         SET code = :code, name = :name, description = :description, price = :price,
                             tickets_per_unit = :ticketsPerUnit, quantity = :quantity, sold = :sold, is_available = :isAvailable, status = :status,
                             updated_at = NOW()
                         WHERE id = :id AND event_id = :eventId'
                    );
                    $stmt->execute($params + ['id' => $batchId]);
                    $keptIds[] = $batchId;
                } else {
                    $stmt = $this->pdo->prepare(
                        'INSERT INTO event_batches (event_id, code, name, description, price, tickets_per_unit, quantity, sold, is_available, status)
                         VALUES (:eventId, :code, :name, :description, :price, :ticketsPerUnit, :quantity, :sold, :isAvailable, :status)'
                    );
                    $stmt->execute($params);
                    $keptIds[] = (int) $this->pdo->lastInsertId();
                }
            }

            $deleteIds = array_values(array_diff($existingIds, $keptIds));
            if ($deleteIds !== []) {
                $placeholders = implode(',', array_fill(0, count($deleteIds), '?'));
                $stmt = $this->pdo->prepare("DELETE FROM event_batches WHERE event_id = ? AND id IN ($placeholders)");
                $stmt->execute(array_merge([$eventId], $deleteIds));
            }

            $this->pdo->commit();
        } catch (\Throwable $throwable) {
            $this->pdo->rollBack();
            throw $throwable;
        }

        foreach ($this->events() as $event) {
            if ((int) $event['id'] === $eventId) {
                return $event;
            }
        }

        throw new HttpException(500, 'Evento salvo mas não pôde ser recarregado.');
    }

    public function deleteEvent(int $eventId): void
    {
        $exists = $this->pdo->prepare('SELECT id FROM events WHERE id = :id LIMIT 1');
        $exists->execute(['id' => $eventId]);
        if (!$exists->fetch()) {
            throw new HttpException(404, 'Evento nao encontrado.');
        }

        $this->pdo->beginTransaction();
        try {
            if ($this->tableExists('event_payout_configs') && $this->columnExistsSafe('event_payout_configs', 'event_id')) {
                $stmt = $this->pdo->prepare('DELETE FROM event_payout_configs WHERE event_id = :id');
                $stmt->execute(['id' => $eventId]);
            }

            if ($this->tableExists('order_payouts') && $this->columnExistsSafe('order_payouts', 'event_id')) {
                $stmt = $this->pdo->prepare('UPDATE order_payouts SET event_id = NULL WHERE event_id = :id');
                $stmt->execute(['id' => $eventId]);
            }

            if ($this->tableExists('tickets') && $this->columnExistsSafe('tickets', 'event_id')) {
                $stmt = $this->pdo->prepare('DELETE FROM tickets WHERE event_id = :id');
                $stmt->execute(['id' => $eventId]);
            }

            if ($this->tableExists('orders') && $this->columnExistsSafe('orders', 'event_id')) {
                $stmt = $this->pdo->prepare('UPDATE orders SET event_id = NULL WHERE event_id = :id');
                $stmt->execute(['id' => $eventId]);
            }

            if ($this->tableExists('event_batches')) {
                if ($this->tableExists('order_items') && $this->columnExistsSafe('order_items', 'batch_id')) {
                    $stmt = $this->pdo->prepare(
                        'UPDATE order_items
                         SET batch_id = NULL
                         WHERE batch_id IN (SELECT id FROM event_batches WHERE event_id = :id)'
                    );
                    $stmt->execute(['id' => $eventId]);
                }

                $stmt = $this->pdo->prepare('DELETE FROM event_batches WHERE event_id = :id');
                $stmt->execute(['id' => $eventId]);
            }

            $stmt = $this->pdo->prepare('DELETE FROM events WHERE id = :id');
            $stmt->execute(['id' => $eventId]);

            $this->pdo->commit();
        } catch (\Throwable $throwable) {
            $this->pdo->rollBack();
            throw $throwable;
        }
    }

    public function partners(): array
    {
        $rows = $this->pdo->query(
            'SELECT p.*, m.code AS plan_code
             FROM partners p
             INNER JOIN membership_plans m ON m.id = p.plan_id
             ORDER BY p.created_at DESC'
        )->fetchAll() ?: [];

        return array_map(static function (array $row): array {
            $cpf = normalizeCpf((string) $row['cpf']);
            return [
                'id' => (string) $row['id'],
                'name' => $row['full_name'],
                'whatsapp' => $row['whatsapp'],
                'cpf' => $row['cpf'],
                'birthDate' => $row['birth_date'],
                'email' => $row['email'],
                'photoUrl' => $row['photo_url'],
                'planId' => $row['plan_code'],
                'startDate' => $row['start_date'],
                'expiryDate' => $row['expiry_date'],
                'status' => $row['status'],
                'memberCode' => 'PARC-' . str_pad((string) $row['id'], 6, '0', STR_PAD_LEFT),
                'qrValue' => 'GALPAO_IMPERIAL:PARTNER:' . $cpf,
                'lastValidatedAt' => $row['last_validated_at'] ?? null,
                'lastValidatedBy' => $row['last_validated_by'] ?? null,
                'notes' => $row['notes'],
            ];
        }, $rows);
    }

    public function savePartner(array $payload, ?int $partnerId = null): array
    {
        $planId = $payload['planDbId'] ?? null;
        if (!$planId && !empty($payload['planId'])) {
            $planStmt = $this->pdo->prepare('SELECT id FROM membership_plans WHERE code = :code LIMIT 1');
            $planStmt->execute(['code' => $payload['planId']]);
            $planId = (int) ($planStmt->fetchColumn() ?: 0);
        }
        if (!$planId) {
            throw new HttpException(422, 'Plano do parceiro é obrigatório.');
        }

        $params = [
            'planId' => $planId,
            'fullName' => trim((string) ($payload['name'] ?? '')),
            'whatsapp' => normalizePhone((string) ($payload['whatsapp'] ?? '')),
            'cpf' => trim((string) ($payload['cpf'] ?? '')),
            'birthDate' => (string) ($payload['birthDate'] ?? date('Y-m-d')),
            'email' => trim((string) ($payload['email'] ?? '')) ?: null,
            'status' => (string) ($payload['status'] ?? 'pending'),
            'startDate' => !empty($payload['startDate']) ? (string) $payload['startDate'] : null,
            'expiryDate' => !empty($payload['expiryDate']) ? (string) $payload['expiryDate'] : null,
            'notes' => trim((string) ($payload['notes'] ?? '')) ?: null,
        ];

        if ($partnerId) {
            $stmt = $this->pdo->prepare(
                'UPDATE partners
                 SET plan_id = :planId, full_name = :fullName, whatsapp = :whatsapp, cpf = :cpf,
                     birth_date = :birthDate, email = :email, status = :status,
                     start_date = :startDate, expiry_date = :expiryDate, notes = :notes, updated_at = NOW()
                 WHERE id = :id'
            );
            $stmt->execute($params + ['id' => $partnerId]);
        } else {
            $stmt = $this->pdo->prepare(
                'INSERT INTO partners (plan_id, full_name, whatsapp, cpf, birth_date, email, status, start_date, expiry_date, notes)
                 VALUES (:planId, :fullName, :whatsapp, :cpf, :birthDate, :email, :status, :startDate, :expiryDate, :notes)'
            );
            $stmt->execute($params);
            $partnerId = (int) $this->pdo->lastInsertId();
        }

        foreach ($this->partners() as $partner) {
            if ((int) $partner['id'] === $partnerId) {
                return $partner;
            }
        }

        throw new HttpException(500, 'Parceiro salvo, mas não pôde ser recarregado.');
    }

    public function renewPartner(int $partnerId, int $days = 30): array
    {
        $stmt = $this->pdo->prepare(
            'UPDATE partners
             SET status = "active",
                 start_date = COALESCE(start_date, NOW()),
                 expiry_date = DATE_ADD(COALESCE(expiry_date, NOW()), INTERVAL :days DAY),
                 updated_at = NOW()
             WHERE id = :id'
        );
        $stmt->bindValue('days', $days, PDO::PARAM_INT);
        $stmt->bindValue('id', $partnerId, PDO::PARAM_INT);
        $stmt->execute();

        foreach ($this->partners() as $partner) {
            if ((int) $partner['id'] === $partnerId) {
                return $partner;
            }
        }

        throw new HttpException(404, 'Parceiro não encontrado.');
    }

    public function deletePartner(int $partnerId): void
    {
        $stmt = $this->pdo->prepare('DELETE FROM partners WHERE id = :id');
        $stmt->execute(['id' => $partnerId]);
    }

    public function promoters(): array
    {
        $this->ensurePromotersSchemaCompatibility();
        if (!$this->tableExists('promoters')) {
            return [];
        }

        $rows = $this->pdo->query(
            "SELECT p.*,
                    COALESCE(pc.clicks, 0) AS clicks,
                    pc.last_click_at,
                    COALESCE(po.total_orders, 0) AS total_orders,
                    COALESCE(po.paid_orders, 0) AS paid_orders,
                    COALESCE(po.total_revenue, 0) AS total_revenue
             FROM promoters p
             LEFT JOIN (
                SELECT promoter_id, COUNT(*) AS clicks, MAX(created_at) AS last_click_at
                FROM promoter_clicks
                GROUP BY promoter_id
             ) pc ON pc.promoter_id = p.id
             LEFT JOIN (
                SELECT promoter_id,
                       COUNT(*) AS total_orders,
                       SUM(CASE WHEN payment_status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
                       SUM(CASE WHEN payment_status = 'paid' THEN total_value ELSE 0 END) AS total_revenue
                FROM orders
                WHERE promoter_id IS NOT NULL
                GROUP BY promoter_id
             ) po ON po.promoter_id = p.id
             ORDER BY p.is_active DESC, p.created_at DESC"
        )->fetchAll() ?: [];

        return array_map(function (array $row): array {
            return [
                'id' => (string) $row['id'],
                'name' => $row['name'],
                'slug' => $row['slug'] ?? null,
                'code' => $row['code'],
                'instagram' => $row['instagram'] ?? null,
                'whatsapp' => $row['whatsapp'] ?? null,
                'notes' => $row['notes'] ?? null,
                'landingUrl' => $row['landing_url'] ?? null,
                'isActive' => (bool) $row['is_active'],
                'clicks' => (int) ($row['clicks'] ?? 0),
                'totalOrders' => (int) ($row['total_orders'] ?? 0),
                'paidOrders' => (int) ($row['paid_orders'] ?? 0),
                'totalRevenue' => (float) ($row['total_revenue'] ?? 0),
                'generatedLink' => $this->buildPromoterLink($row['landing_url'] ?? null, (string) $row['code']),
                'lastClickAt' => $row['last_click_at'] ?? null,
                'createdAt' => $row['created_at'] ?? null,
            ];
        }, $rows);
    }

    public function savePromoter(array $payload, ?int $promoterId = null): array
    {
        $this->ensurePromotersSchemaCompatibility();

        $code = trim((string) ($payload['code'] ?? ''));
        $name = trim((string) ($payload['name'] ?? ''));
        if ($name === '') {
            throw new HttpException(422, 'Nome do promotor é obrigatório.');
        }
        if ($code === '') {
            $code = $this->generateUniquePromoterCode($name, $promoterId);
        } else {
            $code = $this->sanitizePromoterCode($code);
        }

        $params = [
            'name' => $name,
            'slug' => $this->slugify($name),
            'code' => $code,
            'instagram' => trim((string) ($payload['instagram'] ?? '')) ?: null,
            'whatsapp' => normalizePhone((string) ($payload['whatsapp'] ?? '')) ?: null,
            'notes' => trim((string) ($payload['notes'] ?? '')) ?: null,
            'landingUrl' => trim((string) ($payload['landingUrl'] ?? $payload['landing_url'] ?? '')) ?: null,
            'isActive' => !empty($payload['isActive']) ? 1 : 0,
        ];

        $duplicateStmt = $this->pdo->prepare('SELECT id FROM promoters WHERE code = :code' . ($promoterId ? ' AND id <> :id' : '') . ' LIMIT 1');
        $duplicateParams = ['code' => $code];
        if ($promoterId) {
            $duplicateParams['id'] = $promoterId;
        }
        $duplicateStmt->execute($duplicateParams);
        if ($duplicateStmt->fetchColumn()) {
            throw new HttpException(422, 'Já existe um promotor com esse código.');
        }

        if ($promoterId) {
            $stmt = $this->pdo->prepare(
                'UPDATE promoters
                 SET name = :name, slug = :slug, code = :code, instagram = :instagram, whatsapp = :whatsapp,
                     notes = :notes, landing_url = :landingUrl, is_active = :isActive, updated_at = NOW()
                 WHERE id = :id'
            );
            $stmt->execute($params + ['id' => $promoterId]);
        } else {
            $stmt = $this->pdo->prepare(
                'INSERT INTO promoters (name, slug, code, instagram, whatsapp, notes, landing_url, is_active)
                 VALUES (:name, :slug, :code, :instagram, :whatsapp, :notes, :landingUrl, :isActive)'
            );
            $stmt->execute($params);
            $promoterId = (int) $this->pdo->lastInsertId();
        }

        foreach ($this->promoters() as $promoter) {
            if ((int) $promoter['id'] === $promoterId) {
                return $promoter;
            }
        }

        throw new HttpException(500, 'Promotor salvo, mas não pôde ser recarregado.');
    }

    public function deletePromoter(int $promoterId): void
    {
        $this->ensurePromotersSchemaCompatibility();
        if ($this->tableExists('promoter_clicks')) {
            $stmt = $this->pdo->prepare('DELETE FROM promoter_clicks WHERE promoter_id = :id');
            $stmt->execute(['id' => $promoterId]);
        }
        $stmt = $this->pdo->prepare('DELETE FROM promoters WHERE id = :id');
        $stmt->execute(['id' => $promoterId]);
    }

    private function buildPromoterLink(?string $landingUrl, string $code): string
    {
        $base = trim((string) $landingUrl);
        if ($base === '') {
            $base = self::defaultPublicUrl() . '/';
        }
        return $base . (str_contains($base, '?') ? '&' : '?') . 'ref=' . urlencode($code);
    }

    private function sanitizePromoterCode(string $value): string
    {
        $value = strtolower(trim($value));
        $value = preg_replace('/[^a-z0-9\-_]+/', '', $value) ?: '';
        return $value !== '' ? $value : 'promotor-' . substr(md5((string) microtime(true)), 0, 6);
    }

    private function slugify(string $value): string
    {
        $value = iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $value) ?: $value;
        $value = strtolower($value);
        $value = preg_replace('/[^a-z0-9]+/', '-', $value) ?: '';
        return trim($value, '-') ?: 'promotor';
    }

    private function generateUniquePromoterCode(string $name, ?int $ignoreId = null): string
    {
        $base = $this->sanitizePromoterCode($this->slugify($name));
        $candidate = $base;
        $attempt = 2;
        while (true) {
            $sql = 'SELECT id FROM promoters WHERE code = :code' . ($ignoreId ? ' AND id <> :id' : '') . ' LIMIT 1';
            $stmt = $this->pdo->prepare($sql);
            $params = ['code' => $candidate];
            if ($ignoreId) {
                $params['id'] = $ignoreId;
            }
            $stmt->execute($params);
            if (!$stmt->fetchColumn()) {
                return $candidate;
            }
            $candidate = $base . $attempt;
            $attempt++;
        }
    }

    private function ensurePromotersSchemaCompatibility(): void
    {
        try {
            if (!$this->tableExists('promoters')) {
                $this->pdo->exec(
                    "CREATE TABLE IF NOT EXISTS promoters (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        name VARCHAR(180) NOT NULL,
                        slug VARCHAR(180) DEFAULT NULL,
                        code VARCHAR(40) NOT NULL UNIQUE,
                        instagram VARCHAR(180) DEFAULT NULL,
                        whatsapp VARCHAR(30) DEFAULT NULL,
                        notes TEXT DEFAULT NULL,
                        landing_url VARCHAR(255) DEFAULT NULL,
                        is_active TINYINT(1) NOT NULL DEFAULT 1,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                    )"
                );
            }
            if (!$this->tableExists('promoter_clicks')) {
                $this->pdo->exec(
                    "CREATE TABLE IF NOT EXISTS promoter_clicks (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        promoter_id INT NOT NULL,
                        code VARCHAR(40) NOT NULL,
                        landing_url VARCHAR(255) DEFAULT NULL,
                        referrer_url VARCHAR(255) DEFAULT NULL,
                        ip_address VARCHAR(45) DEFAULT NULL,
                        user_agent VARCHAR(255) DEFAULT NULL,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        KEY idx_promoter_clicks_promoter_id (promoter_id),
                        KEY idx_promoter_clicks_code (code)
                    )"
                );
            }

            $columns = [
                'orders' => [
                    'promoter_id' => 'ALTER TABLE orders ADD COLUMN promoter_id INT DEFAULT NULL AFTER event_id',
                    'promoter_code' => 'ALTER TABLE orders ADD COLUMN promoter_code VARCHAR(40) DEFAULT NULL AFTER promoter_id',
                    'promoter_assigned_at' => 'ALTER TABLE orders ADD COLUMN promoter_assigned_at DATETIME DEFAULT NULL AFTER promoter_code',
                ],
            ];
            foreach ($columns as $table => $tableColumns) {
                foreach ($tableColumns as $column => $sql) {
                    if (!$this->columnExistsSafe($table, $column)) {
                        $this->pdo->exec($sql);
                    }
                }
            }
        } catch (\Throwable) {
            // segue o fluxo mesmo em hospedagem restrita
        }
    }

    public function sales(): array
    {
        $rows = $this->pdo->query(
            'SELECT o.*, e.title AS event_title, e.image_url AS event_image_url
             FROM orders o
             LEFT JOIN events e ON e.id = o.event_id
             ORDER BY o.created_at DESC'
        )->fetchAll() ?: [];
        $ticketStmt = $this->pdo->prepare(
            'SELECT t.*, b.name AS batch_name
             FROM tickets t
             LEFT JOIN event_batches b ON b.id = t.batch_id
             WHERE t.order_id = :orderId
             ORDER BY t.id ASC'
        );
        $itemTotalStmt = $this->pdo->prepare('SELECT COALESCE(SUM(total_price), 0) FROM order_items WHERE order_id = :orderId');
        $expectedTicketStmt = $this->pdo->prepare(
            'SELECT COALESCE(SUM(oi.quantity * GREATEST(COALESCE(b.tickets_per_unit, 1), 1)), 0)
             FROM order_items oi
             LEFT JOIN event_batches b ON b.id = oi.batch_id
             WHERE oi.order_id = :orderId'
        );
        $sales = [];

        foreach ($rows as $row) {
            $ticketStmt->execute(['orderId' => $row['id']]);
            $tickets = $ticketStmt->fetchAll() ?: [];
            $itemTotalStmt->execute(['orderId' => $row['id']]);
            $subtotalValue = (float) ($itemTotalStmt->fetchColumn() ?: 0);
            $expectedTicketStmt->execute(['orderId' => $row['id']]);
            $expectedTicketCount = (int) ($expectedTicketStmt->fetchColumn() ?: 0);
            if ($expectedTicketCount <= 0 && $row['order_type'] === 'ticket') {
                $expectedTicketCount = (int) $row['quantity'];
            }
            if ($subtotalValue <= 0) {
                $subtotalValue = (float) $row['total_value'];
            }
            $serviceFeeValue = max(0.0, (float) $row['total_value'] - $subtotalValue);

            $sales[] = [
                'id' => (string) $row['id'],
                'orderNumber' => $row['order_number'],
                'orderType' => $row['order_type'],
                'buyerName' => $row['customer_name'],
                'email' => $row['customer_email'],
                'whatsapp' => $row['customer_whatsapp'],
                'customerCpf' => $row['customer_cpf'] ?? null,
                'eventId' => $row['event_id'] !== null ? (string) $row['event_id'] : '',
                'eventTitle' => $row['event_title'] ?? ($row['order_type'] === 'membership' ? 'Clube de Parceiros' : 'Sem evento'),
                'eventImageUrl' => $row['event_image_url'] ?? null,
                'quantity' => (int) $row['quantity'],
                'expectedTicketCount' => $expectedTicketCount,
                'missingTicketCount' => max(0, $expectedTicketCount - count($tickets)),
                'subtotalValue' => (float) ($row['subtotal_value'] ?? $subtotalValue),
                'serviceFee' => (float) ($row['platform_fee_value'] ?? $serviceFeeValue),
                'platformFeeValue' => (float) ($row['platform_fee_value'] ?? $serviceFeeValue),
                'asaasFeeValue' => (float) ($row['asaas_fee_value'] ?? 0),
                'platformNetFeeValue' => (float) ($row['platform_fee_value'] ?? $serviceFeeValue),
                'organizerNetValue' => (float) ($row['organizer_net_value'] ?? $subtotalValue),
                'totalValue' => (float) $row['total_value'],
                'paymentMethod' => $row['payment_method'],
                'paymentStatus' => $row['payment_status'],
                'purchaseDate' => $row['created_at'],
                'ticketStatus' => $row['ticket_status'] ?? 'unused',
                'paymentId' => $row['asaas_payment_id'],
                'ticketEmailSentAt' => $row['ticket_email_sent_at'] ?? null,
                'ticketEmailError' => $row['ticket_email_error'] ?? null,
                'tickets' => array_map(static fn (array $ticket): array => [
                    'id' => (string) $ticket['id'],
                    'code' => $ticket['ticket_code'],
                    'batchName' => $ticket['batch_name'] ?? '',
                    'status' => $ticket['status'],
                    'usedAt' => $ticket['validated_at'],
                    'validatedBy' => $ticket['validated_by'],
                    'qrValue' => 'GALPAO_IMPERIAL:TICKET:' . $ticket['ticket_code'],
                    'holderName' => $ticket['holder_name'] ?? null,
                    'holderEmail' => $ticket['holder_email'] ?? null,
                    'ticketEmailSentAt' => $ticket['ticket_email_sent_at'] ?? null,
                    'ticketEmailError' => $ticket['ticket_email_error'] ?? null,
                ], $tickets),
            ];
        }

        return $sales;
    }

    public function deleteSale(int $saleId): void
    {
        $stmt = $this->pdo->prepare('SELECT id, order_type, partner_id FROM orders WHERE id = :id LIMIT 1');
        $stmt->execute(['id' => $saleId]);
        $order = $stmt->fetch();

        if (!$order) {
            throw new HttpException(404, 'Venda não encontrada.');
        }

        $this->pdo->beginTransaction();
        try {
            $deleteOrder = $this->pdo->prepare('DELETE FROM orders WHERE id = :id');
            $deleteOrder->execute(['id' => $saleId]);

            if ($order['order_type'] === 'membership' && !empty($order['partner_id'])) {
                $deletePartner = $this->pdo->prepare('DELETE FROM partners WHERE id = :id');
                $deletePartner->execute(['id' => $order['partner_id']]);
            }

            $this->pdo->commit();
        } catch (\Throwable $throwable) {
            $this->pdo->rollBack();
            throw $throwable;
        }
    }

    public function giftClaims(): array
    {
        $rows = $this->pdo->query('SELECT * FROM gift_claims ORDER BY claim_date DESC')->fetchAll() ?: [];

        return array_map(static fn (array $row): array => [
            'id' => (string) $row['id'],
            'firstName' => $row['first_name'],
            'lastName' => $row['last_name'],
            'cpf' => $row['cpf'] ?? null,
            'whatsapp' => $row['whatsapp'],
            'claimDate' => $row['claim_date'],
            'code' => $row['claim_code'],
            'status' => $row['status'],
            'usedAt' => $row['used_at'],
            'guestAllowance' => 3,
        ], $rows);
    }

    public function createGiftClaim(array $payload): array
    {
        $cpf = normalizeCpf((string) ($payload['cpf'] ?? ''));
        $whatsapp = normalizePhone((string) ($payload['whatsapp'] ?? ''));
        $customCode = normalizeGiftCode((string) ($payload['customCode'] ?? ''));

        $check = $this->pdo->prepare('SELECT id FROM gift_claims WHERE whatsapp = :whatsapp OR cpf = :cpf LIMIT 1');
        $check->execute(['whatsapp' => $whatsapp, 'cpf' => $cpf]);
        if ($check->fetch()) {
            throw new HttpException(409, 'Já existe um presente cadastrado para este WhatsApp ou CPF.');
        }

        $code = $customCode !== ''
            ? 'PRESENTE-' . $customCode
            : 'PRESENTE-' . strtoupper(substr(slugify((string) $payload['firstName']), 0, 12)) . '-' . random_int(1000, 9999);

        $duplicateCode = $this->pdo->prepare('SELECT id FROM gift_claims WHERE claim_code = :code LIMIT 1');
        $duplicateCode->execute(['code' => $code]);
        if ($duplicateCode->fetch()) {
            throw new HttpException(409, 'Esse código de presente já está em uso. Escolha outro.');
        }

        $stmt = $this->pdo->prepare(
            'INSERT INTO gift_claims (first_name, last_name, cpf, whatsapp, claim_code, status, claim_date)
             VALUES (:firstName, :lastName, :cpf, :whatsapp, :claimCode, :status, NOW())'
        );
        $stmt->execute([
            'firstName' => $payload['firstName'],
            'lastName' => $payload['lastName'],
            'cpf' => $cpf,
            'whatsapp' => $whatsapp,
            'claimCode' => $code,
            'status' => 'confirmed',
        ]);

        return $this->findGiftClaim($code);
    }

    public function findTicketValidation(string $query, ?int $eventId = null): array
    {
        $normalized = trim($query);
        $ticketCode = str_starts_with($normalized, 'GALPAO_IMPERIAL:TICKET:') ? substr($normalized, 15) : $normalized;
        $cpf = normalizeCpf($normalized);
        $phone = normalizePhone($normalized);
        $cpfLookup = strlen($cpf) >= 11 ? $cpf : '__NO_CPF_LOOKUP__';
        $phoneLookup = strlen($phone) >= 8 ? $phone : '__NO_PHONE_LOOKUP__';

        $sql = 'SELECT o.*, e.title AS event_title, t.ticket_code, t.status AS ticket_status_value,
                       t.validated_at, t.validated_by, t.holder_name, t.holder_email,
                       t.ticket_email_sent_at, t.ticket_email_error, b.name AS batch_name
                FROM orders o
                LEFT JOIN events e ON e.id = o.event_id
                LEFT JOIN tickets t ON t.order_id = o.id
                LEFT JOIN event_batches b ON b.id = t.batch_id
                WHERE (
                    t.ticket_code = :ticketCode
                    OR REPLACE(REPLACE(REPLACE(o.customer_cpf, \'.\', \'\'), \'-\', \'\'), \' \', \'\') = :cpf
                    OR o.customer_whatsapp = :phone
                    OR o.customer_email LIKE :emailLike
                    OR o.customer_name LIKE :nameLike
                    OR o.order_number LIKE :orderNumberLike
                    OR t.holder_name LIKE :nameLike
                    OR t.holder_email LIKE :emailLike
                )';

        if ($eventId !== null) {
            $sql .= ' AND o.event_id = :eventId';
        }

        $sql .= " ORDER BY
                    CASE WHEN o.payment_status = 'paid' THEN 0 ELSE 1 END ASC,
                    CASE WHEN t.status = 'used' THEN 1 ELSE 0 END ASC,
                    o.created_at DESC,
                    t.id ASC
                  LIMIT 20";

        $stmt = $this->pdo->prepare($sql);
        $params = [
            'ticketCode' => $ticketCode,
            'cpf' => $cpfLookup,
            'phone' => $phoneLookup,
            'emailLike' => '%' . $normalized . '%',
            'nameLike' => '%' . $normalized . '%',
            'orderNumberLike' => '%' . ltrim($normalized, '#') . '%',
        ];
        if ($eventId !== null) {
            $params['eventId'] = $eventId;
        }
        $stmt->execute($params);
        $rows = $stmt->fetchAll() ?: [];

        if ($rows === []) {
            throw new HttpException(404, 'Ingresso não encontrado.');
        }

        $order = $rows[0];
        $tickets = [];
        foreach ($rows as $row) {
            if (!$row['ticket_code']) {
                continue;
            }
            $tickets[] = [
                'code' => $row['ticket_code'],
                'status' => $row['ticket_status_value'],
                'batchName' => $row['batch_name'],
                'usedAt' => $row['validated_at'],
                'validatedBy' => $row['validated_by'],
                'qrValue' => 'GALPAO_IMPERIAL:TICKET:' . $row['ticket_code'],
                'holderName' => $row['holder_name'] ?? null,
                'holderEmail' => $row['holder_email'] ?? null,
                'ticketEmailSentAt' => $row['ticket_email_sent_at'] ?? null,
                'ticketEmailError' => $row['ticket_email_error'] ?? null,
            ];
        }

        return [
            'orderNumber' => $order['order_number'],
            'buyerName' => $order['customer_name'],
            'customerCpf' => $order['customer_cpf'] ?? null,
            'whatsapp' => $order['customer_whatsapp'],
            'eventId' => $order['event_id'] !== null ? (string) $order['event_id'] : null,
            'eventTitle' => $order['event_title'],
            'paymentStatus' => $order['payment_status'],
            'tickets' => $tickets,
        ];
    }

    public function listTicketValidation(string $query = '', ?int $eventId = null, int $limit = 80, bool $activeOnly = true): array
    {
        $normalized = trim($query);
        $ticketCode = str_starts_with($normalized, 'GALPAO_IMPERIAL:TICKET:') ? substr($normalized, 15) : $normalized;
        $cpf = normalizeCpf($normalized);
        $phone = normalizePhone($normalized);
        $cpfLookup = strlen($cpf) >= 11 ? $cpf : '__NO_CPF_LOOKUP__';
        $phoneLookup = strlen($phone) >= 8 ? $phone : '__NO_PHONE_LOOKUP__';
        $limit = max(10, min(150, $limit));

        $where = ['o.order_type = "ticket"', 't.ticket_code IS NOT NULL'];
        $params = [];

        if ($normalized !== '') {
            $where[] = '(
                t.ticket_code = :ticketCode
                OR REPLACE(REPLACE(REPLACE(o.customer_cpf, ".", ""), "-", ""), " ", "") = :cpf
                OR REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(o.customer_whatsapp, "(", ""), ")", ""), "-", ""), " ", ""), "+", "") = :phone
                OR o.customer_email LIKE :emailLike
                OR o.customer_name LIKE :nameLike
                OR o.order_number LIKE :orderNumberLike
                OR t.holder_name LIKE :nameLike
                OR t.holder_email LIKE :emailLike
            )';
            $params = [
                'ticketCode' => $ticketCode,
                'cpf' => $cpfLookup,
                'phone' => $phoneLookup,
                'emailLike' => '%' . $normalized . '%',
                'nameLike' => '%' . $normalized . '%',
                'orderNumberLike' => '%' . ltrim($normalized, '#') . '%',
            ];
        }

        if ($eventId !== null) {
            $where[] = 't.event_id = :eventId';
            $params['eventId'] = $eventId;
        } elseif ($activeOnly) {
            $where[] = 'e.status IN ("active", "featured", "last_tickets")';
        }

        $sql = 'SELECT
                    o.id AS order_id, o.order_number, o.customer_name, o.customer_cpf,
                    o.customer_whatsapp, o.customer_email, o.payment_status, o.quantity,
                    o.total_value, o.created_at AS purchase_date,
                    e.title AS event_title, e.event_date AS event_date,
                    t.id AS ticket_id, t.ticket_code, t.status AS ticket_status_value,
                    t.validated_at, t.validated_by, t.holder_name, t.holder_email,
                    t.ticket_email_sent_at, t.ticket_email_error, b.name AS batch_name
                FROM tickets t
                INNER JOIN orders o ON o.id = t.order_id
                LEFT JOIN events e ON e.id = t.event_id
                LEFT JOIN event_batches b ON b.id = t.batch_id
                WHERE ' . implode(' AND ', $where) . '
                ORDER BY
                    CASE WHEN o.payment_status = "paid" THEN 0 ELSE 1 END ASC,
                    CASE WHEN t.status = "unused" THEN 0 ELSE 1 END ASC,
                    o.created_at DESC,
                    t.id ASC
                LIMIT ' . $limit;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll() ?: [];

        return array_map(static fn (array $row): array => [
            'orderId' => (string) $row['order_id'],
            'orderNumber' => $row['order_number'],
            'buyerName' => $row['customer_name'],
            'customerCpf' => $row['customer_cpf'] ?? null,
            'whatsapp' => $row['customer_whatsapp'] ?? null,
            'email' => $row['customer_email'] ?? null,
            'paymentStatus' => $row['payment_status'],
            'quantity' => (int) ($row['quantity'] ?? 0),
            'totalValue' => (float) ($row['total_value'] ?? 0),
            'purchaseDate' => $row['purchase_date'] ?? null,
            'eventTitle' => $row['event_title'] ?? 'Sem evento',
            'eventDate' => $row['event_date'] ?? null,
            'ticket' => [
                'id' => (string) $row['ticket_id'],
                'code' => $row['ticket_code'],
                'status' => $row['ticket_status_value'],
                'batchName' => $row['batch_name'] ?? '',
                'usedAt' => $row['validated_at'],
                'validatedBy' => $row['validated_by'],
                'qrValue' => 'GALPAO_IMPERIAL:TICKET:' . $row['ticket_code'],
                'holderName' => $row['holder_name'] ?? null,
                'holderEmail' => $row['holder_email'] ?? null,
                'ticketEmailSentAt' => $row['ticket_email_sent_at'] ?? null,
                'ticketEmailError' => $row['ticket_email_error'] ?? null,
            ],
        ], $rows);
    }

    public function checkInTicket(string $ticketCode, string $validatedBy = 'Portaria', ?int $eventId = null): array
    {
        $ticketCode = str_starts_with($ticketCode, 'GALPAO_IMPERIAL:TICKET:') ? substr($ticketCode, 15) : $ticketCode;
        $sql = 'SELECT t.*, o.payment_status, o.customer_name, o.customer_cpf, e.title AS event_title, b.name AS batch_name
                FROM tickets t
                INNER JOIN orders o ON o.id = t.order_id
                INNER JOIN events e ON e.id = t.event_id
                LEFT JOIN event_batches b ON b.id = t.batch_id
                WHERE t.ticket_code = :ticketCode';
        if ($eventId !== null) {
            $sql .= ' AND t.event_id = :eventId';
        }
        $sql .= ' LIMIT 1';

        $stmt = $this->pdo->prepare($sql);
        $params = ['ticketCode' => $ticketCode];
        if ($eventId !== null) {
            $params['eventId'] = $eventId;
        }
        $stmt->execute($params);
        $ticket = $stmt->fetch();

        if (!$ticket) {
            throw new HttpException(404, 'Ticket não encontrado para este evento.');
        }
        if ($ticket['payment_status'] !== 'paid') {
            throw new HttpException(409, 'Pagamento ainda não foi confirmado.');
        }
        if ($ticket['status'] === 'used') {
            throw new HttpException(409, 'Ingresso já utilizado.', ['usedAt' => $ticket['validated_at']]);
        }

        $update = $this->pdo->prepare(
            'UPDATE tickets SET status = :status, validated_by = :validatedBy, validated_at = NOW(), updated_at = NOW()
             WHERE id = :id'
        );
        $update->execute([
            'status' => 'used',
            'validatedBy' => $validatedBy,
            'id' => $ticket['id'],
        ]);

        return [
            'status' => 'success',
            'buyer' => $ticket['customer_name'],
            'customerCpf' => $ticket['customer_cpf'],
            'event' => $ticket['event_title'],
            'type' => $ticket['batch_name'] ?? '',
            'code' => $ticket['ticket_code'],
            'time' => nowInBrasilia()->format('H:i:s'),
        ];
    }

    public function findPartnerValidation(string $query): array
    {
        $normalized = trim($query);
        $rawPartnerCode = str_replace(['GALPAO_IMPERIAL:PARTNER:', 'GALPAO_IMPERIAL:CARD:'], '', strtoupper($normalized));
        $cpf = normalizeCpf($rawPartnerCode !== '' ? $rawPartnerCode : $normalized);
        $phone = normalizePhone($normalized);

        $stmt = $this->pdo->prepare(
            'SELECT p.*, m.code AS plan_code
             FROM partners p
             INNER JOIN membership_plans m ON m.id = p.plan_id
             WHERE REPLACE(REPLACE(p.cpf, ".", ""), "-", "") = :cpf
                OR p.whatsapp = :phone
                OR p.full_name LIKE :nameLike
                OR p.id = :id
             ORDER BY p.created_at DESC
             LIMIT 1'
        );
        $stmt->execute([
            'cpf' => $cpf,
            'phone' => $phone,
            'nameLike' => '%' . $normalized . '%',
            'id' => ctype_digit($rawPartnerCode) ? (int) $rawPartnerCode : 0,
        ]);
        $row = $stmt->fetch();

        if (!$row) {
            throw new HttpException(404, 'Carteirinha não encontrada.');
        }

        $normalizedCpf = normalizeCpf((string) $row['cpf']);
        return [
            'id' => (string) $row['id'],
            'name' => $row['full_name'],
            'cpf' => $row['cpf'],
            'whatsapp' => $row['whatsapp'],
            'status' => $row['status'],
            'planId' => $row['plan_code'],
            'memberCode' => 'PARC-' . str_pad((string) $row['id'], 6, '0', STR_PAD_LEFT),
            'qrValue' => 'GALPAO_IMPERIAL:PARTNER:' . $normalizedCpf,
            'expiryDate' => $row['expiry_date'],
            'lastValidatedAt' => $row['last_validated_at'] ?? null,
            'lastValidatedBy' => $row['last_validated_by'] ?? null,
        ];
    }

    public function validatePartner(string $query, string $validatedBy = 'Portaria'): array
    {
        $partner = $this->findPartnerValidation($query);

        if ($partner['status'] !== 'active') {
            throw new HttpException(409, 'A carteirinha não está ativa.');
        }

        $stmt = $this->pdo->prepare(
            'UPDATE partners
             SET last_validated_at = NOW(), last_validated_by = :validatedBy, updated_at = NOW()
             WHERE id = :id'
        );
        $stmt->execute([
            'validatedBy' => $validatedBy,
            'id' => $partner['id'],
        ]);

        return $this->findPartnerValidation((string) $partner['cpf']);
    }

    public function findGiftClaim(string $query): array
    {
        $normalized = trim($query);
        $cpf = normalizeCpf($normalized);
        $phone = normalizePhone($normalized);
        $stmt = $this->pdo->prepare(
            'SELECT * FROM gift_claims
             WHERE claim_code = :code
                OR cpf = :cpf
                OR whatsapp = :phone
                OR CONCAT(first_name, " ", last_name) LIKE :nameLike
             ORDER BY claim_date DESC
             LIMIT 1'
        );
        $stmt->execute([
            'code' => $normalized,
            'cpf' => $cpf,
            'phone' => $phone,
            'nameLike' => '%' . $normalized . '%',
        ]);
        $row = $stmt->fetch();
        if (!$row) {
            throw new HttpException(404, 'Presente não encontrado.');
        }

        return [
            'id' => (string) $row['id'],
            'firstName' => $row['first_name'],
            'lastName' => $row['last_name'],
            'cpf' => $row['cpf'] ?? null,
            'whatsapp' => $row['whatsapp'],
            'claimDate' => $row['claim_date'],
            'code' => $row['claim_code'],
            'status' => $row['status'],
            'usedAt' => $row['used_at'],
            'guestAllowance' => 3,
        ];
    }

    public function useGiftClaim(int $claimId): array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM gift_claims WHERE id = :id LIMIT 1');
        $stmt->execute(['id' => $claimId]);
        $row = $stmt->fetch();
        if (!$row) {
            throw new HttpException(404, 'Presente não encontrado.');
        }
        if ($row['status'] === 'used') {
            throw new HttpException(409, 'Presente já utilizado.');
        }

        $update = $this->pdo->prepare('UPDATE gift_claims SET status = :status, used_at = NOW(), updated_at = NOW() WHERE id = :id');
        $update->execute([
            'status' => 'used',
            'id' => $claimId,
        ]);

        return $this->findGiftClaim((string) $row['claim_code']);
    }

    public function getOrderByPayment(string $paymentId): ?array
    {
        foreach ($this->sales() as $sale) {
            if (($sale['paymentId'] ?? null) === $paymentId) {
                return $sale;
            }
        }

        return null;
    }

    public function markSalePaidById(int $orderId): ?array
    {
        $stmt = $this->pdo->prepare('SELECT asaas_payment_id FROM orders WHERE id = :id LIMIT 1');
        $stmt->execute(['id' => $orderId]);
        $paymentId = (string) ($stmt->fetchColumn() ?: '');
        if ($paymentId !== '') {
            return $this->markOrderPaid($paymentId);
        }

        $this->pdo->beginTransaction();
        try {
            $updateOrder = $this->pdo->prepare(
                'UPDATE orders
                 SET payment_status = :status,
                     ticket_status = CASE WHEN order_type = "ticket" THEN "unused" ELSE ticket_status END,
                     paid_at = NOW(),
                     updated_at = NOW()
                 WHERE id = :id'
            );
            $updateOrder->execute([
                'status' => 'paid',
                'id' => $orderId,
            ]);
            $this->pdo->commit();
        } catch (\Throwable $throwable) {
            $this->pdo->rollBack();
            throw $throwable;
        }

        $sale = null;
        foreach ($this->sales() as $item) {
            if ((int) $item['id'] === $orderId) {
                $sale = $item;
                break;
            }
        }

        if (($sale['orderType'] ?? null) === 'ticket') {
            try {
                $this->settleTicketOrderFinance($orderId);
            } catch (\Throwable) {
            }
            try {
                $this->sendTicketEmailForOrderId($orderId);
            } catch (\Throwable) {
            }
        }

        return $sale;
    }

    public function markOrderPaid(string $paymentId): ?array
    {
        $this->pdo->beginTransaction();
        try {
            $findOrder = $this->pdo->prepare(
                'SELECT id, order_type, payment_status, quantity, partner_id
                 FROM orders
                 WHERE asaas_payment_id = :paymentId
                 LIMIT 1'
            );
            $findOrder->execute(['paymentId' => $paymentId]);
            $order = $findOrder->fetch();

            if (!$order) {
                $this->pdo->rollBack();
                return null;
            }

            if ($order['payment_status'] !== 'paid') {
                $updateOrder = $this->pdo->prepare(
                    'UPDATE orders
                     SET payment_status = :status,
                         ticket_status = CASE WHEN order_type = "ticket" THEN "unused" ELSE ticket_status END,
                         paid_at = NOW(),
                         updated_at = NOW()
                     WHERE asaas_payment_id = :paymentId'
                );
                $updateOrder->execute([
                    'status' => 'paid',
                    'paymentId' => $paymentId,
                ]);

                if ($order['order_type'] === 'ticket') {
                    $items = $this->pdo->prepare('SELECT batch_id, quantity FROM order_items WHERE order_id = :orderId');
                    $items->execute(['orderId' => $order['id']]);
                    foreach ($items->fetchAll() ?: [] as $item) {
                        if (!$item['batch_id']) {
                            continue;
                        }

                        $updateBatch = $this->pdo->prepare(
                            'UPDATE event_batches
                             SET sold = sold + :quantity,
                                 status = CASE
                                    WHEN quantity IS NOT NULL AND (sold + :quantity) >= quantity THEN "sold_out"
                                    ELSE status
                                 END,
                                 is_available = CASE
                                    WHEN quantity IS NOT NULL AND (sold + :quantity) >= quantity THEN 0
                                    WHEN status = "paused" THEN 0
                                    ELSE 1
                                 END,
                                 updated_at = NOW()
                             WHERE id = :batchId'
                        );
                        $updateBatch->execute([
                            'quantity' => (int) $item['quantity'],
                            'batchId' => $item['batch_id'],
                        ]);
                    }
                }
            }

            $updatePartner = $this->pdo->prepare(
                'UPDATE partners
                 SET status = :status,
                     start_date = COALESCE(start_date, NOW()),
                     expiry_date = COALESCE(expiry_date, DATE_ADD(NOW(), INTERVAL 30 DAY)),
                     updated_at = NOW()
                 WHERE asaas_payment_id = :paymentId'
            );
            $updatePartner->execute([
                'status' => 'active',
                'paymentId' => $paymentId,
            ]);

            $this->pdo->commit();
        } catch (\Throwable $throwable) {
            $this->pdo->rollBack();
            throw $throwable;
        }

        $sale = $this->getOrderByPayment($paymentId);
        if (($sale['paymentStatus'] ?? null) === 'paid') {
            if (($sale['orderType'] ?? null) === 'ticket') {
                try {
                    $this->sendTicketEmailForPayment($paymentId);
                } catch (\Throwable) {
                }
            }
            try {
                if (($sale['orderType'] ?? null) === 'ticket') {
                    $this->settleTicketOrderFinance((int) $sale['id']);
                }
                if (($sale['orderType'] ?? null) === 'ticket') {
                    $this->sendTicketEmailForPayment($paymentId);
                }
                if (($sale['orderType'] ?? null) === 'membership') {
                    $this->sendPartnerCardEmailForPayment($paymentId);
                }
            } catch (\Throwable) {
                // O pagamento não pode falhar por problema de envio de e-mail.
            }
        }

        return $this->getOrderByPayment($paymentId);
    }

    public function markOrderPaidByExternalReference(string $externalReference): ?array
    {
        $stmt = $this->pdo->prepare('SELECT id, asaas_payment_id FROM orders WHERE external_reference = :externalReference LIMIT 1');
        $stmt->execute(['externalReference' => $externalReference]);
        $order = $stmt->fetch();
        if (!$order) {
            return null;
        }

        $paymentId = (string) ($order['asaas_payment_id'] ?? '');
        if ($paymentId === '') {
            return $this->markSalePaidById((int) $order['id']);
        }
        return $this->markOrderPaid($paymentId);
    }

    public function syncPendingAsaasPayments(int $limit = 50): array
    {
        $limit = max(1, min(100, $limit));
        $asaas = new AsaasClient();
        if (!$asaas->isEnabled()) {
            return [
                'checked' => 0,
                'paid' => 0,
                'closed' => 0,
                'pending' => 0,
                'errors' => ['ASAAS_API_KEY nao configurada.'],
            ];
        }

        $stmt = $this->pdo->query(
            'SELECT id, order_number, asaas_payment_id
             FROM orders
             WHERE payment_status = "pending"
               AND asaas_payment_id IS NOT NULL
               AND asaas_payment_id <> ""
             ORDER BY created_at DESC
             LIMIT ' . $limit
        );
        $orders = $stmt->fetchAll() ?: [];

        $summary = [
            'checked' => 0,
            'paid' => 0,
            'closed' => 0,
            'pending' => 0,
            'errors' => [],
        ];

        foreach ($orders as $order) {
            $paymentId = (string) ($order['asaas_payment_id'] ?? '');
            if ($paymentId === '') {
                continue;
            }

            $summary['checked']++;
            try {
                $payment = $asaas->getPayment($paymentId);
                $status = mapAsaasPaymentStatus($payment['status'] ?? null);

                if ($status === 'paid') {
                    $this->markOrderPaid($paymentId);
                    $summary['paid']++;
                    continue;
                }

                if (in_array($status, ['cancelled', 'refunded'], true)) {
                    $update = $this->pdo->prepare(
                        'UPDATE orders
                         SET payment_status = :status,
                             updated_at = NOW()
                         WHERE id = :id AND payment_status = "pending"'
                    );
                    $update->execute([
                        'status' => $status,
                        'id' => $order['id'],
                    ]);
                    $summary['closed']++;
                    continue;
                }

                $summary['pending']++;
            } catch (\Throwable $throwable) {
                $summary['errors'][] = sprintf(
                    '%s: %s',
                    (string) ($order['order_number'] ?? $paymentId),
                    $throwable->getMessage()
                );
            }
        }

        return $summary;
    }

    public function ticketEmailSummary(int $orderId): array
    {
        $this->ensureTicketEmailColumns();
        $orderStmt = $this->pdo->prepare('SELECT id, order_number, customer_email, ticket_email_sent_at, ticket_email_error FROM orders WHERE id = :orderId LIMIT 1');
        $orderStmt->execute(['orderId' => $orderId]);
        $order = $orderStmt->fetch() ?: [];

        $ticketStmt = $this->pdo->prepare('SELECT id, ticket_code, holder_email, ticket_email_sent_at, ticket_email_error FROM tickets WHERE order_id = :orderId ORDER BY id ASC');
        $ticketStmt->execute(['orderId' => $orderId]);
        $tickets = $ticketStmt->fetchAll() ?: [];
        $sentCount = 0;
        foreach ($tickets as $ticket) {
            if (!empty($ticket['ticket_email_sent_at'])) {
                $sentCount++;
            }
        }

        return [
            'orderId' => $orderId,
            'orderNumber' => $order['order_number'] ?? null,
            'sentTo' => $order['customer_email'] ?? null,
            'orderEmailSentAt' => $order['ticket_email_sent_at'] ?? null,
            'orderEmailError' => $order['ticket_email_error'] ?? null,
            'sentCount' => $sentCount,
            'totalTickets' => count($tickets),
            'tickets' => array_map(static fn (array $ticket): array => [
                'id' => (string) $ticket['id'],
                'code' => $ticket['ticket_code'],
                'holderEmail' => $ticket['holder_email'] ?? null,
                'sentAt' => $ticket['ticket_email_sent_at'] ?? null,
                'error' => $ticket['ticket_email_error'] ?? null,
            ], $tickets),
        ];
    }

    public function sendTicketEmailForPayment(string $paymentId, bool $forceResend = false, ?string $overrideEmail = null): bool
    {
        $this->ensureTicketEmailColumns();

        $stmt = $this->pdo->prepare(
            'SELECT o.*, e.title AS event_title, e.event_date AS event_date, e.time_label AS event_time, e.location_label AS event_location
             FROM orders o
             LEFT JOIN events e ON e.id = o.event_id
             WHERE o.asaas_payment_id = :paymentId AND o.order_type = "ticket"
             LIMIT 1'
        );
        $stmt->execute(['paymentId' => $paymentId]);
        $order = $stmt->fetch();

        if (!$order || $order['payment_status'] !== 'paid') {
            return false;
        }

        $fallbackEmail = trim((string) ($order['customer_email'] ?? ''));
        if ($fallbackEmail === '' || !filter_var($fallbackEmail, FILTER_VALIDATE_EMAIL)) {
            $this->storeTicketEmailResult((int) $order['id'], false, 'E-mail do comprador inválido ou vazio.');
            return false;
        }

        $ticketStmt = $this->pdo->prepare(
            'SELECT t.*, b.name AS batch_name
             FROM tickets t
             LEFT JOIN event_batches b ON b.id = t.batch_id
             WHERE t.order_id = :orderId
             ORDER BY t.id ASC'
        );
        $ticketStmt->execute(['orderId' => $order['id']]);
        $tickets = $ticketStmt->fetchAll() ?: [];

        if ($tickets === []) {
            $this->storeTicketEmailResult((int) $order['id'], false, 'Pedido pago sem tickets gerados.');
            return false;
        }

        $errors = [];
        $sentCount = 0;
        $totalTickets = count($tickets);
        foreach ($tickets as $index => $ticket) {
            if (!$forceResend && !empty($ticket['ticket_email_sent_at'])) {
                $sentCount++;
                continue;
            }

            $email = $overrideEmail ? trim($overrideEmail) : trim((string) ($ticket['holder_email'] ?? ''));
            if ($email === '' || !filter_var($email, FILTER_VALIDATE_EMAIL)) {
                $email = $fallbackEmail;
            }
            $holderName = trim((string) ($ticket['holder_name'] ?? '')) ?: (string) $order['customer_name'];

            $subject = sprintf('Ingresso %d/%d - %s', $index + 1, $totalTickets, (string) ($order['event_title'] ?? 'Galpão Imperial'));
            $html = $this->renderTicketEmail($order, [$ticket], $holderName, $index + 1, $totalTickets);
            $plain = $this->renderTicketEmailText($order, [$ticket], $holderName, $index + 1, $totalTickets);
            $sent = Mailer::sendHtml($email, $subject, $html, null, $plain);
            $error = $sent ? null : (Mailer::lastError() ?: 'Falha no envio do e-mail.');
            $this->storeSingleTicketEmailResult((int) $ticket['id'], $sent, $error);
            if ($sent) {
                $sentCount++;
            } else {
                $errors[] = $ticket['ticket_code'] . ': ' . $error;
            }
        }

        $allSent = $sentCount >= $totalTickets;
        $this->storeTicketEmailResult((int) $order['id'], $allSent, $allSent ? null : implode(' | ', $errors));

        return $allSent;
    }


    public function sendTicketEmailForOrderId(int $orderId, ?string $overrideEmail = null): bool
    {
        $this->ensureTicketEmailColumns();

        $stmt = $this->pdo->prepare('SELECT asaas_payment_id FROM orders WHERE id = :orderId AND order_type = "ticket" LIMIT 1');
        $stmt->execute(['orderId' => $orderId]);
        $paymentId = (string) ($stmt->fetchColumn() ?: '');

        if ($paymentId === '') {
            $this->storeTicketEmailResult($orderId, false, 'Pedido sem asaas_payment_id para reenvio.');
            return false;
        }

        return $this->sendTicketEmailForPayment($paymentId, true, $overrideEmail);
    }

    public function repairMissingTicketsForOrderId(int $orderId): array
    {
        $this->ensureTicketEmailColumns();

        $orderStmt = $this->pdo->prepare(
            'SELECT id, event_id, customer_name, customer_email, payment_status, order_type
             FROM orders
             WHERE id = :orderId AND order_type = "ticket"
             LIMIT 1'
        );
        $orderStmt->execute(['orderId' => $orderId]);
        $order = $orderStmt->fetch();
        if (!$order) {
            throw new HttpException(404, 'Pedido de ingresso nao encontrado.');
        }
        if (($order['payment_status'] ?? '') !== 'paid') {
            throw new HttpException(422, 'Somente pedidos pagos podem receber QR faltante.');
        }

        $itemStmt = $this->pdo->prepare(
            'SELECT oi.id, oi.batch_id, oi.quantity, COALESCE(b.tickets_per_unit, 1) AS tickets_per_unit
             FROM order_items oi
             LEFT JOIN event_batches b ON b.id = oi.batch_id
             WHERE oi.order_id = :orderId
             ORDER BY oi.id ASC'
        );
        $itemStmt->execute(['orderId' => $orderId]);
        $items = $itemStmt->fetchAll() ?: [];

        if ($items === []) {
            throw new HttpException(422, 'Pedido sem itens para conferir lote promocional.');
        }

        $countStmt = $this->pdo->prepare('SELECT COUNT(*) FROM tickets WHERE order_item_id = :orderItemId');
        $insertTicket = $this->pdo->prepare(
            'INSERT INTO tickets (order_id, order_item_id, event_id, batch_id, ticket_code, holder_name, holder_email, status)
             VALUES (:orderId, :orderItemId, :eventId, :batchId, :ticketCode, :holderName, :holderEmail, "unused")'
        );

        $created = 0;
        $expectedTotal = 0;
        $existingTotal = 0;

        $this->pdo->beginTransaction();
        try {
            foreach ($items as $item) {
                $ticketsPerUnit = max(1, (int) ($item['tickets_per_unit'] ?? 1));
                $expected = max(1, (int) $item['quantity']) * $ticketsPerUnit;
                $countStmt->execute(['orderItemId' => $item['id']]);
                $existing = (int) ($countStmt->fetchColumn() ?: 0);
                $missing = max(0, $expected - $existing);
                $expectedTotal += $expected;
                $existingTotal += $existing;

                for ($index = 0; $index < $missing; $index++) {
                    $insertTicket->execute([
                        'orderId' => $orderId,
                        'orderItemId' => $item['id'],
                        'eventId' => $order['event_id'],
                        'batchId' => $item['batch_id'],
                        'ticketCode' => generateTicketCode(),
                        'holderName' => $order['customer_name'],
                        'holderEmail' => $order['customer_email'],
                    ]);
                    $created++;
                }
            }

            $this->pdo->commit();
        } catch (\Throwable $throwable) {
            $this->pdo->rollBack();
            throw $throwable;
        }

        return [
            'orderId' => $orderId,
            'expectedTickets' => $expectedTotal,
            'existingTicketsBefore' => $existingTotal,
            'createdTickets' => $created,
            'totalTicketsAfter' => $existingTotal + $created,
        ];
    }

    private function ensureTicketEmailColumns(): void
    {
        try {
            $sentColumn = $this->pdo->query("SHOW COLUMNS FROM orders LIKE 'ticket_email_sent_at'")->fetch();
            if (!$sentColumn) {
                $this->pdo->exec('ALTER TABLE orders ADD COLUMN ticket_email_sent_at DATETIME DEFAULT NULL');
            }

            $errorColumn = $this->pdo->query("SHOW COLUMNS FROM orders LIKE 'ticket_email_error'")->fetch();
            if (!$errorColumn) {
                $this->pdo->exec('ALTER TABLE orders ADD COLUMN ticket_email_error TEXT DEFAULT NULL');
            }

            if (!$this->pdo->query("SHOW COLUMNS FROM tickets LIKE 'holder_name'")->fetch()) {
                $this->pdo->exec('ALTER TABLE tickets ADD COLUMN holder_name VARCHAR(180) NULL AFTER ticket_code');
            }
            if (!$this->pdo->query("SHOW COLUMNS FROM tickets LIKE 'holder_email'")->fetch()) {
                $this->pdo->exec('ALTER TABLE tickets ADD COLUMN holder_email VARCHAR(180) NULL AFTER holder_name');
            }
            if (!$this->pdo->query("SHOW COLUMNS FROM tickets LIKE 'ticket_email_sent_at'")->fetch()) {
                $this->pdo->exec('ALTER TABLE tickets ADD COLUMN ticket_email_sent_at DATETIME DEFAULT NULL');
            }
            if (!$this->pdo->query("SHOW COLUMNS FROM tickets LIKE 'ticket_email_error'")->fetch()) {
                $this->pdo->exec('ALTER TABLE tickets ADD COLUMN ticket_email_error TEXT DEFAULT NULL');
            }
        } catch (\Throwable) {
            // Se o usuário do banco não puder alterar tabela, seguimos sem travar o fluxo.
        }
    }

    private function storeTicketEmailResult(int $orderId, bool $sent, ?string $error): void
    {
        try {
            $this->ensureTicketEmailColumns();
            $stmt = $this->pdo->prepare(
                'UPDATE orders
                 SET ticket_email_sent_at = CASE WHEN :sent = 1 THEN NOW() ELSE ticket_email_sent_at END,
                     ticket_email_error = :error,
                     updated_at = NOW()
                 WHERE id = :orderId'
            );
            $stmt->execute([
                'sent' => $sent ? 1 : 0,
                'error' => $error,
                'orderId' => $orderId,
            ]);
        } catch (\Throwable) {
            // Não interromper confirmação de pagamento por erro de log de e-mail.
        }
    }


    private function storeSingleTicketEmailResult(int $ticketId, bool $sent, ?string $error): void
    {
        try {
            $this->ensureTicketEmailColumns();
            $stmt = $this->pdo->prepare(
                'UPDATE tickets
                 SET ticket_email_sent_at = CASE WHEN :sent = 1 THEN NOW() ELSE ticket_email_sent_at END,
                     ticket_email_error = :error
                 WHERE id = :ticketId'
            );
            $stmt->execute([
                'sent' => $sent ? 1 : 0,
                'error' => $error,
                'ticketId' => $ticketId,
            ]);
        } catch (\Throwable) {
            // Não interromper fluxo por falha ao registrar e-mail por ticket.
        }
    }

    private function renderTicketEmailText(array $order, array $tickets, ?string $recipientName = null, int $ticketIndex = 1, int $ticketTotal = 1): string
    {
        $appUrl = rtrim(env('APP_URL', self::defaultPublicUrl()) ?? self::defaultPublicUrl(), '/');
        $lines = [];
        $lines[] = 'Galpão Imperial - Ingresso liberado';
        $lines[] = 'Olá, ' . (string) ($recipientName ?: $order['customer_name']) . '.';
        $lines[] = 'Pedido: ' . (string) ($order['order_number'] ?? '');
        $lines[] = 'Evento: ' . (string) ($order['event_title'] ?? 'Evento Galpão Imperial');
        if (!empty($order['event_date'])) {
            $lines[] = 'Data: ' . date('d/m/Y', strtotime((string) $order['event_date']));
        }
        if (!empty($order['event_time'])) {
            $lines[] = 'Hora: ' . (string) $order['event_time'];
        }
        $lines[] = 'Local: ' . (string) ($order['event_location'] ?? 'Galpão Imperial');
        $lines[] = 'Ingresso: ' . $ticketIndex . '/' . $ticketTotal;
        foreach ($tickets as $ticket) {
            $ticketCode = (string) $ticket['ticket_code'];
            $qrValue = 'GALPAO_IMPERIAL:TICKET:' . $ticketCode;
            $qrImageUrl = 'https://api.qrserver.com/v1/create-qr-code/?size=700x700&margin=12&data=' . rawurlencode($qrValue);
            $lines[] = 'Código: ' . $ticketCode;
            $lines[] = 'QR Code em tela cheia: ' . $qrImageUrl;
            $lines[] = 'Dado do QR: ' . $qrValue;
        }
        $lines[] = 'Tire um print do QR Code. Se perder, a portaria consegue consultar pelo CPF ou número do pedido.';
        $lines[] = 'Site: ' . $appUrl;
        return implode("
", $lines);
    }

    private function renderTicketEmail(array $order, array $tickets, ?string $recipientName = null, int $ticketIndex = 1, int $ticketTotal = 1): string
    {
        $appUrl = rtrim(env('APP_URL', self::defaultPublicUrl()) ?? self::defaultPublicUrl(), '/');
        $buyerName = htmlspecialchars((string) ($recipientName ?: $order['customer_name']), ENT_QUOTES, 'UTF-8');
        $eventTitle = htmlspecialchars((string) ($order['event_title'] ?? 'Evento Galpão Imperial'), ENT_QUOTES, 'UTF-8');
        $orderNumber = htmlspecialchars((string) $order['order_number'], ENT_QUOTES, 'UTF-8');
        $eventDate = !empty($order['event_date']) ? date('d/m/Y', strtotime((string) $order['event_date'])) : '';
        $eventTime = htmlspecialchars((string) ($order['event_time'] ?? ''), ENT_QUOTES, 'UTF-8');
        $eventLocation = htmlspecialchars((string) ($order['event_location'] ?? 'Galpão Imperial'), ENT_QUOTES, 'UTF-8');

        $ticketBlocks = '';
        foreach ($tickets as $ticket) {
            $ticketCode = (string) $ticket['ticket_code'];
            $qrValue = 'GALPAO_IMPERIAL:TICKET:' . $ticketCode;
            $qrImageUrl = 'https://api.qrserver.com/v1/create-qr-code/?size=320x320&margin=12&data=' . rawurlencode($qrValue);
            $safeCode = htmlspecialchars($ticketCode, ENT_QUOTES, 'UTF-8');
            $safeQrValue = htmlspecialchars($qrValue, ENT_QUOTES, 'UTF-8');
            $safeBatch = htmlspecialchars((string) ($ticket['batch_name'] ?? 'Ingresso'), ENT_QUOTES, 'UTF-8');

            $ticketBlocks .= '
                <div style="margin:22px 0;padding:22px;border:1px solid #2a2a2a;border-radius:24px;background:#111111;">
                    <p style="margin:0 0 8px;color:#D4AF37;font-size:12px;font-weight:800;text-transform:uppercase;letter-spacing:2px;">' . $safeBatch . '</p>
                    <p style="margin:0 0 16px;color:#ffffff;font-size:20px;font-weight:900;">Código: ' . $safeCode . '</p>
                    <div style="background:#ffffff;padding:12px;border-radius:18px;display:inline-block;">
                        <img src="' . $qrImageUrl . '" alt="QR Code do ingresso ' . $safeCode . '" width="220" height="220" style="display:block;width:220px;height:220px;" />
                    </div>
                    <p style="margin:14px 0 0;color:#D4AF37;font-size:12px;font-weight:900;text-transform:uppercase;letter-spacing:1px;"><a href="' . $qrImageUrl . '" style="color:#D4AF37;text-decoration:none;">Abrir QR Code em tela cheia</a></p>
                    <p style="margin:16px 0 0;color:#9ca3af;font-size:11px;line-height:1.6;word-break:break-all;">Dado do QR: ' . $safeQrValue . '</p>
                </div>';
        }

        return '<!doctype html>
<html lang="pt-BR">
<head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1.0"></head>
<body style="margin:0;background:#050505;font-family:Arial,Helvetica,sans-serif;color:#ffffff;">
  <div style="max-width:680px;margin:0 auto;padding:28px 16px;">
    <div style="border:1px solid #222;border-radius:28px;background:#0b0b0b;padding:28px;">
      <p style="margin:0 0 10px;color:#D4AF37;font-size:12px;font-weight:900;text-transform:uppercase;letter-spacing:3px;">Pagamento confirmado</p>
      <h1 style="margin:0 0 16px;color:#ffffff;font-size:30px;line-height:1.1;text-transform:uppercase;">Seu ingresso está liberado</h1>
      <p style="margin:0 0 20px;color:#d1d5db;font-size:15px;line-height:1.7;">Olá, <strong>' . $buyerName . '</strong>. Recebemos seu pagamento e liberamos seu(s) ingresso(s) para o evento <strong>' . $eventTitle . '</strong>. Este e-mail contém o ingresso <strong>' . $ticketIndex . '/' . $ticketTotal . '</strong> do pedido.</p>
      <div style="padding:18px;border-radius:20px;background:#161616;margin-bottom:20px;">
        <p style="margin:0;color:#9ca3af;font-size:13px;line-height:1.8;"><strong style="color:#ffffff;">Pedido:</strong> ' . $orderNumber . '<br><strong style="color:#ffffff;">Evento:</strong> ' . $eventTitle . '<br>' . ($eventDate !== '' ? '<strong style="color:#ffffff;">Data:</strong> ' . $eventDate . '<br>' : '') . '' . ($eventTime !== '' ? '<strong style="color:#ffffff;">Hora:</strong> ' . $eventTime . '<br>' : '') . '<strong style="color:#ffffff;">Local:</strong> ' . $eventLocation . '</p>
      </div>
      ' . $ticketBlocks . '
      <div style="padding:18px;border-radius:20px;background:#1a1205;border:1px solid rgba(212,175,55,.25);margin-top:24px;">
        <p style="margin:0;color:#f5e6a8;font-size:13px;line-height:1.7;">Mostre este QR Code na portaria. Para sua segurança, tire um print deste QR Code agora. Se a imagem não carregar no Outlook/Hotmail/Gmail, clique em "Abrir QR Code em tela cheia" ou informe o código do ingresso/CPF na portaria.</p>
      </div>
      <p style="margin:24px 0 0;color:#6b7280;font-size:12px;line-height:1.6;">Também é possível acompanhar pelo site: <a href="' . htmlspecialchars($appUrl, ENT_QUOTES, 'UTF-8') . '" style="color:#D4AF37;">' . htmlspecialchars($appUrl, ENT_QUOTES, 'UTF-8') . '</a></p>
    </div>
  </div>
</body>
</html>';
    }

    private static function defaultPublicUrl(): string
    {
        $host = $_SERVER['HTTP_HOST'] ?? 'galpaoimperial.minhabagg.com.br';
        $scheme = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off') ? 'https' : 'https';
        return $scheme . '://' . $host;
    }

    public function walletOverview(bool $includePlatform = false): array
    {
        $this->ensureFinancialSchemaCompatibility();
        $this->syncFailedWithdrawalsFromAsaas();
        $settings = $this->settings();
        $organizerTransactions = $this->walletTransactions('organizer');
        $organizerBalance = $this->walletBalance('organizer');

        $data = [
            'organizer' => [
                'balance' => $organizerBalance,
                'availableForManualWithdraw' => max(0.0, $organizerBalance),
                'autoWithdrawThreshold' => (float) ($settings['organizerAutoWithdrawThreshold'] ?? 200),
                'manualWithdrawFee' => 0.0,
                'autoWithdrawFee' => 0.0,
                'pixKey' => $settings['organizerPixKey'] ?? null,
                'pixKeyType' => $settings['organizerPixKeyType'] ?? 'CPF',
                'recipientName' => $settings['organizerRecipientName'] ?? null,
                'transactions' => $organizerTransactions,
            ],
            'advancedUnlocked' => $includePlatform,
        ];

        if ($includePlatform) {
            $platformBalance = $this->walletBalance('platform');
            $data['platform'] = [
                'balance' => $platformBalance,
                'availableForManualWithdraw' => max(0.0, $platformBalance),
                'pixKey' => $settings['platformPixKey'] ?? null,
                'pixKeyType' => $settings['platformPixKeyType'] ?? 'CPF',
                'recipientName' => $settings['platformRecipientName'] ?? null,
                'transactions' => $this->walletTransactions('platform'),
            ];
        }

        return $data;
    }

    public function requestWalletWithdrawal(
        string $ownerType,
        float $amount,
        string $triggerType = 'manual',
        bool $approvedWithKey = false,
        ?string $requestedBy = null
    ): array {
        $this->ensureFinancialSchemaCompatibility();
        $ownerType = $ownerType === 'platform' ? 'platform' : 'organizer';
        $triggerType = $triggerType === 'auto' ? 'auto' : 'manual';
        $settings = $this->settings();
        $amount = round($amount, 2);

        if ($amount <= 0) {
            throw new HttpException(422, 'Informe um valor de saque maior que zero.');
        }

        $balance = $this->walletBalance($ownerType);
        if ($balance + 0.0001 < $amount) {
            throw new HttpException(422, 'Saldo insuficiente para este saque.');
        }

        $feeAmount = 0.0;
        $destinationPixKey = $settings['organizerPixKey'] ?? null;
        $destinationPixKeyType = $settings['organizerPixKeyType'] ?? 'CPF';
        $destinationName = $settings['organizerRecipientName'] ?? null;

        if ($ownerType === 'platform') {
            $destinationPixKey = $settings['platformPixKey'] ?? null;
            $destinationPixKeyType = $settings['platformPixKeyType'] ?? 'CPF';
            $destinationName = $settings['platformRecipientName'] ?? null;
        }

        if (!$destinationPixKey) {
            throw new HttpException(422, 'Configure a chave Pix de destino antes de sacar.');
        }

        $netAmount = $amount;
        $externalReference = null;
        $requestId = 0;

        $this->pdo->beginTransaction();
        try {
            $insertRequest = $this->pdo->prepare(
                'INSERT INTO withdrawal_requests (
                    owner_type, trigger_type, amount_gross, fee_amount, amount_net, destination_pix_key,
                    destination_pix_key_type, destination_name, requested_by, approved_with_key, status, metadata_json
                 ) VALUES (
                    :ownerType, :triggerType, :amountGross, :feeAmount, :amountNet, :destinationPixKey,
                    :destinationPixKeyType, :destinationName, :requestedBy, :approvedWithKey, :status, :metadataJson
                 )'
            );
            $insertRequest->execute([
                'ownerType' => $ownerType,
                'triggerType' => $triggerType,
                'amountGross' => $amount,
                'feeAmount' => $feeAmount,
                'amountNet' => $netAmount,
                'destinationPixKey' => $destinationPixKey,
                'destinationPixKeyType' => $destinationPixKeyType,
                'destinationName' => $destinationName,
                'requestedBy' => $requestedBy ?: 'Admin',
                'approvedWithKey' => $approvedWithKey ? 1 : 0,
                'status' => 'pending',
                'metadataJson' => json_encode(['balanceBefore' => $balance], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
            ]);
            $requestId = (int) $this->pdo->lastInsertId();
            $externalReference = sprintf('%s_withdraw_%d', $ownerType, $requestId);

            $updateMetadata = $this->pdo->prepare(
                'UPDATE withdrawal_requests
                 SET metadata_json = :metadataJson
                 WHERE id = :id'
            );
            $updateMetadata->execute([
                'metadataJson' => json_encode([
                    'balanceBefore' => $balance,
                    'externalReference' => $externalReference,
                ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
                'id' => $requestId,
            ]);

            $this->pdo->commit();
        } catch (\Throwable $throwable) {
            $this->pdo->rollBack();
            throw $throwable;
        }

        try {
            $asaas = new AsaasClient();
            $transferResponse = $asaas->createTransfer([
                'operationType' => 'PIX',
                'value' => $netAmount,
                'pixAddressKey' => $destinationPixKey,
                'pixAddressKeyType' => $destinationPixKeyType,
                'description' => sprintf(
                    '%s %s - Galpão Imperial',
                    $ownerType === 'platform' ? 'Saque plataforma' : 'Saque carteira',
                    str_pad((string) $requestId, 6, '0', STR_PAD_LEFT)
                ),
                'externalReference' => $externalReference,
            ]);

            $this->pdo->beginTransaction();

            $this->recordWalletTransaction(
                $ownerType,
                'debit',
                $amount,
                $triggerType === 'auto' ? 'auto_withdrawal' : 'manual_withdrawal',
                $triggerType === 'auto'
                    ? 'Repasse automático para o estabelecimento'
                    : ($ownerType === 'platform' ? 'Saque manual da plataforma' : 'Saque manual do estabelecimento'),
                'withdrawal',
                (string) $requestId,
                ['netAmount' => $netAmount, 'feeAmount' => $feeAmount]
            );

            $updateRequest = $this->pdo->prepare(
                'UPDATE withdrawal_requests
                 SET status = :status, asaas_transfer_id = :transferId, asaas_response_json = :responseJson, processed_at = NOW()
                 WHERE id = :id'
            );
            $updateRequest->execute([
                'status' => 'completed',
                'transferId' => $transferResponse['id'] ?? null,
                'responseJson' => json_encode($transferResponse, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
                'id' => $requestId,
            ]);

            $this->pdo->commit();
        } catch (\Throwable $throwable) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            if (!empty($requestId)) {
                try {
                    $failStmt = $this->pdo->prepare(
                        'UPDATE withdrawal_requests
                         SET status = :status, error_message = :errorMessage, processed_at = NOW()
                         WHERE id = :id'
                    );
                    $failStmt->execute([
                        'status' => 'failed',
                        'errorMessage' => $throwable->getMessage(),
                        'id' => $requestId,
                    ]);
                } catch (\Throwable) {
                }
            }
            throw $throwable;
        }

        return [
            'success' => true,
            'ownerType' => $ownerType,
            'triggerType' => $triggerType,
            'grossAmount' => $amount,
            'feeAmount' => $feeAmount,
            'netAmount' => $netAmount,
            'wallet' => $this->walletOverview($ownerType === 'platform'),
        ];
    }

    public function settleTicketOrderFinance(int $orderId): void
    {
        $this->ensureFinancialSchemaCompatibility();
        $existsStmt = $this->pdo->prepare(
            'SELECT COUNT(*) FROM wallet_transactions
             WHERE reference_type = :referenceType AND reference_id = :referenceId AND category = :category'
        );
        $existsStmt->execute([
            'referenceType' => 'order',
            'referenceId' => (string) $orderId,
            'category' => 'ticket_revenue',
        ]);
        if ((int) $existsStmt->fetchColumn() > 0) {
            return;
        }

        $orderStmt = $this->pdo->prepare(
            'SELECT id, order_type, order_number, quantity, subtotal_value, platform_fee_value, asaas_fee_value, organizer_net_value
             FROM orders
             WHERE id = :id AND order_type = "ticket"
             LIMIT 1'
        );
        $orderStmt->execute(['id' => $orderId]);
        $order = $orderStmt->fetch();
        if (!$order) {
            return;
        }

        $subtotalValue = round((float) ($order['subtotal_value'] ?? 0), 2);
        $platformFeeValue = round((float) ($order['platform_fee_value'] ?? 0), 2);
        $asaasFeeValue = round((float) ($order['asaas_fee_value'] ?? 0), 2);
        $organizerNetValue = round((float) ($order['organizer_net_value'] ?? max(0.0, $subtotalValue - max(0.0, $asaasFeeValue - $platformFeeValue))), 2);
        $this->pdo->beginTransaction();
        try {
            if ($organizerNetValue > 0) {
                $this->recordWalletTransaction(
                    'organizer',
                    'credit',
                    $organizerNetValue,
                    'ticket_revenue',
                    'Crédito de venda de ingresso',
                    'order',
                    (string) $orderId,
                    ['orderNumber' => $order['order_number'], 'quantity' => (int) $order['quantity']]
                );
            }

            if ($platformFeeValue > 0) {
                $this->recordWalletTransaction(
                    'platform',
                    'credit',
                    $platformFeeValue,
                    'ticket_fee',
                    'NDS Studio Code (taxa por ingresso)',
                    'order',
                    (string) $orderId,
                    ['orderNumber' => $order['order_number']]
                );
            }

            $this->pdo->commit();
        } catch (\Throwable $throwable) {
            $this->pdo->rollBack();
            throw $throwable;
        }

        // Saques reais ficam manuais por enquanto. Isso evita disparar Pix automaticamente
        // quando um pagamento e confirmado pelo webhook do Asaas.
    }

    private function syncFailedWithdrawalsFromAsaas(): void
    {
        $stmt = $this->pdo->query(
            'SELECT id, owner_type, amount_gross, asaas_transfer_id
             FROM withdrawal_requests
             WHERE asaas_transfer_id IS NOT NULL
               AND status IN ("pending", "completed")
             ORDER BY id DESC
             LIMIT 10'
        );
        $rows = $stmt ? ($stmt->fetchAll() ?: []) : [];
        if ($rows === []) {
            return;
        }

        foreach ($rows as $row) {
            $transferId = (string) ($row['asaas_transfer_id'] ?? '');
            if ($transferId === '') {
                continue;
            }

            $transfer = $this->fetchAsaasTransferStatus($transferId);
            if ($transfer === null) {
                continue;
            }

            if (($transfer['status'] ?? null) !== 'FAILED') {
                continue;
            }

            $withdrawalId = (int) $row['id'];
            $exists = $this->pdo->prepare(
                'SELECT COUNT(*) FROM wallet_transactions
                 WHERE owner_type = :ownerType
                   AND direction = "credit"
                   AND category = "withdrawal_refund"
                   AND reference_type = "withdrawal"
                   AND reference_id = :referenceId'
            );
            $exists->execute([
                'ownerType' => $row['owner_type'],
                'referenceId' => (string) $withdrawalId,
            ]);
            if ((int) $exists->fetchColumn() > 0) {
                $updateOnly = $this->pdo->prepare(
                    'UPDATE withdrawal_requests
                     SET status = "failed", error_message = :errorMessage
                     WHERE id = :id'
                );
                $updateOnly->execute([
                    'errorMessage' => (string) ($transfer['failReason'] ?? 'Transferencia recusada pelo Asaas.'),
                    'id' => $withdrawalId,
                ]);
                continue;
            }

            $this->pdo->beginTransaction();
            try {
                $this->recordWalletTransaction(
                    (string) $row['owner_type'],
                    'credit',
                    (float) $row['amount_gross'],
                    'withdrawal_refund',
                    'Estorno de saque recusado pelo Asaas',
                    'withdrawal',
                    (string) $withdrawalId,
                    [
                        'reason' => (string) ($transfer['failReason'] ?? 'Transferencia recusada pelo Asaas.'),
                        'asaasTransferId' => $transferId,
                    ]
                );

                $update = $this->pdo->prepare(
                    'UPDATE withdrawal_requests
                     SET status = "failed", error_message = :errorMessage
                     WHERE id = :id'
                );
                $update->execute([
                    'errorMessage' => (string) ($transfer['failReason'] ?? 'Transferencia recusada pelo Asaas.'),
                    'id' => $withdrawalId,
                ]);

                $this->pdo->commit();
            } catch (\Throwable $throwable) {
                $this->pdo->rollBack();
                throw $throwable;
            }
        }
    }

    private function fetchAsaasTransferStatus(string $transferId): ?array
    {
        $environment = env('ASAAS_ENVIRONMENT', 'sandbox');
        $baseUrl = $environment === 'production'
            ? 'https://api.asaas.com/v3'
            : 'https://api-sandbox.asaas.com/v3';
        $token = env('ASAAS_TRANSFER_API_KEY', env('ASAAS_API_KEY'));
        if (!$token) {
            return null;
        }

        $curl = curl_init($baseUrl . '/transfers/' . urlencode($transferId));
        if ($curl === false) {
            return null;
        }

        curl_setopt_array($curl, [
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_HTTPHEADER => [
                'accept: application/json',
                'access_token: ' . $token,
                'User-Agent: GalpaoImperial/1.0',
            ],
            CURLOPT_TIMEOUT => 8,
            CURLOPT_PROXY => '',
            CURLOPT_NOPROXY => '*',
        ]);

        $response = curl_exec($curl);
        $status = (int) curl_getinfo($curl, CURLINFO_RESPONSE_CODE);
        curl_close($curl);

        if ($response === false || $status < 200 || $status >= 300) {
            return null;
        }

        $decoded = json_decode((string) $response, true);
        return is_array($decoded) ? $decoded : null;
    }

    private function walletBalance(string $ownerType): float
    {
        $stmt = $this->pdo->prepare(
            'SELECT COALESCE(SUM(CASE WHEN direction = "credit" THEN amount ELSE -amount END), 0)
             FROM wallet_transactions
             WHERE owner_type = :ownerType'
        );
        $stmt->execute(['ownerType' => $ownerType]);
        return round((float) ($stmt->fetchColumn() ?: 0), 2);
    }

    private function walletTransactions(string $ownerType, int $limit = 20): array
    {
        $stmt = $this->pdo->prepare(
            'SELECT *
             FROM wallet_transactions
             WHERE owner_type = :ownerType
             ORDER BY created_at DESC, id DESC
             LIMIT :limit'
        );
        $stmt->bindValue('ownerType', $ownerType);
        $stmt->bindValue('limit', $limit, \PDO::PARAM_INT);
        $stmt->execute();
        $rows = $stmt->fetchAll() ?: [];

        return array_map(static function (array $row): array {
            $signedAmount = $row['direction'] === 'credit'
                ? (float) $row['amount']
                : (float) $row['amount'] * -1;

            return [
                'id' => (string) $row['id'],
                'ownerType' => $row['owner_type'],
                'direction' => $row['direction'],
                'amount' => (float) $row['amount'],
                'signedAmount' => $signedAmount,
                'category' => $row['category'],
                'description' => self::repairWalletDescription((string) $row['description']),
                'referenceType' => $row['reference_type'],
                'referenceId' => $row['reference_id'],
                'createdAt' => $row['created_at'],
                'metadata' => json_decode((string) ($row['metadata_json'] ?? 'null'), true),
            ];
        }, $rows);
    }

    private static function repairWalletDescription(string $description): string
    {
        return strtr($description, [
            'CrÃ©dito' => 'Crédito',
            'transaÃ§Ã£o' => 'transação',
            'automÃ¡tico' => 'automático',
            'PÃ¡ssaros' => 'Pássaros',
        ]);
    }

    private function recordWalletTransaction(
        string $ownerType,
        string $direction,
        float $amount,
        string $category,
        string $description,
        ?string $referenceType = null,
        ?string $referenceId = null,
        ?array $metadata = null
    ): void {
        $stmt = $this->pdo->prepare(
            'INSERT INTO wallet_transactions (
                owner_type, direction, amount, category, description, reference_type, reference_id, metadata_json
             ) VALUES (
                :ownerType, :direction, :amount, :category, :description, :referenceType, :referenceId, :metadataJson
             )'
        );
        $stmt->execute([
            'ownerType' => $ownerType,
            'direction' => $direction,
            'amount' => round($amount, 2),
            'category' => $category,
            'description' => $description,
            'referenceType' => $referenceType,
            'referenceId' => $referenceId,
            'metadataJson' => $metadata !== null ? json_encode($metadata, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) : null,
        ]);
    }

    private function ensureFinancialSchemaCompatibility(): void
    {
        try {
            if (!$this->tableExists('wallet_transactions')) {
                $this->pdo->exec(
                    "CREATE TABLE IF NOT EXISTS wallet_transactions (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        owner_type ENUM('organizer','platform') NOT NULL,
                        direction ENUM('credit','debit') NOT NULL,
                        amount DECIMAL(10,2) NOT NULL,
                        category VARCHAR(80) NOT NULL,
                        description VARCHAR(255) NOT NULL,
                        reference_type VARCHAR(60) DEFAULT NULL,
                        reference_id VARCHAR(120) DEFAULT NULL,
                        metadata_json JSON DEFAULT NULL,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    )"
                );
            }

            if (!$this->tableExists('withdrawal_requests')) {
                $this->pdo->exec(
                    "CREATE TABLE IF NOT EXISTS withdrawal_requests (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        owner_type ENUM('organizer','platform') NOT NULL,
                        trigger_type ENUM('manual','auto') NOT NULL DEFAULT 'manual',
                        amount_gross DECIMAL(10,2) NOT NULL,
                        fee_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
                        amount_net DECIMAL(10,2) NOT NULL,
                        destination_pix_key VARCHAR(255) NOT NULL,
                        destination_pix_key_type VARCHAR(40) NOT NULL,
                        destination_name VARCHAR(180) DEFAULT NULL,
                        requested_by VARCHAR(120) DEFAULT NULL,
                        approved_with_key TINYINT(1) NOT NULL DEFAULT 0,
                        status ENUM('pending','completed','failed') NOT NULL DEFAULT 'pending',
                        asaas_transfer_id VARCHAR(80) DEFAULT NULL,
                        asaas_response_json JSON DEFAULT NULL,
                        error_message TEXT DEFAULT NULL,
                        metadata_json JSON DEFAULT NULL,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        processed_at DATETIME DEFAULT NULL
                    )"
                );
            }

            $columns = [
                'site_settings' => [
                    'asaas_fee_per_order' => 'ALTER TABLE site_settings ADD COLUMN asaas_fee_per_order DECIMAL(10,2) NOT NULL DEFAULT 0.99 AFTER service_tax',
                    'organizer_pix_key' => 'ALTER TABLE site_settings ADD COLUMN organizer_pix_key VARCHAR(255) DEFAULT NULL AFTER pix_key',
                    'organizer_pix_key_type' => "ALTER TABLE site_settings ADD COLUMN organizer_pix_key_type VARCHAR(40) NOT NULL DEFAULT 'CPF' AFTER organizer_pix_key",
                    'organizer_recipient_name' => 'ALTER TABLE site_settings ADD COLUMN organizer_recipient_name VARCHAR(180) DEFAULT NULL AFTER organizer_pix_key_type',
                    'platform_pix_key' => 'ALTER TABLE site_settings ADD COLUMN platform_pix_key VARCHAR(255) DEFAULT NULL AFTER organizer_recipient_name',
                    'platform_pix_key_type' => "ALTER TABLE site_settings ADD COLUMN platform_pix_key_type VARCHAR(40) NOT NULL DEFAULT 'CPF' AFTER platform_pix_key",
                    'platform_recipient_name' => 'ALTER TABLE site_settings ADD COLUMN platform_recipient_name VARCHAR(180) DEFAULT NULL AFTER platform_pix_key_type',
                    'organizer_auto_withdraw_threshold' => 'ALTER TABLE site_settings ADD COLUMN organizer_auto_withdraw_threshold DECIMAL(10,2) NOT NULL DEFAULT 200.00',
                    'organizer_manual_withdraw_fee' => 'ALTER TABLE site_settings ADD COLUMN organizer_manual_withdraw_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00',
                    'organizer_auto_withdraw_fee' => 'ALTER TABLE site_settings ADD COLUMN organizer_auto_withdraw_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00',
                ],
                'orders' => [
                    'subtotal_value' => 'ALTER TABLE orders ADD COLUMN subtotal_value DECIMAL(10,2) DEFAULT NULL AFTER total_value',
                    'platform_fee_value' => 'ALTER TABLE orders ADD COLUMN platform_fee_value DECIMAL(10,2) DEFAULT NULL AFTER subtotal_value',
                    'asaas_fee_value' => 'ALTER TABLE orders ADD COLUMN asaas_fee_value DECIMAL(10,2) DEFAULT NULL AFTER platform_fee_value',
                    'organizer_net_value' => 'ALTER TABLE orders ADD COLUMN organizer_net_value DECIMAL(10,2) DEFAULT NULL AFTER asaas_fee_value',
                ],
                'webhook_logs' => [
                    'processing_notes' => 'ALTER TABLE webhook_logs ADD COLUMN processing_notes TEXT DEFAULT NULL',
                    'processed_at' => 'ALTER TABLE webhook_logs ADD COLUMN processed_at DATETIME DEFAULT NULL',
                ],
            ];

            foreach ($columns as $table => $tableColumns) {
                foreach ($tableColumns as $column => $sql) {
                    if (!$this->columnExistsSafe($table, $column)) {
                        $this->pdo->exec($sql);
                    }
                }
            }
        } catch (\Throwable) {
            // Em ambiente restrito, o painel deve continuar carregando mesmo sem a camada financeira estendida.
        }
    }

    private function columnExistsSafe(string $tableName, string $columnName): bool
    {
        try {
            $stmt = $this->pdo->prepare(
                'SELECT COUNT(*) FROM information_schema.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :tableName AND COLUMN_NAME = :columnName'
            );
            $stmt->execute([
                'tableName' => $tableName,
                'columnName' => $columnName,
            ]);
            return (int) $stmt->fetchColumn() > 0;
        } catch (\Throwable) {
            return false;
        }
    }

    public function galleryImages(): array
    {
        if (!$this->tableExists('gallery_images')) {
            return [];
        }

        $rows = $this->pdo->query('SELECT * FROM gallery_images ORDER BY source_type ASC, display_order ASC, id ASC')->fetchAll() ?: [];
        return array_map(static fn (array $row): array => [
            'id' => (string) $row['id'],
            'url' => $row['image_url'],
            'type' => $row['source_type'],
            'title' => $row['title'],
            'displayOrder' => (int) $row['display_order'],
        ], $rows);
    }

    public function saveGalleryImage(array $payload, ?int $imageId = null): array
    {
        if (!$this->tableExists('gallery_images')) {
            throw new HttpException(500, 'Tabela da galeria não está disponível.');
        }

        $params = [
            'url' => trim((string) ($payload['url'] ?? '')),
            'type' => ($payload['type'] ?? 'upload') === 'instagram' ? 'instagram' : 'upload',
            'title' => trim((string) ($payload['title'] ?? '')) ?: null,
            'displayOrder' => (int) ($payload['displayOrder'] ?? 0),
        ];

        if ($params['url'] === '') {
            throw new HttpException(422, 'URL da imagem é obrigatória.');
        }

        if ($imageId) {
            $stmt = $this->pdo->prepare(
                'UPDATE gallery_images
                 SET image_url = :url, source_type = :type, title = :title, display_order = :displayOrder, updated_at = NOW()
                 WHERE id = :id'
            );
            $stmt->execute($params + ['id' => $imageId]);
        } else {
            $stmt = $this->pdo->prepare(
                'INSERT INTO gallery_images (image_url, source_type, title, display_order)
                 VALUES (:url, :type, :title, :displayOrder)'
            );
            $stmt->execute($params);
            $imageId = (int) $this->pdo->lastInsertId();
        }

        foreach ($this->galleryImages() as $image) {
            if ((int) $image['id'] === $imageId) {
                return $image;
            }
        }

        throw new HttpException(500, 'Imagem salva mas não pôde ser recarregada.');
    }

    public function deleteGalleryImage(int $imageId): void
    {
        $stmt = $this->pdo->prepare('DELETE FROM gallery_images WHERE id = :id');
        $stmt->execute(['id' => $imageId]);
    }

    private function tableExists(string $tableName): bool
    {
        $stmt = $this->pdo->prepare('SHOW TABLES LIKE :tableName');
        $stmt->execute(['tableName' => $tableName]);
        return (bool) $stmt->fetchColumn();
    }
}
