<?php
// csv_module_common.php — helper functions for Etsy Order Items CSV parser.

function csv_mod_normalize_header(string $h): string
{
    $h = preg_replace('/^\xEF\xBB\xBF/', '', $h);
    $h = trim($h);
    $h = strtolower($h);
    $h = preg_replace('/[^a-z0-9]+/i', '_', $h);
    return trim($h, '_');
}

function csv_mod_clean_text(?string $value): string
{
    $value = (string)$value;
    $value = html_entity_decode($value, ENT_QUOTES | ENT_HTML5, 'UTF-8');
    $value = str_replace(["\xC2\xA0", "\r\n", "\r"], [' ', "\n", "\n"], $value);
    return trim($value);
}

function csv_mod_get(array $row, array $idx, string $field, string $default = ''): string
{
    if (!isset($idx[$field])) {
        return $default;
    }
    $pos = $idx[$field];
    return isset($row[$pos]) ? csv_mod_clean_text((string)$row[$pos]) : $default;
}

function csv_mod_parse_money(?string $value): ?float
{
    $value = csv_mod_clean_text($value);
    if ($value === '') return null;
    $value = str_replace([',', '$'], ['', ''], $value);
    return is_numeric($value) ? (float)$value : null;
}

function csv_mod_parse_int(?string $value, int $default = 1): int
{
    $value = csv_mod_clean_text($value);
    if ($value === '') return $default;
    return max(0, (int)$value);
}

function csv_mod_parse_date(?string $value, bool $dateOnly = false): ?string
{
    $value = csv_mod_clean_text($value);
    if ($value === '') return null;

    $formats = ['m/d/y', 'm/d/Y', 'Y-m-d', 'm/d/y H:i', 'm/d/Y H:i', 'Y-m-d H:i:s'];
    foreach ($formats as $format) {
        $dt = DateTime::createFromFormat($format, $value);
        if ($dt instanceof DateTime) {
            return $dateOnly ? $dt->format('Y-m-d') : $dt->format('Y-m-d H:i:s');
        }
    }

    $ts = strtotime($value);
    return $ts !== false ? ($dateOnly ? date('Y-m-d', $ts) : date('Y-m-d H:i:s', $ts)) : null;
}

function csv_mod_parse_buyer(string $buyer): array
{
    $buyer = csv_mod_clean_text($buyer);
    $buyerName = $buyer;
    $buyerUsername = '';

    if (preg_match('/^(.*?)\s*\(([^)]+)\)\s*$/u', $buyer, $m)) {
        $buyerName = trim($m[1]);
        $buyerUsername = trim($m[2]);
    }

    return [$buyerName, $buyerUsername];
}

function csv_mod_norm_pair_key(string $key): string
{
    $key = csv_mod_clean_text($key);
    $key = str_replace('&', ' and ', $key);
    $key = strtolower($key);
    $key = preg_replace('/[^a-z0-9]+/i', '_', $key);
    return trim($key, '_');
}

function csv_mod_extract_variation_pairs(string $text): array
{
    $text = csv_mod_clean_text($text);
    if ($text === '') return [];

    $keys = [
        'Material', 'Metal', 'Primary color', 'Color', 'Material & Color', 'Birthstone & Material',
        'Ring size', 'Ring Size', 'Size', 'Width of the Band - Karat - Color',
        'Necklace Length', 'Chain Length', 'Chain Lenght', 'Chain Size', 'Length',
        'Number of the Disc- Size (Diameter)', 'Single / Full Pair',
        'Personalization'
    ];

    $pattern = '/(?:^|,|\n)\s*(' . implode('|', array_map(fn($k) => preg_quote($k, '/'), $keys)) . ')\s*:/iu';

    if (!preg_match_all($pattern, $text, $matches, PREG_OFFSET_CAPTURE)) {
        return [];
    }

    $pairs = [];
    $count = count($matches[0]);

    for ($i = 0; $i < $count; $i++) {
        $fullMatch = $matches[0][$i][0];
        $matchPos = $matches[0][$i][1];
        $key = trim($matches[1][$i][0]);
        $valueStart = $matchPos + strlen($fullMatch);
        $valueEnd = ($i + 1 < $count) ? $matches[0][$i + 1][1] : strlen($text);
        $value = substr($text, $valueStart, $valueEnd - $valueStart);
        $value = preg_replace('/^\s*,\s*/', '', $value);
        $value = csv_mod_clean_text($value);

        $normKey = csv_mod_norm_pair_key($key);
        $pairs[$normKey] = $value;
    }

    return $pairs;
}

function csv_mod_parse_material_text(string $material): array
{
    $material = csv_mod_clean_text($material);
    $parsedKarat = '';
    $parsedColor = '';

    if ($material !== '') {
        if (preg_match('/(8|9|10|14|18)\s*k/i', $material, $m)) {
            $parsedKarat = strtoupper($m[1] . 'K');
        }

        $flat = strtolower(preg_replace('/\s+/', '', $material));
        if (str_contains($flat, 'yellowgold') || preg_match('/yellow/i', $material)) {
            $parsedColor = 'Yellow Gold';
        } elseif (str_contains($flat, 'whitegold') || preg_match('/white/i', $material)) {
            $parsedColor = 'White Gold';
        } elseif (str_contains($flat, 'rosegold') || preg_match('/rose/i', $material)) {
            $parsedColor = 'Rose Gold';
        }
    }

    return [$parsedKarat, $parsedColor];
}

function csv_mod_parse_variations(string $text): array
{
    $cleanText = csv_mod_clean_text($text);
    $pairs = csv_mod_extract_variation_pairs($cleanText);

    $materialKeys = [
        'material', 'metal', 'primary_color', 'color', 'material_and_color',
        'birthstone_and_material', 'width_of_the_band_karat_color'
    ];

    $material = '';
    foreach ($materialKeys as $key) {
        if (!empty($pairs[$key])) {
            $material = $pairs[$key];
            break;
        }
    }

    [$parsedKarat, $parsedColor] = csv_mod_parse_material_text($material);
    $parsedSize = '';
    $parsedChainLength = '';
    $personalization = $pairs['personalization'] ?? '';

    foreach (['ring_size', 'size'] as $key) {
        if (!empty($pairs[$key])) {
            $parsedSize = trim($pairs[$key]);
            break;
        }
    }

    foreach (['necklace_length', 'chain_length', 'chain_lenght', 'chain_size', 'length'] as $key) {
        if (!empty($pairs[$key])) {
            $parsedChainLength = trim($pairs[$key]);
            if ($parsedSize === '') $parsedSize = $parsedChainLength;
            break;
        }
    }

    return [
        'pairs' => $pairs,
        'parsed_karat' => $parsedKarat,
        'parsed_color' => $parsedColor,
        'parsed_size' => $parsedSize,
        'parsed_chain_length' => $parsedChainLength,
        'personalization' => $personalization,
        'clean_text' => $cleanText,
    ];
}

function csv_mod_infer_product_category_from_sku(string $sku): string
{
    $sku = strtoupper(trim($sku));
    if (strlen($sku) < 2) return 'other';

    $typeLetter = substr($sku, 1, 1);
    return match ($typeLetter) {
        'R' => 'ring',
        'P' => 'pendant',
        'E' => 'earring',
        'B' => 'bracelet',
        default => 'other',
    };
}

function csv_mod_product_category_label(?string $category): string
{
    $category = strtolower((string)$category);
    return match ($category) {
        'ring', 'rings', 'yuzuk', 'yüzük' => 'Yüzük',
        'pendant', 'necklace', 'kolye' => 'Kolye / Pendant',
        'earring', 'earrings', 'kupe', 'küpe' => 'Küpe',
        'bracelet', 'bileklik' => 'Bileklik',
        default => 'Diğer',
    };
}

function csv_mod_is_custom_sku(?string $sku, ?string $title = ''): bool
{
    $sku = strtoupper(trim((string)$sku));
    $title = strtoupper(trim((string)$title));
    if ($sku === '') return false;
    if (str_starts_with($sku, 'CUSTOM')) return true;
    if (str_contains($sku, 'CUSTOM')) return true;
    if (str_contains($title, 'ORDER UPGRADE') || str_contains($title, 'CUSTOM ORDER')) return true;
    return false;
}

function csv_mod_quantity_label(array $row): string
{
    $idx = (int)($row['quantity_unit_index'] ?? 1);
    $total = (int)($row['quantity_unit_total'] ?? 1);
    if ($total > 1 && $idx > 0) return $idx . '/' . $total;
    $qty = (int)($row['quantity'] ?? 1);
    return $qty > 1 ? (string)$qty : '';
}

function csv_mod_check_flagged_customer(string $name, string $address, array $flaggedList): array
{
    $hayName = mb_strtolower($name, 'UTF-8');
    $hayAddress = mb_strtolower($address, 'UTF-8');
    $hayAll = trim($hayName . ' ' . $hayAddress);

    foreach ($flaggedList as $flag) {
        if ((int)$flag['is_active'] !== 1) continue;

        $matches = [];

        $fullName = mb_strtolower(trim((string)($flag['full_name'] ?? '')), 'UTF-8');
        if ($fullName !== '' && str_contains($hayName, $fullName)) {
            $matches[] = 'Tam ad eşleşmesi: ' . $flag['full_name'];
        }

        $lastName = mb_strtolower(trim((string)($flag['last_name'] ?? '')), 'UTF-8');
        if ($lastName !== '' && preg_match('/\b' . preg_quote($lastName, '/') . '\b/u', $hayName)) {
            $matches[] = 'Soyadı eşleşmesi: ' . $flag['last_name'];
        }

        $keywordsText = (string)($flag['address_keywords'] ?? '');
        $keywords = preg_split('/[\n,;]+/', $keywordsText);
        foreach ($keywords as $kw) {
            $kw = trim($kw);
            if ($kw === '') continue;
            $kwLower = mb_strtolower($kw, 'UTF-8');
            if (str_contains($hayAll, $kwLower)) {
                $matches[] = 'Adres/anahtar kelime eşleşmesi: ' . $kw;
            }
        }

        if ($matches) {
            $detail = implode(' | ', $matches);
            if (!empty($flag['risk_level'])) $detail .= ' | Risk: ' . $flag['risk_level'];
            if (!empty($flag['reason'])) $detail .= ' | Neden: ' . $flag['reason'];
            if (!empty($flag['notes'])) $detail .= ' | Not: ' . $flag['notes'];

            return [
                'flagged_customer_id' => (int)$flag['id'],
                'has_flag_warning' => 1,
                'flag_public_text' => 'Riskli müşteri kontrolünde uyarı aldı.',
                'flag_admin_detail' => $detail,
            ];
        }
    }

    return [
        'flagged_customer_id' => null,
        'has_flag_warning' => 0,
        'flag_public_text' => '',
        'flag_admin_detail' => '',
    ];
}

function csv_mod_image_url(?string $path): string
{
    $path = trim((string)$path);
    if ($path === '') return '';
    if (preg_match('~^https?://~i', $path)) return $path;
    if (str_starts_with($path, '/')) return $path;
    if (str_starts_with($path, 'uploads/')) return $path;
    return 'uploads/products/' . $path;
}

// Defensive DB helpers for CSV/production pages. These keep pages alive on older partial installs.
function csv_mod_db_table_exists(string $table): bool
{
    static $cache = [];
    $table = preg_replace('/[^A-Za-z0-9_]/', '', $table);
    if ($table === '') return false;
    if (array_key_exists($table, $cache)) return $cache[$table];
    try {
        $stmt = db()->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=?");
        $stmt->execute([$table]);
        return $cache[$table] = ((int)$stmt->fetchColumn() > 0);
    } catch (Throwable $e) {
        return $cache[$table] = false;
    }
}

function csv_mod_db_column_exists(string $table, string $column): bool
{
    static $cache = [];
    $table = preg_replace('/[^A-Za-z0-9_]/', '', $table);
    $column = preg_replace('/[^A-Za-z0-9_]/', '', $column);
    if ($table === '' || $column === '') return false;
    $key = $table . '.' . $column;
    if (array_key_exists($key, $cache)) return $cache[$key];
    try {
        $stmt = db()->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=?");
        $stmt->execute([$table, $column]);
        return $cache[$key] = ((int)$stmt->fetchColumn() > 0);
    } catch (Throwable $e) {
        return $cache[$key] = false;
    }
}

function csv_mod_product_image_sql(string $productAlias = 'p', string $as = 'image_path'): array
{
    $productAlias = preg_replace('/[^A-Za-z0-9_]/', '', $productAlias) ?: 'p';
    $as = preg_replace('/[^A-Za-z0-9_]/', '', $as) ?: 'image_path';
    if (csv_mod_db_table_exists('product_images') && csv_mod_db_column_exists('product_images', 'file_path')) {
        return ["pi.file_path AS {$as}", "LEFT JOIN product_images pi ON pi.product_id={$productAlias}.id AND pi.is_primary=1"];
    }
    return ["'' AS {$as}", ''];
}

function csv_mod_order_image_sql(string $orderAlias = 'oi', string $productAlias = 'p', string $as = 'image_path'): array
{
    $orderAlias = preg_replace('/[^A-Za-z0-9_]/', '', $orderAlias) ?: 'oi';
    $productAlias = preg_replace('/[^A-Za-z0-9_]/', '', $productAlias) ?: 'p';
    $as = preg_replace('/[^A-Za-z0-9_]/', '', $as) ?: 'image_path';
    $hasProductImages = csv_mod_db_table_exists('product_images') && csv_mod_db_column_exists('product_images', 'file_path');
    $hasOrderImage = csv_mod_db_column_exists('order_items', 'product_image_path');
    if ($hasProductImages && $hasOrderImage) {
        return ["COALESCE(pi.file_path, {$orderAlias}.product_image_path) AS {$as}", "LEFT JOIN product_images pi ON pi.product_id={$productAlias}.id AND pi.is_primary=1"];
    }
    if ($hasProductImages) {
        return ["pi.file_path AS {$as}", "LEFT JOIN product_images pi ON pi.product_id={$productAlias}.id AND pi.is_primary=1"];
    }
    if ($hasOrderImage) {
        return ["{$orderAlias}.product_image_path AS {$as}", ''];
    }
    return ["'' AS {$as}", ''];
}

// --- Operations CSV v4 helper functions ---
function csv_mod_money_value($value): float
{
    if ($value === null || $value === '') return 0.0;
    return (float)$value;
}

function csv_mod_net_item_total($price, $discount): ?float
{
    if (($price === null || $price === '') && ($discount === null || $discount === '')) return null;
    $net = csv_mod_money_value($price) - csv_mod_money_value($discount);
    return round($net, 2);
}

function csv_mod_shipping_type_from_values($orderShipping, ?string $country = ''): string
{
    $shipping = csv_mod_money_value($orderShipping);
    $countryNorm = mb_strtolower(trim((string)$country), 'UTF-8');
    if ($shipping > 0) return 'express';
    if ($countryNorm !== '' && !in_array($countryNorm, ['united states', 'usa', 'us', 'u.s.', 'u.s.a.'], true)) return 'international';
    return 'standard';
}

function csv_mod_shipping_label(?string $shippingType): string
{
    return match ((string)$shippingType) {
        'express' => 'EXSP',
        'international' => 'INT',
        'standard' => 'EKO',
        default => '-',
    };
}

function csv_mod_shipping_badge_class(?string $shippingType): string
{
    return match ((string)$shippingType) {
        'express' => 'op-badge-danger',
        'international' => 'op-badge-warn',
        'standard' => 'op-badge-muted',
        default => 'op-badge-muted',
    };
}

function csv_mod_compact_key(?string $value): string
{
    $value = mb_strtolower(csv_mod_clean_text($value), 'UTF-8');
    $value = preg_replace('/[^\p{L}\p{N}]+/u', ' ', $value);
    $value = trim(preg_replace('/\s+/u', ' ', $value));
    return $value;
}

function csv_mod_customer_group_key(?string $shipName, ?string $address1 = ''): string
{
    $name = csv_mod_compact_key($shipName);
    $addr = csv_mod_compact_key($address1);
    return trim($name . '|' . $addr, '|');
}

function csv_mod_order_group_key(?string $orderId, ?string $shipName, ?string $shipByDate = ''): string
{
    $orderId = csv_mod_compact_key($orderId);
    if ($orderId !== '') return 'order:' . $orderId;
    return 'fallback:' . csv_mod_compact_key($shipName) . '|' . csv_mod_compact_key($shipByDate);
}

function csv_mod_best_product_name(array $row): string
{
    foreach (['short_name', 'product_short_name', 'product_name'] as $field) {
        if (!empty($row[$field])) return (string)$row[$field];
    }
    return (string)($row['listing_title'] ?? '-');
}

function csv_mod_format_money($amount, ?string $currency = 'USD'): string
{
    if ($amount === null || $amount === '') return '-';
    return number_format((float)$amount, 2, '.', ',') . ' ' . ($currency ?: 'USD');
}

function csv_mod_check_flagged_customer_v4(string $name, string $address1, array $flaggedList): array
{
    $hayName = ' ' . csv_mod_compact_key($name) . ' ';
    $hayAddress = csv_mod_compact_key($address1);

    foreach ($flaggedList as $flag) {
        if ((int)($flag['is_active'] ?? 0) !== 1) continue;

        $lastNameRaw = trim((string)($flag['last_name'] ?? ''));
        $lastName = csv_mod_compact_key($lastNameRaw);
        $fullNameRaw = trim((string)($flag['full_name'] ?? ''));
        $fullName = csv_mod_compact_key($fullNameRaw);
        $keywordsText = (string)($flag['address_keywords'] ?? '');
        $keywords = preg_split('/[\n,;]+/', $keywordsText) ?: [];

        $nameMatch = false;
        $addressMatch = false;
        $matchParts = [];

        if ($lastName !== '' && str_contains($hayName, ' ' . $lastName . ' ')) {
            $nameMatch = true;
            $matchParts[] = 'Soyadı eşleşti: ' . $lastNameRaw;
        }
        if ($fullName !== '' && str_contains(trim($hayName), $fullName)) {
            $nameMatch = true;
            $matchParts[] = 'Ad eşleşti: ' . $fullNameRaw;
        }

        foreach ($keywords as $kw) {
            $kwRaw = trim($kw);
            if ($kwRaw === '') continue;
            $kwNorm = csv_mod_compact_key($kwRaw);
            if ($kwNorm !== '' && $hayAddress !== '' && str_contains($hayAddress, $kwNorm)) {
                $addressMatch = true;
                $matchParts[] = 'Adres eşleşti: ' . $kwRaw;
                break;
            }
        }

        if (!$nameMatch && !$addressMatch) continue;

        $matchType = ($nameMatch && $addressMatch) ? 'blocked' : 'suspicious';
        $publicText = $matchType === 'blocked' ? 'YASAKLI MÜŞTERİ UYARISI' : 'ŞÜPHELİ MÜŞTERİ UYARISI';
        $detail = implode(' | ', $matchParts);
        if (!empty($flag['risk_level'])) $detail .= ' | Risk: ' . $flag['risk_level'];
        if (!empty($flag['reason'])) $detail .= ' | Neden: ' . $flag['reason'];
        if (!empty($flag['notes'])) $detail .= ' | Not: ' . $flag['notes'];

        return [
            'flagged_customer_id' => (int)$flag['id'],
            'has_flag_warning' => 1,
            'flag_public_text' => $publicText,
            'flag_match_type' => $matchType,
            'flag_admin_detail' => $detail,
        ];
    }

    return [
        'flagged_customer_id' => null,
        'has_flag_warning' => 0,
        'flag_public_text' => '',
        'flag_match_type' => '',
        'flag_admin_detail' => '',
    ];
}
