--
-- G-alaxy Create
--
-- by Alan G. Labouseur
--    alan@labosueur.com
-- 

-- 
-- Drop everything in reverse dependency order.
--
drop table Edges;
drop table Vertexes;
drop table Graphs;

--
-- Create the tables in dependency order.
--
-- Note 1: We're using int and bigint rather than serial and bigserial for now
--         because that makes loading referentially linked test data far easier.

create table Graphs (
  gid  integer not null,                              -- Should be serial eventually.
  name text,
 primary key (gid)
);

create table Vertexes (
   gid       integer not null references Graphs(gid),      -- Should be serial eventually.
   vid       bigint  not null,                             -- Should be bigserial eventually.
   name      text,
   data      text,
   sssp_est  int,       -- For Single-source shortest path calculations. 
   sssp_pred int,       -- See chapter 24 in CLRS (specifically page 648 in the 3rd edition.) 
 primary key (gid, vid)
);

create table Edges (
   gid        integer not null references Graphs(gid),  -- Should be serial eventually.
   fromVertex bigint  not null,                         -- Should be bigserial eventually.
   toVertex   bigint  not null,                         -- Should be bigserial eventually.
   weight     real,
   data       text,   
 primary key (gid, fromVertex, toVertex),
 foreign key (gid, fromVertex) references Vertexes(gid, vid),
 foreign key (gid, toVertex)   references Vertexes(gid, vid)
);


--
-- Insert the Great Underground Empire (ground level) graph data.
--

-- Add a new graph; graph 0: The Great Underground Empire.
insert into Graphs(gid, name) values(0, 'The Great Underground Empire, ground level');

--Add the Vertexes to this new graph.
insert into Vertexes(gid, vid, name) values(0,  0, 'West of House');
insert into Vertexes(gid, vid, name) values(0,  1, 'North of House');
insert into Vertexes(gid, vid, name) values(0,  2, 'Behind House');
insert into Vertexes(gid, vid, name) values(0,  3, 'South of House');
insert into Vertexes(gid, vid, name) values(0,  4, 'Kitchen');
insert into Vertexes(gid, vid, name) values(0,  5, 'Attic');
insert into Vertexes(gid, vid, name) values(0,  6, 'Living Room');
insert into Vertexes(gid, vid, name) values(0,  7, 'Clearing #1');
insert into Vertexes(gid, vid, name) values(0,  8, 'Canyon View');
insert into Vertexes(gid, vid, name) values(0,  9, 'Rocky Ledge');
insert into Vertexes(gid, vid, name) values(0, 10, 'End of Rainbow');
insert into Vertexes(gid, vid, name) values(0, 11, 'Canyon Bottom');
insert into Vertexes(gid, vid, name) values(0, 12, 'Forest #3');
insert into Vertexes(gid, vid, name) values(0, 13, 'Forest #1');
insert into Vertexes(gid, vid, name) values(0, 14, 'Forest Path');
insert into Vertexes(gid, vid, name) values(0, 15, 'Clearing #2');
insert into Vertexes(gid, vid, name) values(0, 16, 'Up a Tree');
insert into Vertexes(gid, vid, name) values(0, 17, 'Forest #2');
insert into Vertexes(gid, vid, name) values(0, 18, 'Forest #4');
insert into Vertexes(gid, vid, name) values(0, 19, 'Stone Barrow');
insert into Vertexes(gid, vid, name) values(0, 20, 'Inside the Barrow');

-- Add the edges to the new Vertexes in the new graph.
insert into Edges(gid, fromVertex, toVertex) values(0,  0,  1);
insert into Edges(gid, fromVertex, toVertex) values(0,  1,  0);
insert into Edges(gid, fromVertex, toVertex) values(0,  1,  2);
insert into Edges(gid, fromVertex, toVertex) values(0,  2,  1);
insert into Edges(gid, fromVertex, toVertex) values(0,  2,  3);
insert into Edges(gid, fromVertex, toVertex) values(0,  3,  2);
insert into Edges(gid, fromVertex, toVertex) values(0,  3,  0);
insert into Edges(gid, fromVertex, toVertex) values(0,  0,  3);
insert into Edges(gid, fromVertex, toVertex) values(0,  2,  4);
insert into Edges(gid, fromVertex, toVertex) values(0,  4,  2);
insert into Edges(gid, fromVertex, toVertex) values(0,  4,  6);
insert into Edges(gid, fromVertex, toVertex) values(0,  6,  4);
insert into Edges(gid, fromVertex, toVertex) values(0,  4,  5);
insert into Edges(gid, fromVertex, toVertex) values(0,  5,  4);
insert into Edges(gid, fromVertex, toVertex) values(0,  2,  7);
insert into Edges(gid, fromVertex, toVertex) values(0,  7,  2);
insert into Edges(gid, fromVertex, toVertex) values(0,  7,  8);
insert into Edges(gid, fromVertex, toVertex) values(0,  8,  7);
insert into Edges(gid, fromVertex, toVertex) values(0,  8,  9);
insert into Edges(gid, fromVertex, toVertex) values(0,  9,  8);
insert into Edges(gid, fromVertex, toVertex) values(0,  9, 11);
insert into Edges(gid, fromVertex, toVertex) values(0, 11,  9);
insert into Edges(gid, fromVertex, toVertex) values(0, 11, 10);
insert into Edges(gid, fromVertex, toVertex) values(0, 10, 11);
insert into Edges(gid, fromVertex, toVertex) values(0,  7, 12);
insert into Edges(gid, fromVertex, toVertex) values(0, 12,  7);
insert into Edges(gid, fromVertex, toVertex) values(0, 12, 13);
insert into Edges(gid, fromVertex, toVertex) values(0, 13, 12);
insert into Edges(gid, fromVertex, toVertex) values(0,  3, 12);
insert into Edges(gid, fromVertex, toVertex) values(0, 12,  3);
insert into Edges(gid, fromVertex, toVertex) values(0,  8, 12); -- One-way.
insert into Edges(gid, fromVertex, toVertex) values(0,  7, 17);
insert into Edges(gid, fromVertex, toVertex) values(0, 17,  7);
insert into Edges(gid, fromVertex, toVertex) values(0,  0, 13); -- One-way.
insert into Edges(gid, fromVertex, toVertex) values(0, 13, 14);
insert into Edges(gid, fromVertex, toVertex) values(0, 14, 13);
insert into Edges(gid, fromVertex, toVertex) values(0, 13, 15);
insert into Edges(gid, fromVertex, toVertex) values(0, 15, 13);
insert into Edges(gid, fromVertex, toVertex) values(0, 14, 15);
insert into Edges(gid, fromVertex, toVertex) values(0, 15, 14);
insert into Edges(gid, fromVertex, toVertex) values(0, 14, 17);
insert into Edges(gid, fromVertex, toVertex) values(0, 17, 14);
insert into Edges(gid, fromVertex, toVertex) values(0, 14,  1);
insert into Edges(gid, fromVertex, toVertex) values(0,  1, 14);
insert into Edges(gid, fromVertex, toVertex) values(0, 14, 16);
insert into Edges(gid, fromVertex, toVertex) values(0, 16, 14);
insert into Edges(gid, fromVertex, toVertex) values(0, 15, 17); -- One-way.
insert into Edges(gid, fromVertex, toVertex) values(0, 17, 18);
insert into Edges(gid, fromVertex, toVertex) values(0, 18, 17);
insert into Edges(gid, fromVertex, toVertex) values(0,  0, 19);
insert into Edges(gid, fromVertex, toVertex) values(0, 19,  0);
insert into Edges(gid, fromVertex, toVertex) values(0, 19, 20);
insert into Edges(gid, fromVertex, toVertex) values(0, 20, 19);


--
-- Some Queries to help validate the data against the paper (magic?) map.
--

-- Display all vertexes.
select *
from Vertexes
;

-- Display all edges.
select *
from Edges
;

-- Display map summary for graph 0.
select v1.vid     as "id",
       v1.name    as "From", 
       v2.name    as "To",
       e.toVertex as "id"
from Graphs    g, 
     Vertexes v1, 
     Edges     e,
     Vertexes v2
where g.gid = 0              -- Only graph 0.
  and v1.gid = g.gid         -- Only verticies in this graph.
  and v2.gid = g.gid         -- Only verticies in this graph.
  and e.gid = g.gid          -- Only edges in this graph.
  and e.fromVertex = v1.vid  -- Link the from-edge to the v1.node.
  and e.toVertex = v2.vid    -- Link the to-edge to the v2.node.
order by v1.vid, v1.name, e.toVertex
;