<?php
function migrate_random_public_user_ids(PDO $db): void {
    $columns = $db->query('PRAGMA table_info(users)')->fetchAll(PDO::FETCH_ASSOC);
    $hasPublicId = false;
    foreach ($columns as $column) {
        if (($column['name'] ?? '') === 'public_id') { $hasPublicId = true; break; }
    }
    if (!$hasPublicId) return;

    $rows = $db->query("SELECT id,public_id FROM users ORDER BY id")->fetchAll(PDO::FETCH_ASSOC);
    $update = $db->prepare('UPDATE users SET public_id=? WHERE id=?');
    foreach ($rows as $row) {
        $current = strtoupper(trim((string)($row['public_id'] ?? '')));
        // IDs antigos sequenciais (LXU-000001...) e vazios são trocados uma única vez.
        if ($current !== '' && public_user_id_is_random($current)) continue;
        $publicId = create_unique_public_user_id($db);
        $update->execute([$publicId,(int)$row['id']]);
    }

    // client_public_id é apenas um snapshot; o relacionamento real continua pelo client_id interno.
    $deliveryColumns = $db->query('PRAGMA table_info(delivery_jobs)')->fetchAll(PDO::FETCH_ASSOC);
    $names = array_column($deliveryColumns, 'name');
    if (in_array('client_public_id', $names, true) && in_array('client_id', $names, true)) {
        $db->exec("UPDATE delivery_jobs SET client_public_id=(SELECT u.public_id FROM users u WHERE u.id=delivery_jobs.client_id) WHERE client_id IS NOT NULL AND EXISTS(SELECT 1 FROM users u WHERE u.id=delivery_jobs.client_id)");
    }
}

function ensure_column(PDO $db, string $table, string $column, string $definition): void {
    $columns = $db->query('PRAGMA table_info(' . $table . ')')->fetchAll(PDO::FETCH_ASSOC);
    foreach ($columns as $item) {
        if (isset($item['name']) && $item['name'] === $column) return;
    }
    $db->exec('ALTER TABLE ' . $table . ' ADD COLUMN ' . $column . ' ' . $definition);
}

function migrate_database(PDO $db) {
    $db->exec('CREATE TABLE IF NOT EXISTS settings (
        key_name TEXT PRIMARY KEY,
        value TEXT,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS plans (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        price REAL NOT NULL DEFAULT 0,
        duration_days INTEGER NOT NULL DEFAULT 30,
        features_json TEXT,
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS companies (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        owner_user_id INTEGER,
        plan_id INTEGER,
        type TEXT NOT NULL DEFAULT "delivery",
        name TEXT NOT NULL,
        slug TEXT UNIQUE,
        document TEXT,
        phone TEXT,
        email TEXT,
        address TEXT,
        city TEXT,
        state TEXT,
        logo TEXT,
        banner TEXT,
        description TEXT,
        open_status TEXT NOT NULL DEFAULT "open",
        status TEXT NOT NULL DEFAULT "pending",
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER,
        role TEXT NOT NULL,
        name TEXT NOT NULL,
        email TEXT NOT NULL UNIQUE,
        phone TEXT,
        document TEXT,
        password_hash TEXT NOT NULL,
        avatar TEXT,
        status TEXT NOT NULL DEFAULT "pending",
        work_status TEXT NOT NULL DEFAULT "offline",
        subscription_expires_at TEXT,
        last_seen TEXT,
        last_ip TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS subscriptions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        company_id INTEGER,
        plan_id INTEGER NOT NULL,
        start_date TEXT NOT NULL,
        end_date TEXT NOT NULL,
        amount REAL NOT NULL DEFAULT 0,
        payment_method TEXT,
        status TEXT NOT NULL DEFAULT "pending",
        notes TEXT,
        created_at TEXT NOT NULL
    )');

    // V2.8.30 — solicitações de plano feitas pelo usuário.
    $db->exec('CREATE TABLE IF NOT EXISTS plan_requests (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        company_id INTEGER,
        target_plan_id INTEGER NOT NULL,
        request_type TEXT NOT NULL DEFAULT "add",
        status TEXT NOT NULL DEFAULT "pending",
        user_notes TEXT,
        admin_notes TEXT,
        reviewed_by INTEGER,
        reviewed_at TEXT,
        subscription_id INTEGER,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_plan_requests_user ON plan_requests(user_id,status,created_at)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_plan_requests_status ON plan_requests(status,created_at)');

    $db->exec('CREATE TABLE IF NOT EXISTS categories (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER,
        module TEXT NOT NULL,
        name TEXT NOT NULL,
        icon TEXT,
        status TEXT NOT NULL DEFAULT "active",
        sort_order INTEGER NOT NULL DEFAULT 0,
        created_at TEXT NOT NULL
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        category_id INTEGER,
        name TEXT NOT NULL,
        description TEXT,
        price REAL NOT NULL DEFAULT 0,
        stock REAL NOT NULL DEFAULT 0,
        unit TEXT NOT NULL DEFAULT "un",
        image TEXT,
        status TEXT NOT NULL DEFAULT "active",
        featured INTEGER NOT NULL DEFAULT 0,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        client_id INTEGER,
        delivery_user_id INTEGER,
        order_number TEXT NOT NULL UNIQUE,
        status TEXT NOT NULL DEFAULT "received",
        subtotal REAL NOT NULL DEFAULT 0,
        delivery_fee REAL NOT NULL DEFAULT 0,
        total REAL NOT NULL DEFAULT 0,
        payment_method TEXT,
        payment_status TEXT NOT NULL DEFAULT "pending",
        address TEXT,
        notes TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS order_items (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        order_id INTEGER NOT NULL,
        product_id INTEGER,
        name TEXT NOT NULL,
        qty REAL NOT NULL DEFAULT 1,
        price REAL NOT NULL DEFAULT 0,
        total REAL NOT NULL DEFAULT 0
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS services (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        provider_id INTEGER NOT NULL,
        category_id INTEGER,
        name TEXT NOT NULL,
        description TEXT,
        price REAL NOT NULL DEFAULT 0,
        price_type TEXT NOT NULL DEFAULT "fixed",
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS service_requests (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        client_id INTEGER NOT NULL,
        provider_id INTEGER NOT NULL,
        service_id INTEGER NOT NULL,
        status TEXT NOT NULL DEFAULT "requested",
        address TEXT,
        scheduled_at TEXT,
        amount REAL NOT NULL DEFAULT 0,
        notes TEXT,
        rating INTEGER,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS rides (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        client_id INTEGER NOT NULL,
        driver_id INTEGER,
        ride_type TEXT NOT NULL,
        pickup TEXT NOT NULL,
        destination TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT "requested",
        estimated_value REAL NOT NULL DEFAULT 0,
        distance_km REAL NOT NULL DEFAULT 0,
        payment_method TEXT,
        scheduled_at TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS properties (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        owner_id INTEGER NOT NULL,
        title TEXT NOT NULL,
        type TEXT NOT NULL,
        purpose TEXT NOT NULL DEFAULT "rent",
        description TEXT,
        address TEXT,
        city TEXT,
        state TEXT,
        price REAL NOT NULL DEFAULT 0,
        bedrooms INTEGER NOT NULL DEFAULT 0,
        bathrooms INTEGER NOT NULL DEFAULT 0,
        garage INTEGER NOT NULL DEFAULT 0,
        area REAL NOT NULL DEFAULT 0,
        image TEXT,
        status TEXT NOT NULL DEFAULT "available",
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS property_bookings (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        property_id INTEGER NOT NULL,
        client_id INTEGER NOT NULL,
        booking_type TEXT NOT NULL DEFAULT "visit",
        status TEXT NOT NULL DEFAULT "requested",
        start_date TEXT,
        end_date TEXT,
        amount REAL NOT NULL DEFAULT 0,
        notes TEXT,
        created_at TEXT NOT NULL
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS vehicles (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER,
        owner_user_id INTEGER,
        type TEXT NOT NULL DEFAULT "car",
        brand TEXT NOT NULL,
        model TEXT NOT NULL,
        year INTEGER,
        plate TEXT,
        color TEXT,
        daily_rate REAL NOT NULL DEFAULT 0,
        mileage REAL NOT NULL DEFAULT 0,
        image TEXT,
        status TEXT NOT NULL DEFAULT "available",
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS vehicle_reservations (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        vehicle_id INTEGER NOT NULL,
        client_id INTEGER NOT NULL,
        status TEXT NOT NULL DEFAULT "requested",
        start_date TEXT NOT NULL,
        end_date TEXT NOT NULL,
        total REAL NOT NULL DEFAULT 0,
        payment_status TEXT NOT NULL DEFAULT "pending",
        notes TEXT,
        created_at TEXT NOT NULL
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS payments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        payer_user_id INTEGER,
        company_id INTEGER,
        reference_type TEXT,
        reference_id INTEGER,
        amount REAL NOT NULL DEFAULT 0,
        method TEXT,
        status TEXT NOT NULL DEFAULT "pending",
        due_date TEXT,
        paid_at TEXT,
        proof TEXT,
        notes TEXT,
        created_at TEXT NOT NULL
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS banners (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        subtitle TEXT,
        image TEXT,
        link TEXT,
        position TEXT NOT NULL DEFAULT "home",
        status TEXT NOT NULL DEFAULT "active",
        sort_order INTEGER NOT NULL DEFAULT 0,
        created_at TEXT NOT NULL
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS notifications (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        title TEXT NOT NULL,
        message TEXT NOT NULL,
        type TEXT NOT NULL DEFAULT "info",
        read_at TEXT,
        created_at TEXT NOT NULL
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS tickets (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        subject TEXT NOT NULL,
        message TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT "open",
        priority TEXT NOT NULL DEFAULT "normal",
        admin_reply TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS activity_logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        action TEXT NOT NULL,
        entity TEXT,
        entity_id INTEGER,
        details TEXT,
        ip TEXT,
        created_at TEXT NOT NULL
    )');

    // LinkExpress 2.3 — Marketplace, loja de veículos, mudanças, clube e validações.
    ensure_column($db, 'companies', 'verified', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'companies', 'premium', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'companies', 'response_minutes', 'INTEGER NOT NULL DEFAULT 60');
    ensure_column($db, 'companies', 'completed_orders', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'companies', 'cancellation_rate', 'REAL NOT NULL DEFAULT 0');
    ensure_column($db, 'users', 'city', 'TEXT');
    ensure_column($db, 'users', 'state', 'TEXT');
    ensure_column($db, 'users', 'verified', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'services', 'city', 'TEXT');
    ensure_column($db, 'services', 'state', 'TEXT');
    ensure_column($db, 'services', 'whatsapp', 'TEXT');
    ensure_column($db, 'services', 'availability', 'TEXT');
    ensure_column($db, 'vehicles', 'listing_mode', 'TEXT NOT NULL DEFAULT "rental"');
    ensure_column($db, 'vehicles', 'transmission', 'TEXT');
    ensure_column($db, 'vehicles', 'fuel', 'TEXT');
    ensure_column($db, 'vehicles', 'chassis', 'TEXT');
    ensure_column($db, 'vehicles', 'sale_price', 'REAL NOT NULL DEFAULT 0');
    ensure_column($db, 'vehicles', 'video_url', 'TEXT');
    ensure_column($db, 'vehicles', 'options_text', 'TEXT');
    ensure_column($db, 'vehicles', 'views', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'vehicles', 'sold_at', 'TEXT');
    ensure_column($db, 'properties', 'views', 'INTEGER NOT NULL DEFAULT 0');

    $db->exec('CREATE TABLE IF NOT EXISTS reviews (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        reviewer_id INTEGER NOT NULL,
        target_type TEXT NOT NULL,
        target_id INTEGER NOT NULL,
        order_id INTEGER,
        service_request_id INTEGER,
        rating INTEGER NOT NULL,
        service_rating INTEGER,
        quality_rating INTEGER,
        delivery_rating INTEGER,
        price_rating INTEGER,
        comment TEXT,
        response TEXT,
        status TEXT NOT NULL DEFAULT "published",
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    ensure_column($db, 'reviews', 'ride_id', 'INTEGER');
    ensure_column($db, 'reviews', 'delivery_job_id', 'INTEGER');
    ensure_column($db, 'reviews', 'vehicle_proposal_id', 'INTEGER');

    $db->exec('CREATE TABLE IF NOT EXISTS favorites (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        target_type TEXT NOT NULL,
        target_id INTEGER NOT NULL,
        created_at TEXT NOT NULL,
        UNIQUE(user_id,target_type,target_id)
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS vehicle_media (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        vehicle_id INTEGER NOT NULL,
        image TEXT NOT NULL,
        sort_order INTEGER NOT NULL DEFAULT 0,
        created_at TEXT NOT NULL
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS vehicle_proposals (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        vehicle_id INTEGER NOT NULL,
        client_id INTEGER NOT NULL,
        amount REAL NOT NULL DEFAULT 0,
        message TEXT,
        status TEXT NOT NULL DEFAULT "pending",
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS vehicle_appointments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        vehicle_id INTEGER NOT NULL,
        client_id INTEGER NOT NULL,
        appointment_type TEXT NOT NULL DEFAULT "test_drive",
        scheduled_at TEXT NOT NULL,
        notes TEXT,
        status TEXT NOT NULL DEFAULT "requested",
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS financing_requests (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        vehicle_id INTEGER NOT NULL,
        client_id INTEGER NOT NULL,
        entry_amount REAL NOT NULL DEFAULT 0,
        term_months INTEGER NOT NULL DEFAULT 12,
        income REAL NOT NULL DEFAULT 0,
        document_file TEXT,
        status TEXT NOT NULL DEFAULT "requested",
        notes TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS moving_requests (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        client_id INTEGER NOT NULL,
        driver_id INTEGER,
        service_type TEXT NOT NULL DEFAULT "residential",
        origin TEXT NOT NULL,
        destination TEXT NOT NULL,
        scheduled_at TEXT,
        vehicle_type TEXT,
        item_quantity INTEGER NOT NULL DEFAULT 0,
        needs_helpers INTEGER NOT NULL DEFAULT 0,
        needs_assembly INTEGER NOT NULL DEFAULT 0,
        needs_packaging INTEGER NOT NULL DEFAULT 0,
        floors INTEGER NOT NULL DEFAULT 0,
        distance_km REAL NOT NULL DEFAULT 0,
        estimated_value REAL NOT NULL DEFAULT 0,
        final_value REAL NOT NULL DEFAULT 0,
        status TEXT NOT NULL DEFAULT "requested",
        notes TEXT,
        cover_image TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS moving_quotes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        request_id INTEGER NOT NULL,
        driver_id INTEGER NOT NULL,
        amount REAL NOT NULL DEFAULT 0,
        message TEXT,
        status TEXT NOT NULL DEFAULT "pending",
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS club_members (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        user_id INTEGER,
        name TEXT NOT NULL,
        document TEXT,
        phone TEXT,
        email TEXT,
        photo TEXT,
        plan_name TEXT,
        status TEXT NOT NULL DEFAULT "active",
        expires_at TEXT,
        notes TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS club_instructors (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        name TEXT NOT NULL,
        specialties TEXT,
        certifications TEXT,
        phone TEXT,
        email TEXT,
        status TEXT NOT NULL DEFAULT "available",
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS club_appointments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        member_id INTEGER,
        instructor_id INTEGER,
        appointment_type TEXT NOT NULL DEFAULT "range",
        scheduled_at TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT "scheduled",
        notes TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS club_courses (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        title TEXT NOT NULL,
        level TEXT,
        instructor_id INTEGER,
        starts_at TEXT,
        capacity INTEGER NOT NULL DEFAULT 0,
        enrolled INTEGER NOT NULL DEFAULT 0,
        status TEXT NOT NULL DEFAULT "open",
        notes TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS club_assets (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        category TEXT,
        asset_name TEXT NOT NULL,
        caliber TEXT,
        patrimony_number TEXT,
        status TEXT NOT NULL DEFAULT "available",
        maintenance_at TEXT,
        notes TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS club_ammunition (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        caliber TEXT NOT NULL,
        quantity INTEGER NOT NULL DEFAULT 0,
        minimum_stock INTEGER NOT NULL DEFAULT 0,
        notes TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');



    // Compatibilidade com cadastros antigos de Mudanças e Fretes.
    $db->exec("UPDATE users SET role='moving_provider' WHERE role IN ('moving','moving_freight','freight','mudancas','mudancas_fretes','mudancas_e_fretes')");

    // LinkExpress 2.4 — bancos, mapas, corridas inteligentes, documentos e cobranças.
    ensure_column($db, 'companies', 'latitude', 'REAL');
    ensure_column($db, 'companies', 'longitude', 'REAL');
    ensure_column($db, 'companies', 'sector', 'TEXT');
    ensure_column($db, 'users', 'latitude', 'REAL');
    ensure_column($db, 'users', 'longitude', 'REAL');
    ensure_column($db, 'properties', 'latitude', 'REAL');
    ensure_column($db, 'properties', 'longitude', 'REAL');
    ensure_column($db, 'rides', 'pickup_lat', 'REAL');
    ensure_column($db, 'rides', 'pickup_lng', 'REAL');
    ensure_column($db, 'rides', 'destination_lat', 'REAL');
    ensure_column($db, 'rides', 'destination_lng', 'REAL');
    ensure_column($db, 'rides', 'duration_minutes', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'rides', 'fare_per_km', 'REAL NOT NULL DEFAULT 0');
    ensure_column($db, 'moving_requests', 'origin_lat', 'REAL');
    ensure_column($db, 'moving_requests', 'origin_lng', 'REAL');
    ensure_column($db, 'moving_requests', 'destination_lat', 'REAL');
    ensure_column($db, 'moving_requests', 'destination_lng', 'REAL');
    ensure_column($db, 'moving_requests', 'duration_minutes', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'vehicle_reservations', 'residence_proof', 'TEXT');
    ensure_column($db, 'vehicle_reservations', 'driver_license', 'TEXT');
    ensure_column($db, 'vehicle_reservations', 'client_phone', 'TEXT');

    $db->exec('CREATE TABLE IF NOT EXISTS banks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        website_url TEXT,
        app_url TEXT,
        image TEXT,
        status TEXT NOT NULL DEFAULT "active",
        sort_order INTEGER NOT NULL DEFAULT 0,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS favorite_addresses (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        label TEXT NOT NULL,
        address TEXT NOT NULL,
        latitude REAL,
        longitude REAL,
        created_at TEXT NOT NULL,
        UNIQUE(user_id,label,address)
    )');

    ensure_column($db, 'favorite_addresses', 'updated_at', 'TEXT');

    $db->exec('CREATE TABLE IF NOT EXISTS favorite_drivers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        driver_id INTEGER NOT NULL,
        created_at TEXT NOT NULL,
        UNIQUE(user_id,driver_id)
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS charges (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        amount REAL NOT NULL DEFAULT 0,
        description TEXT NOT NULL,
        due_date TEXT,
        payment_link TEXT,
        payment_method TEXT NOT NULL DEFAULT "pix",
        status TEXT NOT NULL DEFAULT "pending",
        proof TEXT,
        blocks_access INTEGER NOT NULL DEFAULT 1,
        admin_notes TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');


    // LinkExpress 2.5 — contatos por ID, entregas empresariais, Pix próprio, órgãos públicos e aprovação de tarifas.
    ensure_column($db, 'users', 'public_id', 'TEXT');
    ensure_column($db, 'users', 'home_address', 'TEXT');
    ensure_column($db, 'users', 'home_lat', 'REAL');
    ensure_column($db, 'users', 'home_lng', 'REAL');
    ensure_column($db, 'users', 'pix_key', 'TEXT');
    ensure_column($db, 'users', 'pix_key_type', 'TEXT');
    ensure_column($db, 'users', 'marketplace_visible', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'orders', 'fulfillment_type', 'TEXT NOT NULL DEFAULT "delivery"');
    ensure_column($db, 'orders', 'client_authorization_status', 'TEXT NOT NULL DEFAULT "not_required"');
    ensure_column($db, 'orders', 'client_address_snapshot', 'TEXT');
    // V2.8.22 - confirmação de pagamento dos pedidos e comprovante Pix.
    ensure_column($db, 'orders', 'payment_proof', 'TEXT');
    ensure_column($db, 'orders', 'payment_proof_uploaded_at', 'TEXT');
    ensure_column($db, 'orders', 'payment_confirmed_by', 'INTEGER');
    ensure_column($db, 'orders', 'payment_confirmed_at', 'TEXT');
    ensure_column($db, 'orders', 'payment_confirmation_source', 'TEXT');
    // V2.8.62 - estoque só é efetivado após comprovante/confirmação e entrega pode nascer do pedido de gás.
    ensure_column($db, 'orders', 'stock_committed', 'INTEGER NOT NULL DEFAULT 1');
    ensure_column($db, 'products', 'low_stock_threshold', 'INTEGER NOT NULL DEFAULT 5');
    ensure_column($db, 'products', 'low_stock_notified', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'rides', 'driver_proposed_value', 'REAL NOT NULL DEFAULT 0');
    ensure_column($db, 'rides', 'admin_minimum_value', 'REAL NOT NULL DEFAULT 0');
    ensure_column($db, 'rides', 'fare_review_status', 'TEXT NOT NULL DEFAULT "not_required"');
    ensure_column($db, 'rides', 'pix_released', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'service_requests', 'pix_released', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'moving_requests', 'pix_released', 'INTEGER NOT NULL DEFAULT 0');

    $db->exec('CREATE TABLE IF NOT EXISTS contact_book (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        owner_user_id INTEGER NOT NULL,
        contact_user_id INTEGER NOT NULL,
        label TEXT,
        created_at TEXT NOT NULL,
        UNIQUE(owner_user_id, contact_user_id)
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS company_couriers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        user_id INTEGER NOT NULL,
        relationship TEXT NOT NULL DEFAULT "autonomous",
        status TEXT NOT NULL DEFAULT "pending",
        created_at TEXT NOT NULL,
        updated_at TEXT,
        UNIQUE(company_id,user_id)
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS delivery_jobs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        client_id INTEGER,
        client_public_id TEXT,
        client_name TEXT,
        client_phone TEXT,
        address TEXT,
        latitude REAL,
        longitude REAL,
        assigned_courier_id INTEGER,
        status TEXT NOT NULL DEFAULT "waiting_authorization",
        authorization_status TEXT NOT NULL DEFAULT "pending",
        notes TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');
    ensure_column($db, 'delivery_jobs', 'order_id', 'INTEGER');
    $db->exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_delivery_jobs_order ON delivery_jobs(order_id) WHERE order_id IS NOT NULL');

    $db->exec('CREATE TABLE IF NOT EXISTS public_orgs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        category TEXT,
        city TEXT NOT NULL,
        state TEXT,
        address TEXT NOT NULL,
        latitude REAL,
        longitude REAL,
        opens_at TEXT,
        closes_at TEXT,
        phone TEXT,
        website TEXT,
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    migrate_random_public_user_ids($db);
    $db->exec("UPDATE users SET marketplace_visible=1 WHERE id IN (SELECT DISTINCT target_id FROM reviews WHERE target_type IN ('service_provider','moving_driver') AND status='published')");
    $db->exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_users_public_id ON users(public_id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_contact_book_owner ON contact_book(owner_user_id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_company_couriers_company ON company_couriers(company_id,status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_delivery_jobs_company ON delivery_jobs(company_id,status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_public_orgs_city ON public_orgs(city,state,status)');

    $db->exec('CREATE INDEX IF NOT EXISTS idx_companies_coordinates ON companies(latitude,longitude)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_charges_user_status ON charges(user_id,status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_favorite_drivers_user ON favorite_drivers(user_id)');

    $db->exec('CREATE INDEX IF NOT EXISTS idx_reviews_target ON reviews(target_type,target_id,status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_reviews_ride ON reviews(ride_id,reviewer_id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_reviews_delivery ON reviews(delivery_job_id,reviewer_id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_reviews_order ON reviews(order_id,reviewer_id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_reviews_service_request ON reviews(service_request_id,reviewer_id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_vehicle_proposals_vehicle ON vehicle_proposals(vehicle_id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_moving_requests_status ON moving_requests(status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_club_members_company ON club_members(company_id)');

    $db->exec('CREATE INDEX IF NOT EXISTS idx_users_role ON users(role)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_users_company ON users(company_id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_orders_company ON orders(company_id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_orders_client ON orders(client_id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_rides_driver ON rides(driver_id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_payments_status ON payments(status)');


    // LinkExpress 2.6 — carrinho, redes sociais, frota, permissões permanentes, arquivos de corridas e segmentos.
    ensure_column($db, 'users', 'instagram', 'TEXT');
    ensure_column($db, 'users', 'telegram', 'TEXT');
    ensure_column($db, 'users', 'home_number', 'TEXT');
    ensure_column($db, 'companies', 'instagram', 'TEXT');
    ensure_column($db, 'companies', 'telegram', 'TEXT');
    ensure_column($db, 'companies', 'address_number', 'TEXT');
    ensure_column($db, 'companies', 'allow_self_edit', 'INTEGER NOT NULL DEFAULT 1');
    // V2.8.28 — limites definidos pelo Administrador Sênior. 0 = cadastro não liberado; acima de 0 = quantidade máxima.
    ensure_column($db, 'companies', 'client_limit', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'companies', 'employee_limit', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'companies', 'courier_limit', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'rides', 'company_id', 'INTEGER');
    ensure_column($db, 'rides', 'dispatch_mode', 'TEXT NOT NULL DEFAULT "open"');
    ensure_column($db, 'rides', 'terminal_at', 'TEXT');
    ensure_column($db, 'rides', 'completed_at', 'TEXT');
    ensure_column($db, 'rides', 'driver_cancel_count', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'delivery_jobs', 'scheduled_at', 'TEXT');
    ensure_column($db, 'delivery_jobs', 'package_description', 'TEXT');
    ensure_column($db, 'delivery_jobs', 'sack_qty', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'delivery_jobs', 'box_qty', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'delivery_jobs', 'distance_km', 'REAL NOT NULL DEFAULT 0');
    ensure_column($db, 'delivery_jobs', 'delivered_at', 'TEXT');
    ensure_column($db, 'delivery_jobs', 'terminal_at', 'TEXT');
    ensure_column($db, 'payments', 'owner_user_id', 'INTEGER');
    ensure_column($db, 'charges', 'issuer_user_id', 'INTEGER');
    ensure_column($db, 'charges', 'pix_key_snapshot', 'TEXT');
    ensure_column($db, 'charges', 'payment_link_id', 'INTEGER');
    ensure_column($db, 'vehicles', 'service_mode', 'TEXT');

    $db->exec('CREATE TABLE IF NOT EXISTS cart_items (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        company_id INTEGER NOT NULL,
        product_id INTEGER NOT NULL,
        qty REAL NOT NULL DEFAULT 1,
        created_at TEXT NOT NULL,
        updated_at TEXT,
        UNIQUE(user_id,product_id)
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS delivery_address_permissions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        user_id INTEGER NOT NULL,
        always_visible INTEGER NOT NULL DEFAULT 1,
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL,
        updated_at TEXT,
        UNIQUE(company_id,user_id)
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS fleet_drivers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        user_id INTEGER NOT NULL,
        vehicle_label TEXT,
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL,
        updated_at TEXT,
        UNIQUE(company_id,user_id)
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS ride_driver_blocks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        ride_id INTEGER NOT NULL,
        driver_id INTEGER NOT NULL,
        reason TEXT,
        created_at TEXT NOT NULL,
        UNIQUE(ride_id,driver_id)
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS payment_links (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        owner_user_id INTEGER NOT NULL,
        title TEXT NOT NULL,
        url TEXT,
        pix_key TEXT,
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    $db->exec('CREATE TABLE IF NOT EXISTS segments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        slug TEXT NOT NULL UNIQUE,
        name TEXT NOT NULL,
        icon TEXT,
        status TEXT NOT NULL DEFAULT "active",
        sort_order INTEGER NOT NULL DEFAULT 0,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    // Os segmentos padrão são semeados apenas uma vez por instalação. O marcador evita
    // que um segmento apagado pelo Administrador Sênior seja recriado automaticamente depois.
    $segmentSeeded = $db->prepare("SELECT value FROM settings WHERE key_name='segments_seeded_v283' LIMIT 1");
    $segmentSeeded->execute();
    if ($segmentSeeded->fetchColumn() === false) {
        $segmentCount = (int)$db->query('SELECT COUNT(*) FROM segments')->fetchColumn();
        if ($segmentCount === 0) {
            $db->exec("INSERT INTO segments(slug,name,icon,status,sort_order,created_at,updated_at) VALUES
                ('delivery','Delivery e Alimentação','🍔','active',10,datetime('now'),datetime('now')),
                ('market','Mercados','🛒','active',20,datetime('now'),datetime('now')),
                ('pharmacy','Farmácias','💊','active',30,datetime('now'),datetime('now')),
                ('vehicle_store','Loja de Veículos','🚙','active',40,datetime('now'),datetime('now')),
                ('rental','Locadora de Veículos','🚗','active',50,datetime('now'),datetime('now')),
                ('moving','Mudanças e Fretes','🚚','active',60,datetime('now'),datetime('now')),
                ('taxi','Empresa de Táxi','🚖','active',70,datetime('now'),datetime('now')),
                ('mototaxi','Empresa de Mototáxi','🏍️','active',80,datetime('now'),datetime('now')),
                ('services','Prestadores de Serviços','🛠️','active',90,datetime('now'),datetime('now')),
                ('shooting_club','Clube e Associados','🎯','active',100,datetime('now'),datetime('now')),
                ('other','Outros','🏢','active',999,datetime('now'),datetime('now'))");
        }
        $mark=$db->prepare('INSERT INTO settings(key_name,value,updated_at) VALUES(?,?,?) ON CONFLICT(key_name) DO UPDATE SET value=excluded.value,updated_at=excluded.updated_at');
        $mark->execute(['segments_seeded_v283','1',date('Y-m-d H:i:s')]);
    }


    // LinkExpress 2.8.5 — estado visual por usuário para avisos/alterações da Dashboard.
    $db->exec('CREATE TABLE IF NOT EXISTS user_ui_state (
        user_id INTEGER NOT NULL,
        key_name TEXT NOT NULL,
        value TEXT,
        updated_at TEXT NOT NULL,
        PRIMARY KEY(user_id,key_name)
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_user_ui_state_user ON user_ui_state(user_id,key_name)');

    // V2.7: clientes criados dentro de uma empresa já autorizam aquela empresa a usar o endereço em entregas.
    $db->exec("INSERT OR IGNORE INTO delivery_address_permissions(company_id,user_id,always_visible,status,created_at,updated_at)
        SELECT company_id,id,1,'active',datetime('now'),datetime('now')
        FROM users WHERE role='client' AND company_id IS NOT NULL AND company_id>0");

    $db->exec('CREATE INDEX IF NOT EXISTS idx_cart_items_user_company ON cart_items(user_id,company_id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_delivery_permissions_company_user ON delivery_address_permissions(company_id,user_id,status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_fleet_drivers_company ON fleet_drivers(company_id,status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_ride_blocks_ride ON ride_driver_blocks(ride_id,driver_id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_payment_links_owner ON payment_links(owner_user_id,status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_rides_terminal ON rides(status,created_at,terminal_at)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_delivery_jobs_schedule ON delivery_jobs(assigned_courier_id,scheduled_at,status)');


    // LinkExpress 2.8.9 — rotas, notificações clicáveis, piscinas, recibos e localização.
    ensure_column($db, 'notifications', 'link_url', 'TEXT');
    ensure_column($db, 'notifications', 'entity_type', 'TEXT');
    ensure_column($db, 'notifications', 'entity_id', 'INTEGER');
    ensure_column($db, 'service_requests', 'latitude', 'REAL');
    ensure_column($db, 'service_requests', 'longitude', 'REAL');
    ensure_column($db, 'properties', 'property_kind', 'TEXT NOT NULL DEFAULT "property"');
    ensure_column($db, 'properties', 'available_weekdays', 'TEXT');
    ensure_column($db, 'charges', 'receipt_path', 'TEXT');
    // V2.8.25 — vínculo direto entre mensalidade e cobrança.
    ensure_column($db, 'charges', 'subscription_id', 'INTEGER');
    ensure_column($db, 'subscriptions', 'updated_at', 'TEXT');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_notifications_user_link ON notifications(user_id,read_at,link_url)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_service_requests_coordinates ON service_requests(latitude,longitude)');
    // LinkExpress 2.8.11 — Lava-Jato e Lotação Van/Carro.
    $db->exec('CREATE TABLE IF NOT EXISTS car_wash_slots (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        weekday INTEGER NOT NULL,
        slot_time TEXT NOT NULL,
        capacity INTEGER NOT NULL DEFAULT 1,
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL,
        updated_at TEXT,
        UNIQUE(company_id,weekday,slot_time)
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS car_wash_blocks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        block_date TEXT NOT NULL,
        slot_time TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT "blocked",
        created_at TEXT NOT NULL,
        UNIQUE(company_id,block_date,slot_time)
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS car_wash_appointments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        client_id INTEGER NOT NULL,
        appointment_date TEXT NOT NULL,
        appointment_time TEXT NOT NULL,
        vehicle_type TEXT,
        plate TEXT,
        status TEXT NOT NULL DEFAULT "requested",
        notes TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    // LinkExpress 2.8.14 — expediente opcional do Lava-Jato por dia.
    $db->exec('CREATE TABLE IF NOT EXISTS car_wash_hours (
        company_id INTEGER NOT NULL,
        weekday INTEGER NOT NULL,
        opening_time TEXT NOT NULL,
        closing_time TEXT NOT NULL,
        interval_minutes INTEGER NOT NULL DEFAULT 60,
        capacity INTEGER NOT NULL DEFAULT 1,
        status TEXT NOT NULL DEFAULT "active",
        updated_at TEXT,
        PRIMARY KEY(company_id,weekday)
    )');
    ensure_column($db, 'car_wash_hours', 'lunch_start_time', 'TEXT');
    ensure_column($db, 'car_wash_hours', 'lunch_end_time', 'TEXT');

    $db->exec('CREATE TABLE IF NOT EXISTS shuttle_driver_profiles (
        user_id INTEGER PRIMARY KEY,
        vehicle_type TEXT NOT NULL DEFAULT "van",
        vehicle_model TEXT,
        plate TEXT,
        capacity INTEGER NOT NULL DEFAULT 4,
        vehicle_photo TEXT,
        cnh_number TEXT,
        cnh_document TEXT,
        vehicle_document TEXT,
        approval_status TEXT NOT NULL DEFAULT "pending",
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS shuttle_routes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER,
        driver_id INTEGER NOT NULL,
        name TEXT NOT NULL,
        origin_city TEXT NOT NULL,
        destination_city TEXT NOT NULL,
        highway TEXT,
        pickup_points TEXT,
        dropoff_points TEXT,
        max_detour_km REAL NOT NULL DEFAULT 5,
        start_time TEXT,
        end_time TEXT,
        weekdays TEXT,
        capacity INTEGER NOT NULL DEFAULT 4,
        price_per_seat REAL NOT NULL DEFAULT 0,
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS shuttle_trips (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        route_id INTEGER NOT NULL,
        driver_id INTEGER NOT NULL,
        company_id INTEGER,
        status TEXT NOT NULL DEFAULT "active",
        available_seats INTEGER NOT NULL DEFAULT 0,
        current_address TEXT,
        current_lat REAL,
        current_lng REAL,
        started_at TEXT NOT NULL,
        ended_at TEXT,
        updated_at TEXT
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS shuttle_bookings (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        trip_id INTEGER NOT NULL,
        client_id INTEGER NOT NULL,
        pickup TEXT NOT NULL,
        destination TEXT NOT NULL,
        pickup_lat REAL,
        pickup_lng REAL,
        destination_lat REAL,
        destination_lng REAL,
        seats INTEGER NOT NULL DEFAULT 1,
        amount REAL NOT NULL DEFAULT 0,
        payment_method TEXT,
        status TEXT NOT NULL DEFAULT "reserved",
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');

    // V2.8.30 — segunda modalidade da Lotação: passageiro solicita e aguarda uma lotação online.
    $db->exec('CREATE TABLE IF NOT EXISTS shuttle_requests (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        client_id INTEGER NOT NULL,
        pickup TEXT NOT NULL,
        destination TEXT NOT NULL,
        pickup_lat REAL,
        pickup_lng REAL,
        destination_lat REAL,
        destination_lng REAL,
        seats INTEGER NOT NULL DEFAULT 1,
        payment_method TEXT,
        status TEXT NOT NULL DEFAULT "waiting",
        accepted_trip_id INTEGER,
        accepted_driver_id INTEGER,
        booking_id INTEGER,
        amount REAL NOT NULL DEFAULT 0,
        accepted_at TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_shuttle_requests_status ON shuttle_requests(status,created_at)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_shuttle_requests_client ON shuttle_requests(client_id,status,created_at)');

    ensure_column($db, 'shuttle_driver_profiles', 'approval_notes', 'TEXT');
    ensure_column($db, 'shuttle_driver_profiles', 'approved_by', 'INTEGER');
    ensure_column($db, 'shuttle_driver_profiles', 'approved_at', 'TEXT');
    $db->exec('CREATE TABLE IF NOT EXISTS shuttle_route_fares (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        route_id INTEGER NOT NULL,
        city_name TEXT NOT NULL,
        normalized_city TEXT NOT NULL,
        price_per_seat REAL NOT NULL DEFAULT 0,
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL,
        updated_at TEXT,
        UNIQUE(route_id,normalized_city)
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_shuttle_fares_route ON shuttle_route_fares(route_id,status,normalized_city)');

    $db->exec('CREATE TABLE IF NOT EXISTS document_requirements (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        target_role TEXT NOT NULL DEFAULT "all",
        name TEXT NOT NULL,
        description TEXT,
        blocks_access INTEGER NOT NULL DEFAULT 0,
        sort_order INTEGER NOT NULL DEFAULT 0,
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS user_documents (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        requirement_id INTEGER NOT NULL,
        user_id INTEGER NOT NULL,
        file_path TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT "pending",
        notes TEXT,
        reviewer_id INTEGER,
        reviewed_at TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT,
        FOREIGN KEY(requirement_id) REFERENCES document_requirements(id) ON DELETE CASCADE,
        FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_document_requirements_role ON document_requirements(target_role,status,sort_order)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_user_documents_user_req ON user_documents(user_id,requirement_id,status)');

    // V2.8.17 - denúncias de usuários
    $db->exec('CREATE TABLE IF NOT EXISTS user_reports (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        reporter_user_id INTEGER,
        reported_user_id INTEGER,
        reporter_name_snapshot TEXT NOT NULL,
        reported_name_snapshot TEXT NOT NULL,
        reason TEXT NOT NULL,
        description TEXT,
        status TEXT NOT NULL DEFAULT "pending",
        admin_notes TEXT,
        reviewed_by INTEGER,
        reviewed_at TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_user_reports_reporter ON user_reports(reporter_user_id,created_at)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_user_reports_reported ON user_reports(reported_user_id,status,created_at)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_user_reports_status ON user_reports(status,created_at)');

    $db->exec('CREATE INDEX IF NOT EXISTS idx_car_wash_slots_company_day ON car_wash_slots(company_id,weekday,status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_car_wash_hours_company_day ON car_wash_hours(company_id,weekday,status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_car_wash_appointments_date ON car_wash_appointments(company_id,appointment_date,appointment_time,status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_shuttle_routes_driver ON shuttle_routes(driver_id,status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_shuttle_trips_status ON shuttle_trips(status,available_seats)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_shuttle_bookings_trip ON shuttle_bookings(trip_id,status)');


    // V2.8.13: perfis de lotação que já foram explicitamente ativados pelo Admin
    // passam a considerar a aprovação da lotação concluída. Cadastros públicos novos
    // continuam pendentes, pois nascem com users.status='pending'.
    $syncApproval=$db->prepare("SELECT value FROM settings WHERE key_name='shuttle_approval_sync_v2813' LIMIT 1");
    $syncApproval->execute();
    if ($syncApproval->fetchColumn() === false) {
        $db->exec("UPDATE shuttle_driver_profiles SET approval_status='approved',approved_at=datetime('now') WHERE approval_status='pending' AND user_id IN (SELECT id FROM users WHERE role='shuttle_driver' AND status='active')");
        $db->exec("INSERT INTO settings(key_name,value,updated_at) VALUES('shuttle_approval_sync_v2813','1',datetime('now')) ON CONFLICT(key_name) DO UPDATE SET value='1',updated_at=datetime('now')");
    }

    $newSegments=$db->prepare("SELECT value FROM settings WHERE key_name='segments_seeded_v2811' LIMIT 1");
    $newSegments->execute();
    if ($newSegments->fetchColumn() === false) {
        $db->exec("INSERT OR IGNORE INTO segments(slug,name,icon,status,sort_order,created_at,updated_at) VALUES
            ('car_wash','Lava-Jato','🚗💦','active',110,datetime('now'),datetime('now')),
            ('shuttle','Lotação Van / Carro','🚐','active',120,datetime('now'),datetime('now'))");
        $mark=$db->prepare('INSERT INTO settings(key_name,value,updated_at) VALUES(?,?,?) ON CONFLICT(key_name) DO UPDATE SET value=excluded.value,updated_at=excluded.updated_at');
        $mark->execute(['segments_seeded_v2811','1',date('Y-m-d H:i:s')]);
    }


    // LinkExpress 2.8.23 — agenda de piscinas, recibos universais, financeiro espelhado,
    // identidade visual dinâmica e segurança de mensalidades/lista negra.
    // V2.8.31 - imagem personalizada dos planos
    ensure_column($db, 'plans', 'image', 'TEXT');

    ensure_column($db, 'properties', 'payment_link', 'TEXT');
    ensure_column($db, 'properties', 'pix_key', 'TEXT');
    ensure_column($db, 'property_bookings', 'proof', 'TEXT');
    ensure_column($db, 'property_bookings', 'payment_status', 'TEXT NOT NULL DEFAULT "pending"');
    ensure_column($db, 'property_bookings', 'receipt_path', 'TEXT');
    ensure_column($db, 'property_bookings', 'paid_at', 'TEXT');
    ensure_column($db, 'property_bookings', 'payment_confirmed_by', 'INTEGER');
    ensure_column($db, 'property_bookings', 'updated_at', 'TEXT');
    ensure_column($db, 'subscriptions', 'receipt_path', 'TEXT');
    ensure_column($db, 'payments', 'receipt_path', 'TEXT');
    ensure_column($db, 'payments', 'entry_kind', 'TEXT');
    ensure_column($db, 'payments', 'source_key', 'TEXT');
    ensure_column($db, 'users', 'blacklisted', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'users', 'blacklist_reason', 'TEXT');
    ensure_column($db, 'users', 'blacklisted_at', 'TEXT');
    ensure_column($db, 'users', 'provisional_access_until', 'TEXT');
    ensure_column($db, 'service_requests', 'payment_status', 'TEXT NOT NULL DEFAULT "pending"');
    ensure_column($db, 'service_requests', 'payment_proof', 'TEXT');
    ensure_column($db, 'service_requests', 'receipt_path', 'TEXT');
    ensure_column($db, 'service_requests', 'paid_at', 'TEXT');
    ensure_column($db, 'service_requests', 'payment_confirmed_by', 'INTEGER');
    ensure_column($db, 'rides', 'payment_status', 'TEXT NOT NULL DEFAULT "pending"');
    ensure_column($db, 'rides', 'payment_proof', 'TEXT');
    ensure_column($db, 'rides', 'receipt_path', 'TEXT');
    ensure_column($db, 'rides', 'paid_at', 'TEXT');
    ensure_column($db, 'rides', 'payment_confirmed_by', 'INTEGER');
    ensure_column($db, 'vehicle_reservations', 'payment_proof', 'TEXT');
    ensure_column($db, 'vehicle_reservations', 'receipt_path', 'TEXT');
    ensure_column($db, 'vehicle_reservations', 'paid_at', 'TEXT');
    ensure_column($db, 'vehicle_reservations', 'payment_confirmed_by', 'INTEGER');

    // V2.8.38 — solicitações de exclusão de conta/empresa com aviso ao Administrador Sênior.
    $db->exec('CREATE TABLE IF NOT EXISTS account_deletion_requests (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        request_type TEXT NOT NULL DEFAULT "user",
        user_id INTEGER NOT NULL,
        company_id INTEGER,
        requested_by INTEGER NOT NULL,
        user_name_snapshot TEXT,
        user_email_snapshot TEXT,
        company_name_snapshot TEXT,
        reason TEXT,
        status TEXT NOT NULL DEFAULT "pending",
        created_at TEXT NOT NULL,
        updated_at TEXT,
        resolved_at TEXT,
        resolved_by INTEGER
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_account_deletion_status ON account_deletion_requests(status,created_at)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_account_deletion_user ON account_deletion_requests(user_id,status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_account_deletion_company ON account_deletion_requests(company_id,status)');
    $db->exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_account_deletion_pending_user ON account_deletion_requests(user_id) WHERE request_type="user" AND status="pending"');
    $db->exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_account_deletion_pending_company ON account_deletion_requests(company_id) WHERE request_type="company" AND status="pending" AND company_id IS NOT NULL');

    // V2.8.37 — notificações segmentadas, limites de publicações e acesso aos bancos.
    ensure_column($db, 'notifications', 'issuer_user_id', 'INTEGER');
    ensure_column($db, 'notifications', 'source_company_id', 'INTEGER');
    ensure_column($db, 'notifications', 'broadcast_id', 'TEXT');
    ensure_column($db, 'notifications', 'audience_type', 'TEXT');
    ensure_column($db, 'notifications', 'audience_value', 'TEXT');
    ensure_column($db, 'notifications', 'audience_label', 'TEXT');
    ensure_column($db, 'notifications', 'image', 'TEXT');
    ensure_column($db, 'notifications', 'home_visible', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'notifications', 'expires_at', 'TEXT');
    ensure_column($db, 'notifications', 'updated_at', 'TEXT');
    ensure_column($db, 'companies', 'employees_can_manage_notifications', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'banks', 'allowed_roles', 'TEXT NOT NULL DEFAULT "all"');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_notifications_broadcast ON notifications(broadcast_id,user_id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_notifications_home ON notifications(user_id,home_visible,expires_at)');

    $db->exec('CREATE TABLE IF NOT EXISTS receipts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        source_type TEXT NOT NULL,
        source_id INTEGER NOT NULL,
        payer_user_id INTEGER,
        recipient_user_id INTEGER,
        company_id INTEGER,
        amount REAL NOT NULL DEFAULT 0,
        description TEXT,
        payment_method TEXT,
        proof_path TEXT,
        receipt_path TEXT,
        confirmed_by INTEGER,
        created_at TEXT NOT NULL,
        UNIQUE(source_type,source_id)
    )');
    ensure_column($db, 'receipts', 'receipt_code', 'TEXT');
    ensure_column($db, 'charges', 'receipt_code', 'TEXT');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_receipts_parties ON receipts(payer_user_id,recipient_user_id,created_at)');
    $db->exec("CREATE UNIQUE INDEX IF NOT EXISTS idx_receipts_code ON receipts(receipt_code) WHERE receipt_code IS NOT NULL AND receipt_code<>''");
    $db->exec("CREATE INDEX IF NOT EXISTS idx_charges_receipt_code ON charges(receipt_code) WHERE receipt_code IS NOT NULL AND receipt_code<>''");
    $db->exec('CREATE INDEX IF NOT EXISTS idx_property_bookings_payment ON property_bookings(property_id,start_date,payment_status,status)');
    $db->exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_payments_source_owner_kind ON payments(source_key,owner_user_id,entry_kind) WHERE source_key IS NOT NULL');


    // LinkExpress 2.8.43 — carteira, subadministrador, cobrança liberada, clientes de prestadores e taxas.
    ensure_column($db, 'users', 'wallet_enabled', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'users', 'pix_billing_enabled', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'users', 'client_billing_enabled', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'users', 'provider_clients_enabled', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'users', 'provider_client_limit', 'INTEGER NOT NULL DEFAULT 0');

    $db->exec('CREATE TABLE IF NOT EXISTS wallet_accounts (
        user_id INTEGER PRIMARY KEY,
        balance REAL NOT NULL DEFAULT 0,
        total_earned REAL NOT NULL DEFAULT 0,
        total_withdrawn REAL NOT NULL DEFAULT 0,
        updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS wallet_transactions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        source_type TEXT,
        source_id INTEGER,
        kind TEXT NOT NULL,
        amount REAL NOT NULL DEFAULT 0,
        description TEXT,
        created_at TEXT NOT NULL,
        UNIQUE(user_id,source_type,source_id,kind)
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS withdrawal_requests (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        amount REAL NOT NULL DEFAULT 0,
        pix_key TEXT,
        status TEXT NOT NULL DEFAULT "pending",
        admin_notes TEXT,
        reviewed_by INTEGER,
        reviewed_at TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_wallet_transactions_user ON wallet_transactions(user_id,created_at)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_withdrawal_requests_status ON withdrawal_requests(status,created_at)');

    $db->exec('CREATE TABLE IF NOT EXISTS provider_clients (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        provider_user_id INTEGER NOT NULL,
        client_user_id INTEGER NOT NULL,
        created_at TEXT NOT NULL,
        UNIQUE(provider_user_id,client_user_id)
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_provider_clients_provider ON provider_clients(provider_user_id,created_at)');

    $db->exec('CREATE TABLE IF NOT EXISTS platform_fee_rules (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        scope_type TEXT NOT NULL DEFAULT "all",
        scope_value TEXT,
        fee_type TEXT NOT NULL DEFAULT "fixed",
        fee_value REAL NOT NULL DEFAULT 0,
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_platform_fee_rules_scope ON platform_fee_rules(scope_type,scope_value,status)');

    // LinkExpress 2.8.44 — cadastro em 2 etapas, segmentos por perfil, termos,
    // mototaxista em entregas e agenda dos prestadores de serviços.
    ensure_column($db, 'users', 'profile_completed', 'INTEGER NOT NULL DEFAULT 1');
    ensure_column($db, 'users', 'account_kind', 'TEXT NOT NULL DEFAULT "legacy"');
    ensure_column($db, 'users', 'segment_slug', 'TEXT');
    ensure_column($db, 'users', 'delivery_mode_enabled', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'segments', 'allowed_roles', 'TEXT NOT NULL DEFAULT "company"');
    ensure_column($db, 'services', 'booking_enabled', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'services', 'deleted_at', 'TEXT');
    ensure_column($db, 'services', 'booking_interval_minutes', 'INTEGER NOT NULL DEFAULT 60');

    $db->exec('CREATE TABLE IF NOT EXISTS delivery_mode_requests (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        status TEXT NOT NULL DEFAULT "pending",
        notes TEXT,
        admin_notes TEXT,
        reviewed_by INTEGER,
        reviewed_at TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_delivery_mode_requests_user ON delivery_mode_requests(user_id,status,created_at)');
    $db->exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_delivery_mode_requests_pending ON delivery_mode_requests(user_id) WHERE status="pending"');

    $db->exec('CREATE TABLE IF NOT EXISTS service_availability (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        service_id INTEGER NOT NULL,
        weekday INTEGER NOT NULL,
        start_time TEXT,
        end_time TEXT,
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL,
        UNIQUE(service_id,weekday)
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_service_availability_service ON service_availability(service_id,weekday,status)');

    // V2.9.07 — encomendas/retirada agendada para empresas (bolos, festas, confeitaria etc.).
    ensure_column($db, 'companies', 'pickup_scheduling_enabled', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'companies', 'pickup_weekdays', 'TEXT NOT NULL DEFAULT "1,2,3,4,5,6"');
    ensure_column($db, 'companies', 'pickup_start_time', 'TEXT NOT NULL DEFAULT "08:00"');
    ensure_column($db, 'companies', 'pickup_end_time', 'TEXT NOT NULL DEFAULT "18:00"');
    ensure_column($db, 'companies', 'pickup_interval_minutes', 'INTEGER NOT NULL DEFAULT 30');
    ensure_column($db, 'orders', 'pickup_scheduled_at', 'TEXT');

    // V2.9.06 — local fixo de atendimento para prestadores de serviços.
    ensure_column($db, 'services', 'fixed_location_enabled', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'services', 'fixed_address', 'TEXT');
    ensure_column($db, 'services', 'fixed_lat', 'REAL');
    ensure_column($db, 'services', 'fixed_lng', 'REAL');

    // V2.9.04 — liberações específicas/em massa de horários para prestadores de serviços.
    $db->exec('CREATE TABLE IF NOT EXISTS service_availability_dates (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        service_id INTEGER NOT NULL,
        availability_date TEXT NOT NULL,
        start_time TEXT NOT NULL,
        end_time TEXT NOT NULL,
        interval_minutes INTEGER NOT NULL DEFAULT 60,
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL,
        UNIQUE(service_id,availability_date)
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_service_availability_dates_lookup ON service_availability_dates(service_id,availability_date,status)');

    $db->exec('CREATE TABLE IF NOT EXISTS platform_terms (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        content TEXT NOT NULL,
        version INTEGER NOT NULL DEFAULT 1,
        required INTEGER NOT NULL DEFAULT 1,
        status TEXT NOT NULL DEFAULT "active",
        sort_order INTEGER NOT NULL DEFAULT 0,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS term_acceptances (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        term_id INTEGER NOT NULL,
        term_version INTEGER NOT NULL DEFAULT 1,
        accepted_at TEXT NOT NULL,
        ip_address TEXT,
        UNIQUE(user_id,term_id,term_version)
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_term_acceptances_user ON term_acceptances(user_id,accepted_at)');

    // Relaciona os segmentos nativos aos perfis compatíveis apenas uma vez.
    $segmentRoleMarker=$db->prepare("SELECT value FROM settings WHERE key_name='segment_roles_mapped_v2844' LIMIT 1");
    $segmentRoleMarker->execute();
    if($segmentRoleMarker->fetchColumn()===false){
        $roleMap=[
            'delivery'=>'company,courier,motofreight',
            'market'=>'company',
            'pharmacy'=>'company',
            'vehicle_store'=>'company',
            'rental'=>'company',
            'moving'=>'company,moving_provider',
            'taxi'=>'company,taxi,private_driver',
            'mototaxi'=>'company,mototaxi',
            'services'=>'company,service_provider',
            'shuttle'=>'company,shuttle_driver',
            'shooting_club'=>'company',
            'clinic'=>'company',
            'other'=>'company,courier,motofreight,mototaxi,taxi,private_driver,service_provider,property_owner,moving_provider,shuttle_driver'
        ];
        $up=$db->prepare('UPDATE segments SET allowed_roles=?,updated_at=? WHERE slug=?');
        foreach($roleMap as $slug=>$roles)$up->execute([$roles,now(),$slug]);
        $mark=$db->prepare('INSERT INTO settings(key_name,value,updated_at) VALUES(?,?,?) ON CONFLICT(key_name) DO UPDATE SET value=excluded.value,updated_at=excluded.updated_at');
        $mark->execute(['segment_roles_mapped_v2844','1',now()]);
    }

    // Primeira instalação da área de termos: cria modelos editáveis pelo administrador.
    $termsSeedMarker=$db->prepare("SELECT value FROM settings WHERE key_name='platform_terms_seeded_v2844' LIMIT 1");
    $termsSeedMarker->execute();
    if($termsSeedMarker->fetchColumn()===false){
        $termCount=(int)$db->query('SELECT COUNT(*) FROM platform_terms')->fetchColumn();
        if($termCount===0){
            $ins=$db->prepare('INSERT INTO platform_terms(title,content,version,required,status,sort_order,created_at,updated_at) VALUES(?,?,?,?,?,?,?,?)');
            $ins->execute(['Termos de Uso','Ao utilizar a plataforma, o usuário declara que fornecerá informações verdadeiras, respeitará as regras de uso e será responsável pelas operações realizadas em sua conta.',1,1,'active',10,now(),now()]);
            $ins->execute(['Política de Privacidade','Os dados informados são utilizados para cadastro, funcionamento dos serviços, segurança, localização quando autorizada e comunicação entre as partes dentro da plataforma.',1,1,'active',20,now(),now()]);
        }
        $mark=$db->prepare('INSERT INTO settings(key_name,value,updated_at) VALUES(?,?,?) ON CONFLICT(key_name) DO UPDATE SET value=excluded.value,updated_at=excluded.updated_at');
        $mark->execute(['platform_terms_seeded_v2844','1',now()]);
    }

    // LinkExpress 2.9.22 — garante que Termos e Políticas não desapareçam em
    // instalações antigas onde a tabela ficou vazia, mesmo que o marcador do seed
    // anterior já exista. Não reativa documentos que o administrador desativou.
    $platformTermsCount=(int)$db->query('SELECT COUNT(*) FROM platform_terms')->fetchColumn();
    if($platformTermsCount===0){
        $ins=$db->prepare('INSERT INTO platform_terms(title,content,version,required,status,sort_order,created_at,updated_at) VALUES(?,?,?,?,?,?,?,?)');
        $ins->execute(['Termos de Uso','Ao utilizar a plataforma, o usuário declara que fornecerá informações verdadeiras, respeitará as regras de uso e será responsável pelas operações realizadas em sua conta.',1,1,'active',10,now(),now()]);
        $ins->execute(['Política de Privacidade','Os dados informados são utilizados para cadastro, funcionamento dos serviços, segurança, localização quando autorizada e comunicação entre as partes dentro da plataforma.',1,1,'active',20,now(),now()]);
    }

    // LinkExpress 2.8.50 — Clínicas como empresa e agenda padrão de serviços.
    // INSERT OR IGNORE permite levar o novo segmento para instalações já existentes sem recriar itens apagados.
    $db->exec("INSERT OR IGNORE INTO segments(slug,name,icon,status,sort_order,allowed_roles,created_at,updated_at) VALUES('clinic','Clínicas e Saúde','🏥','active',125,'company',datetime('now'),datetime('now'))");
    $db->exec("UPDATE segments SET allowed_roles='company',updated_at=datetime('now') WHERE slug='clinic' AND (allowed_roles IS NULL OR trim(allowed_roles)='')");


    // LinkExpress 2.8.51 — painel próprio de Clínicas + catálogo de lavagens/preços.
    // Reforça os dois segmentos mesmo em instalações onde o catálogo foi limpo manualmente.
    $db->exec("INSERT OR IGNORE INTO segments(slug,name,icon,status,sort_order,allowed_roles,created_at,updated_at) VALUES('car_wash','Lava-Jato','🚗💦','active',110,'company',datetime('now'),datetime('now'))");
    $db->exec("INSERT OR IGNORE INTO segments(slug,name,icon,status,sort_order,allowed_roles,created_at,updated_at) VALUES('clinic','Clínicas e Saúde','🏥','active',125,'company',datetime('now'),datetime('now'))");
    $db->exec("UPDATE segments SET name='Lava-Jato',icon='🚗💦',allowed_roles='company',status='active',updated_at=datetime('now') WHERE slug='car_wash'");
    $db->exec("UPDATE segments SET name='Clínicas e Saúde',icon='🏥',allowed_roles='company',status='active',updated_at=datetime('now') WHERE slug='clinic'");

    $db->exec('CREATE TABLE IF NOT EXISTS clinic_specialties (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        name TEXT NOT NULL,
        description TEXT,
        duration_minutes INTEGER NOT NULL DEFAULT 30,
        price REAL NOT NULL DEFAULT 0,
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS clinic_professionals (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        specialty_id INTEGER,
        name TEXT NOT NULL,
        professional_type TEXT,
        registry TEXT,
        phone TEXT,
        email TEXT,
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS clinic_patients (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        platform_user_id INTEGER,
        name TEXT NOT NULL,
        document TEXT,
        phone TEXT,
        email TEXT,
        birth_date TEXT,
        notes TEXT,
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS clinic_appointments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        patient_id INTEGER NOT NULL,
        professional_id INTEGER,
        specialty_id INTEGER,
        appointment_date TEXT NOT NULL,
        appointment_time TEXT NOT NULL,
        amount REAL NOT NULL DEFAULT 0,
        payment_method TEXT,
        payment_status TEXT NOT NULL DEFAULT "pending",
        status TEXT NOT NULL DEFAULT "scheduled",
        notes TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_specialties_company ON clinic_specialties(company_id,status,name)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_professionals_company ON clinic_professionals(company_id,status,name)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_patients_company ON clinic_patients(company_id,status,name)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_appointments_schedule ON clinic_appointments(company_id,appointment_date,appointment_time,status)');
    // LinkExpress 2.8.54 — segunda etapa completa do módulo de Clínicas.
    ensure_column($db, 'clinic_professionals', 'user_id', 'INTEGER');
    ensure_column($db, 'clinic_professionals', 'share_whatsapp', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'clinic_professionals', 'document', 'TEXT');
    ensure_column($db, 'clinic_patients', 'platform_user_id', 'INTEGER');
    ensure_column($db, 'clinic_appointments', 'requested_by_user_id', 'INTEGER');
    ensure_column($db, 'clinic_appointments', 'scheduled_by_user_id', 'INTEGER');
    ensure_column($db, 'clinic_appointments', 'professional_notes', 'TEXT');
    ensure_column($db, 'clinic_appointments', 'rescheduled_from_id', 'INTEGER');
    ensure_column($db, 'clinic_appointments', 'reminder_24h_sent', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'clinic_appointments', 'reminder_3h_sent', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'clinic_appointments', 'receipt_code', 'TEXT');
    ensure_column($db, 'reviews', 'clinic_appointment_id', 'INTEGER');

    $db->exec('CREATE TABLE IF NOT EXISTS clinic_settings (
        company_id INTEGER PRIMARY KEY,
        opening_hours TEXT,
        allow_self_booking INTEGER NOT NULL DEFAULT 1,
        allow_reschedule INTEGER NOT NULL DEFAULT 0,
        online_payment_enabled INTEGER NOT NULL DEFAULT 0,
        payment_method TEXT NOT NULL DEFAULT "pix",
        pix_key TEXT,
        payment_link TEXT,
        professional_limit INTEGER NOT NULL DEFAULT 0,
        patient_limit INTEGER NOT NULL DEFAULT 0,
        attendant_limit INTEGER NOT NULL DEFAULT 0,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS clinic_professional_availability (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        professional_id INTEGER NOT NULL,
        weekday INTEGER NOT NULL,
        start_time TEXT NOT NULL,
        end_time TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL,
        updated_at TEXT,
        UNIQUE(professional_id,weekday)
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS clinic_professional_blocks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        professional_id INTEGER NOT NULL,
        block_date TEXT NOT NULL,
        start_time TEXT,
        end_time TEXT,
        reason TEXT,
        created_at TEXT NOT NULL
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_prof_user ON clinic_professionals(user_id,company_id,status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_patient_user ON clinic_patients(platform_user_id,company_id,status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_availability_prof ON clinic_professional_availability(professional_id,weekday,status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_blocks_prof_date ON clinic_professional_blocks(professional_id,block_date)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_reviews_clinic_appointment ON reviews(clinic_appointment_id,target_type,target_id)');
    $db->exec("INSERT OR IGNORE INTO clinic_settings(company_id,opening_hours,allow_self_booking,allow_reschedule,online_payment_enabled,payment_method,professional_limit,patient_limit,attendant_limit,created_at,updated_at) SELECT id,'Segunda a sexta, 08:00–18:00',1,0,0,'pix',0,0,0,datetime('now'),datetime('now') FROM companies WHERE lower(COALESCE(type,''))='clinic'");

    $db->exec('CREATE TABLE IF NOT EXISTS car_wash_services (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        name TEXT NOT NULL,
        description TEXT,
        price REAL NOT NULL DEFAULT 0,
        duration_minutes INTEGER NOT NULL DEFAULT 60,
        status TEXT NOT NULL DEFAULT "inactive",
        sort_order INTEGER NOT NULL DEFAULT 0,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');
    ensure_column($db, 'car_wash_appointments', 'service_id', 'INTEGER');
    ensure_column($db, 'car_wash_appointments', 'service_name', 'TEXT');
    ensure_column($db, 'car_wash_appointments', 'service_price', 'REAL NOT NULL DEFAULT 0');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_car_wash_services_company ON car_wash_services(company_id,status,sort_order,name)');

    // V2.8.45 — mensalidades recorrentes, data de pagamento e link rápido.
    ensure_column($db, 'subscriptions', 'recurring_enabled', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'subscriptions', 'billing_interval_days', 'INTEGER NOT NULL DEFAULT 30');
    ensure_column($db, 'subscriptions', 'billing_anchor_date', 'TEXT');
    ensure_column($db, 'subscriptions', 'next_billing_date', 'TEXT');
    ensure_column($db, 'subscriptions', 'last_billing_date', 'TEXT');
    ensure_column($db, 'subscriptions', 'payment_link_id', 'INTEGER');
    ensure_column($db, 'subscriptions', 'payment_link', 'TEXT');
    ensure_column($db, 'subscriptions', 'billing_issuer_user_id', 'INTEGER');
    ensure_column($db, 'charges', 'recurrence_key', 'TEXT');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_subscriptions_recurring_due ON subscriptions(recurring_enabled,next_billing_date,status)');
    $db->exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_charges_recurrence_key ON charges(recurrence_key) WHERE recurrence_key IS NOT NULL');

    // LinkExpress 2.8.47 — links por plano, auditoria de subadmin, Central de Integrações, identidade e taxas financeiras.
    ensure_column($db, 'payment_links', 'plan_id', 'INTEGER');
    ensure_column($db, 'payment_links', 'link_kind', 'TEXT NOT NULL DEFAULT "generic"');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_payment_links_plan ON payment_links(plan_id,status,owner_user_id)');

    ensure_column($db, 'users', 'identity_edit_unlocked', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'users', 'new_area_enabled', 'INTEGER NOT NULL DEFAULT 1');
    ensure_column($db, 'users', 'platform_links_enabled', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'users', 'platform_billing_mode', 'TEXT NOT NULL DEFAULT "transaction"');
    ensure_column($db, 'users', 'fee_payer_preference', 'TEXT NOT NULL DEFAULT "client"');
    ensure_column($db, 'users', 'own_receiving_enabled', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'users', 'receiving_method', 'TEXT NOT NULL DEFAULT "pix"');
    ensure_column($db, 'users', 'personal_payment_link', 'TEXT');
    ensure_column($db, 'companies', 'new_area_enabled', 'INTEGER NOT NULL DEFAULT 1');
    $db->exec('UPDATE users SET new_area_enabled=1 WHERE COALESCE(new_area_enabled,0)<>1');
    $db->exec('UPDATE companies SET new_area_enabled=1 WHERE COALESCE(new_area_enabled,0)<>1');
    ensure_column($db, 'companies', 'identity_edit_unlocked', 'INTEGER NOT NULL DEFAULT 0');

    ensure_column($db, 'withdrawal_requests', 'fee_amount', 'REAL NOT NULL DEFAULT 0');
    ensure_column($db, 'withdrawal_requests', 'payout_amount', 'REAL NOT NULL DEFAULT 0');

    ensure_column($db, 'service_requests', 'provider_payment_confirmed', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'service_requests', 'client_completion_confirmed', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'service_requests', 'settled_at', 'TEXT');
    ensure_column($db, 'service_requests', 'payment_channel', 'TEXT');

    $db->exec('CREATE TABLE IF NOT EXISTS external_module_links (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        url TEXT NOT NULL,
        description TEXT,
        status TEXT NOT NULL DEFAULT "active",
        sort_order INTEGER NOT NULL DEFAULT 0,
        created_by INTEGER,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
    )');
    ensure_column($db, 'external_module_links', 'target_type', 'TEXT NOT NULL DEFAULT "all"');
    ensure_column($db, 'external_module_links', 'target_id', 'INTEGER');
    ensure_column($db, 'external_module_links', 'link_kind', 'TEXT NOT NULL DEFAULT "panel"');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_external_module_links_status ON external_module_links(status,sort_order,id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_external_module_links_target ON external_module_links(target_type,target_id,status)');

    $db->exec('CREATE TABLE IF NOT EXISTS external_module_client_access (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        module_link_id INTEGER NOT NULL,
        granted_by_user_id INTEGER NOT NULL,
        company_id INTEGER,
        client_user_id INTEGER NOT NULL,
        display_name TEXT,
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL,
        UNIQUE(module_link_id,granted_by_user_id,client_user_id)
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_external_client_access_client ON external_module_client_access(client_user_id,status,module_link_id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_external_client_access_owner ON external_module_client_access(granted_by_user_id,status,module_link_id)');

    $db->exec('CREATE TABLE IF NOT EXISTS manual_items (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        description TEXT,
        content TEXT,
        video_url TEXT,
        status TEXT NOT NULL DEFAULT "active",
        sort_order INTEGER NOT NULL DEFAULT 0,
        created_by INTEGER,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_manual_items_status ON manual_items(status,sort_order,id)');

    $db->exec('CREATE TABLE IF NOT EXISTS remember_tokens (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        selector TEXT NOT NULL UNIQUE,
        token_hash TEXT NOT NULL,
        expires_at TEXT NOT NULL,
        user_agent TEXT,
        created_at TEXT NOT NULL,
        last_used_at TEXT
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_remember_tokens_user ON remember_tokens(user_id,expires_at)');

    $db->exec('CREATE TABLE IF NOT EXISTS admin_daily_presence (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        work_date TEXT NOT NULL,
        first_seen TEXT NOT NULL,
        last_seen TEXT NOT NULL,
        last_ping TEXT NOT NULL,
        active_seconds INTEGER NOT NULL DEFAULT 0,
        UNIQUE(user_id,work_date)
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_admin_daily_presence_user ON admin_daily_presence(user_id,work_date)');


    // LinkExpress 2.8.60 — terceira etapa do módulo Clínicas/Profissionais.
    // Migração incremental: preserva todos os cadastros existentes.
    ensure_column($db, 'users', 'created_by_admin_id', 'INTEGER');
    ensure_column($db, 'users', 'activated_by_admin_id', 'INTEGER');
    ensure_column($db, 'users', 'admin_module_permissions', 'TEXT NOT NULL DEFAULT ""');
    ensure_column($db, 'companies', 'created_by_admin_id', 'INTEGER');
    ensure_column($db, 'companies', 'activated_by_admin_id', 'INTEGER');

    ensure_column($db, 'clinic_professionals', 'link_state', 'TEXT NOT NULL DEFAULT "active"');
    ensure_column($db, 'clinic_professionals', 'requested_by_user_id', 'INTEGER');
    ensure_column($db, 'clinic_professionals', 'professional_responded_at', 'TEXT');
    ensure_column($db, 'clinic_professionals', 'admin_approval_status', 'TEXT NOT NULL DEFAULT "not_required"');
    ensure_column($db, 'clinic_professionals', 'admin_approved_by', 'INTEGER');
    ensure_column($db, 'clinic_professionals', 'admin_approved_at', 'TEXT');
    ensure_column($db, 'clinic_professionals', 'billing_required', 'INTEGER NOT NULL DEFAULT 0');

    ensure_column($db, 'clinic_specialties', 'service_kind', 'TEXT NOT NULL DEFAULT "consultation"');
    ensure_column($db, 'clinic_specialties', 'preparation_instructions', 'TEXT');
    ensure_column($db, 'clinic_specialties', 'available_days', 'TEXT');
    ensure_column($db, 'clinic_specialties', 'available_hours', 'TEXT');
    ensure_column($db, 'clinic_specialties', 'allow_patient_booking', 'INTEGER NOT NULL DEFAULT 1');
    // LinkExpress 2.9.21 — agenda estruturada de exames e ordem de chegada.
    ensure_column($db, 'clinic_specialties', 'exam_booking_mode', 'TEXT NOT NULL DEFAULT "time"');
    ensure_column($db, 'clinic_specialties', 'exam_weekdays', 'TEXT');
    ensure_column($db, 'clinic_specialties', 'exam_start_time', 'TEXT');
    ensure_column($db, 'clinic_specialties', 'exam_end_time', 'TEXT');
    ensure_column($db, 'clinic_exam_requests', 'booking_mode', 'TEXT NOT NULL DEFAULT "time"');
    ensure_column($db, 'clinic_exam_requests', 'arrival_order', 'INTEGER');
    ensure_column($db, 'clinic_appointments', 'visit_type', 'TEXT NOT NULL DEFAULT "consultation"');
    ensure_column($db, 'clinic_appointments', 'first_visit', 'INTEGER NOT NULL DEFAULT 0');

    ensure_column($db, 'user_reports', 'reported_company_id', 'INTEGER');
    ensure_column($db, 'user_reports', 'reported_clinic_professional_id', 'INTEGER');

    $db->exec('CREATE TABLE IF NOT EXISTS clinic_professional_contacts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        owner_user_id INTEGER NOT NULL,
        company_id INTEGER NOT NULL,
        patient_id INTEGER,
        name TEXT NOT NULL,
        phone TEXT,
        email TEXT,
        notes TEXT,
        is_automatic INTEGER NOT NULL DEFAULT 0,
        created_at TEXT NOT NULL,
        updated_at TEXT,
        UNIQUE(owner_user_id,company_id,patient_id)
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_prof_contacts_owner ON clinic_professional_contacts(owner_user_id,company_id,name)');

    $db->exec('CREATE TABLE IF NOT EXISTS clinic_patient_records (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        patient_id INTEGER NOT NULL,
        professional_id INTEGER NOT NULL,
        appointment_id INTEGER,
        symptoms TEXT,
        assessment TEXT,
        diagnosis TEXT,
        prescriptions TEXT,
        exam_requests TEXT,
        allergies TEXT,
        medications TEXT,
        observations TEXT,
        attachment_path TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_patient_records_patient ON clinic_patient_records(company_id,patient_id,created_at)');

    $db->exec('CREATE TABLE IF NOT EXISTS clinic_exam_requests (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        patient_id INTEGER NOT NULL,
        professional_id INTEGER,
        specialty_id INTEGER,
        requested_by_user_id INTEGER,
        scheduled_date TEXT,
        scheduled_time TEXT,
        status TEXT NOT NULL DEFAULT "requested",
        notes TEXT,
        result_path TEXT,
        result_notes TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_exam_requests_patient ON clinic_exam_requests(patient_id,status,created_at)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_exam_requests_company ON clinic_exam_requests(company_id,status,scheduled_date)');


    // LinkExpress 2.8.61 — organização da navegação, nome público, moderação em dupla,
    // métricas de WhatsApp e módulo de consumo de gás.
    ensure_column($db, 'users', 'username', 'TEXT');
    ensure_column($db, 'products', 'is_gas_cylinder', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'products', 'cost_price', 'REAL NOT NULL DEFAULT 0');
    ensure_column($db, 'products', 'gas_weight_kg', 'REAL NOT NULL DEFAULT 13');
    $db->exec("CREATE UNIQUE INDEX IF NOT EXISTS idx_users_username_lower ON users(lower(username)) WHERE username IS NOT NULL AND trim(username)<>''");

    $db->exec('CREATE TABLE IF NOT EXISTS moderation_deletion_approvals (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        target_type TEXT NOT NULL,
        target_id INTEGER NOT NULL,
        admin_user_id INTEGER NOT NULL,
        created_at TEXT NOT NULL,
        UNIQUE(target_type,target_id,admin_user_id)
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_moderation_delete_target ON moderation_deletion_approvals(target_type,target_id,created_at)');

    $db->exec('CREATE TABLE IF NOT EXISTS whatsapp_clicks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        visitor_user_id INTEGER,
        target_user_id INTEGER,
        target_company_id INTEGER,
        phone TEXT NOT NULL,
        source TEXT,
        created_at TEXT NOT NULL
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_whatsapp_clicks_month ON whatsapp_clicks(created_at,target_user_id,target_company_id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_whatsapp_clicks_target_user ON whatsapp_clicks(target_user_id,created_at)');

    $db->exec('CREATE TABLE IF NOT EXISTS gas_consumption_alerts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        client_id INTEGER NOT NULL,
        predicted_date TEXT NOT NULL,
        notified_at TEXT NOT NULL,
        UNIQUE(company_id,client_id,predicted_date)
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_gas_alerts_client ON gas_consumption_alerts(client_id,predicted_date)');

    $db->exec("INSERT OR IGNORE INTO segments(slug,name,icon,status,sort_order,allowed_roles,created_at,updated_at) VALUES('gas','Distribuidora de Gás','🔥','active',65,'company',datetime('now'),datetime('now'))");
    $db->exec("UPDATE segments SET name='Distribuidora de Gás',icon='🔥',status='active',allowed_roles='company',updated_at=datetime('now') WHERE slug='gas'");

    // Cadastros antigos já eram vínculos confirmados; apenas novas solicitações usam estados pendentes.
    $db->exec("UPDATE clinic_professionals SET link_state='active' WHERE COALESCE(link_state,'')='' AND status='active'");

    // Pet Shop deve permanecer disponível tanto no cadastro quanto no Marketplace.
    $db->exec("INSERT OR IGNORE INTO segments(slug,name,icon,status,sort_order,allowed_roles,created_at,updated_at) VALUES('petshop','Pet Shop','🐾','active',120,'company,service_provider',datetime('now'),datetime('now'))");
    $db->exec("UPDATE segments SET name='Pet Shop',icon='🐾',status='active',allowed_roles='company,service_provider',updated_at=datetime('now') WHERE slug='petshop'");

    // V2.8.53 — repara clínicas criadas por versões em que o segmento ficou
    // apenas no usuário responsável, evitando o painel voltar para a Home.
    try {
        $db->exec("UPDATE companies SET type='clinic',updated_at=datetime('now') WHERE id IN (SELECT c.id FROM companies c LEFT JOIN users ou ON ou.id=c.owner_user_id LEFT JOIN segments s ON s.slug=c.type WHERE lower(COALESCE(c.type,''))<>'clinic' AND (lower(COALESCE(c.sector,'')) LIKE '%clinic%' OR lower(COALESCE(c.sector,'')) LIKE '%clínic%' OR lower(COALESCE(c.type,'')) LIKE '%clinic%' OR lower(COALESCE(c.type,'')) LIKE '%clínic%' OR lower(COALESCE(s.name,'')) LIKE '%clinic%' OR lower(COALESCE(s.name,'')) LIKE '%clínic%' OR lower(COALESCE(ou.segment_slug,'')) LIKE '%clinic%' OR lower(COALESCE(ou.segment_slug,'')) LIKE '%clínic%' OR EXISTS(SELECT 1 FROM users mu WHERE mu.company_id=c.id AND (lower(COALESCE(mu.segment_slug,'')) LIKE '%clinic%' OR lower(COALESCE(mu.segment_slug,'')) LIKE '%clínic%'))) )");
        $db->exec("UPDATE users SET segment_slug='clinic',updated_at=datetime('now') WHERE company_id IN (SELECT id FROM companies WHERE lower(COALESCE(type,''))='clinic') AND role IN ('company','employee') AND (segment_slug IS NULL OR trim(segment_slug)='')");
    } catch (Throwable $ignored) {}


    // LinkExpress 2.9.00 — permissões de edição, prontuário ampliado, veterinária/pet shop e currículos/vagas.
    $db->exec('CREATE TABLE IF NOT EXISTS company_edit_permission_requests (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        target_type TEXT NOT NULL,
        target_user_id INTEGER NOT NULL,
        requested_by INTEGER NOT NULL,
        reason TEXT,
        status TEXT NOT NULL DEFAULT "pending",
        reviewed_by INTEGER,
        reviewed_at TEXT,
        expires_at TEXT,
        consumed_at TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_company_edit_permissions_company ON company_edit_permission_requests(company_id,status,created_at)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_company_edit_permissions_target ON company_edit_permission_requests(target_user_id,target_type,status)');

    // Dados clínicos completos do paciente.
    foreach ([
        'photo'=>'TEXT','rg'=>'TEXT','sex'=>'TEXT','marital_status'=>'TEXT','occupation'=>'TEXT','sus_card'=>'TEXT',
        'whatsapp'=>'TEXT','residential_phone'=>'TEXT','cep'=>'TEXT','address'=>'TEXT','address_number'=>'TEXT','neighborhood'=>'TEXT',
        'city'=>'TEXT','state'=>'TEXT','complement'=>'TEXT','reference_point'=>'TEXT','emergency_name'=>'TEXT','emergency_relation'=>'TEXT',
        'emergency_phone'=>'TEXT','emergency_whatsapp'=>'TEXT','emergency_notes'=>'TEXT'
    ] as $column=>$definition) ensure_column($db,'clinic_patients',$column,$definition);

    $db->exec('CREATE TABLE IF NOT EXISTS clinic_diagnoses (
        id INTEGER PRIMARY KEY AUTOINCREMENT, company_id INTEGER NOT NULL, patient_id INTEGER NOT NULL, professional_id INTEGER,
        appointment_id INTEGER, diagnosis TEXT NOT NULL, cid TEXT, observations TEXT, evolution TEXT, diagnosed_at TEXT NOT NULL,
        created_at TEXT NOT NULL, updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_diagnoses_patient ON clinic_diagnoses(patient_id,diagnosed_at)');

    $db->exec('CREATE TABLE IF NOT EXISTS clinic_prescriptions (
        id INTEGER PRIMARY KEY AUTOINCREMENT, company_id INTEGER NOT NULL, patient_id INTEGER NOT NULL, professional_id INTEGER NOT NULL,
        appointment_id INTEGER, title TEXT, instructions TEXT, release_to_patient INTEGER NOT NULL DEFAULT 1, status TEXT NOT NULL DEFAULT "active",
        prescribed_at TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS clinic_prescription_items (
        id INTEGER PRIMARY KEY AUTOINCREMENT, prescription_id INTEGER NOT NULL, medicine_name TEXT NOT NULL, dosage TEXT, frequency TEXT,
        duration_text TEXT, start_date TEXT, end_date TEXT, times_csv TEXT, observations TEXT, last_reminder_key TEXT,
        created_at TEXT NOT NULL, updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_prescriptions_patient ON clinic_prescriptions(patient_id,status,prescribed_at)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_prescription_items_rx ON clinic_prescription_items(prescription_id)');

    $db->exec('CREATE TABLE IF NOT EXISTS clinic_patient_documents (
        id INTEGER PRIMARY KEY AUTOINCREMENT, company_id INTEGER NOT NULL, patient_id INTEGER NOT NULL, uploaded_by INTEGER,
        document_type TEXT NOT NULL, title TEXT NOT NULL, file_path TEXT NOT NULL, notes TEXT, created_at TEXT NOT NULL
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_patient_documents_patient ON clinic_patient_documents(patient_id,created_at)');

    // Módulo Veterinária / Pet Shop.
    $db->exec('CREATE TABLE IF NOT EXISTS pet_clients (
        id INTEGER PRIMARY KEY AUTOINCREMENT, company_id INTEGER NOT NULL, platform_user_id INTEGER, name TEXT NOT NULL, document TEXT,
        photo TEXT, phone TEXT, whatsapp TEXT, email TEXT, address TEXT, city TEXT, state TEXT, notes TEXT, status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL, updated_at TEXT NOT NULL, UNIQUE(company_id,platform_user_id)
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS pets (
        id INTEGER PRIMARY KEY AUTOINCREMENT, company_id INTEGER NOT NULL, client_id INTEGER NOT NULL, public_token TEXT NOT NULL UNIQUE,
        name TEXT NOT NULL, photo TEXT, species TEXT, breed TEXT, sex TEXT, birth_date TEXT, weight REAL, color TEXT, size TEXT, microchip TEXT,
        allergies TEXT, medications TEXT, diseases TEXT, notes TEXT, status TEXT NOT NULL DEFAULT "active", created_at TEXT NOT NULL, updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS pet_appointments (
        id INTEGER PRIMARY KEY AUTOINCREMENT, company_id INTEGER NOT NULL, pet_id INTEGER NOT NULL, service_type TEXT NOT NULL,
        service_name TEXT, professional_name TEXT, appointment_date TEXT NOT NULL, appointment_time TEXT NOT NULL, amount REAL NOT NULL DEFAULT 0,
        payment_method TEXT, payment_status TEXT NOT NULL DEFAULT "pending", status TEXT NOT NULL DEFAULT "scheduled", notes TEXT,
        checkin_at TEXT, checkout_at TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL
    )');
    ensure_column($db,'pet_appointments','slot_id','INTEGER');
    $db->exec('CREATE TABLE IF NOT EXISTS pet_booking_slots (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        slot_date TEXT NOT NULL,
        slot_time TEXT NOT NULL,
        service_type TEXT NOT NULL DEFAULT "consultation",
        service_name TEXT,
        amount REAL NOT NULL DEFAULT 0,
        capacity INTEGER NOT NULL DEFAULT 1,
        status TEXT NOT NULL DEFAULT "active",
        created_by INTEGER,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL,
        UNIQUE(company_id,slot_date,slot_time,service_type)
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS pet_medical_records (
        id INTEGER PRIMARY KEY AUTOINCREMENT, company_id INTEGER NOT NULL, pet_id INTEGER NOT NULL, appointment_id INTEGER,
        professional_name TEXT, complaint TEXT, anamnesis TEXT, weight REAL, temperature REAL, diagnosis TEXT, treatment TEXT,
        medications TEXT, allergies TEXT, diseases TEXT, surgeries TEXT, observations TEXT, evolution TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS pet_vaccines (
        id INTEGER PRIMARY KEY AUTOINCREMENT, company_id INTEGER NOT NULL, pet_id INTEGER NOT NULL, vaccine_name TEXT NOT NULL,
        applied_at TEXT, dose TEXT, batch TEXT, professional_name TEXT, next_dose_at TEXT, expires_at TEXT, notes TEXT,
        created_at TEXT NOT NULL, updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS pet_exams (
        id INTEGER PRIMARY KEY AUTOINCREMENT, company_id INTEGER NOT NULL, pet_id INTEGER NOT NULL, exam_type TEXT NOT NULL,
        requested_at TEXT, performed_at TEXT, result_text TEXT, report_text TEXT, file_path TEXT, professional_name TEXT,
        status TEXT NOT NULL DEFAULT "requested", created_at TEXT NOT NULL, updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS pet_prescriptions (
        id INTEGER PRIMARY KEY AUTOINCREMENT, company_id INTEGER NOT NULL, pet_id INTEGER NOT NULL, professional_name TEXT,
        medicine_name TEXT NOT NULL, dosage TEXT, frequency TEXT, duration_text TEXT, orientations TEXT, prescribed_at TEXT NOT NULL,
        created_at TEXT NOT NULL, updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS pet_hospitalizations (
        id INTEGER PRIMARY KEY AUTOINCREMENT, company_id INTEGER NOT NULL, pet_id INTEGER NOT NULL, box_name TEXT, admitted_at TEXT NOT NULL,
        reason TEXT, professional_name TEXT, medications TEXT, feeding TEXT, vital_signs TEXT, evolution TEXT, observations TEXT,
        discharged_at TEXT, status TEXT NOT NULL DEFAULT "admitted", created_at TEXT NOT NULL, updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS pet_stock_movements (
        id INTEGER PRIMARY KEY AUTOINCREMENT, company_id INTEGER NOT NULL, product_id INTEGER NOT NULL, movement_type TEXT NOT NULL,
        qty REAL NOT NULL DEFAULT 0, reason TEXT, created_by INTEGER, created_at TEXT NOT NULL
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS pet_suppliers (
        id INTEGER PRIMARY KEY AUTOINCREMENT, company_id INTEGER NOT NULL, name TEXT NOT NULL, document TEXT, phone TEXT, email TEXT,
        products_notes TEXT, status TEXT NOT NULL DEFAULT "active", created_at TEXT NOT NULL, updated_at TEXT NOT NULL
    )');
    ensure_column($db,'pet_vaccines','reminder_sent_at','TEXT');
    ensure_column($db,'pet_appointments','reminder_sent_at','TEXT');
    // Preferências do módulo veterinário e segregação do estoque do Pet Shop.
    ensure_column($db,'companies','pet_booking_enabled','INTEGER NOT NULL DEFAULT 0');
    ensure_column($db,'companies','pet_opening_time','TEXT NOT NULL DEFAULT "08:00"');
    ensure_column($db,'companies','pet_closing_time','TEXT NOT NULL DEFAULT "18:00"');
    ensure_column($db,'users','company_position','TEXT');
    ensure_column($db,'users','company_access_level','TEXT NOT NULL DEFAULT "staff"');

    // V2.9.10 — exigências individuais de documentação e cadastro completo pelo administrador.
    // V2.9.11 — exclusão segura de produtos sem perder histórico de pedidos.
    ensure_column($db,'products','deleted_at','TEXT');
    ensure_column($db,'users','admin_require_documents','INTEGER NOT NULL DEFAULT 0');
    ensure_column($db,'users','admin_require_profile','INTEGER NOT NULL DEFAULT 0');
    ensure_column($db,'document_requirements','target_user_id','INTEGER');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_document_requirements_user ON document_requirements(target_user_id,status,sort_order)');
    foreach ([
        'brand'=>'TEXT','stock_min'=>'REAL NOT NULL DEFAULT 0','batch'=>'TEXT','expires_at'=>'TEXT',
        'petshop_only'=>'INTEGER NOT NULL DEFAULT 0'
    ] as $column=>$definition) ensure_column($db,'products',$column,$definition);
    $db->exec('CREATE INDEX IF NOT EXISTS idx_pet_clients_company ON pet_clients(company_id,status,name)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_pets_client ON pets(client_id,status,name)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_pet_appointments_company ON pet_appointments(company_id,appointment_date,status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_pet_booking_slots_date ON pet_booking_slots(company_id,slot_date,slot_time,status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_pet_appointments_slot ON pet_appointments(slot_id,status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_pet_vaccines_pet ON pet_vaccines(pet_id,next_dose_at,expires_at)');

    // Área de Currículos e Vagas.
    $db->exec('CREATE TABLE IF NOT EXISTS career_profiles (
        user_id INTEGER PRIMARY KEY, birth_date TEXT, city TEXT, state TEXT, address TEXT, linkedin TEXT, portfolio TEXT,
        desired_job_type TEXT, desired_work_mode TEXT, availability TEXT, salary_expectation REAL, visibility TEXT NOT NULL DEFAULT "registered",
        allow_company_contact INTEGER NOT NULL DEFAULT 1, updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS resumes (
        id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL, title TEXT NOT NULL, objective TEXT, summary TEXT,
        skills TEXT, languages TEXT, additional_info TEXT, salary_expectation REAL, availability TEXT, desired_contract TEXT,
        visibility TEXT NOT NULL DEFAULT "registered", status TEXT NOT NULL DEFAULT "active", created_at TEXT NOT NULL, updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS resume_experiences (
        id INTEGER PRIMARY KEY AUTOINCREMENT, resume_id INTEGER NOT NULL, company_name TEXT NOT NULL, job_title TEXT NOT NULL,
        start_date TEXT, end_date TEXT, activities TEXT, last_salary REAL, leaving_reason TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS resume_education (
        id INTEGER PRIMARY KEY AUTOINCREMENT, resume_id INTEGER NOT NULL, education_level TEXT, institution TEXT, course TEXT,
        completion_year TEXT, certificate_path TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS resume_documents (
        id INTEGER PRIMARY KEY AUTOINCREMENT, resume_id INTEGER NOT NULL, document_type TEXT NOT NULL, title TEXT NOT NULL,
        file_path TEXT NOT NULL, created_at TEXT NOT NULL
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS job_posts (
        id INTEGER PRIMARY KEY AUTOINCREMENT, company_id INTEGER NOT NULL, created_by INTEGER NOT NULL, title TEXT NOT NULL, professional_area TEXT,
        city TEXT, state TEXT, salary_min REAL, salary_max REAL, contract_type TEXT, work_mode TEXT, description TEXT NOT NULL,
        requirements TEXT, status TEXT NOT NULL DEFAULT "active", created_at TEXT NOT NULL, updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS job_applications (
        id INTEGER PRIMARY KEY AUTOINCREMENT, job_id INTEGER NOT NULL, user_id INTEGER NOT NULL, resume_id INTEGER NOT NULL,
        status TEXT NOT NULL DEFAULT "sent", company_notes TEXT, interview_at TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL,
        UNIQUE(job_id,user_id)
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS job_favorites (
        user_id INTEGER NOT NULL, job_id INTEGER NOT NULL, created_at TEXT NOT NULL, PRIMARY KEY(user_id,job_id)
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS resume_views (
        id INTEGER PRIMARY KEY AUTOINCREMENT, resume_id INTEGER NOT NULL, company_id INTEGER, viewed_by INTEGER, viewed_at TEXT NOT NULL
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS candidate_favorites (
        company_id INTEGER NOT NULL, resume_id INTEGER NOT NULL, created_by INTEGER, created_at TEXT NOT NULL,
        PRIMARY KEY(company_id,resume_id)
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS career_subscriptions (
        user_id INTEGER PRIMARY KEY, amount REAL NOT NULL DEFAULT 0, due_date TEXT, status TEXT NOT NULL DEFAULT "active",
        proof_path TEXT, reviewed_by INTEGER, reviewed_at TEXT, notes TEXT, updated_at TEXT NOT NULL
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_resumes_user ON resumes(user_id,status,created_at)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_job_posts_search ON job_posts(status,city,professional_area,created_at)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_job_applications_company ON job_applications(job_id,status,created_at)');
    $db->exec("INSERT OR IGNORE INTO settings(key_name,value,updated_at) VALUES('career_monthly_fee','0',datetime('now'))");

    // V2.9.02 — códigos pesquisáveis de comprovantes e correção de nomes dos tutores vinculados.
    try {
        $rows=$db->query("SELECT id,source_type,source_id,created_at,receipt_code FROM receipts")->fetchAll();
        $upd=$db->prepare('UPDATE receipts SET receipt_code=? WHERE id=?');
        foreach($rows as $row){
            $code=strtoupper(trim((string)($row['receipt_code']??'')));
            if($code==='')$code=strtoupper(substr(hash('sha256',(string)$row['source_type'].'|'.(int)$row['source_id'].'|'.(string)$row['created_at']),0,12));
            $upd->execute([$code,(int)$row['id']]);
        }
    } catch (Throwable $ignored) {}
    try {
        $rows=$db->query("SELECT id,company_id,created_at,receipt_code FROM clinic_appointments")->fetchAll();
        $upd=$db->prepare('UPDATE clinic_appointments SET receipt_code=? WHERE id=?');
        foreach($rows as $row){
            $code=strtoupper(trim((string)($row['receipt_code']??'')));
            if($code==='')$code=strtoupper(substr(hash('sha256','clinic|'.(int)$row['id'].'|'.(string)$row['created_at'].'|'.(int)$row['company_id']),0,12));
            $upd->execute([$code,(int)$row['id']]);
        }
    } catch (Throwable $ignored) {}
    try {
        $rows=$db->query("SELECT id,created_at,receipt_code FROM charges")->fetchAll();
        $upd=$db->prepare('UPDATE charges SET receipt_code=? WHERE id=?');
        foreach($rows as $row){
            $code=strtoupper(trim((string)($row['receipt_code']??'')));
            if($code==='')$code=strtoupper(substr(hash('sha256','charge|'.(int)$row['id'].'|'.(string)$row['created_at']),0,12));
            $upd->execute([$code,(int)$row['id']]);
        }
    } catch (Throwable $ignored) {}
    try {
        $db->exec("UPDATE pet_clients
            SET name=(SELECT u.name FROM users u WHERE u.id=pet_clients.platform_user_id)
            WHERE platform_user_id IS NOT NULL
              AND EXISTS(SELECT 1 FROM users u WHERE u.id=pet_clients.platform_user_id AND trim(COALESCE(u.name,''))<>'')
              AND (
                    trim(COALESCE(name,''))=''
                    OR trim(name)=CAST(platform_user_id AS TEXT)
                    OR upper(trim(name))=(SELECT upper(trim(COALESCE(u.public_id,''))) FROM users u WHERE u.id=pet_clients.platform_user_id)
                  )");
    } catch (Throwable $ignored) {}

    // V2.9.03 — agenda veterinária em lote e horário de funcionamento.
    try {
        $db->exec("UPDATE companies SET pet_opening_time='08:00' WHERE trim(COALESCE(pet_opening_time,''))=''");
        $db->exec("UPDATE companies SET pet_closing_time='18:00' WHERE trim(COALESCE(pet_closing_time,''))=''");
    } catch (Throwable $ignored) {}

    // V2.9.16 — correções de agenda clínica, Pix, tarifa de corrida, bancos e lotação.
    // Todos os campos são incrementais para preservar instalações/bancos já existentes.
    ensure_column($db, 'clinic_settings', 'daily_consultation_limit', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'clinic_settings', 'daily_exam_limit', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'clinic_settings', 'checkin_radius_meters', 'INTEGER NOT NULL DEFAULT 250');
    ensure_column($db, 'clinic_professionals', 'daily_consultation_limit', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'clinic_professionals', 'daily_exam_limit', 'INTEGER NOT NULL DEFAULT 0');
    ensure_column($db, 'clinic_appointments', 'booking_mode', 'TEXT NOT NULL DEFAULT "time"');
    ensure_column($db, 'clinic_appointments', 'arrival_order', 'INTEGER');
    ensure_column($db, 'clinic_appointments', 'checkin_at', 'TEXT');
    ensure_column($db, 'clinic_appointments', 'checkin_lat', 'REAL');
    ensure_column($db, 'clinic_appointments', 'checkin_lng', 'REAL');
    ensure_column($db, 'clinic_appointments', 'checkin_distance_m', 'REAL');
    ensure_column($db, 'clinic_appointments', 'payment_proof', 'TEXT');
    ensure_column($db, 'clinic_appointments', 'payment_proof_uploaded_at', 'TEXT');
    ensure_column($db, 'clinic_exam_requests', 'payment_method', 'TEXT');
    ensure_column($db, 'clinic_exam_requests', 'payment_status', 'TEXT NOT NULL DEFAULT "pending"');
    ensure_column($db, 'clinic_exam_requests', 'payment_proof', 'TEXT');
    ensure_column($db, 'clinic_exam_requests', 'payment_proof_uploaded_at', 'TEXT');
    ensure_column($db, 'banks', 'account_creation_url', 'TEXT');
    ensure_column($db, 'rides', 'client_fare_status', 'TEXT NOT NULL DEFAULT "not_required"');
    ensure_column($db, 'rides', 'client_fare_responded_at', 'TEXT');
    $db->exec("CREATE INDEX IF NOT EXISTS idx_clinic_daily_professional ON clinic_appointments(professional_id,appointment_date,status)");
    $db->exec("CREATE INDEX IF NOT EXISTS idx_clinic_exam_daily_professional ON clinic_exam_requests(professional_id,scheduled_date,status)");

    // V2.9.17 — Fila Virtual com Dia Marcado.
    $db->exec('CREATE TABLE IF NOT EXISTS clinic_virtual_queue (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        patient_id INTEGER NOT NULL,
        platform_user_id INTEGER NOT NULL,
        specialty_id INTEGER,
        professional_id INTEGER,
        queue_date TEXT NOT NULL,
        queue_number INTEGER NOT NULL,
        status TEXT NOT NULL DEFAULT "scheduled",
        joined_at TEXT,
        called_at TEXT,
        completed_at TEXT,
        cancelled_at TEXT,
        next_notified_at TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_virtual_queue_day ON clinic_virtual_queue(company_id,queue_date,status,queue_number)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_virtual_queue_user ON clinic_virtual_queue(platform_user_id,queue_date,status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_virtual_queue_professional ON clinic_virtual_queue(professional_id,queue_date,status,queue_number)');

    // V2.9.19 — Tarifas por transporte e agenda clínica recorrente.
    ensure_column($db, 'clinic_virtual_queue', 'queue_time', 'TEXT');
    ensure_column($db, 'clinic_virtual_queue', 'attendance_mode', 'TEXT NOT NULL DEFAULT "day_queue"');
    ensure_column($db, 'clinic_virtual_queue', 'skipped_at', 'TEXT');
    ensure_column($db, 'clinic_virtual_queue', 'recalled_at', 'TEXT');
    ensure_column($db, 'clinic_virtual_queue', 'transferred_from_professional_id', 'INTEGER');
    ensure_column($db, 'clinic_virtual_queue', 'sort_order', 'INTEGER NOT NULL DEFAULT 0');

    $db->exec('CREATE TABLE IF NOT EXISTS clinic_queue_settings (
        company_id INTEGER PRIMARY KEY,
        attendance_mode TEXT NOT NULL DEFAULT "day_queue",
        agenda_open INTEGER NOT NULL DEFAULT 1,
        start_time TEXT NOT NULL DEFAULT "08:00",
        end_time TEXT NOT NULL DEFAULT "18:00",
        max_clients_per_day INTEGER NOT NULL DEFAULT 50,
        average_minutes INTEGER NOT NULL DEFAULT 15,
        allow_reorder INTEGER NOT NULL DEFAULT 0,
        message_near TEXT NOT NULL DEFAULT "Seu atendimento está próximo.",
        message_next TEXT NOT NULL DEFAULT "Você é o próximo!",
        message_turn TEXT NOT NULL DEFAULT "É sua vez! Dirija-se ao atendimento.",
        message_cancelled TEXT NOT NULL DEFAULT "Seu atendimento foi cancelado.",
        message_changed TEXT NOT NULL DEFAULT "Seu horário foi alterado.",
        updated_by INTEGER,
        updated_at TEXT
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS clinic_queue_days (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        professional_id INTEGER,
        service_id INTEGER,
        service_date TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT "open",
        start_time TEXT,
        end_time TEXT,
        max_clients INTEGER,
        note TEXT,
        created_by INTEGER,
        created_at TEXT NOT NULL,
        updated_at TEXT,
        UNIQUE(company_id,professional_id,service_id,service_date)
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_queue_days_date ON clinic_queue_days(company_id,service_date,status)');
    $db->exec('CREATE TABLE IF NOT EXISTS clinic_queue_weekly_rules (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        professional_id INTEGER,
        service_id INTEGER,
        weekday INTEGER NOT NULL,
        status TEXT NOT NULL DEFAULT "open",
        start_time TEXT,
        end_time TEXT,
        max_clients INTEGER,
        effective_from TEXT NOT NULL,
        effective_until TEXT,
        note TEXT,
        created_by INTEGER,
        created_at TEXT NOT NULL,
        updated_at TEXT,
        UNIQUE(company_id,professional_id,service_id,weekday)
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_queue_weekly ON clinic_queue_weekly_rules(company_id,weekday,status)');
    $db->exec('CREATE TABLE IF NOT EXISTS clinic_queue_client_blocks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        platform_user_id INTEGER NOT NULL,
        blocked INTEGER NOT NULL DEFAULT 1,
        reason TEXT,
        updated_by INTEGER,
        updated_at TEXT NOT NULL,
        UNIQUE(company_id,platform_user_id)
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_clinic_queue_blocks_company ON clinic_queue_client_blocks(company_id,blocked)');


    // V3.1 — correções de agenda/pagamento + módulo de viagens de ônibus.
    ensure_column($db, 'services', 'booking_mode', 'TEXT NOT NULL DEFAULT "time"');
    ensure_column($db, 'services', 'booking_start_date', 'TEXT');
    ensure_column($db, 'services', 'booking_end_date', 'TEXT');

    $db->exec('CREATE TABLE IF NOT EXISTS bus_companies (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL UNIQUE,
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS bus_vehicles (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        identification TEXT NOT NULL,
        plate TEXT,
        model TEXT,
        seats_count INTEGER NOT NULL DEFAULT 0,
        layout_json TEXT NOT NULL DEFAULT "[]",
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS bus_routes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        name TEXT NOT NULL,
        origin_city TEXT NOT NULL,
        final_destination_city TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT "active",
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS bus_route_stops (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        route_id INTEGER NOT NULL,
        city TEXT NOT NULL,
        boarding_location TEXT,
        dropoff_location TEXT,
        estimated_time TEXT,
        allow_boarding INTEGER NOT NULL DEFAULT 1,
        allow_dropoff INTEGER NOT NULL DEFAULT 1,
        stop_order INTEGER NOT NULL,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(route_id, stop_order),
        FOREIGN KEY(route_id) REFERENCES bus_routes(id) ON DELETE CASCADE
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS bus_trips (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        company_id INTEGER NOT NULL,
        route_id INTEGER NOT NULL,
        bus_id INTEGER NOT NULL,
        driver_user_id INTEGER,
        trip_date TEXT NOT NULL,
        departure_time TEXT NOT NULL,
        final_arrival_time TEXT,
        base_price REAL NOT NULL DEFAULT 0,
        status TEXT NOT NULL DEFAULT "draft",
        published_at TEXT,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT,
        FOREIGN KEY(route_id) REFERENCES bus_routes(id),
        FOREIGN KEY(bus_id) REFERENCES bus_vehicles(id)
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS bus_trip_stops (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        trip_id INTEGER NOT NULL,
        route_stop_id INTEGER NOT NULL,
        city TEXT NOT NULL,
        boarding_location TEXT,
        dropoff_location TEXT,
        estimated_time TEXT,
        allow_boarding INTEGER NOT NULL DEFAULT 1,
        allow_dropoff INTEGER NOT NULL DEFAULT 1,
        stop_order INTEGER NOT NULL,
        UNIQUE(trip_id, stop_order),
        UNIQUE(trip_id, route_stop_id),
        FOREIGN KEY(trip_id) REFERENCES bus_trips(id) ON DELETE CASCADE,
        FOREIGN KEY(route_stop_id) REFERENCES bus_route_stops(id)
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS bus_seats (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        bus_id INTEGER NOT NULL,
        seat_number TEXT NOT NULL,
        row_number INTEGER,
        column_number INTEGER,
        seat_type TEXT NOT NULL DEFAULT "normal",
        position TEXT,
        active INTEGER NOT NULL DEFAULT 1,
        UNIQUE(bus_id, seat_number),
        FOREIGN KEY(bus_id) REFERENCES bus_vehicles(id) ON DELETE CASCADE
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS bus_passengers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        platform_user_id INTEGER,
        owner_user_id INTEGER,
        full_name TEXT NOT NULL,
        document TEXT,
        birth_date TEXT,
        phone TEXT,
        platform_public_id TEXT,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS bus_bookings (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        booking_code TEXT NOT NULL UNIQUE,
        trip_id INTEGER NOT NULL,
        passenger_id INTEGER NOT NULL,
        seat_id INTEGER NOT NULL,
        origin_stop_id INTEGER NOT NULL,
        destination_stop_id INTEGER NOT NULL,
        buyer_user_id INTEGER NOT NULL,
        amount REAL NOT NULL DEFAULT 0,
        payment_method TEXT,
        payment_status TEXT NOT NULL DEFAULT "pending",
        payment_proof TEXT,
        status TEXT NOT NULL DEFAULT "reserved",
        temporary_expires_at TEXT,
        qr_token TEXT UNIQUE,
        digital_ticket_path TEXT,
        original_pdf_path TEXT,
        cancellation_reason TEXT,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT,
        FOREIGN KEY(trip_id) REFERENCES bus_trips(id),
        FOREIGN KEY(passenger_id) REFERENCES bus_passengers(id),
        FOREIGN KEY(seat_id) REFERENCES bus_seats(id),
        FOREIGN KEY(origin_stop_id) REFERENCES bus_trip_stops(id),
        FOREIGN KEY(destination_stop_id) REFERENCES bus_trip_stops(id)
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS bus_boarding_events (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        booking_id INTEGER NOT NULL,
        trip_stop_id INTEGER NOT NULL,
        driver_user_id INTEGER,
        event_type TEXT NOT NULL CHECK(event_type IN ("boarding","dropoff")),
        status TEXT NOT NULL DEFAULT "confirmed",
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY(booking_id) REFERENCES bus_bookings(id) ON DELETE CASCADE,
        FOREIGN KEY(trip_stop_id) REFERENCES bus_trip_stops(id)
    )');
    $db->exec('CREATE TABLE IF NOT EXISTS bus_trip_driver_notes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        trip_id INTEGER NOT NULL,
        driver_user_id INTEGER,
        note TEXT NOT NULL,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY(trip_id) REFERENCES bus_trips(id) ON DELETE CASCADE
    )');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_bus_routes_company ON bus_routes(company_id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_bus_route_stops_route ON bus_route_stops(route_id, stop_order)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_bus_trips_search ON bus_trips(company_id, trip_date, status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_bus_trip_stops_trip ON bus_trip_stops(trip_id, stop_order)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_bus_bookings_trip ON bus_bookings(trip_id, status)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_bus_bookings_passenger ON bus_bookings(passenger_id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_bus_bookings_buyer ON bus_bookings(buyer_user_id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_bus_boarding_booking ON bus_boarding_events(booking_id, event_type)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_bus_passengers_owner ON bus_passengers(owner_user_id)');

    seed_database($db);
    // Executa novamente após o seed para que instalações novas também recebam IDs aleatórios imediatamente.
    migrate_random_public_user_ids($db);
}


function seed_default_settings(PDO $db): void {
    $defaults = [
        'company_name' => 'LinkExpress',
        'brand_logo' => '',
        'primary_color' => '#5b5cf0',
        'secondary_color' => '#00d4a6',
        'whatsapp' => '5571999999999',
        'support_email' => 'suporte@linkexpress.local',
        'pix_key' => '',
        'subscription_payment_link' => '',
        'maintenance_mode' => '0',
        'allow_public_registration' => '1',
        'platform_city' => 'Salvador',
        'platform_state' => 'BA',
        'ride_price_km' => '3.50',
        'ride_base_fare' => '6.00',
        'ride_min_fare' => '10.00',
        'ride_auto_fare_enabled' => '1',
        'ride_mototaxi_price_km' => '3.50',
        'ride_mototaxi_base_fare' => '6.00',
        'ride_mototaxi_min_fare' => '10.00',
        'ride_mototaxi_auto_fare_enabled' => '0',
        'ride_taxi_price_km' => '3.50',
        'ride_taxi_base_fare' => '6.00',
        'ride_taxi_min_fare' => '10.00',
        'ride_taxi_auto_fare_enabled' => '0',
        'ride_private_driver_price_km' => '3.50',
        'ride_private_driver_base_fare' => '6.00',
        'ride_private_driver_min_fare' => '10.00',
        'ride_private_driver_auto_fare_enabled' => '0',
        'moving_price_km' => '4.50',
        'moving_floor_fee' => '25',
        'moving_helper_fee' => '120',
        'moving_assembly_fee' => '150',
        'moving_packaging_fee' => '100',
        'max_products_per_company' => '0',
        'max_publications_per_user' => '0',
        'service_provider_radius_km' => '30',
        'wallet_min_withdrawal' => '10'
    ];
    $stmt = $db->prepare('INSERT OR IGNORE INTO settings(key_name,value,updated_at) VALUES(?,?,?)');
    foreach ($defaults as $key => $value) $stmt->execute([$key,$value,now()]);
}

function seed_database(PDO $db) {
    // V2.8.42 — o seed é somente de instalação. Antes, "companies = 0"
    // fazia Sabor Express e os usuários demo renascerem após exclusão e podia
    // causar UNIQUE users.email / HTTP 500 no request seguinte.
    seed_default_settings($db);

    $marker = $db->prepare('SELECT value FROM settings WHERE key_name=? LIMIT 1');
    $marker->execute(['initial_seed_completed']);
    if ((string)$marker->fetchColumn() === '1') return;

    // Banco já em uso vindo de versão anterior: preserve exatamente o que o
    // administrador manteve/apagou e apenas marque o seed como concluído.
    $existingUsers = (int)$db->query('SELECT COUNT(*) FROM users')->fetchColumn();
    $existingCompanies = (int)$db->query('SELECT COUNT(*) FROM companies')->fetchColumn();
    if ($existingUsers > 0 || $existingCompanies > 0) {
        $mark = $db->prepare('INSERT INTO settings(key_name,value,updated_at) VALUES(?,?,?) ON CONFLICT(key_name) DO UPDATE SET value=excluded.value,updated_at=excluded.updated_at');
        $mark->execute(['initial_seed_completed','1',now()]);
        return;
    }

    $ownsTransaction = !$db->inTransaction();
    if ($ownsTransaction) $db->beginTransaction();
    try {
        $count = (int)$db->query('SELECT COUNT(*) FROM plans')->fetchColumn();
        if ($count === 0) {
            $stmt = $db->prepare('INSERT INTO plans(name,price,duration_days,features_json,status,created_at) VALUES(?,?,?,?,?,?)');
            $stmt->execute(['Essencial', 49.90, 30, json_encode(['Painel completo','Suporte padrão','Até 200 cadastros']), 'active', now()]);
            $stmt->execute(['Profissional', 99.90, 30, json_encode(['Cadastros ilimitados','Relatórios avançados','Destaque na plataforma']), 'active', now()]);
            $stmt->execute(['Premium', 179.90, 30, json_encode(['Todos os módulos','Suporte prioritário','Banners patrocinados']), 'active', now()]);
        }

        $stmt = $db->prepare('INSERT INTO users(role,name,email,phone,password_hash,status,work_status,subscription_expires_at,last_seen,last_ip,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?)');
        $stmt->execute([
            'super_admin','Administrador LinkExpress',config('default_admin_email'),'71999999999',password_hash(config('default_admin_password'), PASSWORD_DEFAULT),'active','online',date('Y-m-d', strtotime('+10 years')),now(),'127.0.0.1',now()
        ]);

        $planId = (int)$db->query('SELECT id FROM plans ORDER BY id LIMIT 1')->fetchColumn();
        $db->prepare('INSERT INTO companies(plan_id,type,name,slug,document,phone,email,address,city,state,description,open_status,status,created_at,updated_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)')
            ->execute([$planId,'delivery','Sabor Express','sabor-express','12.345.678/0001-90','71988887777','contato@saborexpress.local','Av. Central, 120','Salvador','BA','Restaurante parceiro demonstrativo.','open','active',now(),now()]);
        $companyId = (int)$db->lastInsertId();
        $db->prepare('INSERT INTO users(company_id,role,name,email,phone,password_hash,status,work_status,subscription_expires_at,created_at) VALUES(?,?,?,?,?,?,?,?,?,?)')
            ->execute([$companyId,'company','Gestor Sabor Express','empresa@linkexpress.local','71988887777',password_hash('123456', PASSWORD_DEFAULT),'active','online',date('Y-m-d', strtotime('+30 days')),now()]);
        $ownerId = (int)$db->lastInsertId();
        $db->prepare('UPDATE companies SET owner_user_id=? WHERE id=?')->execute([$ownerId,$companyId]);

        $demoUsers = [
            ['client','Cliente Demonstração','cliente@linkexpress.local'],
            ['courier','Entregador Demonstração','entregador@linkexpress.local'],
            ['mototaxi','Mototaxista Demonstração','mototaxi@linkexpress.local'],
            ['service_provider','Prestador Demonstração','prestador@linkexpress.local'],
            ['property_owner','Proprietário Demonstração','imoveis@linkexpress.local'],
            ['rental_company','Locadora Demonstração','locadora@linkexpress.local'],
        ];
        foreach ($demoUsers as $u) {
            $db->prepare('INSERT INTO users(role,name,email,phone,password_hash,status,work_status,subscription_expires_at,created_at) VALUES(?,?,?,?,?,?,?,?,?)')
                ->execute([$u[0],$u[1],$u[2],'71977776666',password_hash('123456', PASSWORD_DEFAULT),'active','available',date('Y-m-d', strtotime('+30 days')),now()]);
        }

        $catStmt = $db->prepare('INSERT INTO categories(company_id,module,name,icon,status,sort_order,created_at) VALUES(?,?,?,?,?,?,?)');
        $catStmt->execute([$companyId,'products','Lanches','🍔','active',1,now()]);
        $cat1 = (int)$db->lastInsertId();
        $catStmt->execute([$companyId,'products','Bebidas','🥤','active',2,now()]);
        $cat2 = (int)$db->lastInsertId();
        $prod = $db->prepare('INSERT INTO products(company_id,category_id,name,description,price,stock,unit,image,status,featured,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?)');
        $prod->execute([$companyId,$cat1,'X-Burger Especial','Pão, carne, queijo, salada e molho da casa.',24.90,100,'un','', 'active',1,now()]);
        $prod->execute([$companyId,$cat2,'Refrigerante 2L','Escolha o sabor no campo de observações.',12.00,80,'un','', 'active',0,now()]);

        $providerId = (int)$db->query("SELECT id FROM users WHERE role='service_provider' LIMIT 1")->fetchColumn();
        $db->prepare('INSERT INTO categories(module,name,icon,status,sort_order,created_at) VALUES(?,?,?,?,?,?)')->execute(['services','Manutenção Residencial','🛠️','active',1,now()]);
        $serviceCat = (int)$db->lastInsertId();
        $db->prepare('INSERT INTO services(provider_id,category_id,name,description,price,price_type,status,created_at) VALUES(?,?,?,?,?,?,?,?)')
            ->execute([$providerId,$serviceCat,'Instalação elétrica','Visita técnica e pequenos reparos elétricos.',120.00,'from','active',now()]);

        $ownerProp = (int)$db->query("SELECT id FROM users WHERE role='property_owner' LIMIT 1")->fetchColumn();
        $db->prepare('INSERT INTO properties(owner_id,title,type,purpose,description,address,city,state,price,bedrooms,bathrooms,garage,area,image,status,created_at,updated_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)')
            ->execute([$ownerProp,'Apartamento moderno no centro','apartment','rent','Imóvel ventilado, próximo a mercados e transporte.','Rua das Flores, 55','Salvador','BA',1800,2,2,1,72,'','available',now(),now()]);

        $rentalOwner = (int)$db->query("SELECT id FROM users WHERE role='rental_company' LIMIT 1")->fetchColumn();
        $db->prepare('INSERT INTO companies(owner_user_id,plan_id,type,name,slug,phone,email,city,state,description,open_status,status,created_at,updated_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)')
            ->execute([$rentalOwner,$planId,'rental','LinkCar Locadora','linkcar-locadora','71966665555','locadora@linkexpress.local','Salvador','BA','Locadora demonstrativa da plataforma.','open','active',now(),now()]);
        $rentalCompany = (int)$db->lastInsertId();
        $db->prepare('UPDATE users SET company_id=? WHERE id=?')->execute([$rentalCompany,$rentalOwner]);
        $db->prepare('INSERT INTO vehicles(company_id,owner_user_id,type,brand,model,year,plate,color,daily_rate,mileage,image,status,created_at,updated_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)')
            ->execute([$rentalCompany,$rentalOwner,'car','Fiat','Argo',2024,'ABC1D23','Branco',149.90,18500,'','available',now(),now()]);

        $db->prepare('INSERT INTO banners(title,subtitle,image,link,position,status,sort_order,created_at) VALUES(?,?,?,?,?,?,?,?)')
            ->execute(['Tudo em um só lugar','Delivery, mobilidade, serviços, imóveis e locação em uma plataforma única.','','#servicos','home','active',1,now()]);

        $mark = $db->prepare('INSERT INTO settings(key_name,value,updated_at) VALUES(?,?,?) ON CONFLICT(key_name) DO UPDATE SET value=excluded.value,updated_at=excluded.updated_at');
        $mark->execute(['initial_seed_completed','1',now()]);
        if ($ownsTransaction) $db->commit();
    } catch (Throwable $e) {
        if ($ownsTransaction && $db->inTransaction()) $db->rollBack();
        throw $e;
    }


}
