<?php
/**
 * Checkin Model
 * Handles bus check-in operations
 */

class Checkin {
    private $conn;
    private $table_name = "checkins";

    public $id;
    public $bus_id;
    public $agent_id;
    public $checkin_date;
    public $checkin_time;
    public $checkin_datetime;
    public $location;
    public $latitude;
    public $longitude;
    public $notes;
    public $is_valid;
    public $created_at;

    public function __construct($db) {
        $this->conn = $db;
    }

    /**
     * Create new check-in
     */
    public function create($data) {
        // Prevent duplicate check-ins within 30 minutes
        if ($this->hasCheckedInRecently($data['bus_id'])) {
            return false; // Bus checked in too recently (within 30 minutes)
        }

        $query = "INSERT INTO " . $this->table_name . "
                  (bus_id, agent_id, checkin_date, checkin_time, checkin_datetime, location, latitude, longitude, notes, is_valid)
                  VALUES (:bus_id, :agent_id, :checkin_date, :checkin_time, NOW(), :location, :latitude, :longitude, :notes, :is_valid)";

        $stmt = $this->conn->prepare($query);

        // Extract values to variables first (bindParam needs references)
        $bus_id = $data['bus_id'];
        $agent_id = $data['agent_id'];
        $checkin_date = $data['checkin_date'];
        $checkin_time = $data['checkin_time'];
        // Removed: $checkin_datetime = date('Y-m-d H:i:s'); // Now using NOW() in SQL
        $location = $data['location'] ?? 'Boumerdes Bus Station';
        $notes = $data['notes'] ?? null;
        $is_valid = $data['is_valid'] ?? true;

        $stmt->bindParam(':bus_id', $bus_id);
        $stmt->bindParam(':agent_id', $agent_id);
        $stmt->bindParam(':checkin_date', $checkin_date);
        $stmt->bindParam(':checkin_time', $checkin_time);
        $stmt->bindParam(':location', $location);

        // Prioritize explicit latitude/longitude if provided, otherwise parse from location
        $latitude = $data['latitude'] ?? null;
        $longitude = $data['longitude'] ?? null;

        if (($latitude === null || $longitude === null) && isset($data['location']) && preg_match('/GPS:\s*([0-9.-]+),\s*([0-9.-]+)/', $data['location'], $matches)) {
            $latitude = $matches[1];
            $longitude = $matches[2];
        }

        $stmt->bindValue(':latitude', $latitude);
        $stmt->bindValue(':longitude', $longitude);
        $stmt->bindValue(':notes', $notes);
        $stmt->bindValue(':is_valid', $is_valid, PDO::PARAM_BOOL);

        if ($stmt->execute()) {
            return $this->conn->lastInsertId();
        }

        return false;
    }

    /**
     * Check if bus has been checked in within the last 30 minutes
     * (allows multiple check-ins per day with minimum 30-minute intervals)
     */
    public function hasCheckedInRecently($bus_id) {
        // Use MySQL time instead of PHP time to avoid timezone issues
        $query = "SELECT
                    COUNT(*) as recent_checkins,
                    MAX(checkin_datetime) as last_checkin_time,
                    TIMESTAMPDIFF(SECOND, MAX(checkin_datetime), NOW()) as seconds_since_last_checkin
                  FROM " . $this->table_name . "
                  WHERE bus_id = :bus_id
                    AND checkin_datetime > DATE_SUB(NOW(), INTERVAL 30 MINUTE)
                    AND is_valid = 1";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':bus_id', $bus_id);
        $stmt->execute();

        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        // Debug logs
        error_log("=== CHECKING BUS $bus_id FOR RECENT CHECKIN ===");
        error_log("Recent checkins count: " . $result['recent_checkins']);
        error_log("Last checkin time: " . $result['last_checkin_time']);
        error_log("Seconds since last checkin: " . $result['seconds_since_last_checkin']);
        error_log("MySQL NOW(): " . date('Y-m-d H:i:s', strtotime('+1 hour'))); // Approximate MySQL time

        $isBlocked = $result['recent_checkins'] > 0;
        error_log("Final result for bus $bus_id: " . ($isBlocked ? 'BLOCKED' : 'ALLOWED'));
        error_log("=== END CHECK FOR BUS $bus_id ===");

        return $isBlocked;
    }

    /**
     * Check if bus has already checked in today (legacy method for compatibility)
     */
    public function hasCheckedInToday($bus_id, $date = null) {
        if (!$date) {
            $date = date('Y-m-d');
        }

        $query = "SELECT id FROM " . $this->table_name . "
                  WHERE bus_id = :bus_id AND checkin_date = :date AND is_valid = 1
                  LIMIT 1";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':bus_id', $bus_id);
        $stmt->bindParam(':date', $date);
        $stmt->execute();

        return $stmt->rowCount() > 0;
    }

    /**
     * Get check-in by ID
     */
    public function getById($id) {
        $query = "SELECT c.*, b.matricule, b.modele, b.qr_code,
                         o.name as operator_name, l.name as line_name, l.ligne_code,
                         u.full_name as agent_name, u.username as agent_username
                  FROM " . $this->table_name . " c
                  LEFT JOIN buses b ON c.bus_id = b.id
                  LEFT JOIN operators o ON b.id_operateur = o.id
                  LEFT JOIN lignes l ON b.id_ligne = l.id
                  LEFT JOIN agents u ON c.agent_id = u.id
                  WHERE c.id = :id
                  LIMIT 1";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':id', $id);
        $stmt->execute();

        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    /**
     * Get all check-ins with filters
     */
    public function getAll($filters = []) {
        $query = "SELECT c.*, b.matricule, b.modele, b.qr_code,
                         o.name as operator_name, l.name as line_name, l.ligne_code,
                         u.full_name as agent_name, u.username as agent_username
                  FROM " . $this->table_name . " c
                  LEFT JOIN buses b ON c.bus_id = b.id
                  LEFT JOIN operators o ON b.id_operateur = o.id
                  LEFT JOIN lignes l ON b.id_ligne = l.id
                  LEFT JOIN agents u ON c.agent_id = u.id";

        $conditions = [];
        $params = [];

        if (isset($filters['bus_id'])) {
            $conditions[] = "c.bus_id = :bus_id";
            $params[':bus_id'] = $filters['bus_id'];
        }

        if (isset($filters['agent_id'])) {
            $conditions[] = "c.agent_id = :agent_id";
            $params[':agent_id'] = $filters['agent_id'];
        }

        if (isset($filters['date'])) {
            $conditions[] = "c.checkin_date = :date";
            $params[':date'] = $filters['date'];
        }

        if (isset($filters['date_from']) && isset($filters['date_to'])) {
            $conditions[] = "c.checkin_date BETWEEN :date_from AND :date_to";
            $params[':date_from'] = $filters['date_from'];
            $params[':date_to'] = $filters['date_to'];
        }

        if (isset($filters['id_operateur'])) {
            $conditions[] = "b.id_operateur = :id_operateur";
            $params[':id_operateur'] = $filters['id_operateur'];
        }

        if (isset($filters['id_ligne'])) {
            $conditions[] = "b.id_ligne = :id_ligne";
            $params[':id_ligne'] = $filters['id_ligne'];
        }

        if (isset($filters['is_valid'])) {
            $conditions[] = "c.is_valid = :is_valid";
            $params[':is_valid'] = $filters['is_valid'];
        }

        if (!empty($conditions)) {
            $query .= " WHERE " . implode(" AND ", $conditions);
        }

        $query .= " ORDER BY c.checkin_datetime DESC";

        // Add LIMIT for performance
        if (isset($filters['limit'])) {
            $query .= " LIMIT :limit";
            $params[':limit'] = (int)$filters['limit'];
        }

        $stmt = $this->conn->prepare($query);

        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }

        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Update check-in
     */
    public function update($id, $data) {
        $fields = [];
        $params = [':id' => $id];

        if (isset($data['location'])) {
            $fields[] = "location = :location";
            $params[':location'] = $data['location'];
        }

        if (isset($data['latitude'])) {
            $fields[] = "latitude = :latitude";
            $params[':latitude'] = $data['latitude'];
        }

        if (isset($data['longitude'])) {
            $fields[] = "longitude = :longitude";
            $params[':longitude'] = $data['longitude'];
        }

        if (isset($data['notes'])) {
            $fields[] = "notes = :notes";
            $params[':notes'] = $data['notes'];
        }

        if (isset($data['is_valid'])) {
            $fields[] = "is_valid = :is_valid";
            $params[':is_valid'] = $data['is_valid'];
        }

        if (empty($fields)) {
            return false;
        }

        $query = "UPDATE " . $this->table_name . "
                  SET " . implode(", ", $fields) . "
                  WHERE id = :id";

        $stmt = $this->conn->prepare($query);

        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }

        return $stmt->execute();
    }

    /**
     * Delete check-in
     */
    public function delete($id) {
        $query = "DELETE FROM " . $this->table_name . " WHERE id = :id";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':id', $id);
        return $stmt->execute();
    }

    /**
     * Get daily check-in summary
     */
    public function getDailySummary($date = null) {
        if (!$date) {
            $date = date('Y-m-d');
        }

        $query = "SELECT
                    COUNT(DISTINCT c.bus_id) as total_checkins,
                    COUNT(DISTINCT c.agent_id) as total_agents,
                    MIN(c.checkin_time) as first_checkin,
                    MAX(c.checkin_time) as last_checkin,
                    AVG(TIME_TO_SEC(c.checkin_time)) as avg_checkin_time_seconds
                  FROM " . $this->table_name . " c
                  WHERE c.checkin_date = :date AND c.is_valid = 1";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':date', $date);
        $stmt->execute();

        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        // Convert average seconds back to time format
        if ($result['avg_checkin_time_seconds']) {
            $hours = floor($result['avg_checkin_time_seconds'] / 3600);
            $minutes = floor(($result['avg_checkin_time_seconds'] % 3600) / 60);
            $seconds = $result['avg_checkin_time_seconds'] % 60;
            $result['avg_checkin_time'] = sprintf('%02d:%02d:%02d', $hours, $minutes, $seconds);
        }

        return $result;
    }

    /**
     * Get check-ins by hour for a specific date
     */
    public function getCheckinsByHour($date = null) {
        if (!$date) {
            $date = date('Y-m-d');
        }

        $query = "SELECT
                    HOUR(c.checkin_time) as hour,
                    COUNT(*) as count
                  FROM " . $this->table_name . " c
                  WHERE c.checkin_date = :date AND c.is_valid = 1
                  GROUP BY HOUR(c.checkin_time)
                  ORDER BY hour";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':date', $date);
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Get check-in statistics for a date range
     */
    public function getStats($date_from = null, $date_to = null) {
        if (!$date_from) {
            $date_from = date('Y-m-d', strtotime('-30 days'));
        }
        if (!$date_to) {
            $date_to = date('Y-m-d');
        }

        $query = "SELECT
                    c.checkin_date,
                    COUNT(DISTINCT c.bus_id) as buses_checked_in,
                    COUNT(DISTINCT c.agent_id) as agents_active,
                    COUNT(*) as total_checkins
                  FROM " . $this->table_name . " c
                  WHERE c.checkin_date BETWEEN :date_from AND :date_to AND c.is_valid = 1
                  GROUP BY c.checkin_date
                  ORDER BY c.checkin_date DESC";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':date_from', $date_from);
        $stmt->bindParam(':date_to', $date_to);
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Get scanned buses count for a specific date
     */
    public function getScannedCountForDate($date = null) {
        if (!$date) {
            $date = date('Y-m-d');
        }

        $query = "SELECT COUNT(DISTINCT bus_id) as count FROM " . $this->table_name . "
                  WHERE checkin_date = :date AND is_valid = 1";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':date', $date);
        $stmt->execute();

        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return (int)$result['count'];
    }

    /**
     * Get most active agents
     */
    public function getMostActiveAgents($date_from = null, $date_to = null, $limit = 10) {
        if (!$date_from) {
            $date_from = date('Y-m-d', strtotime('-30 days'));
        }
        if (!$date_to) {
            $date_to = date('Y-m-d');
        }

        $query = "SELECT
                    a.id, a.username, a.full_name,
                    COUNT(c.id) as total_checkins,
                    COUNT(DISTINCT c.checkin_date) as active_days,
                    MAX(c.checkin_datetime) as last_checkin
                  FROM agents a
                  LEFT JOIN " . $this->table_name . " c ON a.id = c.agent_id AND c.checkin_date BETWEEN :date_from AND :date_to AND c.is_valid = 1
                  WHERE a.role = 'Agent' AND a.is_active = 1
                  GROUP BY a.id, a.username, a.full_name
                  HAVING total_checkins > 0
                  ORDER BY total_checkins DESC
                  LIMIT :limit";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':date_from', $date_from);
        $stmt->bindParam(':date_to', $date_to);
        $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}
?>
