DROP DATABASE IF EXISTS ai_interview_db;
CREATE DATABASE ai_interview_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ai_interview_db;
CREATE TABLE positions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(180), department VARCHAR(120), description TEXT, presentation_title VARCHAR(255), created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL);
CREATE TABLE applications (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED, position_id BIGINT UNSIGNED, interviewer_id BIGINT UNSIGNED, status VARCHAR(50) DEFAULT 'assigned', personal_verified TINYINT(1) DEFAULT 0, ai_score DECIMAL(5,2) NULL, final_score DECIMAL(5,2) NULL, remarks TEXT NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL);
CREATE TABLE questions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, application_id BIGINT UNSIGNED, question TEXT, keywords TEXT, marks INT DEFAULT 10, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL);
CREATE TABLE answers (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, application_id BIGINT UNSIGNED, question_id BIGINT UNSIGNED, answer TEXT, score DECIMAL(6,2), created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL);
CREATE TABLE evidence (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, application_id BIGINT UNSIGNED, file_path VARCHAR(255), file_type VARCHAR(100), created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL);
INSERT INTO positions(title,department,description,presentation_title,created_at,updated_at) VALUES ('Software Developer','IT','Laravel/PHP developer position','Design a secure web-based student service system in 10 minutes',NOW(),NOW());
INSERT INTO applications(user_id,position_id,interviewer_id,status,created_at,updated_at) VALUES (3,1,2,'assigned',NOW(),NOW());
INSERT INTO questions(application_id,question,keywords,marks,created_at,updated_at) VALUES
(1,'Explain your proposed system architecture for the assigned presentation title.','architecture,database,security,interface,workflow',20,NOW(),NOW()),
(1,'How will you protect candidate data and recorded interview evidence?','authentication,authorization,encryption,privacy,backup',20,NOW(),NOW());
