create table users (
    id serial primary key,
    login varchar(30) unique not null,
    password_hash varchar(100) not null
);

create table `character` (
    id serial primary key,
    creator_id bigint unsigned not null,
    name longtext not null,
    bio longtext,
    age int,
    height int,
    temperament longtext,
    likes longtext,
    dislikes longtext,
    fears longtext,
    profileimage mediumtext,
    foreign key (creator_id) references users(id)
);

create table character_images (
    character_id bigint unsigned not null,
    imagepath text not null,
    foreign key (character_id) references `character`(id)
);

create table story (
    id serial primary key,
    creator_id bigint unsigned not null,
    name text not null,
    year year not null,
    genre text,
    type text,
    plot text,
    foreign key (creator_id) references users(id)
);

create table character_relationships (
    one_char_id bigint unsigned not null,
    another_char_id bigint unsigned not null,
    relationship int not null,
    tag mediumtext,
    foreign key (one_char_id) references `character`(id),
    foreign key (another_char_id) references `character`(id),
    primary key (one_char_id, another_char_id)
);

create table character_story (
    character_id bigint unsigned not null,
    story_id bigint unsigned not null,
    foreign key (character_id) references `character`(id),
    foreign key (story_id) references story(id),
    primary key (character_id, story_id)
);