<?php
// ─── Savuki Drilling — GET /api/dashboard/stats.php ─────────────────────
// Returns role-appropriate stats for the dashboard home screen.

define('ROOT', dirname(__DIR__, 2));
require_once ROOT . '/core/DB.php';
require_once ROOT . '/core/Response.php';
require_once ROOT . '/core/Auth.php';

$user = Auth::require();
$db   = DB::get();
$role = Auth::role($user);

// ── Helpers ───────────────────────────────────────────────────────────────

function activeJobcards($db) {
    return $db->rows(
        "SELECT j.record_id, j.jc_no, j.client_name, j.address,
                j.jc_current_status, j.action_date, j.date_created,
                t.name AS team_name, a.name AS asset_name
         FROM   jobcards j
         LEFT JOIN teams  t ON t.record_id = j.team_assigned_id
         LEFT JOIN assets a ON a.record_id = j.user_assigned
         WHERE  j.jc_current_status != 'COMPLETE'
           AND  j.jc_current_status IS NOT NULL
           AND  j.jc_current_status != ''
         ORDER BY j.action_date DESC
         LIMIT 50"
    );
}

function jobcardsByTeam($db) {
    return $db->rows(
        "SELECT t.name AS team_name, t.record_id AS team_id,
                SUM(j.jc_current_status = 'DRILLING') AS drilling,
                SUM(j.jc_current_status = 'ACTIVE')   AS active,
                SUM(j.jc_current_status = 'PENDING')  AS pending,
                SUM(j.jc_current_status != 'COMPLETE' AND j.jc_current_status IS NOT NULL AND j.jc_current_status != '') AS active_total
         FROM   teams t
         LEFT JOIN jobcards j ON j.team_assigned_id = t.record_id
         WHERE  t.status = 'ACTIVE'
         GROUP BY t.record_id, t.name
         ORDER BY active_total DESC, t.name"
    );
}

function jobcardsByTeamMonthly($db) {
    $thisStart = date('Y-m-01');

    // Get active teams first
    $teams = $db->rows("SELECT record_id, name FROM teams WHERE status = 'ACTIVE' ORDER BY name");
    $result = [];
    foreach ($teams as $t) {
        $tid = (int)$t['record_id'];

        $jcDone = (int)$db->scalar(
            "SELECT COUNT(*) FROM jobcards WHERE team_assigned_id = ? AND jc_current_status = 'COMPLETE' AND date_created >= ?",
            [$tid, $thisStart]
        );
        $meters = (float)$db->scalar(
            "SELECT COALESCE(SUM(jt.meters),0) FROM jobcard_timeline jt
             JOIN jobcards j ON j.record_id = jt.jobcard_id
             WHERE j.team_assigned_id = ? AND j.jc_current_status = 'COMPLETE'
               AND j.date_created >= ? AND jt.meters > 0",
            [$tid, $thisStart]
        );
        $installs = (int)$db->scalar(
            "SELECT COUNT(*) FROM pump_installation WHERE drilling_team_id = ? AND status = 1 AND date_time_created >= ?",
            [$tid, $thisStart]
        );
        $repairs = (int)$db->scalar(
            "SELECT COUNT(*) FROM pump_repair WHERE drilling_team_id = ? AND status = 1 AND date_time_created >= ?",
            [$tid, $thisStart]
        );

        $result[] = [
            'team_id'        => $tid,
            'team_name'      => $t['name'],
            'jc_done'        => $jcDone,
            'meters_drilled' => $meters,
            'pump_installs'  => $installs,
            'pump_repairs'   => $repairs,
        ];
    }
    return $result;
}

function stockMovementSummary($db) {
    $thisStart = date('Y-m-01');
    $lastStart = date('Y-m-01', strtotime('-1 month'));
    $lastEnd   = date('Y-m-t',  strtotime('-1 month')) . ' 23:59:59';

    // Consumed = USED transactions
    $consumedThis = (int) $db->scalar(
        "SELECT COALESCE(SUM(CAST(quantity AS DECIMAL(10,2))),0) FROM stock_trans
         WHERE status='USED' AND datetime_created >= ?", [$thisStart]
    );
    $consumedLast = (int) $db->scalar(
        "SELECT COALESCE(SUM(CAST(quantity AS DECIMAL(10,2))),0) FROM stock_trans
         WHERE status='USED' AND datetime_created BETWEEN ? AND ?", [$lastStart, $lastEnd]
    );

    // Received = RECEIVED transactions
    $receivedThis = (int) $db->scalar(
        "SELECT COALESCE(SUM(CAST(quantity AS DECIMAL(10,2))),0) FROM stock_trans
         WHERE status='RECEIVED' AND datetime_created >= ?", [$thisStart]
    );
    $receivedLast = (int) $db->scalar(
        "SELECT COALESCE(SUM(CAST(quantity AS DECIMAL(10,2))),0) FROM stock_trans
         WHERE status='RECEIVED' AND datetime_created BETWEEN ? AND ?", [$lastStart, $lastEnd]
    );

    // Net movement per category this month
    $byCategory = $db->rows(
        "SELECT st.name AS category,
                COALESCE(SUM(CASE WHEN tr.status='RECEIVED' AND tr.datetime_created >= ? THEN CAST(tr.quantity AS DECIMAL(10,2)) ELSE 0 END),0) AS received,
                COALESCE(SUM(CASE WHEN tr.status='USED'     AND tr.datetime_created >= ? THEN CAST(tr.quantity AS DECIMAL(10,2)) ELSE 0 END),0) AS consumed
         FROM   stock_types st
         LEFT JOIN stock s  ON s.stock_type_id = st.record_id
         LEFT JOIN stock_trans tr ON tr.stock_no = s.stock_no
         GROUP BY st.record_id, st.name
         ORDER BY st.name",
        [$thisStart, $thisStart]
    );

    return [
        'consumed_this' => $consumedThis,
        'consumed_last' => $consumedLast,
        'received_this' => $receivedThis,
        'received_last' => $receivedLast,
        'by_category'   => $byCategory,
        'this_label'    => date('M Y'),
        'last_label'    => date('M Y', strtotime('-1 month')),
    ];
}

function stockBookingStats($db) {
    $today     = date('Y-m-d');
    $weekStart = date('Y-m-d', strtotime('monday this week'));
    $monthStart= date('Y-m-01');

    return [
        'today' => (int) $db->scalar(
            "SELECT COUNT(DISTINCT bs.order_no) FROM book_stock bs
             WHERE DATE(bs.date_time) = ?", [$today]
        ),
        'week' => (int) $db->scalar(
            "SELECT COUNT(DISTINCT bs.order_no) FROM book_stock bs
             WHERE DATE(bs.date_time) >= ?", [$weekStart]
        ),
        'month' => (int) $db->scalar(
            "SELECT COUNT(DISTINCT bs.order_no) FROM book_stock bs
             WHERE DATE(bs.date_time) >= ?", [$monthStart]
        ),
        'items_today' => (int) $db->scalar(
            "SELECT COALESCE(SUM(bsl.amount),0) FROM book_stock_list bsl
             JOIN book_stock bs ON bs.order_no = bsl.order_no
             WHERE DATE(bs.date_time) = ?", [$today]
        ),
        'items_week' => (int) $db->scalar(
            "SELECT COALESCE(SUM(bsl.amount),0) FROM book_stock_list bsl
             JOIN book_stock bs ON bs.order_no = bsl.order_no
             WHERE DATE(bs.date_time) >= ?", [$weekStart]
        ),
        'items_month' => (int) $db->scalar(
            "SELECT COALESCE(SUM(bsl.amount),0) FROM book_stock_list bsl
             JOIN book_stock bs ON bs.order_no = bsl.order_no
             WHERE DATE(bs.date_time) >= ?", [$monthStart]
        ),
    ];
}

function teamStockOut($db) {
    // Active (unreturned) bookings per team with their items
    $orders = $db->rows(
        "SELECT bs.order_no, bs.team_assigned_id, bs.booking_date, t.name AS team_name
         FROM   book_stock bs
         LEFT JOIN teams t ON t.record_id = bs.team_assigned_id
         WHERE  bs.status != 'returned'
         ORDER BY t.name, bs.date_time DESC"
    );
    if (!$orders) return [];

    // Group by team
    $teams = [];
    foreach ($orders as $o) {
        $tid = $o['team_assigned_id'];
        if (!isset($teams[$tid])) {
            $teams[$tid] = [
                'team_name'  => $o['team_name'] ?? 'Unknown',
                'order_count'=> 0,
                'item_count' => 0,
                'items'      => [],
            ];
        }
        $teams[$tid]['order_count']++;

        $items = $db->rows(
            "SELECT item_name, amount FROM book_stock_list WHERE order_no = ?",
            [$o['order_no']]
        );
        foreach ($items as $item) {
            $teams[$tid]['item_count'] += (int)$item['amount'];
            $teams[$tid]['items'][] = $item['item_name'] . ' ×' . $item['amount'];
        }
    }

    // Deduplicate items per team and sort by item_count desc
    $result = array_values($teams);
    usort($result, function($a, $b) { return $b['item_count'] - $a['item_count']; });
    return $result;
}

function stockSummary($db) {
    return $db->rows(
        "SELECT st.name AS type_name,
                COUNT(s.record_id) AS item_count,
                SUM(s.status)      AS total_qty
         FROM   stock_types st
         LEFT JOIN stock s ON s.stock_type_id = st.record_id
         GROUP BY st.record_id, st.name
         ORDER BY st.name"
    );
}

function lowStockItems($db) {
    return $db->rows(
        "SELECT s.record_id, s.stock_no, s.item_name,
                s.status AS quantity, st.name AS type_name
         FROM   stock s
         JOIN   stock_types st ON st.record_id = s.stock_type_id
         WHERE  s.status <= 2 AND s.status >= 0
         ORDER BY s.status ASC
         LIMIT 20"
    );
}

function bookedStockCount($db) {
    return (int) $db->scalar(
        "SELECT COUNT(*) FROM book_stock WHERE status != 'returned'"
    );
}

function assetsSummary($db) {
    return $db->rows(
        "SELECT a.record_id, a.name, a.status, a.odo,
                at.name AS type_name
         FROM   assets a
         LEFT JOIN asset_type at ON at.record_id = a.asset_type_id
         ORDER BY a.name"
    );
}

function myTeamJobcards($db, $teamId) {
    return $db->rows(
        "SELECT j.record_id, j.jc_no, j.client_name, j.address,
                j.jc_current_status, j.action_date,
                a.name AS asset_name
         FROM   jobcards j
         LEFT JOIN assets a ON a.record_id = j.user_assigned
         WHERE  j.team_assigned_id = ?
           AND  j.jc_current_status != 'COMPLETE'
         ORDER BY j.action_date DESC
         LIMIT 20",
        [$teamId]
    );
}

function recentMeters($db, $teamId = null) {
    $where = $teamId ? "AND j.team_assigned_id = $teamId" : '';
    return $db->rows(
        "SELECT jt.jobcard_id, j.client_name, j.jc_no,
                SUM(jt.meters) AS total_meters,
                MAX(jt.date_time) AS last_update
         FROM   jobcard_timeline jt
         JOIN   jobcards j ON j.record_id = jt.jobcard_id
         WHERE  jt.meters > 0 $where
         GROUP BY jt.jobcard_id, j.client_name, j.jc_no
         ORDER BY last_update DESC
         LIMIT 10"
    );
}

function totalMetersThisMonth($db) {
    return (int) $db->scalar(
        "SELECT COALESCE(SUM(meters), 0)
         FROM   jobcard_timeline
         WHERE  meters > 0
           AND  date_time >= DATE_FORMAT(NOW(), '%Y-%m-01')"
    );
}

function pumpJobcardsSummary($db) {
    return [
        'installs' => (int) $db->scalar("SELECT COUNT(*) FROM pump_installation WHERE status != 9"),
        'repairs'  => (int) $db->scalar("SELECT COUNT(*) FROM pump_repair WHERE status != 9"),
    ];
}

function recentActivity($db) {
    return $db->rows(
        "SELECT l.date_time, l.table_name, u.username
         FROM   logs l
         LEFT JOIN users u ON u.record_id = l.user_id
         ORDER BY l.record_id DESC
         LIMIT 15"
    );
}

function invoiceSummary($db) {
    return $db->rows(
        "SELECT status, COUNT(*) AS count
         FROM   invoices
         GROUP BY status"
    );
}

function outstandingInvoices($db) {
    $rows = $db->rows(
        "SELECT i.invoice_no, i.client_name, i.invoice_date, i.status,
                COALESCE(SUM(ii.total), 0) AS total_value
         FROM   invoices i
         LEFT JOIN invoice_items ii ON ii.invoice_no = i.invoice_no
         WHERE  i.status = 'UNPAID'
         GROUP BY i.record_id
         ORDER BY i.date_time DESC
         LIMIT 50"
    );
    return [
        'count' => count($rows),
        'total' => array_sum(array_column($rows, 'total_value')),
        'items' => $rows,
    ];
}

function pumpDetailSummary($db) {
    $thisMonth = date('Y-m-01');
    return [
        'active_installs'    => (int) $db->scalar("SELECT COUNT(*) FROM pump_installation WHERE status = 0"),
        'active_repairs'     => (int) $db->scalar("SELECT COUNT(*) FROM pump_repair WHERE status = 0"),
        'done_installs_this' => (int) $db->scalar("SELECT COUNT(*) FROM pump_installation WHERE status = 1 AND date_time_created >= ?", [$thisMonth]),
        'done_repairs_this'  => (int) $db->scalar("SELECT COUNT(*) FROM pump_repair WHERE status = 1 AND date_time_created >= ?", [$thisMonth]),
    ];
}

function waterStrikeAverage($db) {
    $thisStart = date('Y-m-01');
    $lastStart = date('Y-m-01', strtotime('-1 month'));
    $lastEnd   = date('Y-m-t',  strtotime('-1 month')) . ' 23:59:59';
    return [
        'this_month' => round((float) $db->scalar(
            "SELECT AVG(water_strike) FROM jobcards
             WHERE jc_current_status = 'COMPLETE' AND water_strike > 0 AND date_created >= ?",
            [$thisStart]
        ), 1),
        'last_month' => round((float) $db->scalar(
            "SELECT AVG(water_strike) FROM jobcards
             WHERE jc_current_status = 'COMPLETE' AND water_strike > 0 AND date_created BETWEEN ? AND ?",
            [$lastStart, $lastEnd]
        ), 1),
        'this_label' => date('M Y'),
        'last_label' => date('M Y', strtotime('-1 month')),
    ];
}

function bitSerialStats($db) {
    // Get distinct serials with summed meters from stop entries
    $rows = $db->rows(
        "SELECT
            serial_number,
            COUNT(DISTINCT jobcard_id) AS jobcard_count,
            COALESCE(SUM(meters), 0) AS total_meters,
            MAX(date_time) AS last_used,
            MAX(CASE WHEN type LIKE '%RIEM%' THEN 1 ELSE 0 END) AS is_riem
         FROM jobcard_timeline
         WHERE type IN ('DRILLING_STOP','RIEMING_STOP','RIEM_STOP','REIMING_STOP')
           AND serial_number IS NOT NULL
           AND serial_number != ''
           AND serial_number != '0'
           AND meters > 0
         GROUP BY serial_number
         ORDER BY total_meters DESC
         LIMIT 100"
    );
    foreach ($rows as &$r) {
        $r['bit_type'] = (int)$r['is_riem'] === 1 ? 'Rieming' : 'Drilling';
        unset($r['is_riem']);
    }
    return $rows;
}

function jobcardsThisVsLastMonth($db) {
    $thisStart = date('Y-m-01');
    $lastStart = date('Y-m-01', strtotime('-1 month'));
    $lastEnd   = date('Y-m-t',  strtotime('-1 month'));
    return [
        'this_month' => (int) $db->scalar("SELECT COUNT(*) FROM jobcards WHERE jc_current_status = 'COMPLETE' AND date_created >= ?", [$thisStart]),
        'last_month' => (int) $db->scalar("SELECT COUNT(*) FROM jobcards WHERE jc_current_status = 'COMPLETE' AND date_created BETWEEN ? AND ?", [$lastStart, $lastEnd . ' 23:59:59']),
        'this_label' => date('M Y'),
        'last_label' => date('M Y', strtotime('-1 month')),
    ];
}

// ── Build response based on role ─────────────────────────────────────────

if (in_array($role, ['dev', 'test'])) {
    $data = [
        'role'              => $role,
        'active_jobcards'   => activeJobcards($db),
        'jobcards_by_team'  => jobcardsByTeam($db),
        'stock_summary'     => stockSummary($db),
        'low_stock'         => lowStockItems($db),
        'booked_stock'      => bookedStockCount($db),
        'assets'            => assetsSummary($db),
        'meters_this_month' => totalMetersThisMonth($db),
        'recent_meters'     => recentMeters($db),
        'pump_summary'      => pumpJobcardsSummary($db),
        'recent_activity'   => recentActivity($db),
        'invoice_summary'   => invoiceSummary($db),
        'bit_serials'       => bitSerialStats($db),
    ];
} elseif ($role === 'admin') {
    try { $teamMonthly = jobcardsByTeamMonthly($db);    } catch (Exception $e) { $teamMonthly = []; error_log('jobcardsByTeamMonthly: '.$e->getMessage()); }
    try { $pumpDetail  = pumpDetailSummary($db);        } catch (Exception $e) { $pumpDetail  = []; error_log('pumpDetailSummary: '.$e->getMessage()); }
    try { $jcCompare   = jobcardsThisVsLastMonth($db);  } catch (Exception $e) { $jcCompare   = []; error_log('jobcardsThisVsLastMonth: '.$e->getMessage()); }
    try { $wsAvg       = waterStrikeAverage($db);       } catch (Exception $e) { $wsAvg       = []; error_log('waterStrikeAverage: '.$e->getMessage()); }
    try { $outInv      = outstandingInvoices($db);      } catch (Exception $e) { $outInv      = []; error_log('outstandingInvoices: '.$e->getMessage()); }
    $data = [
        'role'              => $role,
        'active_jobcards'   => activeJobcards($db),
        'jobcards_by_team'  => jobcardsByTeam($db),
        'team_monthly'      => $teamMonthly,
        'meters_this_month' => totalMetersThisMonth($db),
        'recent_meters'     => recentMeters($db),
        'pump_summary'      => pumpJobcardsSummary($db),
        'pump_detail'       => $pumpDetail,
        'jc_month_compare'  => $jcCompare,
        'water_strike_avg'  => $wsAvg,
        'invoice_summary'   => invoiceSummary($db),
        'outstanding_inv'   => $outInv,
        'bit_serials'       => bitSerialStats($db),
    ];
} elseif (in_array($role, ['dispatch', 'workshop'])) {
    try { $stockMov  = stockMovementSummary($db); } catch (Exception $e) { $stockMov  = ['error' => $e->getMessage()]; error_log('stockMovementSummary: '.$e->getMessage()); }
    try { $bookStats = stockBookingStats($db);     } catch (Exception $e) { $bookStats = ['error' => $e->getMessage()]; error_log('stockBookingStats: '.$e->getMessage()); }
    try { $teamOut   = teamStockOut($db);          } catch (Exception $e) { $teamOut   = [];                             error_log('teamStockOut: '.$e->getMessage()); }
    $data = [
        'role'           => $role,
        'stock_summary'  => stockSummary($db),
        'low_stock'      => lowStockItems($db),
        'booked_stock'   => bookedStockCount($db),
        'recent_meters'  => recentMeters($db),
        'stock_movement' => $stockMov,
        'booking_stats'  => $bookStats,
        'team_stock_out' => $teamOut,
    ];
} elseif ($role === 'manager') {
    $data = [
        'role'              => $role,
        'jobcards_by_team'  => jobcardsByTeam($db),
        'meters_this_month' => totalMetersThisMonth($db),
        'recent_meters'     => recentMeters($db),
    ];
} else {
    $teamId = $db->scalar(
        "SELECT record_id FROM teams WHERE name = ?", [$user['team_id']]
    );
    $tid = (int)($teamId ?? 0);
    $thisStart = date('Y-m-01');

    // Team performance this month
    $jcDoneThis = (int)$db->scalar(
        "SELECT COUNT(*) FROM jobcards WHERE team_assigned_id = ? AND jc_current_status = 'COMPLETE' AND date_created >= ?",
        [$tid, $thisStart]
    );
    $metersThis = (float)$db->scalar(
        "SELECT COALESCE(SUM(jt.meters),0) FROM jobcard_timeline jt
         JOIN jobcards j ON j.record_id = jt.jobcard_id
         WHERE j.team_assigned_id = ? AND jt.meters > 0 AND jt.date_time >= ?",
        [$tid, $thisStart]
    );

    // Stock booked to this team (detail)
    $myStock = $db->rows(
        "SELECT bsl.item_name, bsl.amount, bs.booking_date, bs.order_no
         FROM book_stock bs
         JOIN book_stock_list bsl ON bsl.order_no = bs.order_no
         WHERE bs.team_assigned_id = ? AND bs.status != 'returned'
         ORDER BY bs.date_time DESC LIMIT 30",
        [$tid]
    );

    // Water results for completed JCs by this team
    $waterResults = $db->rows(
        "SELECT jc_no, client_name, address, water_strike, water_flow, date_created
         FROM jobcards
         WHERE team_assigned_id = ? AND jc_current_status = 'COMPLETE'
           AND water_strike > 0
         ORDER BY record_id DESC LIMIT 10",
        [$tid]
    );
    $avgWaterStrike = round((float)$db->scalar(
        "SELECT AVG(water_strike) FROM jobcards
         WHERE team_assigned_id = ? AND jc_current_status = 'COMPLETE'
           AND water_strike > 0 AND date_created >= ?",
        [$tid, $thisStart]
    ), 1);

    // Pump stats for install teams
    $pumpInstalls = (int)$db->scalar(
        "SELECT COUNT(*) FROM pump_installation WHERE drilling_team_id = ? AND status = 1 AND date_time_created >= ?",
        [$tid, $thisStart]
    );
    $pumpRepairs = (int)$db->scalar(
        "SELECT COUNT(*) FROM pump_repair WHERE drilling_team_id = ? AND status = 1 AND date_time_created >= ?",
        [$tid, $thisStart]
    );

    $data = [
        'role'            => $role,
        'my_jobcards'     => myTeamJobcards($db, $tid),
        'recent_meters'   => recentMeters($db, $tid ?: null),
        'booked_stock'    => bookedStockCount($db),
        'my_stock_detail' => $myStock,
        'team_perf'       => [
            'jc_done'     => $jcDoneThis,
            'meters'      => round($metersThis, 1),
            'this_label'  => date('M Y'),
        ],
        'water_results'   => $waterResults,
        'avg_water_strike'=> $avgWaterStrike,
        'pump_this_month' => [
            'installs' => $pumpInstalls,
            'repairs'  => $pumpRepairs,
        ],
    ];
}

Response::ok($data);