-- drop database if exists  settlementsint
-- create database settlements;

create table users (
    id integer primary key autoincrement,
    name varchar,
    surnames varchar,
    email varchar
);

create table user_accounts (
    id integer primary key autoincrement,
    user_id bigint references users (id) unique,
    username varchar unique not null,
    password varchar default null,
    attempts smallint default 0,
    locked bool default false,
    active bool default true
);

create table permissions (
    id integer primary key autoincrement,
    code varchar unique not null,
    description varchar default null
);

create table groups (
    id integer primary key autoincrement,
    code varchar unique not null,
    description varchar default null
);

create table user_account_groups (
    account_id bigint references user_accounts (id),
    group_id bigint references groups (id)
);

create table user_account_permissions (
    account_id bigint references user_accounts (id),
    permission_id bigint references permissions (id)
);

create table group_permissions (
    group_id bigint references groups (id),
    permission_id bigint references permissions (id)
);

create table user_account_sessions (
    id integer primary key autoincrement,
    account_id bigint not null references user_accounts (id),
    token varchar not null unique,
    created_at timestamp default current_timestamp,
    active bool default true
);

create table user_account_recover_requests (
    id integer primary key autoincrement,
    account_id bigint not null references user_accounts (id),
    token varchar not null unique,
    created_at timestamp default current_timestamp,
    active bool default true
);

create table settlements (
    id integer primary key autoincrement,
    name varchar not null unique,
    address varchar not null unique,
    created_at timestamp not null default current_timestamp,
    last_done timestamp default null,
    last_done_by bigint references users (id),
    active bool default true
);

create table animal_coats (
    id integer primary key autoincrement,
    code varchar unique not null,
    description varchar default null
);

create table animals (
    id integer primary key autoincrement,
    name varchar not null,
    microchip varchar default null,
    coat bigint not null references animal_coats (id),
    sex int not null check(sex in ('female', 'male', 'unknown')) default 'unknown',
    settlement_id bigint references settlements (id),
    has_ear_mark bool default false,
    controlled bool default false,
    castrated bool default false,
    adopted bool default false,
    friendly bool default null,
    friendly_children bool default null,
    friendly_dogs bool default null,
    first_view timestamp default null,
    last_view timestamp default null,
    created_at timestamp not null default current_timestamp,
    active bool default true
);

create table animal_comments (
    id integer primary key autoincrement,
    animal_id bigint not null references animals (id),
    _type int not null check(_type in ('info', 'alert')) default 'info',
    message varchar not null,
    created_at timestamp default current_timestamp,
    public bool default false,
    active bool default true
);

create table animal_alerts (
    id integer primary key autoincrement,
    animal_id bigint not null references animals (id),
    _type int not null check(_type in ('info', 'low', 'medium', 'high', 'ultra')) default 'high',
    comment varchar not null,
    created_at timestamp default current_timestamp,
    active bool default true
);

create table animal_images (
    id integer primary key autoincrement,
    animal_id bigint not null references animals (id),
    image varchar not null unique,
    created_at timestamp default current_timestamp,
    is_primary bool default false,
    public bool default false,
    active bool default true
);