-- Users table
CREATE TABLE IF NOT EXISTS public.users (
    user_id varchar(16) PRIMARY KEY, -- Use BIGINT for larger integer values
    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 varchar(16) 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);