-- Pathology Education Database Schema
CREATE DATABASE IF NOT EXISTS pathology_education;
USE pathology_education;

-- Roles table
CREATE TABLE roles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Hospitals table
CREATE TABLE hospitals (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) UNIQUE NOT NULL,
    abbreviation VARCHAR(10),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Users table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    tussenvoegsels VARCHAR(50),
    last_name VARCHAR(100) NOT NULL,
    role_id INT NOT NULL,
    is_educator BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

-- User hospital connections (many-to-many relationship)
CREATE TABLE user_hospitals (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    hospital_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (hospital_id) REFERENCES hospitals(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_hospital (user_id, hospital_id)
);

-- Insert default roles
INSERT INTO roles (name) VALUES 
('admin'),
('pathologist'),
('aios');

-- Insert hospitals
INSERT INTO hospitals (name, abbreviation) VALUES 
('UMCG', 'UMCG'),
('Martini Ziekenhuis', 'MZH'),
('Laboratorium voor de Volksgezondheid in Friesland', 'LVF'),
('ISALA', 'ISALA');

-- Insert admin user (password: admin123)
INSERT INTO users (username, password, first_name, last_name, role_id) VALUES 
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Admin', 'User', 1);