drop table if exists tablet;
drop table if exists tablet_word;
drop table if exists word_occurrence;

create table tablet (
    id integer primary key,
    author text,
    catalogue_title text not null,
    source_name text,
    source_url text,
    content text not null
);

-- the actual words and formatting in each tablet in order
-- inserted in order so id can be used to sort for tablet
create table tablet_content (
    id integer primary key,
    tablet_id integer not null,
    content_type_id integer not null,
    content_text text not null, -- empty string if type == newline
    foreign key (tablet_id) references tablet (id),
    foreign key (content_type_id) references tablet_content_type (id)
);

create table tablet_publication (
    id integer primary key,
    tablet_id integer not null,
    publication_id integer not null,
    primary_publication boolean not null default false
);

create table publication (
    id integer primary key,
    citation text not null
);

create table root (
    id integer primary key,
    root_text text not null
);

-- selected_word defines where the word is in the document
-- this is in whole words with the first word in the doc being 0
-- Note: "word" is the actual text selected
create table word_occurrence (
    id integer primary key,
    tablet_id integer not null,
    root_id integer not null,
    word text not null,
    selected_word integer not null,
    grammatical_person_id integer not null,
    grammatical_number_id integer not null,
    grammatical_gender_id integer not null,
    foreign key (tablet_id) references tablet (id),
    foreign key (root_id) references 'root' (id),
    foreign key (grammatical_person_id) references grammatical_person (id),
    foreign key (grammatical_number_id) references grammatical_number (id),
    foreign key (grammatical_gender_id) references grammatical_gender (id)
);

create table grammatical_person (
    id integer primary key,
    display_name text not null
);

create table grammatical_number (
    id integer primary key,
    display_name text not null
);

create table grammatical_gender (
    id integer primary key,
    display_name text not null
);

create table tablet_content_type (
    id integer primary key,
    content_type text not null
)