CREATE TABLE network_segments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(100) NOT NULL,
start_odp_id INTEGER NOT NULL,
end_odp_id INTEGER,
segment_type VARCHAR(50) DEFAULT 'Backbone' CHECK (segment_type IN ('Backbone', 'Distribution', 'Access')),
cable_length DECIMAL(10,2),
status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'maintenance', 'damaged', 'inactive')),
installation_date DATE,
notes TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (start_odp_id) REFERENCES odps(id) ON DELETE CASCADE,
FOREIGN KEY (end_odp_id) REFERENCES odps(id) ON DELETE CASCADE
)