<?php
/**
 * import_extras.php - one-off importer for legacy extras (Pioneers).
 *
 * Posts each CSV row as an extra on a booking's MASTER folio, writing the four
 * tables that must stay consistent:
 *
 *   folio_transactions  - the money (the debit line)
 *   extra_charge_items  - the itemised detail behind that line
 *   folios              - total_charges / balance, recalculated
 *   orders              - payment_status, re-synced
 *
 * RUN DRY FIRST. With no --commit it writes NOTHING and prints exactly what it
 * would do, including unmatched items, missing bookings and probable duplicates.
 *
 *   php import_extras.php --csv=extras_pioneers.csv
 *   php import_extras.php --csv=extras_pioneers.csv --commit
 *
 * DECISIONS BAKED IN (agreed before writing):
 *   1. The CSV Charge is the NEGOTIATED price and is authoritative. The same
 *      item imports at wildly different amounts across bookings - "BGL Tour of
 *      The Falls" appears at 50, 52, 64, 100, 128, 150, 154, 385 - and the
 *      catalogue says 0.00. unit_price comes from the CSV; standard_unit_price
 *      from the item; is_manual_override = 1 where they differ.
 *   2. "Surcharge of 3% Levy on Card Payments" rows are SKIPPED. That is not an
 *      extra - post_cc_surcharge_to_folio() owns card surcharges, and importing
 *      them here would make them invisible to surcharge reporting.
 *   3. An unmatched item name is NOT a reason to drop the money. The row
 *      imports as source_table = 'manual_extra', source_id = 0, description
 *      verbatim.
 *
 * PHP 7.2 compatible.
 */

$opts = getopt('', ['csv:', 'commit', 'host:', 'db:', 'user:', 'pass:', 'limit:']);
$csvPath = isset($opts['csv']) ? $opts['csv'] : 'extras_pioneers.csv';
$commit  = true;//isset($opts['commit']);
$limit   = isset($opts['limit']) ? (int)$opts['limit'] : 0;

$host = isset($opts['host']) ? $opts['host'] : 'localhost';
$db   = isset($opts['db'])   ? $opts['db']   : 'macsoftlabsco_pioneers';
$user = isset($opts['user']) ? $opts['user'] : 'macsoftlabsco';
$pass = isset($opts['pass']) ? $opts['pass'] : 'S6x0Widn}trK^NN';

if ($db === '') {
    fwrite(STDERR, "ERROR: --db is required. This MUST be the Pioneers database.\n");
    fwrite(STDERR, "Usage: php import_extras.php --db=macsoftlabsco_pioneers --user=U --pass=P --csv=extras_pioneers.csv [--commit]\n");
    exit(1);
}

$pdo = new PDO("mysql:host={$host};dbname={$db};charset=utf8mb4", $user, $pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

echo str_repeat('=', 78), "\n";
echo "  EXTRAS IMPORT  -  ", ($commit ? "*** COMMIT (writing) ***" : "DRY RUN (no writes)"), "\n";
echo "  database: {$db}\n";
echo "  csv:      {$csvPath}\n";
echo str_repeat('=', 78), "\n\n";

/* ---------- helpers ---------- */

function tbl_exists(PDO $pdo, $t) {
    $s = $pdo->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=:t");
    $s->execute([':t' => $t]);
    return (int)$s->fetchColumn() === 1;
}
function col_exists(PDO $pdo, $t, $c) {
    $s = $pdo->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=:t AND COLUMN_NAME=:c");
    $s->execute([':t' => $t, ':c' => $c]);
    return (int)$s->fetchColumn() === 1;
}

/* ---------- preflight ---------- */

$required = ['orders', 'folios', 'folio_transactions', 'inventory_items'];
foreach ($required as $t) {
    if (!tbl_exists($pdo, $t)) {
        fwrite(STDERR, "ABORT: required table '{$t}' not found in {$db}.\n");
        exit(1);
    }
}
$hasExtraItems = tbl_exists($pdo, 'extra_charge_items');
if (!$hasExtraItems) {
    echo "NOTE: extra_charge_items does not exist. Folio lines will still be\n";
    echo "      posted, but without the itemised detail rows.\n\n";
}

/* ---------- read the CSV ---------- */

if (!is_readable($csvPath)) {
    fwrite(STDERR, "ABORT: cannot read {$csvPath}\n");
    exit(1);
}
$fh = fopen($csvPath, 'r');
$header = fgetcsv($fh);
// strip a UTF-8 BOM off the first header cell
if (isset($header[0])) {
    $header[0] = preg_replace('/^\xEF\xBB\xBF/', '', $header[0]);
}
$rows = [];
while (($line = fgetcsv($fh)) !== false) {
    if (count($line) === 1 && trim((string)$line[0]) === '') continue;
    $rows[] = array_combine($header, array_pad(array_slice($line, 0, count($header)), count($header), ''));
}
fclose($fh);
echo "CSV rows read: ", count($rows), "\n\n";

/* ---------- classify ---------- */

$toImport = [];
$skippedSurcharge = [];

foreach ($rows as $r) {
    $desc = trim((string)$r['Extra description']);
    // Decision 2: card surcharge is not an extra.
    if (stripos($desc, 'Surcharge') !== false && stripos($desc, 'Card') !== false) {
        $skippedSurcharge[] = $r;
        continue;
    }
    $toImport[] = $r;
}

echo "Card-surcharge rows skipped: ", count($skippedSurcharge), "\n";
foreach ($skippedSurcharge as $r) {
    printf("   order %-8s %s  %8.2f  %s\n", $r['order_id'], $r['service date'], (float)$r['Charge'], $r['Extra description']);
}
echo "\n";

/* ---------- resolve bookings ---------- */

$orderIds = [];
foreach ($toImport as $r) { $orderIds[(int)$r['order_id']] = true; }
$orderIds = array_keys($orderIds);

$in = implode(',', array_fill(0, count($orderIds), '?'));
$stmt = $pdo->prepare("SELECT id, property_id, order_no FROM orders WHERE id IN ({$in})");
$stmt->execute($orderIds);
$foundOrders = [];
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $o) { $foundOrders[(int)$o['id']] = $o; }

$missingOrders = array_values(array_diff($orderIds, array_keys($foundOrders)));

echo "Bookings referenced: ", count($orderIds), "\n";
echo "  found in {$db}: ", count($foundOrders), "\n";
echo "  MISSING:         ", count($missingOrders), "\n";
if ($missingOrders) {
    echo "  -> ", implode(', ', array_slice($missingOrders, 0, 30));
    if (count($missingOrders) > 30) echo ' ... (+' . (count($missingOrders) - 30) . ' more)';
    echo "\n  Rows for missing bookings CANNOT be imported - there is no folio to post to.\n";
}
echo "\n";

/* ---------- property sanity ---------- */

$propMismatch = [];
foreach ($toImport as $r) {
    $oid = (int)$r['order_id'];
    if (!isset($foundOrders[$oid])) continue;
    $csvProp = (int)$r['Property'];
    $dbProp  = (int)$foundOrders[$oid]['property_id'];
    if ($csvProp !== $dbProp) {
        $propMismatch[] = [$oid, $csvProp, $dbProp];
    }
}
if ($propMismatch) {
    echo "!! PROPERTY MISMATCH on ", count($propMismatch), " rows (csv says X, booking says Y):\n";
    foreach (array_slice($propMismatch, 0, 10) as $m) {
        printf("   order %-8s csv=%d  db=%d\n", $m[0], $m[1], $m[2]);
    }
    echo "   The booking's own property_id will be used, not the CSV's.\n\n";
} else {
    echo "Property check: all rows agree with their booking's property_id.\n\n";
}

/* ---------- match items ---------- */

$itemStmt = $pdo->prepare("SELECT id, item_name, selling_price FROM inventory_items WHERE item_name = :n LIMIT 1");
$itemCache = [];
$matched = 0; $unmatched = 0;
$unmatchedNames = [];

foreach ($toImport as $i => $r) {
    $name = trim((string)$r['Extra description']);
    if (!array_key_exists($name, $itemCache)) {
        $itemStmt->execute([':n' => $name]);
        $itemCache[$name] = $itemStmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }
    if ($itemCache[$name]) { $matched++; }
    else { $unmatched++; $unmatchedNames[$name] = isset($unmatchedNames[$name]) ? $unmatchedNames[$name] + 1 : 1; }
}

echo "Item matching (inventory_items.item_name, exact):\n";
echo "  matched:   {$matched} rows\n";
echo "  unmatched: {$unmatched} rows  -> imported as manual_extra (decision 3)\n";
if ($unmatchedNames) {
    arsort($unmatchedNames);
    echo "  unmatched names (", count($unmatchedNames), "):\n";
    $n = 0;
    foreach ($unmatchedNames as $nm => $c) {
        printf("     %4d  %s\n", $c, $nm);
        if (++$n >= 25) { echo "     ... (+" . (count($unmatchedNames) - 25) . " more)\n"; break; }
    }
}
echo "\n";

/* ---------- duplicate detection ---------- */

$dupStmt = $pdo->prepare("
    SELECT COUNT(*) FROM folio_transactions
    WHERE order_id = :order_id
      AND DATE(tx_date) = :d
      AND description LIKE :desc
      AND ROUND(debit,2) = :amt
      AND COALESCE(is_voided,0) = 0
");
$dupes = 0;
foreach ($toImport as $r) {
    $oid = (int)$r['order_id'];
    if (!isset($foundOrders[$oid])) continue;
    $dupStmt->execute([
        ':order_id' => $oid,
        ':d'        => $r['service date'],
        ':desc'     => '%' . trim((string)$r['Extra description']) . '%',
        ':amt'      => round((float)$r['Charge'], 2),
    ]);
    if ((int)$dupStmt->fetchColumn() > 0) { $dupes++; }
}
echo "Probable duplicates already on folios: {$dupes}\n";
if ($dupes > 0) {
    echo "  -> These will be SKIPPED. Re-running this import cannot double-charge.\n";
}
echo "\n";

/* ---------- summary ---------- */

$importable = 0; $importValue = 0.0;
foreach ($toImport as $r) {
    if (!isset($foundOrders[(int)$r['order_id']])) continue;
    $importable++;
    $importValue += (float)$r['Charge'];
}
echo str_repeat('-', 78), "\n";
printf("  WOULD IMPORT: %d rows across %d bookings, value %s %s\n",
    $importable, count($foundOrders), number_format($importValue, 2), 'USD');
printf("  skipped: %d surcharge, %d for missing bookings, %d duplicates\n",
    count($skippedSurcharge), count($toImport) - $importable, $dupes);
echo str_repeat('-', 78), "\n\n";

if (!$commit) {
    echo "DRY RUN - nothing written. Re-run with --commit to apply.\n";
    exit(0);
}

echo "COMMITTING...\n\n";

/* =====================================================================
   COMMIT
   ---------------------------------------------------------------------
   Mirrors what extras.php does when a user adds an extra by hand:
     1. resolve/create the booking's MASTER folio
     2. INSERT folio_transactions  (the money)
     3. INSERT extra_charge_items  (the detail, linked by folio_transaction_id)
     4. recalculate folios.total_charges / total_payments / balance
     5. re-sync orders.payment_status

   Steps 4 and 5 are not optional. folios.total_charges is denormalised - it
   does not update itself - so inserting transactions without recalculating
   leaves every touched folio silently wrong.

   One transaction PER BOOKING, not one for the whole run: a failure on booking
   #300 must not roll back the 299 that already imported cleanly.
   ===================================================================== */

$folioTxHasPropertyId = col_exists($pdo, 'folio_transactions', 'property_id');
$foliosHasUpdatedAt   = col_exists($pdo, 'folios', 'updated_at');

/* --- master folio, created if absent (same shape as get_or_create_master_folio) --- */
function import_master_folio(PDO $pdo, $orderId, $propertyId, $orderNo) {
    $s = $pdo->prepare("SELECT id FROM folios WHERE order_id = :o AND folio_type = 'master' LIMIT 1");
    $s->execute([':o' => $orderId]);
    $id = (int)($s->fetchColumn() ?: 0);
    if ($id > 0) return $id;

    $folioNo = 'FOL-M-' . $orderId;
    $s = $pdo->prepare("SELECT id FROM folios WHERE folio_no = :fn LIMIT 1");
    $s->execute([':fn' => $folioNo]);
    $id = (int)($s->fetchColumn() ?: 0);
    if ($id > 0) return $id;

    $pdo->prepare("
        INSERT INTO folios (property_id, order_id, folio_no, folio_name, folio_type, status,
                            total_charges, total_payments, balance)
        VALUES (:p, :o, :fn, :fname, 'master', 1, 0, 0, 0)
    ")->execute([
        ':p'     => $propertyId,
        ':o'     => $orderId,
        ':fn'    => $folioNo,
        ':fname' => 'Master Folio - ' . ($orderNo !== '' ? $orderNo : ('#' . $orderId)),
    ]);
    return (int)$pdo->lastInsertId();
}

/* --- recalculate a folio, honouring both void styles --- */
function import_recalc_folio(PDO $pdo, $folioId, $foliosHasUpdatedAt) {
    $s = $pdo->prepare("
        SELECT
            COALESCE(SUM(CASE
                WHEN LOWER(COALESCE(tx_type,'')) IN
                     ('payment_void','void_payment','payment_reversal','reversal','refund','payment_refund')
                THEN 0 ELSE debit END), 0) AS charges,
            COALESCE(SUM(credit), 0)
            - COALESCE(SUM(CASE
                WHEN LOWER(COALESCE(tx_type,'')) IN
                     ('payment_void','void_payment','payment_reversal','reversal','refund','payment_refund')
                THEN debit ELSE 0 END), 0) AS payments
        FROM folio_transactions
        WHERE folio_id = :f AND COALESCE(is_voided,0) = 0
    ");
    $s->execute([':f' => $folioId]);
    $t = $s->fetch(PDO::FETCH_ASSOC) ?: ['charges' => 0, 'payments' => 0];

    $charges  = round((float)$t['charges'], 2);
    $payments = round((float)$t['payments'], 2);

    $sql = "UPDATE folios SET total_charges = :c, total_payments = :p, balance = :b";
    if ($foliosHasUpdatedAt) $sql .= ", updated_at = NOW()";
    $sql .= " WHERE id = :id";
    $pdo->prepare($sql)->execute([
        ':c' => $charges, ':p' => $payments, ':b' => round($charges - $payments, 2), ':id' => $folioId,
    ]);
    return [$charges, $payments];
}

/* --- payment_status, matching sync_order_payment_status() --- */
function import_sync_payment_status(PDO $pdo, $orderId) {
    if (!col_exists($pdo, 'orders', 'payment_status')) return;
    $s = $pdo->prepare("
        SELECT COALESCE(SUM(total_charges),0) AS c, COALESCE(SUM(total_payments),0) AS p
        FROM folios WHERE order_id = :o AND COALESCE(status,1) = 1
    ");
    $s->execute([':o' => $orderId]);
    $r = $s->fetch(PDO::FETCH_ASSOC) ?: ['c' => 0, 'p' => 0];
    $c = round((float)$r['c'], 2); $p = round((float)$r['p'], 2);

    if ($p <= 0)          { $status = 0; }   // unpaid
    elseif ($p + 0.009 < $c) { $status = 1; } // partial
    else                  { $status = 2; }   // paid

    $pdo->prepare("UPDATE orders SET payment_status = :s WHERE id = :o")
        ->execute([':s' => $status, ':o' => $orderId]);
}

/* --- prepared statements --- */
$txCols = ['folio_id','order_id','tx_date','tx_type','description','debit','credit','source_table','source_id'];
$txVals = [':folio_id',':order_id',':tx_date',"'extra'",':description',':debit','0',':source_table',':source_id'];
if ($folioTxHasPropertyId) { array_unshift($txCols, 'property_id'); array_unshift($txVals, ':property_id'); }
$stmtTx = $pdo->prepare("INSERT INTO folio_transactions (" . implode(',', $txCols) . ") VALUES (" . implode(',', $txVals) . ")");

if ($hasExtraItems) {
    $stmtItem = $pdo->prepare("
        INSERT INTO extra_charge_items
        (property_id, order_id, folio_id, folio_transaction_id, line_no, source_table, source_id,
         item_type, item_name, item_code, qty, standard_unit_price, unit_price, standard_amount,
         line_total, is_manual_override, description, source_payload_json, created_by)
        VALUES
        (:property_id, :order_id, :folio_id, :folio_transaction_id, 1, :source_table, :source_id,
         :item_type, :item_name, :item_code, :qty, :standard_unit_price, :unit_price, :standard_amount,
         :line_total, :is_manual_override, :description, :source_payload_json, NULL)
    ");
}

/* --- group by booking so each commits independently --- */
$byOrder = [];
foreach ($toImport as $r) {
    $oid = (int)$r['order_id'];
    if (!isset($foundOrders[$oid])) continue;
    $byOrder[$oid][] = $r;
}

$okRows = 0; $okOrders = 0; $failOrders = 0; $skipDupes = 0; $importedValue = 0.0;
$done = 0;

foreach ($byOrder as $oid => $orderRows) {
    if ($limit > 0 && $done >= $limit) break;

    $order = $foundOrders[$oid];
    $propertyId = (int)$order['property_id'];   // the BOOKING's property, never the CSV's

    try {
        $pdo->beginTransaction();
        $folioId = import_master_folio($pdo, $oid, $propertyId, (string)($order['order_no'] ?? ''));
        $rowsThisOrder = 0;

        foreach ($orderRows as $r) {
            $desc   = trim((string)$r['Extra description']);
            $amount = round((float)$r['Charge'], 2);
            $qty    = (float)($r['Qty'] !== '' ? $r['Qty'] : 1);
            $date   = trim((string)$r['service date']);
            if ($amount <= 0 || $date === '') continue;

            // Duplicate guard - re-running must not double-charge.
            $dupStmt->execute([
                ':order_id' => $oid, ':d' => $date,
                ':desc' => '%' . $desc . '%', ':amt' => $amount,
            ]);
            if ((int)$dupStmt->fetchColumn() > 0) { $skipDupes++; continue; }

            $item = isset($itemCache[$desc]) ? $itemCache[$desc] : null;

            // Decision 3: no match is not a reason to drop the money.
            $sourceTable = $item ? 'inventory_items' : 'manual_extra';
            $sourceId    = $item ? (int)$item['id'] : 0;

            // Decision 1: the CSV amount is the negotiated price and wins.
            $unitPrice     = $qty > 0 ? round($amount / $qty, 2) : $amount;
            $standardPrice = $item ? round((float)$item['selling_price'], 2) : 0.00;
            $isOverride    = ($standardPrice > 0 && abs($standardPrice - $unitPrice) > 0.009) ? 1 : 0;

            // Same description format extras.php writes: "<desc> x <qty> @ <price>"
            $fullDesc = $desc . ' x ' . number_format($qty, 2, '.', '') . ' @ ' . number_format($unitPrice, 2, '.', '');

            $txParams = [
                ':folio_id'     => $folioId,
                ':order_id'     => $oid,
                ':tx_date'      => $date . ' 00:00:00',
                ':description'  => $fullDesc,
                ':debit'        => $amount,
                ':source_table' => $sourceTable,
                ':source_id'    => $sourceId,
            ];
            if ($folioTxHasPropertyId) $txParams[':property_id'] = $propertyId;
            $stmtTx->execute($txParams);
            $txId = (int)$pdo->lastInsertId();

            if ($hasExtraItems) {
                $stmtItem->execute([
                    ':property_id'          => $propertyId,
                    ':order_id'             => $oid,
                    ':folio_id'             => $folioId,
                    ':folio_transaction_id' => $txId,
                    ':source_table'         => $sourceTable,
                    ':source_id'            => $sourceId,
                    ':item_type'            => $item ? 'inventory_item' : 'manual',
                    ':item_name'            => mb_substr($desc, 0, 255),
                    ':item_code'            => null,
                    ':qty'                  => $qty,
                    ':standard_unit_price'  => $standardPrice,
                    ':unit_price'           => $unitPrice,
                    ':standard_amount'      => round($standardPrice * $qty, 2),
                    ':line_total'           => $amount,
                    ':is_manual_override'   => $isOverride,
                    ':description'          => mb_substr($fullDesc, 0, 255),
                    ':source_payload_json'  => json_encode([
                        'imported_from'   => basename($csvPath),
                        'imported_at'     => date('Y-m-d H:i:s'),
                        'csv_charge'      => $amount,
                        'csv_service_date'=> $date,
                        'matched_item'    => $item ? (int)$item['id'] : null,
                    ]),
                ]);
            }

            $rowsThisOrder++;
            $importedValue += $amount;
        }

        // MUST run: folios.total_charges is denormalised.
        import_recalc_folio($pdo, $folioId, $foliosHasUpdatedAt);
        import_sync_payment_status($pdo, $oid);

        $pdo->commit();
        $okRows += $rowsThisOrder;
        if ($rowsThisOrder > 0) $okOrders++;
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) $pdo->rollBack();
        $failOrders++;
        fwrite(STDERR, "  FAILED order {$oid}: " . $e->getMessage() . "\n");
    }

    $done++;
    if ($done % 50 === 0) echo "  ... {$done} bookings processed\n";
}

echo "\n", str_repeat('=', 78), "\n";
printf("  IMPORTED: %d rows across %d bookings, value %s USD\n",
    $okRows, $okOrders, number_format($importedValue, 2));
printf("  skipped duplicates: %d\n", $skipDupes);
printf("  failed bookings:    %d\n", $failOrders);
echo str_repeat('=', 78), "\n";
echo "\nVerify:\n";
echo "  SELECT COUNT(*), SUM(debit) FROM folio_transactions\n";
echo "   WHERE tx_type='extra' AND source_table IN ('inventory_items','manual_extra');\n";
