-- Create Database
CREATE DATABASE IF NOT EXISTS ultrasound_review_db;
USE ultrasound_review_db;

-- 1. users table
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('admin', 'doctor', 'reviewer') NOT NULL
);

-- 2. images table
CREATE TABLE IF NOT EXISTS images (
  id INT AUTO_INCREMENT PRIMARY KEY,
  original_image_path VARCHAR(255) NOT NULL,
  mask_image_path VARCHAR(255) NOT NULL
);

-- 3. questions table
CREATE TABLE IF NOT EXISTS questions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  question_text VARCHAR(255) NOT NULL,
  options JSON,
  sort_order INT NULL
);

-- 4. responses table
CREATE TABLE IF NOT EXISTS responses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  doctor_id INT NOT NULL,
  image_id INT NOT NULL,
  answers JSON NOT NULL,
  timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (doctor_id) REFERENCES users(id),
  FOREIGN KEY (image_id) REFERENCES images(id),
  UNIQUE KEY unique_response (doctor_id, image_id)
);

-- 5. reviews table
CREATE TABLE IF NOT EXISTS reviews (
  id INT AUTO_INCREMENT PRIMARY KEY,
  image_id INT NOT NULL,
  reviewer_id INT NOT NULL,
  doctor1_id INT NOT NULL,
  doctor2_id INT NOT NULL,
  comment TEXT,
  decision ENUM('Agree with Doctor 1', 'Agree with Doctor 2', 'Both correct', 'Both incorrect', 'Need further review') NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (image_id) REFERENCES images(id),
  FOREIGN KEY (reviewer_id) REFERENCES users(id),
  FOREIGN KEY (doctor1_id) REFERENCES users(id),
  FOREIGN KEY (doctor2_id) REFERENCES users(id),
  UNIQUE KEY unique_review_per_image (image_id)
);

-- Note: The passwords below are hashed with bcrypt. 
-- The plaintext password for all these initial users is 'password'.
-- $2a$10$C8eH9l2r/Fj8XJ2K//8t2e4mU6YmJk5x5TXXrNn3GzK5wT0zR1rJ. 

INSERT INTO users (username, password, role) VALUES 
('admin', '$2a$10$C8eH9l2r/Fj8XJ2K//8t2e4mU6YmJk5x5TXXrNn3GzK5wT0zR1rJ.', 'admin'),
('doctor1', '$2a$10$C8eH9l2r/Fj8XJ2K//8t2e4mU6YmJk5x5TXXrNn3GzK5wT0zR1rJ.', 'doctor'),
('doctor2', '$2a$10$C8eH9l2r/Fj8XJ2K//8t2e4mU6YmJk5x5TXXrNn3GzK5wT0zR1rJ.', 'doctor'),
('reviewer1', '$2a$10$C8eH9l2r/Fj8XJ2K//8t2e4mU6YmJk5x5TXXrNn3GzK5wT0zR1rJ.', 'reviewer')
ON DUPLICATE KEY UPDATE username=username;

-- Insert initial 12 questions (with explicit sort order)
INSERT INTO questions (id, question_text, options, sort_order) VALUES
(1, 'What is the final classification of this lesion?', '["Benign", "Malignant", "Normal"]', 1),
(2, 'Where is the lesion located?', '[]', 2),
(3, 'What is the shape of the lesion?', '[]', 3),
(4, 'What are the lesion margins like?', '[]', 4),
(5, 'What is the lesion orientation?', '[]', 5),
(6, 'What is the echo pattern of the lesion?', '[]', 6),
(7, 'What are the posterior acoustic features?', '[]', 7),
(8, 'Is there architectural distortion or surrounding tissue disruption?', '[]', 8),
(9, 'What is the lesion boundary clarity?', '[]', 9),
(10, 'Are there associated secondary signs?', '[]', 10),
(11, 'Overall visual reasoning', '[]', 11),
(12, 'BI-RADS assessment category', '[]', 12)
ON DUPLICATE KEY UPDATE
  question_text = VALUES(question_text),
  options = VALUES(options),
  sort_order = VALUES(sort_order);