PRAGMA foreign_keys = on;

create table post_types (
    name varchar(32) not null primary key
);

insert into post_types (name) values ('article'), ('note');

create table posts (
    id integer not null primary key autoincrement,
    created_at bigint not null default (unixepoch()),
    updated_at bigint,
    post_type varchar(32) not null default ('note'),
    foreign key (post_type) references post_types(name)
);

create table tags (
    name varchar(128) not null primary key
);

create table posts_tags (
    post_id integer not null,
    tag varchar(128) not null,
    foreign key (post_id) references posts(id),
    foreign key (tag) references tags(name),
    primary key (post_id, tag)
);

create table articles (
    post_id integer not null,
    source varchar(128) not null,
    hash varchar(64) not null,
    foreign key (post_id) references posts(id)
);

create table text_formats (
    name varchar(16) not null primary key
);

insert into text_formats (name) values ('plaintext'), ('markdown'), ('html');

create table notes (
    post_id integer not null,
    text_format varchar(16) not null default ('plaintext'),
    text_content text,
    foreign key (post_id) references posts(id),
    foreign key (text_format) references text_formats(name)
);

create table media_types (
    name varchar(32) not null primary key
);

insert into media_types (name) values ('image'), ('video'), ('audio');

create table media (
    id integer not null primary key autoincrement,
    media_type varchar(32) not null,
    source varchar(128) not null,
    foreign key (media_type) references media_types(name)
);

create table notes_media (
    note_id integer not null, 
    media_id integer not null,
    foreign key (note_id) references notes(note_id),
    foreign key (media_id) references media(id),
    primary key (note_id, media_id)
);