<?php
include("../../../include/startup.php");
header('Content-Type: application/json');
ini_set('display_errors', 0);
ob_start();

if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'get_breakdown_report') {

    $pg        = max(1, intval($_REQUEST['pg'] ?? 1));
    $perPage   = 20;
    $offset    = ($pg - 1) * $perPage;

    $filterMachine  = intval($_REQUEST['machine_id']   ?? 0);
    $filterType     = intval($_REQUEST['machine_type'] ?? 0);
    $filterDateFrom = mysqli_real_escape_string($conn, $_REQUEST['date_from'] ?? '');
    $filterDateTo   = mysqli_real_escape_string($conn, $_REQUEST['date_to']   ?? '');

    // NOTE: 'RW' (not 'REPLICA WOOD') deliberately — the front-end reuses
    // this label, lowercased, as a CSS class name (machine-type-pill rw /
    // bd-summary-card rw). A label with a space would split into two
    // separate class tokens and never match those rules.
    $machineTypeLabels = [1=>'VAC', 2=>'ASSM', 3=>'CNC', 4=>'PU', 5=>'ASSYPU', 6=>'RW'];

    // Each entry: [breakdown_log_table, machine_type_id, keycode_name]
    $sources = [
        ['vac_machine_breakdown_log',    1, 'vac_machine_breakdown_reason'],
        ['assbly_machine_breakdown_log', 2, 'breakdown_reason'],
        ['cnc_machine_breakdown_log',    3, 'cnc_machine_breakdown_reason'],
        ['pu_machine_breakdown_log',     4, 'cnc_machine_breakdown_reason'],
        ['assypu_machine_breakdown_log', 5, 'breakdown_reason'],
    ];

    // Check which tables actually exist
    $existingTables = [];
    $tblCheck = mysqli_query($conn, "SHOW TABLES");
    while ($t = mysqli_fetch_row($tblCheck)) $existingTables[] = $t[0];

    $unionParts = [];

    foreach ($sources as [$table, $typeId, $keycodeName]) {
        if ($filterType > 0 && $filterType !== $typeId) continue;
        if (!in_array($table, $existingTables)) continue;

$whereClause = "1=1";
if ($filterMachine > 0)     $whereClause .= " AND bl.machine_id = $filterMachine";
if ($filterDateFrom !== '') $whereClause .= " AND DATE(bl.created_at) >= '$filterDateFrom'";
if ($filterDateTo   !== '') $whereClause .= " AND DATE(bl.created_at) <= '$filterDateTo'";

// Hide 'active' rows that have already been closed out by a later 'completed' row
// for the same work_order_id/part_id/machine_id — those are the same incident,
// already represented (correctly) by the completed row.
$whereClause .= " AND NOT (
    bl.status = 'active'
    AND EXISTS (
        SELECT 1 FROM `$table` comp
        WHERE comp.status        = 'completed'
          AND comp.work_order_id = bl.work_order_id
          AND comp.part_id       = bl.part_id
          AND comp.machine_id    = bl.machine_id
          AND comp.created_at   >= bl.created_at
    )
)";
        $label = $machineTypeLabels[$typeId];

        // "actual_duration_seconds" is computed by pairing a completed row with the
        // most recent preceding active row for the same work_order_id/part_id/machine_id.
        // For a row still in "active" status, we show live elapsed time (flagged is_ongoing=1).
        $unionParts[] = "
            SELECT
                bl.id,
                $typeId          AS machine_type_id,
                '$label'         AS machine_type_label,
                bl.machine_id,
                bl.engineer_id,
                sd.resource_code AS machine_name,
                bl.breakdown_reason,
                kc.code          AS reason_text,
                bl.breakdown_duration,
                bl.status,
                bl.created_at,
                a.uname          AS engineer_name,
                CASE
                    WHEN bl.status = 'completed' THEN TIMESTAMPDIFF(SECOND, act.created_at, bl.created_at)
                    WHEN bl.status = 'active'    THEN TIMESTAMPDIFF(SECOND, bl.created_at, NOW())
                    ELSE NULL
                END AS actual_duration_seconds,
                CASE WHEN bl.status = 'active' THEN 1 ELSE 0 END AS is_ongoing
            FROM `$table` bl
            LEFT JOIN station_detail_table sd
                ON bl.machine_id = sd.id
            LEFT JOIN keycode kc
                ON kc.id   = bl.breakdown_reason
               AND kc.name = '$keycodeName'
            LEFT JOIN admin a
                ON bl.engineer_id = a.id
            LEFT JOIN `$table` act
                ON act.status        = 'active'
               AND act.work_order_id = bl.work_order_id
               AND act.part_id       = bl.part_id
               AND act.machine_id    = bl.machine_id
               AND act.created_at = (
                    SELECT MAX(act2.created_at)
                    FROM `$table` act2
                    WHERE act2.status        = 'active'
                      AND act2.work_order_id = bl.work_order_id
                      AND act2.part_id       = bl.part_id
                      AND act2.machine_id    = bl.machine_id
                      AND act2.created_at   <= bl.created_at
               )
            WHERE $whereClause
        ";
    }

    // ------------------------------------------------------------------
    // REPLICA WOOD (machine_type_id = 6) — own block, single-row model
    // (started_at/ended_at on the same row, no active+completed pairing).
    // NOTE: Replica Wood breakdowns are now WHOLE-DASHBOARD, not per line
    // (there's no meaningful "machine_id" to filter/join on any more), so
    // this ignores $filterMachine entirely and machine_name is a fixed
    // label rather than a per-row lookup.
    // ------------------------------------------------------------------
    if (($filterType === 0 || $filterType === 6) && in_array('replica_wood_breakdown_log', $existingTables)) {

        $rwWhere = "1=1";
        if ($filterDateFrom !== '') $rwWhere .= " AND DATE(bl.started_at) >= '$filterDateFrom'";
        if ($filterDateTo   !== '') $rwWhere .= " AND DATE(bl.started_at) <= '$filterDateTo'";

        $unionParts[] = "
            SELECT
                bl.id,
                6                                       AS machine_type_id,
                'RW'                                    AS machine_type_label,
                NULL                                    AS machine_id,
                bl.engineer_id,
                'Replica Wood'                          AS machine_name,
                NULL                                    AS breakdown_reason,
                bl.reason                                AS reason_text,
                NULL                                    AS breakdown_duration,
                bl.status,
                bl.started_at                            AS created_at,
                a.uname                                  AS engineer_name,
                CASE
                    WHEN bl.status = 'completed' THEN TIMESTAMPDIFF(SECOND, bl.started_at, bl.ended_at)
                    WHEN bl.status = 'active'    THEN TIMESTAMPDIFF(SECOND, bl.started_at, NOW())
                    ELSE NULL
                END AS actual_duration_seconds,
                CASE WHEN bl.status = 'active' THEN 1 ELSE 0 END AS is_ongoing
            FROM `replica_wood_breakdown_log` bl
            LEFT JOIN admin a
                ON bl.engineer_id = a.id
            WHERE $rwWhere
        ";
    }

    if (empty($unionParts)) {
        ob_end_clean();
        echo json_encode(['success'=>true,'data'=>[],'total'=>0,'pages'=>1,'page'=>$pg,'per_page'=>$perPage]);
        exit;
    }

    $unionSql = implode(' UNION ALL ', $unionParts);

    $countResult = mysqli_query($conn, "SELECT COUNT(*) AS total FROM ($unionSql) AS combined");
    if (!$countResult) {
        ob_end_clean();
        echo json_encode(['success'=>false,'message'=>'Count failed: '.mysqli_error($conn)]);
        exit;
    }
    $totalRows  = (int)mysqli_fetch_assoc($countResult)['total'];
    $totalPages = max(1, (int)ceil($totalRows / $perPage));

    // Newest records first, always. All historical records remain reachable via pagination below.
    $dataResult = mysqli_query($conn, "SELECT * FROM ($unionSql) AS combined ORDER BY created_at DESC LIMIT $perPage OFFSET $offset");
    if (!$dataResult) {
        ob_end_clean();
        echo json_encode(['success'=>false,'message'=>'Data failed: '.mysqli_error($conn)]);
        exit;
    }

    $rows = [];
    while ($row = mysqli_fetch_assoc($dataResult)) {
        // than a misleading "0m".
        if ($row['breakdown_duration'] === null) {
            $row['duration_formatted'] = '—';
        } else {
            $dur   = (float)$row['breakdown_duration'];
            $hours = (int)floor($dur);
            $mins  = (int)round(($dur - $hours) * 60);
            $row['duration_formatted']   = $hours > 0
                ? ($hours.'h'.($mins > 0 ? ' '.$mins.'m' : ''))
                : ($mins.'m');
        }

        // Actual duration, computed from active -> completed timestamps
        // (or started_at -> ended_at for Replica Wood's single-row model).
        $actualSecs = $row['actual_duration_seconds'];
        if ($actualSecs === null) {
            $row['actual_duration_formatted'] = '—';
        } else {
            $actualSecs = (int)$actualSecs;
            $h = intdiv($actualSecs, 3600);
            $m = intdiv($actualSecs % 3600, 60);
            $s = $actualSecs % 60;
            if ($h > 0)      $row['actual_duration_formatted'] = "{$h}h {$m}m";
            elseif ($m > 0)  $row['actual_duration_formatted'] = "{$m}m {$s}s";
            else             $row['actual_duration_formatted'] = "{$s}s";

            if ((int)$row['is_ongoing'] === 1) {
                $row['actual_duration_formatted'] .= ' (ongoing)';
            }
        }

        $row['created_at_formatted'] = date('d M Y H:i', strtotime($row['created_at']));
        $rows[] = $row;
    }

    ob_end_clean();
    echo json_encode([
        'success'  => true,
        'data'     => $rows,
        'total'    => $totalRows,
        'pages'    => $totalPages,
        'page'     => $pg,
        'per_page' => $perPage,
    ]);
    exit;
}

else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'get_breakdown_summary') {

    // Month in 'YYYY-MM' format, defaults to current month
    $month = $_REQUEST['month'] ?? date('Y-m');
    if (!preg_match('/^\d{4}-(0[1-9]|1[0-2])$/', $month)) {
        $month = date('Y-m');
    }
    $monthStart = $month . '-01';
    $monthEnd   = date('Y-m-t', strtotime($monthStart));

    $filterMachine = intval($_REQUEST['machine_id'] ?? 0);

    $machineTypeLabels = [1=>'VAC', 2=>'ASSM', 3=>'CNC', 4=>'PU', 5=>'ASSYPU', 6=>'RW'];

    $sources = [
        ['vac_machine_breakdown_log',    1, 'vac_machine_breakdown_reason'],
        ['assbly_machine_breakdown_log', 2, 'breakdown_reason'],
        ['cnc_machine_breakdown_log',    3, 'cnc_machine_breakdown_reason'],
        ['pu_machine_breakdown_log',     4, 'cnc_machine_breakdown_reason'],
        ['assypu_machine_breakdown_log', 5, 'breakdown_reason'],
    ];

    $existingTables = [];
    $tblCheck = mysqli_query($conn, "SHOW TABLES");
    while ($t = mysqli_fetch_row($tblCheck)) $existingTables[] = $t[0];

    $unionParts = [];

    foreach ($sources as [$table, $typeId, $keycodeName]) {
        if (!in_array($table, $existingTables)) continue;

        $whereClause = "DATE(bl.created_at) >= '$monthStart' AND DATE(bl.created_at) <= '$monthEnd'";
        if ($filterMachine > 0) $whereClause .= " AND bl.machine_id = $filterMachine";

        // Same rule as the main report: hide 'active' rows already closed out
        // by a later 'completed' row for the same work_order/part/machine.
        $whereClause .= " AND NOT (
            bl.status = 'active'
            AND EXISTS (
                SELECT 1 FROM `$table` comp
                WHERE comp.status        = 'completed'
                  AND comp.work_order_id = bl.work_order_id
                  AND comp.part_id       = bl.part_id
                  AND comp.machine_id    = bl.machine_id
                  AND comp.created_at   >= bl.created_at
            )
        )";

        $label = $machineTypeLabels[$typeId];

        $unionParts[] = "
            SELECT
                $typeId AS machine_type_id,
                '$label' AS machine_type_label,
                CASE
                    WHEN bl.status = 'completed' THEN TIMESTAMPDIFF(SECOND, act.created_at, bl.created_at)
                    WHEN bl.status = 'active'    THEN TIMESTAMPDIFF(SECOND, bl.created_at, NOW())
                    ELSE NULL
                END AS actual_duration_seconds,
                CASE WHEN bl.status = 'active' THEN 1 ELSE 0 END AS is_ongoing
            FROM `$table` bl
            LEFT JOIN `$table` act
                ON act.status        = 'active'
               AND act.work_order_id = bl.work_order_id
               AND act.part_id       = bl.part_id
               AND act.machine_id    = bl.machine_id
               AND act.created_at = (
                    SELECT MAX(act2.created_at)
                    FROM `$table` act2
                    WHERE act2.status        = 'active'
                      AND act2.work_order_id = bl.work_order_id
                      AND act2.part_id       = bl.part_id
                      AND act2.machine_id    = bl.machine_id
                      AND act2.created_at   <= bl.created_at
               )
            WHERE $whereClause
        ";
    }

    // Replica Wood — same simpler single-row model, now whole-dashboard
    // scoped (no line_id to filter on any more).
    if (in_array('replica_wood_breakdown_log', $existingTables)) {
        $rwWhere = "DATE(bl.started_at) >= '$monthStart' AND DATE(bl.started_at) <= '$monthEnd'";

        $unionParts[] = "
            SELECT
                6        AS machine_type_id,
                'RW'     AS machine_type_label,
                CASE
                    WHEN bl.status = 'completed' THEN TIMESTAMPDIFF(SECOND, bl.started_at, bl.ended_at)
                    WHEN bl.status = 'active'    THEN TIMESTAMPDIFF(SECOND, bl.started_at, NOW())
                    ELSE NULL
                END AS actual_duration_seconds,
                CASE WHEN bl.status = 'active' THEN 1 ELSE 0 END AS is_ongoing
            FROM `replica_wood_breakdown_log` bl
            WHERE $rwWhere
        ";
    }

    // Default every machine type to zero so the UI always has a card to show
    $summary = [];
    foreach ($machineTypeLabels as $typeId => $label) {
        $summary[$typeId] = [
            'machine_type_id'    => $typeId,
            'machine_type_label' => $label,
            'total_seconds'      => 0,
            'breakdown_count'    => 0,
            'ongoing_count'      => 0,
        ];
    }

    if (!empty($unionParts)) {
        $unionSql = implode(' UNION ALL ', $unionParts);
        $sql = "
            SELECT
                machine_type_id,
                machine_type_label,
                COALESCE(SUM(actual_duration_seconds), 0) AS total_seconds,
                COUNT(*) AS breakdown_count,
                SUM(CASE WHEN is_ongoing = 1 THEN 1 ELSE 0 END) AS ongoing_count
            FROM ($unionSql) AS combined
            GROUP BY machine_type_id, machine_type_label
        ";
        $result = mysqli_query($conn, $sql);
        if (!$result) {
            ob_end_clean();
            echo json_encode(['success'=>false,'message'=>'Summary failed: '.mysqli_error($conn)]);
            exit;
        }
        while ($row = mysqli_fetch_assoc($result)) {
            $tid = (int)$row['machine_type_id'];
            $summary[$tid]['total_seconds']   = (int)$row['total_seconds'];
            $summary[$tid]['breakdown_count'] = (int)$row['breakdown_count'];
            $summary[$tid]['ongoing_count']   = (int)$row['ongoing_count'];
        }
    }

    $grandTotalSeconds = 0;
    foreach ($summary as $row) $grandTotalSeconds += $row['total_seconds'];

    // Format durations for display — decimal hours
    foreach ($summary as $tid => &$row) {
        $hoursDecimal = round($row['total_seconds'] / 3600, 1);
        $row['total_hours']     = $hoursDecimal;
        $row['total_formatted'] = number_format($hoursDecimal, 1) . 'h';
    }
    unset($row);

    ob_end_clean();
    echo json_encode([
        'success'             => true,
        'month'               => $month,
        'data'                => array_values($summary),
        'grand_total_seconds' => $grandTotalSeconds,
    ]);
    exit;
}

else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'get_breakdown_machines') {
    $typeFilter = intval($_REQUEST['machine_type'] ?? 0);

    if ($typeFilter === 6) {
        // Replica Wood breakdowns are whole-dashboard now, not per line —
        // there's nothing meaningful to filter by here any more.
        echo json_encode(['success' => true, 'data' => []]);
        exit;
    }

    $where = $typeFilter > 0
        ? "WHERE machine_type = $typeFilter AND hide = 0"
        : "WHERE hide = 0";
    $result = mysqli_query($conn, "SELECT id, resource_code, machine_type FROM station_detail_table $where ORDER BY resource_code");

    if (!$result) { ob_end_clean(); echo json_encode(['success'=>false,'message'=>mysqli_error($conn)]); exit; }
    $machines = [];
    while ($row = mysqli_fetch_assoc($result)) $machines[] = $row;
    ob_end_clean();
    echo json_encode(['success'=>true,'data'=>$machines]);
    exit;
}

ob_end_clean();
echo json_encode(['success'=>false,'message'=>'Unknown action: '.($_REQUEST['action'] ?? 'none')]);
?>