<?php
/* ============================================================================
   WBMCZ — ORDER MIGRATION
   (visit-based selling · advance payment · cart · cash collection · seller dues)
   Run this ONCE on the live server:   https://wbmcz.online/ordmigration.php

   What it sets up
     1. seller_services   how each product is delivered — straight online, or a
                          seller visits — and how much is taken as advance
     2. service_categories the same two settings as a category-wide default, so a
                          new product starts out right without being told
     3. carts             the shopping basket
     4. orders            everything an order now has to remember: what was paid
                          online, what is still to be collected, who collects it,
                          which of the seven stages it has reached
     5. order_items       one row per product in the order, each with its own
                          seller, payout and amount due
     6. order_timeline    the seven stages, stamped as they happen
     7. seller_collections what a seller says they took from the customer, and
                          whether the office has verified it
     8. seller_settlements money handed over between seller and company, so
                          "how much is this seller holding" always has an answer

   Every old order keeps working exactly as it did — the new columns simply
   describe it as a fully-paid online order, which is what it was.

   Safe to run more than once. DELETE THIS FILE after a successful run.
   ============================================================================ */

require_once __DIR__ . '/config.php';
require_once __DIR__ . '/db.php';

$conn = $db->getConnection();
$log  = [];

function orm_table($conn, $t) {
    try { $r = $conn->query("SHOW TABLES LIKE '$t'"); return $r && $r->num_rows > 0; }
    catch (\Throwable $e) { return false; }
}
function orm_col($conn, $t, $c) {
    try { $r = $conn->query("SHOW COLUMNS FROM `$t` LIKE '$c'"); return $r && $r->num_rows > 0; }
    catch (\Throwable $e) { return false; }
}
function orm_add(&$log, $conn, $t, $c, $def) {
    if (!orm_table($conn, $t))  { $log[] = ['skip', "$t.$c", 'table is not there']; return; }
    if (orm_col($conn, $t, $c)) { $log[] = ['ok',   "$t.$c", 'already present'];    return; }
    try { $conn->query("ALTER TABLE `$t` ADD COLUMN `$c` $def"); $log[] = ['add', "$t.$c", $def]; }
    catch (\Throwable $e) { $log[] = ['fail', "$t.$c", $e->getMessage()]; }
}
function orm_create(&$log, $conn, $t, $sql) {
    if (orm_table($conn, $t)) { $log[] = ['ok', $t, 'table already exists']; return; }
    try { $conn->query($sql); $log[] = ['add', $t, 'table created']; }
    catch (\Throwable $e) { $log[] = ['fail', $t, $e->getMessage()]; }
}
function orm_index(&$log, $conn, $t, $name, $sql) {
    if (!orm_table($conn, $t)) return;
    try {
        $r = $conn->query("SHOW INDEX FROM `$t` WHERE Key_name = '$name'");
        if ($r && $r->num_rows > 0) { $log[] = ['ok', "$t.$name", 'already present']; return; }
        $conn->query("ALTER TABLE `$t` ADD $sql");
        $log[] = ['add', "$t.$name", 'index added'];
    } catch (\Throwable $e) { $log[] = ['skip', "$t.$name", $e->getMessage()]; }
}

/* ---------------------------------------------------------------------------
   1. How a product is delivered, and what is taken up front

   fulfilment and advance_mode are left NULL on purpose: a product with nothing
   of its own follows whatever its category says. Only a product that was
   deliberately set carries a value, and that value then wins. It is what lets
   the office switch a whole category over in one move without trampling the
   few products somebody set by hand.
   --------------------------------------------------------------------------- */
foreach (['seller_services', 'agency_services'] as $t) {
    orm_add($log, $conn, $t, 'fulfilment',      "VARCHAR(20) NULL DEFAULT NULL");
    orm_add($log, $conn, $t, 'advance_mode',    "VARCHAR(20) NULL DEFAULT NULL");
    orm_add($log, $conn, $t, 'advance_amount',  "DECIMAL(10,2) NOT NULL DEFAULT 0.00");
    orm_add($log, $conn, $t, 'advance_percent', "DECIMAL(5,2) NOT NULL DEFAULT 0.00");
    orm_add($log, $conn, $t, 'allow_cart',      "TINYINT(1) NOT NULL DEFAULT 1");
    orm_add($log, $conn, $t, 'allow_code',      "TINYINT(1) NOT NULL DEFAULT 1");
    orm_add($log, $conn, $t, 'visit_note',      "VARCHAR(255) NULL");
}
orm_index($log, $conn, 'seller_services', 'idx_ss_fulfil', "INDEX idx_ss_fulfil (fulfilment)");

/* An earlier build of this file made those two columns NOT NULL with a default
   of 'online', which quietly froze every product to "full payment" and left the
   category switch with nothing to do. Put them right, and hand back the products
   that were never actually set by anybody. */
foreach (['seller_services', 'agency_services'] as $t) {
    if (!orm_table($conn, $t) || !orm_col($conn, $t, 'fulfilment')) { continue; }
    try {
        $conn->query("ALTER TABLE `$t`
                        MODIFY fulfilment   VARCHAR(20) NULL DEFAULT NULL,
                        MODIFY advance_mode VARCHAR(20) NULL DEFAULT NULL");
        $conn->query("UPDATE `$t` SET fulfilment = NULL WHERE fulfilment = 'online'");
        $n1 = $conn->affected_rows;
        $conn->query("UPDATE `$t` SET advance_mode = NULL WHERE advance_mode = 'company'");
        $log[] = ['ok', "$t.fulfilment", $n1 > 0
            ? "$n1 product(s) handed back to their category's setting"
            : 'already following the category'];
    } catch (\Throwable $e) { $log[] = ['skip', "$t.fulfilment", $e->getMessage()]; }
}

/* ---------------------------------------------------------------------------
   2. The same two settings, category-wide, as the starting point
   --------------------------------------------------------------------------- */
orm_add($log, $conn, 'service_categories', 'fulfilment',      "VARCHAR(20) NOT NULL DEFAULT 'online'");
orm_add($log, $conn, 'service_categories', 'advance_mode',    "VARCHAR(20) NOT NULL DEFAULT 'company'");
orm_add($log, $conn, 'service_categories', 'advance_percent', "DECIMAL(5,2) NOT NULL DEFAULT 0.00");
orm_add($log, $conn, 'service_categories', 'advance_amount',  "DECIMAL(10,2) NOT NULL DEFAULT 0.00");

/* ---------------------------------------------------------------------------
   3. The basket
   --------------------------------------------------------------------------- */
orm_create($log, $conn, 'carts', "
    CREATE TABLE IF NOT EXISTS carts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        service_id INT NOT NULL,
        service_type VARCHAR(20) NOT NULL DEFAULT 'seller',
        qty INT NOT NULL DEFAULT 1,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uniq_cart (user_id, service_id, service_type),
        KEY idx_cart_user (user_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");

/* ---------------------------------------------------------------------------
   4. Everything an order now has to remember
   --------------------------------------------------------------------------- */
$order_cols = [
    'order_code'          => "VARCHAR(40) NULL",
    'fulfilment'          => "VARCHAR(20) NOT NULL DEFAULT 'online'",
    'customer_type'       => "VARCHAR(20) NOT NULL DEFAULT 'regular'",
    'payment_mode'        => "VARCHAR(20) NOT NULL DEFAULT 'full'",
    'mrp'                 => "DECIMAL(10,2) NOT NULL DEFAULT 0.00",
    'base_amount'         => "DECIMAL(10,2) NOT NULL DEFAULT 0.00",
    'discount_amount'     => "DECIMAL(10,2) NOT NULL DEFAULT 0.00",
    'paid_amount'         => "DECIMAL(10,2) NOT NULL DEFAULT 0.00",
    'wallet_used'         => "DECIMAL(10,2) NOT NULL DEFAULT 0.00",
    'gateway_used'        => "DECIMAL(10,2) NOT NULL DEFAULT 0.00",
    'due_amount'          => "DECIMAL(10,2) NOT NULL DEFAULT 0.00",
    'collected_amount'    => "DECIMAL(10,2) NOT NULL DEFAULT 0.00",
    'collected_mode'      => "VARCHAR(20) NULL",
    'collected_at'        => "DATETIME NULL",
    'collected_by'        => "INT NULL",
    'seller_payout'       => "DECIMAL(10,2) NOT NULL DEFAULT 0.00",
    'company_profit'      => "DECIMAL(10,2) NOT NULL DEFAULT 0.00",
    'payment_code'        => "VARCHAR(30) NULL",
    'payment_code_status' => "VARCHAR(20) NOT NULL DEFAULT 'none'",
    'visit_status'        => "VARCHAR(20) NOT NULL DEFAULT 'none'",
    'visit_at'            => "DATETIME NULL",
    'stage'               => "VARCHAR(30) NOT NULL DEFAULT 'placed'",
    'customer_name'       => "VARCHAR(150) NULL",
    'customer_mobile'     => "VARCHAR(25) NULL",
    'address'             => "VARCHAR(500) NULL",
    'note'                => "VARCHAR(500) NULL",
    'item_count'          => "INT NOT NULL DEFAULT 1",
    'updated_at'          => "TIMESTAMP NULL DEFAULT NULL",
];
foreach ($order_cols as $c => $d) { orm_add($log, $conn, 'orders', $c, $d); }

orm_index($log, $conn, 'orders', 'uniq_order_code', "UNIQUE INDEX uniq_order_code (order_code)");
orm_index($log, $conn, 'orders', 'idx_orders_stage',  "INDEX idx_orders_stage (stage)");
orm_index($log, $conn, 'orders', 'idx_orders_seller', "INDEX idx_orders_seller (seller_id, stage)");
orm_index($log, $conn, 'orders', 'idx_orders_user',   "INDEX idx_orders_user (user_id, created_at)");
orm_index($log, $conn, 'orders', 'idx_orders_code',   "INDEX idx_orders_code (payment_code)");

/* ---------------------------------------------------------------------------
   5. One row per product inside an order
   --------------------------------------------------------------------------- */
orm_create($log, $conn, 'order_items', "
    CREATE TABLE IF NOT EXISTS order_items (
        id INT AUTO_INCREMENT PRIMARY KEY,
        order_id INT NOT NULL,
        service_id INT NOT NULL,
        service_type VARCHAR(20) NOT NULL DEFAULT 'seller',
        title VARCHAR(255) NOT NULL,
        category_id INT NULL,
        seller_id INT NULL,
        seller_name VARCHAR(150) NULL,
        seller_mobile VARCHAR(25) NULL,
        qty INT NOT NULL DEFAULT 1,
        mrp DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        base_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        discount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        advance DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        due DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        seller_payout DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        company_profit DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        fulfilment VARCHAR(20) NOT NULL DEFAULT 'online',
        status VARCHAR(30) NOT NULL DEFAULT 'pending',
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        KEY idx_oi_order (order_id),
        KEY idx_oi_seller (seller_id, status),
        KEY idx_oi_service (service_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");

/* ---------------------------------------------------------------------------
   6. The seven stages, stamped as they happen
   --------------------------------------------------------------------------- */
orm_create($log, $conn, 'order_timeline', "
    CREATE TABLE IF NOT EXISTS order_timeline (
        id BIGINT AUTO_INCREMENT PRIMARY KEY,
        order_id INT NOT NULL,
        order_code VARCHAR(40) NULL,
        stage VARCHAR(30) NOT NULL,
        title VARCHAR(160) NOT NULL,
        note VARCHAR(500) NULL,
        amount DECIMAL(10,2) NULL,
        actor_id INT NULL,
        actor_name VARCHAR(150) NULL,
        actor_role VARCHAR(20) NULL,
        ip_address VARCHAR(45) NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        KEY idx_ot_order (order_id, id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");

/* ---------------------------------------------------------------------------
   7. What the seller says they took, and whether the office agrees
   --------------------------------------------------------------------------- */
orm_create($log, $conn, 'seller_collections', "
    CREATE TABLE IF NOT EXISTS seller_collections (
        id INT AUTO_INCREMENT PRIMARY KEY,
        order_id INT NOT NULL,
        order_code VARCHAR(40) NULL,
        item_id INT NULL,
        seller_id INT NOT NULL,
        customer_id INT NULL,
        customer_name VARCHAR(150) NULL,
        amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        mode VARCHAR(20) NOT NULL DEFAULT 'cash',
        reference VARCHAR(120) NULL,
        note VARCHAR(500) NULL,
        status VARCHAR(20) NOT NULL DEFAULT 'pending',
        recorded_by INT NULL,
        recorded_by_name VARCHAR(150) NULL,
        approved_by INT NULL,
        approved_by_name VARCHAR(150) NULL,
        approved_at DATETIME NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        KEY idx_sc_order (order_id),
        KEY idx_sc_seller (seller_id, status),
        KEY idx_sc_status (status, created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");

/* ---------------------------------------------------------------------------
   8. Money handed over between seller and company
   --------------------------------------------------------------------------- */
orm_create($log, $conn, 'seller_settlements', "
    CREATE TABLE IF NOT EXISTS seller_settlements (
        id INT AUTO_INCREMENT PRIMARY KEY,
        seller_id INT NOT NULL,
        direction VARCHAR(10) NOT NULL DEFAULT 'in',
        amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        mode VARCHAR(20) NOT NULL DEFAULT 'cash',
        reference VARCHAR(120) NULL,
        note VARCHAR(500) NULL,
        order_id INT NULL,
        created_by INT NULL,
        created_by_name VARCHAR(150) NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        KEY idx_st_seller (seller_id, created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
");

/* ---------------------------------------------------------------------------
   9. Describe every order that already exists
      They were all paid in full, online, so that is what they are marked as.
      Nothing about them changes — they simply gain the words for it.
   --------------------------------------------------------------------------- */
if (orm_table($conn, 'orders') && orm_col($conn, 'orders', 'order_code')) {

    /* a code for the ones that have none: WBMCZ-YYMMDD-000123 */
    try {
        $conn->query(
            "UPDATE orders
                SET order_code = CONCAT('WBMCZ-', DATE_FORMAT(created_at, '%y%m%d'), '-', LPAD(id, 6, '0'))
              WHERE order_code IS NULL OR order_code = ''");
        $n = $conn->affected_rows;
        $log[] = ['add', 'orders.order_code', $n > 0 ? "$n old order(s) given a code" : 'every order already had one'];
    } catch (\Throwable $e) { $log[] = ['fail', 'orders.order_code', $e->getMessage()]; }

    /* everything paid, nothing outstanding */
    try {
        $conn->query(
            "UPDATE orders
                SET paid_amount = amount, base_amount = amount, mrp = amount,
                    due_amount = 0, item_count = 1
              WHERE paid_amount = 0 AND amount > 0");
        $n = $conn->affected_rows;
        $log[] = ['add', 'orders amounts', $n > 0 ? "$n old order(s) marked fully paid" : 'nothing needed filling in'];
    } catch (\Throwable $e) { $log[] = ['fail', 'orders amounts', $e->getMessage()]; }

    /* the stage that matches the status they already carry */
    try {
        $conn->query(
            "UPDATE orders SET stage = CASE
                WHEN LOWER(status) IN ('completed','complete','success') THEN 'completed'
                WHEN LOWER(status) IN ('cancelled','canceled','rejected')  THEN 'cancelled'
                ELSE 'paid' END
             WHERE stage = 'placed'");
        $n = $conn->affected_rows;
        $log[] = ['add', 'orders.stage', $n > 0 ? "$n old order(s) placed on the timeline" : 'already done'];
    } catch (\Throwable $e) { $log[] = ['fail', 'orders.stage', $e->getMessage()]; }

    /* one item row each, so every order reads the same way everywhere */
    if (orm_table($conn, 'order_items')) {
        try {
            $conn->query(
                "INSERT INTO order_items
                    (order_id, service_id, service_type, title, category_id, seller_id, qty,
                     mrp, base_price, amount, advance, due, fulfilment, status)
                 SELECT o.id, 0, 'seller', o.service_name, o.category_id, o.seller_id, 1,
                        o.amount, o.amount, o.amount, o.amount, 0, 'online',
                        CASE WHEN LOWER(o.status) IN ('completed','complete','success') THEN 'completed' ELSE 'pending' END
                   FROM orders o
                  WHERE NOT EXISTS (SELECT 1 FROM order_items i WHERE i.order_id = o.id)");
            $n = $conn->affected_rows;
            $log[] = ['add', 'order_items', $n > 0 ? "$n old order(s) given their item row" : 'already there'];
        } catch (\Throwable $e) { $log[] = ['fail', 'order_items backfill', $e->getMessage()]; }
    }
}

/* ---------------------------------------------------------------------------
   10. The new modules join every full-access role
   --------------------------------------------------------------------------- */
if (orm_table($conn, 'roles')) {
    try {
        $rs = $conn->query("SELECT id, permissions FROM roles");
        $t = 0;
        while ($rs && ($r = $rs->fetch_assoc())) {
            $p = json_decode((string)$r['permissions'], true);
            if (!is_array($p) || !in_array('all', $p, true)) continue;
            foreach (['manage_orders'] as $need) {
                if (!in_array($need, $p, true)) { $p[] = $need; $t++; }
            }
            $st = $conn->prepare("UPDATE roles SET permissions = ? WHERE id = ?");
            $j = json_encode(array_values($p)); $st->bind_param('si', $j, $r['id']); $st->execute(); $st->close();
        }
        $log[] = ['ok', 'roles', $t > 0 ? "$t entry added" : 'nothing needed changing'];
    } catch (\Throwable $e) { $log[] = ['fail', 'roles', $e->getMessage()]; }
}

$fails = 0; $adds = 0;
foreach ($log as $l) { if ($l[0] === 'fail') $fails++; if ($l[0] === 'add') $adds++; }
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Order Migration — visit selling &amp; advance payment</title>
    <script src="https://cdn.tailwindcss.com"></script>
</head>
<body class="bg-slate-100 p-6 font-sans">
    <div class="max-w-3xl mx-auto bg-white rounded-2xl shadow p-6 border border-slate-200">
        <h1 class="text-2xl font-extrabold text-slate-800 mb-1">Order Migration</h1>
        <p class="text-sm text-slate-500 mb-5">
            Database: <b><?php echo DB_NAME; ?></b> &middot;
            visit-based selling, advance payment, the cart, cash collection and seller dues
        </p>

        <div class="mb-5 rounded-xl px-4 py-3 <?php echo $fails ? 'bg-rose-50 text-rose-700 border border-rose-200' : 'bg-emerald-50 text-emerald-700 border border-emerald-200'; ?>">
            <?php echo $fails ? "Finished with $fails error(s) — see the rows marked FAIL."
                              : "Migration completed successfully — $adds change(s) applied. Every existing order was left exactly as it was."; ?>
        </div>

        <table class="w-full text-sm border-collapse">
            <thead>
                <tr class="bg-slate-50 text-slate-500 text-xs uppercase tracking-wider">
                    <th class="text-left py-2 px-3">Result</th>
                    <th class="text-left py-2 px-3">Target</th>
                    <th class="text-left py-2 px-3">Detail</th>
                </tr>
            </thead>
            <tbody class="divide-y divide-slate-100">
            <?php foreach ($log as $l):
                $cls = ['ok' => 'text-slate-400', 'add' => 'text-emerald-600', 'skip' => 'text-amber-600', 'fail' => 'text-rose-600'][$l[0]] ?? '';
            ?>
                <tr>
                    <td class="py-2 px-3 font-bold uppercase <?php echo $cls; ?>"><?php echo $l[0]; ?></td>
                    <td class="py-2 px-3 font-mono text-slate-700 text-xs"><?php echo htmlspecialchars($l[1]); ?></td>
                    <td class="py-2 px-3 text-slate-500 text-xs"><?php echo htmlspecialchars($l[2]); ?></td>
                </tr>
            <?php endforeach; ?>
            </tbody>
        </table>

        <div class="mt-6 text-xs text-slate-600 space-y-2 bg-slate-50 border border-slate-200 rounded-xl p-4">
            <p class="font-bold text-slate-700 text-sm">Where to go next</p>
            <p><b>Admin &rarr; Service Management &rarr; Product Management</b> — each product now has a
               <b>Delivery</b> setting. Leave it on <i>Online</i> for anything that finishes on the website, or
               switch it to <i>Seller visit</i> for the ones somebody has to go out for. Set the advance there too.</p>
            <p><b>Admin &rarr; Order Management</b> — every order, what has been paid, what is still to collect,
               and how much each seller is holding.</p>
            <p><b>Seller &rarr; Collections</b> — the seller's own list of visits to make and money to bring in.</p>
        </div>

        <p class="mt-4 text-xs text-rose-600 font-bold">Delete ordmigration.php from the server once this page shows &ldquo;completed successfully&rdquo;.</p>
    </div>
</body>
</html>
