<?php

require_once __DIR__ . '/helpers.php';
require_once __DIR__ . '/locations.php';

function trip_table_exists(string $table): bool
{
    $row = fetch_one(
        'SELECT 1
         FROM information_schema.tables
         WHERE table_schema = DATABASE()
           AND table_name = ?
         LIMIT 1',
        's',
        [$table]
    );

    return $row !== null;
}

function trip_column_exists(string $table, string $column): bool
{
    $row = fetch_one(
        'SELECT 1
         FROM information_schema.columns
         WHERE table_schema = DATABASE()
           AND table_name = ?
           AND column_name = ?
         LIMIT 1',
        'ss',
        [$table, $column]
    );

    return $row !== null;
}

function trip_add_column_if_missing(string $table, string $column, string $definition): void
{
    if (!trip_column_exists($table, $column)) {
        db()->query('ALTER TABLE ' . $table . ' ADD COLUMN ' . $column . ' ' . $definition);
    }
}

function trip_ensure_schema(): void
{
    trip_add_column_if_missing('expense_trips', 'destination', 'VARCHAR(150) NOT NULL DEFAULT "" AFTER travel_type');
    trip_add_column_if_missing('expense_trips', 'business_purpose', 'VARCHAR(255) NOT NULL DEFAULT "" AFTER destination');
    trip_add_column_if_missing('expense_trips', 'is_visa_required', 'TINYINT(1) NOT NULL DEFAULT 0 AFTER destination');
    trip_add_column_if_missing('expense_trips', 'cover_image_path', 'VARCHAR(255) NOT NULL DEFAULT "" AFTER end_date');
    trip_add_column_if_missing('expense_trips', 'is_deleted', 'TINYINT(1) NOT NULL DEFAULT 0 AFTER cover_image_path');
    trip_add_column_if_missing('expense_trips', 'deleted_at', 'DATETIME NULL DEFAULT NULL AFTER is_deleted');
    trip_add_column_if_missing('expense_trips', 'deleted_by', 'INT NULL DEFAULT NULL AFTER deleted_at');
    trip_add_column_if_missing('expense_trips', 'deleted_location', 'VARCHAR(190) NOT NULL DEFAULT "" AFTER deleted_by');
    trip_add_column_if_missing('expense_trips', 'deleted_reason', 'VARCHAR(255) NOT NULL DEFAULT "" AFTER deleted_location');

    $statusColumn = fetch_one(
        'SELECT column_type
         FROM information_schema.columns
         WHERE table_schema = DATABASE()
           AND table_name = ?
           AND column_name = ?
         LIMIT 1',
        'ss',
        ['expense_trips', 'status']
    );

    $statusType = strtolower((string) ($statusColumn['column_type'] ?? ''));
    if ($statusType !== '' && strpos($statusType, 'archived') === false) {
        db()->query(
            'ALTER TABLE expense_trips
             MODIFY status ENUM("planned", "active", "completed", "archived", "cancelled") NOT NULL DEFAULT "planned"'
        );
    }

    if (trip_column_exists('expense_trips', 'purpose')) {
        db()->query(
            'UPDATE expense_trips
             SET business_purpose = COALESCE(NULLIF(TRIM(business_purpose), ""), purpose)
             WHERE (business_purpose IS NULL OR business_purpose = "")
               AND purpose IS NOT NULL
               AND purpose <> ""'
        );
    }

    $tripTables = [
        'expense_trip_itineraries' => <<<SQL
CREATE TABLE IF NOT EXISTS expense_trip_itineraries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    trip_id INT NOT NULL,
    mode ENUM('flight', 'hotel', 'car', 'bus', 'train') NOT NULL DEFAULT 'flight',
    leg_type ENUM('one_way', 'round_trip', 'multi_city', 'single') NOT NULL DEFAULT 'single',
    item_title VARCHAR(150) NOT NULL DEFAULT '',
    sort_order INT NOT NULL DEFAULT 0,
    payload_json LONGTEXT NULL DEFAULT NULL,
    status ENUM('draft', 'active', 'cancelled') NOT NULL DEFAULT 'draft',
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,
    deleted_at DATETIME NULL DEFAULT NULL,
    deleted_by INT NULL DEFAULT NULL,
    deleted_location VARCHAR(190) NOT NULL DEFAULT '',
    deleted_reason VARCHAR(255) NOT NULL DEFAULT '',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_trip_itineraries_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_trip_itineraries_trip FOREIGN KEY (trip_id) REFERENCES expense_trips(id) ON DELETE CASCADE,
    INDEX idx_trip_itineraries_trip_mode (trip_id, mode, sort_order)
)
SQL,
        'expense_trip_comments' => <<<SQL
CREATE TABLE IF NOT EXISTS expense_trip_comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    trip_id INT NOT NULL,
    comment_text LONGTEXT NOT NULL,
    comment_type ENUM('comment', 'system') NOT NULL DEFAULT 'comment',
    parent_comment_id INT NULL DEFAULT NULL,
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,
    deleted_at DATETIME NULL DEFAULT NULL,
    deleted_by INT NULL DEFAULT NULL,
    deleted_location VARCHAR(190) NOT NULL DEFAULT '',
    deleted_reason VARCHAR(255) NOT NULL DEFAULT '',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_trip_comments_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_trip_comments_trip FOREIGN KEY (trip_id) REFERENCES expense_trips(id) ON DELETE CASCADE,
    CONSTRAINT fk_trip_comments_parent FOREIGN KEY (parent_comment_id) REFERENCES expense_trip_comments(id) ON DELETE SET NULL,
    INDEX idx_trip_comments_trip_time (trip_id, created_at)
)
SQL,
        'expense_trip_events' => <<<SQL
CREATE TABLE IF NOT EXISTS expense_trip_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    trip_id INT NOT NULL,
    event_type VARCHAR(80) NOT NULL,
    event_title VARCHAR(150) NOT NULL DEFAULT '',
    event_description VARCHAR(500) NOT NULL DEFAULT '',
    payload_json LONGTEXT NULL DEFAULT NULL,
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,
    deleted_at DATETIME NULL DEFAULT NULL,
    deleted_by INT NULL DEFAULT NULL,
    deleted_location VARCHAR(190) NOT NULL DEFAULT '',
    deleted_reason VARCHAR(255) NOT NULL DEFAULT '',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_trip_events_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_trip_events_trip FOREIGN KEY (trip_id) REFERENCES expense_trips(id) ON DELETE CASCADE,
    INDEX idx_trip_events_trip_time (trip_id, created_at)
)
SQL,
        'expense_trip_documents' => <<<SQL
CREATE TABLE IF NOT EXISTS expense_trip_documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    trip_id INT NOT NULL,
    document_title VARCHAR(150) NOT NULL DEFAULT '',
    file_path VARCHAR(255) NOT NULL DEFAULT '',
    file_type VARCHAR(80) NOT NULL DEFAULT '',
    file_size INT NOT NULL DEFAULT 0,
    notes VARCHAR(255) NOT NULL DEFAULT '',
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,
    deleted_at DATETIME NULL DEFAULT NULL,
    deleted_by INT NULL DEFAULT NULL,
    deleted_location VARCHAR(190) NOT NULL DEFAULT '',
    deleted_reason VARCHAR(255) NOT NULL DEFAULT '',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_trip_documents_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_trip_documents_trip FOREIGN KEY (trip_id) REFERENCES expense_trips(id) ON DELETE CASCADE,
    INDEX idx_trip_documents_trip_time (trip_id, created_at)
)
SQL,
    ];

    foreach ($tripTables as $sql) {
        db()->query($sql);
    }
}

function trip_country_options(): array
{
    $options = get_country_names(db());
    return array_values(array_unique(array_map('trim', $options)));
}

function trip_planner_location_options(): array
{
    return [
        ['label' => 'Amery (AHH)', 'detail' => 'Municipal', 'country' => 'U.S.A'],
        ['label' => 'Alpha (ABH)', 'detail' => 'Alpha', 'country' => 'Australia'],
        ['label' => 'Aiken (AIK)', 'detail' => 'Municipal', 'country' => 'U.S.A'],
        ['label' => 'El Arish (AAC)', 'detail' => 'El Arish International Airport', 'country' => 'Egypt'],
        ['label' => 'Alderney (ACI)', 'detail' => 'The Blaye', 'country' => 'Guernsey'],
        ['label' => 'Afutara (AFT)', 'detail' => 'Afutara Aerodrome', 'country' => 'Solomon Islands'],
        ['label' => 'Ahuas', 'detail' => '', 'country' => 'Honduras'],
        ['label' => 'Akjoujt', 'detail' => '', 'country' => 'Mauritania'],
        ['label' => 'Agrinion', 'detail' => '', 'country' => 'Greece'],
        ['label' => 'Ainsworth', 'detail' => '', 'country' => 'U.S.A'],
        ['label' => 'Al Asad', 'detail' => '', 'country' => 'Iraq'],
        ['label' => 'Agana', 'detail' => '', 'country' => 'Guam'],
        ['label' => 'Aktyubinsk', 'detail' => '', 'country' => 'Kazakhstan'],
        ['label' => 'Auckland', 'detail' => 'Auckland Airport', 'country' => 'New Zealand'],
        ['label' => 'Amsterdam', 'detail' => 'Schiphol', 'country' => 'Netherlands'],
        ['label' => 'Athens', 'detail' => 'Eleftherios Venizelos', 'country' => 'Greece'],
        ['label' => 'Abu Dhabi', 'detail' => 'Zayed International Airport', 'country' => 'United Arab Emirates'],
        ['label' => 'Addis Ababa', 'detail' => 'Bole International Airport', 'country' => 'Ethiopia'],
        ['label' => 'Bangkok', 'detail' => 'Suvarnabhumi', 'country' => 'Thailand'],
        ['label' => 'Barcelona', 'detail' => 'El Prat', 'country' => 'Spain'],
        ['label' => 'Beijing', 'detail' => 'Capital', 'country' => 'China'],
        ['label' => 'Berlin', 'detail' => 'Brandenburg', 'country' => 'Germany'],
        ['label' => 'Bogota', 'detail' => 'El Dorado', 'country' => 'Colombia'],
        ['label' => 'Brussels', 'detail' => 'Brussels Airport', 'country' => 'Belgium'],
        ['label' => 'Budapest', 'detail' => 'Ferenc Liszt', 'country' => 'Hungary'],
        ['label' => 'Buenos Aires', 'detail' => 'Ezeiza', 'country' => 'Argentina'],
        ['label' => 'Cairo', 'detail' => 'Cairo International Airport', 'country' => 'Egypt'],
        ['label' => 'Cape Town', 'detail' => 'Cape Town International', 'country' => 'South Africa'],
        ['label' => 'Chicago', 'detail' => 'O Hare International', 'country' => 'U.S.A'],
        ['label' => 'Delhi', 'detail' => 'Indira Gandhi International', 'country' => 'India'],
        ['label' => 'Dubai', 'detail' => 'Dubai International Airport', 'country' => 'United Arab Emirates'],
        ['label' => 'Frankfurt', 'detail' => 'Frankfurt Airport', 'country' => 'Germany'],
        ['label' => 'Geneva', 'detail' => 'Geneva Airport', 'country' => 'Switzerland'],
        ['label' => 'Hanoi', 'detail' => 'Noi Bai', 'country' => 'Vietnam'],
        ['label' => 'Hong Kong', 'detail' => 'Hong Kong International', 'country' => 'Hong Kong'],
        ['label' => 'Istanbul', 'detail' => 'Istanbul Airport', 'country' => 'Turkey'],
        ['label' => 'Jakarta', 'detail' => 'Soekarno Hatta', 'country' => 'Indonesia'],
        ['label' => 'Johannesburg', 'detail' => 'O. R. Tambo', 'country' => 'South Africa'],
        ['label' => 'Kathmandu', 'detail' => 'Tribhuvan International', 'country' => 'Nepal'],
        ['label' => 'Kuala Lumpur', 'detail' => 'KLIA', 'country' => 'Malaysia'],
        ['label' => 'Lagos', 'detail' => 'Murtala Muhammed', 'country' => 'Nigeria'],
        ['label' => 'London', 'detail' => 'Heathrow', 'country' => 'United Kingdom'],
        ['label' => 'Los Angeles', 'detail' => 'LAX', 'country' => 'U.S.A'],
        ['label' => 'Madrid', 'detail' => 'Adolfo Suarez Madrid-Barajas', 'country' => 'Spain'],
        ['label' => 'Manila', 'detail' => 'Ninoy Aquino International', 'country' => 'Philippines'],
        ['label' => 'Melbourne', 'detail' => 'Melbourne Airport', 'country' => 'Australia'],
        ['label' => 'Mexico City', 'detail' => 'Benito Juarez', 'country' => 'Mexico'],
        ['label' => 'Milan', 'detail' => 'Malpensa', 'country' => 'Italy'],
        ['label' => 'Mumbai', 'detail' => 'Chhatrapati Shivaji Maharaj International', 'country' => 'India'],
        ['label' => 'Nairobi', 'detail' => 'Jomo Kenyatta International', 'country' => 'Kenya'],
        ['label' => 'New York', 'detail' => 'JFK / LaGuardia / Newark', 'country' => 'U.S.A'],
        ['label' => 'Osaka', 'detail' => 'Kansai International', 'country' => 'Japan'],
        ['label' => 'Paris', 'detail' => 'Charles de Gaulle', 'country' => 'France'],
        ['label' => 'Perth', 'detail' => 'Perth Airport', 'country' => 'Australia'],
        ['label' => 'Prague', 'detail' => 'Vaclav Havel', 'country' => 'Czechia'],
        ['label' => 'Riyadh', 'detail' => 'King Khalid International', 'country' => 'Saudi Arabia'],
        ['label' => 'Rome', 'detail' => 'Fiumicino', 'country' => 'Italy'],
        ['label' => 'Seoul', 'detail' => 'Incheon International', 'country' => 'South Korea'],
        ['label' => 'Shanghai', 'detail' => 'Pudong International', 'country' => 'China'],
        ['label' => 'Singapore', 'detail' => 'Changi', 'country' => 'Singapore'],
        ['label' => 'Sydney', 'detail' => 'Kingsford Smith', 'country' => 'Australia'],
        ['label' => 'Tokyo', 'detail' => 'Haneda', 'country' => 'Japan'],
        ['label' => 'Toronto', 'detail' => 'Pearson International', 'country' => 'Canada'],
        ['label' => 'Vienna', 'detail' => 'Vienna International', 'country' => 'Austria'],
        ['label' => 'Wellington', 'detail' => 'Wellington Airport', 'country' => 'New Zealand'],
        ['label' => 'Zurich', 'detail' => 'Zurich Airport', 'country' => 'Switzerland'],
    ];
}

function trip_planner_preference_options(): array
{
    return [
        'time_preference' => [
            ['label' => '12AM - 8AM', 'icon' => 'fa-regular fa-clock'],
            ['label' => '8AM - 12PM', 'icon' => 'fa-regular fa-clock'],
            ['label' => '12PM - 8PM', 'icon' => 'fa-regular fa-clock'],
            ['label' => '8PM - 12AM', 'icon' => 'fa-regular fa-clock'],
        ],
        'class_preference' => [
            ['label' => 'Economy', 'icon' => 'fa-solid fa-plane'],
            ['label' => 'Premium Economy', 'icon' => 'fa-solid fa-plane'],
            ['label' => 'Business', 'icon' => 'fa-solid fa-plane'],
            ['label' => 'First', 'icon' => 'fa-solid fa-plane'],
        ],
        'seat_preference' => [
            ['label' => 'None', 'icon' => 'fa-solid fa-ban'],
            ['label' => 'Window', 'icon' => 'fa-solid fa-chair'],
            ['label' => 'Middle', 'icon' => 'fa-solid fa-chair'],
            ['label' => 'Aisle', 'icon' => 'fa-solid fa-chair'],
        ],
        'meal_preference' => [
            ['label' => 'None', 'icon' => 'fa-solid fa-utensils'],
            ['label' => 'Asian Vegetarian Meal', 'icon' => 'fa-solid fa-utensils'],
            ['label' => 'Vegetarian Meal (non-dairy)', 'icon' => 'fa-solid fa-utensils'],
            ['label' => 'Vegetarian Meal (lacto-ovo)', 'icon' => 'fa-solid fa-utensils'],
            ['label' => 'Vegetarian Jain Meal', 'icon' => 'fa-solid fa-utensils'],
            ['label' => 'Baby Meal', 'icon' => 'fa-solid fa-utensils'],
            ['label' => 'Child Meal', 'icon' => 'fa-solid fa-utensils'],
            ['label' => 'Diabetic Meal', 'icon' => 'fa-solid fa-utensils'],
            ['label' => 'Bland Meal', 'icon' => 'fa-solid fa-utensils'],
            ['label' => 'Fruit Platter', 'icon' => 'fa-solid fa-utensils'],
            ['label' => 'Gluten-free Meal', 'icon' => 'fa-solid fa-utensils'],
            ['label' => 'Hindu (Non-Vegetarian) Meal', 'icon' => 'fa-solid fa-utensils'],
            ['label' => 'Kosher Meal', 'icon' => 'fa-solid fa-utensils'],
            ['label' => 'Muslim Meal', 'icon' => 'fa-solid fa-utensils'],
            ['label' => 'Low cholesterol/ Low Fat Meal', 'icon' => 'fa-solid fa-utensils'],
            ['label' => 'Low Sodium Meal (no salt)', 'icon' => 'fa-solid fa-utensils'],
            ['label' => 'Non-Lactose Meal', 'icon' => 'fa-solid fa-utensils'],
            ['label' => 'Low Calories Meal', 'icon' => 'fa-solid fa-utensils'],
        ],
        'car_type' => [
            ['label' => 'Small', 'icon' => 'fa-solid fa-car-side'],
            ['label' => 'Medium', 'icon' => 'fa-solid fa-car-side'],
            ['label' => 'Large', 'icon' => 'fa-solid fa-car-side'],
            ['label' => 'Estate', 'icon' => 'fa-solid fa-car-side'],
            ['label' => 'Premium', 'icon' => 'fa-solid fa-car-side'],
            ['label' => 'People Carrier', 'icon' => 'fa-solid fa-van-shuttle'],
            ['label' => 'SUV', 'icon' => 'fa-solid fa-car-side'],
        ],
        'driver_required' => [
            ['label' => 'Yes', 'icon' => 'fa-solid fa-circle-check'],
            ['label' => 'No', 'icon' => 'fa-regular fa-circle'],
        ],
    ];
}

function trip_trip_number(int $tripId): string
{
    return 'TRIP-' . str_pad((string) max(0, $tripId), 5, '0', STR_PAD_LEFT);
}

function trip_normalize_bool($value): int
{
    $value = is_string($value) ? strtolower(trim($value)) : $value;

    return in_array($value, [1, true, '1', 'true', 'yes', 'on'], true) ? 1 : 0;
}

function trip_planner_normalize_date_value($value): string
{
    $value = trim((string) $value);
    if ($value === '') {
        return '';
    }

    $formats = ['Y-m-d', 'd M Y', 'd/m/Y', 'd-m-Y', 'd M, Y'];
    foreach ($formats as $format) {
        $date = DateTime::createFromFormat($format, $value);
        if ($date instanceof DateTime) {
            return $date->format('Y-m-d');
        }
    }

    $timestamp = strtotime($value);
    if ($timestamp !== false) {
      return date('Y-m-d', $timestamp);
    }

    return $value;
}

function trip_planner_normalize_itinerary_payload(array $item): array
{
    $mode = strtolower(trim((string) ($item['mode'] ?? 'flight')));
    $payload = [];
    $excluded = ['mode' => true, 'leg_type' => true, 'item_title' => true, 'sort_order' => true];

    foreach ($item as $key => $value) {
        if (isset($excluded[$key])) {
            continue;
        }

        if (is_array($value) || is_object($value)) {
            continue;
        }

        $cleanKey = trim((string) $key);
        $cleanValue = trim((string) $value);
        if ($cleanKey === '') {
            continue;
        }

        if (in_array($cleanKey, ['departure_date', 'return_date', 'check_in_date', 'check_out_date', 'pickup_date', 'dropoff_date'], true)) {
            $cleanValue = trip_planner_normalize_date_value($cleanValue);
        }

        if ($cleanKey === 'driver_required') {
            $cleanValue = ucfirst(strtolower($cleanValue));
        }

        if ($cleanValue === '') {
            continue;
        }

        $payload[$cleanKey] = $cleanValue;
    }

    if ($mode === 'car') {
        $payload['pickup_location'] = trim((string) ($payload['pickup_location'] ?? ''));
        $payload['dropoff_location'] = trim((string) ($payload['dropoff_location'] ?? ''));
    }

    return $payload;
}

function trip_cover_url(string $path): string
{
    $path = trim($path);
    if ($path === '') {
        return asset_url('images/img1.jpg');
    }

    if (str_starts_with($path, 'http://') || str_starts_with($path, 'https://') || str_starts_with($path, '../') || str_starts_with($path, '/')) {
        return $path;
    }

    return asset_url($path);
}

function trip_store_upload(array $file, string $prefix, int $userId): string
{
    if (($file['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_OK || empty($file['tmp_name'])) {
        return '';
    }

    $originalName = (string) ($file['name'] ?? '');
    $extension = strtolower(pathinfo($originalName, PATHINFO_EXTENSION));
    if ($extension === '') {
        return '';
    }

    $directory = __DIR__ . '/../images/trips';
    if (!is_dir($directory)) {
        mkdir($directory, 0777, true);
    }

    $fileName = $prefix . '_' . $userId . '_' . time() . '_' . bin2hex(random_bytes(3)) . '.' . $extension;
    $target = $directory . '/' . $fileName;

    if (!move_uploaded_file((string) $file['tmp_name'], $target)) {
        return '';
    }

    return 'images/trips/' . $fileName;
}

function trip_fetch_by_id(int $tripId, int $userId): ?array
{
    $row = fetch_one(
        'SELECT id, user_id, trip_name, travel_type, destination, business_purpose, is_visa_required,
                status, start_date, end_date, cover_image_path, created_at, updated_at
         FROM expense_trips
         WHERE id = ? AND user_id = ? AND is_deleted = 0 AND deleted_at IS NULL
         LIMIT 1',
        'ii',
        [$tripId, $userId]
    );

    return $row ?: null;
}

function trip_fetch_comments(int $tripId): array
{
    return fetch_all(
        'SELECT c.id, c.user_id, c.trip_id, c.comment_text, c.comment_type, c.parent_comment_id, c.created_at,
                COALESCE(u.business_name, u.email, "Unknown") AS author_name
         FROM expense_trip_comments c
         JOIN users u ON u.id = c.user_id
         WHERE c.trip_id = ? AND c.is_deleted = 0 AND c.deleted_at IS NULL
         ORDER BY c.created_at ASC, c.id ASC',
        'i',
        [$tripId]
    );
}

function trip_fetch_events(int $tripId): array
{
    return fetch_all(
        'SELECT e.id, e.user_id, e.trip_id, e.event_type, e.event_title, e.event_description, e.payload_json, e.created_at,
                COALESCE(u.business_name, u.email, "System") AS author_name
         FROM expense_trip_events e
         JOIN users u ON u.id = e.user_id
         WHERE e.trip_id = ? AND e.is_deleted = 0 AND e.deleted_at IS NULL
         ORDER BY e.created_at ASC, e.id ASC',
        'i',
        [$tripId]
    );
}

function trip_fetch_itineraries(int $tripId): array
{
    return fetch_all(
        'SELECT id, user_id, trip_id, mode, leg_type, item_title, sort_order, payload_json, status, created_at
         FROM expense_trip_itineraries
         WHERE trip_id = ? AND is_deleted = 0 AND deleted_at IS NULL
         ORDER BY sort_order ASC, id ASC',
        'i',
        [$tripId]
    );
}

function trip_fetch_documents(int $tripId): array
{
    return fetch_all(
        'SELECT id, user_id, trip_id, document_title, file_path, file_type, file_size, notes, created_at
         FROM expense_trip_documents
         WHERE trip_id = ? AND is_deleted = 0 AND deleted_at IS NULL
         ORDER BY created_at DESC, id DESC',
        'i',
        [$tripId]
    );
}

function trip_log_event(int $tripId, int $userId, string $type, string $title, string $description = '', array $payload = []): int
{
    return execute_query(
        'INSERT INTO expense_trip_events (user_id, trip_id, event_type, event_title, event_description, payload_json)
         VALUES (?, ?, ?, ?, ?, ?)',
        'iissss',
        [
            $userId,
            $tripId,
            $type,
            $title,
            $description,
            $payload !== [] ? json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) : null,
        ]
    );
}

function trip_store_comment(int $tripId, int $userId, string $commentText, string $commentType = 'comment'): int
{
    $commentText = trim($commentText);
    if ($commentText === '') {
        return -1;
    }

    $insertId = execute_query(
        'INSERT INTO expense_trip_comments (user_id, trip_id, comment_text, comment_type)
         VALUES (?, ?, ?, ?)',
        'iiss',
        [$userId, $tripId, $commentText, $commentType]
    );

    if ($insertId > 0) {
        trip_log_event($tripId, $userId, 'comment', 'Comment added', $commentText, ['comment_id' => $insertId]);
    }

    return $insertId;
}

function trip_store_document(int $tripId, int $userId, array $file, string $documentTitle = ''): int
{
    $path = trip_store_upload($file, 'trip_doc', $userId);
    if ($path === '') {
        return -1;
    }

    $title = trim($documentTitle);
    if ($title === '') {
        $title = pathinfo((string) ($file['name'] ?? ''), PATHINFO_FILENAME);
    }
    if ($title === '') {
        $title = 'Trip Document';
    }

    $insertId = execute_query(
        'INSERT INTO expense_trip_documents (user_id, trip_id, document_title, file_path, file_type, file_size)
         VALUES (?, ?, ?, ?, ?, ?)',
        'iisssi',
        [
            $userId,
            $tripId,
            $title,
            $path,
            (string) ($file['type'] ?? ''),
            (int) ($file['size'] ?? 0),
        ]
    );

    if ($insertId > 0) {
        trip_log_event($tripId, $userId, 'document', 'Document added', $title, ['document_id' => $insertId, 'file_path' => $path]);
    }

    return $insertId;
}

function trip_store_itineraries(int $tripId, int $userId, array $items): int
{
    if ($items === []) {
        return 0;
    }

    db()->begin_transaction();
    try {
        $modes = [];
        foreach ($items as $item) {
            $mode = strtolower(trim((string) ($item['mode'] ?? 'flight')));
            if (in_array($mode, ['flight', 'hotel', 'car', 'bus', 'train'], true)) {
                $modes[$mode] = true;
            }
        }

        $location = $_SERVER['HTTP_HOST'] ?? 'web';
        $reason = 'Replaced by itinerary save';
        $delete = db()->prepare('UPDATE expense_trip_itineraries SET is_deleted = 1, deleted_at = NOW(), deleted_by = ?, deleted_location = ?, deleted_reason = ? WHERE trip_id = ? AND mode = ? AND is_deleted = 0');
        if ($delete) {
            foreach (array_keys($modes) as $mode) {
                $delete->bind_param('issis', $userId, $location, $reason, $tripId, $mode);
                $delete->execute();
            }
            $delete->close();
        }

        $sortOrder = 0;
        foreach ($items as $item) {
            $mode = strtolower(trim((string) ($item['mode'] ?? 'flight')));
            $legType = strtolower(trim((string) ($item['leg_type'] ?? 'single')));
            $title = trim((string) ($item['item_title'] ?? ''));
            $payload = trip_planner_normalize_itinerary_payload($item);

            if (!in_array($mode, ['flight', 'hotel', 'car', 'bus', 'train'], true)) {
                $mode = 'flight';
            }

            if (!in_array($legType, ['one_way', 'round_trip', 'multi_city', 'single'], true)) {
                $legType = 'single';
            }

            if ($title === '') {
                $title = ucfirst($mode) . ' itinerary';
            }

            execute_query(
                'INSERT INTO expense_trip_itineraries (user_id, trip_id, mode, leg_type, item_title, sort_order, payload_json)
                 VALUES (?, ?, ?, ?, ?, ?, ?)',
                'iisssis',
                [
                    $userId,
                    $tripId,
                    $mode,
                    $legType,
                    $title,
                    ++$sortOrder,
                    json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
                ]
            );
        }

        trip_log_event($tripId, $userId, 'itinerary', 'Itinerary updated', 'Travel itinerary entries were updated.', ['rows' => count($items)]);
        db()->commit();

        return count($items);
    } catch (Throwable $e) {
        db()->rollback();
        return -1;
    }
}

function trip_update_status(int $tripId, int $userId, string $status): bool
{
    if (!in_array($status, ['planned', 'active', 'completed', 'archived', 'cancelled'], true)) {
        return false;
    }

    $updated = execute_query(
        'UPDATE expense_trips
         SET status = ?
         WHERE id = ? AND user_id = ?',
        'sii',
        [$status, $tripId, $userId]
    );

    if ($updated > 0) {
        trip_log_event($tripId, $userId, 'status', 'Trip status changed', 'Status changed to ' . $status, ['status' => $status]);
        return true;
    }

    return false;
}

function trip_soft_delete(int $tripId, int $userId, string $reason = 'Deleted from trip page'): bool
{
    $trip = trip_fetch_by_id($tripId, $userId);
    if (!$trip) {
        return false;
    }

    $deletedLocation = $_SERVER['HTTP_HOST'] ?? 'web';
    $deletedAt = date('Y-m-d H:i:s');
    $updated = execute_query(
        'UPDATE expense_trips
         SET is_deleted = 1, deleted_at = NOW(), deleted_by = ?, deleted_location = ?, deleted_reason = ?
         WHERE id = ? AND user_id = ?',
        'issii',
        [$userId, $deletedLocation, $reason, $tripId, $userId]
    );

    if ($updated > 0) {
        $trip['is_deleted'] = 1;
        $trip['deleted_at'] = $deletedAt;
        $trip['deleted_by'] = $userId;
        $trip['deleted_location'] = $deletedLocation;
        $trip['deleted_reason'] = $reason;

        record_deletion_log(
            'expense_trips',
            $tripId,
            0,
            (int) ($trip['user_id'] ?? $userId),
            $userId,
            $deletedLocation,
            $reason,
            $trip
        );

        trip_log_event($tripId, $userId, 'deleted', 'Trip deleted', $reason);
        return true;
    }

    return false;
}

function trip_archive(int $tripId, int $userId): bool
{
    return trip_update_status($tripId, $userId, 'archived');
}

function trip_status_label(string $status): string
{
    $status = strtolower(trim($status));

    return match ($status) {
        'planned' => 'Draft',
        'active' => 'Approved',
        'completed' => 'Completed',
        'archived' => 'Archived',
        'cancelled' => 'Cancelled',
        default => $status !== '' ? ucfirst($status) : '-',
    };
}

function trip_trip_rows_to_payload(array $rows): array
{
    $normalized = [];
    foreach ($rows as $row) {
        if (!is_array($row)) {
            continue;
        }
        $normalized[] = $row;
    }

    return $normalized;
}
