<?php
//////////////////////////////////// clock hours /////////////////////////////////////////////
function getProductionClockHours($conn, $machineId, $date, $tableName)
{
    $allowedTables = [
        'vac_machine_activity_log',
        'assbly_machine_activity_log',
        'assypu_machine_activity_log'
    ];

    if (!in_array($tableName, $allowedTables)) {
        return ['hours' => 0, 'minutes' => 0, 'seconds' => 0];
    }

    $windowStart = date('Y-m-d 06:00:00', strtotime($date));
    $windowEnd   = date('Y-m-d 06:00:00', strtotime($date . ' +1 day'));

    $productiveSeconds = 0;
    $runningStart = null;

    $prevSql = "
        SELECT action_type, created_at
        FROM {$tableName}
        WHERE machine_id = {$machineId}
          AND created_at < '{$windowStart}'
        ORDER BY created_at DESC
        LIMIT 1
    ";

    $prevRes = mysqli_query($conn, $prevSql);
    if ($prevRes && mysqli_num_rows($prevRes) > 0) {
        $prevRow    = mysqli_fetch_assoc($prevRes);
        $prevAction = strtolower(trim($prevRow['action_type']));
        if ($prevAction == 'start') {
            $runningStart = strtotime($windowStart);
        }
    }

    $sql = "
        SELECT action_type, created_at
        FROM {$tableName}
        WHERE machine_id = {$machineId}
          AND created_at >= '{$windowStart}'
          AND created_at < '{$windowEnd}'
        ORDER BY created_at ASC
    ";

    $result = mysqli_query($conn, $sql);
    while ($result && $row = mysqli_fetch_assoc($result)) {
        $action = strtolower(trim($row['action_type']));
        $time   = strtotime($row['created_at']);

        if ($action == 'start') {
            if ($runningStart === null) {
                $runningStart = $time;
            }
        } elseif (in_array($action, ['pause','break','breakdown','changeover','maintenance','finish'])) {
            if ($runningStart !== null) {
                $productiveSeconds += ($time - $runningStart);
                $runningStart = null;
            }
        }
    }

    if ($runningStart !== null) {
        $productiveSeconds += (strtotime($windowEnd) - $runningStart);
    }

    return [
        'hours'   => round($productiveSeconds / 3600, 2),
        'minutes' => round($productiveSeconds / 60, 2),
        'seconds' => $productiveSeconds
    ];
}

function getDepartmentProductionHours($conn, $date, $machineType, $tableName)
{
    $totalSeconds = 0;

    $machineSql = "
        SELECT id, resource_code
        FROM station_detail_table
        WHERE machine_type = {$machineType}
          AND hide = 0
    ";

    $machineResult = mysqli_query($conn, $machineSql);
    while ($machineResult && $machine = mysqli_fetch_assoc($machineResult)) {
        $machineId = (int)$machine['id'];
        $clock     = getProductionClockHours($conn, $machineId, $date, $tableName);
        $totalSeconds += $clock['seconds'];
    }

    return [
        'hours'   => round($totalSeconds / 3600, 2),
        'minutes' => round($totalSeconds / 60, 2),
        'seconds' => $totalSeconds
    ];
}

$date = date('Y-m-d', strtotime('-1 day'));

// Clock Hours
$vac      = getDepartmentProductionHours($conn, $date, 1, 'vac_machine_activity_log');
$assembly = getDepartmentProductionHours($conn, $date, 2, 'assbly_machine_activity_log');
$assypu   = getDepartmentProductionHours($conn, $date, 5, 'assypu_machine_activity_log');

echo "<h3>Production Clock Hours ({$date})</h3>";
echo "VAC Hours: {$vac['hours']}<br>";
echo "Assembly Hours: {$assembly['hours']}<br>";
echo "ASSYPU Hours: {$assypu['hours']}<br>";

$totalHours = $vac['hours'] + $assembly['hours'] + $assypu['hours'];
echo "<br><strong>Total Production Hours: " . round($totalHours, 2) . "</strong><br>";

// CNC clock hours from pre-aggregated table
$sql = "SELECT SUM(production_seconds) AS total_seconds FROM cnc_machine_clock_daily WHERE production_date = '$date'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$cnc_clock_seconds = intval($row['total_seconds']);
$cnc_clock_hours   = round($cnc_clock_seconds / 3600, 2);
echo "Seconds: " . $cnc_clock_seconds . "<br>";
echo "Hours for cnc: " . $cnc_clock_hours . "<br><br>";

// PU clock hours from pre-aggregated table
$sql = "SELECT SUM(production_seconds) AS total_seconds FROM pu_machine_clock_daily WHERE production_date = '$date'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$pu_clock_seconds = intval($row['total_seconds']);
$pu_clock_hours   = round($pu_clock_seconds / 3600, 2);
echo "Seconds: " . $pu_clock_seconds . "<br>";
echo "Hours for pu: " . $pu_clock_hours;

///////////////////////////////////////////////////   clock hours ends ////////////////////////

////////////////////////////////////////////  std hours /////////////////////////////////
echo "<br><br>";
echo "<h3>Std Hours ({$date})</h3>";

// NOTE: date_wise_quantity has been removed from *_part_count_log tables
// (vac, cnc, pu, assbly, assypu). Every OK count is now its own row with its
// own created_at timestamp, so "today's quantity" is SUM(quantity) within the
// production-day window — matching the SAME 6am-to-6am window used by
// getProductionClockHours() above, not a plain midnight-to-midnight calendar
// day. A click at 2am belongs to the PREVIOUS production day, same as clock
// hours treats it.

function getStdHoursWindow($date) {
    return [
        date('Y-m-d 06:00:00', strtotime($date)),
        date('Y-m-d 06:00:00', strtotime($date . ' +1 day')),
    ];
}

function updateDailyStdHoursComplete_cnc($conn, $date) {
    list($windowStart, $windowEnd) = getStdHoursWindow($date);

    // NOTE: cnc_part_count_log.cnc_machine_table_id stores manuf_id (per the
    // manuf_id migration), so the join must be against cmt.manuf_id, not cmt.id.
    $sql = "
        SELECT
            cpl.cnc_machine_table_id,
            SUM(cpl.quantity) AS today_qty,
            cmt.original_uph
        FROM cnc_part_count_log cpl
        LEFT JOIN cnc_machine_table cmt ON cmt.manuf_id = cpl.cnc_machine_table_id
        WHERE
            cpl.type = 'ok'
            AND cmt.original_uph > 0
            AND cpl.created_at >= '$windowStart'
            AND cpl.created_at <  '$windowEnd'
        GROUP BY cpl.cnc_machine_table_id, cmt.original_uph
    ";

    $result = mysqli_query($conn, $sql);
    if (!$result) { return false; }

    $totalStdHoursComplete = 0.0;

    while ($row = mysqli_fetch_assoc($result)) {
        $originalUph = (float)$row['original_uph'];
        $todayQty    = (float)$row['today_qty'];

        if ($todayQty > 0 && $originalUph > 0) {
            $totalStdHoursComplete += $todayQty / $originalUph;
        }
    }

    return round($totalStdHoursComplete, 2);
}

function updateDailyStdHoursComplete_vac($conn, $date) {
    list($windowStart, $windowEnd) = getStdHoursWindow($date);

    $sql = "
        SELECT vpl.work_order_id, vpl.part_id,
               SUM(vpl.quantity) AS today_qty,
               mmt.quantity AS planned_qty, mmt.total_hours
        FROM vac_part_count_log vpl
        INNER JOIN manufacture_machine_table mmt
            ON mmt.work_order_id = vpl.work_order_id AND mmt.part_id = vpl.part_id
        WHERE
            vpl.type = 'ok'
            AND mmt.quantity > 0
            AND mmt.total_hours > 0
            AND vpl.created_at >= '$windowStart'
            AND vpl.created_at <  '$windowEnd'
        GROUP BY vpl.work_order_id, vpl.part_id, mmt.quantity, mmt.total_hours
    ";

    $result = mysqli_query($conn, $sql);
    if (!$result) { return false; }

    $totalStdHoursComplete = 0.0;
    while ($row = mysqli_fetch_assoc($result)) {
        $plannedQty = (float)$row['planned_qty'];
        $totalHours = (float)$row['total_hours'];
        $todayQty   = (float)$row['today_qty'];
        if ($plannedQty <= 0 || $totalHours <= 0 || $todayQty <= 0) continue;

        $totalStdHoursComplete += ($todayQty * $totalHours / $plannedQty);
    }

    return round($totalStdHoursComplete, 2);
}

function updateDailyStdHoursComplete_pu($conn, $date) {
    list($windowStart, $windowEnd) = getStdHoursWindow($date);

    $sql = "
        SELECT vpl.work_order_id, vpl.part_id,
               SUM(vpl.quantity) AS today_qty,
               mmt.quantity AS planned_qty, mmt.total_hours
        FROM pu_part_count_log vpl
        INNER JOIN manufacture_machine_table mmt
            ON mmt.work_order_id = vpl.work_order_id AND mmt.part_id = vpl.part_id
        WHERE
            vpl.type = 'ok'
            AND mmt.quantity > 0
            AND mmt.total_hours > 0
            AND vpl.created_at >= '$windowStart'
            AND vpl.created_at <  '$windowEnd'
        GROUP BY vpl.work_order_id, vpl.part_id, mmt.quantity, mmt.total_hours
    ";

    $result = mysqli_query($conn, $sql);
    if (!$result) { return false; }

    $totalStdHoursComplete = 0.0;
    while ($row = mysqli_fetch_assoc($result)) {
        $plannedQty = (float)$row['planned_qty'];
        $totalHours = (float)$row['total_hours'];
        $todayQty   = (float)$row['today_qty'];
        if ($plannedQty <= 0 || $totalHours <= 0 || $todayQty <= 0) continue;

        $totalStdHoursComplete += ($todayQty * $totalHours / $plannedQty);
    }

    return round($totalStdHoursComplete, 2);
}

function updateDailyStdHoursComplete_assbly($conn, $date) {
    list($windowStart, $windowEnd) = getStdHoursWindow($date);

    $sql = "
        SELECT vpl.work_order_id, vpl.part_id,
               SUM(vpl.quantity) AS today_qty,
               mmt.quantity AS planned_qty, mmt.total_hours
        FROM assbly_part_count_log vpl
        INNER JOIN manufacture_machine_table mmt
            ON mmt.work_order_id = vpl.work_order_id AND mmt.part_id = vpl.part_id
        WHERE
            vpl.type = 'ok'
            AND mmt.quantity > 0
            AND mmt.total_hours > 0
            AND vpl.created_at >= '$windowStart'
            AND vpl.created_at <  '$windowEnd'
        GROUP BY vpl.work_order_id, vpl.part_id, mmt.quantity, mmt.total_hours
    ";

    $result = mysqli_query($conn, $sql);
    if (!$result) { return false; }

    $totalStdHoursComplete = 0.0;
    while ($row = mysqli_fetch_assoc($result)) {
        $plannedQty = (float)$row['planned_qty'];
        $totalHours = (float)$row['total_hours'];
        $todayQty   = (float)$row['today_qty'];
        if ($plannedQty <= 0 || $totalHours <= 0 || $todayQty <= 0) continue;

        $totalStdHoursComplete += ($todayQty * $totalHours / $plannedQty);
    }

    return round($totalStdHoursComplete, 2);
}

function updateDailyStdHoursComplete_assypu($conn, $date) {
    list($windowStart, $windowEnd) = getStdHoursWindow($date);

    $sql = "
        SELECT vpl.work_order_id, vpl.part_id,
               SUM(vpl.quantity) AS today_qty,
               mmt.quantity AS planned_qty, mmt.total_hours
        FROM assypu_part_count_log vpl
        INNER JOIN manufacture_machine_table mmt
            ON mmt.work_order_id = vpl.work_order_id AND mmt.part_id = vpl.part_id
        WHERE
            vpl.type = 'ok'
            AND mmt.quantity > 0
            AND mmt.total_hours > 0
            AND vpl.created_at >= '$windowStart'
            AND vpl.created_at <  '$windowEnd'
        GROUP BY vpl.work_order_id, vpl.part_id, mmt.quantity, mmt.total_hours
    ";

    $result = mysqli_query($conn, $sql);
    if (!$result) { return false; }

    $totalStdHoursComplete = 0.0;
    while ($row = mysqli_fetch_assoc($result)) {
        $plannedQty = (float)$row['planned_qty'];
        $totalHours = (float)$row['total_hours'];
        $todayQty   = (float)$row['today_qty'];
        if ($plannedQty <= 0 || $totalHours <= 0 || $todayQty <= 0) continue;

        $totalStdHoursComplete += ($todayQty * $totalHours / $plannedQty);
    }

    return round($totalStdHoursComplete, 2);
}

// Collect all std hours
$std_cnc    = updateDailyStdHoursComplete_cnc($conn, $date);
$std_vac    = updateDailyStdHoursComplete_vac($conn, $date);
$std_pu     = updateDailyStdHoursComplete_pu($conn, $date);
$std_assbly = updateDailyStdHoursComplete_assbly($conn, $date);
$std_assypu = updateDailyStdHoursComplete_assypu($conn, $date);

echo "cnc std hours: "      . ($std_cnc    !== false ? $std_cnc    : 'Error') . "<br>";
echo "Vac std hours: "      . ($std_vac    !== false ? $std_vac    : 'Error') . "<br>";
echo "PU std hours: "       . ($std_pu     !== false ? $std_pu     : 'Error') . "<br>";
echo "Assembly std hours: " . ($std_assbly !== false ? $std_assbly : 'Error') . "<br>";
echo "assypu std hours: "   . ($std_assypu !== false ? $std_assypu : 'Error') . "<br>";

/////////////////////////////////////////////// std hours end //////////////////////////

//////////////////////////   WIP  ////////////////////////

function getWipTotal($conn, $productGroup) {
    $sql = "
        SELECT
            (COALESCE(i.quantity_in_stock, 0) * COALESCE(im.standard_cost_ebq, 0)) AS wip_value
        FROM item_management im
        LEFT JOIN inventory i ON i.item_id = im.id
        WHERE im.product_group = {$productGroup}
    ";
    $result = mysqli_query($conn, $sql);
    $total = 0;
    while ($row = mysqli_fetch_assoc($result)) {
        $total += (float)$row['wip_value'];
    }
    return round($total, 2);
}

$vac_wip = getWipTotal($conn, 22);
$pu_wip  = getWipTotal($conn, 16);
$cnc_wip = getWipTotal($conn, 10);

echo "<br>Total vac WIP: " . $vac_wip . "<br>";
echo "<br>Total pu WIP: "  . $pu_wip  . "<br>";
echo "<br>Total CNC WIP: " . $cnc_wip;

////////////////////////////////////// WIP end //////////////////////////////////////

// RM Value
function getRMTotal($conn) {
    $sql = "
        SELECT SUM(COALESCE(inv.quantity_in_stock, 0) * COALESCE(im.standard_cost, 0)) AS total
        FROM item_management im
        INNER JOIN inventory inv ON inv.item_id = im.id
        WHERE im.usual_source = 'Bought In'
    ";
    $result = mysqli_query($conn, $sql);
    $row    = mysqli_fetch_assoc($result);
    return round((float)$row['total'], 2);
}

$rm_value = getRMTotal($conn);
echo "<br>Total RM Value: " . number_format($rm_value, 2) . "<br><br>";

// FG Value
function getFGTotal($conn) {
    $sql = "
        SELECT SUM(COALESCE(inv.quantity_in_stock, 0) * COALESCE(im.standard_cost_ebq, 0)) AS total
        FROM item_management im
        LEFT JOIN bom b   ON im.id = b.main_product_id
        LEFT JOIN inventory inv ON inv.item_id = im.id
        WHERE im.usual_source = 'Manufactured'
          AND b.main_product_id IS NULL
    ";
    $result = mysqli_query($conn, $sql);
    $row    = mysqli_fetch_assoc($result);
    return round((float)$row['total'], 2);
}

$fg_value = getFGTotal($conn);
echo "Total FG Value: " . number_format($fg_value, 2) . "<br>";

// FG Difference vs day-before-yesterday
$day_before_yesterday = date("Y-m-d", strtotime("-2 day"));
$res_y = $conn->query("SELECT fg FROM daily_production_summary WHERE record_date = '$day_before_yesterday' LIMIT 1");
$fg_yesterday   = ($res_y && $res_y->num_rows > 0) ? (float)$res_y->fetch_assoc()['fg'] : 0;
$fg_difference  = round($fg_value - $fg_yesterday, 2);

echo "<br>FG Difference: " . number_format($fg_difference, 2) . "<br><br><br>";

//////////////////////////////// FG end ////////////////////////////////////////

///////////////////////////////  UPSERT into daily_production_summary //////////////////////////

$upsertSql = "
    INSERT INTO daily_production_summary (
        record_date,
        vac_wip,
        cnc_wip,
        pu_wip,
        rm,
        fg,
        fg_difference,
        clock_hours_vac,
        clock_hours_cnc,
        clock_hours_pu,
        clock_hours_assmpu,
        clock_hours_assbly,
        std_hours_vac,
        std_hours_cnc,
        std_hours_pu,
        std_hours_assbly,
        std_hours_assmpu
    ) VALUES (
        '$date',
        $vac_wip,
        $cnc_wip,
        $pu_wip,
        $rm_value,
        $fg_value,
        $fg_difference,
        {$vac['hours']},
        $cnc_clock_hours,
        $pu_clock_hours,
        {$assypu['hours']},
        {$assembly['hours']},
        " . ($std_vac    !== false ? $std_vac    : 0) . ",
        " . ($std_cnc    !== false ? $std_cnc    : 0) . ",
        " . ($std_pu     !== false ? $std_pu     : 0) . ",
        " . ($std_assbly !== false ? $std_assbly : 0) . ",
        " . ($std_assypu !== false ? $std_assypu : 0) . "
    )
    ON DUPLICATE KEY UPDATE
        vac_wip            = VALUES(vac_wip),
        cnc_wip            = VALUES(cnc_wip),
        pu_wip             = VALUES(pu_wip),
        rm                 = VALUES(rm),
        fg                 = VALUES(fg),
        fg_difference      = VALUES(fg_difference),
        clock_hours_vac    = VALUES(clock_hours_vac),
        clock_hours_cnc    = VALUES(clock_hours_cnc),
        clock_hours_pu     = VALUES(clock_hours_pu),
        clock_hours_assmpu = VALUES(clock_hours_assmpu),
        clock_hours_assbly = VALUES(clock_hours_assbly),
        std_hours_vac      = VALUES(std_hours_vac),
        std_hours_cnc      = VALUES(std_hours_cnc),
        std_hours_pu       = VALUES(std_hours_pu),
        std_hours_assbly   = VALUES(std_hours_assbly),
        std_hours_assmpu   = VALUES(std_hours_assmpu)
";

if (mysqli_query($conn, $upsertSql)) {
    echo "<strong style='color:green;'> daily_production_summary saved for {$date}</strong><br>";
} else {
    echo "<strong style='color:red;'>Save failed: " . mysqli_error($conn) . "</strong><br>";
}
?>