router-dashboard/sql/02-seed_data.sql

DELIMITER //

CREATE PROCEDURE seed_complete_router_system()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE table_name VARCHAR(64);
    DECLARE table_cursor CURSOR FOR 
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = DATABASE() 
          AND table_name IN (
            'auth_log', 'user_sessions', 'user_router_access', 'users',
            'container_status_history', 'router_status_history', 
            'container_status', 'vm_details', 'dicom_study_overview', 
            'system_status', 'router_settings_history', 'router_settings', 
            'routers', 'status_type', 'status_category'
          );

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- Disable foreign key checks
    SET FOREIGN_KEY_CHECKS=0;

    -- Truncate all tables dynamically
    OPEN table_cursor;
    truncate_loop: LOOP
        FETCH table_cursor INTO table_name;
        IF done THEN
            LEAVE truncate_loop;
        END IF;

        SET @query = CONCAT('TRUNCATE TABLE ', table_name);
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE table_cursor;

    -- Re-enable foreign key checks
    SET FOREIGN_KEY_CHECKS=1;

    -- Insert Status Categories
    INSERT INTO status_category (name, description) 
    VALUES
        ('Network', 'Network related statuses'),
        ('Disk', 'Disk related statuses'),
        ('VPN', 'VPN connection statuses'),
        ('License', 'License statuses'),
        ('Container', 'Container related statuses')
    ON DUPLICATE KEY UPDATE id = id;

    -- Insert Status Types
    INSERT INTO status_type (category_id, name, code, description, severity) 
    VALUES
        (1, 'Online', 'NET_ONLINE', 'System is online', 1),
        (1, 'Offline', 'NET_OFFLINE', 'System is offline', 5),
        (2, 'Normal', 'DISK_NORMAL', 'Disk usage is normal', 1),
        (2, 'Warning', 'DISK_WARNING', 'Disk usage is high', 3),
        (2, 'Critical', 'DISK_CRITICAL', 'Disk usage is critical', 5),
        (3, 'Connected', 'VPN_CONNECTED', 'VPN is connected', 1),
        (3, 'Disconnected', 'VPN_DISCONNECTED', 'VPN is disconnected', 5),
        (5, 'Running', 'CONTAINER_RUNNING', 'Container is running', 1),
        (5, 'Stopped', 'CONTAINER_STOPPED', 'Container is stopped', 5)
    ON DUPLICATE KEY UPDATE id = id;

    -- Insert Routers
    INSERT INTO routers (router_id, facility, router_alias, last_seen, vpn_status_code, disk_status_code, license_status, free_disk, total_disk, disk_usage) 
    VALUES
        ('RTR001', 'Main Hospital', 'MAIN_RAD', NOW(), 'VPN_CONNECTED', 'DISK_NORMAL', 'active', 500000000000, 1000000000000, 50.00),
        ('RTR002', 'Emergency Center', 'ER_RAD', NOW(), 'VPN_CONNECTED', 'DISK_WARNING', 'active', 400000000000, 1000000000000, 60.00),
        ('RTR003', 'Imaging Center', 'IMG_CENTER', NOW(), 'VPN_CONNECTED', 'DISK_NORMAL', 'active', 600000000000, 1000000000000, 40.00)
    ON DUPLICATE KEY UPDATE id = id;

    -- Store Router IDs
    SET @router1_id = (SELECT id FROM routers WHERE router_id = 'RTR001');
    SET @router2_id = (SELECT id FROM routers WHERE router_id = 'RTR002');
    SET @router3_id = (SELECT id FROM routers WHERE router_id = 'RTR003');

    -- Insert System Status
    INSERT INTO system_status (router_id) 
    VALUES 
        (@router1_id),
        (@router2_id),
        (@router3_id)
    ON DUPLICATE KEY UPDATE id = id;

    -- Insert Container Status
    INSERT INTO container_status (system_status_id, container_number, status_code) 
    VALUES
        (1, 1, 'CONTAINER_RUNNING'),
        (1, 2, 'CONTAINER_RUNNING'),
        (2, 1, 'CONTAINER_RUNNING'),
        (2, 2, 'CONTAINER_STOPPED'),
        (3, 1, 'CONTAINER_RUNNING')
    ON DUPLICATE KEY UPDATE id = id;

    -- Insert VM Details
    INSERT INTO vm_details (router_id, vm_number, status_code) 
    VALUES
        (@router1_id, 1, 'NET_ONLINE'),
        (@router2_id, 1, 'NET_ONLINE'),
        (@router3_id, 1, 'NET_ONLINE')
    ON DUPLICATE KEY UPDATE id = id;

    -- Insert DICOM Study Overview
    INSERT INTO dicom_study_overview (
        router_id, study_instance_uid, patient_id, patient_name, 
        accession_number, study_date, modality, study_description, 
        series_instance_uid, procedure_code, referring_physician_name
    ) 
    VALUES
        (@router1_id, '1.2.840.113619.2.55.3.283116435.276.1543707218.134', 'P1', 'John Doe', 'ACC1234', '2024-03-15', 'CT', 'Chest CT', '1.2.840.113619.2.55.3.283116435.276.1543707219.135', 'CT001', 'Dr. Smith'),
        (@router2_id, '1.2.840.113619.2.55.3.283116435.276.1543707218.136', 'P2', 'Jane Doe', 'ACC1235', '2024-03-15', 'MR', 'Brain MRI', '1.2.840.113619.2.55.3.283116435.276.1543707219.137', 'MR001', 'Dr. Johnson')
    ON DUPLICATE KEY UPDATE id = id;

END //

DELIMITER ;

-- Automatically call the procedure after creation
CALL seed_complete_router_system();