--[ Database Schema Version ]--
-- Version: 1.0.0
-- Last Updated: 2024-02-24
-- Description: Core 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,
    full_name text,
    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';