DELIMITER //

CREATE PROCEDURE seed_complete_router_system()
BEGIN
    -- Disable foreign key checks and start fresh
    SET FOREIGN_KEY_CHECKS=0;

    -- Conditionally clear existing data, only if the table exists
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'auth_log') THEN
        TRUNCATE TABLE auth_log;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'user_sessions') THEN
        TRUNCATE TABLE user_sessions;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'user_router_access') THEN
        TRUNCATE TABLE user_router_access;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'users') THEN
        TRUNCATE TABLE users;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'container_status_history') THEN
        TRUNCATE TABLE container_status_history;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'router_status_history') THEN
        TRUNCATE TABLE router_status_history;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'container_status') THEN
        TRUNCATE TABLE container_status;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'vm_details') THEN
        TRUNCATE TABLE vm_details;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'dicom_study_overview') THEN
        TRUNCATE TABLE dicom_study_overview;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'system_status') THEN
        TRUNCATE TABLE system_status;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'router_settings_history') THEN
        TRUNCATE TABLE router_settings_history;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'router_settings') THEN
        TRUNCATE TABLE router_settings;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'routers') THEN
        TRUNCATE TABLE routers;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'status_type') THEN
        TRUNCATE TABLE status_type;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'status_category') THEN
        TRUNCATE TABLE status_category;
    END IF;

    -- 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 for later use
    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 studies
    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;

    -- Insert router settings for each router (calls to upsert_router_settings are disabled)
    -- Main Hospital Router
    -- CALL upsert_router_settings(
    --     @router1_id,
    --     'client',
    --     '{
    --         "dicom": {
    --             "local": {
    --                 "aet": "MAIN_RAD",
    --                 "port": 104,
    --                 "file_directory": "/dicom_images",
    --                 "wait_time": 2,
    --                 "receiver_wait_time": 5000
    --             },
    --             "association": {
    --                 "acse_timeout": 5,
    --                 "dimse_timeout": 1000,
    --                 "network_timeout": 1000,
    --                 "retry": {
    --                     "attempts": 3,
    --                     "interval": 10
    --                 }
    --             }
    --         },
    --         "rabbitmq": {
    --             "local": {
    --                 "hostname": "router-rabbitmq",
    --                 "port": 5672,
    --                 "credentials": {
    --                     "username": "vitalengine",
    --                     "password": "vitalengine"
    --                 },
    --                 "settings": {
    --                     "durable": true,
    --                     "auto_delete": false,
    --                     "exchange_type": "direct",
    --                     "heartbeat": 50
    --                 }
    --             }
    --         },
    --         "scp_connections": {
    --             "pacs_nodes": [
    --                 {
    --                     "host": "pacsmain.example.com",
    --                     "port": 104
    --                 },
    --                 {
    --                     "host": "pacsbackup.example.com",
    --                     "port": 104
    --                 }
    --             ]
    --         }
    --     }',
    --     'system',
    --     'Initial client configuration for Main Hospital'
    -- );

    -- Emergency Center Router
    -- CALL upsert_router_settings(
    --     @router2_id,
    --     'client',
    --     '{
    --         "dicom": {
    --             "local": {
    --                 "aet": "ER_RAD",
    --                 "port": 104,
    --                 "file_directory": "/dicom_images",
    --                 "wait_time": 2,
    --                 "receiver_wait_time": 5000
    --             },
    --             "association": {
    --                 "acse_timeout": 5,
    --                 "dimse_timeout": 1000,
    --                 "network_timeout": 1000,
    --                 "retry": {
    --                     "attempts": 3,
    --                     "interval": 10
    --                 }
    --             }
    --         },
    --         "rabbitmq": {
    --             "local": {
    --                 "hostname": "router-rabbitmq",
    --                 "port": 5672,
    --                 "credentials": {
    --                     "username": "vitalengine",
    --                     "password": "vitalengine"
    --                 },
    --                 "settings": {
    --                     "durable": true,
    --                     "auto_delete": false,
    --                     "exchange_type": "direct",
    --                     "heartbeat": 50
    --                 }
    --             }
    --         },
    --         "scp_connections": {
    --             "pacs_nodes": [
    --                 {
    --                     "host": "pacsemergency.example.com",
    --                     "port": 104
    --                 }
    --             ]
    --         }
    --     }',
    --     'system',
    --     'Initial client configuration for Emergency Center'
    -- );

    -- Insert settings for other routers as needed...

END //

DELIMITER ;