<?php
session_start();

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

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

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

function normalizeSupplierReturnModels($modelIds)
{
    if (!is_array($modelIds)) {
        return [];
    }

    $normalized = [];
    foreach ($modelIds as $modelId) {
        $modelId = (int)$modelId;
        if ($modelId > 0) {
            $normalized[$modelId] = $modelId;
        }
    }

    return array_values($normalized);
}

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

function getSupplierReturnBatchId(Model $obj, array $purchase)
{
    $purchaseId = (int)$purchase['purchase_id'];
    $activeBatch = $obj->rawSqlSingle("
        SELECT batch_id
        FROM product_model
        WHERE purchase_id = '$purchaseId'
          AND batch_id IS NOT NULL
          AND batch_id <> ''
        ORDER BY deleted_at IS NULL DESC, id ASC
        LIMIT 1
    ");

    if (!empty($activeBatch['batch_id'])) {
        return $activeBatch['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 getSupplierReturnStats(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
        FROM product_model
        WHERE purchase_id = '$purchaseId'
    ");

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

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

    $stats = getSupplierReturnStats($obj, (int)$purchase['purchase_id']);
    if ($stats['active_count'] <= 0) {
        return ['success' => false, 'status' => 'Purchase must have at least one model'];
    }

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

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

    $stock = $obj->rawSqlSingle("SELECT * FROM stock WHERE batch_id = '$batchId' AND deleted_at IS NULL ORDER BY stock_id ASC LIMIT 1");
    if (!$stock) {
        return ['success' => false, 'status' => 'Stock not found for this purchase batch'];
    }

    $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'],
    ]);

    return ['success' => true, 'batch_id' => $batchId];
}

$wherecond = '1=1';

if (isset($_GET['supplier']) && $_GET['supplier'] !== '') {
    $supplier = (int)$_GET['supplier'];
    $wherecond .= " AND supplier_return.supplier_id = $supplier";
}

if (isset($_GET['product']) && $_GET['product'] !== '') {
    $product = (int)$_GET['product'];
    $wherecond .= " AND supplier_return.product_id = $product";
}

if (isset($_GET['datefrom']) && $_GET['datefrom'] !== '') {
    $dateFrom = date('Y-m-d', strtotime($_GET['datefrom']));
    $wherecond .= " AND DATE(supplier_return.return_date) >= '$dateFrom'";
}

if (isset($_GET['dateto']) && $_GET['dateto'] !== '') {
    $dateTo = date('Y-m-d', strtotime($_GET['dateto']));
    $wherecond .= " AND DATE(supplier_return.return_date) <= '$dateTo'";
}

$order_column = $_GET['order'][0]['column'] ?? '';
$order_dir = $_GET['order'][0]['dir'] ?? '';
$columns = [
    'return_id',
    'supplier_name',
    'product_name',
    'quantity_returned',
    'model_id',
    'return_date',
    'return_reason',
    'FullName',
];

if (isset($order_column) && isset($columns[$order_column])) {
    if ($order_column == '1' || $order_column == '5') {
        $order_by = 'supplier_return.supplier_id ' . $order_dir;
    } elseif ($order_column == '2') {
        $order_by = 'supplier_return.product_id ' . $order_dir;
    } elseif ($order_column == '7') {
        $order_by = 'supplier_return.created_by ' . $order_dir;
    } else {
        $order_by = 'supplier_return.' . $columns[$order_column] . ' ' . $order_dir;
    }
} else {
    $order_by = 'supplier_return.return_id DESC';
}

$start = isset($_GET['start']) ? (int)$_GET['start'] : 0;
$length = isset($_GET['length']) ? (int)$_GET['length'] : 10;
$search = $_GET['search']['value'] ?? '';

if ($search !== '') {
    $wherecond .= " AND (
        suppliers.supplier_name LIKE '%$search%' OR
        products.product_name LIKE '%$search%' OR
        supplier_return.quantity_returned LIKE '%$search%' OR
        supplier_return.return_date LIKE '%$search%' OR
        supplier_return.return_reason LIKE '%$search%' OR
        _createuser.FullName LIKE '%$search%'
    )";
}

if ($_SERVER['REQUEST_METHOD'] === 'GET') {
    $returnData = $obj->rawSql("
        SELECT
            supplier_return.*,
            products.product_name,
            suppliers.supplier_name,
            _createuser.FullName
        FROM supplier_return
        LEFT JOIN products ON supplier_return.product_id = products.product_id
        LEFT JOIN suppliers ON supplier_return.supplier_id = suppliers.supplier_id
        LEFT JOIN _createuser ON supplier_return.created_by = _createuser.UserId
        WHERE supplier_return.deleted_at IS NULL AND $wherecond
        ORDER BY $order_by
        LIMIT $length OFFSET $start
    ");

    $i = $start + 1;
    foreach ($returnData as &$row) {
        $row['sl'] = $i++;
        $model_ids = json_decode($row['model_id'], true);
        if (!is_array($model_ids)) {
            $model_ids = [];
        }

        $model_numbers = [];
        foreach ($model_ids as $key => $model_id) {
            $model = $obj->rawSqlSingle("SELECT * FROM product_model WHERE id = '$model_id' AND deleted_at IS NULL");
            if ($model) {
                $model_numbers[$key] = [
                    'model_no' => $model['model_no'],
                    'serial_no' => $model['serial_no'],
                    'expire_date' => $model['expire_date'],
                ];
            }
        }

        $row['model_id'] = $model_numbers;
    }

    $totalData = $obj->rawSql("
        SELECT COUNT(*) AS total
        FROM supplier_return
        WHERE deleted_at IS NULL
    ")[0]['total'] ?? 0;

    $totalFiltered = $obj->rawSql("
        SELECT COUNT(*) AS total
        FROM supplier_return
        LEFT JOIN products ON supplier_return.product_id = products.product_id
        LEFT JOIN suppliers ON supplier_return.supplier_id = suppliers.supplier_id
        LEFT JOIN _createuser ON supplier_return.created_by = _createuser.UserId
        WHERE supplier_return.deleted_at IS NULL AND $wherecond
    ")[0]['total'] ?? 0;

    echo json_encode([
        'draw' => isset($_GET['draw']) ? (int)$_GET['draw'] : 1,
        'recordsTotal' => (int)$totalData,
        'recordsFiltered' => (int)$totalFiltered,
        'data' => $returnData,
    ]);
    exit;
}

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['id'])) {
    $id = (int)($_POST['id'] ?? 0);
    $product = (int)($_POST['product'] ?? 0);
    $quantity = (int)($_POST['quantity'] ?? 0);
    $supplier = (int)($_POST['supplier'] ?? 0);
    $returnDate = trim($_POST['return_date'] ?? '');
    $reason = trim($_POST['reason'] ?? '');
    $models = normalizeSupplierReturnModels($_POST['models'] ?? []);

    if ($id <= 0 || $product <= 0 || $supplier <= 0 || $quantity <= 0 || $returnDate === '') {
        supplierReturnResponse(false, 'Please fill all required return fields');
    }

    if (count($models) !== $quantity) {
        supplierReturnResponse(false, 'Selected model count must match return quantity');
    }

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

    if ((int)$purchase['product_id'] !== $product || (int)$purchase['supplier_id'] !== $supplier) {
        supplierReturnResponse(false, 'Return request does not match the original purchase');
    }

    $modelIdList = implode(',', $models);
    $validModels = $obj->rawSql("
        SELECT id
        FROM product_model
        WHERE id IN ($modelIdList)
          AND purchase_id = '$id'
          AND sold = 0
          AND returned = 0
          AND deleted_at IS NULL
    ");

    if (count($validModels) !== count($models)) {
        supplierReturnResponse(false, 'One or more selected models are unavailable for supplier return');
    }

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

        $insertStmt = $db->prepare("
            INSERT INTO supplier_return (product_id, purchase_id, supplier_id, quantity_returned, model_id, return_reason, return_date, created_by, created_at, deleted_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");
        $insertStmt->execute([
            $product,
            $id,
            $supplier,
            $quantity,
            json_encode($models),
            $reason,
            $returnDate,
            $userid,
            $now,
            null,
        ]);

        $modelStmt = $db->prepare("
            UPDATE product_model
            SET returned = 1, updated_by = ?, updated_at = ?
            WHERE purchase_id = ? AND id = ?
        ");
        foreach ($models as $modelId) {
            $modelStmt->execute([$userid, $now, $id, $modelId]);
        }

        $purchaseStmt = $db->prepare("
            UPDATE purchases
            SET return_qty = ?, updated_at = ?, updated_by = ?
            WHERE purchase_id = ?
        ");
        $purchaseStmt->execute([
            ((int)$purchase['return_qty']) + $quantity,
            $now,
            $userid,
            $id,
        ]);

        $sync = syncSupplierReturnInventory($db, $obj, $purchase, $userid);
        if (empty($sync['success'])) {
            throw new RuntimeException($sync['status'] ?? 'Failed to sync inventory');
        }

        $db->commit();
        supplierReturnResponse(true, 'Added successfully', ['batch_id' => $sync['batch_id']]);
    } catch (Throwable $e) {
        if ($db->inTransaction()) {
            $db->rollBack();
        }
        supplierReturnResponse(false, $e->getMessage() ?: 'Failed to add supplier return');
    }
}
