<?php
session_start();
require '../assets/db_connect.php';
header('Content-Type: application/json');

$shop_id    = $_SESSION['user']['shop_id'] ?? 0;
$created_by = $_SESSION['user']['id'] ?? 0;

if ($shop_id == 0 || $created_by == 0) {
    echo json_encode(['status'=>'error','message'=>'Shop or user session missing']);
    exit;
}

$supplier_id   = intval($_POST['supplier_id'] ?? 0);
$invoice_no    = trim($_POST['invoice_no'] ?? '');
$purchase_date = $_POST['purchase_date'] ?? date('Y-m-d');

$product_ids   = $_POST['product_id'] ?? [];
$batch_nos     = $_POST['batch_no'] ?? [];
$qtys          = $_POST['qty'] ?? [];
$cost_prices   = $_POST['cost_price'] ?? [];
$sell_prices   = $_POST['sell_price'] ?? [];
$expiry_dates  = $_POST['expiry_date'] ?? [];

if ($supplier_id == 0 || empty($invoice_no) || empty($product_ids)) {
    echo json_encode(['status'=>'error','message'=>'Please fill all required fields']);
    exit;
}

try {
    $pdo->beginTransaction();

    // Insert purchase - REMOVED updated_at from query
    $stmt = $pdo->prepare("
        INSERT INTO purchases 
        (supplier_id, invoice_no, purchase_date, total, created_by, updated_by, shop_id, created_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, NOW())
    ");
    $stmt->execute([$supplier_id, $invoice_no, $purchase_date, 0, $created_by, $created_by, $shop_id]);
    $purchase_id = $pdo->lastInsertId();

    $total = 0;

    for($i = 0; $i < count($product_ids); $i++) {
        $pid   = intval($product_ids[$i]);
        $batch = trim($batch_nos[$i]);
        $qty   = intval($qtys[$i]);
        $cost  = floatval($cost_prices[$i]);
        $sell  = floatval($sell_prices[$i]);
        $exp   = !empty($expiry_dates[$i]) ? $expiry_dates[$i] : null;

        if ($pid == 0 || $qty <= 0 || $cost <= 0 || $sell <= 0) {
            continue;
        }

        // Check if product exists
        $checkProduct = $pdo->prepare("SELECT id FROM products WHERE id = ? AND shop_id = ?");
        $checkProduct->execute([$pid, $shop_id]);
        if (!$checkProduct->fetch()) {
            throw new Exception("Product ID $pid not found in your shop");
        }

        // Check if batch exists
        $checkBatch = $pdo->prepare("
            SELECT id FROM product_batches 
            WHERE product_id = ? AND batch_no = ? AND shop_id = ?
        ");
        $checkBatch->execute([$pid, $batch, $shop_id]);
        $existingBatch = $checkBatch->fetch(PDO::FETCH_ASSOC);
        
        if ($existingBatch) {
            // Update existing batch
            $batch_id = $existingBatch['id'];
            $updateBatch = $pdo->prepare("
                UPDATE product_batches 
                SET qty = qty + ?, 
                    cost_price = ?, 
                    sell_price = ?, 
                    expiry_date = COALESCE(?, expiry_date),
                    updated_by = ?, 
                    updated_at = NOW()
                WHERE id = ?
            ");
            $updateBatch->execute([$qty, $cost, $sell, $exp, $created_by, $batch_id]);
        } else {
            // Insert new batch
            $batchStmt = $pdo->prepare("
                INSERT INTO product_batches
                (product_id, batch_no, qty, cost_price, sell_price, expiry_date, 
                 supplier_id, created_by, updated_by, shop_id, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
            ");
            $batchStmt->execute([$pid, $batch, $qty, $cost, $sell, $exp, $supplier_id, $created_by, $created_by, $shop_id]);
            $batch_id = $pdo->lastInsertId();
        }

        // Insert purchase line
        $lineStmt = $pdo->prepare("
            INSERT INTO purchase_lines (purchase_id, batch_id, qty, cost_price)
            VALUES (?, ?, ?, ?)
        ");
        $lineStmt->execute([$purchase_id, $batch_id, $qty, $cost]);

        // Add inventory movement
        $movementStmt = $pdo->prepare("
            INSERT INTO inventory_movements 
            (product_id, batch_id, change_qty, movement_type, reference_id, 
             created_by, shop_id, created_at)
            VALUES (?, ?, ?, 'PURCHASE', ?, ?, ?, NOW())
        ");
        $movementStmt->execute([$pid, $batch_id, $qty, $purchase_id, $created_by, $shop_id]);

        $total += $qty * $cost;
    }

    // Check if any valid items were added
    if ($total == 0) {
        throw new Exception("No valid items were added to the purchase");
    }

    // Update purchase total - REMOVED updated_at
    $update = $pdo->prepare("UPDATE purchases SET total = ? WHERE id = ?");
    $update->execute([$total, $purchase_id]);

    $pdo->commit();
    
    // Store success message
    $_SESSION['success_message'] = "Purchase #$purchase_id (Invoice: $invoice_no) saved successfully! Total: TSh " . number_format($total, 2);
    
    echo json_encode([
        'status' => 'success',
        'message' => "Purchase saved successfully!",
        'purchase_id' => $purchase_id,
        'invoice_no' => $invoice_no,
        'total' => $total
    ]);

} catch (Exception $e) {
    $pdo->rollBack();
    echo json_encode([
        'status' => 'error',
        'message' => "Failed to save purchase: " . $e->getMessage()
    ]);
}