<?php
/**
 * Svc_Talent — جذب و شناسایی کارشناس از فضای مجازی
 * Pipeline: ثبت‌نام → هویت دیجیتال → معرفی حرفه‌ای → ارزیابی آنلاین → بررسی
 */
if ( ! defined( 'ABSPATH' ) ) exit;

class Svc_Talent {

    public const PASS_THRESHOLD = 70.0;
    public const ASSESSMENT_TIME_SEC = 1800;
    public const QUESTIONS_PER_CATEGORY = 3;

    /** @var array<string,string> */
    public static $STATUSES = [
        'draft'           => 'پیش‌نویس',
        'profile'         => 'پروفایل تکمیل',
        'identity'        => 'هویت دیجیتال',
        'assessed'        => 'ارزیابی انجام شد',
        'pending_review'  => 'در انتظار بررسی',
        'approved'        => 'تأیید شده',
        'rejected'        => 'رد شده',
    ];

    /** @var array<string,string> */
    public static $SPECIALTIES = [
        'chiller'           => 'چیلر و سرمایش صنعتی',
        'refrigeration'     => 'سردخانه و تبرید',
        'vrf_vrv'           => 'VRF / VRV',
        'commercial_hvac'   => 'HVAC تجاری',
        'residential_hvac'  => 'اسپلیت و خانگی',
        'controls_bms'      => 'کنترل و BMS',
        'electrical'        => 'برق و اتوماسیون',
    ];

    /** @var array<string,string> */
    public static $PLATFORMS = [
        'linkedin'   => 'LinkedIn',
        'instagram'  => 'Instagram',
        'telegram'   => 'Telegram',
        'youtube'    => 'YouTube',
        'aparat'     => 'آپارات',
        'portfolio'  => 'وب‌سایت / پورتفولیو',
        'github'     => 'GitHub',
        'other'      => 'سایر',
    ];

    /** @var array<string,string> */
    public static $CATEGORIES = [
        'refrigeration' => 'مبانی تبرید',
        'chiller'       => 'سیستم‌های چیلر',
        'safety'        => 'ایمنی و استاندارد',
        'troubleshoot'  => 'عیب‌یابی',
        'service'       => 'خدمات مشتری',
    ];

    public static function applications_table(): string {
        global $wpdb;
        return $wpdb->prefix . 'svc_talent_applications';
    }

    public static function identities_table(): string {
        global $wpdb;
        return $wpdb->prefix . 'svc_talent_identities';
    }

    public static function certs_table(): string {
        global $wpdb;
        return $wpdb->prefix . 'svc_talent_certificates';
    }

    public static function questions_table(): string {
        global $wpdb;
        return $wpdb->prefix . 'svc_talent_questions';
    }

    public static function options_table(): string {
        global $wpdb;
        return $wpdb->prefix . 'svc_talent_question_options';
    }

    public static function attempts_table(): string {
        global $wpdb;
        return $wpdb->prefix . 'svc_talent_assessment_attempts';
    }

    public static function tables_exist(): bool {
        global $wpdb;
        $t = self::applications_table();
        return $wpdb->get_var( "SHOW TABLES LIKE '{$t}'" ) === $t;
    }

    public static function generate_token(): string {
        return bin2hex( random_bytes( 24 ) );
    }

    public static function generate_applicant_no(): string {
        global $wpdb;
        $year = gmdate( 'Y' );
        $cnt  = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM " . self::applications_table() . " WHERE applicant_no LIKE %s",
            'TAL-' . $year . '-%'
        ) );
        return sprintf( 'TAL-%s-%05d', $year, $cnt + 1 );
    }

    public static function get_by_token( string $token ): ?array {
        if ( $token === '' || ! self::tables_exist() ) return null;
        global $wpdb;
        $row = $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM " . self::applications_table() . " WHERE access_token = %s LIMIT 1",
            $token
        ), ARRAY_A );
        return $row ?: null;
    }

    public static function get_by_id( int $id ): ?array {
        if ( $id <= 0 || ! self::tables_exist() ) return null;
        global $wpdb;
        $row = $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM " . self::applications_table() . " WHERE id = %d LIMIT 1",
            $id
        ), ARRAY_A );
        return $row ?: null;
    }

    public static function start_application( array $data ): array {
        global $wpdb;
        $token = self::generate_token();
        $now   = current_time( 'mysql' );

        $wpdb->insert( self::applications_table(), [
            'access_token'      => $token,
            'applicant_no'      => self::generate_applicant_no(),
            'status'            => 'draft',
            'full_name'         => sanitize_text_field( $data['full_name'] ?? '' ),
            'email'             => sanitize_email( $data['email'] ?? '' ),
            'phone'             => sanitize_text_field( $data['phone'] ?? '' ),
            'city'              => sanitize_text_field( $data['city'] ?? '' ),
            'province'          => sanitize_text_field( $data['province'] ?? '' ),
            'source_channel'    => sanitize_text_field( $data['source_channel'] ?? 'website' ),
            'referral_code'     => sanitize_text_field( $data['referral_code'] ?? '' ),
            'stage_step'        => 1,
            'created_at'        => $now,
            'updated_at'        => $now,
        ] );

        return self::format_application( self::get_by_id( (int) $wpdb->insert_id ) );
    }

    public static function update_profile( string $token, array $data ): ?array {
        $app = self::get_by_token( $token );
        if ( ! $app ) return null;

        global $wpdb;
        $skills = $data['secondary_skills'] ?? [];
        if ( is_array( $skills ) ) {
            $skills = array_values( array_filter( array_map( 'sanitize_text_field', $skills ) ) );
        } else {
            $skills = [];
        }

        $wpdb->update( self::applications_table(), [
            'full_name'          => sanitize_text_field( $data['full_name'] ?? $app['full_name'] ),
            'email'              => sanitize_email( $data['email'] ?? $app['email'] ),
            'phone'              => sanitize_text_field( $data['phone'] ?? $app['phone'] ),
            'city'               => sanitize_text_field( $data['city'] ?? $app['city'] ),
            'province'           => sanitize_text_field( $data['province'] ?? $app['province'] ),
            'source_channel'     => sanitize_text_field( $data['source_channel'] ?? $app['source_channel'] ),
            'referral_code'      => sanitize_text_field( $data['referral_code'] ?? $app['referral_code'] ),
            'years_experience'   => max( 0, (int) ( $data['years_experience'] ?? $app['years_experience'] ?? 0 ) ),
            'primary_specialty'  => sanitize_text_field( $data['primary_specialty'] ?? $app['primary_specialty'] ?? '' ),
            'secondary_skills'   => wp_json_encode( $skills, JSON_UNESCAPED_UNICODE ),
            'bio'                => sanitize_textarea_field( $data['bio'] ?? $app['bio'] ?? '' ),
            'cover_letter'       => sanitize_textarea_field( $data['cover_letter'] ?? $app['cover_letter'] ?? '' ),
            'preferred_work_type'=> sanitize_text_field( $data['preferred_work_type'] ?? $app['preferred_work_type'] ?? '' ),
            'availability'       => sanitize_text_field( $data['availability'] ?? $app['availability'] ?? '' ),
            'video_intro_url'    => esc_url_raw( $data['video_intro_url'] ?? $app['video_intro_url'] ?? '' ),
            'status'             => 'profile',
            'stage_step'         => max( (int) $app['stage_step'], 2 ),
            'updated_at'         => current_time( 'mysql' ),
        ], [ 'id' => (int) $app['id'] ] );

        return self::format_application( self::get_by_id( (int) $app['id'] ) );
    }

    public static function save_identities( string $token, array $identities, array $certificates = [] ): ?array {
        $app = self::get_by_token( $token );
        if ( ! $app ) return null;

        global $wpdb;
        $app_id = (int) $app['id'];

        $wpdb->delete( self::identities_table(), [ 'application_id' => $app_id ] );
        foreach ( $identities as $item ) {
            if ( empty( $item['profile_url'] ) && empty( $item['username'] ) ) continue;
            $platform = sanitize_text_field( $item['platform'] ?? 'other' );
            if ( ! isset( self::$PLATFORMS[ $platform ] ) ) $platform = 'other';
            $wpdb->insert( self::identities_table(), [
                'application_id'  => $app_id,
                'platform'        => $platform,
                'profile_url'     => esc_url_raw( $item['profile_url'] ?? '' ),
                'username'        => sanitize_text_field( $item['username'] ?? '' ),
                'followers_count' => max( 0, (int) ( $item['followers_count'] ?? 0 ) ),
                'verified'        => ! empty( $item['verified'] ) ? 1 : 0,
                'created_at'      => current_time( 'mysql' ),
            ] );
        }

        $wpdb->delete( self::certs_table(), [ 'application_id' => $app_id ] );
        foreach ( $certificates as $cert ) {
            if ( empty( $cert['title'] ) ) continue;
            $wpdb->insert( self::certs_table(), [
                'application_id' => $app_id,
                'title'          => sanitize_text_field( $cert['title'] ?? '' ),
                'issuer'         => sanitize_text_field( $cert['issuer'] ?? '' ),
                'cert_url'       => esc_url_raw( $cert['cert_url'] ?? '' ),
                'year'           => max( 0, (int) ( $cert['year'] ?? 0 ) ),
                'created_at'     => current_time( 'mysql' ),
            ] );
        }

        $score = self::calc_digital_score( $app_id );
        $wpdb->update( self::applications_table(), [
            'digital_presence_score' => $score,
            'status'                 => 'identity',
            'stage_step'             => max( (int) $app['stage_step'], 3 ),
            'updated_at'             => current_time( 'mysql' ),
        ], [ 'id' => $app_id ] );

        return self::format_application( self::get_by_id( $app_id ) );
    }

    public static function calc_digital_score( int $application_id ): float {
        global $wpdb;
        $identities = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM " . self::identities_table() . " WHERE application_id = %d",
            $application_id
        ) );
        $certs = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM " . self::certs_table() . " WHERE application_id = %d",
            $application_id
        ) );
        $app = self::get_by_id( $application_id );
        $score = min( 50, $identities * 10 );
        if ( ! empty( $app['video_intro_url'] ) ) $score += 15;
        if ( $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM " . self::identities_table() . " WHERE application_id = %d AND platform = 'portfolio'",
            $application_id
        ) ) ) {
            $score += 15;
        }
        $score += min( 20, $certs * 10 );
        if ( strlen( trim( $app['bio'] ?? '' ) ) >= 100 ) $score += 10;
        return min( 100.0, round( (float) $score, 1 ) );
    }

    public static function start_assessment( string $token ): ?array {
        $app = self::get_by_token( $token );
        if ( ! $app ) return null;

        $existing = self::get_active_attempt( (int) $app['id'] );
        if ( $existing ) {
            return self::format_attempt_for_client( $existing );
        }

        $question_ids = self::pick_assessment_questions();
        if ( ! $question_ids ) return null;

        global $wpdb;
        $now     = current_time( 'mysql' );
        $expires = date( 'Y-m-d H:i:s', time() + self::ASSESSMENT_TIME_SEC );

        $wpdb->insert( self::attempts_table(), [
            'application_id'  => (int) $app['id'],
            'attempt_no'      => self::next_attempt_no( (int) $app['id'] ),
            'status'          => 'in_progress',
            'question_ids'    => wp_json_encode( $question_ids ),
            'answers'         => '{}',
            'pass_threshold'  => self::PASS_THRESHOLD,
            'time_limit_sec'  => self::ASSESSMENT_TIME_SEC,
            'started_at'      => $now,
            'expires_at'      => $expires,
        ] );

        $attempt = $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM " . self::attempts_table() . " WHERE id = %d",
            (int) $wpdb->insert_id
        ), ARRAY_A );

        return self::format_attempt_for_client( $attempt );
    }

    public static function get_active_attempt( int $application_id ): ?array {
        global $wpdb;
        $row = $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM " . self::attempts_table() . "
             WHERE application_id = %d AND status = 'in_progress'
             ORDER BY id DESC LIMIT 1",
            $application_id
        ), ARRAY_A );
        if ( ! $row ) return null;
        if ( strtotime( $row['expires_at'] ) <= time() ) {
            $wpdb->update( self::attempts_table(), [ 'status' => 'expired' ], [ 'id' => (int) $row['id'] ] );
            return null;
        }
        return $row;
    }

    private static function next_attempt_no( int $application_id ): int {
        global $wpdb;
        return 1 + (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COALESCE(MAX(attempt_no),0) FROM " . self::attempts_table() . " WHERE application_id = %d",
            $application_id
        ) );
    }

    /** @return int[] */
    public static function pick_assessment_questions(): array {
        global $wpdb;
        $ids = [];
        foreach ( array_keys( self::$CATEGORIES ) as $cat ) {
            $rows = $wpdb->get_col( $wpdb->prepare(
                "SELECT id FROM " . self::questions_table() . "
                 WHERE category = %s AND is_active = 1
                 ORDER BY RAND() LIMIT %d",
                $cat,
                self::QUESTIONS_PER_CATEGORY
            ) ) ?: [];
            foreach ( $rows as $id ) {
                $ids[] = (int) $id;
            }
        }
        shuffle( $ids );
        return $ids;
    }

    public static function format_attempt_for_client( ?array $attempt ): ?array {
        if ( ! $attempt ) return null;
        $qids = json_decode( $attempt['question_ids'] ?? '[]', true ) ?: [];
        $questions = self::load_questions_for_client( $qids );
        return [
            'id'             => (int) $attempt['id'],
            'status'         => $attempt['status'],
            'attempt_no'     => (int) $attempt['attempt_no'],
            'time_limit_sec' => (int) $attempt['time_limit_sec'],
            'started_at'     => $attempt['started_at'],
            'expires_at'     => $attempt['expires_at'],
            'pass_threshold' => (float) $attempt['pass_threshold'],
            'questions'      => $questions,
        ];
    }

    /** @param int[] $ids */
    public static function load_questions_for_client( array $ids ): array {
        if ( ! $ids ) return [];
        global $wpdb;
        $ph  = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM " . self::questions_table() . " WHERE id IN ({$ph})",
            ...$ids
        ), ARRAY_A ) ?: [];

        $out = [];
        foreach ( $rows as $row ) {
            $opts = $wpdb->get_results( $wpdb->prepare(
                "SELECT id, option_text, sort_order FROM " . self::options_table() . "
                 WHERE question_id = %d ORDER BY sort_order, id",
                (int) $row['id']
            ), ARRAY_A ) ?: [];
            shuffle( $opts );
            $out[] = [
                'id'       => (int) $row['id'],
                'category' => $row['category'],
                'category_label' => self::$CATEGORIES[ $row['category'] ] ?? $row['category'],
                'difficulty' => $row['difficulty'],
                'text'     => $row['question_text'],
                'type'     => $row['question_type'],
                'points'   => (int) $row['points'],
                'options'  => array_map( static function ( $o ) {
                    return [
                        'id'   => (int) $o['id'],
                        'text' => $o['option_text'],
                    ];
                }, $opts ),
            ];
        }
        return $out;
    }

    public static function submit_assessment( string $token, array $answers ): ?array {
        $app = self::get_by_token( $token );
        if ( ! $app ) return null;

        $attempt = self::get_active_attempt( (int) $app['id'] );
        if ( ! $attempt ) return null;

        $qids = json_decode( $attempt['question_ids'] ?? '[]', true ) ?: [];
        $score_data = self::grade_answers( $qids, $answers );

        global $wpdb;
        $now = current_time( 'mysql' );
        $passed = $score_data['percent'] >= (float) $attempt['pass_threshold'];

        $wpdb->update( self::attempts_table(), [
            'status'       => 'submitted',
            'answers'      => wp_json_encode( $answers, JSON_UNESCAPED_UNICODE ),
            'score'        => $score_data['earned'],
            'max_score'    => $score_data['max'],
            'passed'       => $passed ? 1 : 0,
            'submitted_at' => $now,
        ], [ 'id' => (int) $attempt['id'] ] );

        $digital = (float) ( $app['digital_presence_score'] ?? 0 );
        $overall = round( ( $score_data['percent'] * 0.7 ) + ( $digital * 0.3 ), 1 );

        $wpdb->update( self::applications_table(), [
            'assessment_score' => $score_data['percent'],
            'assessment_pass'  => $passed ? 1 : 0,
            'overall_score'    => $overall,
            'status'           => 'assessed',
            'stage_step'       => 5,
            'submitted_at'     => $now,
            'updated_at'       => $now,
        ], [ 'id' => (int) $app['id'] ] );

        self::mark_pending_review( (int) $app['id'] );

        return self::get_result( $token );
    }

    private static function mark_pending_review( int $application_id ): void {
        global $wpdb;
        $wpdb->update( self::applications_table(), [
            'status'     => 'pending_review',
            'updated_at' => current_time( 'mysql' ),
        ], [ 'id' => $application_id ] );
    }

    /** @param int[] $qids */
    public static function grade_answers( array $qids, array $answers ): array {
        global $wpdb;
        if ( ! $qids ) {
            return [ 'earned' => 0, 'max' => 0, 'percent' => 0, 'breakdown' => [] ];
        }

        $ph   = implode( ',', array_fill( 0, count( $qids ), '%d' ) );
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT id, category, points, question_type FROM " . self::questions_table() . " WHERE id IN ({$ph})",
            ...$qids
        ), ARRAY_A ) ?: [];

        $earned = 0.0;
        $max    = 0.0;
        $breakdown = [];

        foreach ( $rows as $row ) {
            $qid   = (int) $row['id'];
            $pts   = (int) $row['points'];
            $max  += $pts;

            $correct_ids = $wpdb->get_col( $wpdb->prepare(
                "SELECT id FROM " . self::options_table() . " WHERE question_id = %d AND is_correct = 1",
                $qid
            ) ) ?: [];
            $correct_ids = array_map( 'intval', $correct_ids );

            $given = $answers[ (string) $qid ] ?? $answers[ $qid ] ?? null;
            if ( $row['question_type'] === 'multi' ) {
                $given_arr = is_array( $given ) ? array_map( 'intval', $given ) : [];
                sort( $given_arr );
                $exp = $correct_ids;
                sort( $exp );
                $ok = $given_arr === $exp;
            } else {
                $ok = in_array( (int) $given, $correct_ids, true );
            }

            if ( $ok ) $earned += $pts;
            $cat = $row['category'];
            if ( ! isset( $breakdown[ $cat ] ) ) {
                $breakdown[ $cat ] = [ 'earned' => 0, 'max' => 0, 'correct' => 0, 'total' => 0 ];
            }
            $breakdown[ $cat ]['earned'] += $ok ? $pts : 0;
            $breakdown[ $cat ]['max']    += $pts;
            $breakdown[ $cat ]['correct'] += $ok ? 1 : 0;
            $breakdown[ $cat ]['total']   += 1;
        }

        $percent = $max > 0 ? round( ( $earned / $max ) * 100, 1 ) : 0;
        return [
            'earned'    => $earned,
            'max'       => $max,
            'percent'   => $percent,
            'breakdown' => $breakdown,
        ];
    }

    public static function get_result( string $token ): ?array {
        $app = self::get_by_token( $token );
        if ( ! $app ) return null;

        global $wpdb;
        $attempt = $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM " . self::attempts_table() . "
             WHERE application_id = %d AND status = 'submitted'
             ORDER BY id DESC LIMIT 1",
            (int) $app['id']
        ), ARRAY_A );

        $breakdown = [];
        if ( $attempt ) {
            $answers = json_decode( $attempt['answers'] ?? '{}', true ) ?: [];
            $qids    = json_decode( $attempt['question_ids'] ?? '[]', true ) ?: [];
            $graded  = self::grade_answers( $qids, $answers );
            foreach ( $graded['breakdown'] as $cat => $b ) {
                $breakdown[] = [
                    'category'       => $cat,
                    'category_label' => self::$CATEGORIES[ $cat ] ?? $cat,
                    'correct'        => $b['correct'],
                    'total'          => $b['total'],
                    'percent'        => $b['max'] > 0 ? round( ( $b['earned'] / $b['max'] ) * 100, 1 ) : 0,
                ];
            }
        }

        return [
            'application' => self::format_application( $app ),
            'assessment'  => $attempt ? [
                'score'          => (float) $attempt['score'],
                'max_score'      => (float) $attempt['max_score'],
                'percent'        => (float) $app['assessment_score'],
                'passed'         => (bool) $attempt['passed'],
                'pass_threshold' => (float) $attempt['pass_threshold'],
                'submitted_at'   => $attempt['submitted_at'],
            ] : null,
            'breakdown'   => $breakdown,
            'next_steps'  => self::next_steps_for( $app ),
        ];
    }

    /** @return array<int,array<string,string>> */
    public static function next_steps_for( array $app ): array {
        $status = $app['status'] ?? '';
        if ( $status === 'approved' ) {
            return [
                [ 'icon' => '✅', 'title' => 'پذیرش نهایی', 'text' => 'حساب ERP شما فعال شد. از ورود کارشناس میدانی استفاده کنید.' ],
                [ 'icon' => '🎓', 'title' => 'آموزش محیطی', 'text' => 'جلسه معارفه آنلاین در ۴۸ ساعت آینده برای شما ارسال می‌شود.' ],
            ];
        }
        if ( $status === 'rejected' ) {
            return [
                [ 'icon' => '📩', 'title' => 'بازخورد', 'text' => 'نتیجه بررسی به ایمیل شما ارسال شده است. پس از ۹۰ روز می‌توانید دوباره درخواست دهید.' ],
            ];
        }
        if ( (float) ( $app['assessment_score'] ?? 0 ) < self::PASS_THRESHOLD ) {
            return [
                [ 'icon' => '📚', 'title' => 'تقویت مهارت', 'text' => 'امتیاز ارزیابی زیر حد نصاب است. منابع آموزشی رایگان برای شما ارسال می‌شود.' ],
                [ 'icon' => '🔄', 'title' => 'تلاش مجدد', 'text' => 'پس از ۳۰ روز می‌توانید ارزیابی را تکرار کنید.' ],
            ];
        }
        return [
            [ 'icon' => '🔍', 'title' => 'بررسی تیم جذب', 'text' => 'پروفایل و هویت دیجیتال شما در ۳–۵ روز کاری بررسی می‌شود.' ],
            [ 'icon' => '🎥', 'title' => 'مصاحبه مجازی', 'text' => 'در صورت تأیید اولیه، دعوت‌نامه مصاحبه ویدیویی ارسال می‌شود.' ],
            [ 'icon' => '🛡️', 'title' => 'اعتبارسنجی', 'text' => 'گواهینامه‌ها و حضور آنلاین شما با استانداردهای بین‌المللی تطبیق داده می‌شود.' ],
        ];
    }

    public static function format_application( ?array $row ): ?array {
        if ( ! $row ) return null;
        $skills = json_decode( $row['secondary_skills'] ?? '[]', true );
        if ( ! is_array( $skills ) ) $skills = [];

        return [
            'id'                     => (int) $row['id'],
            'access_token'           => $row['access_token'],
            'applicant_no'           => $row['applicant_no'],
            'status'                 => $row['status'],
            'status_label'           => self::$STATUSES[ $row['status'] ] ?? $row['status'],
            'stage_step'             => (int) $row['stage_step'],
            'full_name'              => $row['full_name'],
            'email'                  => $row['email'],
            'phone'                  => $row['phone'],
            'city'                   => $row['city'],
            'province'               => $row['province'],
            'years_experience'       => (int) $row['years_experience'],
            'primary_specialty'      => $row['primary_specialty'],
            'primary_specialty_label'=> self::$SPECIALTIES[ $row['primary_specialty'] ] ?? $row['primary_specialty'],
            'secondary_skills'       => $skills,
            'bio'                    => $row['bio'],
            'cover_letter'           => $row['cover_letter'],
            'preferred_work_type'    => $row['preferred_work_type'],
            'availability'           => $row['availability'],
            'video_intro_url'        => $row['video_intro_url'] ?? '',
            'source_channel'         => $row['source_channel'],
            'referral_code'          => $row['referral_code'],
            'digital_presence_score' => (float) $row['digital_presence_score'],
            'assessment_score'       => $row['assessment_score'] !== null ? (float) $row['assessment_score'] : null,
            'assessment_pass'        => (bool) $row['assessment_pass'],
            'overall_score'          => (float) $row['overall_score'],
            'review_notes'           => $row['review_notes'] ?? '',
            'created_at'             => $row['created_at'],
            'submitted_at'           => $row['submitted_at'] ?? null,
            'identities'             => self::load_identities( (int) $row['id'] ),
            'certificates'           => self::load_certificates( (int) $row['id'] ),
        ];
    }

    public static function load_identities( int $application_id ): array {
        global $wpdb;
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM " . self::identities_table() . " WHERE application_id = %d ORDER BY id",
            $application_id
        ), ARRAY_A ) ?: [];
        return array_map( static function ( $r ) {
            return [
                'id'              => (int) $r['id'],
                'platform'        => $r['platform'],
                'platform_label'  => self::$PLATFORMS[ $r['platform'] ] ?? $r['platform'],
                'profile_url'     => $r['profile_url'],
                'username'        => $r['username'],
                'followers_count' => (int) $r['followers_count'],
                'verified'        => (bool) $r['verified'],
            ];
        }, $rows );
    }

    public static function load_certificates( int $application_id ): array {
        global $wpdb;
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM " . self::certs_table() . " WHERE application_id = %d ORDER BY id",
            $application_id
        ), ARRAY_A ) ?: [];
        return array_map( static function ( $r ) {
            return [
                'id'       => (int) $r['id'],
                'title'    => $r['title'],
                'issuer'   => $r['issuer'],
                'cert_url' => $r['cert_url'],
                'year'     => (int) $r['year'],
            ];
        }, $rows );
    }

    public static function list_applications( array $filters = [] ): array {
        global $wpdb;
        $wheres = [ '1=1' ];
        $args   = [];

        if ( ! empty( $filters['status'] ) ) {
            $wheres[] = 'status = %s';
            $args[]   = $filters['status'];
        }
        if ( ! empty( $filters['q'] ) ) {
            $wheres[] = '(full_name LIKE %s OR email LIKE %s OR phone LIKE %s OR applicant_no LIKE %s)';
            $like = '%' . $wpdb->esc_like( $filters['q'] ) . '%';
            $args = array_merge( $args, [ $like, $like, $like, $like ] );
        }

        $limit  = max( 1, min( 100, (int) ( $filters['limit'] ?? 50 ) ) );
        $offset = max( 0, (int) ( $filters['offset'] ?? 0 ) );
        $sql    = "SELECT * FROM " . self::applications_table() . " WHERE " . implode( ' AND ', $wheres )
                . " ORDER BY updated_at DESC LIMIT {$limit} OFFSET {$offset}";

        if ( $args ) {
            $rows = $wpdb->get_results( $wpdb->prepare( $sql, ...$args ), ARRAY_A ) ?: [];
        } else {
            $rows = $wpdb->get_results( $sql, ARRAY_A ) ?: [];
        }

        return array_map( static function ( $r ) {
            $f = self::format_application( $r );
            unset( $f['access_token'] );
            return $f;
        }, $rows );
    }

    public static function review_application( int $id, string $action, int $reviewer_id, array $opts = [] ): ?array {
        $app = self::get_by_id( $id );
        if ( ! $app ) return null;

        global $wpdb;
        $now   = current_time( 'mysql' );
        $notes = sanitize_textarea_field( $opts['notes'] ?? '' );

        if ( $action === 'approve' ) {
            $user_id = self::provision_technician_user( $app, $opts );
            $wpdb->update( self::applications_table(), [
                'status'           => 'approved',
                'reviewer_id'      => $reviewer_id,
                'review_notes'     => $notes,
                'approved_user_id' => $user_id,
                'reviewed_at'      => $now,
                'updated_at'       => $now,
            ], [ 'id' => $id ] );

            if ( $user_id && ! empty( $opts['senior_user_id'] ) ) {
                require_once ACC_PATH . 'includes/helpers/svc-teams.php';
                Svc_Teams::add_team_member( (int) $opts['senior_user_id'], $user_id );
            }
        } else {
            $wpdb->update( self::applications_table(), [
                'status'       => 'rejected',
                'reviewer_id'  => $reviewer_id,
                'review_notes' => $notes,
                'reviewed_at'  => $now,
                'updated_at'   => $now,
            ], [ 'id' => $id ] );
        }

        return self::format_application( self::get_by_id( $id ) );
    }

    private static function provision_technician_user( array $app, array $opts ): int {
        global $wpdb;

        $existing = (int) ( $app['approved_user_id'] ?? 0 );
        if ( $existing > 0 ) return $existing;

        $email = sanitize_email( $app['email'] ?? '' );
        if ( $email ) {
            $by_email = (int) $wpdb->get_var( $wpdb->prepare(
                "SELECT ID FROM {$wpdb->users} WHERE user_email = %s LIMIT 1", $email
            ) );
            if ( $by_email ) {
                self::assign_technician_role( $by_email );
                return $by_email;
            }
        }

        $base_login = 'svc_talent_' . strtolower( preg_replace( '/[^a-z0-9]/i', '', substr( $app['applicant_no'] ?? 'x', -8 ) ) );
        $login = $base_login;
        $n = 1;
        while ( $wpdb->get_var( $wpdb->prepare( "SELECT ID FROM {$wpdb->users} WHERE user_login = %s", $login ) ) ) {
            $login = $base_login . $n++;
        }

        $pass = $opts['temp_password'] ?? wp_generate_password( 12, true );
        $new_id = (int) $wpdb->get_var( "SELECT COALESCE(MAX(ID),0)+1 FROM {$wpdb->users}" );
        $wpdb->insert( $wpdb->users, [
            'ID'              => $new_id,
            'user_login'      => $login,
            'user_pass'       => password_hash( $pass, PASSWORD_BCRYPT ),
            'user_nicename'   => $login,
            'user_email'      => $email ?: ( $login . '@talent.local' ),
            'user_registered' => current_time( 'mysql' ),
            'display_name'    => $app['full_name'] ?? $login,
        ] );

        self::assign_technician_role( $new_id );

        if ( class_exists( 'Svc_Rewards' ) ) {
            Svc_Rewards::get_or_create_account( 'technician', [
                'user_id'      => $new_id,
                'display_name' => $app['full_name'] ?? '',
                'phone'        => $app['phone'] ?? '',
            ] );
        }

        return $new_id;
    }

    public static function assign_technician_role( int $user_id ): void {
        global $wpdb;
        $role_id = (int) $wpdb->get_var(
            "SELECT id FROM {$wpdb->prefix}acl_roles WHERE slug = 'service_technician' LIMIT 1"
        );
        if ( ! $role_id ) return;
        $exists = $wpdb->get_var( $wpdb->prepare(
            "SELECT id FROM {$wpdb->prefix}acl_user_roles WHERE user_id = %d AND role_id = %d AND scope_type = 'global'",
            $user_id, $role_id
        ) );
        if ( ! $exists ) {
            $wpdb->insert( "{$wpdb->prefix}acl_user_roles", [
                'user_id'    => $user_id,
                'role_id'    => $role_id,
                'scope_type' => 'global',
                'scope_id'   => null,
            ] );
        }
    }

    public static function stats(): array {
        global $wpdb;
        $t = self::applications_table();
        $rows = $wpdb->get_results(
            "SELECT status, COUNT(*) AS cnt FROM {$t} GROUP BY status",
            ARRAY_A
        ) ?: [];
        $by_status = [];
        foreach ( $rows as $r ) {
            $by_status[ $r['status'] ] = (int) $r['cnt'];
        }
        return [
            'total'      => array_sum( $by_status ),
            'by_status'  => $by_status,
            'pending'    => (int) ( $by_status['pending_review'] ?? 0 ),
            'approved'   => (int) ( $by_status['approved'] ?? 0 ),
            'pass_rate'  => self::assessment_pass_rate(),
        ];
    }

    private static function assessment_pass_rate(): float {
        global $wpdb;
        $total = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM " . self::attempts_table() . " WHERE status = 'submitted'"
        );
        if ( ! $total ) return 0;
        $passed = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM " . self::attempts_table() . " WHERE status = 'submitted' AND passed = 1"
        );
        return round( ( $passed / $total ) * 100, 1 );
    }

    public static function meta(): array {
        return [
            'statuses'    => self::$STATUSES,
            'specialties' => self::$SPECIALTIES,
            'platforms'   => self::$PLATFORMS,
            'categories'  => self::$CATEGORIES,
            'pass_threshold' => self::PASS_THRESHOLD,
            'time_limit_min' => (int) ( self::ASSESSMENT_TIME_SEC / 60 ),
        ];
    }
}
