-- Profiles (extends Supabase auth users) create table profiles ( id uuid references auth.users primary key, full_name text, role text check (role in ('teacher','student','admin')), department text, student_id text, created_at timestamptz default now() ); -- Departments create table departments ( id uuid primary key default gen_random_uuid(), name text not null, code text not null, hod text, students int default 0, teachers int default 0, created_at timestamptz default now() ); -- Courses create table courses ( id uuid primary key default gen_random_uuid(), name text not null, code text not null, teacher_id uuid references profiles(id), created_at timestamptz default now() ); -- Attendance create table attendance ( id uuid primary key default gen_random_uuid(), course_id uuid references courses(id), student_id uuid references profiles(id), date date default current_date, status text check (status in ('present','absent','late')), created_at timestamptz default now() ); -- Grades create table grades ( id uuid primary key default gen_random_uuid(), course_id uuid references courses(id), student_id uuid references profiles(id), midterm numeric, assignment numeric, final numeric, created_at timestamptz default now() ); -- Assignments create table assignments ( id uuid primary key default gen_random_uuid(), course_id uuid references courses(id), title text, description text, due_date date, created_at timestamptz default now() ); -- Announcements create table announcements ( id uuid primary key default gen_random_uuid(), course_id uuid references courses(id), teacher_id uuid references profiles(id), title text, message text, created_at timestamptz default now() ); -- Row Level Security (enable for all tables) alter table profiles enable row level security; alter table courses enable row level security; alter table attendance enable row level security; alter table grades enable row level security; alter table assignments enable row level security; alter table announcements enable row level security; -- Policies (allow authenticated users to read/write) create policy "auth_all" on profiles for all using (auth.role()='authenticated'); create policy "auth_all" on courses for all using (auth.role()='authenticated'); create policy "auth_all" on attendance for all using (auth.role()='authenticated'); create policy "auth_all" on grades for all using (auth.role()='authenticated'); create policy "auth_all" on assignments for all using (auth.role()='authenticated'); create policy "auth_all" on announcements for all using (auth.role()='authenticated');