USE dbRadiology;

CREATE TABLE IF NOT EXISTS disease_image_descriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    disease_id INT NOT NULL,
    image_url TEXT,
    title VARCHAR(255),
    description TEXT,
    language VARCHAR(5),
    FOREIGN KEY (disease_id)
        REFERENCES diseases_new(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS disease_references (
    id INT AUTO_INCREMENT PRIMARY KEY,
    disease_id INT NOT NULL,
    pmid VARCHAR(50),
    title TEXT,
    authors TEXT,
    journal TEXT,
    FOREIGN KEY (disease_id)
        REFERENCES diseases_new(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS disease_age_ranges (
    id INT AUTO_INCREMENT PRIMARY KEY,
    disease_id INT NOT NULL,
    min_age INT,
    max_age INT,
    FOREIGN KEY (disease_id)
        REFERENCES diseases_new(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
