<?php
/**
 * Bus Model
 * Handles bus data and QR code operations
 */

class Bus {
    private $conn;
    private $table_name = "buses";

    public $id;
    public $code_bus;
    public $type;
    public $matricule;
    public $description;
    public $modele;
    public $annee_fabrication;
    public $capacite;
    public $id_operateur;
    public $id_ligne;
    public $actif;
    public $status;
    public $qr_code;
    public $notes;
    public $date_creation;
    public $date_modification;

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

    /**
     * Get bus by QR code
     * Recherche par champ qr_code exact de la table buses
     */
    public function getByQRCode($qr_code) {
        $query = "SELECT b.*, o.name as operator_name, l.name as line_name, l.ligne_code
                  FROM " . $this->table_name . " b
                  LEFT JOIN operators o ON b.id_operateur = o.id
                  LEFT JOIN lignes l ON (b.id_ligne = l.id OR b.id_ligne = l.ligne_code)
                  WHERE b.qr_code = :qr_code AND b.status = 'Active'
                  LIMIT 1";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':qr_code', $qr_code);
        $stmt->execute();
        
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    /**
     * Get bus by ID
     */
    public function getById($id) {
        $query = "SELECT b.*, o.name as operator_name, l.name as line_name, l.ligne_code
                  FROM " . $this->table_name . " b
                  LEFT JOIN operators o ON b.id_operateur = o.id
                  LEFT JOIN lignes l ON (b.id_ligne = l.id OR b.id_ligne = l.ligne_code)
                  WHERE b.id = :id
                  LIMIT 1";

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

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

    /**
     * Get all buses with filters
     */
    public function getAll($filters = []) {
        $query = "SELECT b.*, o.name as operator_name, l.name as line_name, l.ligne_code
                  FROM " . $this->table_name . " b
                  LEFT JOIN operators o ON b.id_operateur = o.id
                  LEFT JOIN lignes l ON (b.id_ligne = l.id OR b.id_ligne = l.ligne_code)";

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

        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['status'])) {
            $conditions[] = "b.status = :status";
            $params[':status'] = $filters['status'];
        }

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

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

        $query .= " ORDER BY b.date_creation DESC";

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

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

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

    /**
     * Create new bus
     */
    public function create($data) {
        // Generate QR code from matricule and ligne code
        $qr_code = $this->generateQRCode($data['matricule'], $data['id_ligne']);

        $query = "INSERT INTO " . $this->table_name . "
                  (code_bus, type, matricule, description, modele, annee_fabrication, capacite, id_operateur, id_ligne, actif, qr_code, status, notes)
                  VALUES (:code_bus, :type, :matricule, :description, :modele, :annee_fabrication, :capacite, :id_operateur, :id_ligne, :actif, :qr_code, :status, :notes)";

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

        $stmt->bindParam(':code_bus', $data['code_bus']);
        $stmt->bindParam(':type', $data['type'] ?? null);
        $stmt->bindParam(':matricule', $data['matricule']);
        $stmt->bindParam(':description', $data['description'] ?? null);
        $stmt->bindParam(':modele', $data['modele'] ?? null);
        $stmt->bindParam(':annee_fabrication', $data['annee_fabrication'] ?? null);
        $stmt->bindParam(':capacite', $data['capacite'] ?? 50);
        $stmt->bindParam(':id_operateur', $data['id_operateur']);
        $stmt->bindParam(':id_ligne', $data['id_ligne']);
        $stmt->bindParam(':actif', $data['actif'] ?? 1);
        $stmt->bindParam(':qr_code', $qr_code);
        $stmt->bindParam(':status', $data['status'] ?? 'Active');
        $stmt->bindParam(':notes', $data['notes'] ?? null);

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

        return false;
    }

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

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

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

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

            // Regenerate QR code if matricule changed
            if (isset($data['id_ligne'])) {
                $fields[] = "qr_code = :qr_code";
                $params[':qr_code'] = $this->generateQRCode($data['matricule'], $data['id_ligne']);
            }
        }

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

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

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

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

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

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

            // Regenerate QR code if ligne changed
            if (isset($data['matricule'])) {
                $fields[] = "qr_code = :qr_code";
                $params[':qr_code'] = $this->generateQRCode($data['matricule'], $data['id_ligne']);
            }
        }

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

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

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

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

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

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

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

        return $stmt->execute();
    }

    /**
     * Delete bus
     */
    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();
    }

    /**
     * Generate QR code string
     */
    private function generateQRCode($matricule, $ligne_id) {
        // Get ligne code
        $ligne_query = "SELECT ligne_code FROM lignes WHERE id = :ligne_id LIMIT 1";
        $ligne_stmt = $this->conn->prepare($ligne_query);
        $ligne_stmt->bindParam(':ligne_id', $ligne_id);
        $ligne_stmt->execute();
        $ligne = $ligne_stmt->fetch(PDO::FETCH_ASSOC);

        if ($ligne) {
            return $matricule . '_' . $ligne['ligne_code'];
        }

        return $matricule . '_UNKNOWN';
    }

    /**
     * Check if matricule exists
     */
    public function matriculeExists($matricule, $exclude_id = null) {
        $query = "SELECT id FROM " . $this->table_name . " WHERE matricule = :matricule";
        $params = [':matricule' => $matricule];

        if ($exclude_id) {
            $query .= " AND id != :exclude_id";
            $params[':exclude_id'] = $exclude_id;
        }

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

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

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

    /**
     * Get buses that checked in today
     */
    public function getCheckedInToday($date = null) {
        if (!$date) {
            $date = date('Y-m-d');
        }

        $query = "SELECT DISTINCT b.*, o.name as operator_name, l.name as line_name, l.ligne_code
                  FROM " . $this->table_name . " b
                  LEFT JOIN operators o ON b.id_operateur = o.id
                  LEFT JOIN lignes l ON b.id_ligne = l.id
                  INNER JOIN checkins c ON b.id = c.bus_id
                  WHERE c.checkin_date = :date AND b.actif = 1
                  ORDER BY c.checkin_time DESC";

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

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

    /**
     * Get buses that are absent today
     */
    public function getAbsentToday($date = null) {
        if (!$date) {
            $date = date('Y-m-d');
        }

        $query = "SELECT b.*, o.name as operator_name, l.name as line_name, l.ligne_code
                  FROM " . $this->table_name . " b
                  LEFT JOIN operators o ON b.id_operateur = o.id
                  LEFT JOIN lignes l ON b.id_ligne = l.id
                  WHERE b.actif = 1
                  AND b.id NOT IN (
                      SELECT DISTINCT bus_id FROM checkins
                      WHERE checkin_date = :date
                  )
                  ORDER BY o.name, l.name";

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

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

    /**
     * Get active buses count
     */
    public function getActiveCount() {
        $query = "SELECT COUNT(*) as count FROM " . $this->table_name . " WHERE actif = 1";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return (int)$result['count'];
    }

    /**
     * Get bus statistics
     */
    public function getStats() {
        $query = "SELECT
                    CASE WHEN actif = 1 THEN 'Actif' ELSE 'Inactif' END as status,
                    COUNT(*) as count,
                    COUNT(DISTINCT id_operateur) as operators_count,
                    COUNT(DISTINCT id_ligne) as lignes_count
                  FROM " . $this->table_name . "
                  GROUP BY actif";

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

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