<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::disableForeignKeyConstraints();

        $sql = "
            -- 9. Tabela de Tipos de Comércio
            CREATE TABLE IF NOT EXISTS business_types (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                description TEXT NULL,
                category VARCHAR(50) NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 10. Tabela de Tipos de Taxa
            CREATE TABLE IF NOT EXISTS tax_types (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                code VARCHAR(20) UNIQUE NOT NULL,
                description TEXT NULL,
                periodicity ENUM('daily', 'weekly', 'monthly', 'yearly', 'one_time') DEFAULT 'monthly',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 11. Tabela de Mercados
            CREATE TABLE IF NOT EXISTS markets (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                neighborhood_id BIGINT UNSIGNED NOT NULL,
                name VARCHAR(255) NOT NULL,
                location VARCHAR(255) NOT NULL,
                address TEXT NULL,
                capacity INT NULL,
                contact_phone VARCHAR(50) NULL,
                contact_email VARCHAR(255) NULL,
                administration JSON NULL,
                status ENUM('active', 'inactive') DEFAULT 'active',
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (neighborhood_id) REFERENCES neighborhoods(id) ON DELETE CASCADE,
                INDEX idx_markets_status (status)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 12. Tabela de Comerciantes (já existe, mas garantimos IF NOT EXISTS)
            CREATE TABLE IF NOT EXISTS merchants (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                business_type_id BIGINT UNSIGNED NULL,
                full_name VARCHAR(255) NOT NULL,
                birth_date DATE NULL,
                identity_number VARCHAR(50) UNIQUE NOT NULL,
                identity_type ENUM('BI', 'Passport') DEFAULT 'BI',
                tax_number VARCHAR(50) UNIQUE NULL,
                phone VARCHAR(50) NOT NULL,
                alternate_phone VARCHAR(50) NULL,
                email VARCHAR(255) NULL,
                address TEXT NULL,
                photo_url VARCHAR(500) NULL,
                approved_by BIGINT UNSIGNED NULL,
                approved_at TIMESTAMP NULL,
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (business_type_id) REFERENCES business_types(id) ON DELETE SET NULL,
                FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
                INDEX idx_merchants_identity (identity_number)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 13. Tabela de Bancadas/Espaços
            CREATE TABLE IF NOT EXISTS stalls (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                market_id BIGINT UNSIGNED NOT NULL,
                merchant_id BIGINT UNSIGNED NULL,
                code VARCHAR(50) NOT NULL,
                section VARCHAR(100) NULL,
                size DECIMAL(10,2) NULL,
                monthly_fee DECIMAL(10,2) NOT NULL DEFAULT 0,
                status ENUM('available', 'occupied', 'maintenance', 'reserved') DEFAULT 'available',
                occupied_since DATE NULL,
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (market_id) REFERENCES markets(id) ON DELETE CASCADE,
                FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE SET NULL,
                UNIQUE KEY unique_market_code (market_id, code),
                INDEX idx_stalls_status (status)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 14. Tabela de Taxas
            CREATE TABLE IF NOT EXISTS taxes (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                market_id BIGINT UNSIGNED NOT NULL,
                tax_type_id BIGINT UNSIGNED NOT NULL,
                business_type_id BIGINT UNSIGNED NULL,
                name VARCHAR(255) NOT NULL,
                description TEXT NULL,
                amount DECIMAL(10,2) NOT NULL,
                valid_from DATE NOT NULL,
                valid_until DATE NULL,
                status ENUM('active', 'inactive') DEFAULT 'active',
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (market_id) REFERENCES markets(id) ON DELETE CASCADE,
                FOREIGN KEY (tax_type_id) REFERENCES tax_types(id) ON DELETE CASCADE,
                FOREIGN KEY (business_type_id) REFERENCES business_types(id) ON DELETE SET NULL,
                INDEX idx_taxes_status (status)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 15. Tabela de Histórico de Ocupação
            CREATE TABLE IF NOT EXISTS stall_histories (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                stall_id BIGINT UNSIGNED NOT NULL,
                merchant_id BIGINT UNSIGNED NOT NULL,
                occupied_from DATE NOT NULL,
                occupied_until DATE NULL,
                monthly_fee DECIMAL(10,2) NOT NULL,
                notes TEXT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (stall_id) REFERENCES stalls(id) ON DELETE CASCADE,
                FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
                INDEX idx_stall_history_dates (occupied_from, occupied_until)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 16. Tabela de Cartões RFID
            CREATE TABLE IF NOT EXISTS rfid_cards (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                card_number VARCHAR(50) UNIQUE NOT NULL,
                merchant_id BIGINT UNSIGNED NOT NULL,
                issued_date DATE NOT NULL,
                expiry_date DATE NOT NULL,
                status ENUM('active', 'blocked', 'expired', 'lost') DEFAULT 'active',
                blocked_reason TEXT NULL,
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
                INDEX idx_rfid_status (status)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 17. Tabela de Contas Digitais
            CREATE TABLE IF NOT EXISTS digital_accounts (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                merchant_id BIGINT UNSIGNED NOT NULL UNIQUE,
                account_number VARCHAR(50) UNIQUE NOT NULL,
                bank_code VARCHAR(20) NULL,
                balance DECIMAL(15,2) DEFAULT 0,
                status ENUM('active', 'blocked', 'closed') DEFAULT 'active',
                opened_date DATE NOT NULL,
                closed_date DATE NULL,
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
                INDEX idx_digital_accounts_status (status)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 20. Tabela de Pagamentos
            CREATE TABLE IF NOT EXISTS payments (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                merchant_id BIGINT UNSIGNED NOT NULL,
                fiscal_id BIGINT UNSIGNED NULL,
                tax_id BIGINT UNSIGNED NOT NULL,
                rfid_card_id BIGINT UNSIGNED NULL,
                amount DECIMAL(10,2) NOT NULL,
                payment_date DATETIME NOT NULL,
                payment_method ENUM('rfid', 'cash', 'card', 'transfer') DEFAULT 'rfid',
                reference_code VARCHAR(100) UNIQUE NOT NULL,
                receipt_number VARCHAR(100) UNIQUE NULL,
                status ENUM('pending', 'completed', 'failed', 'reversed') DEFAULT 'completed',
                synced BOOLEAN DEFAULT true,
                sync_date DATETIME NULL,
                location_lat DECIMAL(10,8) NULL,
                location_lng DECIMAL(11,8) NULL,
                photo_url VARCHAR(500) NULL,
                notes TEXT NULL,
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
                FOREIGN KEY (fiscal_id) REFERENCES fiscals(id) ON DELETE SET NULL,
                FOREIGN KEY (tax_id) REFERENCES taxes(id) ON DELETE CASCADE,
                FOREIGN KEY (rfid_card_id) REFERENCES rfid_cards(id) ON DELETE SET NULL,
                INDEX idx_payments_date (payment_date),
                INDEX idx_payments_status (status),
                INDEX idx_payments_synced (synced)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 21. Tabela de Ocorrências
            CREATE TABLE IF NOT EXISTS occurrences (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                merchant_id BIGINT UNSIGNED NOT NULL,
                fiscal_id BIGINT UNSIGNED NOT NULL,
                type ENUM('infraction', 'complaint', 'incident', 'maintenance', 'other') NOT NULL,
                title VARCHAR(255) NOT NULL,
                description TEXT NOT NULL,
                severity ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
                status ENUM('open', 'in_progress', 'resolved', 'closed') DEFAULT 'open',
                resolution TEXT NULL,
                resolved_by BIGINT UNSIGNED NULL,
                resolved_at TIMESTAMP NULL,
                location_lat DECIMAL(10,8) NULL,
                location_lng DECIMAL(11,8) NULL,
                photos JSON NULL,
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
                FOREIGN KEY (fiscal_id) REFERENCES fiscals(id) ON DELETE CASCADE,
                FOREIGN KEY (resolved_by) REFERENCES users(id) ON DELETE SET NULL,
                INDEX idx_occurrences_status (status)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 22. Tabela de Movimentos Financeiros
            CREATE TABLE IF NOT EXISTS financial_movements (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                digital_account_id BIGINT UNSIGNED NOT NULL,
                payment_id BIGINT UNSIGNED NULL,
                type ENUM('credit', 'debit') NOT NULL,
                amount DECIMAL(15,2) NOT NULL,
                balance_before DECIMAL(15,2) NOT NULL,
                balance_after DECIMAL(15,2) NOT NULL,
                description VARCHAR(255) NULL,
                transaction_code VARCHAR(100) UNIQUE NULL,
                bank_reference VARCHAR(100) NULL,
                status ENUM('pending', 'completed', 'failed') DEFAULT 'completed',
                reconciled BOOLEAN DEFAULT false,
                reconciled_at TIMESTAMP NULL,
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (digital_account_id) REFERENCES digital_accounts(id) ON DELETE CASCADE,
                FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE SET NULL,
                INDEX idx_financial_movements_status (status),
                INDEX idx_financial_movements_reconciled (reconciled)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 23. Tabela de Sincronização Offline
            CREATE TABLE IF NOT EXISTS offline_syncs (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                fiscal_id BIGINT UNSIGNED NOT NULL,
                sync_type ENUM('push', 'pull') NOT NULL,
                status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
                items_count INT DEFAULT 0,
                items_processed INT DEFAULT 0,
                data JSON NULL,
                error_log TEXT NULL,
                started_at TIMESTAMP NULL,
                completed_at TIMESTAMP NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (fiscal_id) REFERENCES fiscals(id) ON DELETE CASCADE,
                INDEX idx_offline_syncs_status (status)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 24. Tabela de Notificações (renomeada para evitar conflito com a do Laravel)
            CREATE TABLE IF NOT EXISTS user_notifications (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                user_id BIGINT UNSIGNED NOT NULL,
                type VARCHAR(100) NOT NULL,
                title VARCHAR(255) NOT NULL,
                body TEXT NOT NULL,
                data JSON NULL,
                read_at TIMESTAMP NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                INDEX idx_notifications_read (read_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 25. Tabela de Logs de Auditoria (já existe, mas garantimos)
            CREATE TABLE IF NOT EXISTS audit_logs (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                user_id BIGINT UNSIGNED NULL,
                action VARCHAR(100) NOT NULL,
                entity_type VARCHAR(100) NOT NULL,
                entity_id BIGINT UNSIGNED NULL,
                old_values JSON NULL,
                new_values JSON NULL,
                ip_address VARCHAR(45) NULL,
                user_agent TEXT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
                INDEX idx_audit_logs_entity (entity_type, entity_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 26. Tabela de Configurações do Sistema (já existe, mas garantimos)
            CREATE TABLE IF NOT EXISTS system_settings (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                `key` VARCHAR(100) UNIQUE NOT NULL,
                `value` JSON NOT NULL,
                description TEXT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 27. Tabela de Integração Bancária
            CREATE TABLE IF NOT EXISTS bank_integrations (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                transaction_id VARCHAR(100) UNIQUE NOT NULL,
                payment_id BIGINT UNSIGNED NULL,
                type ENUM('payment', 'deposit', 'withdraw', 'reversal') NOT NULL,
                amount DECIMAL(15,2) NOT NULL,
                bank_code VARCHAR(20) NOT NULL,
                bank_response JSON NULL,
                status ENUM('pending', 'success', 'failed') DEFAULT 'pending',
                error_message TEXT NULL,
                processed_at TIMESTAMP NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE SET NULL,
                INDEX idx_bank_integrations_status (status)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 28. Dependentes do Comerciante
            CREATE TABLE IF NOT EXISTS merchant_dependents (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                merchant_id BIGINT UNSIGNED NOT NULL,
                full_name VARCHAR(255) NOT NULL,
                relationship VARCHAR(100) NOT NULL,
                identity_number VARCHAR(50) NULL,
                birth_date DATE NULL,
                phone VARCHAR(50) NULL,
                photo_url VARCHAR(500) NULL,
                is_active BOOLEAN DEFAULT true,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
                INDEX idx_dependents_merchant (merchant_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 29. Documentos do Comerciante
            CREATE TABLE IF NOT EXISTS merchant_documents (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                merchant_id BIGINT UNSIGNED NOT NULL,
                document_type ENUM('bi', 'nif', 'commercial_license', 'health_license', 'other') NOT NULL,
                document_number VARCHAR(100) NULL,
                file_path VARCHAR(500) NOT NULL,
                file_name VARCHAR(255) NOT NULL,
                mime_type VARCHAR(100) NULL,
                file_size INT NULL,
                issue_date DATE NULL,
                expiry_date DATE NULL,
                is_verified BOOLEAN DEFAULT false,
                verified_by BIGINT UNSIGNED NULL,
                verified_at TIMESTAMP NULL,
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
                FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL,
                INDEX idx_documents_merchant (merchant_id),
                INDEX idx_documents_expiry (expiry_date)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 30. Isenções de Taxas
            CREATE TABLE IF NOT EXISTS tax_exemptions (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                merchant_id BIGINT UNSIGNED NOT NULL,
                tax_id BIGINT UNSIGNED NOT NULL,
                exemption_type ENUM('total', 'partial') DEFAULT 'total',
                discount_percentage DECIMAL(5,2) NULL,
                reason TEXT NOT NULL,
                approved_by BIGINT UNSIGNED NOT NULL,
                valid_from DATE NOT NULL,
                valid_until DATE NULL,
                status ENUM('active', 'expired', 'cancelled') DEFAULT 'active',
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
                FOREIGN KEY (tax_id) REFERENCES taxes(id) ON DELETE CASCADE,
                FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE CASCADE,
                INDEX idx_exemptions_merchant (merchant_id),
                INDEX idx_exemptions_validity (valid_from, valid_until)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 31. Movimentos do Cartão RFID
            CREATE TABLE IF NOT EXISTS rfid_movements (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                rfid_card_id BIGINT UNSIGNED NOT NULL,
                movement_type ENUM('payment', 'load', 'block', 'unblock', 'reissue') NOT NULL,
                amount DECIMAL(15,2) NULL,
                location_lat DECIMAL(10,8) NULL,
                location_lng DECIMAL(11,8) NULL,
                device_id VARCHAR(100) NULL,
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (rfid_card_id) REFERENCES rfid_cards(id) ON DELETE CASCADE,
                INDEX idx_rfid_movements_card (rfid_card_id),
                INDEX idx_rfid_movements_created (created_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 32. Dispositivos dos Fiscais
            CREATE TABLE IF NOT EXISTS fiscal_devices (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                fiscal_id BIGINT UNSIGNED NOT NULL,
                device_id VARCHAR(100) UNIQUE NOT NULL,
                device_model VARCHAR(100) NULL,
                device_os VARCHAR(50) NULL,
                app_version VARCHAR(20) NULL,
                last_sync_at TIMESTAMP NULL,
                last_location_lat DECIMAL(10,8) NULL,
                last_location_lng DECIMAL(11,8) NULL,
                battery_level INT NULL,
                status ENUM('active', 'inactive', 'lost', 'maintenance') DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (fiscal_id) REFERENCES fiscals(id) ON DELETE CASCADE,
                INDEX idx_devices_fiscal (fiscal_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 33. Localizações dos Fiscais (tracking)
            CREATE TABLE IF NOT EXISTS fiscal_locations (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                fiscal_id BIGINT UNSIGNED NOT NULL,
                latitude DECIMAL(10,8) NOT NULL,
                longitude DECIMAL(11,8) NOT NULL,
                accuracy DECIMAL(10,2) NULL,
                recorded_at TIMESTAMP NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (fiscal_id) REFERENCES fiscals(id) ON DELETE CASCADE,
                INDEX idx_locations_fiscal (fiscal_id),
                INDEX idx_locations_time (recorded_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 34. Jornada dos Fiscais
            CREATE TABLE IF NOT EXISTS fiscal_journeys (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                fiscal_id BIGINT UNSIGNED NOT NULL,
                start_time TIMESTAMP NOT NULL,
                end_time TIMESTAMP NULL,
                start_lat DECIMAL(10,8) NULL,
                start_lng DECIMAL(11,8) NULL,
                end_lat DECIMAL(10,8) NULL,
                end_lng DECIMAL(11,8) NULL,
                total_payments INT DEFAULT 0,
                total_amount DECIMAL(15,2) DEFAULT 0,
                total_occurrences INT DEFAULT 0,
                distance_covered DECIMAL(10,2) NULL,
                status ENUM('active', 'completed', 'interrupted') DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (fiscal_id) REFERENCES fiscals(id) ON DELETE CASCADE,
                INDEX idx_journeys_fiscal (fiscal_id),
                INDEX idx_journeys_period (start_time, end_time)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 35. Métodos de Pagamento
            CREATE TABLE IF NOT EXISTS payment_methods (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                code VARCHAR(50) UNIQUE NOT NULL,
                name VARCHAR(100) NOT NULL,
                description TEXT NULL,
                icon VARCHAR(255) NULL,
                is_active BOOLEAN DEFAULT true,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 36. Conciliação de Pagamentos
            CREATE TABLE IF NOT EXISTS payment_reconciliations (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                payment_id BIGINT UNSIGNED NOT NULL,
                bank_transaction_id VARCHAR(100) NOT NULL,
                reconciliation_date TIMESTAMP NOT NULL,
                bank_reference VARCHAR(100) NULL,
                bank_response JSON NULL,
                status ENUM('pending', 'matched', 'mismatched', 'failed') DEFAULT 'pending',
                mismatch_reason TEXT NULL,
                reconciled_by BIGINT UNSIGNED NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE,
                FOREIGN KEY (reconciled_by) REFERENCES users(id) ON DELETE SET NULL,
                INDEX idx_reconciliation_payment (payment_id),
                INDEX idx_reconciliation_status (status)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 37. Tipos de Ocorrência
            CREATE TABLE IF NOT EXISTS occurrence_types (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                code VARCHAR(50) UNIQUE NOT NULL,
                name VARCHAR(100) NOT NULL,
                description TEXT NULL,
                severity ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
                requires_photo BOOLEAN DEFAULT false,
                requires_signature BOOLEAN DEFAULT false,
                notification_template TEXT NULL,
                is_active BOOLEAN DEFAULT true,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 38. Anexos de Ocorrências
            CREATE TABLE IF NOT EXISTS occurrence_attachments (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                occurrence_id BIGINT UNSIGNED NOT NULL,
                file_path VARCHAR(500) NOT NULL,
                file_name VARCHAR(255) NOT NULL,
                file_type ENUM('photo', 'document', 'audio', 'video') NOT NULL,
                mime_type VARCHAR(100) NULL,
                file_size INT NULL,
                uploaded_by BIGINT UNSIGNED NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (occurrence_id) REFERENCES occurrences(id) ON DELETE CASCADE,
                FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE,
                INDEX idx_attachments_occurrence (occurrence_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 39. Templates de Notificação
            CREATE TABLE IF NOT EXISTS notification_templates (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                code VARCHAR(50) UNIQUE NOT NULL,
                name VARCHAR(100) NOT NULL,
                type ENUM('sms', 'email', 'push', 'system') NOT NULL,
                subject VARCHAR(255) NULL,
                content TEXT NOT NULL,
                variables JSON NULL,
                is_active BOOLEAN DEFAULT true,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 40. Filas de Notificação
            CREATE TABLE IF NOT EXISTS notification_queue (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                notification_id BIGINT UNSIGNED NOT NULL,
                recipient_type ENUM('user', 'merchant', 'fiscal', 'phone', 'email') NOT NULL,
                recipient_id BIGINT UNSIGNED NULL,
                recipient_address VARCHAR(255) NOT NULL,
                channel ENUM('sms', 'email', 'push') NOT NULL,
                subject VARCHAR(255) NULL,
                content TEXT NOT NULL,
                status ENUM('pending', 'sent', 'failed', 'cancelled') DEFAULT 'pending',
                sent_at TIMESTAMP NULL,
                error_message TEXT NULL,
                retry_count INT DEFAULT 0,
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (notification_id) REFERENCES user_notifications(id) ON DELETE CASCADE,
                INDEX idx_queue_status (status),
                INDEX idx_queue_recipient (recipient_type, recipient_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 41. Templates de Relatório
            CREATE TABLE IF NOT EXISTS report_templates (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                code VARCHAR(50) UNIQUE NOT NULL,
                type ENUM('revenue', 'occupation', 'defaulters', 'occurrences', 'custom') NOT NULL,
                configuration JSON NOT NULL,
                created_by BIGINT UNSIGNED NOT NULL,
                is_public BOOLEAN DEFAULT false,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
                INDEX idx_templates_type (type)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 42. Atribuições de Fiscais a Mercados
            CREATE TABLE IF NOT EXISTS fiscal_market_assignments (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                fiscal_id BIGINT UNSIGNED NOT NULL,
                market_id BIGINT UNSIGNED NOT NULL,
                assigned_zones JSON NULL,
                primary_fiscal BOOLEAN DEFAULT false,
                start_date DATE NOT NULL,
                end_date DATE NULL,
                notes TEXT NULL,
                status ENUM('active', 'inactive') DEFAULT 'active',
                assigned_by BIGINT UNSIGNED NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (fiscal_id) REFERENCES fiscals(id) ON DELETE CASCADE,
                FOREIGN KEY (market_id) REFERENCES markets(id) ON DELETE CASCADE,
                FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE CASCADE,
                UNIQUE KEY unique_fiscal_market_active (fiscal_id, market_id, status),
                INDEX idx_assignments_fiscal (fiscal_id),
                INDEX idx_assignments_market (market_id),
                INDEX idx_assignments_status (status),
                INDEX idx_assignments_dates (start_date, end_date)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 43. Histórico de Atribuições
            CREATE TABLE IF NOT EXISTS fiscal_assignment_history (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                assignment_id BIGINT UNSIGNED NOT NULL,
                action ENUM('assigned', 'updated', 'removed', 'expired') NOT NULL,
                old_data JSON NULL,
                new_data JSON NULL,
                performed_by BIGINT UNSIGNED NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (assignment_id) REFERENCES fiscal_market_assignments(id) ON DELETE CASCADE,
                FOREIGN KEY (performed_by) REFERENCES users(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 44. Tabela de Espécies de Peixe
            CREATE TABLE IF NOT EXISTS fish_species (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                code VARCHAR(50) NOT NULL UNIQUE,
                scientific_name VARCHAR(255) NOT NULL,
                common_name_pt VARCHAR(100) NOT NULL,
                common_name_en VARCHAR(100) NULL,
                family VARCHAR(100) NULL,
                category ENUM('pelagic', 'demersal', 'crustacean', 'mollusk', 'other') NOT NULL,
                commercial_value ENUM('low', 'medium', 'high', 'very_high') DEFAULT 'medium',
                minimum_size_cm DECIMAL(10,2) NULL,
                closed_season_start DATE NULL,
                closed_season_end DATE NULL,
                quota_limit_kg DECIMAL(10,2) NULL,
                is_protected BOOLEAN DEFAULT FALSE,
                is_endangered BOOLEAN DEFAULT FALSE,
                image_url VARCHAR(500) NULL,
                description TEXT NULL,
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_species_code (code),
                INDEX idx_species_category (category)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 45. Tabela de Tipos de Pesca
            CREATE TABLE IF NOT EXISTS fishing_types (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                code VARCHAR(50) NOT NULL UNIQUE,
                name VARCHAR(100) NOT NULL,
                description TEXT NULL,
                category ENUM('artisanal', 'semi_industrial', 'industrial') NOT NULL,
                allowed_gear TEXT NULL,
                prohibited_gear TEXT NULL,
                minimum_size_cm DECIMAL(10,2) NULL,
                requires_license BOOLEAN DEFAULT TRUE,
                quota_limit_kg DECIMAL(10,2) NULL,
                is_active BOOLEAN DEFAULT TRUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_fishing_types_category (category)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 46. Tabela de Artes de Pesca
            CREATE TABLE IF NOT EXISTS fishing_gears (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                code VARCHAR(50) NOT NULL UNIQUE,
                name VARCHAR(100) NOT NULL,
                description TEXT NULL,
                category ENUM('net', 'line', 'trap', 'harpoon', 'other') NOT NULL,
                is_allowed BOOLEAN DEFAULT TRUE,
                mesh_size_min_cm DECIMAL(10,2) NULL,
                requires_license BOOLEAN DEFAULT FALSE,
                restrictions TEXT NULL,
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_gears_category (category)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 47. Tabela de Pescadores
            CREATE TABLE IF NOT EXISTS fishermen (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                merchant_id BIGINT UNSIGNED NOT NULL UNIQUE,
                registration_number VARCHAR(50) NOT NULL UNIQUE,
                fishing_license_number VARCHAR(50) NULL UNIQUE,
                license_expiry_date DATE NULL,
                professional_category ENUM('artisanal', 'semi_industrial', 'industrial', 'recreational') DEFAULT 'artisanal',
                fishing_zone VARCHAR(100) NULL,
                vessel_owner BOOLEAN DEFAULT FALSE,
                dependent_fishermen INT DEFAULT 0,
                next_of_kin_name VARCHAR(255) NULL,
                next_of_kin_phone VARCHAR(50) NULL,
                next_of_kin_relationship VARCHAR(100) NULL,
                blood_type VARCHAR(5) NULL,
                emergency_contact_phone VARCHAR(50) NULL,
                health_insurance_number VARCHAR(100) NULL,
                health_insurance_expiry DATE NULL,
                training_certificate VARCHAR(100) NULL,
                training_certificate_expiry DATE NULL,
                medical_certificate VARCHAR(100) NULL,
                medical_certificate_expiry DATE NULL,
                status ENUM('pending', 'approved', 'active', 'suspended', 'expired', 'deceased', 'inactive') NOT NULL DEFAULT 'pending',
                deactivated_at TIMESTAMP NULL,
                deactivated_reason TEXT NULL,
                approved_at TIMESTAMP NULL,
                approved_by BIGINT UNSIGNED NULL,
                suspended_reason TEXT NULL,
                suspended_at TIMESTAMP NULL,
                suspended_by BIGINT UNSIGNED NULL,
                suspended_until DATE NULL,
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
                FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
                FOREIGN KEY (suspended_by) REFERENCES users(id) ON DELETE SET NULL,
                INDEX idx_fishermen_registration (registration_number),
                INDEX idx_fishermen_license (fishing_license_number),
                INDEX idx_fishermen_status (status),
                INDEX idx_fishermen_license_expiry (license_expiry_date)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 48. Tabela de Embarcações
            CREATE TABLE IF NOT EXISTS vessels (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                owner_fisherman_id BIGINT UNSIGNED NOT NULL,
                registration_number VARCHAR(50) NOT NULL UNIQUE,
                vessel_name VARCHAR(255) NOT NULL,
                vessel_type ENUM('canoa', 'lancha', 'bote', 'traineira', 'arrastão', 'outro') NOT NULL,
                construction_material ENUM('madeira', 'fibra', 'aluminio', 'aco') DEFAULT 'madeira',
                length DECIMAL(10,2) NULL,
                width DECIMAL(10,2) NULL,
                depth DECIMAL(10,2) NULL,
                gross_tonnage DECIMAL(10,2) NULL,
                engine_power DECIMAL(10,2) NULL,
                engine_type VARCHAR(100) NULL,
                engine_serial_number VARCHAR(100) NULL,
                capacity_kg DECIMAL(10,2) NOT NULL,
                max_crew INT NOT NULL,
                fishing_gear VARCHAR(255) NULL,
                communication_equipment JSON NULL,
                safety_equipment JSON NULL,
                navigation_equipment JSON NULL,
                inspection_certificate VARCHAR(100) NULL,
                inspection_certificate_expiry DATE NULL,
                insurance_policy_number VARCHAR(100) NULL,
                insurance_expiry_date DATE NULL,
                fishing_license_number VARCHAR(100) NULL,
                fishing_license_expiry DATE NULL,
                color VARCHAR(50) NULL,
                photo_url VARCHAR(500) NULL,
                registration_photo_url VARCHAR(500) NULL,
                status ENUM('active', 'maintenance', 'damaged', 'decommissioned', 'suspended') DEFAULT 'active',
                notes TEXT NULL,
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (owner_fisherman_id) REFERENCES fishermen(id) ON DELETE CASCADE,
                INDEX idx_vessels_registration (registration_number),
                INDEX idx_vessels_owner (owner_fisherman_id),
                INDEX idx_vessels_status (status),
                INDEX idx_vessels_license_expiry (fishing_license_expiry)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 49. Tabela de Tripulação das Embarcações
            CREATE TABLE IF NOT EXISTS vessel_crews (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                vessel_id BIGINT UNSIGNED NOT NULL,
                fisherman_id BIGINT UNSIGNED NOT NULL,
                role ENUM('captain', 'engineer', 'navigator', 'fisherman', 'cook', 'other') NOT NULL,
                is_primary BOOLEAN DEFAULT FALSE,
                joined_at DATE NOT NULL,
                left_at DATE NULL,
                notes TEXT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (vessel_id) REFERENCES vessels(id) ON DELETE CASCADE,
                FOREIGN KEY (fisherman_id) REFERENCES fishermen(id) ON DELETE CASCADE,
                UNIQUE KEY unique_vessel_fisherman_active (vessel_id, fisherman_id, left_at),
                INDEX idx_crew_vessel (vessel_id),
                INDEX idx_crew_fisherman (fisherman_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 50. Tabela de Saídas para Pesca (Boardings)
            CREATE TABLE IF NOT EXISTS boardings (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                vessel_id BIGINT UNSIGNED NOT NULL,
                captain_id BIGINT UNSIGNED NOT NULL,
                fishing_type_id BIGINT UNSIGNED NOT NULL,
                departure_datetime TIMESTAMP NOT NULL,
                expected_return_datetime TIMESTAMP NULL,
                actual_return_datetime TIMESTAMP NULL,
                departure_location VARCHAR(255) NOT NULL,
                departure_point_id BIGINT UNSIGNED NULL,
                crew_count INT NOT NULL,
                crew_list JSON NULL,
                fishing_zones VARCHAR(500) NULL,
                expected_catch JSON NULL,
                fuel_quantity DECIMAL(10,2) NULL,
                ice_quantity DECIMAL(10,2) NULL,
                supplies_notes TEXT NULL,
                fiscal_id BIGINT UNSIGNED NULL,
                weather_conditions VARCHAR(255) NULL,
                sea_conditions VARCHAR(255) NULL,
                status ENUM('scheduled', 'departed', 'at_sea', 'returned', 'overdue', 'cancelled', 'emergency') DEFAULT 'scheduled',
                emergency_contact_made BOOLEAN DEFAULT FALSE,
                emergency_notes TEXT NULL,
                notes TEXT NULL,
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (vessel_id) REFERENCES vessels(id) ON DELETE CASCADE,
                FOREIGN KEY (captain_id) REFERENCES fishermen(id) ON DELETE CASCADE,
                FOREIGN KEY (fishing_type_id) REFERENCES fishing_types(id) ON DELETE CASCADE,
                FOREIGN KEY (departure_point_id) REFERENCES fishing_points(id) ON DELETE SET NULL,
                FOREIGN KEY (fiscal_id) REFERENCES fiscals(id) ON DELETE SET NULL,
                INDEX idx_boardings_status (status),
                INDEX idx_boardings_departure (departure_datetime),
                INDEX idx_boardings_vessel (vessel_id),
                INDEX idx_boardings_captain (captain_id),
                INDEX idx_boardings_overdue (status, expected_return_datetime)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 51. Tabela de Desembarques (Landings)
            CREATE TABLE IF NOT EXISTS landings (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                boarding_id BIGINT UNSIGNED NOT NULL UNIQUE,
                landing_datetime TIMESTAMP NOT NULL,
                landing_location VARCHAR(255) NOT NULL,
                landing_point_id BIGINT UNSIGNED NULL,
                total_weight_kg DECIMAL(10,2) NOT NULL,
                total_value_estimate DECIMAL(15,2) NULL,
                crew_count INT NOT NULL,
                weather_conditions VARCHAR(255) NULL,
                sea_conditions VARCHAR(255) NULL,
                fuel_consumption DECIMAL(10,2) NULL,
                fishing_duration_hours DECIMAL(10,2) NULL,
                fishing_depth_meters DECIMAL(10,2) NULL,
                water_temperature DECIMAL(5,2) NULL,
                catch_declaration JSON NOT NULL,
                catch_by_species JSON NULL,
                discards_kg DECIMAL(10,2) NULL,
                discarded_reason TEXT NULL,
                bycatch_kg DECIMAL(10,2) NULL,
                illegal_catch_detected BOOLEAN DEFAULT FALSE,
                illegal_catch_details TEXT NULL,
                inspection_notes TEXT NULL,
                fiscal_id BIGINT UNSIGNED NULL,
                photos JSON NULL,
                notes TEXT NULL,
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (boarding_id) REFERENCES boardings(id) ON DELETE CASCADE,
                FOREIGN KEY (landing_point_id) REFERENCES fishing_points(id) ON DELETE SET NULL,
                FOREIGN KEY (fiscal_id) REFERENCES fiscals(id) ON DELETE SET NULL,
                INDEX idx_landings_datetime (landing_datetime),
                INDEX idx_landings_boarding (boarding_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 52. Tabela de Declarações de Captura
            CREATE TABLE IF NOT EXISTS catch_declarations (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                landing_id BIGINT UNSIGNED NOT NULL,
                species_code VARCHAR(50) NOT NULL,
                species_name VARCHAR(100) NOT NULL,
                quantity_kg DECIMAL(10,2) NOT NULL,
                quantity_units INT NULL,
                average_weight_kg DECIMAL(10,2) NULL,
                size_min_cm DECIMAL(10,2) NULL,
                size_max_cm DECIMAL(10,2) NULL,
                size_avg_cm DECIMAL(10,2) NULL,
                quality ENUM('excellent', 'good', 'fair', 'poor') DEFAULT 'good',
                price_estimate DECIMAL(10,2) NULL,
                total_value DECIMAL(15,2) NULL,
                fishing_method VARCHAR(100) NULL,
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (landing_id) REFERENCES landings(id) ON DELETE CASCADE,
                INDEX idx_catch_landing (landing_id),
                INDEX idx_catch_species (species_code)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 53. Tabela de Licenças de Pesca
            CREATE TABLE IF NOT EXISTS fishing_licenses (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                vessel_id BIGINT UNSIGNED NULL,
                fisherman_id BIGINT UNSIGNED NULL,
                fishing_type_id BIGINT UNSIGNED NOT NULL,
                license_number VARCHAR(100) NOT NULL UNIQUE,
                issue_date DATE NOT NULL,
                expiry_date DATE NOT NULL,
                authorized_zones JSON NULL,
                authorized_species JSON NULL,
                quota_limit_kg DECIMAL(10,2) NULL,
                cost DECIMAL(10,2) NOT NULL,
                payment_id BIGINT UNSIGNED NULL,
                issued_by BIGINT UNSIGNED NOT NULL,
                status ENUM('active', 'expired', 'suspended', 'cancelled') DEFAULT 'active',
                suspension_reason TEXT NULL,
                suspended_at TIMESTAMP NULL,
                suspended_by BIGINT UNSIGNED NULL,
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (vessel_id) REFERENCES vessels(id) ON DELETE CASCADE,
                FOREIGN KEY (fisherman_id) REFERENCES fishermen(id) ON DELETE CASCADE,
                FOREIGN KEY (fishing_type_id) REFERENCES fishing_types(id) ON DELETE CASCADE,
                FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE SET NULL,
                FOREIGN KEY (issued_by) REFERENCES users(id) ON DELETE CASCADE,
                FOREIGN KEY (suspended_by) REFERENCES users(id) ON DELETE SET NULL,
                INDEX idx_licenses_number (license_number),
                INDEX idx_licenses_expiry (expiry_date),
                INDEX idx_licenses_status (status)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 54. Tabela de Permissões de Pesca
            CREATE TABLE IF NOT EXISTS fishing_permissions (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                fishing_type_id BIGINT UNSIGNED NOT NULL,
                species_id BIGINT UNSIGNED NULL,
                zone_description VARCHAR(255) NOT NULL,
                coordinates JSON NULL,
                allowed_from_date DATE NOT NULL,
                allowed_to_date DATE NOT NULL,
                allowed_days_of_week JSON NULL,
                allowed_time_start TIME NULL,
                allowed_time_end TIME NULL,
                max_vessels INT NULL,
                quota_per_vessel DECIMAL(10,2) NULL,
                notes TEXT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (fishing_type_id) REFERENCES fishing_types(id) ON DELETE CASCADE,
                FOREIGN KEY (species_id) REFERENCES fish_species(id) ON DELETE SET NULL,
                INDEX idx_permissions_dates (allowed_from_date, allowed_to_date),
                INDEX idx_permissions_type (fishing_type_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 55. Tabela de Pontos de Pesca (Portos, Praias, Cais)
            CREATE TABLE IF NOT EXISTS fishing_points (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                market_id BIGINT UNSIGNED NULL,
                neighborhood_id BIGINT UNSIGNED NOT NULL,
                name VARCHAR(255) NOT NULL,
                type ENUM('beach', 'port', 'pier', 'market', 'other') NOT NULL,
                latitude DECIMAL(10,8) NOT NULL,
                longitude DECIMAL(11,8) NOT NULL,
                address TEXT NULL,
                capacity_vessels INT NULL,
                has_ice_plant BOOLEAN DEFAULT FALSE,
                has_cold_storage BOOLEAN DEFAULT FALSE,
                has_weighing_scale BOOLEAN DEFAULT FALSE,
                has_lighting BOOLEAN DEFAULT FALSE,
                has_restroom BOOLEAN DEFAULT FALSE,
                has_parking BOOLEAN DEFAULT FALSE,
                contact_phone VARCHAR(50) NULL,
                contact_name VARCHAR(255) NULL,
                operating_hours JSON NULL,
                status ENUM('active', 'inactive', 'under_maintenance') DEFAULT 'active',
                notes TEXT NULL,
                metadata JSON NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (market_id) REFERENCES markets(id) ON DELETE SET NULL,
                FOREIGN KEY (neighborhood_id) REFERENCES neighborhoods(id) ON DELETE CASCADE,
                INDEX idx_fishing_points_type (type),
                INDEX idx_fishing_points_location (latitude, longitude)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            -- 56. Tabela de Infrações de Pesca
            CREATE TABLE IF NOT EXISTS fishing_infractions (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                occurrence_id BIGINT UNSIGNED NULL,
                boarding_id BIGINT UNSIGNED NULL,
                landing_id BIGINT UNSIGNED NULL,
                fisherman_id BIGINT UNSIGNED NOT NULL,
                vessel_id BIGINT UNSIGNED NULL,
                infraction_type ENUM('illegal_fishing', 'undersized_fish', 'forbidden_gear', 'closed_season', 'license_expired', 'exceeded_quota', 'illegal_species', 'fishing_zone_violation', 'documentation_issue', 'other') NOT NULL,
                description TEXT NOT NULL,
                evidence JSON NULL,
                penalty_amount DECIMAL(10,2) NULL,
                penalty_paid BOOLEAN DEFAULT FALSE,
                penalty_payment_id BIGINT UNSIGNED NULL,
                suspension_days INT NULL,
                warning_issued BOOLEAN DEFAULT FALSE,
                warning_notes TEXT NULL,
                status ENUM('pending', 'under_review', 'penalized', 'appealed', 'dismissed') DEFAULT 'pending',
                reviewed_by BIGINT UNSIGNED NULL,
                reviewed_at TIMESTAMP NULL,
                penalty_notes TEXT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (occurrence_id) REFERENCES occurrences(id) ON DELETE SET NULL,
                FOREIGN KEY (boarding_id) REFERENCES boardings(id) ON DELETE SET NULL,
                FOREIGN KEY (landing_id) REFERENCES landings(id) ON DELETE SET NULL,
                FOREIGN KEY (fisherman_id) REFERENCES fishermen(id) ON DELETE CASCADE,
                FOREIGN KEY (vessel_id) REFERENCES vessels(id) ON DELETE SET NULL,
                FOREIGN KEY (penalty_payment_id) REFERENCES payments(id) ON DELETE SET NULL,
                FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
                INDEX idx_infractions_fisherman (fisherman_id),
                INDEX idx_infractions_status (status)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        ";

        DB::unprepared($sql);

        Schema::enableForeignKeyConstraints();
    }

    public function down(): void
    {
        Schema::disableForeignKeyConstraints();

        // Drop tables in reverse order (simplified, you can add more if needed)
        $tables = [
            'fishing_infractions', 'fishing_permissions', 'fishing_licenses',
            'catch_declarations', 'landings', 'boardings', 'vessel_crews',
            'vessels', 'fishermen', 'fishing_gears', 'fishing_types',
            'fish_species', 'fiscal_assignment_history', 'fiscal_market_assignments',
            'report_templates', 'notification_queue', 'notification_templates',
            'occurrence_attachments', 'occurrence_types', 'payment_reconciliations',
            'payment_methods', 'fiscal_journeys', 'fiscal_locations',
            'fiscal_devices', 'rfid_movements', 'tax_exemptions',
            'merchant_documents', 'merchant_dependents', 'bank_integrations',
            'financial_movements', 'occurrences', 'payments',
            'digital_accounts', 'rfid_cards', 'stall_histories',
            'taxes', 'stalls', 'merchants', 'markets',
            'tax_types', 'business_types', 'user_notifications',
        ];

        foreach ($tables as $table) {
            Schema::dropIfExists($table);
        }

        Schema::enableForeignKeyConstraints();
    }
};
