-- Topics

drop table Topics;
create table Topics (
    topic_id    int4        not null,   -- unique ID
    parent_id   int4        not null,   -- parent topic, 0 = root
    sort_order  int4        not null,   -- sort by this!
    columns     int4        not null,   -- column hint
    total_links int4        not null,   -- total links here and in children
    lastupdate  datetime    not null,   -- when last changed
    title       varchar(40) not null,   -- short title
    description text        not null    -- long description
);


create unique index idx_Topics_id on Topics (topic_id);
create index idx_Topics_parent on Topics (parent_id);
create index idx_Topics_sort on Topics (sort_order);

drop sequence seq_topic_id;
create sequence seq_topic_id start 1;

-- Links

drop table Links;
create table Links (
    link_id     int4        not null,   -- unique ID
    flags       int4        not null,   -- 32 bits of pure joy
    url         text        not null,   -- where it goes
    title       text        not null,   -- short title - link text
    description text        not null    -- long description
);

create unique index idx_Links_id on Links (link_id);

drop sequence seq_link_id;
create sequence seq_link_id start 1;

-- Topic-Link relation

drop table Topic_Link;
create table Topic_Link (
    tl_id       int4        not null,   -- serial number
    topic_id    int4        not null,   -- topic
    link_id     int4        not null,   -- link
    sort_order  int4        not null    -- sort by this!
);

create unique index idx_TL_id on Topic_Link (tl_id);
create index idx_TL_topic on Topic_Link (topic_id);
create index idx_TL_link on Topic_Link (link_id);
create index idx_TL_sort on Topic_Link (sort_order);

drop sequence seq_tl_id;
create sequence seq_tl_id start 1;

-- Topic-Topic relation

drop table Topic_Topic;
create table Topic_Topic (
    ta_id       int4        not null,   -- one topic
    tb_id       int4        not null    -- the other
);

create index idx_TT_ta on Topic_Topic (ta_id);
create index idx_TT_tb on Topic_Topic (tb_id);

