<?php
/**
 * WPCode 片段 1/4 — 核心数据层
 * 名称建议：IM - 核心数据层
 * 类型：PHP Snippet
 * 位置：Run everywhere
 *
 * 包含：全局常量 · 自动建表 · IM_Candidate · IM_Token · IM_Mailer
 */

defined('ABSPATH') || exit;

/* ============================================================
   !! 配置区 —— 根据实际情况修改以下常量 !!
   ============================================================ */

// 开启调试模式：在列表页后台显示所有相关申请表、面试链接
if (!defined('IM_DEBUG_LINKS'))
    define('IM_DEBUG_LINKS', true);

// 视频最大尺寸（字节），默认 500 MB
if (!defined('IM_MAX_VIDEO_SIZE'))
    define('IM_MAX_VIDEO_SIZE', 500 * 1024 * 1024);

// 详细申请表单页面 URL（用于 Join Us 确认邮件中的链接）
// 请将此处改为您在 WordPress 后台创建的页面 URL，该页面需嵌入 [im_apply_form] shortcode
if (!defined('IM_APPLY_PAGE_URL'))
    define('IM_APPLY_PAGE_URL', home_url('/apply/'));

// 面试页面 URL（用于面试邀请邮件中的链接）
// 请将此处改为您在 WordPress 后台创建的页面 URL，该页面需嵌入 [im_interview] shortcode
if (!defined('IM_INTERVIEW_PAGE_URL'))
    define('IM_INTERVIEW_PAGE_URL', home_url('/interview/'));

// 培训页面 URL（用于录取邮件中的培训链接）
// 请将此处改为您在 WordPress 后台创建的页面 URL，该页面需嵌入 [im_training] shortcode
if (!defined('IM_TRAINING_PAGE_URL'))
    define('IM_TRAINING_PAGE_URL', home_url('/training/'));

// 培训完成后发送的临时账号信息（用于培训完成邮件）
// 请将此处改为实际的临时账号信息
if (!defined('IM_TRAINING_ACCOUNT'))
    define('IM_TRAINING_ACCOUNT', 'Teaching@YStarEdu.onmicrosoft.com');
if (!defined('IM_TRAINING_PASSWORD'))
    define('IM_TRAINING_PASSWORD', 'YStarEdu1021');

/* ============================================================
   自动建表（用 wp_options 版本号做幂等控制）
   ============================================================ */
function im_maybe_create_tables()
{
    $current_version = get_option('im_db_version');
    if ($current_version === '1.7')
        return;

    global $wpdb;
    $charset = $wpdb->get_charset_collate();
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';

    // 候选人主表
    dbDelta("CREATE TABLE IF NOT EXISTS {$wpdb->prefix}im_candidates (
        id               BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        first_name       VARCHAR(100) NOT NULL DEFAULT '',
        last_name        VARCHAR(100) NOT NULL DEFAULT '',
        preferred_name   VARCHAR(100) NOT NULL DEFAULT '',
        email            VARCHAR(200) NOT NULL DEFAULT '',
        phone            VARCHAR(50)  NOT NULL DEFAULT '',
        location         VARCHAR(200) NOT NULL DEFAULT '',
        degree_level     VARCHAR(50)  NOT NULL DEFAULT '',
        university       VARCHAR(200) NOT NULL DEFAULT '',
        major            VARCHAR(200) NOT NULL DEFAULT '',
        grad_year        VARCHAR(10)  NOT NULL DEFAULT '',
        gpa              VARCHAR(20)  NOT NULL DEFAULT '',
        deans_list       TINYINT(1)   NOT NULL DEFAULT 0,
        ug_university    VARCHAR(200) NOT NULL DEFAULT '',
        ug_major         VARCHAR(200) NOT NULL DEFAULT '',
        ug_grad_year     VARCHAR(10)  NOT NULL DEFAULT '',
        ms_university    VARCHAR(200) NOT NULL DEFAULT '',
        ms_major         VARCHAR(200) NOT NULL DEFAULT '',
        ms_grad_year     VARCHAR(10)  NOT NULL DEFAULT '',
        languages        TEXT,
        teaching_exp     TEXT,
        has_achievement  TINYINT(1)   NOT NULL DEFAULT 0,
        achievement_type VARCHAR(200) NOT NULL DEFAULT '',
        achievement_desc TEXT,
        subjects         TEXT,
        extra_notes      TEXT,
        status           VARCHAR(20)  NOT NULL DEFAULT 'applied',
        reject_reason    TEXT,
        apply_token      VARCHAR(64)  NOT NULL DEFAULT '' COMMENT '详细表单专属 token，防止他人访问',
        apply_token_used TINYINT(1)   NOT NULL DEFAULT 0,
        apply_opened_at  DATETIME     DEFAULT NULL,
        ca_highschool    TINYINT(1)   NOT NULL DEFAULT 0,
        ca_highschool_name VARCHAR(200) NOT NULL DEFAULT '',
        country          VARCHAR(100) NOT NULL DEFAULT '',
        city             VARCHAR(200) NOT NULL DEFAULT '',
        training_token   VARCHAR(64) NOT NULL DEFAULT '',
        training_opened_at DATETIME DEFAULT NULL,
        training_completed_at DATETIME DEFAULT NULL,
        created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY email (email),
        KEY status (status),
        KEY apply_token (apply_token),
        KEY training_token (training_token)
    ) $charset;");

    // 面试 Token 表
    dbDelta("CREATE TABLE IF NOT EXISTS {$wpdb->prefix}im_interview_tokens (
        id               BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        candidate_id     BIGINT(20) UNSIGNED NOT NULL,
        token            VARCHAR(64)  NOT NULL DEFAULT '',
        expires_at       DATETIME     NOT NULL,
        is_used          TINYINT(1)   NOT NULL DEFAULT 0,
        video_path       TEXT,
        video_filename   VARCHAR(255) NOT NULL DEFAULT '',
        sent_at          DATETIME     DEFAULT NULL,
        opened_at        DATETIME     DEFAULT NULL,
        submitted_at     DATETIME     DEFAULT NULL,
        created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY token (token),
        KEY candidate_id (candidate_id)
    ) $charset;");

    // 附件上传记录表
    dbDelta("CREATE TABLE IF NOT EXISTS {$wpdb->prefix}im_attachments (
        id               BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        candidate_id     BIGINT(20) UNSIGNED NOT NULL,
        file_path        TEXT        NOT NULL,
        file_name        VARCHAR(255) NOT NULL DEFAULT '',
        file_type        VARCHAR(50)  NOT NULL DEFAULT '',
        uploaded_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY candidate_id (candidate_id)
    ) $charset;");

    if ($current_version === '1.1') {
        $wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates MODIFY COLUMN status VARCHAR(20) NOT NULL DEFAULT 'applied'");
        $wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD COLUMN IF NOT EXISTS reject_reason TEXT");
        $wpdb->query("UPDATE {$wpdb->prefix}im_candidates SET status='applied' WHERE status='pending'");
        $wpdb->query("UPDATE {$wpdb->prefix}im_candidates SET status='screening' WHERE status='submitted' AND apply_token_used=1");
        $wpdb->query("UPDATE {$wpdb->prefix}im_candidates c JOIN {$wpdb->prefix}im_interview_tokens t ON c.id=t.candidate_id AND t.is_used=1 SET c.status='completed' ");
    }
    if (version_compare($current_version, '1.4', '<')) {
        // Bulletproof method to add columns for older MySQL versions that fail on "ADD COLUMN IF NOT EXISTS"
        $cand_cols = $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}im_candidates");
        if (!in_array('apply_opened_at', $cand_cols)) {
            $wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD apply_opened_at DATETIME DEFAULT NULL");
        }
        if (!in_array('country', $cand_cols)) {
            $wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD country VARCHAR(100) NOT NULL DEFAULT ''");
        }
        if (!in_array('city', $cand_cols)) {
            $wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD city VARCHAR(200) NOT NULL DEFAULT ''");
        }

        $tok_cols = $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}im_interview_tokens");
        if (!in_array('opened_at', $tok_cols)) {
            $wpdb->query("ALTER TABLE {$wpdb->prefix}im_interview_tokens ADD opened_at DATETIME DEFAULT NULL");
        }

        update_option('im_db_version', '1.4');
    }
    if (version_compare(get_option('im_db_version'), '1.5', '<')) {
        $cand_cols = $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}im_candidates");
        if (!in_array('ca_highschool', $cand_cols)) {
            $wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD ca_highschool TINYINT(1) NOT NULL DEFAULT 0");
        }
        if (!in_array('ca_highschool_name', $cand_cols)) {
            $wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD ca_highschool_name VARCHAR(200) NOT NULL DEFAULT ''");
        }
        update_option('im_db_version', '1.5');
    }
    if (version_compare(get_option('im_db_version'), '1.6', '<')) {
        $cand_cols = $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}im_candidates");
        if (!in_array('ms_university', $cand_cols)) {
            $wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD ms_university VARCHAR(200) NOT NULL DEFAULT ''");
        }
        if (!in_array('ms_major', $cand_cols)) {
            $wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD ms_major VARCHAR(200) NOT NULL DEFAULT ''");
        }
        if (!in_array('ms_grad_year', $cand_cols)) {
            $wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD ms_grad_year VARCHAR(10) NOT NULL DEFAULT ''");
        }
        update_option('im_db_version', '1.6');
    }
    if (version_compare(get_option('im_db_version'), '1.7', '<')) {
        $cand_cols = $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}im_candidates");
        if (!in_array('training_token', $cand_cols)) {
            $wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD training_token VARCHAR(64) NOT NULL DEFAULT ''");
            $wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD INDEX training_token (training_token)");
        }
        if (!in_array('training_opened_at', $cand_cols)) {
            $wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD training_opened_at DATETIME DEFAULT NULL");
        }
        if (!in_array('training_completed_at', $cand_cols)) {
            $wpdb->query("ALTER TABLE {$wpdb->prefix}im_candidates ADD training_completed_at DATETIME DEFAULT NULL");
        }
        // Migrate existing 'hired' candidates to 'training' status
        $wpdb->query("UPDATE {$wpdb->prefix}im_candidates SET status='training' WHERE status='hired'");
        update_option('im_db_version', '1.7');
    }
}
add_action('init', 'im_maybe_create_tables', 1);

/* ============================================================
   IM_Candidate
   ============================================================ */
if (!class_exists('IM_Candidate')) {
    class IM_Candidate
    {

        public static function table()
        {
            global $wpdb;
            return $wpdb->prefix . 'im_candidates';
        }

        /** 第一步：仅凭 Join Us 简表单创建候选人（只有姓名、邮箱） */
        public static function create_from_joinus(string $first_name, string $last_name, string $email)
        {
            global $wpdb;
            $email = sanitize_email($email);
            $existing = self::get_by_email($email);
            if ($existing)
                return (int) $existing->id;

            $apply_token = bin2hex(random_bytes(24)); // 48 位申请表 token
            $wpdb->insert(self::table(), [
                'first_name' => sanitize_text_field($first_name),
                'last_name' => sanitize_text_field($last_name),
                'email' => $email,
                'apply_token' => $apply_token,
                'status' => 'applied',
            ]);
            return $wpdb->insert_id ?: false;
        }

        /** 第二步：详细表单提交后，更新候选人全部信息 */
        public static function update_full(int $id, array $data)
        {
            global $wpdb;
            return $wpdb->update(self::table(), [
                'preferred_name' => sanitize_text_field($data['preferred_name'] ?? ''),
                'phone' => sanitize_text_field($data['phone'] ?? ''),
                'country' => sanitize_text_field($data['country'] ?? ''),
                'city' => sanitize_text_field($data['city'] ?? ''),
                'degree_level' => sanitize_text_field($data['degree_level'] ?? ''),
                'university' => sanitize_text_field($data['university'] ?? ''),
                'major' => sanitize_text_field($data['major'] ?? ''),
                'grad_year' => sanitize_text_field($data['grad_year'] ?? ''),
                'gpa' => sanitize_text_field($data['gpa'] ?? ''),
                'deans_list' => (int) ($data['deans_list'] ?? 0),
                'ug_university' => sanitize_text_field($data['ug_university'] ?? ''),
                'ug_major' => sanitize_text_field($data['ug_major'] ?? ''),
                'ug_grad_year' => sanitize_text_field($data['ug_grad_year'] ?? ''),
                'ms_university' => sanitize_text_field($data['ms_university'] ?? ''),
                'ms_major' => sanitize_text_field($data['ms_major'] ?? ''),
                'ms_grad_year' => sanitize_text_field($data['ms_grad_year'] ?? ''),
                'ca_highschool' => (int) ($data['ca_highschool'] ?? 0),
                'ca_highschool_name' => sanitize_text_field($data['ca_highschool_name'] ?? ''),
                'languages' => sanitize_textarea_field($data['languages'] ?? ''),
                'teaching_exp' => sanitize_textarea_field($data['teaching_exp'] ?? ''),
                'has_achievement' => (int) ($data['has_achievement'] ?? 0),
                'achievement_type' => sanitize_text_field($data['achievement_type'] ?? ''),
                'achievement_desc' => sanitize_textarea_field($data['achievement_desc'] ?? ''),
                'subjects' => is_array($data['subjects'] ?? null)
                    ? json_encode(array_values($data['subjects']), JSON_UNESCAPED_UNICODE)
                    : '[]',
                'extra_notes' => sanitize_textarea_field($data['extra_notes'] ?? ''),
                'status' => 'screening',
                'apply_token_used' => 1,
            ], ['id' => $id]);
        }

        public static function get(int $id)
        {
            global $wpdb;
            return $wpdb->get_row($wpdb->prepare('SELECT * FROM ' . self::table() . ' WHERE id=%d', $id));
        }

        public static function mark_apply_opened(int $id)
        {
            global $wpdb;
            return $wpdb->query($wpdb->prepare('UPDATE ' . self::table() . ' SET apply_opened_at = CURRENT_TIMESTAMP WHERE id = %d AND apply_opened_at IS NULL', $id));
        }

        public static function get_by_email(string $email)
        {
            global $wpdb;
            return $wpdb->get_row($wpdb->prepare(
                'SELECT * FROM ' . self::table() . ' WHERE email=%s',
                sanitize_email($email)
            ));
        }

        public static function get_by_apply_token(string $token)
        {
            global $wpdb;
            return $wpdb->get_row($wpdb->prepare(
                'SELECT * FROM ' . self::table() . ' WHERE apply_token=%s AND apply_token_used=0',
                $token
            ));
        }

        public static function get_by_apply_token_any(string $token)
        {
            global $wpdb;
            return $wpdb->get_row($wpdb->prepare(
                'SELECT * FROM ' . self::table() . ' WHERE apply_token=%s',
                $token
            ));
        }

        public static function get_list(array $args = [])
        {
            global $wpdb;
            $limit = (int) ($args['per_page'] ?? 20);
            $offset = ((int) ($args['page'] ?? 1) - 1) * $limit;
            [$where, $params] = self::_where($args);
            $sql = 'SELECT * FROM ' . self::table() . " WHERE $where ORDER BY created_at DESC LIMIT %d OFFSET %d";
            return $wpdb->get_results($wpdb->prepare($sql, ...array_merge($params, [$limit, $offset])));
        }

        public static function count(array $args = [])
        {
            global $wpdb;
            [$where, $params] = self::_where($args);
            $sql = 'SELECT COUNT(*) FROM ' . self::table() . " WHERE $where";
            return (int) ($params
                ? $wpdb->get_var($wpdb->prepare($sql, ...$params))
                : $wpdb->get_var($sql));
        }

        public static function update_status(int $id, string $status)
        {
            global $wpdb;
            return $wpdb->update(self::table(), ['status' => $status], ['id' => $id]);
        }

        public static function reject(int $id, string $reason)
        {
            global $wpdb;
            return $wpdb->update(self::table(), ['status' => 'rejected', 'reject_reason' => sanitize_textarea_field($reason)], ['id' => $id]);
        }

        /** 删除候选人及其所有关联数据和文件 */
        public static function delete_with_files(int $id): bool
        {
            global $wpdb;
            $c = self::get($id);
            if (!$c)
                return false;

            $upload_dir = wp_upload_dir();

            // 删除申请附件目录
            $app_dir = $upload_dir['basedir'] . '/im-applications/' . $id;
            if (is_dir($app_dir)) {
                self::_rmdir($app_dir);
            }

            // 删除面试视频目录
            $iv_dir = $upload_dir['basedir'] . '/interviews/' . $id;
            if (is_dir($iv_dir)) {
                self::_rmdir($iv_dir);
            }

            // 删除数据库记录
            $wpdb->delete(IM_Attachment::table(), ['candidate_id' => $id]);
            $wpdb->delete(IM_Token::table(), ['candidate_id' => $id]);
            $wpdb->delete(self::table(), ['id' => $id]);

            return true;
        }

        private static function _rmdir(string $dir): void
        {
            $files = new \RecursiveIteratorIterator(
                new \RecursiveDirectoryIterator($dir, \FilesystemIterator::SKIP_DOTS),
                \RecursiveIteratorIterator::CHILD_FIRST
            );
            foreach ($files as $f) {
                $f->isDir() ? rmdir($f->getRealPath()) : unlink($f->getRealPath());
            }
            rmdir($dir);
        }

        public static function get_by_training_token(string $token)
        {
            global $wpdb;
            return $wpdb->get_row($wpdb->prepare(
                'SELECT * FROM ' . self::table() . ' WHERE training_token=%s AND status=%s',
                $token,
                'training'
            ));
        }

        public static function get_by_training_token_any(string $token)
        {
            global $wpdb;
            return $wpdb->get_row($wpdb->prepare(
                'SELECT * FROM ' . self::table() . ' WHERE training_token=%s',
                $token
            ));
        }

        public static function generate_training_token(int $id): string|false
        {
            global $wpdb;
            $c = self::get($id);
            if (!$c)
                return false;
            if ($c->training_token) {
                return add_query_arg('im_training_token', $c->training_token, IM_TRAINING_PAGE_URL);
            }
            $token = bin2hex(random_bytes(24));
            $ok = $wpdb->update(self::table(), [
                'training_token' => $token,
                'status' => 'training',
            ], ['id' => $id]);
            if ($ok === false)
                return false;
            return add_query_arg('im_training_token', $token, IM_TRAINING_PAGE_URL);
        }

        public static function mark_training_opened(int $id)
        {
            global $wpdb;
            return $wpdb->query($wpdb->prepare(
                'UPDATE ' . self::table() . ' SET training_opened_at = CURRENT_TIMESTAMP WHERE id = %d AND training_opened_at IS NULL',
                $id
            ));
        }

        public static function mark_training_completed(int $id)
        {
            global $wpdb;
            return $wpdb->update(self::table(), [
                'status' => 'trained',
                'training_completed_at' => current_time('mysql'),
            ], ['id' => $id]);
        }

        public static function get_subjects($candidate): array
        {
            if (is_numeric($candidate))
                $candidate = self::get((int) $candidate);
            if (!$candidate)
                return [];
            $s = json_decode($candidate->subjects ?? '[]', true);
            if (!is_array($s))
                return [];
            $titles = [];
            foreach ($s as $id) {
                if (is_numeric($id)) {
                    $post = get_post((int) $id);
                    $titles[] = $post ? $post->post_title : (string) $id;
                } else {
                    $titles[] = (string) $id;
                }
            }
            return $titles;
        }

        private static function _where(array $args): array
        {
            global $wpdb;
            $where = ['1=1'];
            $params = [];
            if (!empty($args['status']) && in_array($args['status'], ['applied', 'screening', 'invited', 'completed', 'rejected', 'hired', 'training', 'trained'], true)) {
                $where[] = 'status=%s';
                $params[] = $args['status'];
            }
            if (!empty($args['search'])) {
                $like = '%' . $wpdb->esc_like($args['search']) . '%';
                $where[] = '(first_name LIKE %s OR last_name LIKE %s OR email LIKE %s)';
                array_push($params, $like, $like, $like);
            }
            return [implode(' AND ', $where), $params];
        }
    }
} // end IM_Candidate

/* ============================================================
   IM_Attachment
   ============================================================ */
if (!class_exists('IM_Attachment')) {
    class IM_Attachment
    {

        public static function table()
        {
            global $wpdb;
            return $wpdb->prefix . 'im_attachments';
        }

        public static function add(int $candidate_id, string $path, string $name, string $type)
        {
            global $wpdb;
            return $wpdb->insert(self::table(), [
                'candidate_id' => $candidate_id,
                'file_path' => $path,
                'file_name' => $name,
                'file_type' => $type,
            ]);
        }

        public static function get_by_candidate(int $candidate_id)
        {
            global $wpdb;
            return $wpdb->get_results($wpdb->prepare(
                'SELECT * FROM ' . self::table() . ' WHERE candidate_id=%d ORDER BY uploaded_at ASC',
                $candidate_id
            ));
        }
    }
} // end IM_Attachment

/* ============================================================
   IM_Token
   ============================================================ */
if (!class_exists('IM_Token')) {
    class IM_Token
    {

        public static function table()
        {
            global $wpdb;
            return $wpdb->prefix . 'im_interview_tokens';
        }

        public static function generate(int $candidate_id): string|false
        {
            global $wpdb;
            $expires_at = date('Y-m-d H:i:s', current_time('timestamp') + 86400 * 3);
            $sent_at = current_time('mysql');

            // Check if token already exists for candidate
            $tk = $wpdb->get_row($wpdb->prepare('SELECT * FROM ' . self::table() . ' WHERE candidate_id=%d ORDER BY id DESC LIMIT 1', $candidate_id));

            if ($tk) {
                $token = $tk->token;
                // Update existing token to reset state and extend validity
                $ok = $wpdb->update(self::table(), [
                    'expires_at' => $expires_at,
                    'sent_at' => $sent_at,
                    'is_used' => 0,
                    'opened_at' => null,
                    'video_path' => null,
                    'video_filename' => null,
                    'submitted_at' => null
                ], ['id' => $tk->id]);
                if ($ok === false)
                    return false;
            } else {
                $token = bin2hex(random_bytes(32));
                $ok = $wpdb->insert(self::table(), [
                    'candidate_id' => $candidate_id,
                    'token' => $token,
                    'expires_at' => $expires_at,
                    'is_used' => 0,
                    'sent_at' => $sent_at,
                ]);
                if (!$ok)
                    return false;
            }

            IM_Candidate::update_status($candidate_id, 'invited');
            return add_query_arg('im_token', $token, IM_INTERVIEW_PAGE_URL);
        }

        public static function validate(string $token): ?object
        {
            global $wpdb;
            return $wpdb->get_row($wpdb->prepare(
                'SELECT * FROM ' . self::table() . ' WHERE token=%s AND is_used=0 AND expires_at>%s',
                $token,
                current_time('mysql')
            )) ?: null;
        }

        public static function mark_opened(int $id): bool
        {
            global $wpdb;
            $now = current_time('mysql');
            $expires = date('Y-m-d H:i:s', current_time('timestamp') + 86400); // 24 hours
            return (bool) $wpdb->query($wpdb->prepare(
                'UPDATE ' . self::table() . ' SET opened_at = %s, expires_at = %s WHERE id = %d AND opened_at IS NULL',
                $now,
                $expires,
                $id
            ));
        }

        public static function get_by_token(string $token): ?object
        {
            global $wpdb;
            return $wpdb->get_row($wpdb->prepare(
                'SELECT * FROM ' . self::table() . ' WHERE token=%s',
                $token
            )) ?: null;
        }

        public static function get_by_candidate(int $candidate_id): array
        {
            global $wpdb;
            return $wpdb->get_results($wpdb->prepare(
                'SELECT * FROM ' . self::table() . ' WHERE candidate_id=%d ORDER BY created_at DESC',
                $candidate_id
            )) ?: [];
        }

        public static function mark_used(int $id, string $path, string $filename): bool
        {
            global $wpdb;
            return (bool) $wpdb->update(self::table(), [
                'is_used' => 1,
                'video_path' => $path,
                'video_filename' => $filename,
                'submitted_at' => current_time('mysql'),
            ], ['id' => $id]);
        }
    }
} // end IM_Token

/* ============================================================
   IM_Mailer
   ============================================================ */
if (!class_exists('IM_Mailer')) {
    class IM_Mailer
    {

        /**
         * Join Us 简表单提交后 → 发给候选人的确认邮件
         * 邮件中包含详细申请表单的专属链接（带 apply_token）
         */
        public static function send_joinus_confirmation(int $candidate_id): bool
        {
            $c = IM_Candidate::get($candidate_id);
            if (!$c)
                return false;

            $name = esc_html($c->first_name);
            $apply_url = add_query_arg('im_apply_token', $c->apply_token, IM_APPLY_PAGE_URL);
            $subject = 'Next Step: Complete Your Application';

            return self::send($c->email, $subject, self::wrap($subject, "
            <p>Dear {$name},</p>
            <p>Thank you for your interest in joining YStar Edu. We have received your initial registration.</p>
            <p>To proceed, please complete the detailed application form by clicking the button below. This form will collect additional information such as your educational background, language proficiency, and teaching subjects.</p>
            <p style='text-align:center;margin:36px 0'>
                <a href='{$apply_url}'
                   style='display:inline-block;background:linear-gradient(135deg,#009dff,#3de1fe,#53eee0);color:#fff;padding:15px 40px;
                          border-radius:8px;text-decoration:none;font-size:16px;font-weight:700'>
                    Complete Application Form
                </a>
            </p>
            <p style='color:#6b7280;font-size:13px'>If the button does not work, you may copy and paste the following link into your browser:<br>
                <a href='{$apply_url}' style='color:#009dff'>{$apply_url}</a>
            </p>
            <p>If you have any questions, please feel free to reply to this email. We are happy to assist you.</p>
            <p>Best regards,<br>Recruitment Team<br>YStar Edu</p>
        "));
        }

        /**
         * 详细申请表单提交后 → 发给候选人的收件确认
         */
        public static function send_apply_confirmation(int $candidate_id): bool
        {
            $c = IM_Candidate::get($candidate_id);
            if (!$c)
                return false;

            $name = esc_html($c->preferred_name ?: $c->first_name);
            $subject = 'Application Received — We Will Contact You Soon';

            return self::send($c->email, $subject, self::wrap($subject, "
            <p>Dear {$name},</p>
            <p>Your application form has been successfully submitted! Our team will contact you after review.</p>
            <p>The review process typically takes 3–5 business days. Thank you for your patience.</p>
            <p>Best regards,<br>Recruitment Team<br>YStar Edu</p>
        "));
        }

        /**
         * 后台手动触发 → 发送面试邀请（含 24h 限时链接）
         */
        public static function send_interview_invite(int $candidate_id): bool
        {
            $c = IM_Candidate::get($candidate_id);
            if (!$c)
                return false;

            $url = IM_Token::generate($candidate_id);
            if (!$url)
                return false;

            $name = esc_html($c->preferred_name ?: $c->first_name);
            $expires = date('F d, Y \a\t g:i A', current_time('timestamp') + 86400 * 3);
            $subject = 'Interview Invitation — Action Required';

            return self::send($c->email, $subject, self::wrap($subject, "
            <p>Dear {$name},</p>
            <p>Thank you for submitting your application. We are pleased to invite you to a video interview.</p>
            <p>Your interview will focus on the subjects you selected in your application. The questions are designed to assess your subject knowledge and teaching ability.</p>
            <p>You will be asked up to three questions in total.</p>
            <p>Please click the button below to access the interview page. You will be able to review the questions and upload your video.</p>
            <p>Supported formats: mp4, mov, avi. Maximum file size: 500 MB.</p>
            <p style='text-align:center;margin:36px 0'>
                <a href='{$url}'
                   style='display:inline-block;background:linear-gradient(135deg,#009dff,#3de1fe,#53eee0);color:#fff;padding:15px 40px;
                          border-radius:8px;text-decoration:none;font-size:16px;font-weight:700'>
                    Go to Interview Page
                </a>
            </p>
            <div style='background:#fff3cd;border:1px solid #ffc107;border-radius:8px;padding:16px;margin:24px 0'>
                <strong>Important Notice</strong><br>
                1. This link is valid until <strong>{$expires}</strong>.<br>
                2. Once you open the interview page, you will have <strong>24 hours</strong> to complete and submit your video. Please ensure you are ready before proceeding.
            </div>
            <p>If you experience any technical issues, please reply to this email.</p>
            <p>Good luck with your interview!</p>
            <p>Best regards,<br>Recruitment Team<br>YStar Edu</p>
        "));
        }

        private static function send(string $to, string $subject, string $html): bool
        {
            // Let SMTP plugin handle From header; explicitly set Reply-To to match
            $from_email = apply_filters('wp_mail_from', get_option('admin_email'));
            $from_name = apply_filters('wp_mail_from_name', get_bloginfo('name'));
            return wp_mail($to, $subject, $html, [
                'Content-Type: text/html; charset=UTF-8',
                'Reply-To: ' . $from_name . ' <' . $from_email . '>',
            ]);
        }

        public static function send_reject(int $candidate_id): bool
        {
            $c = IM_Candidate::get($candidate_id);
            if (!$c)
                return false;
            $name = esc_html($c->preferred_name ?: $c->first_name);
            $subject = 'Update on Your Application';

            return self::send($c->email, $subject, self::wrap($subject, "
            <p>Dear {$name},</p>
            <p>Thank you for taking the time to apply and for your interest in joining YStar Edu.</p>
            <p>After careful review, we regret to inform you that we will not be moving forward with your application at this time.</p>
            <p>We appreciate your interest and wish you all the best in your future endeavors.</p>
            <p>Best regards,<br>Recruitment Team<br>YStar Edu</p>
        "));
        }

        public static function send_hire(int $candidate_id): bool
        {
            $c = IM_Candidate::get($candidate_id);
            if (!$c)
                return false;
            $name = esc_html($c->preferred_name ?: $c->first_name);
            $training_url = IM_Candidate::generate_training_token($candidate_id);
            if (!$training_url)
                return false;
            $subject = 'YSTAR EDU | Tutor Offer & Onboarding Training';

            return self::send($c->email, $subject, self::wrap($subject, "
            <p>Dear {$name},</p>
            <p>Congratulations and welcome to YSTAR EDU!</p>
            <p>We are pleased to inform you that you have successfully passed the interview process and have been selected to join our team as a <strong>Tutor</strong>.</p>
            <p>Before starting classes, all new tutors are required to complete our online onboarding training (E-learning).</p>
            <p>Please access the onboarding training using the link below:</p>
            <p style='text-align:center;margin:28px 0'>
                <a href='{$training_url}'
                   style='display:inline-block;background:linear-gradient(135deg,#009dff,#3de1fe,#53eee0);color:#fff;padding:15px 40px;
                          border-radius:8px;text-decoration:none;font-size:16px;font-weight:700'>
                    Start Onboarding Training
                </a>
            </p>
            <p style='color:#6b7280;font-size:13px'>If the button does not work, you may copy and paste the following link into your browser:<br>
                <a href='{$training_url}' style='color:#009dff'>{$training_url}</a>
            </p>
            <h3 style='color:#009dff;margin:24px 0 12px'>Important Notes</h3>
            <ul style='padding-left:20px;color:#374151'>
                <li>The onboarding training includes key information about our teaching platform, class procedures, and tutoring standards.</li>
                <li>The E-learning training requires a <strong>100% passing score</strong> to ensure all tutors are fully prepared before teaching.</li>
                <li>Once the training is successfully completed, you will receive a <strong>trial Microsoft Teams tutoring account</strong>.<br>
                    This account will be used for trial classes and initial tutoring sessions.<br>
                    A personal tutoring account will be issued once you are assigned official students.</li>
            </ul>
            <p>We recommend completing the onboarding training as soon as possible so that your tutoring account can be activated.</p>
            <p>We are excited to welcome you to YSTAR EDU and look forward to working with you.</p>
            <p>Best regards,<br>Recruitment Team<br>YStar Edu</p>
        "));
        }

        public static function send_training_complete(int $candidate_id): bool
        {
            $c = IM_Candidate::get($candidate_id);
            if (!$c)
                return false;
            $name = esc_html($c->preferred_name ?: $c->first_name);
            $account = esc_html(IM_TRAINING_ACCOUNT);
            $password = esc_html(IM_TRAINING_PASSWORD);
            $subject = 'Your Microsoft Teams Trial Account – YStar Edu';

            return self::send($c->email, $subject, self::wrap($subject, "
            <p>Dear {$name},</p>
            <p>Congratulations! You have successfully completed the onboarding training with a <strong>100% passing score</strong>.</p>
            <p>Here are your Microsoft Teams trial account details for tutoring:</p>
            <div style='background:#f0f9ff;border:1px solid #bae6fd;border-radius:10px;padding:20px 24px;margin:24px 0'>
                <table style='width:100%;border-collapse:collapse'>
                    <tr>
                        <td style='padding:8px 0;font-weight:700;color:#374151;width:180px'>Account Email:</td>
                        <td style='padding:8px 0;color:#009dff;font-weight:600;font-size:16px'>{$account}</td>
                    </tr>
                    <tr>
                        <td style='padding:8px 0;font-weight:700;color:#374151'>Temporary Password:</td>
                        <td style='padding:8px 0;color:#009dff;font-weight:600;font-size:16px'>{$password}</td>
                    </tr>
                </table>
            </div>
            <p>Please log in to your account and familiarize yourself with Microsoft Teams before your first lesson.</p>
            <div style='background:#fff3cd;border:1px solid #ffc107;border-radius:8px;padding:16px;margin:24px 0'>
                <strong>Important Notice:</strong><br>
                This is a temporary account for your initial tutoring sessions.<br>
                A permanent account will be provided once you are assigned official students.
            </div>
            <p>If you have any questions about account setup or your first session, feel free to reply to this email — we're happy to help.</p>
            <p>Welcome to YStar Edu!</p>
            <p>Best regards,<br>Recruitment Team<br>YStar Edu</p>
        "));
        }

        private static function wrap(string $title, string $content): string
        {
            $site = get_bloginfo('name');
            return <<<HTML
<!DOCTYPE html><html lang="en"><head><meta charset="UTF-8"><title>{$title}</title></head>
<body style="margin:0;padding:0;background:#f4f6f9;font-family:-apple-system,BlinkMacSystemFont,'Segoe UI','PingFang SC',sans-serif">
<div style="max-width:600px;margin:40px auto;background:#fff;border-radius:12px;overflow:hidden;box-shadow:0 2px 12px rgba(0,0,0,.08)">
  <div style="padding:0;text-align:center;font-size:0;line-height:0">
    <img src="https://ystaredu.ca/wp-content/uploads/2026/03/new_logo-scaled22.webp" alt="{$site}" style="width:100%;display:block">
  </div>
  <div style="padding:36px 40px;color:#374151;font-size:15px;line-height:1.8">{$content}</div>
  <div style="padding:0;text-align:center;font-size:0;line-height:0">
    <img src="https://ystaredu.ca/wp-content/uploads/2026/03/footer-mail.webp" alt="{$site}" style="width:100%;display:block">
  </div>
  <div style="padding:28px 40px;text-align:center;background:#fff">
    <img src="https://ystaredu.ca/wp-content/uploads/2026/03/colors-h-logo.jpg" alt="YStar Edu" style="max-height:40px;width:auto;margin-bottom:16px">
    <div style="font-size:13px;color:#6b7280;line-height:1.8">
      <p style="margin:0 0 4px">This email was sent by YStar Edu</p>
      <p style="margin:0 0 4px">204 - 1455 16TH AVE RICHMOND HILL, ON</p>
      <p style="margin:0">To learn more about YStar Edu, please <a href="https://ystaredu.ca/" style="color:#009dff;text-decoration:none;font-weight:600">click here</a> to visit our website.</p>
    </div>
  </div>
</div>
</body></html>
HTML;
        }
    }
} // end IM_Mailer