settlements-admin/database/update_from_sqlite__1_schema.sql

-- drop database if exists  settlements;
-- create database settlements;
drop type if exists alert_types cascade;

create type alert_types as enum ('info', 'low', 'medium', 'high', 'ultra');

drop type if exists message_types cascade;

create type message_types as enum ('info', 'alert');

drop type if exists animal_sexs cascade;

create type animal_sexs as enum ('female', 'male', 'unknown');

drop table if exists users cascade;

drop table if exists user_accounts cascade;

drop table if exists permissions cascade;

drop table if exists groups cascade;

drop table if exists user_groups cascade;

drop table if exists user_permissions cascade;

drop table if exists group_permissions cascade;

drop table if exists user_groups cascade;

drop table if exists user_sessions cascade;

drop table if exists user_recover_requests cascade;

drop table if exists settlements cascade;

drop table if exists animals cascade;

drop table if exists animal_comments cascade;

drop table if exists animal_alerts cascade;

drop table if exists animal_images cascade;

drop table if exists animal_coats cascade;

create table users (
    id bigint generated by default as identity primary key,
    name varchar,
    surnames varchar,
    email varchar
);

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

create table permissions (
    id bigint generated by default as identity primary key,
    code varchar unique not null,
    description varchar default null
);

create table groups (
    id bigint generated by default as identity primary key,
    code varchar unique not null,
    description varchar default null
);

create table user_groups (
    user_id bigint references users (id),
    group_id bigint references groups (id)
);

create table user_permissions (
    user_id bigint references users (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_sessions (
    id bigint generated by default as identity primary key,
    user_id bigint not null references users (id),
    token varchar not null unique,
    created_at timestamp default current_timestamp,
    active bool default true
);

create table user_recover_requests (
    id bigint generated by default as identity primary key,
    user_id bigint not null references users (id),
    token varchar not null unique,
    created_at timestamp default current_timestamp,
    active bool default true
);

create table settlements (
    id bigint generated by default as identity primary key,
    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 bigint generated by default as identity primary key,
    code varchar unique not null,
    description varchar default null
);

create table animals (
    id bigint generated by default as identity primary key,
    name varchar not null,
    microchip varchar default null,
    coat bigint not null references animal_coats (id),
    sex animal_sexs 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 bigint generated by default as identity primary key,
    animal_id bigint not null references animals (id),
    type message_types 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 bigint generated by default as identity primary key,
    animal_id bigint not null references animals (id),
    type alert_types default 'high',
    comment varchar not null,
    created_at timestamp default current_timestamp,
    active bool default true
);

create table animal_images (
    id bigint generated by default as identity primary key,
    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
);