<?php
declare(strict_types=1);

// check_company_docs.php
date_default_timezone_set('Asia/Dubai');

use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

require_once __DIR__ . '/vendor/phpmailer/phpmailer/src/Exception.php';
require_once __DIR__ . '/vendor/phpmailer/phpmailer/src/PHPMailer.php';
require_once __DIR__ . '/vendor/phpmailer/phpmailer/src/SMTP.php';

/* ================== إعدادات عامة ================== */
$HR_EMAIL   = 'lawyer.halkaabi@gmail.com';

$START_DAYS        = 30;
$STEP_DAYS         = 3;
$EXPIRED_EVERY_DAY = true;
$FROM_NAME         = 'HLG HR Bot';

/* رابط لوحة مستندات الشركة */
$ADMIN_KEY     = 'HLG-2026';
$DASHBOARD_URL = 'https://hlg.hamdanlegalgroup.ae/HLG-Justice-Hub/hr/company_docs_dashboard.php?key=' . urlencode($ADMIN_KEY);

/* ================== إعدادات SMTP ================== */
$SMTP_HOST   = 'mail.hamdanlegalgroup.ae';
$SMTP_USER   = 'contact@hamdanlegalgroup.ae';
$SMTP_PASS   = 'Saif1979s';
$SMTP_PORT   = 465;
$SMTP_SECURE = PHPMailer::ENCRYPTION_SMTPS;
/* ================== إعدادات قاعدة البيانات ================== */
$DB_HOST = 'localhost';
$DB_NAME = 'hamdanle_hleg607';
$DB_USER = 'hamdanle_hleg607';
$DB_PASS = '@Saif1979s';

/* ================== دوال مساعدة ================== */
function h($s): string
{
    return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8');
}

function log_line_company(string $message): void
{
    file_put_contents(
        __DIR__ . '/company_docs_cron_log.txt',
        date('Y-m-d H:i:s') . ' | ' . $message . PHP_EOL,
        FILE_APPEND
    );
}

function days_between_company(string $fromYmd, string $toYmd): int
{
    $from = new DateTime($fromYmd);
    $to   = new DateTime($toYmd);
    return (int)$from->diff($to)->format('%r%a');
}

function table_exists_company(PDO $pdo, string $table): bool
{
    static $cache = [];

    if (isset($cache[$table])) {
        return $cache[$table];
    }

    $stmt = $pdo->prepare("
        SELECT COUNT(*)
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = :table
    ");
    $stmt->execute([':table' => $table]);

    $cache[$table] = ((int)$stmt->fetchColumn() > 0);
    return $cache[$table];
}

function column_exists_company(PDO $pdo, string $table, string $column): bool
{
    static $cache = [];

    $key = $table . '.' . $column;
    if (isset($cache[$key])) {
        return $cache[$key];
    }

    $stmt = $pdo->prepare("
        SELECT COUNT(*)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = :table
          AND COLUMN_NAME = :column
    ");
    $stmt->execute([
        ':table'  => $table,
        ':column' => $column,
    ]);

    $cache[$key] = ((int)$stmt->fetchColumn() > 0);
    return $cache[$key];
}

function company_branch_name_alert(int $branchId): string
{
    return match ($branchId) {
        1 => 'أبوظبي',
        2 => 'دبي',
        3 => 'العين',
        default => 'غير محدد',
    };
}

function delete_company_alert(PDO $pdo, int $companyDocsId, string $docKey): void
{
    try {
        if (table_exists_company($pdo, 'company_document_alerts')) {
            $stmt = $pdo->prepare("
                DELETE FROM company_document_alerts
                WHERE company_docs_id = :company_docs_id
                  AND document_key = :document_key
            ");
            $stmt->execute([
                ':company_docs_id' => $companyDocsId,
                ':document_key'    => $docKey,
            ]);
        }
    } catch (Throwable $e) {
        log_line_company('Delete company alert failed: docs_id=' . $companyDocsId . ', key=' . $docKey . ' | ' . $e->getMessage());
    }
}

function delete_company_alert_from_hr(PDO $pdo, int $companyDocsId, string $docKey): void
{
    try {
        if (table_exists_company($pdo, 'hr_document_alerts')) {
            $stmt = $pdo->prepare("
                DELETE FROM hr_document_alerts
                WHERE employee_id = 0
                  AND docs_id = :docs_id
                  AND document_key = :document_key
            ");
            $stmt->execute([
                ':docs_id'       => $companyDocsId,
                ':document_key'  => $docKey,
            ]);
        }
    } catch (Throwable $e) {
        log_line_company('Delete HR mirror alert failed: docs_id=' . $companyDocsId . ', key=' . $docKey . ' | ' . $e->getMessage());
    }
}

function upsert_company_alert(PDO $pdo, array $a): void
{
    if (!table_exists_company($pdo, 'company_document_alerts')) {
        return;
    }

    $statusValue = ((int)$a['days_left'] < 0) ? 'expired' : 'warning';

    try {
        $checkStmt = $pdo->prepare("
            SELECT id
            FROM company_document_alerts
            WHERE company_docs_id = :company_docs_id
              AND document_key = :document_key
            LIMIT 1
        ");
        $checkStmt->execute([
            ':company_docs_id' => (int)$a['company_docs_id'],
            ':document_key'    => (string)$a['document_key'],
        ]);

        $existingId = $checkStmt->fetchColumn();

        if ($existingId) {
            $stmt = $pdo->prepare("
                UPDATE company_document_alerts
                SET
                    branch_id      = :branch_id,
                    branch_name    = :branch_name,
                    document_label = :document_label,
                    expiry_date    = :expiry_date,
                    days_left      = :days_left,
                    status         = :status,
                    alert_date     = :alert_date,
                    is_read        = 0
                WHERE id = :id
            ");
            $stmt->execute([
                ':branch_id'      => (int)$a['branch_id'],
                ':branch_name'    => (string)$a['branch_name'],
                ':document_label' => (string)$a['document'],
                ':expiry_date'    => (string)$a['expiry'],
                ':days_left'      => (int)$a['days_left'],
                ':status'         => $statusValue,
                ':alert_date'     => (string)$a['today'],
                ':id'             => (int)$existingId,
            ]);
        } else {
            $stmt = $pdo->prepare("
                INSERT INTO company_document_alerts
                (
                    company_docs_id,
                    branch_id,
                    branch_name,
                    document_key,
                    document_label,
                    expiry_date,
                    days_left,
                    status,
                    alert_date,
                    is_read,
                    created_at
                )
                VALUES
                (
                    :company_docs_id,
                    :branch_id,
                    :branch_name,
                    :document_key,
                    :document_label,
                    :expiry_date,
                    :days_left,
                    :status,
                    :alert_date,
                    0,
                    NOW()
                )
            ");
            $stmt->execute([
                ':company_docs_id' => (int)$a['company_docs_id'],
                ':branch_id'       => (int)$a['branch_id'],
                ':branch_name'     => (string)$a['branch_name'],
                ':document_key'    => (string)$a['document_key'],
                ':document_label'  => (string)$a['document'],
                ':expiry_date'     => (string)$a['expiry'],
                ':days_left'       => (int)$a['days_left'],
                ':status'          => $statusValue,
                ':alert_date'      => (string)$a['today'],
            ]);
        }
    } catch (Throwable $e) {
        log_line_company('Upsert company alert failed: docs_id=' . (int)$a['company_docs_id'] . ', key=' . (string)$a['document_key'] . ' | ' . $e->getMessage());
    }
}

function sync_company_alert_to_hr(PDO $pdo, array $a): void
{
    if (!table_exists_company($pdo, 'hr_document_alerts')) {
        return;
    }

    $statusValue = ((int)$a['days_left'] < 0) ? 'expired' : 'warning';

    try {
        $checkStmt = $pdo->prepare("
            SELECT id
            FROM hr_document_alerts
            WHERE employee_id = 0
              AND docs_id = :docs_id
              AND document_key = :document_key
            LIMIT 1
        ");
        $checkStmt->execute([
            ':docs_id'       => (int)$a['company_docs_id'],
            ':document_key'  => (string)$a['document_key'],
        ]);

        $existingId = $checkStmt->fetchColumn();

        $documentLabelForHr = (string)$a['document'];
        $employeeNameForHr  = 'مستندات الشركة';

        $hasEmployeeName = column_exists_company($pdo, 'hr_document_alerts', 'employee_name');
        $hasIsResolved   = column_exists_company($pdo, 'hr_document_alerts', 'is_resolved');
        $hasAlertDate    = column_exists_company($pdo, 'hr_document_alerts', 'alert_date');

        if ($existingId) {
            $parts = [
                'branch_id = :branch_id',
                'document_label = :document_label',
                'expiry_date = :expiry_date',
                'days_left = :days_left',
                'status = :status',
                'is_read = 0',
            ];

            if ($hasEmployeeName) {
                $parts[] = 'employee_name = :employee_name';
            }

            if ($hasIsResolved) {
                $parts[] = 'is_resolved = 0';
            }

            if ($hasAlertDate) {
                $parts[] = 'alert_date = :alert_date';
            }

            $sql = "
                UPDATE hr_document_alerts
                SET " . implode(', ', $parts) . "
                WHERE id = :id
            ";

            $params = [
                ':branch_id'      => (int)$a['branch_id'],
                ':document_label' => $documentLabelForHr,
                ':expiry_date'    => (string)$a['expiry'],
                ':days_left'      => (int)$a['days_left'],
                ':status'         => $statusValue,
                ':id'             => (int)$existingId,
            ];

            if ($hasEmployeeName) {
                $params[':employee_name'] = $employeeNameForHr;
            }

            if ($hasAlertDate) {
                $params[':alert_date'] = (string)$a['today'];
            }

            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
        } else {
            $columns = [
                'employee_id',
                'docs_id',
                'branch_id',
                'document_key',
                'document_label',
                'expiry_date',
                'days_left',
                'status',
                'is_read',
                'created_at',
            ];

            $values = [
                ':employee_id',
                ':docs_id',
                ':branch_id',
                ':document_key',
                ':document_label',
                ':expiry_date',
                ':days_left',
                ':status',
                '0',
                'NOW()',
            ];

            $params = [
                ':employee_id'    => 0,
                ':docs_id'        => (int)$a['company_docs_id'],
                ':branch_id'      => (int)$a['branch_id'],
                ':document_key'   => (string)$a['document_key'],
                ':document_label' => $documentLabelForHr,
                ':expiry_date'    => (string)$a['expiry'],
                ':days_left'      => (int)$a['days_left'],
                ':status'         => $statusValue,
            ];

            if ($hasEmployeeName) {
                $columns[] = 'employee_name';
                $values[]  = ':employee_name';
                $params[':employee_name'] = $employeeNameForHr;
            }

            if ($hasIsResolved) {
                $columns[] = 'is_resolved';
                $values[]  = '0';
            }

            if ($hasAlertDate) {
                $columns[] = 'alert_date';
                $values[]  = ':alert_date';
                $params[':alert_date'] = (string)$a['today'];
            }

            $stmt = $pdo->prepare("
                INSERT INTO hr_document_alerts
                (" . implode(', ', $columns) . ")
                VALUES
                (" . implode(', ', $values) . ")
            ");
            $stmt->execute($params);
        }
    } catch (Throwable $e) {
        log_line_company('Sync HR mirror failed: docs_id=' . (int)$a['company_docs_id'] . ', key=' . (string)$a['document_key'] . ' | ' . $e->getMessage());
    }
}

/* ================== اتصال قاعدة البيانات ================== */
try {
    $pdo = new PDO(
        "mysql:host={$DB_HOST};dbname={$DB_NAME};charset=utf8mb4",
        $DB_USER,
        $DB_PASS,
        [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci",
        ]
    );
} catch (Throwable $e) {
    http_response_code(500);
    log_line_company('DB connection failed: ' . $e->getMessage());
    exit("DB connection failed\n");
}

$today = date('Y-m-d');

/* ================== التحقق من الجداول/الأعمدة ================== */
if (!table_exists_company($pdo, 'company_docs')) {
    log_line_company('Missing required table: company_docs');
    exit("Required table company_docs not found\n");
}

$hasBranchId                   = column_exists_company($pdo, 'company_docs', 'branch_id');
$hasAdLicenseExpiry            = column_exists_company($pdo, 'company_docs', 'ad_license_expiry');
$hasDubaiLicenseExpiry         = column_exists_company($pdo, 'company_docs', 'dubai_license_expiry');
$hasJudicialRegistrationExpiry = column_exists_company($pdo, 'company_docs', 'judicial_registration_expiry');
$hasDubaiRegistrationExpiry    = column_exists_company($pdo, 'company_docs', 'dubai_registration_expiry');
$hasFederalRegistrationExpiry  = column_exists_company($pdo, 'company_docs', 'federal_registration_expiry');

$hasAdLicenseNotified            = column_exists_company($pdo, 'company_docs', 'ad_license_notified');
$hasDubaiLicenseNotified         = column_exists_company($pdo, 'company_docs', 'dubai_license_notified');
$hasJudicialRegistrationNotified = column_exists_company($pdo, 'company_docs', 'judicial_registration_notified');
$hasDubaiRegistrationNotified    = column_exists_company($pdo, 'company_docs', 'dubai_registration_notified');
$hasFederalRegistrationNotified  = column_exists_company($pdo, 'company_docs', 'federal_registration_notified');

/* ================== جلب مستندات الشركة ================== */
$sql = "
SELECT
    id AS company_docs_id,
    " . ($hasBranchId ? "branch_id" : "0") . " AS branch_id,
    " . ($hasAdLicenseExpiry ? "ad_license_expiry" : "NULL") . " AS ad_license_expiry,
    " . ($hasDubaiLicenseExpiry ? "dubai_license_expiry" : "NULL") . " AS dubai_license_expiry,
    " . ($hasJudicialRegistrationExpiry ? "judicial_registration_expiry" : "NULL") . " AS judicial_registration_expiry,
    " . ($hasDubaiRegistrationExpiry ? "dubai_registration_expiry" : "NULL") . " AS dubai_registration_expiry,
    " . ($hasFederalRegistrationExpiry ? "federal_registration_expiry" : "NULL") . " AS federal_registration_expiry,
    " . ($hasAdLicenseNotified ? "ad_license_notified" : "NULL") . " AS ad_license_notified,
    " . ($hasDubaiLicenseNotified ? "dubai_license_notified" : "NULL") . " AS dubai_license_notified,
    " . ($hasJudicialRegistrationNotified ? "judicial_registration_notified" : "NULL") . " AS judicial_registration_notified,
    " . ($hasDubaiRegistrationNotified ? "dubai_registration_notified" : "NULL") . " AS dubai_registration_notified,
    " . ($hasFederalRegistrationNotified ? "federal_registration_notified" : "NULL") . " AS federal_registration_notified
FROM company_docs
ORDER BY id DESC
";

$rows = $pdo->query($sql)->fetchAll();

/* ================== بناء التنبيهات ================== */
$activeAlerts = [];
$mailAlerts   = [];

foreach ($rows as $r) {
    $companyDocsId = (int)($r['company_docs_id'] ?? 0);
    $branchId      = (int)($r['branch_id'] ?? 0);
    $branchName    = company_branch_name_alert($branchId);

    $docs = [
        [
            'key'          => 'ad_license',
            'label'        => 'رخصة أبوظبي',
            'expiry'       => $r['ad_license_expiry'] ?? null,
            'notified_col' => 'ad_license_notified',
            'notified_val' => $r['ad_license_notified'] ?? null,
            'enabled'      => $hasAdLicenseNotified,
        ],
        [
            'key'          => 'dubai_license',
            'label'        => 'رخصة دبي',
            'expiry'       => $r['dubai_license_expiry'] ?? null,
            'notified_col' => 'dubai_license_notified',
            'notified_val' => $r['dubai_license_notified'] ?? null,
            'enabled'      => $hasDubaiLicenseNotified,
        ],
        [
            'key'          => 'judicial_registration',
            'label'        => 'قيد دائرة القضاء',
            'expiry'       => $r['judicial_registration_expiry'] ?? null,
            'notified_col' => 'judicial_registration_notified',
            'notified_val' => $r['judicial_registration_notified'] ?? null,
            'enabled'      => $hasJudicialRegistrationNotified,
        ],
        [
            'key'          => 'dubai_registration',
            'label'        => 'قيد دبي',
            'expiry'       => $r['dubai_registration_expiry'] ?? null,
            'notified_col' => 'dubai_registration_notified',
            'notified_val' => $r['dubai_registration_notified'] ?? null,
            'enabled'      => $hasDubaiRegistrationNotified,
        ],
        [
            'key'          => 'federal_registration',
            'label'        => 'القيد الاتحادي',
            'expiry'       => $r['federal_registration_expiry'] ?? null,
            'notified_col' => 'federal_registration_notified',
            'notified_val' => $r['federal_registration_notified'] ?? null,
            'enabled'      => $hasFederalRegistrationNotified,
        ],
    ];

    foreach ($docs as $doc) {
        $docKey  = (string)$doc['key'];
        $expiry  = trim((string)($doc['expiry'] ?? ''));

        if ($expiry === '' || $expiry === '0000-00-00') {
            delete_company_alert($pdo, $companyDocsId, $docKey);
            delete_company_alert_from_hr($pdo, $companyDocsId, $docKey);
            continue;
        }

        $daysLeft = days_between_company($today, $expiry);

        if ($daysLeft > $START_DAYS) {
            delete_company_alert($pdo, $companyDocsId, $docKey);
            delete_company_alert_from_hr($pdo, $companyDocsId, $docKey);
            continue;
        }

        $alert = [
            'company_docs_id' => $companyDocsId,
            'branch_id'       => $branchId,
            'branch_name'     => $branchName,
            'document'        => (string)$doc['label'],
            'document_key'    => $docKey,
            'expiry'          => $expiry,
            'days_left'       => $daysLeft,
            'status'          => ($daysLeft < 0) ? 'منتهي' : 'قريب الانتهاء',
            'notified_col'    => (string)$doc['notified_col'],
            'notified_val'    => (string)($doc['notified_val'] ?? ''),
            'can_update'      => (bool)$doc['enabled'],
            'today'           => $today,
        ];

        $activeAlerts[] = $alert;

        upsert_company_alert($pdo, $alert);
        sync_company_alert_to_hr($pdo, $alert);

       $shouldNotify = true;
        if ($shouldNotify && !(!empty($doc['notified_val']) && (string)$doc['notified_val'] === $today)) {
            $mailAlerts[] = $alert;
        }
    }
}

/* ================== إذا لا توجد تنبيهات جديدة للبريد اليوم ================== */
if (count($mailAlerts) === 0) {
    log_line_company('No email alerts today (sync completed)');
    exit("No email alerts today\n");
}

/* ================== ترتيب + إحصائيات ================== */
usort($mailAlerts, static function ($a, $b) {
    $ad = (int)$a['days_left'];
    $bd = (int)$b['days_left'];

    if ($ad === $bd) {
        return 0;
    }
    return ($ad < $bd) ? -1 : 1;
});

$expiredCount = 0;
$nearCount    = 0;

foreach ($mailAlerts as $a) {
    if ((int)$a['days_left'] < 0) {
        $expiredCount++;
    } else {
        $nearCount++;
    }
}

$subject = "تنبيهات مستندات الشركة - {$today}";

/* ================== بناء Rows HTML ================== */
$rowsHtml = '';

foreach ($mailAlerts as $a) {
    $dl = (int)$a['days_left'];
    $dlText = ($dl < 0) ? ('منتهي منذ ' . abs($dl) . ' يوم') : ('متبقي ' . $dl . ' يوم');

    if ($dl < 0) {
        $badgeBg = '#ffe1e1';
        $badgeTx = '#8a1f1f';
        $label   = 'منتهي';
    } else {
        $badgeBg = '#fff4d6';
        $badgeTx = '#8a5a00';
        $label   = 'قريب';
    }

    $rowsHtml .= '
    <tr>
        <td style="padding:12px;border-bottom:1px solid #eee;text-align:right;direction:rtl;">
            ' . h($a['branch_name']) . '
        </td>
        <td style="padding:12px;border-bottom:1px solid #eee;text-align:right;direction:rtl;">' . h($a['document']) . '</td>
        <td style="padding:12px;border-bottom:1px solid #eee;text-align:right;direction:rtl;">' . h($a['expiry']) . '</td>
        <td style="padding:12px;border-bottom:1px solid #eee;text-align:right;direction:rtl;">
            <span style="padding:6px 12px;border-radius:999px;font-size:12px;background:' . $badgeBg . ';color:' . $badgeTx . ';font-weight:700;display:inline-block;">' . $label . '</span>
            <span style="font-size:12px;color:#666;margin-right:8px;display:inline-block;">(' . h($dlText) . ')</span>
        </td>
    </tr>';
}

/* ================== HTML Body ================== */
$htmlBody = '
<!doctype html>
<html lang="ar" dir="rtl">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
</head>
<body style="margin:0;background:#f3f6f8;font-family:Tajawal,Arial,sans-serif;direction:rtl;text-align:right;">
    <div style="max-width:850px;margin:20px auto;padding:10px;">

        <div style="background:linear-gradient(135deg,#00424f,#006a7a);color:#fff;border-radius:18px;padding:18px;text-align:right;">
            <div style="font-size:20px;font-weight:800;line-height:1.4;">تنبيهات مستندات الشركة</div>
            <div style="font-size:13px;margin-top:6px;opacity:.9;">التاريخ: ' . h($today) . '</div>

            <div style="margin-top:12px;">
                <a href="' . h($DASHBOARD_URL) . '"
                   style="display:inline-block;background:#ffffff;color:#00424f;text-decoration:none;padding:10px 14px;border-radius:12px;font-weight:700;font-size:13px;">
                    فتح لوحة مستندات الشركة
                </a>
            </div>
        </div>

        <table width="100%" cellpadding="0" cellspacing="0" style="margin-top:12px;direction:rtl;text-align:right;">
            <tr>
                <td style="width:33%;padding:6px;">
                    <div style="background:#fff;border-radius:14px;padding:14px;border:1px solid #e6eef0;">
                        <div style="font-size:12px;color:#777;">عدد التنبيهات</div>
                        <div style="font-size:22px;font-weight:800;">' . count($mailAlerts) . '</div>
                    </div>
                </td>

                <td style="width:33%;padding:6px;">
                    <div style="background:#fff;border-radius:14px;padding:14px;border:1px solid #e6eef0;">
                        <div style="font-size:12px;color:#777;">منتهي</div>
                        <div style="font-size:22px;font-weight:800;color:#d62828;">' . (int)$expiredCount . '</div>
                    </div>
                </td>

                <td style="width:33%;padding:6px;">
                    <div style="background:#fff;border-radius:14px;padding:14px;border:1px solid #e6eef0;">
                        <div style="font-size:12px;color:#777;">قريب (≤ 30 يوم)</div>
                        <div style="font-size:22px;font-weight:800;color:#b08900;">' . (int)$nearCount . '</div>
                    </div>
                </td>
            </tr>
        </table>

        <div style="background:#fff;border-radius:16px;margin-top:14px;border:1px solid #e6eef0;overflow:hidden;">
            <div style="padding:14px;font-weight:800;border-bottom:1px solid #eee;text-align:right;">التفاصيل</div>

            <table width="100%" cellpadding="0" cellspacing="0" style="border-collapse:collapse;text-align:right;direction:rtl;">
                <thead>
                    <tr style="background:#f7fafb;font-size:13px;color:#555;">
                        <th style="padding:12px;text-align:right;">الفرع</th>
                        <th style="padding:12px;text-align:right;">المستند</th>
                        <th style="padding:12px;text-align:right;">تاريخ الانتهاء</th>
                        <th style="padding:12px;text-align:right;">الحالة</th>
                    </tr>
                </thead>
                <tbody>
                    ' . $rowsHtml . '
                </tbody>
            </table>
        </div>

        <div style="text-align:center;color:#888;font-size:12px;margin-top:12px;">
            هذه رسالة آلية من نظام الموارد البشرية - مجموعة حمدان القانونية
        </div>

    </div>
</body>
</html>
';

/* ================== Plain Body ================== */
$plainLines   = [];
$plainLines[] = "تنبيهات مستندات الشركة";
$plainLines[] = "التاريخ: {$today}";
$plainLines[] = "فتح لوحة مستندات الشركة: {$DASHBOARD_URL}";
$plainLines[] = "---------------------------";

foreach ($mailAlerts as $a) {
    $dl = (int)$a['days_left'];
    $dlText = ($dl < 0) ? ('منتهي منذ ' . abs($dl) . ' يوم') : ('متبقي ' . $dl . ' يوم');

    $plainLines[] = "الفرع: {$a['branch_name']}";
    $plainLines[] = "المستند: {$a['document']}";
    $plainLines[] = "تاريخ الانتهاء: {$a['expiry']}";
    $plainLines[] = "الحالة: {$a['status']} ({$dlText})";
    $plainLines[] = "---------------------------";
}

$plainBody = implode("\n", $plainLines);

/* ================== إرسال البريد ================== */
$sent = false;
$errorMessage = '';

try {
    $mail = new PHPMailer(true);
    $mail->CharSet = 'UTF-8';

    $mail->isSMTP();
    $mail->Host       = $SMTP_HOST;
    $mail->SMTPAuth   = true;
    $mail->Username   = $SMTP_USER;
    $mail->Password   = $SMTP_PASS;
    $mail->SMTPSecure = $SMTP_SECURE;
    $mail->Port       = $SMTP_PORT;

    $mail->SMTPOptions = [
        'ssl' => [
            'verify_peer'       => false,
            'verify_peer_name'  => false,
            'allow_self_signed' => true,
        ],
    ];

    $mail->setFrom($SMTP_USER, $FROM_NAME);
    $mail->addAddress($HR_EMAIL);

    $mail->Subject = $subject;
    $mail->isHTML(true);
    $mail->Body    = $htmlBody;
    $mail->AltBody = $plainBody;

    $mail->send();
    $sent = true;
} catch (Throwable $e) {
    $errorMessage = $e->getMessage();
    $sent = false;
}

/* ================== تحديث notified بعد نجاح البريد ================== */
if ($sent) {
    $allowedCols = [
        'ad_license_notified',
        'dubai_license_notified',
        'judicial_registration_notified',
        'dubai_registration_notified',
        'federal_registration_notified',
    ];

    foreach ($mailAlerts as $a) {
        if ($a['can_update'] && in_array($a['notified_col'], $allowedCols, true)) {
            try {
                $sqlUpd = "UPDATE company_docs SET {$a['notified_col']} = :today WHERE id = :id";
                $stmtUpd = $pdo->prepare($sqlUpd);
                $stmtUpd->execute([
                    ':today' => $today,
                    ':id'    => (int)$a['company_docs_id'],
                ]);
            } catch (Throwable $e) {
                log_line_company('Failed updating notified column: docs_id=' . (int)$a['company_docs_id'] . ', key=' . (string)$a['document_key'] . ' | ' . $e->getMessage());
            }
        }
    }

    log_line_company('Sent to HR: ' . count($mailAlerts) . ' alerts');
    echo "Sent to HR: " . count($mailAlerts) . " alerts\n";
} else {
    log_line_company('Mail failed: ' . $errorMessage);
    echo "SMTP Mail failed: " . h($errorMessage) . "\n";
}