<?php
declare(strict_types=1);
require_once __DIR__ . '/../lib.php';

// This script should be run via cron job every 15 minutes
// Example cron: */15 * * * * /usr/bin/php /path/to/sms_triggers.php

try {
    $pdo = hw_db();
    
    // 1. Send appointment reminders (1 hour before)
    $stmt = $pdo->query("
        SELECT a.*, u.first_name, u.phone_number, c.display_name as coach_name
        FROM appointments a
        JOIN users u ON a.user_id = u.id
        JOIN user_phone_numbers p ON u.id = p.user_id AND p.is_primary = 1 AND p.sms_enabled = 1
        JOIN coaches coach ON a.coach_id = coach.id
        JOIN users c ON coach.user_id = c.id
        WHERE a.status = 'scheduled' 
        AND a.starts_at BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 1 HOUR)
        AND NOT EXISTS (
            SELECT 1 FROM sms_messages s 
            WHERE s.user_id = a.user_id 
            AND s.message_type = 'appointment' 
            AND s.content LIKE CONCAT('%', DATE_FORMAT(a.starts_at, '%Y-%m-%d'), '%')
        )
    ");
    $upcomingAppointments = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    foreach ($upcomingAppointments as $appointment) {
        $message = "Hi {$appointment['first_name']}! Reminder: You have a wellness session with {$appointment['coach_name']} in 1 hour at " . date('g:i A', strtotime($appointment['starts_at'])) . ". Join here: https://wellness.local/video-session";
        
        // Send SMS
        $stmt = $pdo->prepare("
            INSERT INTO sms_messages (user_id, phone_number, message_type, content, status, sent_at) 
            VALUES (?, ?, 'appointment', ?, 'sent', NOW())
        ");
        $stmt->execute([
            $appointment['user_id'],
            $appointment['phone_number'],
            $message
        ]);
        
        echo "Sent appointment reminder to {$appointment['first_name']} ({$appointment['phone_number']})\n";
    }
    
    // 2. Send membership renewal reminders (3 days before expiry)
    $stmt = $pdo->query("
        SELECT s.*, u.first_name, u.phone_number, p.name as plan_name
        FROM subscriptions s
        JOIN users u ON s.user_id = u.id
        JOIN user_phone_numbers phone ON u.id = phone.user_id AND phone.is_primary = 1 AND phone.sms_enabled = 1
        JOIN membership_plans p ON s.plan_id = p.id
        WHERE s.status = 'active' 
        AND s.ends_at BETWEEN DATE_ADD(NOW(), INTERVAL 3 DAY) AND DATE_ADD(NOW(), INTERVAL 4 DAY)
        AND NOT EXISTS (
            SELECT 1 FROM sms_messages sm 
            WHERE sm.user_id = s.user_id 
            AND sm.message_type = 'renewal' 
            AND sm.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
        )
    ");
    $expiringMemberships = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    foreach ($expiringMemberships as $membership) {
        $message = "Hi {$membership['first_name']}! Your {$membership['plan_name']} membership expires in 3 days. Renew now to continue your wellness journey: https://wellness.local/membership";
        
        // Send SMS
        $stmt = $pdo->prepare("
            INSERT INTO sms_messages (user_id, phone_number, message_type, content, status, sent_at) 
            VALUES (?, ?, 'renewal', ?, 'sent', NOW())
        ");
        $stmt->execute([
            $membership['user_id'],
            $membership['phone_number'],
            $message
        ]);
        
        echo "Sent renewal reminder to {$membership['first_name']} ({$membership['phone_number']})\n";
    }
    
    // 3. Send welcome messages to new users (within 1 hour of registration)
    $stmt = $pdo->query("
        SELECT u.*, p.phone_number
        FROM users u
        JOIN user_phone_numbers p ON u.id = p.user_id AND p.is_primary = 1 AND p.sms_enabled = 1
        WHERE u.created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
        AND NOT EXISTS (
            SELECT 1 FROM sms_messages s 
            WHERE s.user_id = u.id 
            AND s.message_type = 'system'
            AND s.content LIKE '%Welcome%'
        )
    ");
    $newUsers = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    foreach ($newUsers as $user) {
        $message = "Welcome to Herbal Wellness Care, {$user['first_name']}! Your wellness journey starts here. Complete your profile to get personalized recommendations: https://wellness.local/profile";
        
        // Send SMS
        $stmt = $pdo->prepare("
            INSERT INTO sms_messages (user_id, phone_number, message_type, content, status, sent_at) 
            VALUES (?, ?, 'system', ?, 'sent', NOW())
        ");
        $stmt->execute([
            $user['id'],
            $user['phone_number'],
            $message
        ]);
        
        echo "Sent welcome message to {$user['first_name']} ({$user['phone_number']})\n";
    }
    
    echo "SMS triggers completed at " . date('Y-m-d H:i:s') . "\n";
    
} catch (Throwable $e) {
    echo "Error in SMS triggers: " . $e->getMessage() . "\n";
}