<?php
namespace Models;

class Service {
    private $db;
    
    public function __construct($db) {
        $this->db = $db;
    }
    
    public function findByCompany($companyId) {
        return $this->db->exec(
            'SELECT s.*, v.registration_number, v.make, v.model, c.name as customer_name, c.phone as customer_phone 
             FROM services s 
             INNER JOIN vehicles v ON s.vehicle_id = v.id 
             INNER JOIN customers c ON v.customer_id = c.id 
             WHERE c.company_id = ? 
             ORDER BY s.next_service_date ASC',
            [$companyId]
        );
    }
    
    public function findById($id) {
        $result = $this->db->exec(
            'SELECT s.*, v.registration_number, v.make, v.model, c.name as customer_name, c.phone as customer_phone 
             FROM services s 
             INNER JOIN vehicles v ON s.vehicle_id = v.id 
             INNER JOIN customers c ON v.customer_id = c.id 
             WHERE s.id = ?',
            [$id]
        );
        return $result ? $result[0] : null;
    }
    
    public function findByVehicle($vehicleId) {
        return $this->db->exec('SELECT * FROM services WHERE vehicle_id = ? ORDER BY service_date DESC', [$vehicleId]);
    }
    
    public function findTodayByCompany($companyId) {
        return $this->db->exec(
            'SELECT s.*, v.registration_number, v.make, v.model, c.name as customer_name, c.phone as customer_phone 
             FROM services s 
             INNER JOIN vehicles v ON s.vehicle_id = v.id 
             INNER JOIN customers c ON v.customer_id = c.id 
             WHERE c.company_id = ? AND s.next_service_date = CURRENT_DATE 
             ORDER BY c.name',
            [$companyId]
        );
    }
    
    public function findOverdueByCompany($companyId) {
        return $this->db->exec(
            'SELECT s.*, v.registration_number, v.make, v.model, c.name as customer_name, c.phone as customer_phone 
             FROM services s 
             INNER JOIN vehicles v ON s.vehicle_id = v.id 
             INNER JOIN customers c ON v.customer_id = c.id 
             WHERE c.company_id = ? AND s.next_service_date < CURRENT_DATE 
             ORDER BY s.next_service_date ASC',
            [$companyId]
        );
    }
    
    public function findUpcomingByCompany($companyId, $days = 7) {
        return $this->db->exec(
            "SELECT s.*, v.registration_number, v.make, v.model, c.name as customer_name, c.phone as customer_phone 
             FROM services s 
             INNER JOIN vehicles v ON s.vehicle_id = v.id 
             INNER JOIN customers c ON v.customer_id = c.id 
             WHERE c.company_id = ? AND s.next_service_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '$days days' 
             ORDER BY s.next_service_date ASC",
            [$companyId]
        );
    }
    
    public function create($data) {
        $this->db->exec(
            'INSERT INTO services (vehicle_id, service_type, service_date, next_service_date, mileage, notes, cost) VALUES (?, ?, ?, ?, ?, ?, ?)',
            [$data['vehicle_id'], $data['service_type'], $data['service_date'], $data['next_service_date'] ?? null, $data['mileage'] ?? null, $data['notes'] ?? null, $data['cost'] ?? 0]
        );
        return $this->db->lastinsertid();
    }
    
    public function update($id, $data) {
        $sets = [];
        $params = [];
        
        foreach (['vehicle_id', 'service_type', 'service_date', 'next_service_date', 'mileage', 'notes', 'cost'] as $field) {
            if (isset($data[$field])) {
                $sets[] = "$field = ?";
                $params[] = $data[$field];
            }
        }
        
        $sets[] = "updated_at = CURRENT_TIMESTAMP";
        $params[] = $id;
        
        $this->db->exec('UPDATE services SET ' . implode(', ', $sets) . ' WHERE id = ?', $params);
    }
    
    public function delete($id) {
        $this->db->exec('DELETE FROM services WHERE id = ?', [$id]);
    }
    
    public function countByCompany($companyId) {
        $result = $this->db->exec(
            'SELECT COUNT(*) as count FROM services s INNER JOIN vehicles v ON s.vehicle_id = v.id INNER JOIN customers c ON v.customer_id = c.id WHERE c.company_id = ?',
            [$companyId]
        );
        return $result[0]['count'];
    }
    
    public function countTodayByCompany($companyId) {
        $result = $this->db->exec(
            'SELECT COUNT(*) as count FROM services s INNER JOIN vehicles v ON s.vehicle_id = v.id INNER JOIN customers c ON v.customer_id = c.id WHERE c.company_id = ? AND s.next_service_date = CURRENT_DATE',
            [$companyId]
        );
        return $result[0]['count'];
    }
    
    public function countOverdueByCompany($companyId) {
        $result = $this->db->exec(
            'SELECT COUNT(*) as count FROM services s INNER JOIN vehicles v ON s.vehicle_id = v.id INNER JOIN customers c ON v.customer_id = c.id WHERE c.company_id = ? AND s.next_service_date < CURRENT_DATE',
            [$companyId]
        );
        return $result[0]['count'];
    }
    
    public function countAll() {
        $result = $this->db->exec('SELECT COUNT(*) as count FROM services');
        return $result[0]['count'];
    }
}
