<?php
/**
 * Seed demo data for Purchase Manager module.
 * Usage:
 *   /backend/seed_purchase_manager_demo.php
 *   /backend/seed_purchase_manager_demo.php?pm_id=1
 */

session_start();
header('Content-Type: application/json');
require_once 'config.php';

function prepareOrThrow($conn, $sql) {
    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        throw new Exception('SQL prepare failed: ' . $conn->error);
    }
    return $stmt;
}

function hasColumn($conn, $table, $column) {
    $tableEsc = $conn->real_escape_string($table);
    $columnEsc = $conn->real_escape_string($column);
    $result = $conn->query("SHOW COLUMNS FROM `$tableEsc` LIKE '$columnEsc'");
    return $result && $result->num_rows > 0;
}

function addColumnIfMissing($conn, $table, $column, $definition) {
    if (!hasColumn($conn, $table, $column)) {
        if (!$conn->query("ALTER TABLE `$table` ADD COLUMN `$column` $definition")) {
            throw new Exception("Failed adding column $table.$column: " . $conn->error);
        }
    }
}

function ensureSeederSchema($conn) {
    // Ensure base tables exist
    $conn->query("CREATE TABLE IF NOT EXISTS purchase_managers (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        phone VARCHAR(20),
        status ENUM('active','inactive') DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        last_login TIMESTAMP NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $conn->query("CREATE TABLE IF NOT EXISTS suppliers (
        id INT PRIMARY KEY AUTO_INCREMENT,
        purchase_manager_id INT NOT NULL,
        name VARCHAR(100) NOT NULL,
        contact_person VARCHAR(100),
        email VARCHAR(100),
        phone VARCHAR(20),
        address TEXT,
        rating DECIMAL(3,2),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $conn->query("CREATE TABLE IF NOT EXISTS purchase_requisitions (
        id INT PRIMARY KEY AUTO_INCREMENT,
        purchase_manager_id INT NOT NULL,
        item_description VARCHAR(255) NOT NULL,
        quantity DECIMAL(10,3) NOT NULL,
        unit VARCHAR(50),
        status ENUM('pending','approved','rejected','ordered','closed') DEFAULT 'pending',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $conn->query("CREATE TABLE IF NOT EXISTS purchase_orders (
        id INT PRIMARY KEY AUTO_INCREMENT,
        purchase_manager_id INT NOT NULL,
        supplier_id INT NULL,
        item_name VARCHAR(255) NOT NULL,
        quantity DECIMAL(10,3) NOT NULL,
        unit_price DECIMAL(10,2) DEFAULT 0,
        total_cost DECIMAL(12,2) DEFAULT 0,
        status ENUM('draft','confirmed','shipped','partially_received','delivered','cancelled') DEFAULT 'draft',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $conn->query("CREATE TABLE IF NOT EXISTS cost_comparisons (
        id INT PRIMARY KEY AUTO_INCREMENT,
        purchase_manager_id INT NOT NULL,
        item_name VARCHAR(255) NOT NULL,
        supplier_id INT NULL,
        price DECIMAL(10,2) NOT NULL,
        quantity DECIMAL(10,3) DEFAULT 1,
        unit VARCHAR(50),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    // Ensure required migrated columns exist
    addColumnIfMissing($conn, 'suppliers', 'lead_time_days', "INT DEFAULT 0");
    addColumnIfMissing($conn, 'suppliers', 'payment_terms', "VARCHAR(100) NULL");
    addColumnIfMissing($conn, 'suppliers', 'status', "ENUM('active','inactive') DEFAULT 'active'");

    addColumnIfMissing($conn, 'purchase_requisitions', 'ingredient_id', "INT NULL");
    addColumnIfMissing($conn, 'purchase_requisitions', 'priority', "ENUM('low','medium','high','urgent') DEFAULT 'medium'");
    addColumnIfMissing($conn, 'purchase_requisitions', 'requested_by_role', "VARCHAR(50) DEFAULT 'kitchen'");
    addColumnIfMissing($conn, 'purchase_requisitions', 'requested_by_name', "VARCHAR(100) NULL");
    addColumnIfMissing($conn, 'purchase_requisitions', 'needed_by_date', "DATE NULL");
    addColumnIfMissing($conn, 'purchase_requisitions', 'approval_date', "TIMESTAMP NULL");
    addColumnIfMissing($conn, 'purchase_requisitions', 'approved_by_manager_id', "INT NULL");
    addColumnIfMissing($conn, 'purchase_requisitions', 'rejection_reason', "VARCHAR(255) NULL");
    addColumnIfMissing($conn, 'purchase_requisitions', 'notes', "TEXT NULL");

    addColumnIfMissing($conn, 'purchase_orders', 'requisition_id', "INT NULL");
    addColumnIfMissing($conn, 'purchase_orders', 'ingredient_id', "INT NULL");
    addColumnIfMissing($conn, 'purchase_orders', 'po_number', "VARCHAR(50) NULL");
    addColumnIfMissing($conn, 'purchase_orders', 'unit', "VARCHAR(50) NULL");
    addColumnIfMissing($conn, 'purchase_orders', 'received_quantity', "DECIMAL(10,3) DEFAULT 0");
    addColumnIfMissing($conn, 'purchase_orders', 'expected_delivery_date', "DATE NULL");
    addColumnIfMissing($conn, 'purchase_orders', 'delivery_date', "TIMESTAMP NULL");
    addColumnIfMissing($conn, 'purchase_orders', 'invoice_number', "VARCHAR(100) NULL");
    addColumnIfMissing($conn, 'purchase_orders', 'received_by', "VARCHAR(100) NULL");
    addColumnIfMissing($conn, 'purchase_orders', 'notes', "TEXT NULL");

    addColumnIfMissing($conn, 'cost_comparisons', 'ingredient_id', "INT NULL");
}

function findIngredientByName($conn, $name) {
    $stmt = prepareOrThrow($conn, "SELECT id, name, unit FROM ingredients WHERE name = ? LIMIT 1");
    $stmt->bind_param('s', $name);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    return $row ?: null;
}

function firstActivePmId($conn) {
    $result = $conn->query("SELECT id FROM purchase_managers WHERE status = 'active' ORDER BY id ASC LIMIT 1");
    if ($result && $result->num_rows > 0) {
        $row = $result->fetch_assoc();
        return (int)$row['id'];
    }
    return null;
}

try {
    ensureSeederSchema($conn);

    $pm_id = isset($_GET['pm_id']) ? (int)$_GET['pm_id'] : 0;
    if ($pm_id <= 0 && isset($_SESSION['purchase_manager_id'])) {
        $pm_id = (int)$_SESSION['purchase_manager_id'];
    }
    if ($pm_id <= 0) {
        $pm_id = firstActivePmId($conn);
    }

    if (!$pm_id) {
        echo json_encode([
            'success' => false,
            'message' => 'No active purchase manager found. Create one from admin first.'
        ]);
        exit;
    }

    $conn->begin_transaction();

    $created = [
        'purchase_manager_id' => $pm_id,
        'suppliers' => [],
        'requisitions' => [],
        'purchase_orders' => [],
        'cost_comparisons' => []
    ];

    // 1) Suppliers
    $suppliers = [
        ['FarmFresh Wholesale', 'Ravi Kumar', 'farmfresh@example.com', '9000011111', 'Koyambedu Market, Chennai', 4.6, 1, 'Net 7', 'active'],
        ['Metro Meat Supply', 'Arjun Das', 'metromeat@example.com', '9000022222', 'Perambur, Chennai', 4.4, 2, 'Net 15', 'active'],
        ['Spice Route Traders', 'Nisha Roy', 'spiceroute@example.com', '9000033333', 'George Town, Chennai', 4.8, 3, 'COD', 'active']
    ];

    $supplierIds = [];
    foreach ($suppliers as $s) {
        $check = prepareOrThrow($conn, "SELECT id FROM suppliers WHERE purchase_manager_id = ? AND name = ? LIMIT 1");
        $check->bind_param('is', $pm_id, $s[0]);
        $check->execute();
        $existing = $check->get_result()->fetch_assoc();
        $check->close();

        if ($existing) {
            $supplierIds[$s[0]] = (int)$existing['id'];
            continue;
        }

        $stmt = prepareOrThrow($conn, "
            INSERT INTO suppliers (
                purchase_manager_id, name, contact_person, email, phone, address, rating,
                lead_time_days, payment_terms, status, created_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
        ");
        $stmt->bind_param('isssssdiss', $pm_id, $s[0], $s[1], $s[2], $s[3], $s[4], $s[5], $s[6], $s[7], $s[8]);
        $stmt->execute();
        $id = (int)$conn->insert_id;
        $stmt->close();

        $supplierIds[$s[0]] = $id;
        $created['suppliers'][] = ['id' => $id, 'name' => $s[0]];
    }

    // 2) Ingredient references (best effort)
    $tomato = findIngredientByName($conn, 'Tomato');
    $chicken = findIngredientByName($conn, 'Chicken Breast');
    $onion = findIngredientByName($conn, 'Onion');

    // 3) Requisitions
    $reqSeed = [
        [
            'ingredient_id' => $tomato['id'] ?? null,
            'item_description' => $tomato['name'] ?? 'Tomato',
            'quantity' => 12.000,
            'unit' => $tomato['unit'] ?? 'kg',
            'priority' => 'high',
            'status' => 'pending',
            'requested_by_name' => 'Head Chef',
            'needed_by_date' => date('Y-m-d', strtotime('+1 day')),
            'notes' => 'Breakfast prep load'
        ],
        [
            'ingredient_id' => $chicken['id'] ?? null,
            'item_description' => $chicken['name'] ?? 'Chicken Breast',
            'quantity' => 20.000,
            'unit' => $chicken['unit'] ?? 'kg',
            'priority' => 'urgent',
            'status' => 'approved',
            'requested_by_name' => 'Sous Chef',
            'needed_by_date' => date('Y-m-d', strtotime('+2 day')),
            'notes' => 'Weekend demand'
        ],
        [
            'ingredient_id' => $onion['id'] ?? null,
            'item_description' => $onion['name'] ?? 'Onion',
            'quantity' => 15.000,
            'unit' => $onion['unit'] ?? 'kg',
            'priority' => 'medium',
            'status' => 'ordered',
            'requested_by_name' => 'Kitchen',
            'needed_by_date' => date('Y-m-d', strtotime('+3 day')),
            'notes' => 'Regular replenishment'
        ]
    ];

    $reqIds = [];
    foreach ($reqSeed as $r) {
        $check = prepareOrThrow($conn, "
            SELECT id FROM purchase_requisitions
            WHERE purchase_manager_id = ? AND item_description = ? AND DATE(created_at) = CURDATE()
            LIMIT 1
        ");
        $check->bind_param('is', $pm_id, $r['item_description']);
        $check->execute();
        $existing = $check->get_result()->fetch_assoc();
        $check->close();

        if ($existing) {
            $reqIds[$r['item_description']] = (int)$existing['id'];
            continue;
        }

        $approvalDate = $r['status'] === 'approved' || $r['status'] === 'ordered' ? date('Y-m-d H:i:s') : null;
        $stmt = prepareOrThrow($conn, "
            INSERT INTO purchase_requisitions (
                purchase_manager_id, ingredient_id, item_description, quantity, unit, priority,
                requested_by_role, requested_by_name, needed_by_date, status, approval_date, approved_by_manager_id, notes, created_at
            ) VALUES (?, ?, ?, ?, ?, ?, 'kitchen', ?, ?, ?, ?, ?, ?, NOW())
        ");
        $stmt->bind_param(
            'iisdssssssis',
            $pm_id,
            $r['ingredient_id'],
            $r['item_description'],
            $r['quantity'],
            $r['unit'],
            $r['priority'],
            $r['requested_by_name'],
            $r['needed_by_date'],
            $r['status'],
            $approvalDate,
            $pm_id,
            $r['notes']
        );
        $stmt->execute();
        $reqId = (int)$conn->insert_id;
        $stmt->close();

        $reqIds[$r['item_description']] = $reqId;
        $created['requisitions'][] = [
            'id' => $reqId,
            'item' => $r['item_description'],
            'status' => $r['status']
        ];
    }

    // 4) Purchase order (linked to "ordered" requisition)
    $poItem = $onion['name'] ?? 'Onion';
    if (isset($reqIds[$poItem])) {
        $checkPo = prepareOrThrow($conn, "SELECT id FROM purchase_orders WHERE purchase_manager_id = ? AND requisition_id = ? LIMIT 1");
        $reqId = $reqIds[$poItem];
        $checkPo->bind_param('ii', $pm_id, $reqId);
        $checkPo->execute();
        $existingPo = $checkPo->get_result()->fetch_assoc();
        $checkPo->close();

        if (!$existingPo) {
            $po_number = 'PO-DEMO-' . strtoupper(substr(md5((string)microtime(true)), 0, 6));
            $supplierId = $supplierIds['FarmFresh Wholesale'] ?? array_values($supplierIds)[0];
            $ingredientId = $onion['id'] ?? null;
            $qty = 15.000;
            $unitPrice = 28.00;
            $total = $qty * $unitPrice;
            $status = 'confirmed';
            $eta = date('Y-m-d', strtotime('+2 day'));
            $notes = 'Demo PO from approved requisition';

            $stmt = prepareOrThrow($conn, "
                INSERT INTO purchase_orders (
                    purchase_manager_id, requisition_id, supplier_id, ingredient_id, po_number, item_name,
                    quantity, unit, unit_price, total_cost, received_quantity, status, created_at, expected_delivery_date, notes
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, ?, NOW(), ?, ?)
            ");
            $unit = $onion['unit'] ?? 'kg';
            $stmt->bind_param(
                'iiiissdsddsss',
                $pm_id,
                $reqId,
                $supplierId,
                $ingredientId,
                $po_number,
                $poItem,
                $qty,
                $unit,
                $unitPrice,
                $total,
                $status,
                $eta,
                $notes
            );
            $stmt->execute();
            $poId = (int)$conn->insert_id;
            $stmt->close();

            $created['purchase_orders'][] = ['id' => $poId, 'po_number' => $po_number, 'item' => $poItem];
        }
    }

    // 5) Cost comparison quotes
    $quotes = [
        ['Tomato', $supplierIds['FarmFresh Wholesale'] ?? null, 42.00, 1.000, 'kg', $tomato['id'] ?? null],
        ['Tomato', $supplierIds['Spice Route Traders'] ?? null, 45.50, 1.000, 'kg', $tomato['id'] ?? null],
        ['Chicken Breast', $supplierIds['Metro Meat Supply'] ?? null, 182.00, 1.000, 'kg', $chicken['id'] ?? null],
        ['Chicken Breast', $supplierIds['FarmFresh Wholesale'] ?? null, 190.00, 1.000, 'kg', $chicken['id'] ?? null]
    ];

    foreach ($quotes as $q) {
        if (!$q[1]) {
            continue;
        }
        $check = prepareOrThrow($conn, "
            SELECT id FROM cost_comparisons
            WHERE purchase_manager_id = ? AND item_name = ? AND supplier_id = ? AND DATE(created_at) = CURDATE()
            LIMIT 1
        ");
        $check->bind_param('isi', $pm_id, $q[0], $q[1]);
        $check->execute();
        $existing = $check->get_result()->fetch_assoc();
        $check->close();
        if ($existing) {
            continue;
        }

        $stmt = prepareOrThrow($conn, "
            INSERT INTO cost_comparisons (
                purchase_manager_id, ingredient_id, item_name, supplier_id, price, quantity, unit, created_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, NOW())
        ");
        $stmt->bind_param('iisidds', $pm_id, $q[5], $q[0], $q[1], $q[2], $q[3], $q[4]);
        $stmt->execute();
        $id = (int)$conn->insert_id;
        $stmt->close();

        $created['cost_comparisons'][] = ['id' => $id, 'item' => $q[0], 'price' => $q[2]];
    }

    $conn->commit();

    echo json_encode([
        'success' => true,
        'message' => 'Demo purchase manager data seeded successfully.',
        'data' => $created
    ]);
} catch (Throwable $e) {
    if ($conn->errno !== null) {
        try { $conn->rollback(); } catch (Throwable $ignored) {}
    }
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'message' => $e->getMessage()
    ]);
}
