<?php
session_start();

require(realpath(__DIR__ . '/../../../services/Model.php'));
$obj = new Model();
$db = $obj->getConnection();

$userid = isset($_SESSION['userid']) ? $_SESSION['userid'] : null;

function purchaseResponse($success, $status, $extra = [])
{
    echo json_encode(array_merge(['success' => $success, 'status' => $status], $extra));
    exit;
}

function getPurchaseRecord(Model $obj, int $purchaseId)
{
    return $obj->rawSqlSingle("SELECT * FROM purchases WHERE purchase_id = '$purchaseId' AND deleted_at IS NULL");
}

function getPurchaseBatchId(Model $obj, array $purchase)
{
    $purchaseId = (int)($purchase['purchase_id'] ?? 0);
    if ($purchaseId <= 0) {
        return null;
    }

    $activeBatch = $obj->rawSqlSingle("
        SELECT batch_id
        FROM product_model
        WHERE purchase_id = '$purchaseId'
          AND batch_id IS NOT NULL
          AND batch_id <> ''
          AND deleted_at IS NULL
        ORDER BY id ASC
        LIMIT 1
    ");

    if (!empty($activeBatch['batch_id'])) {
        return $activeBatch['batch_id'];
    }

    $fallbackBatch = $obj->rawSqlSingle("
        SELECT batch_id
        FROM product_model
        WHERE purchase_id = '$purchaseId'
          AND batch_id IS NOT NULL
          AND batch_id <> ''
        ORDER BY id ASC
        LIMIT 1
    ");

    if (!empty($fallbackBatch['batch_id'])) {
        return $fallbackBatch['batch_id'];
    }

    $productId = (int)($purchase['product_id'] ?? 0);
    $supplierId = (int)($purchase['supplier_id'] ?? 0);
    $stockRows = $obj->rawSql("
        SELECT batch_id
        FROM stock
        WHERE product_id = '$productId'
          AND supplier_id = '$supplierId'
          AND deleted_at IS NULL
        ORDER BY stock_id ASC
        LIMIT 2
    ");

    if (count($stockRows) === 1 && !empty($stockRows[0]['batch_id'])) {
        return $stockRows[0]['batch_id'];
    }

    return null;
}

function getPurchaseStats(Model $obj, int $purchaseId)
{
    $stats = $obj->rawSqlSingle("
        SELECT
            COALESCE(SUM(CASE WHEN deleted_at IS NULL THEN 1 ELSE 0 END), 0) AS active_count,
            COALESCE(SUM(CASE WHEN deleted_at IS NULL AND sold = 0 AND returned = 0 THEN 1 ELSE 0 END), 0) AS available_count,
            COALESCE(SUM(CASE WHEN deleted_at IS NULL AND sold = 1 THEN 1 ELSE 0 END), 0) AS sold_count,
            COALESCE(SUM(CASE WHEN deleted_at IS NULL AND returned = 1 THEN 1 ELSE 0 END), 0) AS returned_count
        FROM product_model
        WHERE purchase_id = '$purchaseId'
    ");

    return [
        'active_count' => (int)($stats['active_count'] ?? 0),
        'available_count' => (int)($stats['available_count'] ?? 0),
        'sold_count' => (int)($stats['sold_count'] ?? 0),
        'returned_count' => (int)($stats['returned_count'] ?? 0),
    ];
}

function syncPurchaseInventory(PDO $db, Model $obj, array $purchase, $userid, $purchaseDate)
{
    $purchaseId = (int)$purchase['purchase_id'];
    $batchId = getPurchaseBatchId($obj, $purchase);
    if ($batchId === null || $batchId === '') {
        return ['success' => false, 'status' => 'Purchase batch not found'];
    }

    $stats = getPurchaseStats($obj, $purchaseId);
    if ($stats['active_count'] <= 0) {
        return ['success' => false, 'status' => 'At least one active model is required'];
    }

    $now = date('Y-m-d H:i:s');

    $purchaseStmt = $db->prepare("
        UPDATE purchases
        SET product_id = ?, supplier_id = ?, quantity = ?, purchase_date = ?, updated_by = ?, updated_at = ?
        WHERE purchase_id = ?
    ");
    $purchaseStmt->execute([
        (int)$purchase['product_id'],
        (int)$purchase['supplier_id'],
        $stats['active_count'],
        $purchaseDate,
        $userid,
        $now,
        $purchaseId,
    ]);

    $modelStmt = $db->prepare("
        UPDATE product_model
        SET product_id = ?, batch_id = ?, updated_by = ?, updated_at = ?
        WHERE purchase_id = ?
    ");
    $modelStmt->execute([
        (int)$purchase['product_id'],
        $batchId,
        $userid,
        $now,
        $purchaseId,
    ]);

    $stock = $obj->rawSqlSingle("SELECT * FROM stock WHERE batch_id = '$batchId' AND deleted_at IS NULL ORDER BY stock_id ASC LIMIT 1");

    if ($stock) {
        $stockStmt = $db->prepare("
            UPDATE stock
            SET product_id = ?, supplier_id = ?, current_stock = ?, updated_by = ?, updated_at = ?
            WHERE stock_id = ?
        ");
        $stockStmt->execute([
            (int)$purchase['product_id'],
            (int)$purchase['supplier_id'],
            $stats['available_count'],
            $userid,
            $now,
            (int)$stock['stock_id'],
        ]);
    } else {
        $insertStockStmt = $db->prepare("
            INSERT INTO stock (product_id, current_stock, batch_id, supplier_id, created_by, created_at, deleted_at)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ");
        $insertStockStmt->execute([
            (int)$purchase['product_id'],
            $stats['available_count'],
            $batchId,
            (int)$purchase['supplier_id'],
            $userid,
            $now,
            null,
        ]);
    }

    return [
        'success' => true,
        'batch_id' => $batchId,
        'quantity' => $stats['active_count'],
        'available_quantity' => $stats['available_count'],
    ];
}

if ($_SERVER['REQUEST_METHOD'] === 'POST' && !isset($_POST['id'])) {
    $batchId = trim($_POST['batch_id'] ?? '');
    $product = (int)($_POST['product'] ?? 0);
    $quantity = (int)($_POST['quantity'] ?? 0);
    $supplier = (int)($_POST['supplier'] ?? 0);
    $purchaseDate = trim($_POST['purchase_date'] ?? '');
    $modelNos = $_POST['modelNos'] ?? [];
    $serialNos = $_POST['serialNos'] ?? [];
    $expireDates = $_POST['expireDates'] ?? [];

    if ($batchId === '' || $product <= 0 || $supplier <= 0 || $quantity <= 0 || $purchaseDate === '') {
        purchaseResponse(false, 'Please fill all required purchase fields');
    }

    if (!is_array($modelNos) || !is_array($serialNos) || !is_array($expireDates)) {
        purchaseResponse(false, 'Invalid product model data');
    }

    if (count($modelNos) !== $quantity || count($serialNos) !== $quantity || count($expireDates) !== $quantity) {
        purchaseResponse(false, 'Quantity must match the number of model rows');
    }

    foreach ($modelNos as $index => $modelNo) {
        if (trim($modelNo) === '' || trim($serialNos[$index] ?? '') === '' || trim($expireDates[$index] ?? '') === '') {
            purchaseResponse(false, 'Every model row must include model, serial, and expire date');
        }
    }

    $existingBatch = $obj->rawSqlSingle("SELECT stock_id FROM stock WHERE batch_id = '$batchId' AND deleted_at IS NULL");
    if ($existingBatch) {
        purchaseResponse(false, 'Generated batch already exists. Please try again');
    }

    try {
        $db->beginTransaction();
        $now = date('Y-m-d H:i:s');

        $purchaseStmt = $db->prepare("
            INSERT INTO purchases (product_id, supplier_id, quantity, purchase_date, created_by, created_at, deleted_at)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ");
        $purchaseStmt->execute([$product, $supplier, $quantity, $purchaseDate, $userid, $now, null]);
        $purchaseId = (int)$db->lastInsertId();

        $stockStmt = $db->prepare("
            INSERT INTO stock (product_id, current_stock, batch_id, supplier_id, created_by, created_at, deleted_at)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ");
        $stockStmt->execute([$product, $quantity, $batchId, $supplier, $userid, $now, null]);

        $modelStmt = $db->prepare("
            INSERT INTO product_model (purchase_id, product_id, batch_id, model_no, serial_no, expire_date, created_by, created_at, deleted_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");
        foreach ($modelNos as $index => $modelNo) {
            $modelStmt->execute([
                $purchaseId,
                $product,
                $batchId,
                trim($modelNo),
                trim($serialNos[$index]),
                trim($expireDates[$index]),
                $userid,
                $now,
                null,
            ]);
        }

        $db->commit();
        purchaseResponse(true, 'Added successfully', ['purchase_id' => $purchaseId, 'batch_id' => $batchId]);
    } catch (Throwable $e) {
        if ($db->inTransaction()) {
            $db->rollBack();
        }
        purchaseResponse(false, 'Failed to add purchase');
    }
} elseif ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['id'])) {
    $id = (int)($_POST['id'] ?? 0);
    $product = (int)($_POST['product'] ?? 0);
    $supplier = (int)($_POST['supplier'] ?? 0);
    $purchaseDate = trim($_POST['purchase_date'] ?? '');

    if ($id <= 0 || $product <= 0 || $supplier <= 0 || $purchaseDate === '') {
        purchaseResponse(false, 'Please fill all required purchase fields');
    }

    $purchase = getPurchaseRecord($obj, $id);
    if (!$purchase) {
        purchaseResponse(false, 'Purchase not found');
    }

    $stats = getPurchaseStats($obj, $id);
    if ($stats['active_count'] <= 0) {
        purchaseResponse(false, 'Purchase must have at least one model');
    }

    $hasLockedModels = $stats['sold_count'] > 0 || $stats['returned_count'] > 0;
    if ($hasLockedModels && ((int)$purchase['product_id'] !== $product || (int)$purchase['supplier_id'] !== $supplier)) {
        purchaseResponse(false, 'This purchase already has sold or returned models, so product or supplier cannot be changed');
    }

    $purchase['product_id'] = $product;
    $purchase['supplier_id'] = $supplier;

    try {
        $db->beginTransaction();
        $syncResult = syncPurchaseInventory($db, $obj, $purchase, $userid, $purchaseDate);
        if (empty($syncResult['success'])) {
            throw new RuntimeException($syncResult['status'] ?? 'Failed to sync purchase');
        }

        $db->commit();
        purchaseResponse(true, 'Updated successfully', ['batch_id' => $syncResult['batch_id']]);
    } catch (Throwable $e) {
        if ($db->inTransaction()) {
            $db->rollBack();
        }
        purchaseResponse(false, $e->getMessage() ?: 'Failed to update purchase');
    }
}

if ($_SERVER['REQUEST_METHOD'] === 'GET' && !isset($_GET['purchaseId'])) {
    $purchases = $obj->rawSql("
        SELECT
            p.*,
            pro.product_name,
            s.supplier_name,
            pm.batch_id,
            _createuser.FullName
        FROM purchases p
        LEFT JOIN suppliers s ON p.supplier_id = s.supplier_id
        LEFT JOIN products pro ON p.product_id = pro.product_id
        LEFT JOIN (
            SELECT purchase_id, MIN(batch_id) AS batch_id
            FROM product_model
            WHERE batch_id IS NOT NULL
              AND batch_id <> ''
            GROUP BY purchase_id
        ) pm ON p.purchase_id = pm.purchase_id
        LEFT JOIN _createuser ON p.created_by = _createuser.UserId
        WHERE p.deleted_at IS NULL
        ORDER BY p.purchase_id DESC
    ");

    $i = 1;
    foreach ($purchases as &$row) {
        $row['sl'] = $i++;
    }

    echo json_encode($purchases);
    exit;
} elseif ($_SERVER['REQUEST_METHOD'] === 'GET' && isset($_GET['purchaseId'])) {
    $purchaseId = (int)($_GET['purchaseId'] ?? 0);
    $mode = trim($_GET['mode'] ?? '');

    if ($purchaseId <= 0) {
        purchaseResponse(false, 'Invalid purchase');
    }

    if ($mode === 'edit') {
        $models = $obj->rawSql("
            SELECT *
            FROM product_model
            WHERE purchase_id = '$purchaseId'
              AND deleted_at IS NULL
            ORDER BY id ASC
        ");
    } else {
        $models = $obj->rawSql("
            SELECT *
            FROM product_model
            WHERE purchase_id = '$purchaseId'
              AND returned = 0
              AND sold = 0
              AND deleted_at IS NULL
            ORDER BY id ASC
        ");
    }

    $i = 1;
    foreach ($models as &$row) {
        $row['sl'] = $i++;
    }

    echo json_encode(['success' => true, 'models' => $models]);
    exit;
}
