<?php
namespace Models;

class Reminder {
    private $db;
    
    public function __construct($db) {
        $this->db = $db;
    }
    
    public function findPendingByCompany($companyId) {
        return $this->db->exec(
            'SELECT r.*, s.service_type, s.next_service_date, v.registration_number, v.make, v.model, c.name as customer_name, c.phone as customer_phone, c.company_id 
             FROM reminders r 
             INNER JOIN services s ON r.service_id = s.id 
             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 r.status != ? 
             ORDER BY s.next_service_date ASC',
            [$companyId, 'sent']
        );
    }
    
    public function findById($id) {
        $result = $this->db->exec(
            'SELECT r.*, s.service_type, s.next_service_date, v.registration_number, v.make, v.model, c.name as customer_name, c.phone as customer_phone, c.company_id 
             FROM reminders r 
             INNER JOIN services s ON r.service_id = s.id 
             INNER JOIN vehicles v ON s.vehicle_id = v.id 
             INNER JOIN customers c ON v.customer_id = c.id 
             WHERE r.id = ?',
            [$id]
        );
        return $result ? $result[0] : null;
    }
    
    public function findByService($serviceId) {
        return $this->db->exec('SELECT * FROM reminders WHERE service_id = ? ORDER BY scheduled_date DESC', [$serviceId]);
    }
    
    public function create($data) {
        $this->db->exec(
            'INSERT INTO reminders (service_id, scheduled_date, reminder_type, message, status) VALUES (?, ?, ?, ?, ?)',
            [$data['service_id'], $data['scheduled_date'], $data['reminder_type'] ?? 'service_due', $data['message'] ?? null, 'pending']
        );
        return $this->db->lastinsertid();
    }
    
    public function updateStatus($id, $status) {
        $this->db->exec('UPDATE reminders SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?', [$status, $id]);
    }
    
    public function updateTypeAndStatus($id, $type, $status) {
        $this->db->exec('UPDATE reminders SET reminder_type = ?, status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?', [$type, $status, $id]);
    }
    
    public function findByCompanyWithFilter($companyId, $type = null) {
        $sql = 'SELECT r.*, s.service_type, s.next_service_date, v.registration_number, v.make, v.model, c.name as customer_name, c.phone as customer_phone, c.company_id 
                FROM reminders r 
                INNER JOIN services s ON r.service_id = s.id 
                INNER JOIN vehicles v ON s.vehicle_id = v.id 
                INNER JOIN customers c ON v.customer_id = c.id 
                WHERE c.company_id = ?';
        $params = [$companyId];
        
        if ($type === 'whatsapp') {
            $sql .= ' AND r.reminder_type = ?';
            $params[] = 'whatsapp';
        } elseif ($type === 'call') {
            $sql .= ' AND r.reminder_type = ?';
            $params[] = 'call';
        }
        
        $sql .= ' ORDER BY r.status ASC, s.next_service_date ASC';
        
        return $this->db->exec($sql, $params);
    }
    
    public function delete($id) {
        $this->db->exec('DELETE FROM reminders WHERE id = ?', [$id]);
    }
    
    public function logAction($reminderId, $actionType, $userId, $notes = null) {
        try {
            $this->db->exec(
                'INSERT INTO reminder_logs (reminder_id, action_type, performed_by, notes, created_at) VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP)',
                [$reminderId, $actionType, $userId, $notes]
            );
        } catch (\Exception $e) {
            $this->db->exec(
                'INSERT INTO activity_logs (user_id, action, description, created_at) VALUES (?, ?, ?, CURRENT_TIMESTAMP)',
                [$userId, $actionType, json_encode(['reminder_id' => $reminderId, 'notes' => $notes])]
            );
        }
    }
    
    public function countPendingByCompany($companyId) {
        $result = $this->db->exec(
            'SELECT COUNT(*) as count FROM reminders r INNER JOIN services s ON r.service_id = s.id 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 r.status = ?',
            [$companyId, 'pending']
        );
        return $result[0]['count'];
    }
    
    public function countAll() {
        $result = $this->db->exec('SELECT COUNT(*) as count FROM reminders');
        return $result[0]['count'];
    }
    
    public function createFromService($serviceId, $nextServiceDate) {
        $scheduledDate = date('Y-m-d', strtotime($nextServiceDate . ' -7 days'));
        return $this->create([
            'service_id' => $serviceId,
            'scheduled_date' => $scheduledDate,
            'reminder_type' => 'service_due',
            'message' => 'Your domain service is due soon'
        ]);
    }
}
