<?php


define('RUNNING_CRON', true);
require_once __DIR__ . '/../api/config/db.php';
require_once __DIR__ . '/../api/config/mailer.php';
require_once __DIR__ . '/../api/emails/templates.php';

$startTime = microtime(true);
$todayStr  = date('Y-m-d');
$nowStr    = date('Y-m-d H:i:s');

echo "[{$nowStr}] Email cron starting...\n";

try {
    $db = getDB();

    // ── Check email is enabled ───────────────────────────────
    $cfgStmt = $db->query("SELECT setting_key, setting_value FROM settings WHERE setting_group = 'email'");
    $cfg = [];
    foreach ($cfgStmt->fetchAll() as $r) $cfg[$r['setting_key']] = $r['setting_value'];

    if (empty($cfg['email_enabled']) || $cfg['email_enabled'] !== '1') {
        echo "[{$nowStr}] Email sending disabled in settings. Exiting.\n";
        if (defined("RUNNING_AS_INCLUDE")) throw new CronExitException(0); else exit(0);
    }
    if (empty($cfg['email_smtp_host']) || empty($cfg['email_smtp_user'])) {
        echo "[{$nowStr}] SMTP not configured. Exiting.\n";
        if (defined("RUNNING_AS_INCLUDE")) throw new CronExitException(0); else exit(0);
    }

    // ── Step 1: Queue checklist due-today reminders ──────────
    _queueChecklistReminders($db, $cfg, $todayStr);

    // ── Step 1b: Queue daily digest emails ───────────────────
    _queueDailyDigests($db, $cfg, $todayStr);

    // ── Step 2: Process pending queue ───────────────────────
    $mailer = new EWGMailer([
        'host'       => $cfg['email_smtp_host'],
        'port'       => $cfg['email_smtp_port'] ?? 587,
        'secure'     => $cfg['email_smtp_secure'] ?? 'tls',
        'user'       => $cfg['email_smtp_user'],
        'pass'       => $cfg['email_smtp_pass'],
        'from_email' => $cfg['email_from_address'],
        'from_name'  => $cfg['email_from_name'] ?? 'Elegant Work',
    ]);

    // Fetch up to 20 pending emails (avoid timeout on large backlog)
    $stmt = $db->query("
        SELECT * FROM email_queue
        WHERE status = 'pending' AND attempts < 3
        ORDER BY created_at ASC
        LIMIT 20
    ");
    $queue = $stmt->fetchAll();

    if (!$queue) {
        echo "[{$nowStr}] No pending emails in queue.\n";
    }

    $sent = $failed = 0;
    foreach ($queue as $item) {
        // Mark as sending (prevents duplicate sends on parallel cron)
        $db->prepare("UPDATE email_queue SET status='sending', attempts=attempts+1, last_attempt=NOW() WHERE id=?")->execute([$item['id']]);

        $ok = $mailer->send(
            $item['recipient_email'],
            $item['recipient_name'] ?? '',
            $item['subject'],
            $item['body_html'],
            $item['attachment_path'] ?: null,
            $item['attachment_name'] ?: null
        );

        if ($ok) {
            $db->prepare("UPDATE email_queue SET status='sent', sent_at=NOW() WHERE id=?")->execute([$item['id']]);
            $db->prepare("INSERT INTO email_log (queue_id, notification_type, recipient_email, subject, status) VALUES (?,?,?,?,'sent')")
               ->execute([$item['id'], $item['notification_type'], $item['recipient_email'], $item['subject']]);
            // Clean up temp PDF attachment after successful send
            if (!empty($item['attachment_path']) && file_exists($item['attachment_path']) && strpos($item['attachment_path'], sys_get_temp_dir()) === 0) {
                @unlink($item['attachment_path']);
            }
            echo "[" . date('Y-m-d H:i:s') . "] ✓ Sent [{$item['notification_type']}] to {$item['recipient_email']}\n";
            $sent++;
        } else {
            $err = $mailer->lastError;
            $db->prepare("UPDATE email_queue SET status='failed', error_message=? WHERE id=?")->execute([$err, $item['id']]);
            // Reset to pending if retries remain
            if ($item['attempts'] < 2) {
                $db->prepare("UPDATE email_queue SET status='pending' WHERE id=?")->execute([$item['id']]);
            }
            $db->prepare("INSERT INTO email_log (queue_id, notification_type, recipient_email, subject, status, error_message) VALUES (?,?,?,?,'failed',?)")
               ->execute([$item['id'], $item['notification_type'], $item['recipient_email'], $item['subject'], $err]);
            echo "[" . date('Y-m-d H:i:s') . "] ✗ Failed [{$item['notification_type']}] to {$item['recipient_email']}: {$err}\n";
            $failed++;
        }
    }

    $elapsed = round(microtime(true) - $startTime, 2);
    echo "[" . date('Y-m-d H:i:s') . "] Done. Sent: {$sent}, Failed: {$failed}. ({$elapsed}s)\n";

} catch (Exception $e) {
    echo "[" . date('Y-m-d H:i:s') . "] FATAL: " . $e->getMessage() . "\n";
    if (defined("RUNNING_AS_INCLUDE")) throw new CronExitException(1); else exit(1);
}

// ── Queue checklist due-today reminders ──────────────────
function _queueChecklistReminders(PDO $db, array $cfg, string $todayStr): void {
    $adminEmail = $cfg['email_admin_address'] ?? '';
    $appUrl     = $cfg['email_app_url'] ?? '';

    $dueRule    = _getRule($db, 'checklist_due');
    $missedRule = _getRule($db, 'checklist_missed');

    // ── Due today ────────────────────────────────────────────
    // Guard: only queue due reminders once per day
    $dueDoneToday = $db->prepare("
        SELECT COUNT(*) FROM email_queue
        WHERE notification_type = 'checklist_due'
          AND DATE(created_at) = ?
    ");
    $dueDoneToday->execute([$todayStr]);

    if ($dueRule && $dueRule['is_enabled'] && (int)$dueDoneToday->fetchColumn() === 0) {
        $stmt = $db->query("
            SELECT ci.*, ct.name AS template_name, ct.linked_type,
                   u.email AS assignee_email, u.full_name AS assignee_name,
                   v.registration AS vehicle_reg, CONCAT(v.make,' ',v.model) AS vehicle_make,
                   (SELECT COUNT(*) FROM checklist_instance_items WHERE instance_id=ci.id) AS total_items,
                   (SELECT COUNT(*) FROM checklist_instance_items WHERE instance_id=ci.id AND (is_checked=1 OR answer_text IS NOT NULL)) AS answered_items
            FROM checklist_instances ci
            JOIN checklist_templates ct ON ct.id = ci.template_id
            LEFT JOIN users u ON u.id = ci.user_id
            LEFT JOIN fleet_vehicles v ON v.id = ci.vehicle_id
            WHERE ci.due_date = '{$todayStr}'
              AND ci.status IN ('pending','in_progress')
        ");
        foreach ($stmt->fetchAll() as $inst) {
            $recipients = _buildRuleRecipients($db, $dueRule, $adminEmail, $inst['user_id'] ?? null);
            foreach ($recipients as $r) {
                // Avoid duplicate if assignee matches a role recipient
                $html = tplChecklistDue($inst, $r['name'], $appUrl);
                queueEmail($db, 'checklist_due', $r['email'], $r['name'],
                    "📋 Checklist Due Today: " . $inst['template_name'], $html, 'checklist', $inst['id']);
            }
            // Also notify assigned person if enabled and not already in recipients
            if ($dueRule['notify_assigned_tech'] && !empty($inst['assignee_email'])) {
                $alreadySent = array_filter($recipients, fn($r) => $r['email'] === $inst['assignee_email']);
                if (empty($alreadySent)) {
                    $html = tplChecklistDue($inst, $inst['assignee_name'], $appUrl);
                    queueEmail($db, 'checklist_due', $inst['assignee_email'], $inst['assignee_name'],
                        "📋 Checklist Due Today: " . $inst['template_name'], $html, 'checklist', $inst['id']);
                }
            }
        }
        echo "[" . date('Y-m-d H:i:s') . "] Checklist due reminders queued.\n";
    }

    // ── Missed / overdue ─────────────────────────────────────
    // Guard: only queue missed alerts once per day
    $missedDoneToday = $db->prepare("
        SELECT COUNT(*) FROM email_queue
        WHERE notification_type = 'checklist_missed'
          AND DATE(created_at) = ?
    ");
    $missedDoneToday->execute([$todayStr]);

    if ($missedRule && $missedRule['is_enabled'] && (int)$missedDoneToday->fetchColumn() === 0) {
        // Fetch ALL currently-missed instances (not just yesterday) that haven't had an alert sent yet today
        $stmt = $db->query("
            SELECT ci.*, ct.name AS template_name,
                   u.email AS assignee_email, u.full_name AS assignee_name,
                   v.registration AS vehicle_reg
            FROM checklist_instances ci
            JOIN checklist_templates ct ON ct.id = ci.template_id
            LEFT JOIN users u ON u.id = ci.user_id
            LEFT JOIN fleet_vehicles v ON v.id = ci.vehicle_id
            WHERE ci.status = 'missed'
              AND ci.due_date < '{$todayStr}'
        ");
        $count = 0;
        foreach ($stmt->fetchAll() as $inst) {
            $recipients = _buildRuleRecipients($db, $missedRule, $adminEmail, $inst['user_id'] ?? null);
            foreach ($recipients as $r) {
                $html = tplChecklistMissed($inst, $r['name'], $appUrl);
                queueEmail($db, 'checklist_missed', $r['email'], $r['name'],
                    "⚠️ Overdue Checklist: " . $inst['template_name'], $html, 'checklist', $inst['id']);
                $count++;
            }
            // Also notify assigned person if enabled and not already in recipients
            if ($missedRule['notify_assigned_tech'] && !empty($inst['assignee_email'])) {
                $alreadySent = array_filter($recipients, fn($r) => $r['email'] === $inst['assignee_email']);
                if (empty($alreadySent)) {
                    $html = tplChecklistMissed($inst, $inst['assignee_name'], $appUrl);
                    queueEmail($db, 'checklist_missed', $inst['assignee_email'], $inst['assignee_name'],
                        "⚠️ Overdue Checklist: " . $inst['template_name'], $html, 'checklist', $inst['id']);
                    $count++;
                }
            }
        }
        echo "[" . date('Y-m-d H:i:s') . "] Missed checklist alerts queued: {$count} email(s).\n";
    }
}

// ── Build recipient list from rule's notify_role_ids + admin email ────────
function _buildRuleRecipients(PDO $db, array $rule, string $adminEmail, ?int $assignedUserId = null): array {
    $recipients = [];
    $seen       = [];

    $add = function(string $email, string $name) use (&$recipients, &$seen) {
        $email = trim($email);
        if (!$email || !filter_var($email, FILTER_VALIDATE_EMAIL)) return;
        if (in_array($email, $seen)) return;
        $seen[]       = $email;
        $recipients[] = ['email' => $email, 'name' => $name];
    };

    // Role-based recipients
    if (!empty($rule['notify_role_ids'])) {
        $roleIds = array_filter(array_map('intval', explode(',', $rule['notify_role_ids'])));
        if ($roleIds) {
            $in    = implode(',', $roleIds);
            $users = $db->query("SELECT email, full_name FROM users WHERE role_id IN ({$in}) AND is_active=1 AND email IS NOT NULL AND email != ''")->fetchAll();
            foreach ($users as $u) $add($u['email'], $u['full_name']);
        }
    } elseif ($rule['notify_admin'] && $adminEmail) {
        // Fallback: if no role IDs set but notify_admin is on, use admin email
        $add($adminEmail, 'Admin');
    }

    // Custom recipients
    if (!empty($rule['custom_recipients'])) {
        foreach (array_filter(array_map('trim', explode(',', $rule['custom_recipients']))) as $em) {
            $add($em, '');
        }
    }

    return $recipients;
}

function _getRule(PDO $db, string $type): ?array {
    $s = $db->prepare("SELECT * FROM email_notification_rules WHERE event_type = ? LIMIT 1");
    $s->execute([$type]);
    $r = $s->fetch();
    return $r ?: null;
}
// ── Daily Digest ─────────────────────────────────────────────
function _queueDailyDigests(PDO $db, array $cfg, string $todayStr): void {
    // Only once per day
    $check = $db->prepare("SELECT COUNT(*) FROM email_queue WHERE notification_type='daily_digest' AND DATE(created_at)=?");
    $check->execute([$todayStr]);
    if ((int)$check->fetchColumn() > 0) return;

    $appUrl      = $cfg['email_app_url'] ?? '';
    $companyName = $db->query("SELECT setting_value FROM settings WHERE setting_key='company_name' LIMIT 1")->fetchColumn() ?: 'Elegant Work';
    $dateLabel   = date('l, d F Y', strtotime($todayStr));

    // Get all active users with an email
    $users = $db->query("SELECT id, full_name, email FROM users WHERE is_active=1 AND email IS NOT NULL AND email != ''")->fetchAll();

    foreach ($users as $u) {
        $uid   = (int)$u['id'];
        $name  = $u['full_name'] ?: $u['email'];
        $sections = [];
        $missed   = [];

        // ── Today's job cards (assigned to or tech on) ───────
        $jcStmt = $db->prepare("
            SELECT jc.job_number, jc.title, jc.status, jc.priority, jc.site_name
            FROM job_cards jc
            LEFT JOIN job_card_technicians jct ON jct.job_card_id = jc.id
            WHERE jc.scheduled_date = ?
              AND jc.status NOT IN ('completed','invoiced','no_charge','cancelled')
              AND (jc.assigned_to = ? OR jct.user_id = ?)
            GROUP BY jc.id
            ORDER BY jc.scheduled_time
        ");
        $jcStmt->execute([$todayStr, $uid, $uid]);
        $jcItems = [];
        foreach ($jcStmt->fetchAll() as $jc) {
            $jcItems[] = [
                'label'  => $jc['job_number'] . ' — ' . $jc['title'],
                'sub'    => ($jc['site_name'] ?? '') . ' · ' . ucwords(str_replace('_',' ',$jc['status'])),
                'urgent' => $jc['priority'] === 'urgent',
            ];
        }
        if ($jcItems) $sections[] = ['title'=>'Job Cards', 'icon'=>'🔧', 'color'=>'#1e40af', 'items'=>$jcItems];

        // ── Today's meetings ──────────────────────────────────
        $mStmt = $db->prepare("
            SELECT m.title, m.type, m.start_time, m.location
            FROM meetings m
            WHERE m.meeting_date = ?
              AND m.status NOT IN ('completed','cancelled')
              AND (m.created_by = ? OR EXISTS (SELECT 1 FROM meeting_attendees ma WHERE ma.meeting_id=m.id AND ma.user_id=?))
            ORDER BY m.start_time
        ");
        $mStmt->execute([$todayStr, $uid, $uid]);
        $mtgItems = [];
        foreach ($mStmt->fetchAll() as $m) {
            $time = $m['start_time'] ? date('H:i', strtotime($m['start_time'])) . ' · ' : '';
            $mtgItems[] = [
                'label' => $m['title'],
                'sub'   => $time . ucfirst($m['type']) . ($m['location'] ? ' · ' . $m['location'] : ''),
            ];
        }
        if ($mtgItems) $sections[] = ['title'=>'Meetings', 'icon'=>'👥', 'color'=>'#1d4ed8', 'items'=>$mtgItems];

        // ── Today's tasks ─────────────────────────────────────
        $tStmt = $db->prepare("
            SELECT title, priority, location
            FROM meetings
            WHERE type='task' AND meeting_date=? AND status='scheduled' AND created_by=?
            ORDER BY priority DESC
        ");
        $tStmt->execute([$todayStr, $uid]);
        $taskItems = [];
        foreach ($tStmt->fetchAll() as $t) {
            $taskItems[] = [
                'label'  => $t['title'],
                'sub'    => ucfirst($t['priority']) . ($t['location'] ? ' · ' . $t['location'] : ''),
                'urgent' => in_array($t['priority'], ['urgent','high']),
            ];
        }
        if ($taskItems) $sections[] = ['title'=>'Tasks', 'icon'=>'✅', 'color'=>'#6b21a8', 'items'=>$taskItems];

        // ── Today's reminders ─────────────────────────────────
        $rStmt = $db->prepare("
            SELECT title, start_time
            FROM meetings
            WHERE type='reminder' AND meeting_date=? AND status='scheduled' AND created_by=?
        ");
        $rStmt->execute([$todayStr, $uid]);
        $remItems = [];
        foreach ($rStmt->fetchAll() as $r) {
            $time = $r['start_time'] ? date('H:i', strtotime($r['start_time'])) : '';
            $remItems[] = ['label' => $r['title'], 'sub' => $time];
        }
        if ($remItems) $sections[] = ['title'=>'Reminders', 'icon'=>'🔔', 'color'=>'#a16207', 'items'=>$remItems];

        // ── Today's checklists ────────────────────────────────
        $clStmt = $db->prepare("
            SELECT ct.name, ci.status
            FROM checklist_instances ci
            JOIN checklist_templates ct ON ct.id=ci.template_id
            WHERE ci.due_date=? AND ci.status IN ('pending','in_progress')
              AND (ci.user_id=? OR ct.assigned_to=?)
        ");
        $clStmt->execute([$todayStr, $uid, $uid]);
        $clItems = [];
        foreach ($clStmt->fetchAll() as $cl) {
            $clItems[] = ['label' => $cl['name'], 'sub' => ucwords(str_replace('_',' ',$cl['status']))];
        }
        if ($clItems) $sections[] = ['title'=>'Checklists', 'icon'=>'📋', 'color'=>'#065f46', 'items'=>$clItems];

        // ── Missed / overdue ──────────────────────────────────
        // Overdue job cards
        $ojcStmt = $db->prepare("
            SELECT jc.job_number, jc.title, jc.scheduled_date
            FROM job_cards jc
            LEFT JOIN job_card_technicians jct ON jct.job_card_id=jc.id
            WHERE jc.scheduled_date < ? AND jc.status NOT IN ('completed','invoiced','no_charge','cancelled')
              AND (jc.assigned_to=? OR jct.user_id=?)
            GROUP BY jc.id ORDER BY jc.scheduled_date DESC LIMIT 5
        ");
        $ojcStmt->execute([$todayStr, $uid, $uid]);
        foreach ($ojcStmt->fetchAll() as $jc) {
            $missed[] = ['label' => '🔧 ' . $jc['job_number'] . ' — ' . $jc['title'], 'sub' => 'Job card overdue since ' . date('d M', strtotime($jc['scheduled_date']))];
        }

        // Missed checklists
        $oclStmt = $db->prepare("
            SELECT ct.name, ci.due_date
            FROM checklist_instances ci
            JOIN checklist_templates ct ON ct.id=ci.template_id
            WHERE ci.status='missed' AND ci.due_date >= DATE_SUB(?,INTERVAL 7 DAY)
              AND (ci.user_id=? OR ct.assigned_to=?)
            ORDER BY ci.due_date DESC LIMIT 5
        ");
        $oclStmt->execute([$todayStr, $uid, $uid]);
        foreach ($oclStmt->fetchAll() as $cl) {
            $missed[] = ['label' => '📋 ' . $cl['name'], 'sub' => 'Checklist missed on ' . date('d M', strtotime($cl['due_date']))];
        }

        // Overdue tasks
        $otStmt = $db->prepare("
            SELECT title, meeting_date FROM meetings
            WHERE type='task' AND meeting_date < ? AND status='scheduled' AND created_by=?
            ORDER BY meeting_date DESC LIMIT 5
        ");
        $otStmt->execute([$todayStr, $uid]);
        foreach ($otStmt->fetchAll() as $t) {
            $missed[] = ['label' => '✅ ' . $t['title'], 'sub' => 'Task overdue since ' . date('d M', strtotime($t['meeting_date']))];
        }

        // Only send if there's something to show
        if (empty($sections) && empty($missed)) continue;

        $html = tplDailyDigest($name, $sections, $missed, $appUrl, $dateLabel);
        queueEmail($db, 'daily_digest', $u['email'], $name,
            "📅 Your Day — {$dateLabel}", $html, null, null);
    }

    echo "[" . date('Y-m-d H:i:s') . "] Daily digests queued for " . count($users) . " user(s).\n";
}