-- TANGLE V2

-- Step 1: Write a tool to convert .tangle files into sqlite,
-- using the schema in this file.
-- Make it work incrementally - parsing each top-level construct in turn
-- and adding it to an existing file, extending links as it goes,
-- rather than working in phases, so we can support interactive
-- expanding of a network.

-- However, we can have a post-process check to find problems to warn
-- about, such as duplicate IPs and MACs or ports with too many cables
-- in and all that.

-- Step 2: Write a tool to convert a database back into a .tangle file

-- Step 3: Write tools to generate HTML + GraphViz from databases,
-- as per the existing output tools.

CREATE TABLE cables (
       cable_id TEXT NOT NULL PRIMARY KEY,
       type TEXT NOT NULL,
       link_id INTEGER NOT NULL,
       notes TEXT
);

CREATE TABLE cable_device (
       device_id TEXT NOT NULL,
       port TEXT,
       cable_id TEXT NOT NULL
);

CREATE TABLE cable_panel (
       panel_id TEXT NOT NULL,
       port TEXT NOT NULL,
       cable_id TEXT NOT NULL
);

CREATE TABLE panels (
       panel_id TEXT NOT NULL PRIMARY KEY,
       title TEXT,
       notes TEXT,
       location_id TEXT,
       organisation_id TEXT
);

CREATE TABLE trunk_groups (
       trunk_group_id TEXT NOT NULL PRIMARY KEY,
       type TEXT NOT NULL,
       title TEXT,
       notes TEXT
);

CREATE TABLE trunks (
       trunk_id TEXT NOT NULL PRIMARY KEY,
       trunk_group_id TEXT NOT NULL,
       panel_id_1 TEXT NOT NULL,
       port_1 TEXT NOT NULL,
       panel_id_2 TEXT NOT NULL,
       port_2 TEXT NOT NULL,
       link_id INTEGER NOT NULL
);

CREATE TABLE links (
       link_id INTEGER NOT NULL PRIMARY KEY,
       title TEXT
);

CREATE TABLE link_circuit (
       link_id TEXT NOT NULL,
       circuit_id TEXT NOT NULL,
       label TEXT -- VLAN tag, pair colour/number, etc
);

CREATE TABLE devices (
       device_id TEXT NOT NULL PRIMARY KEY,
       type TEXT,
       location_id TEXT,
       notes TEXT
);

CREATE TABLE vm_host (
       device_id_parent TEXT NOT NULL,
       device_id_child TEXT NOT NULL
);

CREATE TABLE mac_ports (
       mac_id TEXT NOT NULL,
       shared CHAR(1) NOT NULL, -- Y/N
       device_id TEXT NOT NULL,
       port TEXT NOT NULL,
       circuit_id TEXT NOT NULL,
);

CREATE TABLE ip4_ports (
       ip4 TEXT NOT NULL,
       shared CHAR(1) NOT NULL, -- Y/N
       stub CHAR(1) NOT NULL, -- Y/N
       device_id TEXT NOT NULL,
       port TEXT NOT NULL,
       circuit_id TEXT NOT NULL
);

CREATE TABLE unused_ports (
       unused_group_id TEXT NOT NULL,
       device_id TEXT NOT NULL,
       port TEXT NOT NULL
);

CREATE TABLE locations (
       location_id TEXT NOT NULL PRIMARY KEY,
       location_id_containing TEXT,
       organisation_id TEXT
);

CREATE TABLE addresses (
       location_id TEXT NOT NULL,
       type TEXT NOT NULL,
       address TEXT NOT NULL
);

CREATE TABLE circuits (
       circuit_id TEXT NOT NULL PRIMARY KEY,
       title TEXT,
       type TEXT NOT NULL,
);

CREATE TABLE ip4_subnets (
       ip4_subnet_id TEXT NOT NULL PRIMARY KEY,
       prefix_len INTEGER NOT NULL,
       title TEXT,
       multiple CHAR(1), -- Y/N
);

CREATE TABLE ip4_reserved (
       ip4_subnet_id TEXT NOT NULL,
       ip4_first TEXT NOT NULL,
       ip4_last TEXT NOT NULL,
       title TEXT
);

CREATE TABLE organisations (
       organisation_id TEXT NOT NULL PRIMARY KEY,
       title TEXT,
       notes TEXT,
       authorisation_contact TEXT,
       emergency_contact TEXT
);