<?php
namespace app\mobile\service;

use think\facade\Db;

/** 新表均为InnoDB；时间为Unix秒，学员按字符串工号关联。 */
class KeyTrainingSchema
{
    public static function install()
    {
        $prefix=Db::connect()->getConfig('prefix');
        if(!preg_match('/^[a-zA-Z0-9_]*$/D',$prefix))throw new \RuntimeException('Invalid database prefix');
        $tables=[
            'kt_template'=>"id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,skill_id INT NOT NULL,title VARCHAR(120) NOT NULL,teacher_ids TEXT NOT NULL,duration_minutes INT NOT NULL DEFAULT 0,sort INT NOT NULL DEFAULT 0,active TINYINT NOT NULL DEFAULT 1,UNIQUE KEY item_title(skill_id,title)",
            'kt_plan'=>"id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,skill_id INT NOT NULL,title VARCHAR(120) NOT NULL,status VARCHAR(20) NOT NULL DEFAULT 'draft',version INT NOT NULL DEFAULT 1,created_by INT NOT NULL,created_at INT NOT NULL,updated_at INT NOT NULL",
            'kt_course'=>"id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,plan_id INT NOT NULL,template_id INT NOT NULL DEFAULT 0,title VARCHAR(120) NOT NULL,teacher_ids TEXT NOT NULL,admin_id INT NOT NULL,start_at INT NOT NULL,end_at INT NOT NULL,location VARCHAR(200) NOT NULL,sign_at INT NOT NULL DEFAULT 0,confirmed_at INT NOT NULL DEFAULT 0,removed TINYINT NOT NULL DEFAULT 0,KEY plan_courses(plan_id,removed),KEY teacher_courses(admin_id,start_at)",
            'kt_registration'=>"id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(32) NOT NULL,ename VARCHAR(100) NOT NULL,skill_id INT NOT NULL,exam_item_id INT NOT NULL DEFAULT 0,applicant_admin_id INT NOT NULL,source VARCHAR(10) NOT NULL,plan_id INT NOT NULL DEFAULT 0,cancelled_at INT NOT NULL DEFAULT 0,cancel_reason VARCHAR(500) NOT NULL DEFAULT '',completed_at INT NOT NULL DEFAULT 0,exam_error VARCHAR(500) NOT NULL DEFAULT '',created_at INT NOT NULL,KEY employee_skill(username,skill_id),KEY applicant(applicant_admin_id,id),KEY plan_people(plan_id)",
            'kt_attendance'=>"id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,registration_id INT NOT NULL,course_id INT NOT NULL,username VARCHAR(32) NOT NULL,attendance VARCHAR(12) NOT NULL DEFAULT 'pending',rating DECIMAL(3,2) NOT NULL DEFAULT 0,feedback_items TEXT NULL,feedback VARCHAR(500) NOT NULL DEFAULT '',rated_at INT NOT NULL DEFAULT 0,UNIQUE KEY one_student_course(registration_id,course_id),KEY course_people(course_id)",
            'kt_restriction'=>"id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(32) NOT NULL,registration_id INT NOT NULL,starts_at INT NOT NULL,until_at INT NOT NULL,released_at INT NOT NULL DEFAULT 0,released_by INT NOT NULL DEFAULT 0,release_reason VARCHAR(500) NOT NULL DEFAULT '',UNIQUE KEY one_cancellation(registration_id),KEY employee_restrictions(username,until_at)",
            'kt_exam_job'=>"id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,registration_id INT NOT NULL,slot VARCHAR(64) NOT NULL,exam_item_id INT NOT NULL DEFAULT 0,exam_at INT NOT NULL,exam_id INT NOT NULL DEFAULT 0,source VARCHAR(10) NOT NULL,error VARCHAR(500) NOT NULL DEFAULT '',created_at INT NOT NULL,UNIQUE KEY one_job(registration_id,slot),KEY pending_jobs(exam_id)",
            'kt_log'=>"id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,actor_admin_id INT NOT NULL,target_type VARCHAR(30) NOT NULL,target_id INT NOT NULL,action VARCHAR(40) NOT NULL,reason VARCHAR(500) NOT NULL,before_data MEDIUMTEXT NOT NULL,after_data MEDIUMTEXT NOT NULL,created_at INT NOT NULL,KEY target_logs(target_type,target_id,id)"
        ];
        foreach($tables as $name=>$definition)Db::execute('CREATE TABLE IF NOT EXISTS `'.$prefix.$name.'` ('.$definition.') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');
        // 开发期间首次方案尚无业务数据；禁止把已有试卷ID静默解释成技能ID。
        foreach(['kt_template','kt_plan','kt_registration'] as $name){
            if(Db::query("SHOW COLUMNS FROM `".$prefix.$name."` LIKE 'item_id'")){
                if(Db::name($name)->count())throw new \RuntimeException('旧培训表含业务数据，请先核对技能与试卷映射：'.$name);
                Db::execute('ALTER TABLE `'.$prefix.$name.'` CHANGE COLUMN item_id skill_id INT NOT NULL');
            }
        }
        foreach(['kt_registration','kt_exam_job'] as $name)if(!Db::query("SHOW COLUMNS FROM `".$prefix.$name."` LIKE 'exam_item_id'"))Db::execute('ALTER TABLE `'.$prefix.$name.'` ADD COLUMN exam_item_id INT NOT NULL DEFAULT 0');
        $columns=Db::query("SHOW COLUMNS FROM `".$prefix."quality_newskill_score` LIKE 'training_job_id'");
        if(!$columns)Db::execute('ALTER TABLE `'.$prefix.'quality_newskill_score` ADD COLUMN training_job_id INT UNSIGNED NULL DEFAULT NULL, ADD UNIQUE KEY uq_training_job(training_job_id)');
        self::upgradeFeedback();
        self::upgradeDuration();
        $connection=Db::connect();$cache=$connection->getConfig('schema_cache_path');$database=$connection->getConfig('database');
        if($cache&&preg_match('/^[a-zA-Z0-9_]+$/D',$database))foreach(array_merge(array_keys($tables),['quality_newskill_score','admin_menus_wx','admin_menus']) as $table){
            $file=$cache.$database.'.'.$prefix.$table.'.php';if(is_file($file)&&!unlink($file))throw new \RuntimeException('无法清理表结构缓存：'.$table);
        }
    }
    public static function upgradeDuration()
    {
        $connection=Db::connect();$prefix=$connection->getConfig('prefix');
        if(!preg_match('/^[a-zA-Z0-9_]*$/D',$prefix))throw new \RuntimeException('Invalid database prefix');
        $table=$prefix.'kt_template';
        if(!Db::query("SHOW COLUMNS FROM `".$table."` LIKE 'duration_minutes'"))Db::execute('ALTER TABLE `'.$table.'` ADD COLUMN duration_minutes INT NOT NULL DEFAULT 0');
        $cache=$connection->getConfig('schema_cache_path');$database=$connection->getConfig('database');
        if($cache&&preg_match('/^[a-zA-Z0-9_]+$/D',$database)){
            $file=$cache.$database.'.'.$table.'.php';if(is_file($file)&&!unlink($file))throw new \RuntimeException('无法清理固定课程表结构缓存');
        }
    }
    public static function upgradeFeedback()
    {
        $prefix=Db::connect()->getConfig('prefix');
        if(!preg_match('/^[a-zA-Z0-9_]*$/D',$prefix))throw new \RuntimeException('Invalid database prefix');
        $table=$prefix.'kt_attendance';
        if(!Db::query("SHOW COLUMNS FROM `".$table."` LIKE 'feedback_items'"))Db::execute('ALTER TABLE `'.$table.'` ADD COLUMN feedback_items TEXT NULL');
        $rating=Db::query("SHOW COLUMNS FROM `".$table."` LIKE 'rating'");
        if(strtolower($rating[0]['Type'])!=='decimal(3,2)')Db::execute('ALTER TABLE `'.$table.'` MODIFY COLUMN rating DECIMAL(3,2) NOT NULL DEFAULT 0');
        $connection=Db::connect();$cache=$connection->getConfig('schema_cache_path');$database=$connection->getConfig('database');
        if($cache&&preg_match('/^[a-zA-Z0-9_]+$/D',$database)){
            $file=$cache.$database.'.'.$table.'.php';if(is_file($file)&&!unlink($file))throw new \RuntimeException('无法清理培训反馈表结构缓存');
        }
    }

}