router-dashboard/sql/01-init.sql

-- Create and use database
CREATE DATABASE IF NOT EXISTS ve_router_db;
USE ve_router_db;

-- Router table
CREATE TABLE IF NOT EXISTS routers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    router_id VARCHAR(10) UNIQUE NOT NULL,  -- Unique router identifier
    facility VARCHAR(50) NOT NULL,
    router_alias VARCHAR(50) NOT NULL,
    last_seen TIMESTAMP NOT NULL,
    vpn_status_code VARCHAR(50) NOT NULL,
    disk_status_code VARCHAR(50) NOT NULL,
    license_status ENUM('active', 'inactive', 'suspended') NOT NULL DEFAULT 'inactive',
    free_disk BIGINT NOT NULL CHECK (free_disk >= 0),
    total_disk BIGINT NOT NULL CHECK (total_disk > 0),
    disk_usage DECIMAL(5,2) NOT NULL CHECK (disk_usage BETWEEN 0 AND 100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Users and Authentication tables
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin', 'operator', 'viewer') NOT NULL DEFAULT 'viewer',
    status ENUM('active', 'locked', 'disabled') NOT NULL DEFAULT 'active',
    failed_login_attempts INT NOT NULL DEFAULT 0,
    last_login TIMESTAMP NULL,
    password_changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT valid_email CHECK (email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT valid_username CHECK (username REGEXP '^[A-Za-z0-9_-]{3,50}$')
);

-- User-Router access permissions
CREATE TABLE IF NOT EXISTS user_router_access (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    router_id INT NOT NULL,
    can_view BOOLEAN NOT NULL DEFAULT TRUE,
    can_manage BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_user_router_access (user_id, router_id)
);

-- Session management
CREATE TABLE IF NOT EXISTS user_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    session_token VARCHAR(255) UNIQUE NOT NULL,
    refresh_token VARCHAR(255) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT,
    expires_at TIMESTAMP NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL 24 HOUR),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT unique_session_token UNIQUE(session_token),
    CONSTRAINT unique_refresh_token UNIQUE(refresh_token)
);

-- System status table
CREATE TABLE IF NOT EXISTS system_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    router_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Container status table
CREATE TABLE IF NOT EXISTS container_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    system_status_id INT NOT NULL,
    container_number INT NOT NULL CHECK (container_number BETWEEN 1 AND 10),
    status_code VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- VM details table
CREATE TABLE IF NOT EXISTS vm_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    router_id INT NOT NULL,
    vm_number INT NOT NULL CHECK (vm_number > 0),
    status_code VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT unique_vm_per_router UNIQUE(router_id, vm_number)
);

-- DICOM study overview table with router_id as a string reference
CREATE TABLE IF NOT EXISTS dicom_study_overview (
    id INT AUTO_INCREMENT PRIMARY KEY,
    router_id VARCHAR(10) NOT NULL,  -- Matching VARCHAR(10) with the routers table
    study_instance_uid VARCHAR(100) UNIQUE NOT NULL,
    patient_id VARCHAR(50) NOT NULL,
    patient_name VARCHAR(100) NOT NULL,
    accession_number VARCHAR(50) NOT NULL,
    study_date DATE NOT NULL,
    modality VARCHAR(20) NOT NULL,
    study_description VARCHAR(255),
    series_instance_uid VARCHAR(100) NOT NULL,
    procedure_code VARCHAR(50),
    referring_physician_name VARCHAR(100),
    study_status_code VARCHAR(50) NOT NULL DEFAULT 'NEW',  -- Default value, ensure 'NEW' exists in status_type
    association_id VARCHAR(50) NOT NULL DEFAULT 'NEW',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create tables if they don't exist
CREATE TABLE IF NOT EXISTS status_type (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id VARCHAR(50),
    name VARCHAR(100),
    code VARCHAR(100),
    description VARCHAR(150),
    severity INT
);

-- History and Audit Tables (No changes required here)
CREATE TABLE IF NOT EXISTS router_status_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    router_id INT NOT NULL,
    vpn_status_code VARCHAR(50) NOT NULL,
    disk_status_code VARCHAR(50) NOT NULL,
    license_status ENUM('active', 'inactive', 'suspended') NOT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Status category table
CREATE TABLE IF NOT EXISTS status_category (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


-- Additional history and settings tables as needed...