<?php
require_once __DIR__ . '/../../../includes/auth.php';
requireLogin();
requirePermission('view_billing');

require_once __DIR__ . '/../../../config/database.php';

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    die("Invalid request.");
}

$companyId = $_SESSION['company_id'];
$periodId  = (int)($_POST['period_id'] ?? 0);

if (!$periodId) {
    die("Billing period missing.");
}

if (!isset($_FILES['file']) || $_FILES['file']['error'] !== UPLOAD_ERR_OK) {
    die("File upload failed.");
}

$file = $_FILES['file']['tmp_name'];

try {

    $pdo->beginTransaction();

    /* ===============================
       Validate Billing Period
    ================================== */
    $stmt = $pdo->prepare("
        SELECT *
        FROM billing_periods
        WHERE id = ?
        AND company_id = ?
        AND status = 'OPEN'
        LIMIT 1
    ");
    $stmt->execute([$periodId, $companyId]);
    $period = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$period) {
        throw new Exception("Invalid or locked billing period.");
    }

    /* ===============================
       READ CSV FILE
    ================================== */
    if (($handle = fopen($file, "r")) === false) {
        throw new Exception("Cannot open file.");
    }

    $header = fgetcsv($handle);

    while (($row = fgetcsv($handle)) !== false) {

        $meterNumber = trim($row[0]);
        $value       = (float)$row[1];

        if (!$meterNumber || $value === null) {
            continue;
        }

        /* ===============================
           GET METER ID FROM NUMBER
        ================================== */
        $stmt = $pdo->prepare("
            SELECT id, status
            FROM operational_meters
            WHERE meter_number = ?
            LIMIT 1
        ");
        $stmt->execute([$meterNumber]);
        $meter = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$meter) {
            continue; // skip unknown meters
        }

        if ($meter['status'] !== 'ACTIVE') {
            continue;
        }

        $meterId = (int)$meter['id'];

        /* ===============================
           FETCH PREVIOUS READING
        ================================== */
        $stmt = $pdo->prepare("
            SELECT reading_value
            FROM meter_readings
            WHERE meter_id = ?
            ORDER BY id DESC
            LIMIT 1
        ");
        $stmt->execute([$meterId]);
        $previous = $stmt->fetchColumn();

        if ($previous !== false && $value < $previous) {
            continue;
        }

        /* ===============================
           CHECK EXISTING
        ================================== */
        $stmt = $pdo->prepare("
            SELECT id
            FROM meter_readings
            WHERE meter_id = ?
            AND reading_date = ?
            LIMIT 1
        ");
        $stmt->execute([$meterId, $period['period_end']]);
        $existingId = $stmt->fetchColumn();

        if ($existingId) {

            $stmt = $pdo->prepare("
                UPDATE meter_readings
                SET reading_value = ?
                WHERE id = ?
            ");
            $stmt->execute([$value, $existingId]);

        } else {

            $stmt = $pdo->prepare("
                INSERT INTO meter_readings
                (meter_id, reading_value, reading_type, source, reading_date, created_at)
                VALUES (?, ?, 'MANUAL', 'BULK_UPLOAD', ?, NOW())
            ");
            $stmt->execute([$meterId, $value, $period['period_end']]);
        }
    }

    fclose($handle);

    $pdo->commit();

} catch (Exception $e) {
    $pdo->rollBack();
    die("Error: " . $e->getMessage());
}

/* ===============================
   FORCE UI REFRESH
================================== */
header("Location: index.php?period_id=".$periodId."&saved=1");
exit;