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 users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150), email VARCHAR(150) UNIQUE, phone VARCHAR(50), password VARCHAR(255), role ENUM('admin','interviewer','interviewee') NOT NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL);
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 users(name,email,phone,password,role,created_at,updated_at) VALUES
('System Admin','admin@local.test','','$2y$12$/Mt8jqSalKcv5YHWaRy3pe7R8WQyIk71oDHQQ/T9Z1ddoI.ZtPTdG','admin',NOW(),NOW()),
('Dr Interviewer','interviewer@local.test','0123456789','$2y$12$/Mt8jqSalKcv5YHWaRy3pe7R8WQyIk71oDHQQ/T9Z1ddoI.ZtPTdG','interviewer',NOW(),NOW()),
('Sample Interviewee','candidate@local.test','0111111111','$2y$12$/Mt8jqSalKcv5YHWaRy3pe7R8WQyIk71oDHQQ/T9Z1ddoI.ZtPTdG','interviewee',NOW(),NOW());
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());