-- Enable UUID extension
create extension if not exists "uuid-ossp";

-- Users table
create table if not exists public.users (
    user_id uuid primary key,
    username text unique not null,
    password text not null,
    email text,
    date_joined timestamp with time zone not null,
    access_level text not null
);

-- Sessions table with composite primary key (user_id, token)
create table if not exists public.sessions (
    user_id uuid references public.users(user_id),
    token text not null,
    expires timestamp with time zone not null,
    device text not null,
    primary key (user_id, token)  -- Composite primary key
);

-- Create indexes for better performance
create index if not exists idx_users_username on public.users(username);
create index if not exists idx_users_user_id on public.users(user_id);
create index if not exists idx_sessions_user_id on public.sessions(user_id);
create index if not exists idx_sessions_token on public.sessions(token);

-- Set up Row Level Security (RLS)
alter table public.users enable row level security;
alter table public.sessions enable row level security;

-- Create policies
create policy "Enable read access for all users"
    on public.users for select
    using (true);

create policy "Enable insert for authenticated users only"
    on public.users for insert
    with check (true);

create policy "Enable update for authenticated users"
    on public.users for update
    using (true);

create policy "Enable all access for sessions"
    on public.sessions for all
    using (true);