<?php
require_once __DIR__ . '/../config/db.php';
require_once __DIR__ . '/../config/auth.php';

$user   = requireAuth();
$db     = getDB();
$roleId = (int)$user['role_id'];
$uid    = (int)$user['id'];
$isAdmin = $roleId === 1;

// ── Permission helper ────────────────────────────────────────
function hasPerm($db, $roleId, $module, $action) {
    if ($roleId === 1) return true;
    try {
        $s = $db->prepare("SELECT allowed FROM role_permissions WHERE role_id=? AND module=? AND action=? LIMIT 1");
        $s->execute([$roleId, $module, $action]);
        $r = $s->fetch();
        return $r && (int)$r['allowed'] === 1;
    } catch (Exception $e) { return false; }
}

$stats = [];

// ── Clients ──────────────────────────────────────────────────
if (hasPerm($db, $roleId, 'clients', 'view')) {
    $stats['total_clients'] = (int)$db->query("SELECT COUNT(*) FROM clients WHERE status='active'")->fetchColumn();
}

// ── Employees ────────────────────────────────────────────────
if (hasPerm($db, $roleId, 'employees', 'view')) {
    $stats['total_employees'] = (int)$db->query("SELECT COUNT(*) FROM employees WHERE status='active'")->fetchColumn();
    if (hasPerm($db, $roleId, 'employees', 'reports')) {
        $stats['pending_leave'] = (int)$db->query("SELECT COUNT(*) FROM employee_leave WHERE status='pending'")->fetchColumn();
        $stats['on_leave_today'] = (int)$db->query("SELECT COUNT(*) FROM employee_leave WHERE status='approved' AND start_date <= CURDATE() AND end_date >= CURDATE()")->fetchColumn();
    }
}

// ── Projects ─────────────────────────────────────────────────
if (hasPerm($db, $roleId, 'projects', 'view')) {
    $stats['active_projects'] = (int)$db->query("SELECT COUNT(*) FROM projects WHERE status='active'")->fetchColumn();
    $stats['open_bugs']       = (int)$db->query("SELECT COUNT(*) FROM project_bugs WHERE status IN ('open','in_progress')")->fetchColumn();
    $s = $db->prepare("SELECT COUNT(*) FROM project_todos WHERE assigned_to=? AND status != 'done'");
    $s->execute([$uid]);
    $stats['my_todos'] = (int)$s->fetchColumn();
}

// ── Job Cards ────────────────────────────────────────────────
if (hasPerm($db, $roleId, 'jobcards', 'view') || hasPerm($db, $roleId, 'jobcards', 'view_own')) {
    $myOnly = !hasPerm($db, $roleId, 'jobcards', 'view') && hasPerm($db, $roleId, 'jobcards', 'view_own');
    $jcWhere = $myOnly ? "AND (jc.assigned_to = $uid OR EXISTS (SELECT 1 FROM job_card_technicians jct WHERE jct.job_card_id = jc.id AND jct.user_id = $uid))" : '';
    $stats['active_jobs']     = (int)$db->query("SELECT COUNT(*) FROM job_cards jc WHERE jc.status IN ('assigned','travelling','on_site','working') $jcWhere")->fetchColumn();
    $stats['completed_today'] = (int)$db->query("SELECT COUNT(*) FROM job_cards jc WHERE DATE(jc.completed_at) = CURDATE() $jcWhere")->fetchColumn();
    $stats['pending_jobs']    = (int)$db->query("SELECT COUNT(*) FROM job_cards jc WHERE jc.status = 'assigned' $jcWhere")->fetchColumn();
    if (hasPerm($db, $roleId, 'jobcards', 'view_costing')) {
        try {
            $plRow = $db->query("SELECT COALESCE(SUM(invoice_amount),0) AS revenue FROM job_cards WHERE status IN ('completed','invoiced') AND MONTH(completed_at)=MONTH(CURDATE()) AND YEAR(completed_at)=YEAR(CURDATE())")->fetch();
            $stats['jc_revenue'] = (float)($plRow['revenue'] ?? 0);
        } catch (Exception $e) {}
    }
}

// ── Stock ────────────────────────────────────────────────────
if (hasPerm($db, $roleId, 'stock', 'view')) {
    $stats['low_stock'] = (int)$db->query("
        SELECT COUNT(*) FROM stock_items si
        LEFT JOIN (SELECT stock_item_id, SUM(CASE WHEN transaction_type LIKE 'receive%' THEN quantity ELSE -quantity END) AS qty FROM stock_transactions GROUP BY stock_item_id) inv ON inv.stock_item_id = si.id
        WHERE COALESCE(inv.qty,0) <= si.min_qty AND si.is_active=1
    ")->fetchColumn();
    if (hasPerm($db, $roleId, 'stock', 'reports')) {
        $stats['stock_value'] = (float)$db->query("
            SELECT COALESCE(SUM(COALESCE(inv.qty,0) * COALESCE(si.unit_cost,0)),0)
            FROM stock_items si
            LEFT JOIN (SELECT stock_item_id, SUM(CASE WHEN transaction_type LIKE 'receive%' THEN quantity ELSE -quantity END) AS qty FROM stock_transactions GROUP BY stock_item_id) inv ON inv.stock_item_id = si.id
            WHERE si.is_active=1
        ")->fetchColumn();
    }
}

// ── Slips ────────────────────────────────────────────────────
if (hasPerm($db, $roleId, 'slips', 'view')) {
    $slipOwn = !$isAdmin ? "AND user_id = $uid" : '';
    $stats['unlinked_slips'] = (int)$db->query("SELECT COUNT(*) FROM slips WHERE status='unlinked' $slipOwn")->fetchColumn();
    if (hasPerm($db, $roleId, 'slips', 'reports')) {
        $stats['slips_this_month'] = (float)$db->query("SELECT COALESCE(SUM(amount),0) FROM slips WHERE MONTH(slip_date)=MONTH(CURDATE()) AND YEAR(slip_date)=YEAR(CURDATE()) $slipOwn")->fetchColumn();
    }
}

// ── Fleet ────────────────────────────────────────────────────
if (hasPerm($db, $roleId, 'fleet', 'view')) {
    try {
        $stats['fleet_vehicles'] = (int)$db->query("SELECT COUNT(*) FROM fleet_vehicles WHERE status='active'")->fetchColumn();
        if (hasPerm($db, $roleId, 'fleet', 'reports')) {
            $stats['fleet_costs_month'] = (float)$db->query("SELECT COALESCE(SUM(amount),0) FROM fleet_costs WHERE MONTH(cost_date)=MONTH(CURDATE()) AND YEAR(cost_date)=YEAR(CURDATE())")->fetchColumn();
        }
    } catch (Exception $e) {}
}

// ── Cash Flow ────────────────────────────────────────────────
if (hasPerm($db, $roleId, 'cashflow', 'view') && hasPerm($db, $roleId, 'cashflow', 'reports')) {
    try {
        $cfRow = $db->query("
            SELECT opening_balance,
                   (SELECT COALESCE(SUM(amount),0) FROM cashflow_lines WHERE month_id=cm.id AND type='income' AND status='actual') AS actual_income,
                   (SELECT COALESCE(SUM(amount),0) FROM cashflow_lines WHERE month_id=cm.id AND type='expense' AND status='actual') AS actual_expenses
            FROM cashflow_months cm
            WHERE MONTH(month_date)=MONTH(CURDATE()) AND YEAR(month_date)=YEAR(CURDATE())
            LIMIT 1
        ")->fetch();
        if ($cfRow) {
            $stats['cf_income']   = (float)$cfRow['actual_income'];
            $stats['cf_expenses'] = (float)$cfRow['actual_expenses'];
            $stats['cf_net']      = $stats['cf_income'] - $stats['cf_expenses'];
        }
    } catch (Exception $e) {}
}

// ── Calendar ─────────────────────────────────────────────────
if (hasPerm($db, $roleId, 'calendar', 'view')) {
    try {
        $calOwn = !$isAdmin ? "AND (ce.created_by = $uid OR ce.assigned_to = $uid)" : '';
        $stats['events_today']    = (int)$db->query("SELECT COUNT(*) FROM calendar_events ce WHERE DATE(event_date) = CURDATE() AND status != 'cancelled' $calOwn")->fetchColumn();
        $stats['events_this_week'] = (int)$db->query("SELECT COUNT(*) FROM calendar_events ce WHERE event_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY) AND status != 'cancelled' $calOwn")->fetchColumn();
    } catch (Exception $e) {}
}

// ── Upcoming Job Cards ────────────────────────────────────────
$upcomingJobs = [];
if (hasPerm($db, $roleId, 'jobcards', 'view') || hasPerm($db, $roleId, 'jobcards', 'view_own')) {
    $myOnly = !hasPerm($db, $roleId, 'jobcards', 'view') && hasPerm($db, $roleId, 'jobcards', 'view_own');
    $jcWhere = $myOnly ? "AND (jc.assigned_to = $uid OR EXISTS (SELECT 1 FROM job_card_technicians jct WHERE jct.job_card_id = jc.id AND jct.user_id = $uid))" : '';
    $upcomingJobs = $db->query("
        SELECT jc.id, jc.job_number, jc.title, jc.scheduled_date, jc.scheduled_time,
               jc.status, jc.priority,
               c.company_name AS client_name,
               u.full_name AS assigned_name
        FROM job_cards jc
        LEFT JOIN clients c ON c.id = jc.client_id
        LEFT JOIN users u ON u.id = jc.assigned_to
        WHERE jc.scheduled_date >= CURDATE() AND jc.status NOT IN ('completed','cancelled','invoiced','no_charge') $jcWhere
        ORDER BY jc.scheduled_date ASC, jc.scheduled_time ASC
        LIMIT 8
    ")->fetchAll();
}

// ── My Today's Jobs (for technicians) ────────────────────────
$todayJobs = [];
if (hasPerm($db, $roleId, 'jobcards', 'view_own') || $myOnly ?? false) {
    $todayJobs = $db->query("
        SELECT jc.id, jc.job_number, jc.title, jc.scheduled_time, jc.status, jc.priority,
               c.company_name AS client_name, jc.site_name
        FROM job_cards jc
        LEFT JOIN clients c ON c.id = jc.client_id
        WHERE jc.scheduled_date = CURDATE()
        AND jc.status NOT IN ('completed','cancelled','invoiced','no_charge')
        AND (jc.assigned_to = $uid OR EXISTS (SELECT 1 FROM job_card_technicians jct WHERE jct.job_card_id = jc.id AND jct.user_id = $uid))
        ORDER BY jc.scheduled_time ASC
        LIMIT 10
    ")->fetchAll();
}

// ── Low stock items ───────────────────────────────────────────
$lowStockItems = [];
if (hasPerm($db, $roleId, 'stock', 'view')) {
    try {
        $lowStockItems = $db->query("
            SELECT si.name, si.sku, si.min_qty,
                   COALESCE(inv.qty,0) AS qty_on_hand
            FROM stock_items si
            LEFT JOIN (SELECT stock_item_id, SUM(CASE WHEN transaction_type LIKE 'receive%' THEN quantity ELSE -quantity END) AS qty FROM stock_transactions GROUP BY stock_item_id) inv ON inv.stock_item_id = si.id
            WHERE COALESCE(inv.qty,0) <= si.min_qty AND si.is_active=1
            ORDER BY qty_on_hand ASC LIMIT 5
        ")->fetchAll();
    } catch (Exception $e) {}
}

// ── Recent Activity ───────────────────────────────────────────
$recentActivity = [];
if (hasPerm($db, $roleId, 'projects', 'view')) {
    try {
        $recentActivity = $db->query("
            SELECT pa.*, u.full_name AS user_name, p.name AS project_name
            FROM project_activity pa
            JOIN users u ON u.id = pa.user_id
            JOIN projects p ON p.id = pa.project_id
            ORDER BY pa.created_at DESC LIMIT 8
        ")->fetchAll();
    } catch (Exception $e) {}
}

apiSuccess([
    'stats'           => $stats,
    'upcoming_jobs'   => $upcomingJobs,
    'today_jobs'      => $todayJobs,
    'low_stock'       => $lowStockItems,
    'recent_activity' => $recentActivity,
    'permissions'     => [
        'jobcards'   => ['view' => hasPerm($db,$roleId,'jobcards','view'), 'view_own' => hasPerm($db,$roleId,'jobcards','view_own'), 'costing' => hasPerm($db,$roleId,'jobcards','view_costing')],
        'clients'    => hasPerm($db,$roleId,'clients','view'),
        'employees'  => ['view' => hasPerm($db,$roleId,'employees','view'), 'reports' => hasPerm($db,$roleId,'employees','reports')],
        'projects'   => hasPerm($db,$roleId,'projects','view'),
        'stock'      => ['view' => hasPerm($db,$roleId,'stock','view'), 'reports' => hasPerm($db,$roleId,'stock','reports')],
        'fleet'      => ['view' => hasPerm($db,$roleId,'fleet','view'), 'reports' => hasPerm($db,$roleId,'fleet','reports')],
        'slips'      => ['view' => hasPerm($db,$roleId,'slips','view'), 'reports' => hasPerm($db,$roleId,'slips','reports')],
        'cashflow'   => ['view' => hasPerm($db,$roleId,'cashflow','view'), 'reports' => hasPerm($db,$roleId,'cashflow','reports')],
        'calendar'   => hasPerm($db,$roleId,'calendar','view'),
    ],
]);