<?php
session_start();

include("../include/config.php");
include("../include/functions.php"); 

// Set timezone to Central Time (CT)
date_default_timezone_set("America/Chicago");

// prevent timeout for large CSV
//ini_set('max_execution_time', 0);
//set_time_limit(-1);
$conn = $GLOBALS['conn'];
if (isset($_POST['submitForm']) && $_POST['submitForm'] == 'yes') {

    $store_code = trim($_POST['store_code']);
	
    if (empty($store_code)) {
        $_SESSION['sess_msg'] = "Store code is required!";
        header("Location: uploadInstacartFile.php");
        exit;
    }

    if (isset($_FILES['csv_file']) && $_FILES['csv_file']['error'] == 0) {

        $fileName = $_FILES['csv_file']['name'];
        $fileTmp  = $_FILES['csv_file']['tmp_name'];
        $ext      = strtolower(pathinfo($fileName, PATHINFO_EXTENSION));

        // Allow only CSV
        if ($ext != 'csv') {
            $_SESSION['sess_msg'] = "Only CSV files are allowed!";
            header("Location: uploadInstacartFile.php");
            exit;
        }

        // ---------------- Parse CSV directly (do not save file) ----------------
        $dataArr = [];
        $header = [];

        if (($file = fopen($fileTmp, 'r')) !== false) {
            $header = fgetcsv($file);

            // Clean headers
            $header = array_map(function ($h) {
                $h = preg_replace('/[\x{FEFF}\x{200B}]/u', '', $h);
                return strtolower(trim($h));
            }, $header);

            while ($row = fgetcsv($file)) {
                if (count($row) != count($header)) continue;
                $dataArr[] = array_combine($header, array_map('trim', $row));
            }
            fclose($file);
        }

        $deleteCount = 0;
        $insertCount = 0;
        $invalidCount = 0;
        $invalidRows = [];
        $seen = [];

        foreach ($dataArr as $index => $row) {
            $lookup_code = $row['lookup_code'] ?? '';
            $store_identifier = $row['store_identifier'] ?? $store_code;
            $price = $row['price'] ?? '';

            // ---------------- Validation checks ----------------
            if (empty($lookup_code)) {
                $invalidCount++;
                $invalidRows[] = "Row " . ($index + 2) . ": Missing lookup_code.";
                continue;
            }
            if (empty($store_identifier)) {
                $invalidCount++;
                $invalidRows[] = "Row " . ($index + 2) . ": Missing store_id.";
                continue;
            }
            if (!is_numeric($price) || (float)$price <= 0) {
                $invalidCount++;
                $invalidRows[] = "Row " . ($index + 2) . ": Invalid price ($price).";
                continue;
            }



            // ---------------- DELETE existing (exact match) ----------------
            $delStmt = $conn->prepare("DELETE FROM cainsta_ff_inventory_sync WHERE store_identifier = ?");
            $delStmt->bind_param("s", $store_code);
            $delStmt->execute();
            if ($delStmt->affected_rows > 0) {
                $deleteCount++;
            }
            $delStmt->close();

            // ---------------- INSERT new record ----------------
            $brand_name            = $row['brand_name'] ?? '';
            $item_name             = $row['item_name'] ?? '';
            $cost_unit             = $row['cost_unit'] ?? '';
            $size                  = $row['size'] ?? '';
            $size_uom              = $row['size_uom'] ?? '';
            $balance_on_hand       = (is_numeric($row['balance_on_hand']) ? $row['balance_on_hand'] : 0);
            $available             = (is_numeric($row['available']) ? $row['available'] : 1);
            $par_weight            = $row['par_weight'] ?? '';
            $department            = $row['department'] ?? '';
            $aisle                 = $row['aisle'] ?? '';
            $item_details          = $row['item_details'] ?? '';
            $remote_image_URL      = $row['remote_image_url'] ?? '';
            $additional_images_url = $row['additional_images_url'] ?? '';

            $insert = $conn->prepare("INSERT INTO cainsta_ff_inventory_sync (
                lookup_code, store_identifier, price, brand_name, item_name, cost_unit, size, size_uom,
                balance_on_hand, available, par_weight, department, aisle, item_details, remote_image_URL, additional_images_url
            ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

            $insert->bind_param(
                "ssdsssssiissssss",
                $lookup_code,
                $store_identifier,
                $price,
                $brand_name,
                $item_name,
                $cost_unit,
                $size,
                $size_uom,
                $balance_on_hand,
                $available,
                $par_weight,
                $department,
                $aisle,
                $item_details,
                $remote_image_URL,
                $additional_images_url
            );

            if ($insert->execute()) {
                $insertCount++;
            }
            $insert->close();
        }

        // Final message
        $msg = "CSV uploaded successfully! Deleted: $deleteCount, Inserted: $insertCount.";
        if ($invalidCount > 0) {
            $msg .= " Skipped: $invalidCount rows.<br><br>Details:<br>" . implode("<br>", $invalidRows);
        }

        $_SESSION['sess_msg'] = $msg;
        header("Location: uploadInstacartFile.php");
        exit;
    } else {
        $_SESSION['sess_msg'] = "Please select a CSV file to upload.";
        header("Location: uploadInstacartFile.php");
        exit;
    }
}
?>





<!DOCTYPE html>
<html>

<head>
    <title>Instacart Inventory Upload</title>
    <meta charset="utf-8">
    <link href="css/admin.css" rel="stylesheet" type="text/css" />
    <script type="text/javascript">
        function validate(obj) {
            if (obj.store_code.value == '') {
                alert("Please enter store code");
                obj.store_code.focus();
                return false;
            }
        }
    </script>
</head>

<body>
    
    <table width="100%" height="100%" border="0" cellpadding="0" cellspacing="0">
        <?php include("header.php") ?> 

        <tr>
            <td align="right" class="paddRtLt70" valign="top">
                <table width="99%" border="0" cellspacing="0" cellpadding="0">
                    <tr>

                        <td align="right" valign="top">
                            <table width="100%" border="0" cellspacing="0" cellpadding="0">
                                <tr>
                                    <td align="left" valign="middle" class="headingbg bodr text14">
                                        <em><img src="images/arrow2.gif" width="21" height="21" hspace="10" align="absmiddle" /></em>Admin: DoDash
                                    </td>
                                </tr>

                                <tr>
                                    <td height="100" align="left" valign="top" bgcolor="#f3f4f6" class="bodr">
                                        <form name="frm" method="POST" enctype="multipart/form-data" action="" onsubmit="return validate(this)">
                                            <input type="hidden" name="submitForm" value="yes" />
                                            <input type="hidden" name="id" value="<?php //echo $_REQUEST['id']; ?>" />
                                            <table width="100%" cellpadding="0" cellspacing="0">
                                                <tr>
                                                    <td align="center" colspan="2" class="paddRt14 paddBot11">
                                                        <font color="#FF0000"><strong><?php echo $_SESSION['sess_msg'];
                                                                                        $_SESSION['sess_msg'] = ''; ?></strong></font>
                                                    </td>
                                                </tr>

                                                <tr>
                                                    <td align="right" class="paddBot11 paddRt14"><strong>Store Id (For ex DD56880CSTX) :</strong></td>
                                                    <td align="left" class="paddBot11">
                                                        <input type="text" name="store_code" id="store_code" size="50">
                                                    </td>
                                                </tr>
                                                <tr>
                                                    <td align="right" class="paddBot11 paddRt14"><strong>Upload FF Csv:</strong></td>
                                                    <td align="left" class="paddBot11"><input name="csv_file" type="file" /><br />
                                                        <!-- <php if (is_file("../upload_images/category/" . $result->photo)) { ?>
                                                            <img src="../upload_images/category/<?php echo  $result->photo; ?>" width="100" height="100" />
                                                        <php } ?> -->
                                                    </td>
                                                </tr>


                                                <tr>
                                                    <td align="right" class="paddRt14 paddBot11">&nbsp;</td>
                                                    <td align="left" class="paddBot11">&nbsp;</td>
                                                </tr>
                                                <tr>
                                                    <td width="18%" align="right" class="paddRt14 paddBot11">&nbsp;</td>
                                                    <td width="82%" align="left" class="paddBot11">
                                                        <input type="submit" name="submit" value="Submit" class="submit" border="0" /> &nbsp;&nbsp;
                                                    </td>
                                                </tr>
                                            </table>
                                        </form>
                                    </td>
                                </tr>

                            </table>
                        </td>
                    </tr>
                </table>
            </td>
        </tr>
        <?php include('footer.php'); ?> 
    </table>
</body>

</html>