<?php
declare(strict_types=1);

require_once __DIR__ . '/../vendor/autoload.php';

use App\Config\Database;
use Dotenv\Dotenv;

// Load env
$dotenv = Dotenv::createImmutable(__DIR__ . '/../');
$dotenv->load();

$db = Database::getInstance();

echo "Starting Payment Reminder Job [" . date('Y-m-d H:i:s') . "]\n";

// Find overdue invoices
$today = date('Y-m-d');
$sql = "SELECT i.*, c.name as customer_name, c.email as customer_email, t.company_name as tenant_name
        FROM invoices i 
        JOIN customers c ON i.customer_id = c.id
        JOIN tenants t ON i.tenant_id = t.id
        WHERE i.due_date < ? AND i.balance_due > 0 AND i.status NOT IN ('cancelled', 'paid')";

$stmt = $db->prepare($sql);
$stmt->execute([$today]);
$overdueInvoices = $stmt->fetchAll();

foreach ($overdueInvoices as $inv) {
    echo "Processing Invoice: {$inv['invoice_number']} for {$inv['customer_name']}\n";

    // Log Notification in DB
    $notif = $db->prepare("INSERT INTO notifications (tenant_id, type, title, message, link) VALUES (?, 'overdue', ?, ?, ?)");
    $title = "Invoice Overdue: " . $inv['invoice_number'];
    $msg = "Customer {$inv['customer_name']} is yet to pay " . $inv['balance_due'];
    $link = "#/invoices/" . $inv['id'];
    $notif->execute([$inv['tenant_id'], $title, $msg, $link]);

    // Update last reminder flag
    $db->prepare("UPDATE invoices SET last_reminder_sent_at = NOW() WHERE id = ?")->execute([$inv['id']]);

    // PHPMailer logic would go here in actual implementation
}

echo "Job Finished. Processed " . count($overdueInvoices) . " invoices.\n";
