<?php
require_once __DIR__ . '/helpers.php';
require_once __DIR__ . '/auth.php';
require_once __DIR__ . '/csv_module_common.php';
require_login();
require_role(['admin', 'production_manager', 'store_user']);

verify_csrf();

$user = current_user();
$role = $user['role'];
$userId = (int)$user['id'];
$storeId = (int)($_POST['store_id'] ?? 0);

if (!$storeId) {
    $_SESSION['flash'] = ['type' => 'error', 'msg' => 'Mağaza seçilmedi.'];
    redirect('csv_upload.php');
}

if ($role === 'store_user' && $storeId !== (int)$user['store_id']) {
    http_response_code(403);
    exit('Yetki hatası.');
}

$stmt = db()->prepare("SELECT * FROM stores WHERE id = :id AND is_active = 1 LIMIT 1");
$stmt->execute(['id' => $storeId]);
$store = $stmt->fetch();

if (!$store) {
    $_SESSION['flash'] = ['type' => 'error', 'msg' => 'Mağaza bulunamadı.'];
    redirect('csv_upload.php');
}

if (!isset($_FILES['csv_file']) || $_FILES['csv_file']['error'] !== UPLOAD_ERR_OK) {
    $_SESSION['flash'] = ['type' => 'error', 'msg' => 'CSV dosyası yüklenemedi.'];
    redirect('csv_upload.php');
}

$file = $_FILES['csv_file'];
$originalName = basename($file['name']);
$ext = strtolower(pathinfo($originalName, PATHINFO_EXTENSION));

if ($ext !== 'csv') {
    $_SESSION['flash'] = ['type' => 'error', 'msg' => 'Sadece .csv dosyası kabul edilir.'];
    redirect('csv_upload.php');
}

if ($file['size'] > 12 * 1024 * 1024) {
    $_SESSION['flash'] = ['type' => 'error', 'msg' => 'CSV dosyası 12 MB sınırını aşıyor.'];
    redirect('csv_upload.php');
}

$handle = fopen($file['tmp_name'], 'r');
if (!$handle) {
    $_SESSION['flash'] = ['type' => 'error', 'msg' => 'CSV dosyası okunamadı.'];
    redirect('csv_upload.php');
}

$bom = fread($handle, 3);
if ($bom !== "\xEF\xBB\xBF") rewind($handle);

$headerRaw = fgetcsv($handle, 0, ',', '"', '\\');
if (!$headerRaw) {
    fclose($handle);
    $_SESSION['flash'] = ['type' => 'error', 'msg' => 'CSV başlık satırı okunamadı.'];
    redirect('csv_upload.php');
}

$header = array_map('csv_mod_normalize_header', $headerRaw);

$columnMap = [
    'sale_date'         => ['sale_date'],
    'listing_title'     => ['item_name', 'listing_title', 'title'],
    'buyer'             => ['buyer', 'buyer_name'],
    'quantity'          => ['quantity'],
    'price'             => ['price'],
    'coupon_code'       => ['coupon_code'],
    'discount_amount'   => ['discount_amount', 'coupon_discount'],
    'shipping_discount' => ['shipping_discount'],
    'order_shipping'    => ['order_shipping'],
    'order_sales_tax'   => ['order_sales_tax'],
    'order_total'       => ['item_total', 'order_total'],
    'currency'          => ['currency'],
    'transaction_id'    => ['transaction_id'],
    'listing_id'        => ['listing_id'],
    'paid_date'         => ['date_paid', 'paid_date'],
    'shipped_date'      => ['date_shipped', 'shipped_date'],
    'ship_by_date'      => ['ship_by_date', 'scheduled_ship_date', 'ship_by', 'ship_date'],
    'ship_name'         => ['ship_name'],
    'ship_address1'     => ['ship_address1'],
    'ship_address2'     => ['ship_address2'],
    'ship_city'         => ['ship_city'],
    'ship_state'        => ['ship_state'],
    'ship_zip'          => ['ship_zipcode', 'ship_zip', 'ship_postal_code'],
    'ship_country'      => ['ship_country'],
    'order_id'          => ['order_id'],
    'variations_text'   => ['variations', 'item_variations', 'variations_text'],
    'order_type'        => ['order_type'],
    'payment_type'      => ['payment_type'],
    'sku'               => ['sku', 'item_sku'],
];

$idx = [];
foreach ($columnMap as $field => $aliases) {
    foreach ($aliases as $alias) {
        $pos = array_search($alias, $header, true);
        if ($pos !== false) {
            $idx[$field] = $pos;
            break;
        }
    }
}

if (!isset($idx['transaction_id'])) {
    fclose($handle);
    $_SESSION['flash'] = ['type' => 'error', 'msg' => 'CSV içinde Transaction ID kolonu bulunamadı.'];
    redirect('csv_upload.php');
}

$pdo = db();

$existingOrders = [];
// Daha önce üretime alınanları hem gerçek üretim transaction_id hem de kaynak Etsy Transaction ID ile kontrol ediyoruz.
// Quantity > 1 satırlar üretimde TID-1/TID-2 diye parçalanabilir; source_transaction_id tekrar importta duplicate yakalamayı sağlar.
try {
    $stmt = $pdo->query("SELECT transaction_id, source_transaction_id FROM order_items");
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        if (!empty($row['transaction_id'])) $existingOrders[(string)$row['transaction_id']] = true;
        if (!empty($row['source_transaction_id'])) $existingOrders[(string)$row['source_transaction_id']] = true;
    }
} catch (Throwable $e) {
    $stmt = $pdo->query("SELECT transaction_id FROM order_items");
    while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
        $existingOrders[(string)$row[0]] = true;
    }
}

$existingRows = [];
$stmt = $pdo->query("
    SELECT id, transaction_id, row_status, admin_decision, admin_rejection_reason, admin_note
    FROM csv_import_rows
");
while ($row = $stmt->fetch()) {
    $existingRows[(string)$row['transaction_id']] = $row;
}

$flaggedList = $pdo->query("SELECT * FROM flagged_customers WHERE is_active = 1")->fetchAll();

$skuMap = [];
// SKU eşleşmesini hem eski product_store_skus tablosundan hem de yeni reçete SKU tablosundan okuyoruz.
// Böylece BR0036V01 gibi yeni ürün kartı SKU'ları CSV'den geldiğinde ürün kartına bağlanabilir.
[$csvProductImageSelect, $csvProductImageJoin] = csv_mod_product_image_sql('p', 'image_path');
$stmt = $pdo->query("
    SELECT
        pss.sku,
        pss.id AS product_store_sku_id,
        p.id AS product_id,
        p.product_name,
        specs.short_name,
        p.category,
        sc.code AS stone_code,
        sc.description AS stone_description,
        $csvProductImageSelect
    FROM product_store_skus pss
    JOIN products p ON p.id = pss.product_id
    LEFT JOIN product_core_specs specs ON specs.product_id = p.id
    LEFT JOIN stone_codes sc ON sc.id = p.stone_code_id
    $csvProductImageJoin
    WHERE pss.is_active = 1 AND p.is_active = 1
");
while ($row = $stmt->fetch()) {
    $skuMap[strtoupper((string)$row['sku'])] = $row;
}
try {
    $stmt = $pdo->query("
        SELECT
            psrs.sales_sku AS sku,
            pss.id AS product_store_sku_id,
            p.id AS product_id,
            p.product_name,
            specs.short_name,
            p.category,
            sc.code AS stone_code,
            sc.description AS stone_description,
            $csvProductImageSelect
        FROM product_store_recipe_skus psrs
        JOIN products p ON p.id = psrs.product_id
        LEFT JOIN product_store_skus pss ON pss.sku = psrs.sales_sku AND pss.is_active = 1
        LEFT JOIN product_core_specs specs ON specs.product_id = p.id
        LEFT JOIN stone_codes sc ON sc.id = p.stone_code_id
        $csvProductImageJoin
        WHERE psrs.is_active = 1 AND p.is_active = 1
    ");
    while ($row = $stmt->fetch()) {
        $skuMap[strtoupper((string)$row['sku'])] = $row;
    }
} catch (Throwable $e) {
    // Eski kurulumlarda product_store_recipe_skus yoksa eski SKU tablosu ile devam edilir.
}

$stoneRows = [];
$stmt = $pdo->query("SELECT code, description, stone_type FROM stone_codes WHERE is_active = 1");
while ($row = $stmt->fetch()) {
    $stoneRows[(string)$row['code']] = $row;
}

$totalRows = 0;
$previewRows = 0;
$skippedExistingOrders = 0;
$skippedPendingAdmin = 0;
$errorRows = 0;

$pdo->beginTransaction();

try {
    $logStmt = $pdo->prepare("
        INSERT INTO csv_import_logs
            (store_id, uploaded_by, filename, original_filename, total_rows, inserted_rows, duplicate_rows, error_rows, status, created_at)
        VALUES
            (:store_id, :uploaded_by, :filename, :original_filename, 0, 0, 0, 0, 'preview_processing', NOW())
    ");
    $logStmt->execute([
        'store_id' => $storeId,
        'uploaded_by' => $userId,
        'filename' => $originalName,
        'original_filename' => $originalName,
    ]);
    $logId = (int)$pdo->lastInsertId();

    $upsert = $pdo->prepare("
        INSERT INTO csv_import_rows (
            csv_import_log_id, last_seen_import_log_id, store_id, uploaded_by,
            transaction_id, etsy_order_id, order_group_key, customer_group_key,
            sale_date, paid_date, ship_by_date, shipped_date,
            buyer_name, buyer_username,
            ship_name, ship_address1, ship_address2, ship_city, ship_state, ship_zip, ship_country,
            sku, listing_id, listing_title,
            quantity, price, currency, order_total, coupon_code, discount_amount, net_item_total, shipping_discount, order_shipping, order_sales_tax, shipping_type,
            variations_text, personalization, parsed_karat, parsed_color, parsed_size, parsed_chain_length, carrier,
            product_id, product_store_sku_id, product_card_missing, product_category, product_image_path, stone_code, stone_description,
            flagged_customer_id, has_flag_warning, flag_public_text, flag_match_type, flag_admin_detail,
            store_decision, store_decided_by, store_decided_at, store_note,
            row_status, seen_count, last_seen_at,
            raw_csv_json
        ) VALUES (
            :csv_import_log_id, :last_seen_import_log_id, :store_id, :uploaded_by,
            :transaction_id, :etsy_order_id, :order_group_key, :customer_group_key,
            :sale_date, :paid_date, :ship_by_date, :shipped_date,
            :buyer_name, :buyer_username,
            :ship_name, :ship_address1, :ship_address2, :ship_city, :ship_state, :ship_zip, :ship_country,
            :sku, :listing_id, :listing_title,
            :quantity, :price, :currency, :order_total, :coupon_code, :discount_amount, :net_item_total, :shipping_discount, :order_shipping, :order_sales_tax, :shipping_type,
            :variations_text, :personalization, :parsed_karat, :parsed_color, :parsed_size, :parsed_chain_length, :carrier,
            :product_id, :product_store_sku_id, :product_card_missing, :product_category, :product_image_path, :stone_code, :stone_description,
            :flagged_customer_id, :has_flag_warning, :flag_public_text, :flag_match_type, :flag_admin_detail,
            'pending', NULL, NULL, NULL,
            'store_pending', 1, NOW(),
            :raw_csv_json
        )
        ON DUPLICATE KEY UPDATE
            last_seen_import_log_id = VALUES(last_seen_import_log_id),
            store_id = VALUES(store_id),
            uploaded_by = VALUES(uploaded_by),
            etsy_order_id = VALUES(etsy_order_id),
            order_group_key = VALUES(order_group_key),
            customer_group_key = VALUES(customer_group_key),
            sale_date = VALUES(sale_date),
            paid_date = VALUES(paid_date),
            ship_by_date = VALUES(ship_by_date),
            shipped_date = VALUES(shipped_date),
            buyer_name = VALUES(buyer_name),
            buyer_username = VALUES(buyer_username),
            ship_name = VALUES(ship_name),
            ship_address1 = VALUES(ship_address1),
            ship_address2 = VALUES(ship_address2),
            ship_city = VALUES(ship_city),
            ship_state = VALUES(ship_state),
            ship_zip = VALUES(ship_zip),
            ship_country = VALUES(ship_country),
            sku = VALUES(sku),
            listing_id = VALUES(listing_id),
            listing_title = VALUES(listing_title),
            quantity = VALUES(quantity),
            price = VALUES(price),
            currency = VALUES(currency),
            order_total = VALUES(order_total),
            coupon_code = VALUES(coupon_code),
            discount_amount = VALUES(discount_amount),
            net_item_total = VALUES(net_item_total),
            shipping_discount = VALUES(shipping_discount),
            order_shipping = VALUES(order_shipping),
            order_sales_tax = VALUES(order_sales_tax),
            shipping_type = VALUES(shipping_type),
            variations_text = VALUES(variations_text),
            personalization = VALUES(personalization),
            parsed_karat = VALUES(parsed_karat),
            parsed_color = VALUES(parsed_color),
            parsed_size = VALUES(parsed_size),
            parsed_chain_length = VALUES(parsed_chain_length),
            product_id = VALUES(product_id),
            product_store_sku_id = VALUES(product_store_sku_id),
            product_card_missing = VALUES(product_card_missing),
            product_category = VALUES(product_category),
            product_image_path = VALUES(product_image_path),
            stone_code = VALUES(stone_code),
            stone_description = VALUES(stone_description),
            flagged_customer_id = VALUES(flagged_customer_id),
            has_flag_warning = VALUES(has_flag_warning),
            flag_public_text = VALUES(flag_public_text),
            flag_match_type = VALUES(flag_match_type),
            flag_admin_detail = VALUES(flag_admin_detail),
            store_decision = 'pending',
            store_decided_by = NULL,
            store_decided_at = NULL,
            store_note = NULL,
            row_status = 'store_pending',
            seen_count = seen_count + 1,
            last_seen_at = NOW(),
            raw_csv_json = VALUES(raw_csv_json),
            updated_at = NOW()
    ");

    while (($row = fgetcsv($handle, 0, ',', '"', '\\')) !== false) {
        if (count(array_filter($row, fn($v) => trim((string)$v) !== '')) === 0) continue;

        $totalRows++;

        $transactionId = preg_replace('/[^0-9A-Za-z\-]/', '', csv_mod_get($row, $idx, 'transaction_id'));
        if ($transactionId === '') {
            $errorRows++;
            continue;
        }

        if (isset($existingOrders[$transactionId])) {
            $skippedExistingOrders++;
            continue;
        }

        $prev = $existingRows[$transactionId] ?? null;
        if ($prev && in_array($prev['row_status'], ['admin_pending', 'converted_to_order', 'admin_approved'], true)) {
            $skippedPendingAdmin++;
            continue;
        }

        $sku = strtoupper(csv_mod_get($row, $idx, 'sku'));
        $variationsText = csv_mod_get($row, $idx, 'variations_text');
        $parsed = csv_mod_parse_variations($variationsText);
        $variationsText = $parsed['clean_text'] ?? $variationsText;

        [$buyerName, $buyerUsername] = csv_mod_parse_buyer(csv_mod_get($row, $idx, 'buyer'));

        $productInfo = $skuMap[$sku] ?? null;
        $productCardMissing = $productInfo ? 0 : 1;
        $productCategory = $productInfo['category'] ?? csv_mod_infer_product_category_from_sku($sku);
        $productImagePath = $productInfo['image_path'] ?? '';
        $stoneCode = $productInfo['stone_code'] ?? '';
        $stoneDescription = $productInfo['stone_description'] ?? '';

        if (!$stoneCode && strlen($sku) >= 2) {
            $suffix = substr($sku, -2);
            if (isset($stoneRows[$suffix])) {
                $stoneCode = $stoneRows[$suffix]['code'];
                $stoneDescription = $stoneRows[$suffix]['description'];
            }
        }

        $shipName = csv_mod_get($row, $idx, 'ship_name');
        $shipAddress1 = csv_mod_get($row, $idx, 'ship_address1');
        $shipCountry = csv_mod_get($row, $idx, 'ship_country');
        $price = csv_mod_parse_money(csv_mod_get($row, $idx, 'price'));
        $discountAmount = csv_mod_parse_money(csv_mod_get($row, $idx, 'discount_amount'));
        $orderShipping = csv_mod_parse_money(csv_mod_get($row, $idx, 'order_shipping'));
        $netItemTotal = csv_mod_net_item_total($price, $discountAmount);
        $shippingType = csv_mod_shipping_type_from_values($orderShipping, $shipCountry);
        $etsyOrderId = csv_mod_get($row, $idx, 'order_id') ?: null;
        $shipByDate = csv_mod_parse_date(csv_mod_get($row, $idx, 'ship_by_date'), true);
        $orderGroupKey = csv_mod_order_group_key($etsyOrderId, $shipName ?: $buyerName, $shipByDate ?: '');
        $customerGroupKey = csv_mod_customer_group_key($shipName ?: $buyerName, $shipAddress1);
        $flag = csv_mod_check_flagged_customer_v4($shipName ?: $buyerName, $shipAddress1, $flaggedList);
        $rawAssoc = array_combine($headerRaw, array_slice(array_pad($row, count($headerRaw), ''), 0, count($headerRaw)));

        $upsert->execute([
            'csv_import_log_id' => $logId,
            'last_seen_import_log_id' => $logId,
            'store_id' => $storeId,
            'uploaded_by' => $userId,
            'transaction_id' => $transactionId,
            'etsy_order_id' => $etsyOrderId,
            'order_group_key' => $orderGroupKey ?: null,
            'customer_group_key' => $customerGroupKey ?: null,
            'sale_date' => csv_mod_parse_date(csv_mod_get($row, $idx, 'sale_date')),
            'paid_date' => csv_mod_parse_date(csv_mod_get($row, $idx, 'paid_date')),
            'ship_by_date' => $shipByDate,
            'shipped_date' => csv_mod_parse_date(csv_mod_get($row, $idx, 'shipped_date'), true),
            'buyer_name' => $buyerName ?: null,
            'buyer_username' => $buyerUsername ?: null,
            'ship_name' => $shipName ?: null,
            'ship_address1' => $shipAddress1 ?: null,
            'ship_address2' => csv_mod_get($row, $idx, 'ship_address2') ?: null,
            'ship_city' => csv_mod_get($row, $idx, 'ship_city') ?: null,
            'ship_state' => csv_mod_get($row, $idx, 'ship_state') ?: null,
            'ship_zip' => csv_mod_get($row, $idx, 'ship_zip') ?: null,
            'ship_country' => $shipCountry ?: null,
            'sku' => $sku ?: null,
            'listing_id' => csv_mod_get($row, $idx, 'listing_id') ?: null,
            'listing_title' => csv_mod_get($row, $idx, 'listing_title') ?: null,
            'quantity' => max(1, csv_mod_parse_int(csv_mod_get($row, $idx, 'quantity'), 1)),
            'price' => $price,
            'currency' => csv_mod_get($row, $idx, 'currency') ?: null,
            'order_total' => csv_mod_parse_money(csv_mod_get($row, $idx, 'order_total')),
            'coupon_code' => csv_mod_get($row, $idx, 'coupon_code') ?: null,
            'discount_amount' => $discountAmount,
            'net_item_total' => $netItemTotal,
            'shipping_discount' => csv_mod_parse_money(csv_mod_get($row, $idx, 'shipping_discount')),
            'order_shipping' => $orderShipping,
            'order_sales_tax' => csv_mod_parse_money(csv_mod_get($row, $idx, 'order_sales_tax')),
            'shipping_type' => $shippingType,
            'variations_text' => $variationsText ?: null,
            'personalization' => $parsed['personalization'] ?: null,
            'parsed_karat' => $parsed['parsed_karat'] ?: null,
            'parsed_color' => $parsed['parsed_color'] ?: null,
            'parsed_size' => $parsed['parsed_size'] ?: null,
            'parsed_chain_length' => $parsed['parsed_chain_length'] ?: null,
            'carrier' => null,
            'product_id' => $productInfo['product_id'] ?? null,
            'product_store_sku_id' => $productInfo['product_store_sku_id'] ?? null,
            'product_card_missing' => $productCardMissing,
            'product_category' => $productCategory ?: null,
            'product_image_path' => $productImagePath ?: null,
            'stone_code' => $stoneCode ?: null,
            'stone_description' => $stoneDescription ?: null,
            'flagged_customer_id' => $flag['flagged_customer_id'] ?: null,
            'has_flag_warning' => (int)$flag['has_flag_warning'],
            'flag_public_text' => $flag['flag_public_text'] ?: null,
            'flag_match_type' => $flag['flag_match_type'] ?: null,
            'flag_admin_detail' => $flag['flag_admin_detail'] ?: null,
            'raw_csv_json' => json_encode($rawAssoc, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
        ]);
        $previewRows++;
    }

    fclose($handle);

    $upd = $pdo->prepare("
        UPDATE csv_import_logs
        SET total_rows = :total_rows,
            inserted_rows = :inserted_rows,
            duplicate_rows = :duplicate_rows,
            error_rows = :error_rows,
            status = 'preview_ready'
        WHERE id = :id
    ");
    $upd->execute([
        'total_rows' => $totalRows,
        'inserted_rows' => $previewRows,
        'duplicate_rows' => $skippedExistingOrders + $skippedPendingAdmin,
        'error_rows' => $errorRows,
        'id' => $logId,
    ]);

    $pdo->commit();
    unset($_SESSION['csv_preview']);
    redirect('csv_preview.php?log_id=' . $logId);
} catch (Throwable $e) {
    $pdo->rollBack();
    fclose($handle);
    http_response_code(500);
    echo 'CSV önizleme kaydı sırasında hata: ' . htmlspecialchars($e->getMessage());
}
