<?php
require_once __DIR__ . '/../config.php';
require_once __DIR__ . '/../includes/auth.php';
require_once __DIR__ . '/../includes/db.php';
require_login();
header('Content-Type: application/json');

$mysqli = db();

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    json_response(['message' => 'Method not allowed'], 405);
}

if (!validate_csrf($_POST['csrf'] ?? '')) {
    json_response(['message' => 'Invalid CSRF'], 400);
}

if (!isset($_FILES['csv']) || !is_uploaded_file($_FILES['csv']['tmp_name'])) {
    json_response(['message' => 'Missing CSV file'], 400);
}

function norm_key($s) {
    $s = strtolower(trim((string)$s));
    $s = preg_replace('/[^a-z0-9]+/', '', $s);
    return $s;
}

function parse_inches($v) {
    $v = trim((string)$v);
    if ($v === '') return null;
    // allow values like 12, 12.5, "12\"", "12 in"
    $v = str_ireplace(['inches', 'inch', 'in', '"'], '', $v);
    $v = trim($v);
    if ($v === '') return null;
    if (!is_numeric($v)) return null;
    $f = (float)$v;
    if (!is_finite($f) || $f <= 0) return null;
    return $f;
}

function norm_color($v) {
    $v = trim((string)$v);
    $v = preg_replace('/\s+/', ' ', $v);
    return $v;
}

$tolerance_in = 1.0;
$tolerance_mm = $tolerance_in * 25.4;

$fh = fopen($_FILES['csv']['tmp_name'], 'r');
if (!$fh) json_response(['message' => 'Failed to read uploaded file'], 400);

$header = fgetcsv($fh);
if (!$header || !is_array($header)) {
    fclose($fh);
    json_response(['message' => 'CSV appears empty'], 400);
}

$cols = [];
foreach ($header as $idx => $h) {
    $cols[norm_key($h)] = $idx;
}

// Acceptable header names
$wIdx = null;
$hIdx = null;
$cIdx = null;
foreach (['width', 'w'] as $k) { if (isset($cols[$k])) { $wIdx = $cols[$k]; break; } }
foreach (['height', 'h'] as $k) { if (isset($cols[$k])) { $hIdx = $cols[$k]; break; } }
foreach (['slabcolor', 'slab_colour', 'slab_color', 'color', 'colour'] as $k) { if (isset($cols[norm_key($k)])) { $cIdx = $cols[norm_key($k)]; break; } }

if ($wIdx === null || $hIdx === null) {
    fclose($fh);
    json_response(['message' => 'CSV headers must include Width and Height (or w/h).'], 400);
}

$rowsOut = [];
$summary = [
    'rows' => 0,
    'raw_matches' => 0,
    'final_matches' => 0,
    'cut_matches' => 0,
];

$rowNo = 1; // data row index excluding header
$colorIdCache = [];
$colorLookupStmt = $mysqli->prepare('SELECT id FROM colors WHERE LOWER(TRIM(name)) = LOWER(TRIM(?)) LIMIT 1');
while (($row = fgetcsv($fh)) !== false) {
    $rowNo++;
    $wIn = parse_inches($row[$wIdx] ?? '');
    $hIn = parse_inches($row[$hIdx] ?? '');
    if ($wIn === null || $hIn === null) continue;

    $slabColor = '';
    $slabColorId = 0;
    if ($cIdx !== null) {
        $slabColor = norm_color($row[$cIdx] ?? '');
        if ($slabColor !== '') {
            $key = strtolower($slabColor);
            if (array_key_exists($key, $colorIdCache)) {
                $slabColorId = (int)($colorIdCache[$key] ?? 0);
            } else {
                $found = 0;
                if ($colorLookupStmt) {
                    $colorLookupStmt->bind_param('s', $slabColor);
                    $colorLookupStmt->execute();
                    $cres = $colorLookupStmt->get_result();
                    if ($cres) {
                        $crow = $cres->fetch_assoc();
                        if ($crow && isset($crow['id'])) $found = (int)$crow['id'];
                    }
                }
                $colorIdCache[$key] = $found;
                $slabColorId = $found;
            }
        }
    }

    $summary['rows']++;

    $wMm = $wIn * 25.4;
    $hMm = $hIn * 25.4;

    // RAW MATERIAL matches
    $rawMatches = [];
    {
        $minA1 = min($wIn, $hIn) - $tolerance_in;
        $maxA1 = min($wIn, $hIn) + $tolerance_in;
        $minB1 = max($wIn, $hIn) - $tolerance_in;
        $maxB1 = max($wIn, $hIn) + $tolerance_in;

        // match either orientation
        $sql = "SELECT r.id, r.slab_no, r.height, r.length, r.sq_ft, r.status, r.po_id,
                       p.order_no AS po_no
                FROM raw_material_products r
                LEFT JOIN purchase_orders p ON p.id=r.po_id
                WHERE r.status='received'
                  AND (? = 0 OR r.color_id = ?)
                  AND ((r.height BETWEEN ? AND ? AND r.length BETWEEN ? AND ?) OR (r.height BETWEEN ? AND ? AND r.length BETWEEN ? AND ?))
                ORDER BY r.id DESC
                LIMIT 50";
        $stmt = $mysqli->prepare($sql);
        if ($stmt) {
            $stmt->bind_param('iidddddddd',
                $slabColorId, $slabColorId,
                $minA1, $maxA1, $minB1, $maxB1,
                $minB1, $maxB1, $minA1, $maxA1
            );
            $stmt->execute();
            $res = $stmt->get_result();
            while ($r = $res->fetch_assoc()) { $rawMatches[] = $r; }
        }
    }

    // FINISHED PRODUCT matches
    $finalMatches = [];
    {
        $minA1 = min($wIn, $hIn) - $tolerance_in;
        $maxA1 = min($wIn, $hIn) + $tolerance_in;
        $minB1 = max($wIn, $hIn) - $tolerance_in;
        $maxB1 = max($wIn, $hIn) + $tolerance_in;

        $sql = "SELECT f.id, f.final_height, f.final_length, f.sale_status, f.branch_id,
                       r.slab_no, p.order_no, fn.name AS finish_name
                FROM final_products f
                LEFT JOIN raw_material_products r ON r.id=f.rmp_id
                LEFT JOIN purchase_orders p ON p.id=f.po_id
                LEFT JOIN finishes fn ON fn.id=f.finish_id
                WHERE f.sale_status='available'
                  AND (? = 0 OR f.color_id = ?)
                  AND ((f.final_height BETWEEN ? AND ? AND f.final_length BETWEEN ? AND ?) OR (f.final_height BETWEEN ? AND ? AND f.final_length BETWEEN ? AND ?))
                ORDER BY f.id DESC
                LIMIT 50";
        $stmt = $mysqli->prepare($sql);
        if ($stmt) {
            $stmt->bind_param('iidddddddd',
                $slabColorId, $slabColorId,
                $minA1, $maxA1, $minB1, $maxB1,
                $minB1, $maxB1, $minA1, $maxA1
            );
            $stmt->execute();
            $res = $stmt->get_result();
            while ($r = $res->fetch_assoc()) { $finalMatches[] = $r; }
        }
    }

    // CUT PIECE matches (length_mm/width_mm stored in mm)
    $cutMatches = [];
    {
        $minA1 = min($wMm, $hMm) - $tolerance_mm;
        $maxA1 = min($wMm, $hMm) + $tolerance_mm;
        $minB1 = max($wMm, $hMm) - $tolerance_mm;
        $maxB1 = max($wMm, $hMm) + $tolerance_mm;

        $sql = "SELECT cp.id, cp.job_id, cp.piece_no, cp.length_mm, cp.width_mm, cp.area_sq_ft,
                       COALESCE(rmp_fp.slab_no, rmp_raw.slab_no, rmp_fp2.slab_no, rmp_raw2.slab_no) AS slab_no,
                       COALESCE(po_fp.order_no, po_raw.order_no, po_fp2.order_no, po_raw2.order_no) AS po_no
                FROM cut_pieces cp
                LEFT JOIN cutting_jobs cj ON cj.id = cp.job_id
                LEFT JOIN final_products fp ON (cj.source_type='finished' AND fp.id=cj.source_id)
                LEFT JOIN raw_material_products rmp_fp ON rmp_fp.id = fp.rmp_id
                LEFT JOIN raw_material_products rmp_raw ON (cj.source_type='raw' AND rmp_raw.id=cj.source_id)
                LEFT JOIN cut_pieces cp2 ON (cj.source_type='cut_piece' AND cp2.id=cj.source_id)
                LEFT JOIN cutting_jobs pj ON pj.id = cp2.job_id
                LEFT JOIN final_products fp2 ON (pj.source_type='finished' AND fp2.id=pj.source_id)
                LEFT JOIN raw_material_products rmp_fp2 ON rmp_fp2.id = fp2.rmp_id
                LEFT JOIN raw_material_products rmp_raw2 ON (pj.source_type='raw' AND rmp_raw2.id=pj.source_id)
                LEFT JOIN purchase_orders po_fp ON po_fp.id = rmp_fp.po_id
                LEFT JOIN purchase_orders po_raw ON po_raw.id = rmp_raw.po_id
                LEFT JOIN purchase_orders po_fp2 ON po_fp2.id = rmp_fp2.po_id
                LEFT JOIN purchase_orders po_raw2 ON po_raw2.id = rmp_raw2.po_id
                WHERE cp.is_available=1
                  AND (? = 0 OR COALESCE(rmp_fp.color_id, rmp_raw.color_id, rmp_fp2.color_id, rmp_raw2.color_id) = ?)
                  AND ((cp.length_mm BETWEEN ? AND ? AND cp.width_mm BETWEEN ? AND ?) OR (cp.length_mm BETWEEN ? AND ? AND cp.width_mm BETWEEN ? AND ?))
                ORDER BY cp.id DESC
                LIMIT 50";
        $stmt = $mysqli->prepare($sql);
        if ($stmt) {
            $stmt->bind_param('iidddddddd',
                $slabColorId, $slabColorId,
                $minA1, $maxA1, $minB1, $maxB1,
                $minB1, $maxB1, $minA1, $maxA1
            );
            $stmt->execute();
            $res = $stmt->get_result();
            while ($r = $res->fetch_assoc()) { $cutMatches[] = $r; }
        }
    }

    $summary['raw_matches'] += count($rawMatches);
    $summary['final_matches'] += count($finalMatches);
    $summary['cut_matches'] += count($cutMatches);

    $rowsOut[] = [
        'row_no' => $rowNo,
        'width_in' => $wIn,
        'height_in' => $hIn,
        'slab_color' => $slabColor,
        'raw_matches' => $rawMatches,
        'final_matches' => $finalMatches,
        'cut_matches' => $cutMatches,
    ];

    // Hard cap to prevent huge server load from massive CSVs
    if ($summary['rows'] >= 200) break;
}

fclose($fh);

json_response([
    'summary' => $summary,
    'rows' => $rowsOut,
]);
