USE dbRadiology;

############################################################
# 1 Convert diseases table to UTF8
############################################################

ALTER TABLE diseases CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

############################################################
# 2 Add indexes for performance
############################################################

ALTER TABLE diseases ADD INDEX idx_category (disease_category);
ALTER TABLE diseases ADD INDEX idx_group (disease_group);

############################################################
# 3 Categories
############################################################

CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    value VARCHAR(100) UNIQUE,
    dataSourceFrequency INT DEFAULT 0,
    image_url TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

############################################################
# 4 Category translations
############################################################

CREATE TABLE IF NOT EXISTS category_translations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    language VARCHAR(5),
    name VARCHAR(255),
    description TEXT,
    FOREIGN KEY (category_id) REFERENCES categories(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

############################################################
# 5 Groups
############################################################

CREATE TABLE IF NOT EXISTS groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    value VARCHAR(100),
    dataSourceFrequency INT DEFAULT 0,
    image_url TEXT,
    FOREIGN KEY (category_id) REFERENCES categories(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

############################################################
# 6 Group translations
############################################################

CREATE TABLE IF NOT EXISTS group_translations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_id INT,
    language VARCHAR(5),
    name VARCHAR(255),
    description TEXT,
    FOREIGN KEY (group_id) REFERENCES groups(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

############################################################
# 7 Diseases (new structure)
############################################################

CREATE TABLE IF NOT EXISTS diseases_new (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_id INT,
    filename VARCHAR(255),
    is_free BOOLEAN,
    is_image_complete BOOLEAN,
    frequency INT,
    disease_onset VARCHAR(50),
    FOREIGN KEY (group_id) REFERENCES groups(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

############################################################
# 8 Disease translations
############################################################

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

############################################################
# 9 Disease images
############################################################

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

############################################################
# 10 Signs
############################################################

CREATE TABLE IF NOT EXISTS signs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    value VARCHAR(100),
    type VARCHAR(50),
    category VARCHAR(100),
    image_name VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

############################################################
# 11 Sign translations
############################################################

CREATE TABLE IF NOT EXISTS sign_translations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sign_id INT,
    language VARCHAR(5),
    name VARCHAR(255),
    description TEXT,
    FOREIGN KEY (sign_id) REFERENCES signs(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

############################################################
# 12 Disease signs
############################################################

CREATE TABLE IF NOT EXISTS disease_signs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    disease_id INT,
    sign_id INT,
    value FLOAT,
    prevalence FLOAT,
    FOREIGN KEY (disease_id) REFERENCES diseases_new(id)
        ON DELETE CASCADE,
    FOREIGN KEY (sign_id) REFERENCES signs(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

############################################################
# 13 Knowledge items (generalDataGroups)
############################################################

CREATE TABLE IF NOT EXISTS knowledge_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_id INT,
    category VARCHAR(100),
    value VARCHAR(100),
    FOREIGN KEY (group_id) REFERENCES groups(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

############################################################
# 14 Knowledge translations
############################################################

CREATE TABLE IF NOT EXISTS knowledge_translations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    knowledge_id INT,
    language VARCHAR(5),
    name VARCHAR(255),
    description TEXT,
    FOREIGN KEY (knowledge_id) REFERENCES knowledge_items(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
