create extension if not exists pgcrypto;

create table if not exists public.app_users (
  username text primary key check (username ~ '^[a-zA-Z0-9_.-]{1,64}$'),
  password_hash text not null,
  role text not null default 'user' check (role in ('admin','user')),
  created_at timestamptz not null default now(),
  last_login timestamptz
);

create table if not exists public.sessions (
  token_hash text primary key,
  username text not null references public.app_users(username) on delete cascade,
  created_at timestamptz not null default now(),
  expires_at timestamptz not null
);
create index if not exists sessions_expires_idx on public.sessions(expires_at);

create table if not exists public.api_tokens (
  id bigint generated by default as identity primary key,
  token_hash text not null unique,
  token_prefix text not null,
  username text not null references public.app_users(username) on delete cascade,
  label text not null,
  scope text not null default 'track',
  created_at timestamptz not null default now(),
  last_used_at timestamptz,
  revoked_at timestamptz
);
create index if not exists api_tokens_user_idx on public.api_tokens(username);
create index if not exists api_tokens_revoked_idx on public.api_tokens(revoked_at);

create table if not exists public.containers (
  container text primary key check (container ~ '^[A-Z0-9]{4,20}$'),
  interval_minutes integer not null default 360 check (interval_minutes between 5 and 1440),
  added_at timestamptz not null default now(),
  last_poll_ts double precision,
  last_poll_at timestamptz,
  last_data jsonb,
  last_status text,
  last_error text
);

create table if not exists public.track_requests (
  id bigint generated by default as identity primary key,
  container text not null,
  requested_by text,
  source text not null default 'api',
  status text,
  error text,
  created_at timestamptz not null default now()
);
create index if not exists track_requests_container_created_idx on public.track_requests(container, created_at desc);

alter table public.app_users enable row level security;
alter table public.sessions enable row level security;
alter table public.api_tokens enable row level security;
alter table public.containers enable row level security;
alter table public.track_requests enable row level security;

-- Vercel server-side uses service_role; no public RLS policies needed.
