<?php
/*
|--------------------------------------------------------------------------
| company/sp_migrate.php
| One-time importer: copies ALL data from the old service_portal database
| into the new company-scoped tables (sp_customers, sp_machines,
| sp_stock_items, sp_stock_movements) for the CURRENT company.
| Run by a company admin. Safe to re-run: it clears this company's rows first.
|--------------------------------------------------------------------------
*/
ob_start();
session_start();
require_once '../config/database.php';

if (!isset($_SESSION['user_id']) || ($_SESSION['role'] ?? '') !== 'company_admin' || empty($_SESSION['company_id'])) {
    header('Location: ../auth/login.php');
    exit;
}
$companyId   = (int)$_SESSION['company_id'];
$companyName = $_SESSION['company_name'] ?? 'Company';
if (!function_exists('esc')) { function esc($v){ return htmlspecialchars((string)$v, ENT_QUOTES, 'UTF-8'); } }

$SP = 'anousone_service_portal';   // source database
$report = [];
$error  = '';
$done   = false;

/* Ensure destination tables exist */
function sp_ensure_tables(PDO $pdo): void {
    $pdo->exec("CREATE TABLE IF NOT EXISTS sp_customers (
        id INT AUTO_INCREMENT PRIMARY KEY, company_id INT NOT NULL, sd_no VARCHAR(50) NOT NULL,
        customer_name VARCHAR(255) DEFAULT NULL, telephone VARCHAR(100) DEFAULT NULL,
        vat_number VARCHAR(100) DEFAULT NULL, contract_status VARCHAR(20) NOT NULL DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_cust (company_id, sd_no), INDEX (company_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS sp_machines (
        id INT AUTO_INCREMENT PRIMARY KEY, company_id INT NOT NULL, sd_no VARCHAR(50) DEFAULT NULL,
        unique_machine_no VARCHAR(100) DEFAULT NULL, machine_model VARCHAR(255) DEFAULT NULL,
        serial_no VARCHAR(100) DEFAULT NULL, location_text TEXT, meter_date VARCHAR(50) DEFAULT NULL,
        main_email VARCHAR(255) DEFAULT NULL, invoice_email VARCHAR(255) DEFAULT NULL, cc_email VARCHAR(255) DEFAULT NULL,
        telephone VARCHAR(100) DEFAULT NULL, status VARCHAR(20) DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX (company_id, sd_no)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS sp_stock_items (
        id INT AUTO_INCREMENT PRIMARY KEY, company_id INT NOT NULL, order_code VARCHAR(100) DEFAULT NULL,
        short_code VARCHAR(255) DEFAULT NULL, description TEXT, supplier VARCHAR(255) DEFAULT NULL,
        machine_type VARCHAR(100) DEFAULT NULL, new_code VARCHAR(100) DEFAULT NULL, note TEXT,
        price DECIMAL(15,2) NOT NULL DEFAULT 0, reorder_level INT NOT NULL DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX (company_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS sp_stock_movements (
        id INT AUTO_INCREMENT PRIMARY KEY, company_id INT NOT NULL, item_id INT NOT NULL,
        move_date DATE DEFAULT NULL, direction VARCHAR(10) NOT NULL DEFAULT 'Out', qty INT NOT NULL DEFAULT 0,
        cost_price DECIMAL(15,2) NOT NULL DEFAULT 0, packing_no VARCHAR(100) DEFAULT NULL,
        customer_name VARCHAR(255) DEFAULT NULL, sd_no VARCHAR(50) DEFAULT NULL, note VARCHAR(255) DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX (company_id, item_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
}

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['run_migration'])) {
    try {
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        sp_ensure_tables($pdo);

        // Clear this company's existing rows (clean re-run)
        foreach (['sp_stock_movements','sp_stock_items','sp_machines','sp_customers'] as $t) {
            $pdo->prepare("DELETE FROM {$t} WHERE company_id=?")->execute([$companyId]);
        }

        /* 1) Customers (merge machines + customer_vat) */
        $custSql = "
            SELECT ids.cid AS sd_no,
                   COALESCE(m.customer_name, v.customer_name) AS customer_name,
                   COALESCE(m.telephone, v.telephone)         AS telephone,
                   v.vat_number                               AS vat_number,
                   COALESCE(v.contract_status, 'active')      AS contract_status
            FROM (
                SELECT customer_id cid FROM {$SP}.machines WHERE customer_id IS NOT NULL AND TRIM(customer_id) <> ''
                UNION
                SELECT customer_id cid FROM {$SP}.customer_vat
            ) ids
            LEFT JOIN (
                SELECT customer_id, MAX(customer_name) customer_name, MAX(NULLIF(TRIM(telephone),'')) telephone
                FROM {$SP}.machines GROUP BY customer_id
            ) m ON m.customer_id = ids.cid
            LEFT JOIN {$SP}.customer_vat v ON v.customer_id = ids.cid
        ";
        $insCust = $pdo->prepare("INSERT INTO sp_customers (company_id, sd_no, customer_name, telephone, vat_number, contract_status)
                                  VALUES (?,?,?,?,?,?)");
        $nCust = 0;
        foreach ($pdo->query($custSql)->fetchAll(PDO::FETCH_ASSOC) as $r) {
            $insCust->execute([$companyId, $r['sd_no'], $r['customer_name'], $r['telephone'], $r['vat_number'], $r['contract_status'] ?: 'active']);
            $nCust++;
        }
        $report['Customers'] = $nCust;

        /* 2) Machines */
        $nMac = $pdo->prepare("
            INSERT INTO sp_machines (company_id, sd_no, unique_machine_no, machine_model, serial_no, location_text,
                                     meter_date, main_email, invoice_email, cc_email, telephone, status)
            SELECT ?, customer_id, unique_machine_no, machine_model, serial_no, location_text,
                   meter_date, main_email, invoice_email, cc_email, telephone, status
            FROM {$SP}.machines
        ");
        $nMac->execute([$companyId]);
        $report['Machines'] = $nMac->rowCount();

        /* 3) Stock items (with id remap) + movements */
        $itemMap = [];
        try {
            $insItem = $pdo->prepare("INSERT INTO sp_stock_items (company_id, order_code, short_code, description, supplier,
                                        machine_type, new_code, note, price, reorder_level) VALUES (?,?,?,?,?,?,?,?,?,?)");
            $nItem = 0;
            foreach ($pdo->query("SELECT * FROM {$SP}.stock_items")->fetchAll(PDO::FETCH_ASSOC) as $it) {
                $insItem->execute([$companyId, $it['order_code'] ?? null, $it['short_code'] ?? null, $it['description'] ?? null,
                    $it['supplier'] ?? null, $it['machine_type'] ?? null, $it['new_code'] ?? null, $it['note'] ?? null,
                    $it['price'] ?? 0, $it['reorder_level'] ?? 0]);
                $itemMap[(int)$it['id']] = (int)$pdo->lastInsertId();
                $nItem++;
            }
            $report['Stock items'] = $nItem;

            $insMv = $pdo->prepare("INSERT INTO sp_stock_movements (company_id, item_id, move_date, direction, qty, cost_price,
                                     packing_no, customer_name, sd_no, note) VALUES (?,?,?,?,?,?,?,?,?,?)");
            $nMv = 0;
            foreach ($pdo->query("SELECT * FROM {$SP}.stock_movements")->fetchAll(PDO::FETCH_ASSOC) as $mv) {
                $newItem = $itemMap[(int)$mv['item_id']] ?? 0;
                if ($newItem === 0) continue;
                $insMv->execute([$companyId, $newItem, $mv['move_date'] ?: null, $mv['direction'] ?? 'Out', (int)($mv['qty'] ?? 0),
                    $mv['cost_price'] ?? 0, $mv['packing_no'] ?? null, $mv['customer_name'] ?? null, $mv['sd_no'] ?? null, $mv['note'] ?? null]);
                $nMv++;
            }
            $report['Stock movements'] = $nMv;
        } catch (Throwable $e) {
            $report['Stock'] = 'skipped (no stock tables in service_portal yet)';
        }

        $done = true;
    } catch (Throwable $e) {
        $error = $e->getMessage();
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Import Service Portal Data</title>
    <style>
        body { font-family:Arial,sans-serif; background:#eef2f9; color:#0f172a; margin:0; }
        .wrap { max-width:640px; margin:40px auto; padding:0 16px; }
        .card { background:#fff; border:1px solid #e6eaf2; border-radius:18px; box-shadow:0 12px 30px rgba(30,41,59,.08); padding:26px; }
        h1 { margin:0 0 8px; font-size:22px; }
        .warn { background:#fffbeb; color:#92400e; border:1px solid #fde68a; padding:12px 15px; border-radius:12px; margin:14px 0; }
        .ok { background:#ecfdf5; color:#047857; border:1px solid #a7f3d0; padding:12px 15px; border-radius:12px; margin:14px 0; }
        .err { background:#fef2f2; color:#b91c1c; border:1px solid #fecaca; padding:12px 15px; border-radius:12px; margin:14px 0; }
        table { width:100%; border-collapse:collapse; margin-top:10px; }
        td { padding:8px 10px; border-bottom:1px solid #eef2f7; }
        .btn { background:#dc2626; color:#fff; border:none; padding:12px 20px; border-radius:12px; font-weight:700; cursor:pointer; }
        a { color:#2563eb; text-decoration:none; font-weight:700; }
    </style>
</head>
<body>
<div class="wrap"><div class="card">
    <h1>Import Service Portal Data → <?= esc($companyName) ?></h1>
    <p>Copies customers, machines and stock from the old <strong>service_portal</strong> database into this company's new tables.</p>

    <?php if ($error): ?>
        <div class="err">Error: <?= esc($error) ?></div>
    <?php endif; ?>

    <?php if ($done): ?>
        <div class="ok">Migration complete.</div>
        <table>
            <?php foreach ($report as $k=>$v): ?>
                <tr><td><?= esc($k) ?></td><td><strong><?= esc($v) ?></strong></td></tr>
            <?php endforeach; ?>
        </table>
        <p style="margin-top:18px;"><a href="sp_customers.php">Open Customer Management &rarr;</a></p>
        <p><a href="dashboard.php">&larr; Back to Dashboard</a></p>
    <?php else: ?>
        <div class="warn">This will <strong>replace</strong> any existing customer/machine/stock data for this company with a fresh copy from service_portal. Safe to run once now.</div>
        <form method="POST" onsubmit="return confirm('Import all service_portal data into this company now?');">
            <button type="submit" name="run_migration" class="btn">Run Import Now</button>
            <a href="dashboard.php" style="margin-left:14px;">Cancel</a>
        </form>
    <?php endif; ?>
</div></div>
</body>
</html>