schema.sql

-- Create a table for public profiles linked to auth.users
create table profiles (
  id uuid references auth.users not null primary key,
  email text,
  username text,
  full_name text,
  avatar_url text,
  updated_at timestamp with time zone
);

-- Secure the profiles table
alter table profiles enable row level security;

create policy "Public profiles are viewable by everyone." on profiles for select using ( true );
create policy "Users can insert their own profile." on profiles for insert with check ( auth.uid() = id );
create policy "Users can update own profile." on profiles for update using ( auth.uid() = id );


-- Create a table for bot configuration
create table bot_config (
  id bigint generated by default as identity primary key,
  system_instructions text not null default 'You are a helpful assistant.',
  allowed_channels text[] -- Array of channel IDs
);

-- Secure the bot_config table
alter table bot_config enable row level security;

-- Only allowing authenticated users (admins) to view and edit config for now.
-- In a real app, you might want stricter roles.
create policy "Allow authenticated read access" on bot_config for select using ( auth.role() = 'authenticated' );
create policy "Allow authenticated update access" on bot_config for update using ( auth.role() = 'authenticated' );
create policy "Allow authenticated insert access" on bot_config for insert with check ( auth.role() = 'authenticated' );

-- Create a table for chat logs
create table chat_logs (
  id bigint generated by default as identity primary key,
  user_handle text,
  message_content text,
  bot_response text,
  timestamp timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Secure the chat_logs table
alter table chat_logs enable row level security;

create policy "Allow authenticated read access" on chat_logs for select using ( auth.role() = 'authenticated' );
-- Bot service role (if using service key) can bypass RLS, or we can add a specific policy for it.
-- For simplicity here, we rely on the service role key for the bot writing logs, 
-- and authenticated web admins for reading logs.