<?php
/**
 * Ligne Model
 * Handles bus ligne (route) data
 */

class Ligne {
    private $conn;
    private $table_name = "lignes";

    public $id;
    public $ligne_code;
    public $name;
    public $description;
    public $start_point;
    public $end_point;
    public $distance_km;
    public $estimated_duration;
    public $is_active;
    public $created_at;
    public $updated_at;

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

    /**
     * Get ligne by ID
     */
    public function getById($id) {
        $query = "SELECT * FROM " . $this->table_name . " WHERE id = :id LIMIT 1";

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

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

    /**
     * Get ligne by code
     */
    public function getByCode($ligne_code) {
        $query = "SELECT * FROM " . $this->table_name . " WHERE ligne_code = :ligne_code LIMIT 1";

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

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

    /**
     * Get all lignes
     */
    public function getAll($filters = []) {
        $query = "SELECT l.*, COUNT(b.id) as buses_count
                  FROM " . $this->table_name . " l
                  LEFT JOIN buses b ON l.id = b.id_ligne AND b.status = 'Active'
                  GROUP BY l.id";

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

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

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

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

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

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

        $query .= " ORDER BY l.ligne_code ASC";

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

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

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

    /**
     * Create new ligne
     */
    public function create($data) {
        $query = "INSERT INTO " . $this->table_name . "
                  (ligne_code, name, description, start_point, end_point, distance_km, estimated_duration, is_active)
                  VALUES (:ligne_code, :name, :description, :start_point, :end_point, :distance_km, :estimated_duration, :is_active)";

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

        $stmt->bindParam(':ligne_code', $data['ligne_code']);
        $stmt->bindParam(':name', $data['name']);
        $stmt->bindParam(':description', $data['description'] ?? null);
        $stmt->bindParam(':start_point', $data['start_point']);
        $stmt->bindParam(':end_point', $data['end_point']);
        $stmt->bindParam(':distance_km', $data['distance_km'] ?? null);
        $stmt->bindParam(':estimated_duration', $data['estimated_duration'] ?? null);
        $stmt->bindParam(':is_active', $data['is_active'] ?? true);

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

        return false;
    }

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

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

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

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

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

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

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

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

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

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

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

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

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

        return $stmt->execute();
    }

    /**
     * Delete ligne
     */
    public function delete($id) {
        // Check if ligne has buses
        $query = "SELECT COUNT(*) as count FROM buses WHERE ligne_id = :id";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':id', $id);
        $stmt->execute();
        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($result['count'] > 0) {
            return false; // Cannot delete ligne with buses
        }

        $query = "DELETE FROM " . $this->table_name . " WHERE id = :id";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':id', $id);
        return $stmt->execute();
    }

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

        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 ligne statistics
     */
    public function getStats() {
        $query = "SELECT
                    COUNT(*) as total_lignes,
                    SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) as active_lignes,
                    AVG(distance_km) as avg_distance,
                    AVG(estimated_duration) as avg_duration,
                    SUM(buses_per_ligne) as total_buses_on_lignes
                  FROM (
                      SELECT l.id, l.distance_km, l.estimated_duration, COUNT(b.id) as buses_per_ligne
                      FROM " . $this->table_name . " l
                      LEFT JOIN buses b ON l.id = b.ligne_id AND b.status = 'Active'
                      GROUP BY l.id, l.distance_km, l.estimated_duration
                  ) as ligne_stats";

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

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

    /**
     * Get popular lignes (by number of buses)
     */
    public function getPopularLignes($limit = 10) {
        $query = "SELECT l.*, COUNT(b.id) as buses_count
                  FROM " . $this->table_name . " l
                  LEFT JOIN buses b ON l.id = b.ligne_id AND b.status = 'Active'
                  WHERE l.is_active = 1
                  GROUP BY l.id
                  ORDER BY buses_count DESC
                  LIMIT :limit";

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

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