

CREATE TABLE IF NOT EXISTS devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    device_type VARCHAR(100) NOT NULL,
    brand VARCHAR(100) DEFAULT NULL,
    model VARCHAR(100) DEFAULT NULL,
    ip_address VARCHAR(50) NOT NULL,
    snmp_community VARCHAR(100) DEFAULT NULL,
    location_name VARCHAR(150) DEFAULT NULL,
    latitude DECIMAL(10,7) DEFAULT NULL,
    longitude DECIMAL(10,7) DEFAULT NULL,
    port_count INT DEFAULT 0,
    notes TEXT DEFAULT NULL,
    status ENUM('online','offline','unknown') DEFAULT 'unknown',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS fiber_nodes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    node_type VARCHAR(100) NOT NULL,
    device_id INT DEFAULT NULL,
    latitude DECIMAL(10,7) DEFAULT NULL,
    longitude DECIMAL(10,7) DEFAULT NULL,
    notes TEXT DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS fiber_links (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    core_device_id INT DEFAULT NULL,
    from_node_id INT DEFAULT NULL,
    to_node_id INT DEFAULT NULL,
    status ENUM('up','down','unknown') DEFAULT 'unknown',
    path_json LONGTEXT DEFAULT NULL,
    notes TEXT DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS device_ports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    port_name VARCHAR(100) NOT NULL,
    port_type VARCHAR(100) DEFAULT NULL,
    status ENUM('up','down','unknown') DEFAULT 'unknown',
    connected_to_type VARCHAR(100) DEFAULT NULL,
    connected_to_id INT DEFAULT NULL,
    notes TEXT DEFAULT NULL
);
