<?php

require_once __DIR__ . '/helpers.php';

function expense_flow_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 expense_flow_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 expense_flow_add_column_if_missing(string $table, string $column, string $definition): void
{
    if (!expense_flow_column_exists($table, $column)) {
        db()->query('ALTER TABLE ' . $table . ' ADD COLUMN ' . $column . ' ' . $definition);
    }
}

function expense_flow_ensure_schema(): void
{
    if (!expense_flow_table_exists('expense_report_events')) {
        db()->query(
            'CREATE TABLE expense_report_events (
                id INT AUTO_INCREMENT PRIMARY KEY,
                report_id INT NOT NULL,
                user_id INT NOT NULL,
                actor_name VARCHAR(150) NOT NULL DEFAULT "",
                actor_avatar_path VARCHAR(255) NOT NULL DEFAULT "",
                event_type VARCHAR(80) NOT NULL DEFAULT "updated",
                event_title VARCHAR(180) NOT NULL DEFAULT "",
                event_description VARCHAR(500) NOT NULL DEFAULT "",
                payload_json LONGTEXT NULL DEFAULT NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_expense_report_events_report_time (report_id, created_at),
                INDEX idx_expense_report_events_user_time (user_id, created_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
        );
    }

    expense_flow_add_column_if_missing('expense_reports', 'trip_id', 'INT NULL DEFAULT NULL AFTER user_id');
    expense_flow_add_column_if_missing('expense_reports', 'business_purpose', 'VARCHAR(500) NOT NULL DEFAULT "" AFTER report_title');
    expense_flow_add_column_if_missing('expense_reports', 'start_date', 'DATE NULL DEFAULT NULL AFTER business_purpose');
    expense_flow_add_column_if_missing('expense_reports', 'end_date', 'DATE NULL DEFAULT NULL AFTER start_date');
    expense_flow_add_column_if_missing('expense_reports', 'submitted_at', 'DATETIME NULL DEFAULT NULL AFTER currency_code');
    expense_flow_add_column_if_missing('expense_reports', 'approved_at', 'DATETIME NULL DEFAULT NULL AFTER submitted_at');
    expense_flow_add_column_if_missing('expense_reports', 'approver_user_id', 'INT NULL DEFAULT NULL AFTER approved_at');
    expense_flow_add_column_if_missing('expense_reports', 'is_deleted', 'TINYINT(1) NOT NULL DEFAULT 0 AFTER approved_at');
    expense_flow_add_column_if_missing('expense_reports', 'deleted_at', 'DATETIME NULL DEFAULT NULL AFTER is_deleted');
    expense_flow_add_column_if_missing('expense_reports', 'deleted_by', 'INT NULL DEFAULT NULL AFTER deleted_at');
    expense_flow_add_column_if_missing('expense_reports', 'deleted_location', 'VARCHAR(190) NOT NULL DEFAULT "" AFTER deleted_by');
    expense_flow_add_column_if_missing('expense_reports', 'deleted_reason', 'VARCHAR(255) NOT NULL DEFAULT "" AFTER deleted_location');

    expense_flow_add_column_if_missing('expense_report_items', 'trip_id', 'INT NULL DEFAULT NULL AFTER report_id');
    expense_flow_add_column_if_missing('expense_report_items', 'category_name', 'VARCHAR(120) NOT NULL DEFAULT "" AFTER merchant_name');
    expense_flow_add_column_if_missing('expense_report_items', 'currency_code', 'VARCHAR(12) NOT NULL DEFAULT "INR" AFTER amount');
    expense_flow_add_column_if_missing('expense_report_items', 'expense_date', 'DATE NULL DEFAULT NULL AFTER currency_code');
    expense_flow_add_column_if_missing('expense_report_items', 'claim_reimbursement', 'TINYINT(1) NOT NULL DEFAULT 1 AFTER expense_date');
    expense_flow_add_column_if_missing('expense_report_items', 'description', 'VARCHAR(500) NOT NULL DEFAULT "" AFTER claim_reimbursement');
    expense_flow_add_column_if_missing('expense_report_items', 'reference_no', 'VARCHAR(100) NOT NULL DEFAULT "" AFTER description');
    expense_flow_add_column_if_missing('expense_report_items', 'is_deleted', 'TINYINT(1) NOT NULL DEFAULT 0 AFTER receipt_path');
    expense_flow_add_column_if_missing('expense_report_items', 'deleted_at', 'DATETIME NULL DEFAULT NULL AFTER is_deleted');
    expense_flow_add_column_if_missing('expense_report_items', 'deleted_by', 'INT NULL DEFAULT NULL AFTER deleted_at');
    expense_flow_add_column_if_missing('expense_report_items', 'deleted_location', 'VARCHAR(190) NOT NULL DEFAULT "" AFTER deleted_by');
    expense_flow_add_column_if_missing('expense_report_items', 'deleted_reason', 'VARCHAR(255) NOT NULL DEFAULT "" AFTER deleted_location');

    expense_flow_add_column_if_missing('expense_advances', 'trip_id', 'INT NULL DEFAULT NULL AFTER user_id');
    expense_flow_add_column_if_missing('expense_advances', 'advance_date', 'DATE NULL DEFAULT NULL AFTER amount');
    expense_flow_add_column_if_missing('expense_advances', 'paid_through', 'VARCHAR(100) NOT NULL DEFAULT "Petty Cash" AFTER advance_date');
    expense_flow_add_column_if_missing('expense_advances', 'reference_no', 'VARCHAR(100) NOT NULL DEFAULT "" AFTER paid_through');
    expense_flow_add_column_if_missing('expense_advances', 'is_deleted', 'TINYINT(1) NOT NULL DEFAULT 0 AFTER advance_status');
    expense_flow_add_column_if_missing('expense_advances', 'deleted_at', 'DATETIME NULL DEFAULT NULL AFTER is_deleted');
    expense_flow_add_column_if_missing('expense_advances', 'deleted_by', 'INT NULL DEFAULT NULL AFTER deleted_at');
    expense_flow_add_column_if_missing('expense_advances', 'deleted_location', 'VARCHAR(190) NOT NULL DEFAULT "" AFTER deleted_by');
    expense_flow_add_column_if_missing('expense_advances', 'deleted_reason', 'VARCHAR(255) NOT NULL DEFAULT "" AFTER deleted_location');

    expense_flow_add_column_if_missing('card_transactions', 'currency_code', 'VARCHAR(12) NOT NULL DEFAULT "INR" AFTER amount');
    expense_flow_add_column_if_missing('card_transactions', 'reference_no', 'VARCHAR(100) NOT NULL DEFAULT "" AFTER transaction_status');
    expense_flow_add_column_if_missing('card_transactions', 'is_deleted', 'TINYINT(1) NOT NULL DEFAULT 0 AFTER reference_no');
    expense_flow_add_column_if_missing('card_transactions', 'deleted_at', 'DATETIME NULL DEFAULT NULL AFTER is_deleted');
}

function expense_flow_report_code(int $reportId): string
{
    return 'ER-' . str_pad((string) $reportId, 5, '0', STR_PAD_LEFT);
}

function expense_flow_actor_snapshot(int $userId): array
{
    $context = current_user_context();
    $name = trim((string) ($context['display_name'] ?? ''));
    if ($name === '') {
        $name = trim((string) ($context['email'] ?? ''));
    }
    if ($name === '') {
        $name = 'Expense user';
    }

    return [
        'name' => $name,
        'avatar_path' => trim((string) ($context['avatar_url'] ?? '')),
    ];
}

function expense_flow_fetch_report_summary(int $reportId, int $userId): ?array
{
    if ($reportId <= 0 || $userId <= 0) {
        return null;
    }

    return fetch_one(
        'SELECT id, report_title, total_amount, currency_code, report_status
         FROM expense_reports
         WHERE id = ? AND user_id = ? AND is_deleted = 0 AND deleted_at IS NULL
         LIMIT 1',
        'ii',
        [$reportId, $userId]
    );
}

function expense_flow_log_report_event(
    int $reportId,
    int $userId,
    string $eventType,
    string $title,
    string $description = '',
    array $payload = []
): int {
    if ($reportId <= 0 || $userId <= 0) {
        return 0;
    }

    expense_flow_ensure_schema();
    $actor = expense_flow_actor_snapshot($userId);
    $payloadJson = json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    if ($payloadJson === false) {
        $payloadJson = null;
    }

    return execute_query(
        'INSERT INTO expense_report_events
            (report_id, user_id, actor_name, actor_avatar_path, event_type, event_title, event_description, payload_json)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
        'iissssss',
        [
            $reportId,
            $userId,
            (string) $actor['name'],
            (string) $actor['avatar_path'],
            trim($eventType),
            trim($title),
            trim($description),
            $payloadJson,
        ]
    );
}

function expense_flow_log_report_total_change(int $reportId, int $userId, float $previousTotal, float $newTotal, string $currency = 'INR'): void
{
    if ($reportId <= 0 || $userId <= 0) {
        return;
    }

    if (round($previousTotal, 2) === round($newTotal, 2)) {
        return;
    }

    expense_flow_log_report_event(
        $reportId,
        $userId,
        'total_changed',
        'Report updated',
        'Total changed to ' . expense_flow_format_money($newTotal, $currency),
        [
            'previous_total' => round($previousTotal, 2),
            'new_total' => round($newTotal, 2),
            'currency_code' => strtoupper(trim($currency)),
        ]
    );
}

function expense_flow_fetch_report_history(int $reportId, int $userId): array
{
    if ($reportId <= 0 || $userId <= 0) {
        return [];
    }

    expense_flow_ensure_schema();

    $events = fetch_all(
        'SELECT id, actor_name, actor_avatar_path, event_type, event_title, event_description, payload_json, created_at
         FROM expense_report_events
         WHERE report_id = ? AND user_id = ?
         ORDER BY created_at DESC, id DESC',
        'ii',
        [$reportId, $userId]
    );

    if ($events !== []) {
        return $events;
    }

    $report = fetch_one(
        'SELECT id, report_title, total_amount, currency_code, report_status, created_at, updated_at, submitted_at, approved_at
         FROM expense_reports
         WHERE id = ? AND user_id = ? AND is_deleted = 0 AND deleted_at IS NULL
         LIMIT 1',
        'ii',
        [$reportId, $userId]
    );

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

    $actor = expense_flow_actor_snapshot($userId);
    $fallback = [[
        'id' => 0,
        'actor_name' => (string) $actor['name'],
        'actor_avatar_path' => (string) $actor['avatar_path'],
        'event_type' => 'created',
        'event_title' => 'Report created',
        'event_description' => '',
        'payload_json' => null,
        'created_at' => (string) ($report['created_at'] ?? ''),
    ]];

    $submittedAt = trim((string) ($report['submitted_at'] ?? ''));
    if ($submittedAt !== '') {
        $fallback[] = [
            'id' => 0,
            'actor_name' => (string) $actor['name'],
            'actor_avatar_path' => (string) $actor['avatar_path'],
            'event_type' => 'submitted',
            'event_title' => 'Report submitted',
            'event_description' => '',
            'payload_json' => null,
            'created_at' => $submittedAt,
        ];
    }

    $approvedAt = trim((string) ($report['approved_at'] ?? ''));
    if ($approvedAt !== '') {
        $fallback[] = [
            'id' => 0,
            'actor_name' => (string) $actor['name'],
            'actor_avatar_path' => (string) $actor['avatar_path'],
            'event_type' => 'approved',
            'event_title' => 'Report approved',
            'event_description' => 'Total changed to ' . expense_flow_format_money((float) ($report['total_amount'] ?? 0), (string) ($report['currency_code'] ?? 'INR')),
            'payload_json' => null,
            'created_at' => $approvedAt,
        ];
    }

    usort($fallback, static function (array $left, array $right): int {
        return strcmp((string) ($right['created_at'] ?? ''), (string) ($left['created_at'] ?? ''));
    });

    return $fallback;
}

function expense_flow_current_organization_id(int $userId): int
{
    if ($userId <= 0) {
        return 0;
    }

    $row = fetch_one(
        'SELECT id
         FROM organizations
         WHERE user_id = ?
         ORDER BY id DESC
         LIMIT 1',
        'i',
        [$userId]
    );

    return (int) ($row['id'] ?? 0);
}

function expense_flow_report_options(int $userId): array
{
    if ($userId <= 0) {
        return [];
    }

    return fetch_all(
        'SELECT
            r.id,
            r.report_title,
            r.report_status,
            r.total_amount,
            r.currency_code,
            r.created_at,
            t.trip_name,
            t.destination
         FROM expense_reports r
         LEFT JOIN expense_trips t ON t.id = r.trip_id
         WHERE r.user_id = ? AND r.is_deleted = 0 AND r.deleted_at IS NULL
         ORDER BY COALESCE(r.submitted_at, r.created_at) DESC, r.id DESC
         LIMIT 20',
        'i',
        [$userId]
    );
}

function expense_flow_recent_items(int $userId, int $limit = 8): array
{
    if ($userId <= 0) {
        return [];
    }

    return fetch_all(
        'SELECT
            i.id,
            i.report_id,
            COALESCE(NULLIF(TRIM(i.expense_title), ""), "Expense") AS expense_title,
            COALESCE(NULLIF(TRIM(i.merchant_name), ""), "") AS merchant_name,
            COALESCE(NULLIF(TRIM(i.category_name), ""), "") AS category_name,
            COALESCE(i.amount, 0.00) AS amount,
            COALESCE(i.currency_code, "INR") AS currency_code,
            i.expense_date,
            i.claim_reimbursement,
            COALESCE(NULLIF(TRIM(i.description), ""), "") AS description,
            COALESCE(NULLIF(TRIM(i.reference_no), ""), "") AS reference_no,
            i.created_at,
            r.report_title
         FROM expense_report_items i
         LEFT JOIN expense_reports r ON r.id = i.report_id
         WHERE i.user_id = ? AND i.is_deleted = 0 AND i.deleted_at IS NULL
         ORDER BY COALESCE(i.expense_date, i.created_at) DESC, i.id DESC
         LIMIT ' . (int) max(1, $limit),
        'i',
        [$userId]
    );
}

function expense_flow_fetch_item(int $itemId, int $userId): ?array
{
    if ($itemId <= 0 || $userId <= 0) {
        return null;
    }

    return fetch_one(
        'SELECT
            i.*,
            r.report_title,
            r.report_status,
            r.currency_code AS report_currency,
            r.trip_id AS report_trip_id
         FROM expense_report_items i
         LEFT JOIN expense_reports r ON r.id = i.report_id
         WHERE i.id = ? AND i.user_id = ? AND i.is_deleted = 0 AND i.deleted_at IS NULL
         LIMIT 1',
        'ii',
        [$itemId, $userId]
    );
}

function expense_flow_fetch_transaction(int $transactionId, int $userId): ?array
{
    if ($transactionId <= 0 || $userId <= 0) {
        return null;
    }

    return fetch_one(
        'SELECT
            t.*,
            a.account_name,
            a.card_network,
            a.bank_name
         FROM card_transactions t
         LEFT JOIN card_accounts a ON a.id = t.card_account_id
         WHERE t.id = ? AND t.user_id = ? AND t.is_deleted = 0 AND t.deleted_at IS NULL
         LIMIT 1',
        'ii',
        [$transactionId, $userId]
    );
}

function expense_flow_format_money(float $amount, string $currency = 'INR'): string
{
    $currency = strtoupper(trim($currency));
    $prefix = $currency === 'INR' ? html_entity_decode('&#8377;') : $currency . ' ';
    return $prefix . number_format($amount, 2);
}

function expense_flow_delete_location(string $fallback = 'expense workspace'): string
{
    $referrer = trim((string) ($_SERVER['HTTP_REFERER'] ?? ''));
    if ($referrer !== '') {
        return $referrer;
    }

    $requestUri = trim((string) ($_SERVER['REQUEST_URI'] ?? ''));
    if ($requestUri !== '') {
        return $requestUri;
    }

    return $fallback;
}

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

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

    $parts = preg_split('/[\/.-]/', $value);
    if (is_array($parts) && count($parts) === 3) {
        [$a, $b, $c] = array_map('trim', $parts);
        if (strlen($c) === 4 && strlen($a) <= 2 && strlen($b) <= 2) {
            $day = str_pad($a, 2, '0', STR_PAD_LEFT);
            $month = str_pad($b, 2, '0', STR_PAD_LEFT);
            return $c . '-' . $month . '-' . $day;
        }
    }

    return '';
}

function expense_flow_sync_report_total(int $reportId, int $userId): void
{
    if ($reportId <= 0 || $userId <= 0) {
        return;
    }

    $existing = expense_flow_fetch_report_summary($reportId, $userId);
    $previousTotal = (float) ($existing['total_amount'] ?? 0.00);
    $existingCurrency = (string) ($existing['currency_code'] ?? 'INR');

    $totals = fetch_one(
        'SELECT COALESCE(SUM(amount), 0.00) AS total_amount, COALESCE(MAX(currency_code), "INR") AS currency_code
         FROM expense_report_items
         WHERE report_id = ? AND user_id = ? AND is_deleted = 0 AND deleted_at IS NULL',
        'ii',
        [$reportId, $userId]
    );

    execute_query(
        'UPDATE expense_reports
         SET total_amount = ?, currency_code = COALESCE(NULLIF(?, ""), currency_code), updated_at = NOW()
         WHERE id = ? AND user_id = ?',
        'dsii',
        [
            (float) ($totals['total_amount'] ?? 0.00),
            (string) ($totals['currency_code'] ?? 'INR'),
            $reportId,
            $userId,
        ]
    );

    expense_flow_log_report_total_change(
        $reportId,
        $userId,
        $previousTotal,
        (float) ($totals['total_amount'] ?? 0.00),
        (string) ($totals['currency_code'] ?? $existingCurrency ?: 'INR')
    );
}

function expense_flow_save_item(int $userId, array $data): int
{
    if ($userId <= 0) {
        return 0;
    }

    expense_flow_ensure_schema();

    $reportId = (int) ($data['report_id'] ?? 0);
    $tripId = (int) ($data['trip_id'] ?? 0);
    $title = trim((string) ($data['expense_title'] ?? 'Expense'));
    $merchant = trim((string) ($data['merchant_name'] ?? ''));
    $category = trim((string) ($data['category_name'] ?? ''));
    $amount = (float) ($data['amount'] ?? 0);
    $currency = strtoupper(trim((string) ($data['currency_code'] ?? 'INR')));
    $expenseDate = expense_flow_normalize_date((string) ($data['expense_date'] ?? ''));
    $claimReimbursement = !empty($data['claim_reimbursement']) ? 1 : 0;
    $description = trim((string) ($data['description'] ?? ''));
    $referenceNo = trim((string) ($data['reference_no'] ?? ''));
    $receiptPath = trim((string) ($data['receipt_path'] ?? ''));

    $itemId = execute_query(
        'INSERT INTO expense_report_items
            (user_id, report_id, trip_id, expense_title, merchant_name, category_name, amount, currency_code, expense_date, claim_reimbursement, description, reference_no, receipt_path)
         VALUES (?, NULLIF(?, 0), NULLIF(?, 0), ?, ?, ?, ?, ?, NULLIF(?, ""), ?, ?, ?, ?)',
        'iiisssdssisss',
        [
            $userId,
            $reportId,
            $tripId,
            $title,
            $merchant,
            $category,
            $amount,
            $currency,
            $expenseDate,
            $claimReimbursement,
            $description,
            $referenceNo,
            $receiptPath,
        ]
    );

    if ($itemId > 0 && $reportId > 0) {
        expense_flow_sync_report_total($reportId, $userId);
        expense_flow_log_report_event(
            $reportId,
            $userId,
            'expense_added',
            'Expense added',
            trim($title !== '' ? $title : $category) !== '' ? 'Added ' . trim($title !== '' ? $title : $category) . ' to the report.' : 'A new expense was added to the report.',
            [
                'expense_item_id' => $itemId,
                'amount' => round($amount, 2),
                'currency_code' => $currency,
                'category_name' => $category,
                'merchant_name' => $merchant,
            ]
        );
    }

    return $itemId > 0 ? $itemId : 0;
}

function expense_flow_update_item(int $itemId, int $userId, array $data): int
{
    if ($itemId <= 0 || $userId <= 0) {
        return 0;
    }

    $existing = expense_flow_fetch_item($itemId, $userId);
    if (!$existing) {
        return 0;
    }

    expense_flow_ensure_schema();

    $reportId = (int) ($data['report_id'] ?? 0);
    $tripId = (int) ($data['trip_id'] ?? 0);
    $title = trim((string) ($data['expense_title'] ?? 'Expense'));
    $merchant = trim((string) ($data['merchant_name'] ?? ''));
    $category = trim((string) ($data['category_name'] ?? ''));
    $amount = (float) ($data['amount'] ?? 0);
    $currency = strtoupper(trim((string) ($data['currency_code'] ?? 'INR')));
    $expenseDate = expense_flow_normalize_date((string) ($data['expense_date'] ?? ''));
    $claimReimbursement = !empty($data['claim_reimbursement']) ? 1 : 0;
    $description = trim((string) ($data['description'] ?? ''));
    $referenceNo = trim((string) ($data['reference_no'] ?? ''));
    $receiptPath = trim((string) ($data['receipt_path'] ?? ($existing['receipt_path'] ?? '')));

    $oldReportId = (int) ($existing['report_id'] ?? 0);
    $updated = execute_query(
        'UPDATE expense_report_items
         SET report_id = NULLIF(?, 0),
             trip_id = NULLIF(?, 0),
             expense_title = ?,
             merchant_name = ?,
             category_name = ?,
             amount = ?,
             currency_code = ?,
             expense_date = NULLIF(?, ""),
             claim_reimbursement = ?,
             description = ?,
             reference_no = ?,
             receipt_path = ?,
             updated_at = NOW()
         WHERE id = ? AND user_id = ?',
        'iisssdssisssii',
        [
            $reportId,
            $tripId,
            $title,
            $merchant,
            $category,
            $amount,
            $currency,
            $expenseDate,
            $claimReimbursement,
            $description,
            $referenceNo,
            $receiptPath,
            $itemId,
            $userId,
        ]
    );

    if ($updated > 0) {
        if ($oldReportId > 0) {
            expense_flow_sync_report_total($oldReportId, $userId);
            if ($oldReportId !== $reportId) {
                expense_flow_log_report_event(
                    $oldReportId,
                    $userId,
                    'expense_removed',
                    'Expense removed',
                    'An expense was moved out of this report.',
                    [
                        'expense_item_id' => $itemId,
                        'amount' => round($amount, 2),
                        'currency_code' => $currency,
                    ]
                );
            }
        }
        if ($reportId > 0) {
            expense_flow_sync_report_total($reportId, $userId);
            expense_flow_log_report_event(
                $reportId,
                $userId,
                $oldReportId > 0 && $oldReportId !== $reportId ? 'expense_added' : 'expense_updated',
                $oldReportId > 0 && $oldReportId !== $reportId ? 'Expense added' : 'Expense updated',
                $oldReportId > 0 && $oldReportId !== $reportId
                    ? 'An expense was moved into this report.'
                    : 'An expense in this report was updated.',
                [
                    'expense_item_id' => $itemId,
                    'amount' => round($amount, 2),
                    'currency_code' => $currency,
                    'category_name' => $category,
                    'merchant_name' => $merchant,
                ]
            );
        }
    }

    return $updated > 0 ? $itemId : 0;
}

function expense_flow_soft_delete_item(int $itemId, int $userId): bool
{
    $item = expense_flow_fetch_item($itemId, $userId);
    if (!$item) {
        return false;
    }

    $reportId = (int) ($item['report_id'] ?? 0);
    $updated = execute_query(
        'UPDATE expense_report_items
         SET is_deleted = 1, deleted_at = NOW(), deleted_by = ?, deleted_location = ?, deleted_reason = ?
         WHERE id = ? AND user_id = ?',
        'issii',
        [
            $userId,
            expense_flow_delete_location('expense page'),
            'Deleted from expense workspace',
            $itemId,
            $userId,
        ]
    );

    if ($updated > 0 && $reportId > 0) {
        expense_flow_sync_report_total($reportId, $userId);
        expense_flow_log_report_event(
            $reportId,
            $userId,
            'expense_deleted',
            'Expense removed',
            'An expense was deleted from this report.',
            [
                'expense_item_id' => $itemId,
                'amount' => round((float) ($item['amount'] ?? 0), 2),
                'currency_code' => (string) ($item['currency_code'] ?? 'INR'),
            ]
        );
    }

    return $updated > 0;
}

function expense_flow_soft_delete_advance(int $advanceId, int $userId): bool
{
    if ($advanceId <= 0 || $userId <= 0) {
        return false;
    }

    expense_flow_ensure_schema();

    $updated = execute_query(
        'UPDATE expense_advances
         SET is_deleted = 1,
             deleted_at = NOW(),
             deleted_by = ?,
             deleted_location = ?,
             deleted_reason = ?,
             updated_at = NOW()
         WHERE id = ? AND user_id = ? AND is_deleted = 0 AND deleted_at IS NULL',
        'issii',
        [
            $userId,
            expense_flow_delete_location('advance board'),
            'Deleted from advances UI',
            $advanceId,
            $userId,
        ]
    );

    return $updated > 0;
}

function expense_flow_clone_item(int $itemId, int $userId): int
{
    $item = expense_flow_fetch_item($itemId, $userId);
    if (!$item) {
        return 0;
    }

    return expense_flow_save_item($userId, [
        'report_id' => (int) ($item['report_id'] ?? 0),
        'trip_id' => (int) ($item['trip_id'] ?? 0),
        'expense_title' => (string) ($item['expense_title'] ?? 'Expense'),
        'merchant_name' => (string) ($item['merchant_name'] ?? ''),
        'category_name' => (string) ($item['category_name'] ?? ''),
        'amount' => (float) ($item['amount'] ?? 0),
        'currency_code' => (string) ($item['currency_code'] ?? 'INR'),
        'expense_date' => (string) ($item['expense_date'] ?? ''),
        'claim_reimbursement' => (int) ($item['claim_reimbursement'] ?? 1),
        'description' => (string) ($item['description'] ?? ''),
        'reference_no' => (string) ($item['reference_no'] ?? ''),
        'receipt_path' => (string) ($item['receipt_path'] ?? ''),
    ]);
}

function expense_flow_match_transaction(int $transactionId, int $userId): int
{
    $transaction = expense_flow_fetch_transaction($transactionId, $userId);
    if (!$transaction) {
        return 0;
    }

    $itemId = expense_flow_save_item($userId, [
        'report_id' => (int) ($transaction['report_id'] ?? 0),
        'expense_title' => 'Card Transaction',
        'merchant_name' => (string) ($transaction['payee_name'] ?? ''),
        'category_name' => 'Card Transaction',
        'amount' => (float) ($transaction['amount'] ?? 0),
        'currency_code' => (string) ($transaction['currency_code'] ?? 'INR'),
        'expense_date' => (string) ($transaction['transaction_date'] ?? ''),
        'claim_reimbursement' => 1,
        'description' => (string) ($transaction['details'] ?? ''),
        'reference_no' => (string) ($transaction['reference_no'] ?? ''),
    ]);

    if ($itemId > 0) {
        execute_query(
            'UPDATE card_transactions
             SET transaction_status = "matched", expense_item_id = ?, updated_at = NOW()
             WHERE id = ? AND user_id = ?',
            'iii',
            [$itemId, $transactionId, $userId]
        );
    }

    return $itemId;
}
