<?php
namespace App\Controllers;

use App\Core\Controller;
use App\Core\Auth;
use App\Core\Database;
use PDO;

class SupervisorController extends Controller
{
    private function detectUserPhotoColumn(PDO $pdo): ?string
    {
        foreach (['photo_path', 'avatar_path', 'profile_photo', 'profile_picture', 'avatar', 'photo', 'image_path'] as $candidate) {
            $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?');
            $stmt->execute(['users', $candidate]);
            if ((int)$stmt->fetchColumn() > 0) {
                return $candidate;
            }
        }

        return null;
    }

    private function resolveUserPhotoUrl(?string $rawPath): ?string
    {
        $normalized = trim((string)$rawPath);
        if ($normalized === '') {
            return null;
        }

        if (preg_match('~^https?://~i', $normalized)) {
            return $normalized;
        }

        if (str_starts_with($normalized, 'storage/uploads/')) {
            $normalized = substr($normalized, strlen('storage/uploads/'));
        } elseif (str_starts_with($normalized, 'uploads/')) {
            $normalized = substr($normalized, strlen('uploads/'));
        }

        return upload_url($normalized);
    }

    /**
     * Dashboard superviseur avec statistiques et actions principales
     */
    public function dashboard(): void
    {
        if (!Auth::check()) { $this->redirect('/login'); }
        Auth::requireRole(['admin', 'manager', 'superviseur', 'supervisor']);

        $pdo = Database::pdo();

        // Statistiques clés
        $stats = [
            'pending_validations' => 0,
            'active_incidents' => 0,
            'validated_today' => 0,
            'total_technicians' => 0
        ];

        try {
            // Rapports en attente de validation
            $stmt = $pdo->query("SELECT COUNT(*) FROM incident_reports WHERE submitted_at IS NOT NULL AND validated_at IS NULL");
            $stats['pending_validations'] = (int)$stmt->fetchColumn();

            // Incidents actifs (non clôturés)
            $stmt = $pdo->query("SELECT COUNT(*) FROM incidents i JOIN incident_statuses s ON s.id = i.status_id WHERE s.key_name NOT IN ('closed', 'cancelled')");
            $stats['active_incidents'] = (int)$stmt->fetchColumn();

            // Validations aujourd'hui
            $stmt = $pdo->query("SELECT COUNT(*) FROM incident_reports WHERE DATE(validated_at) = CURDATE()");
            $stats['validated_today'] = (int)$stmt->fetchColumn();

            // Techniciens actifs
            $stmt = $pdo->query("SELECT COUNT(*) FROM users WHERE active = 1 AND role_key IN ('technicien', 'technician')");
            $stats['total_technicians'] = (int)$stmt->fetchColumn();

        } catch (\Throwable $e) {
            error_log("Supervisor stats error: " . $e->getMessage());
        }

        // Rapports en attente de validation avec détails
        $pendingReports = [];
        try {
            $stmt = $pdo->query("
                SELECT 
                    ir.id as report_id,
                    ir.incident_id,
                    ir.submitted_at,
                    i.ticket_id,
                    i.title,
                    i.priority,
                    s.color as priority_color,
                    u.name as technician_name
                FROM incident_reports ir
                JOIN incidents i ON i.id = ir.incident_id
                JOIN users u ON u.id = ir.technician_id
                LEFT JOIN incident_statuses s ON s.id = i.status_id
                WHERE ir.submitted_at IS NOT NULL 
                  AND ir.validated_at IS NULL
                ORDER BY ir.submitted_at ASC
                LIMIT 10
            ");
            $pendingReports = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (\Throwable $e) {
            error_log("Pending reports error: " . $e->getMessage());
        }

        // Incidents récents
        $recentIncidents = [];
        try {
            $stmt = $pdo->query("
                SELECT 
                    i.id,
                    i.ticket_id,
                    i.title,
                    i.created_at,
                    s.label as status_label,
                    s.color as status_color
                FROM incidents i
                JOIN incident_statuses s ON s.id = i.status_id
                ORDER BY i.created_at DESC
                LIMIT 10
            ");
            $recentIncidents = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (\Throwable $e) {
            error_log("Recent incidents error: " . $e->getMessage());
        }

        $this->view('supervisor/dashboard', [
            'stats' => $stats,
            'pending_reports' => $pendingReports,
            'recent_incidents' => $recentIncidents
        ]);
    }

    /**
     * Vue liste des techniciens avec leurs statistiques
     */
    public function technicians(): void
    {
        if (!Auth::check()) { $this->redirect('/login'); }
        Auth::requireRole(['admin', 'manager', 'superviseur', 'supervisor']);

        $pdo = Database::pdo();
        $photoColumn = $this->detectUserPhotoColumn($pdo);
        $photoSelect = $photoColumn ? 'u.`' . str_replace('`', '', $photoColumn) . '` AS photo_path' : 'NULL AS photo_path';
        
        $technicians = [];
        try {
            $stmt = $pdo->query("
                SELECT 
                    u.id,
                    u.name,
                    u.email,
                    u.phone,
                    u.active,
                    u.takeover_at,
                    {$photoSelect},
                    COALESCE(inc.assigned_incidents, 0) as assigned_incidents,
                    COALESCE(inc.active_incidents, 0) as active_incidents,
                    COALESCE(ftth.assigned_ftth_tickets, 0) as assigned_ftth_tickets,
                    COALESCE(ftth.active_ftth_tickets, 0) as active_ftth_tickets,
                    COALESCE(ftth.awaiting_ftth_validation, 0) as awaiting_ftth_validation
                FROM users u
                LEFT JOIN (
                    SELECT 
                        ia.user_id,
                        COUNT(DISTINCT ia.incident_id) as assigned_incidents,
                        COUNT(DISTINCT CASE WHEN s.key_name NOT IN ('closed', 'cancelled') THEN ia.incident_id END) as active_incidents
                    FROM incident_assignments ia
                    LEFT JOIN incidents i ON i.id = ia.incident_id
                    LEFT JOIN incident_statuses s ON s.id = i.status_id
                    WHERE ia.active IS NULL OR ia.active = 1
                    GROUP BY ia.user_id
                ) inc ON inc.user_id = u.id
                LEFT JOIN (
                    SELECT 
                        a.technician_id,
                        COUNT(DISTINCT a.ticket_id) as assigned_ftth_tickets,
                        COUNT(DISTINCT CASE WHEN t.status NOT IN ('traité', 'validé', 'clôturé') THEN a.ticket_id END) as active_ftth_tickets,
                        COUNT(DISTINCT CASE WHEN r.status = 'soumis' THEN a.ticket_id END) as awaiting_ftth_validation
                    FROM maintenance_ftth_assignments a
                    JOIN maintenance_ftth_tickets t ON t.id = a.ticket_id
                    LEFT JOIN maintenance_ftth_reports r ON r.ticket_id = a.ticket_id
                    GROUP BY a.technician_id
                ) ftth ON ftth.technician_id = u.id
                WHERE u.role_key IN ('technicien', 'technician')
                ORDER BY u.active DESC, u.name ASC
            ");
            $technicians = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
            foreach ($technicians as &$technician) {
                $technician['photo_url'] = $this->resolveUserPhotoUrl($technician['photo_path'] ?? null);
            }
            unset($technician);
        } catch (\Throwable $e) {
            error_log("Technicians list error: " . $e->getMessage());
        }

        $this->view('supervisor/technicians', ['technicians' => $technicians]);
    }

    /**
     * Vue activité d'un technicien spécifique
     */
    public function technicianActivity(): void
    {
        if (!Auth::check()) { $this->redirect('/login'); }
        Auth::requireRole(['admin', 'manager', 'superviseur', 'supervisor']);

        $technicianId = (int)($_GET['id'] ?? 0);
        if (!$technicianId) { $this->redirect('/supervisor/technicians'); }

        $pdo = Database::pdo();
        $photoColumn = $this->detectUserPhotoColumn($pdo);
        $photoSelect = $photoColumn ? '`' . str_replace('`', '', $photoColumn) . '` AS photo_path' : 'NULL AS photo_path';
        
        // Info technicien
        $technician = null;
        try {
            $stmt = $pdo->prepare("SELECT id, name, email, phone, takeover_at, active, {$photoSelect} FROM users WHERE id = ? AND role_key IN ('technicien', 'technician')");
            $stmt->execute([$technicianId]);
            $technician = $stmt->fetch(PDO::FETCH_ASSOC);
            if ($technician) {
                $technician['photo_url'] = $this->resolveUserPhotoUrl($technician['photo_path'] ?? null);
            }
        } catch (\Throwable $e) {
            error_log("Technician info error: " . $e->getMessage());
        }

        if (!$technician) { $this->redirect('/supervisor/technicians'); }

        // Incidents assignés
        $assignments = [];
        try {
            $stmt = $pdo->prepare("
                SELECT 
                    i.id,
                    i.id as incident_number,
                    i.title,
                    i.priority,
                    s.label as status_label,
                    s.color as status_color,
                    ia.assigned_at,
                    i.created_at
                FROM incident_assignments ia
                JOIN incidents i ON i.id = ia.incident_id
                LEFT JOIN incident_statuses s ON s.id = i.status_id
                WHERE ia.user_id = ? AND (ia.active IS NULL OR ia.active = 1)
                ORDER BY ia.assigned_at DESC
                LIMIT 20
            ");
            $stmt->execute([$technicianId]);
            $assignments = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (\Throwable $e) {
            error_log("Assignments error: " . $e->getMessage());
        }

        // Tickets FTTH assignés
        $ftthAssignments = [];
        try {
            $stmt = $pdo->prepare(" 
                SELECT
                    t.id,
                    t.ref_code,
                    t.client_name,
                    t.client_address,
                    t.nature_intervention,
                    t.priority,
                    t.status,
                    a.assigned_at,
                    t.created_at,
                    r.status AS report_status,
                    r.updated_at AS report_updated_at
                FROM maintenance_ftth_assignments a
                JOIN maintenance_ftth_tickets t ON t.id = a.ticket_id
                LEFT JOIN maintenance_ftth_reports r ON r.ticket_id = t.id
                WHERE a.technician_id = ?
                ORDER BY a.assigned_at DESC
                LIMIT 20
            ");
            $stmt->execute([$technicianId]);
            $ftthAssignments = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (\Throwable $e) {
            error_log("FTTH assignments error: " . $e->getMessage());
        }

        // Dernière position
        $lastPosition = null;
        try {
            $stmt = $pdo->prepare("SELECT lat, lng, taken_at FROM user_takeover_events WHERE user_id = ? AND lat IS NOT NULL ORDER BY id DESC LIMIT 1");
            $stmt->execute([$technicianId]);
            $lastPosition = $stmt->fetch(PDO::FETCH_ASSOC);
        } catch (\Throwable $e) {
            error_log("Last position error: " . $e->getMessage());
        }

        $this->view('supervisor/technician_activity', [
            'technician' => $technician,
            'assignments' => $assignments,
            'ftth_assignments' => $ftthAssignments,
            'last_position' => $lastPosition
        ]);
    }
}