CREATE TABLE IF NOT EXISTS assessment_tokens (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 application_id INT UNSIGNED NOT NULL,
 token VARCHAR(80) NOT NULL UNIQUE,
 assessment_type ENUM('disc','dope') NOT NULL,
 status ENUM('pending','completed') NOT NULL DEFAULT 'pending',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 completed_at TIMESTAMP NULL,
 CONSTRAINT fk_assessment_application
   FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS assessment_answers (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 assessment_token_id INT UNSIGNED NOT NULL,
 question_no INT NOT NULL,
 answer_key VARCHAR(30) NOT NULL,
 score_value INT NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_assessment_answer_token
   FOREIGN KEY (assessment_token_id) REFERENCES assessment_tokens(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS assessment_results (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 assessment_token_id INT UNSIGNED NOT NULL UNIQUE,
 primary_type VARCHAR(30) NOT NULL,
 secondary_type VARCHAR(30) NULL,
 result_json LONGTEXT NOT NULL,
 admin_summary TEXT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_assessment_result_token
   FOREIGN KEY (assessment_token_id) REFERENCES assessment_tokens(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;