supabase/.backup/seed.sql

--[ Database Schema Version ]--
-- Version: 1.0.0
-- Last Updated: 2024-02-24
-- Description: Initial schema setup for ClassConcepts
-- Dependencies: auth.users (Supabase Auth)

--[ Validation ]--
do $$
begin
    -- Verify required extensions
    if not exists (select 1 from pg_extension where extname = 'uuid-ossp') then
        raise exception 'Required extension uuid-ossp is not installed';
    end if;
    
    -- Verify auth schema exists
    if not exists (select 1 from information_schema.schemata where schema_name = 'auth') then
        raise exception 'Required auth schema is not available';
    end if;
    
    -- Verify storage schema exists
    if not exists (select 1 from information_schema.schemata where schema_name = 'storage') then
        raise exception 'Required storage schema is not available';
    end if;
end $$;

--[ 1. Extensions ]--
create extension if not exists "uuid-ossp";

-- Create rpc schema if it doesn't exist
create schema if not exists rpc;
grant usage on schema rpc to anon, authenticated;

-- Create exec_sql function for admin operations
create or replace function exec_sql(query text)
returns void as $$
begin
    execute query;
end;
$$ language plpgsql security definer;

-- Create updated_at trigger function
create or replace function public.handle_updated_at()
returns trigger as $$
begin
    new.updated_at = timezone('utc'::text, now());
    return new;
end;
$$ language plpgsql security definer;

--[ 5. Core Tables ]--
-- Base user profiles
create table if not exists public.profiles (
    id uuid primary key references auth.users(id) on delete cascade,
    email text not null unique,
    user_type text not null check (user_type in ('admin', 'email_teacher', 'email_student')),
    username text not null unique,
    display_name text,
    metadata jsonb default '{}'::jsonb,
    last_login timestamp with time zone,
    created_at timestamp with time zone default timezone('utc'::text, now()),
    updated_at timestamp with time zone default timezone('utc'::text, now())
);
comment on table public.profiles is 'User profiles linked to Supabase auth.users';
comment on column public.profiles.user_type is 'Type of user: admin, teacher, or student';

-- Institute import data
create table if not exists public.institute_imports (
    id uuid primary key default uuid_generate_v4(),
    urn text unique,
    establishment_name text not null,
    la_code text,
    la_name text,
    establishment_number text,
    establishment_type text,
    establishment_type_group text,
    establishment_status text,
    reason_establishment_opened text,
    open_date date,
    reason_establishment_closed text,
    close_date date,
    phase_of_education text,
    statutory_low_age integer,
    statutory_high_age integer,
    boarders text,
    nursery_provision text,
    official_sixth_form text,
    gender text,
    religious_character text,
    religious_ethos text,
    diocese text,
    admissions_policy text,
    school_capacity integer,
    special_classes text,
    census_date date,
    number_of_pupils integer,
    number_of_boys integer,
    number_of_girls integer,
    percentage_fsm numeric(5,2),
    trust_school_flag text,
    trusts_name text,
    school_sponsor_flag text,
    school_sponsors_name text,
    federation_flag text,
    federations_name text,
    ukprn text,
    fehe_identifier text,
    further_education_type text,
    ofsted_last_inspection date,
    last_changed_date date,
    street text,
    locality text,
    address3 text,
    town text,
    county text,
    postcode text,
    school_website text,
    telephone_num text,
    head_title text,
    head_first_name text,
    head_last_name text,
    head_preferred_job_title text,
    gssla_code text,
    parliamentary_constituency text,
    urban_rural text,
    rsc_region text,
    country text,
    uprn text,
    sen_stat boolean,
    sen_no_stat boolean,
    sen_unit_on_roll integer,
    sen_unit_capacity integer,
    resourced_provision_on_roll integer,
    resourced_provision_capacity integer,
    metadata jsonb default '{}'::jsonb,
    imported_at timestamp with time zone default timezone('utc'::text, now()),
    updated_at timestamp with time zone default timezone('utc'::text, now())
);
comment on table public.institute_imports is 'Raw institute data imported from external sources';

-- Active institutes
create table if not exists public.institutes (
    id uuid primary key default uuid_generate_v4(),
    import_id uuid references public.institute_imports(id),
    name text not null,
    urn text unique,
    status text not null default 'active' check (status in ('active', 'inactive', 'pending')),
    address jsonb default '{}'::jsonb,
    website text,
    metadata jsonb default '{}'::jsonb,
    neo4j_unique_id text,
    neo4j_public_sync_status text default 'pending' check (neo4j_public_sync_status in ('pending', 'synced', 'failed')),
    neo4j_public_sync_at timestamp with time zone,
    neo4j_private_sync_status text default 'not_started' check (neo4j_private_sync_status in ('not_started', 'pending', 'synced', 'failed')),
    neo4j_private_sync_at timestamp with time zone,
    created_at timestamp with time zone default timezone('utc'::text, now()),
    updated_at timestamp with time zone default timezone('utc'::text, now())
);
comment on table public.institutes is 'Active institutes in the system';

--[ 6. Relationship Tables ]--
-- Institute memberships
create table if not exists public.institute_memberships (
    id uuid primary key default uuid_generate_v4(),
    profile_id uuid references public.profiles(id) on delete cascade,
    institute_id uuid references public.institutes(id) on delete cascade,
    role text not null check (role in ('admin', 'teacher', 'student')),
    tldraw_preferences jsonb default '{}'::jsonb,
    metadata jsonb default '{}'::jsonb,
    created_at timestamp with time zone default timezone('utc'::text, now()),
    updated_at timestamp with time zone default timezone('utc'::text, now()),
    unique(profile_id, institute_id)
);
comment on table public.institute_memberships is 'Manages user roles and relationships with institutes';

-- Membership requests
create table if not exists public.institute_membership_requests (
    id uuid primary key default uuid_generate_v4(),
    profile_id uuid references public.profiles(id) on delete cascade,
    institute_id uuid references public.institutes(id) on delete cascade,
    requested_role text check (requested_role in ('teacher', 'student')),
    status text default 'pending' check (status in ('pending', 'approved', 'rejected')),
    metadata jsonb default '{}'::jsonb,
    created_at timestamp with time zone default timezone('utc'::text, now()),
    updated_at timestamp with time zone default timezone('utc'::text, now())
);
comment on table public.institute_membership_requests is 'Tracks requests to join institutes';

--[ 7. Audit Tables ]--
-- System audit logs
create table if not exists public.audit_logs (
    id uuid primary key default uuid_generate_v4(),
    profile_id uuid references public.profiles(id) on delete set null,
    action_type text,
    table_name text,
    record_id uuid,
    changes jsonb,
    created_at timestamp with time zone default timezone('utc'::text, now())
);
comment on table public.audit_logs is 'System-wide audit trail for important operations';

--[ 8. Auth Functions ]--
-- Create a secure function to check admin status
create or replace function auth.is_admin()
returns boolean as $$
  select coalesce(
    (select true
     from public.profiles
     where id = auth.uid()
     and user_type = 'admin'),
    false
  );
$$ language sql security definer;

-- Create a secure function to check super admin status
create or replace function auth.is_super_admin()
returns boolean as $$
  select coalesce(
    (select role = 'supabase_admin'
     from auth.users
     where id = auth.uid()),
    false
  );
$$ language sql security definer;

-- Create public wrappers for the auth functions
create or replace function public.is_admin()
returns boolean as $$
  select auth.is_admin();
$$ language sql security definer;

create or replace function public.is_super_admin()
returns boolean as $$
  select auth.is_super_admin();
$$ language sql security definer;

-- Grant execute permissions
grant execute on function public.is_admin to authenticated;
grant execute on function public.is_super_admin to authenticated;
grant execute on function auth.is_admin to authenticated;
grant execute on function auth.is_super_admin to authenticated;

-- Initial admin setup function
create or replace function public.setup_initial_admin(admin_email text)
returns json
language plpgsql
security definer
as $$
declare
    result json;
begin
    -- Only allow this to run as service role or supabase_admin
    if not (
        current_user = 'service_role' 
        or exists (
            select 1 from pg_roles 
            where rolname = current_user 
            and rolsuper
        )
    ) then
        raise exception 'Must be run as service_role or superuser';
    end if;

    -- Update user_type and username for admin
    update public.profiles
    set user_type = 'admin',
        username = coalesce(username, 'superadmin'),
        display_name = coalesce(display_name, 'Super Admin')
    where email = admin_email
    returning json_build_object(
        'id', id,
        'email', email,
        'user_type', user_type,
        'username', username,
        'display_name', display_name
    ) into result;

    if result is null then
        raise exception 'Admin user with email % not found', admin_email;
    end if;

    return result;
end;
$$;

-- Grant execute permissions
revoke execute on function public.setup_initial_admin from public;
grant execute on function public.setup_initial_admin to authenticated, service_role, supabase_admin;

-- Create RPC wrapper for REST API access
create or replace function rpc.setup_initial_admin(admin_email text)
returns json
language plpgsql
security definer
as $$
begin
    return public.setup_initial_admin(admin_email);
end;
$$;

-- Grant execute permissions for RPC wrapper
grant execute on function rpc.setup_initial_admin to authenticated, service_role, supabase_admin;

--[ 9. Utility Functions ]--
-- Check if database is ready
create or replace function check_db_ready()
returns boolean
language plpgsql
security definer
as $$
begin
    -- Check if essential schemas exist
    if not exists (
        select 1 
        from information_schema.schemata 
        where schema_name in ('auth', 'storage', 'public')
    ) then
        return false;
    end if;

    -- Check if essential tables exist
    if not exists (
        select 1 
        from information_schema.tables 
        where table_schema = 'auth' 
        and table_name = 'users'
    ) then
        return false;
    end if;

    -- Check if RLS is enabled on public.profiles
    if not exists (
        select 1 
        from pg_tables 
        where schemaname = 'public' 
        and tablename = 'profiles' 
        and rowsecurity = true
    ) then
        return false;
    end if;

    return true;
end;
$$;

-- Grant execute permission
grant execute on function check_db_ready to anon, authenticated, service_role;

-- Function to handle new user registration
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
declare
    default_user_type text := 'email_student';
    default_username text;
begin
    -- Generate username from email
    default_username := split_part(new.email, '@', 1);
    
    insert into public.profiles (
        id,
        email,
        user_type,
        username,
        display_name
    )
    values (
        new.id,
        new.email,
        coalesce(new.raw_user_meta_data->>'user_type', default_user_type),
        coalesce(new.raw_user_meta_data->>'username', default_username),
        coalesce(new.raw_user_meta_data->>'display_name', default_username)
    );
    return new;
end;
$$;

-- Trigger for new user creation
drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
    after insert on auth.users
    for each row execute procedure public.handle_new_user();

--[ 10. Security Setup ]--
-- Enable RLS
alter table if exists public.profiles enable row level security;
alter table if exists public.institute_imports enable row level security;
alter table if exists public.institutes enable row level security;
alter table if exists public.institute_memberships enable row level security;
alter table if exists public.institute_membership_requests enable row level security;
alter table if exists public.audit_logs enable row level security;

-- First, ensure proper schema access
grant usage on schema public to anon, authenticated;

-- First, drop existing policies
drop policy if exists "Users can read and update own profile" on public.profiles;
drop policy if exists "Users can update their profile during registration" on public.profiles;

-- Create updated policies
create policy "Users can read own profile"
on public.profiles for select
to authenticated
using (auth.uid() = id);

create policy "Users can update own profile"
on public.profiles for update
to authenticated
using (auth.uid() = id)
with check (auth.uid() = id);

create policy "Public can read basic profile info"
on public.profiles for select 
to anon, authenticated
using (
    user_type in ('email_teacher', 'email_student')
);

create policy "Super admins have full access"
on public.profiles for all
using (auth.is_super_admin());

create policy "Admins can read all profiles"
on public.profiles for select
using (auth.is_admin() or auth.is_super_admin());

-- Grant permissions
grant select, update on public.profiles to authenticated;
grant select (id, email, user_type, display_name) on public.profiles to anon;

-- Storage bucket policies
alter table if exists storage.buckets enable row level security;

-- Allow super admin full access to buckets
create policy "Super admin has full access to buckets"
on storage.buckets for all
using (current_user = 'service_role' or current_user = 'supabase_admin' or current_user = 'authenticated');

-- Allow authenticated users to create buckets if they are the owner
create policy "Users can create their own buckets"
on storage.buckets for insert
to authenticated
with check (true);  -- We'll handle ownership in the application layer

-- Allow users to view buckets they own or public buckets
create policy "Users can view their own buckets"
on storage.buckets for select
to authenticated
using (
    owner::text = auth.uid()::text
);

--[ 11. Database Triggers ]--
drop trigger if exists handle_profiles_updated_at on public.profiles;
create trigger handle_profiles_updated_at
    before update on public.profiles
    for each row execute function public.handle_updated_at();

drop trigger if exists handle_institute_memberships_updated_at on public.institute_memberships;
create trigger handle_institute_memberships_updated_at
    before update on public.institute_memberships
    for each row execute function public.handle_updated_at();

drop trigger if exists handle_membership_requests_updated_at on public.institute_membership_requests;
create trigger handle_membership_requests_updated_at
    before update on public.institute_membership_requests
    for each row execute function public.handle_updated_at();

--[ 12. Permissions ]--
-- Grant schema access
grant usage on schema public to postgres, anon, authenticated;

-- Grant table permissions
grant all privileges on all tables in schema public to postgres;
grant select, insert, update on all tables in schema public to authenticated;

--[ 13. Realtime Setup ]--
-- Drop existing publication if it exists
drop publication if exists supabase_realtime;

-- Create publication (without IF NOT EXISTS)
create publication supabase_realtime;

-- Add tables to publication (these are idempotent operations)
alter publication supabase_realtime add table profiles;
alter publication supabase_realtime add table institute_imports;
alter publication supabase_realtime add table institutes;
alter publication supabase_realtime add table institute_memberships;
alter publication supabase_realtime add table institute_membership_requests;
alter publication supabase_realtime add table audit_logs;