-- EXTENSIONS ------------------------------------------------------------------
create extension pg_trgm;

-- TYPES -----------------------------------------------------------------------
create type user_role_enum as enum (
    'admin',
    'guest',
    'professor',
    'student',
    'cleaning',
    'developer'
);

-- TABLES ----------------------------------------------------------------------

create table user_account (
    id uuid default uuidv7(),
    user_role user_role_enum not null,
    full_name text not null,
    password_hash text not null,
    phone text unique not null,
    email text unique not null check (email like '%@%'),
    is_active boolean not null default true,

    created_at timestamp not null default current_timestamp,
    updated_at timestamp not null default current_timestamp,

    primary key (id),
    check (updated_at >= created_at)
);

create index idx_user_account_search_name
on user_account using gin (full_name gin_trgm_ops);


create table access_area (
    id uuid default uuidv7(),
    label text not null,

    primary key (id)
);


create table access_card (
    id uuid default uuidv7(),
    label text not null,

    created_at timestamp not null default current_timestamp,
    updated_at timestamp not null default current_timestamp
    check (updated_at >= created_at),

    primary key (id)
);


create table access_card_ownership (
    id uuid default uuidv7(),

    card_id uuid not null references access_card (id)
    on update cascade on delete cascade,

    user_id uuid not null references user_account (id)
    on update cascade on delete cascade,

    primary key (id),
    unique (card_id, user_id)
);


create table card_usage (
    id uuid default uuidv7(),

    card_id uuid not null references access_card (id)
    on update cascade on delete cascade,

    user_id uuid not null references user_account (id)
    on update cascade on delete cascade,

    access_area_id uuid not null references access_area (id)
    on update cascade on delete cascade,

    usage_timestamp timestamp not null
    default current_timestamp,

    primary key (id)
);

create index idx_card_usage_card_id
on card_usage (card_id);

create index idx_card_usage_user_id
on card_usage (user_id);

create index idx_card_usage_area_id
on card_usage (access_area_id);

create index idx_card_usage_timestamp
on card_usage (usage_timestamp);


create table item (
    id uuid default uuidv7(),
    label text not null,

    primary key (id)
);

create index idx_item_search_label
on item using gin (label gin_trgm_ops);