
-- SQL struktur til due diligence system

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    password_hash VARCHAR(255),
    role ENUM('admin', 'user') NOT NULL DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE cases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    access_password VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE case_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    case_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (case_id) REFERENCES cases(id)
);

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    case_id INT,
    name VARCHAR(255),
    FOREIGN KEY (case_id) REFERENCES cases(id)
);

CREATE TABLE questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    text TEXT,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE answers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question_id INT,
    user_id INT,
    text TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (question_id) REFERENCES questions(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE answer_versions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    answer_id INT,
    text TEXT,
    edited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (answer_id) REFERENCES answers(id)
);

CREATE TABLE answer_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    answer_id INT,
    filename VARCHAR(255),
    filepath VARCHAR(255),
    FOREIGN KEY (answer_id) REFERENCES answers(id)
);
