<?php
/*
|--------------------------------------------------------------------------
| company/sp_migrate_jobs.php
| Imports the full jobs system from the old service_portal database into
| the new company-scoped tables for the CURRENT company:
|   sp_payment_types, sp_jobs, sp_job_staff, sp_job_images
| Handles ID remapping:
|   - job.machine_id  -> sp_machines.id via unique_machine_no
|   - assignment.staff_id -> Seven user_id via staff.seven_staff_code -> company_staff.staff_code
|   - customer_id -> stored as sd_no (already the SD code)
| Safe to re-run: clears this company's job 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';
$report = [];
$error  = '';
$done   = false;

function jobs_ensure_tables(PDO $pdo): void {
    $pdo->exec("CREATE TABLE IF NOT EXISTS sp_payment_types (
        id INT AUTO_INCREMENT PRIMARY KEY, company_id INT NOT NULL,
        code VARCHAR(50) DEFAULT NULL, label VARCHAR(255) DEFAULT NULL, fee DECIMAL(15,2) NOT NULL DEFAULT 0,
        is_manual_fee TINYINT(1) NOT NULL DEFAULT 0, status VARCHAR(20) DEFAULT 'active', sort_order INT DEFAULT 0,
        INDEX(company_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS sp_jobs (
        id INT AUTO_INCREMENT PRIMARY KEY, company_id INT NOT NULL,
        job_no VARCHAR(60) DEFAULT NULL, job_type VARCHAR(60) DEFAULT NULL,
        sd_no VARCHAR(50) DEFAULT NULL, machine_id INT DEFAULT NULL, machine_no VARCHAR(100) DEFAULT NULL,
        issue_title VARCHAR(255) DEFAULT NULL, issue_description TEXT, priority VARCHAR(20) DEFAULT NULL,
        payment_type VARCHAR(60) DEFAULT NULL, staff_fee DECIMAL(15,2) NOT NULL DEFAULT 0,
        status VARCHAR(20) DEFAULT 'open', open_date DATETIME DEFAULT NULL, solution_note TEXT,
        closed_at DATETIME DEFAULT NULL, opened_by_admin_id INT DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX(company_id), INDEX(company_id, status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS sp_job_staff (
        id INT AUTO_INCREMENT PRIMARY KEY, company_id INT NOT NULL, job_id INT NOT NULL, user_id INT NOT NULL,
        INDEX(company_id, job_id), INDEX(company_id, user_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $pdo->exec("CREATE TABLE IF NOT EXISTS sp_job_images (
        id INT AUTO_INCREMENT PRIMARY KEY, company_id INT NOT NULL, job_id INT NOT NULL,
        image_path VARCHAR(500) DEFAULT NULL, image_type VARCHAR(50) DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX(company_id, job_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
}

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

        foreach (['sp_job_images','sp_job_staff','sp_jobs','sp_payment_types'] as $t) {
            $pdo->prepare("DELETE FROM {$t} WHERE company_id=?")->execute([$companyId]);
        }

        /* 1) Payment types */
        $nPt = 0;
        try {
            $insPt = $pdo->prepare("INSERT INTO sp_payment_types (company_id, code, label, fee, is_manual_fee, status, sort_order)
                                    VALUES (?,?,?,?,?,?,?)");
            foreach ($pdo->query("SELECT * FROM {$SP}.payment_types")->fetchAll(PDO::FETCH_ASSOC) as $p) {
                $insPt->execute([$companyId, $p['code'] ?? null, $p['label'] ?? null, $p['fee'] ?? 0,
                    $p['is_manual_fee'] ?? 0, $p['status'] ?? 'active', $p['sort_order'] ?? 0]);
                $nPt++;
            }
        } catch (Throwable $e) { /* no payment_types table */ }
        $report['Payment types'] = $nPt;

        /* Build staff_id -> Seven user_id map (via seven_staff_code) */
        $staffToUser = [];
        try {
            $mapStmt = $pdo->prepare("
                SELECT s.id AS staff_id, cs.user_id
                FROM {$SP}.staff s
                JOIN company_staff cs ON cs.company_id = ? AND cs.staff_code = s.seven_staff_code
                WHERE s.seven_staff_code IS NOT NULL AND s.seven_staff_code <> ''
            ");
            $mapStmt->execute([$companyId]);
            foreach ($mapStmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
                $staffToUser[(int)$r['staff_id']] = (int)$r['user_id'];
            }
        } catch (Throwable $e) { /* mapping unavailable */ }

        /* 2) Jobs (with machine remap by unique_machine_no) */
        $spMachine = $pdo->prepare("SELECT id FROM sp_machines WHERE company_id=? AND unique_machine_no=? LIMIT 1");
        $insJob = $pdo->prepare("INSERT INTO sp_jobs
            (company_id, job_no, job_type, sd_no, machine_id, machine_no, issue_title, issue_description,
             priority, payment_type, staff_fee, status, open_date, solution_note, closed_at, opened_by_admin_id, created_at)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        $jobMap = [];
        $nJob = 0;
        $jobRows = $pdo->query("
            SELECT j.*, m.unique_machine_no AS umn
            FROM {$SP}.jobs j
            LEFT JOIN {$SP}.machines m ON m.id = j.machine_id
        ")->fetchAll(PDO::FETCH_ASSOC);
        foreach ($jobRows as $j) {
            $umn = $j['umn'] ?? null;
            $newMachineId = null;
            if ($umn) {
                $spMachine->execute([$companyId, $umn]);
                $newMachineId = $spMachine->fetchColumn() ?: null;
            }
            $insJob->execute([
                $companyId, $j['job_no'] ?? null, $j['job_type'] ?? null, $j['customer_id'] ?? null,
                $newMachineId ? (int)$newMachineId : null, $umn,
                $j['issue_title'] ?? null, $j['issue_description'] ?? null, $j['priority'] ?? null,
                $j['payment_type'] ?? null, $j['staff_fee'] ?? 0, $j['status'] ?? 'open',
                $j['open_date'] ?? null, $j['solution_note'] ?? null, $j['closed_at'] ?? null,
                $j['opened_by_admin_id'] ?? null, $j['created_at'] ?? null
            ]);
            $jobMap[(int)$j['id']] = (int)$pdo->lastInsertId();
            $nJob++;
        }
        $report['Jobs'] = $nJob;

        /* 3) Job staff assignments (remap job_id + staff_id) */
        $nAsg = 0; $skippedAsg = 0;
        try {
            $insAsg = $pdo->prepare("INSERT INTO sp_job_staff (company_id, job_id, user_id) VALUES (?,?,?)");
            foreach ($pdo->query("SELECT job_id, staff_id FROM {$SP}.job_staff_assignments")->fetchAll(PDO::FETCH_ASSOC) as $a) {
                $newJob = $jobMap[(int)$a['job_id']] ?? 0;
                $uid    = $staffToUser[(int)$a['staff_id']] ?? 0;
                if ($newJob && $uid) { $insAsg->execute([$companyId, $newJob, $uid]); $nAsg++; }
                else { $skippedAsg++; }
            }
        } catch (Throwable $e) { /* no assignments table */ }
        $report['Job assignments'] = $nAsg . ($skippedAsg ? " ({$skippedAsg} skipped: staff not mapped)" : '');

        /* 4) Job images (remap job_id) */
        $nImg = 0;
        try {
            $insImg = $pdo->prepare("INSERT INTO sp_job_images (company_id, job_id, image_path, image_type, created_at) VALUES (?,?,?,?,?)");
            foreach ($pdo->query("SELECT * FROM {$SP}.job_images")->fetchAll(PDO::FETCH_ASSOC) as $im) {
                $newJob = $jobMap[(int)$im['job_id']] ?? 0;
                if ($newJob) { $insImg->execute([$companyId, $newJob, $im['image_path'] ?? null, $im['image_type'] ?? null, $im['created_at'] ?? null]); $nImg++; }
            }
        } catch (Throwable $e) { /* no images table */ }
        $report['Job images'] = $nImg;

        $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 Jobs 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:#0f766e; 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 Jobs Data &rarr; <?= esc($companyName) ?></h1>
    <p>Copies all service jobs, assignments, payment types and photo references from <strong>service_portal</strong> into this company's new job tables. Run this <em>after</em> the customer/machine import (jobs link to machines).</p>
    <?php if ($error): ?><div class="err">Error: <?= esc($error) ?></div><?php endif; ?>
    <?php if ($done): ?>
        <div class="ok">Jobs 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="dashboard.php">&larr; Back to Dashboard</a></p>
    <?php else: ?>
        <div class="warn">This <strong>replaces</strong> any existing job data for this company with a fresh copy from service_portal. Make sure the customer/machine import was run first.</div>
        <form method="POST" onsubmit="return confirm('Import all jobs into this company now?');">
            <button type="submit" name="run_migration" class="btn">Run Jobs Import Now</button>
            <a href="dashboard.php" style="margin-left:14px;">Cancel</a>
        </form>
    <?php endif; ?>
</div></div>
</body></html>