<?php
include("../../../include/startup.php");

header('Content-Type: application/json');
error_reporting(E_ALL);
ini_set('display_errors', 0);   // Off in production; errors go to JSON response

$action = isset($_REQUEST['action']) ? trim($_REQUEST['action']) : '';

// ============================================================
// HELPER: slugify a material name  (mirrors JS slugKey)
//   "ABS/PC" → "abs_pc"   "PP" → "pp"   "HDPE" → "hdpe"
// ============================================================
function slugKey($name) {
    $s = strtolower(preg_replace('/[^a-z0-9]+/i', '_', trim($name)));
    return trim($s, '_');
}

// ============================================================
// HELPER: get all scrap items from scrap_items
// Returns [{id, scrap_item, slug}, ...]
// ============================================================
function getScrapItems($conn) {
    $result = mysqli_query($conn,
        "SELECT id, scrap_item, description
         FROM scrap_items
         ORDER BY id ASC"
    );
    if (!$result) return [];
    $items = [];
    while ($row = mysqli_fetch_assoc($result)) {
        $row['slug'] = slugKey($row['scrap_item']);
        $items[] = $row;
    }
    return $items;
}

// ============================================================
// HELPER: get latest price per material slug
// Returns {slug: price_per_kg}
// ============================================================
function getScrapPrices($conn) {
    $result = mysqli_query($conn,
        "SELECT p1.material_slug, p1.price_per_kg
         FROM scrap_prices p1
         INNER JOIN (
             SELECT material_slug, MAX(id) AS max_id
             FROM scrap_prices
             GROUP BY material_slug
         ) p2 ON p1.material_slug = p2.material_slug
              AND p1.id = p2.max_id"
    );
    $prices = [];
    if ($result) {
        while ($row = mysqli_fetch_assoc($result)) {
            $prices[$row['material_slug']] = floatval($row['price_per_kg']);
        }
    }
    return $prices;
}

// ============================================================
// HELPER: calculate total value of a collection row
// $weights = ['pp' => 100, 'abs' => 200, ...]  (slug => kg)
// ============================================================
function calcScrapValue($weights, $prices) {
    $total = 0;
    foreach ($weights as $slug => $kg) {
        $total += floatval($kg) * floatval($prices[$slug] ?? 0);
    }
    return $total;
}

// ============================================================
// ACTION: get_scrap_collections
// POST — returns all collections with dynamic weights
// ============================================================
if ($action === 'get_scrap_collections') {

    $items  = getScrapItems($conn);
    $prices = getScrapPrices($conn);

    if (empty($items)) {
        echo json_encode([
            'status'  => 'success',
            'data'    => [],
            'totals'  => ['total_kg' => 0, 'total_value' => 0],
            'prices'  => $prices,
            'items'   => [],
            'count'   => 0
        ]);
        exit;
    }

    // Build WHERE clause
    $where  = "1=1";
    if (!empty($_POST['from'])) {
        $from   = mysqli_real_escape_string($conn, $_POST['from']);
        $where .= " AND sc.collection_date >= '$from'";
    }
    if (!empty($_POST['to'])) {
        $to     = mysqli_real_escape_string($conn, $_POST['to']);
        $where .= " AND sc.collection_date <= '$to'";
    }

    $result = mysqli_query($conn,
        "SELECT sc.*
         FROM scrap_collections sc
         WHERE $where
         ORDER BY sc.collection_date ASC"
    );

    if (!$result) {
        echo json_encode(['status' => 'error', 'message' => mysqli_error($conn)]);
        exit;
    }

    $rows   = [];
    $totals = ['total_kg' => 0, 'total_value' => 0];

    // Initialise per-material totals
    foreach ($items as $item) {
        $totals['weight_' . $item['slug']] = 0;
    }

    while ($row = mysqli_fetch_assoc($result)) {
        // Fetch weights for this collection
        $wResult = mysqli_query($conn,
            "SELECT material_slug, weight_kg
             FROM scrap_collection_weights
             WHERE collection_id = " . intval($row['id'])
        );

        $weights  = [];
        $totalKg  = 0;

        // Initialise all materials to 0
        foreach ($items as $item) {
            $weights[$item['slug']] = 0;
        }

        if ($wResult) {
            while ($w = mysqli_fetch_assoc($wResult)) {
                $slug = $w['material_slug'];
                $kg   = floatval($w['weight_kg']);
                $weights[$slug] = $kg;
                $totalKg += $kg;
            }
        }

        $totalVal = calcScrapValue($weights, $prices);

        // Build output row — flat structure with weight_{slug} keys
        $outRow = [
            'id'              => $row['id'],
            'collection_date' => $row['collection_date'],
            'notes'           => $row['notes'],
            'total_kg'        => round($totalKg, 2),
            'total_value'     => round($totalVal, 2)
        ];
        foreach ($weights as $slug => $kg) {
            $outRow['weight_' . $slug] = round($kg, 2);
            $totals['weight_' . $slug] += $kg;
        }

        $totals['total_kg']    += $totalKg;
        $totals['total_value'] += $totalVal;

        $rows[] = $outRow;
    }

    $totals['total_value'] = round($totals['total_value'], 2);

    echo json_encode([
        'status'  => 'success',
        'data'    => $rows,
        'totals'  => $totals,
        'prices'  => $prices,
        'items'   => $items,
        'count'   => count($rows)
    ]);
    exit;
}

// ============================================================
// ACTION: get_single_collection
// POST — returns one collection row with all weights
// ============================================================
else if ($action === 'get_single_collection') {

    $id = intval($_POST['id'] ?? $_GET['id'] ?? 0);
    if ($id <= 0) {
        echo json_encode(['status' => 'error', 'message' => 'Invalid ID']);
        exit;
    }

    $result = mysqli_query($conn,
        "SELECT * FROM scrap_collections WHERE id = $id LIMIT 1"
    );

    if (!$result || mysqli_num_rows($result) === 0) {
        echo json_encode(['status' => 'error', 'message' => 'Record not found']);
        exit;
    }

    $row   = mysqli_fetch_assoc($result);
    $items = getScrapItems($conn);

    // Fetch weights
    $wResult = mysqli_query($conn,
        "SELECT material_slug, weight_kg
         FROM scrap_collection_weights
         WHERE collection_id = $id"
    );

    // Initialise all materials to 0
    foreach ($items as $item) {
        $row['weight_' . $item['slug']] = 0;
    }

    if ($wResult) {
        while ($w = mysqli_fetch_assoc($wResult)) {
            $row['weight_' . $w['material_slug']] = floatval($w['weight_kg']);
        }
    }

    echo json_encode(['status' => 'success', 'data' => $row]);
    exit;
}

// ============================================================
// ACTION: save_scrap_collection  (INSERT)
// ============================================================
else if ($action === 'save_scrap_collection') {

    $collection_date = mysqli_real_escape_string($conn, $_POST['collection_date'] ?? '');
    if (empty($collection_date)) {
        echo json_encode(['status' => 'error', 'message' => 'Collection date is required.']);
        exit;
    }
    if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $collection_date)) {
        echo json_encode(['status' => 'error', 'message' => 'Invalid date format (YYYY-MM-DD).']);
        exit;
    }

    $notes = mysqli_real_escape_string($conn, $_POST['notes'] ?? '');
    $items = getScrapItems($conn);

    // Insert header row
    $sql = "INSERT INTO scrap_collections (collection_date, notes, created_at)
            VALUES ('$collection_date', '$notes', NOW())";

    if (!mysqli_query($conn, $sql)) {
        echo json_encode(['status' => 'error', 'message' => mysqli_error($conn)]);
        exit;
    }

    $newId = mysqli_insert_id($conn);

    // Insert weight rows
    foreach ($items as $item) {
        $key = 'weight_' . $item['slug'];
        $kg  = floatval($_POST[$key] ?? 0);
        if ($kg > 0) {
            $slug = mysqli_real_escape_string($conn, $item['slug']);
            mysqli_query($conn,
                "INSERT INTO scrap_collection_weights (collection_id, material_slug, weight_kg)
                 VALUES ($newId, '$slug', $kg)
                 ON DUPLICATE KEY UPDATE weight_kg = $kg"
            );
        }
    }

    echo json_encode([
        'status'  => 'success',
        'message' => 'Collection saved successfully.',
        'id'      => $newId
    ]);
    exit;
}

// ============================================================
// ACTION: update_scrap_collection  (UPDATE)
// ============================================================
else if ($action === 'update_scrap_collection') {

    $id = intval($_POST['id'] ?? 0);
    if ($id <= 0) {
        echo json_encode(['status' => 'error', 'message' => 'Invalid ID.']);
        exit;
    }

    $collection_date = mysqli_real_escape_string($conn, $_POST['collection_date'] ?? '');
    if (empty($collection_date)) {
        echo json_encode(['status' => 'error', 'message' => 'Collection date is required.']);
        exit;
    }

    $notes = mysqli_real_escape_string($conn, $_POST['notes'] ?? '');
    $items = getScrapItems($conn);

    // Update header row
    $sql = "UPDATE scrap_collections
            SET collection_date = '$collection_date',
                notes           = '$notes',
                updated_at      = NOW()
            WHERE id = $id";

    if (!mysqli_query($conn, $sql)) {
        echo json_encode(['status' => 'error', 'message' => mysqli_error($conn)]);
        exit;
    }

    // Delete existing weights and re-insert (clean approach)
    mysqli_query($conn, "DELETE FROM scrap_collection_weights WHERE collection_id = $id");

    foreach ($items as $item) {
        $key = 'weight_' . $item['slug'];
        $kg  = floatval($_POST[$key] ?? 0);
        // Insert all materials (including 0 so they show in table)
        $slug = mysqli_real_escape_string($conn, $item['slug']);
        mysqli_query($conn,
            "INSERT INTO scrap_collection_weights (collection_id, material_slug, weight_kg)
             VALUES ($id, '$slug', $kg)"
        );
    }

    echo json_encode(['status' => 'success', 'message' => 'Collection updated successfully.']);
    exit;
}

// ============================================================
// ACTION: delete_scrap_collection
// ============================================================
else if ($action === 'delete_scrap_collection') {

    $id = intval($_POST['id'] ?? 0);
    if ($id <= 0) {
        echo json_encode(['status' => 'error', 'message' => 'Invalid ID.']);
        exit;
    }

    // Weights deleted via ON DELETE CASCADE (see table structure below)
    // or explicitly:
    mysqli_query($conn, "DELETE FROM scrap_collection_weights WHERE collection_id = $id");

    if (mysqli_query($conn, "DELETE FROM scrap_collections WHERE id = $id")) {
        echo json_encode(['status' => 'success', 'message' => 'Collection deleted.']);
    } else {
        echo json_encode(['status' => 'error', 'message' => mysqli_error($conn)]);
    }
    exit;
}

// ============================================================
// ACTION: get_scrap_prices
// Returns latest price per material + full history + items
// ============================================================
else if ($action === 'get_scrap_prices') {

    $items = getScrapItems($conn);

    // Latest price per material_slug (using MAX id for tie-breaking)
    $result = mysqli_query($conn,
        "SELECT p1.*
         FROM scrap_prices p1
         INNER JOIN (
             SELECT material_slug, MAX(id) AS max_id
             FROM scrap_prices
             GROUP BY material_slug
         ) p2 ON p1.id = p2.max_id
         ORDER BY p1.material_slug ASC"
    );

    $latestRows = [];
    if ($result) {
        while ($row = mysqli_fetch_assoc($result)) {
            $latestRows[] = $row;
        }
    }

    // Full history (most recent first, grouped by snapshot)
    $histResult = mysqli_query($conn,
        "SELECT * FROM scrap_prices ORDER BY snapshot_id DESC, material_slug ASC LIMIT 200"
    );
    $history = [];
    if ($histResult) {
        while ($row = mysqli_fetch_assoc($histResult)) {
            $history[] = $row;
        }
    }

    echo json_encode([
        'status'  => 'success',
        'data'    => $latestRows,
        'history' => $history,
        'items'   => $items
    ]);
    exit;
}

// ============================================================
// ACTION: update_scrap_prices
// Inserts a new snapshot for ALL materials in one batch
// POST keys: slug_{material_slug} = price
// ============================================================
else if ($action === 'update_scrap_prices') {

    $items    = getScrapItems($conn);
    $inserted = 0;

    // Generate a single snapshot_id for this batch so history
    // groups all materials saved at the same time together
    $snapshot_id = time();

    foreach ($items as $item) {
        $postKey = 'slug_' . $item['slug'];
        if (isset($_POST[$postKey]) && is_numeric($_POST[$postKey])) {
            $price = round(floatval($_POST[$postKey]), 4);
            $slug  = mysqli_real_escape_string($conn, $item['slug']);

            if (mysqli_query($conn,
                "INSERT INTO scrap_prices (material_slug, price_per_kg, snapshot_id, updated_at)
                 VALUES ('$slug', $price, $snapshot_id, NOW())"
            )) {
                $inserted++;
            }
        }
    }

    echo json_encode([
        'status'      => 'success',
        'message'     => 'Prices saved successfully.',
        'inserted'    => $inserted,
        'snapshot_id' => $snapshot_id
    ]);
    exit;
}

// ============================================================
// Unknown action fallback
// ============================================================
else {
    echo json_encode([
        'status'  => 'error',
        'message' => 'Unknown action: ' . htmlspecialchars($action)
    ]);
    exit;
}

/*
============================================================
 SQL TABLE STRUCTURES
 Run these once to set up the new normalised schema.
 If upgrading from the old hardcoded columns, see migration
 note at the bottom.
============================================================

-- 1. Scrap items (your existing scrap_items table)
--    No changes needed — already has: id, scrap_item, description

-- 2. Collections header (replaces old scrap_collections with hardcoded columns)
CREATE TABLE IF NOT EXISTS `scrap_collections` (
    `id`              INT(11)      NOT NULL AUTO_INCREMENT,
    `collection_date` DATE         NOT NULL,
    `notes`           VARCHAR(500) DEFAULT NULL,
    `created_at`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      DATETIME     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_date` (`collection_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Per-material weights (one row per material per collection)
CREATE TABLE IF NOT EXISTS `scrap_collection_weights` (
    `id`              INT(11)        NOT NULL AUTO_INCREMENT,
    `collection_id`   INT(11)        NOT NULL,
    `material_slug`   VARCHAR(100)   NOT NULL,
    `weight_kg`       DECIMAL(10,3)  NOT NULL DEFAULT 0.000,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_coll_mat` (`collection_id`, `material_slug`),
    KEY `idx_collection` (`collection_id`),
    CONSTRAINT `fk_scw_coll`
        FOREIGN KEY (`collection_id`)
        REFERENCES `scrap_collections` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Scrap prices (replaces old scrap_prices with hardcoded material_key)
--    Uses snapshot_id (unix timestamp) to group a batch of prices saved together
CREATE TABLE IF NOT EXISTS `scrap_prices` (
    `id`             INT(11)        NOT NULL AUTO_INCREMENT,
    `material_slug`  VARCHAR(100)   NOT NULL,
    `price_per_kg`   DECIMAL(10,4)  NOT NULL DEFAULT 0.0000,
    `snapshot_id`    BIGINT(20)     NOT NULL DEFAULT 0,
    `updated_at`     DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_slug`     (`material_slug`),
    KEY `idx_snapshot` (`snapshot_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Seed initial prices for your existing 6 materials
--    (Run once after creating the table — adjust prices as needed)
INSERT INTO `scrap_prices` (`material_slug`, `price_per_kg`, `snapshot_id`, `updated_at`) VALUES
('pp',     0.1500, UNIX_TIMESTAMP(), NOW()),
('abs',    0.2000, UNIX_TIMESTAMP(), NOW()),
('hdpe',   0.1200, UNIX_TIMESTAMP(), NOW()),
('aca',    0.1800, UNIX_TIMESTAMP(), NOW()),
('pc',     0.2500, UNIX_TIMESTAMP(), NOW()),
('abs_pc', 0.2200, UNIX_TIMESTAMP(), NOW());

-- ============================================================
-- MIGRATION from old hardcoded-column schema
-- Only needed if you had data in the old scrap_collections table
-- with columns: pp, abs, hdpe, aca, pc, abs_pc
-- ============================================================

-- Step 1: Create the new tables above (scrap_collections keeps id, date, notes)
-- Step 2: Run this to migrate existing weight data:

INSERT INTO scrap_collection_weights (collection_id, material_slug, weight_kg)
SELECT id, 'pp',     pp     FROM scrap_collections_old WHERE pp     > 0
UNION ALL
SELECT id, 'abs',    abs    FROM scrap_collections_old WHERE abs    > 0
UNION ALL
SELECT id, 'hdpe',   hdpe   FROM scrap_collections_old WHERE hdpe   > 0
UNION ALL
SELECT id, 'aca',    aca    FROM scrap_collections_old WHERE aca    > 0
UNION ALL
SELECT id, 'pc',     pc     FROM scrap_collections_old WHERE pc     > 0
UNION ALL
SELECT id, 'abs_pc', abs_pc FROM scrap_collections_old WHERE abs_pc > 0;

-- (Rename your old table first:  RENAME TABLE scrap_collections TO scrap_collections_old;
--  then create the new scrap_collections with just id/date/notes/timestamps)
============================================================
*/
?>