-- TGMS Nampally: database schema for Supabase (PostgreSQL)
-- Run in Supabase > SQL Editor. Roles: principal, teacher, office, parent.
-- Student data belongs to minors: RLS is ON everywhere; nothing personal is public.

create table profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  full_name text not null,
  role text not null check (role in ('principal','teacher','office','parent')),
  created_at timestamptz default now()
);

create table classes (
  id serial primary key,
  name text unique not null            -- e.g. 'Class 6', 'Inter 1st year - MPC'
);

create table students (
  id uuid primary key default gen_random_uuid(),
  admission_no text unique not null,
  full_name text not null,
  class_id int references classes(id),
  section text,
  dob date,
  gender text check (gender in ('M','F','O')),
  guardian_name text,
  guardian_phone text,
  parent_user uuid references profiles(id),   -- login of the parent, if any
  active boolean default true,
  created_at timestamptz default now()
);

create table attendance (
  id uuid primary key default gen_random_uuid(),
  student_id uuid not null references students(id) on delete cascade,
  day date not null,
  status text not null check (status in ('present','absent','leave')),
  marked_by uuid references profiles(id),
  unique (student_id, day)
);

create table exams (
  id uuid primary key default gen_random_uuid(),
  name text not null, class_id int references classes(id), exam_date date
);

create table marks (
  id uuid primary key default gen_random_uuid(),
  exam_id uuid not null references exams(id) on delete cascade,
  student_id uuid not null references students(id) on delete cascade,
  subject text not null,
  marks numeric, max_marks numeric default 100,
  unique (exam_id, student_id, subject)
);

create table notices (
  id uuid primary key default gen_random_uuid(),
  title text not null, title_te text, body text, file_url text,
  is_public boolean default false,
  published_at timestamptz default now(),
  created_by uuid references profiles(id)
);

create table applications (               -- admission enquiries from the public form
  id uuid primary key default gen_random_uuid(),
  student_name text not null, phone text not null,
  class_or_group text, message text,
  status text default 'new' check (status in ('new','contacted','selected','rejected','joined')),
  created_at timestamptz default now()
);

create table certificate_requests (
  id uuid primary key default gen_random_uuid(),
  student_id uuid not null references students(id),
  kind text not null check (kind in ('bonafide','tc')),
  status text default 'pending' check (status in ('pending','issued','rejected')),
  requested_by uuid references profiles(id),
  created_at timestamptz default now()
);

create table audit_log (
  id bigserial primary key,
  user_id uuid, action text, table_name text, row_id text,
  at timestamptz default now()
);

-- ---------- helpers ----------
create function auth_role() returns text language sql stable security definer set search_path = public
as $$ select role from profiles where id = auth.uid() $$;

create function audit() returns trigger language plpgsql security definer set search_path = public
as $$ begin
  insert into audit_log(user_id, action, table_name, row_id)
  values (auth.uid(), tg_op, tg_table_name, coalesce(to_jsonb(new)->>'id', to_jsonb(old)->>'id'));
  return coalesce(new, old);
end $$;

-- call from the app whenever a student record is opened (views cannot be caught by triggers)
create function log_view(student uuid) returns void language sql security definer set search_path = public
as $$ insert into audit_log(user_id, action, table_name, row_id) values (auth.uid(), 'VIEW', 'students', student::text) $$;

create trigger a_students after insert or update or delete on students for each row execute function audit();
create trigger a_marks after insert or update or delete on marks for each row execute function audit();
create trigger a_attendance after insert or update or delete on attendance for each row execute function audit();
create trigger a_certs after insert or update or delete on certificate_requests for each row execute function audit();

-- ---------- row-level security ----------
alter table profiles enable row level security;
alter table classes enable row level security;
alter table students enable row level security;
alter table attendance enable row level security;
alter table exams enable row level security;
alter table marks enable row level security;
alter table notices enable row level security;
alter table applications enable row level security;
alter table certificate_requests enable row level security;
alter table audit_log enable row level security;

create policy profiles_self on profiles for select using (id = auth.uid() or auth_role() = 'principal');
create policy profiles_admin on profiles for all using (auth_role() = 'principal') with check (auth_role() = 'principal');

create policy classes_read on classes for select using (auth.uid() is not null);
create policy classes_write on classes for all using (auth_role() in ('principal','office')) with check (auth_role() in ('principal','office'));

create policy students_staff_read on students for select using (auth_role() in ('principal','teacher','office'));
create policy students_parent_read on students for select using (parent_user = auth.uid());
create policy students_write on students for all using (auth_role() in ('principal','office')) with check (auth_role() in ('principal','office'));

create policy att_staff on attendance for select using (auth_role() in ('principal','teacher','office'));
create policy att_parent on attendance for select using (student_id in (select id from students where parent_user = auth.uid()));
create policy att_write on attendance for all using (auth_role() in ('principal','teacher','office')) with check (auth_role() in ('principal','teacher','office'));

create policy exams_read on exams for select using (auth.uid() is not null);
create policy exams_write on exams for all using (auth_role() in ('principal','teacher')) with check (auth_role() in ('principal','teacher'));

create policy marks_staff on marks for select using (auth_role() in ('principal','teacher','office'));
create policy marks_parent on marks for select using (student_id in (select id from students where parent_user = auth.uid()));
create policy marks_write on marks for all using (auth_role() in ('principal','teacher')) with check (auth_role() in ('principal','teacher'));

create policy notices_public on notices for select using (is_public = true);
create policy notices_staff on notices for select using (auth_role() in ('principal','teacher','office'));
create policy notices_write on notices for all using (auth_role() in ('principal','office')) with check (auth_role() in ('principal','office'));

create policy apps_insert on applications for insert to anon, authenticated with check (true);  -- public enquiry form
create policy apps_staff on applications for select using (auth_role() in ('principal','office'));
create policy apps_update on applications for update using (auth_role() in ('principal','office'));

create policy certs_staff on certificate_requests for all using (auth_role() in ('principal','office')) with check (auth_role() in ('principal','office'));
create policy certs_parent_read on certificate_requests for select using (requested_by = auth.uid());
create policy certs_parent_insert on certificate_requests for insert with check (requested_by = auth.uid());

create policy audit_principal on audit_log for select using (auth_role() = 'principal');

-- ---------- dummy seed data (replace before going live) ----------
insert into classes(name) values
 ('Class 6'),('Class 7'),('Class 8'),('Class 9'),('Class 10'),
 ('Inter 1st year - MPC'),('Inter 1st year - BiPC'),('Inter 1st year - CEC'),('Inter 1st year - ACE'),
 ('Inter 2nd year - MPC'),('Inter 2nd year - BiPC'),('Inter 2nd year - CEC'),('Inter 2nd year - ACE');

insert into students(admission_no, full_name, class_id, section, gender, guardian_name, guardian_phone)
select 'DUMMY-'||g, 'Sample Student '||g, (g % 5) + 1, 'A', 'O', 'Sample Guardian '||g, '0000000000'
from generate_series(1,6) g;

insert into notices(title, title_te, body, is_public) values
 ('Sample notice: Admissions open', 'నమూనా నోటీసు: ప్రవేశాలు ప్రారంభం', 'Replace with the real notice.', true),
 ('Sample notice: Examination timetable', 'నమూనా నోటీసు: పరీక్షల టైమ్‌టేబుల్', 'Replace with the real notice.', true);

-- After creating your own login in Supabase > Authentication, make yourself the principal:
-- insert into profiles(id, full_name, role) values ('<your auth user id>', 'G. Bhagyalakshmi', 'principal');