-- types

create type user_role_enum as enum (
    'admin',
    'analyst',
    'captain',
    'developer',
    'firefighter',
    'sargeant',
    'none'
);

-- 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,
    is_active boolean not null default false,
    created_at timestamp not null default current_timestamp,
    updated_at timestamp not null default current_timestamp,

    primary key (id)
);

create index idx_user_account_email
on user_account (email);


create table crew (
    id uuid default uuidv7(),
    crew_leader uuid not null references user_account (id)
    on update cascade on delete cascade,
    crew_name text unique not null,
    is_active boolean not null default false,
    created_at timestamp not null default current_timestamp,
    updated_at timestamp not null default current_timestamp,

    primary key (id)
);

create index idx_crew_leader
on crew (crew_leader);


create table crew_membership (
    id uuid default uuidv7(),
    crew_id uuid not null references crew (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 (crew_id, user_id)
);

create index idx_crew_membership_crew
on crew_membership (crew_id);

create index idx_crew_membership_user
on crew_membership (user_id);