-- Hybrid AI 운영 시스템용 신규 테이블
-- 기존 테이블 변경 없음

CREATE TABLE IF NOT EXISTS ns20_1_ai_review (
  id INT NOT NULL AUTO_INCREMENT,
  report_id INT NOT NULL,
  deviceid VARCHAR(20) NOT NULL,
  farm_name VARCHAR(100) DEFAULT NULL,
  reviewer_name VARCHAR(100) DEFAULT NULL,
  review_status ENUM('AI원본','검토완료','수정완료','승인') NOT NULL DEFAULT 'AI원본',
  risk_override ENUM('적정','주의','위험') DEFAULT NULL,
  confidence_score TINYINT NOT NULL DEFAULT 3,
  corrected_summary TEXT,
  expert_comment MEDIUMTEXT,
  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 uq_ns20_1_ai_review_report_id (report_id),
  KEY idx_ns20_1_ai_review_deviceid (deviceid),
  KEY idx_ns20_1_ai_review_status (review_status),
  KEY idx_ns20_1_ai_review_risk (risk_override)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ns20_1_ai_manager_review (
  id INT NOT NULL AUTO_INCREMENT,
  report_id INT NOT NULL,
  reviewer_name VARCHAR(100) DEFAULT NULL,
  review_status ENUM('AI원본','검토완료','수정완료','승인') NOT NULL DEFAULT 'AI원본',
  risk_override ENUM('적정','주의','위험') DEFAULT NULL,
  confidence_score TINYINT NOT NULL DEFAULT 3,
  corrected_summary TEXT,
  expert_comment MEDIUMTEXT,
  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 uq_ns20_1_ai_manager_review_report_id (report_id),
  KEY idx_ns20_1_ai_manager_review_status (review_status),
  KEY idx_ns20_1_ai_manager_review_risk (risk_override)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ns20_1_ai_training_dataset (
  id INT NOT NULL AUTO_INCREMENT,
  source_scope ENUM('device','manager') NOT NULL,
  source_review_id INT DEFAULT NULL,
  report_id INT NOT NULL,
  deviceid VARCHAR(20) DEFAULT NULL,
  farm_name VARCHAR(100) DEFAULT NULL,
  report_date DATE DEFAULT NULL,
  sensor_data LONGTEXT,
  json_file VARCHAR(255) DEFAULT NULL,
  ai_status VARCHAR(20) DEFAULT NULL,
  expert_status VARCHAR(20) DEFAULT NULL,
  review_status VARCHAR(20) DEFAULT NULL,
  corrected_summary TEXT,
  expert_comment MEDIUMTEXT,
  confidence_score TINYINT DEFAULT NULL,
  dataset_json LONGTEXT,
  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 uq_ns20_1_ai_training_dataset_scope_report (source_scope, report_id),
  KEY idx_ns20_1_ai_training_dataset_deviceid (deviceid),
  KEY idx_ns20_1_ai_training_dataset_report_date (report_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
