-- ============================================================================
-- PHÂN QUYỀN THEO NHÓM NGƯỜI DÙNG — WEB PHÒNG TỔ CHỨC VÀ QUẢN LÍ ĐÀO TẠO
-- Chạy 1 lần trong Supabase Dashboard → SQL Editor → New query → dán toàn bộ
-- file này → Run. File này KHÔNG chứa khóa bí mật nào, chỉ tạo cấu trúc.
--
-- File này THAY file phan-quyen-module.sql cũ (cấp quyền theo từng tài
-- khoản). Trang web từ nay chỉ đọc/ghi theo mô hình NHÓM. Nếu anh đã từng
-- chạy file cũ thì bảng module_permissions cũ vẫn được giữ nguyên (không
-- xóa), và phạm vi dữ liệu đã lưu trong đó được chép sang ở mục 7.
--
-- Nội dung:
--   1) Bảng public.user_groups: nhóm người dùng. 5 nhóm mặc định ứng với
--      5 vai trò đã chốt (Admin quản trị, Ban giám hiệu, Tổ trưởng chuyên
--      môn, Giáo viên, Cán bộ phòng đào tạo); Admin thêm nhóm mới ngay
--      trên trang web, không cần sửa SQL.
--   2) Bảng public.group_permissions: quyền của NHÓM theo module_key —
--      gồm cả mã module ('doi-chieu', 'thong-ke', 'gio-giang', 'tao-tkb', 'tin-tuc'),
--      từng tab của module Thống kê điểm ('thong-ke:<tab>') và 2 chức
--      năng của module Tính Giờ Giảng ('gio-giang:cau-hinh',
--      'gio-giang:tinh-gio'). Đây là chỗ 2 tab "Phân quyền module" và
--      "Phân quyền chức năng" trên trang web đọc/ghi.
--   3) profiles thêm cột: group_id (người dùng thuộc nhóm nào),
--      account_status (active / locked), data_scope_type +
--      data_scope_value (phạm vi dữ liệu theo TỪNG người dùng),
--      granted_by + granted_at (ai cấp quyền, cấp lúc nào), admin_note
--      (ghi chú), last_sign_in_at (lần đăng nhập cuối).
--   4) Mở rộng danh sách vai trò hợp lệ của profiles.role (như file cũ).
--   5) Gán sẵn người dùng hiện có vào nhóm mặc định ứng với vai trò của họ.
--   6) Quyền mặc định cho 5 nhóm: tab xem số liệu mở cho nhóm có quyền
--      module; riêng tab Tải điểm chỉ mở cho Admin.
--   7) Chép phạm vi dữ liệu từ bảng module_permissions cũ (nếu có).
--   8) Đồng bộ lần đăng nhập cuối từ auth.users sang profiles.
--   9) RLS cho 2 bảng mới: mọi tài khoản đã đăng nhập đọc được danh sách
--      nhóm; quyền thì chỉ đọc được của nhóm mình (Admin đọc hết); chỉ
--      Admin mới thêm/sửa/xóa.
--
-- Lưu ý: RLS trên các bảng dữ liệu điểm (scores, term_students,
-- user_scopes...) KHÔNG đổi — phạm vi dữ liệu vẫn bị chặn theo từng
-- người dùng ở phía máy chủ như cũ. Hai bảng mới chỉ là lớp quyền ở giao
-- diện site (ẩn module, chặn mở module, chia nhóm), không thay thế RLS.
-- ============================================================================

-- ---------------------------------------------------------------------------
-- 0. Hàm phụ trợ is_admin() (đã có sẵn nếu project đã chạy schema của module
--    Thống kê điểm; tạo lại ở đây cho chắc, nội dung không đổi).
-- ---------------------------------------------------------------------------
create or replace function public.is_admin()
returns boolean
language sql stable security definer set search_path = public as $$
  select exists (
    select 1 from public.profiles p
    where p.id = auth.uid() and p.role = 'admin'
  );
$$;

-- ---------------------------------------------------------------------------
-- 1. BẢNG user_groups — nhóm người dùng
--    role_key: chỉ 5 nhóm mặc định mới có (gắn với vai trò trong profiles);
--    nhóm tự tạo để trống (null). Khi chia người dùng vào 1 nhóm mặc định,
--    trang web đồng bộ luôn profiles.role theo role_key của nhóm đó để
--    cổng đăng nhập và RLS ở máy chủ giữ nguyên cách chặn như cũ.
-- ---------------------------------------------------------------------------
create table if not exists public.user_groups (
  id          uuid primary key default gen_random_uuid(),
  name        text not null,
  description text,
  role_key    text unique,
  is_system   boolean not null default false,
  sort_order  int not null default 100,
  created_at  timestamptz not null default now()
);

alter table public.user_groups enable row level security;

-- ---------------------------------------------------------------------------
-- 2. profiles — THÊM CÁC CỘT PHỤC VỤ MÔ HÌNH NHÓM
-- ---------------------------------------------------------------------------
alter table public.profiles
  add column if not exists group_id         uuid references public.user_groups (id) on delete set null,
  add column if not exists account_status   text not null default 'active',
  add column if not exists data_scope_type  text not null default 'school',
  add column if not exists data_scope_value text,
  add column if not exists granted_by       uuid references public.profiles (id),
  add column if not exists granted_at       timestamptz,
  add column if not exists admin_note       text,
  add column if not exists last_sign_in_at  timestamptz;

do $$
begin
  begin
    alter table public.profiles add constraint profiles_account_status_check
      check (account_status in ('active', 'locked'));
  exception when duplicate_object then null;
  end;
  begin
    alter table public.profiles add constraint profiles_data_scope_type_check
      check (data_scope_type in ('school', 'org', 'class'));
  exception when duplicate_object then null;
  end;
end $$;

-- ---------------------------------------------------------------------------
-- 3. BẢNG group_permissions — quyền của nhóm theo module_key
--    module_key: mã module của site. Hiện có:
--      'doi-chieu'  = Đối chiếu bảng điểm
--      'thong-ke'   = Thống kê điểm
--      'gio-giang'  = Tính Giờ Giảng
--      'tao-tkb'    = Tạo file up TKB
--    Các tab của module Thống kê điểm cũng cấp quyền riêng bằng dòng có
--    module_key dạng 'thong-ke:<tab>':
--      'thong-ke:overview' = tab Tổng quan
--      'thong-ke:compare'  = tab Tương quan học tập
--      'thong-ke:classes'  = tab Lớp học
--      'thong-ke:dist'     = tab Phổ điểm
--      'thong-ke:subjects' = tab Môn học
--      'thong-ke:alerts'   = tab Cảnh báo
--      'thong-ke:upload'   = tab Tải điểm (tạo kỳ, tải điểm lên)
--    2 chức năng của module Tính Giờ Giảng cũng cấp quyền riêng bằng
--    module_key dạng 'gio-giang:<chức năng>':
--      'gio-giang:cau-hinh' = tab Cấu hình tiết học (mặc định chỉ Admin)
--      'tin-tuc:dang-bai' = chức năng Đăng bài của module Tin tức &
--                             Thông báo (mặc định chỉ Admin)
--      'gio-giang:tinh-gio' = tab Tính giờ (mặc định theo quyền module:
--                             nhóm nào được vào module thì dùng được)
--    Module Tạo file up TKB chỉ có khóa module 'tao-tkb', không có
--    tab/chức năng con. Module/tab mới thêm sau này chỉ cần thêm dòng
--    với module_key mới, không phải sửa cấu trúc bảng.
-- ---------------------------------------------------------------------------
create table if not exists public.group_permissions (
  group_id   uuid not null references public.user_groups (id) on delete cascade,
  module_key text not null,
  can_access boolean not null default false,
  updated_at timestamptz not null default now(),
  primary key (group_id, module_key)
);

alter table public.group_permissions enable row level security;

-- Nhóm của người đang đăng nhập (dùng trong policy đọc quyền của nhóm mình).
create or replace function public.current_user_group()
returns uuid
language sql stable security definer set search_path = public as $$
  select group_id from public.profiles where id = auth.uid();
$$;

-- ---------------------------------------------------------------------------
-- 4. MỞ RỘNG VAI TRÒ TRÊN profiles.role (như file SQL cũ)
--    Thêm 4 vai trò của site: bgh (Ban giám hiệu), to_truong (Tổ trưởng
--    chuyên môn), giao_vien (Giáo viên), can_bo (Cán bộ phòng đào tạo).
--    Giữ 'viewer' (vai trò cũ, vẫn được coi là đã cấp quyền) và 'pending'.
-- ---------------------------------------------------------------------------
alter table public.profiles drop constraint if exists profiles_role_check;
alter table public.profiles add constraint profiles_role_check
  check (role in ('admin', 'bgh', 'to_truong', 'giao_vien', 'can_bo',
                  'viewer', 'pending'));

-- ---------------------------------------------------------------------------
-- 5. RLS CHO 2 BẢNG MỚI
-- ---------------------------------------------------------------------------
-- user_groups: tài khoản đã đăng nhập nào cũng đọc được danh sách nhóm
-- (để hiện tên nhóm của mình); chỉ Admin mới thêm / sửa / xóa nhóm.
drop policy if exists user_groups_select on public.user_groups;
create policy user_groups_select on public.user_groups
  for select to authenticated
  using (true);

drop policy if exists user_groups_admin_write on public.user_groups;
create policy user_groups_admin_write on public.user_groups
  for all to authenticated
  using (public.is_admin()) with check (public.is_admin());

grant select, insert, update, delete
  on public.user_groups to authenticated;

-- group_permissions: tự đọc quyền của nhóm mình; admin đọc hết (để hiển
-- thị trang cấp quyền); chỉ Admin mới sửa được.
drop policy if exists group_permissions_select on public.group_permissions;
create policy group_permissions_select on public.group_permissions
  for select to authenticated
  using (public.is_admin() or group_id = public.current_user_group());

drop policy if exists group_permissions_admin_write on public.group_permissions;
create policy group_permissions_admin_write on public.group_permissions
  for all to authenticated
  using (public.is_admin()) with check (public.is_admin());

grant select, insert, update, delete
  on public.group_permissions to authenticated;

-- ---------------------------------------------------------------------------
-- 6. TẠO 5 NHÓM MẶC ĐỊNH + QUYỀN MẶC ĐỊNH + GÁN NGƯỜI DÙNG HIỆN CÓ
-- ---------------------------------------------------------------------------
insert into public.user_groups (name, description, role_key, is_system, sort_order)
values
  ('Admin quản trị',
   'Người quản trị hệ thống: quản lí nhóm và người dùng, cấp quyền, cấu hình hệ thống, truy cập mọi module.',
   'admin', true, 1),
  ('Ban giám hiệu',
   'Lãnh đạo nhà trường: xem tổng quan và báo cáo của phòng, theo dõi kết quả đối chiếu; không sửa cấu hình hệ thống.',
   'bgh', true, 2),
  ('Tổ trưởng chuyên môn',
   'Tổ trưởng các tổ chuyên môn: dùng các module liên quan đến tổ của mình, xem báo cáo và dữ liệu thuộc phạm vi tổ phụ trách.',
   'to_truong', true, 3),
  ('Giáo viên',
   'Giáo viên trong trường: dùng các module nghiệp vụ được cấp quyền, xem dữ liệu liên quan đến lớp và môn mình phụ trách.',
   'giao_vien', true, 4),
  ('Cán bộ phòng đào tạo',
   'Cán bộ Phòng Tổ chức và Quản lí đào tạo: vận hành các module của phòng — đối chiếu bảng điểm, thống kê điểm, văn bản & biểu mẫu, lịch công tác.',
   'can_bo', true, 5)
on conflict (role_key) do nothing;

-- Quyền mặc định: các tab xem số liệu (Tổng quan, Tương quan học tập,
-- Lớp học, Phổ điểm, Môn học, Cảnh báo) mở cho mọi nhóm được vào module
-- Thống kê điểm; riêng tab Tải điểm chỉ mở cho Admin. Giáo viên mặc định
-- chưa vào module Đối chiếu bảng điểm (Admin mở thêm trên trang web nếu
-- cần). Module Tính Giờ Giảng và Tạo file up TKB mặc định chỉ Admin vào
-- được (Admin mở thêm cho nhóm khác trên trang web nếu cần); chức năng
-- 'gio-giang:tinh-gio' ghi sẵn true cho mọi nhóm VÌ nó áp dụng theo
-- quyền module — nhóm chưa được vào module Tính Giờ Giảng thì vẫn bị
-- chặn ở khóa module. Tab Cấu hình tiết học ('gio-giang:cau-hinh') chỉ
-- Admin. Admin thì không cần dòng quyền — trang web luôn cho Admin vào
-- mọi module — nhưng cứ ghi sẵn cho rõ.
insert into public.group_permissions (group_id, module_key, can_access)
select g.id, v.module_key, v.can_access
from public.user_groups g
join (values
  ('admin',     'doi-chieu',         true),
  ('admin',     'thong-ke',          true),
  ('admin',     'thong-ke:overview', true),
  ('admin',     'thong-ke:compare',  true),
  ('admin',     'thong-ke:classes',  true),
  ('admin',     'thong-ke:dist',     true),
  ('admin',     'thong-ke:subjects', true),
  ('admin',     'thong-ke:alerts',   true),
  ('admin',     'thong-ke:upload',   true),
  ('bgh',       'doi-chieu',         true),
  ('bgh',       'thong-ke',          true),
  ('bgh',       'thong-ke:overview', true),
  ('bgh',       'thong-ke:compare',  true),
  ('bgh',       'thong-ke:classes',  true),
  ('bgh',       'thong-ke:dist',     true),
  ('bgh',       'thong-ke:subjects', true),
  ('bgh',       'thong-ke:alerts',   true),
  ('bgh',       'thong-ke:upload',   false),
  ('to_truong', 'doi-chieu',         true),
  ('to_truong', 'thong-ke',          true),
  ('to_truong', 'thong-ke:overview', true),
  ('to_truong', 'thong-ke:compare',  true),
  ('to_truong', 'thong-ke:classes',  true),
  ('to_truong', 'thong-ke:dist',     true),
  ('to_truong', 'thong-ke:subjects', true),
  ('to_truong', 'thong-ke:alerts',   true),
  ('to_truong', 'thong-ke:upload',   false),
  ('giao_vien', 'doi-chieu',         false),
  ('giao_vien', 'thong-ke',          true),
  ('giao_vien', 'thong-ke:overview', true),
  ('giao_vien', 'thong-ke:compare',  true),
  ('giao_vien', 'thong-ke:classes',  true),
  ('giao_vien', 'thong-ke:dist',     true),
  ('giao_vien', 'thong-ke:subjects', true),
  ('giao_vien', 'thong-ke:alerts',   true),
  ('giao_vien', 'thong-ke:upload',   false),
  ('can_bo',    'doi-chieu',         true),
  ('can_bo',    'thong-ke',          true),
  ('can_bo',    'thong-ke:overview', true),
  ('can_bo',    'thong-ke:compare',  true),
  ('can_bo',    'thong-ke:classes',  true),
  ('can_bo',    'thong-ke:dist',     true),
  ('can_bo',    'thong-ke:subjects', true),
  ('can_bo',    'thong-ke:alerts',   true),
  ('can_bo',    'thong-ke:upload',   false),
  ('admin',     'gio-giang',           true),
  ('admin',     'gio-giang:cau-hinh',  true),
  ('admin',     'gio-giang:tinh-gio',  true),
  ('admin',     'tao-tkb',             true),
  ('bgh',       'gio-giang',           false),
  ('bgh',       'gio-giang:cau-hinh',  false),
  ('bgh',       'gio-giang:tinh-gio',  true),
  ('bgh',       'tao-tkb',             false),
  ('to_truong', 'gio-giang',           false),
  ('to_truong', 'gio-giang:cau-hinh',  false),
  ('to_truong', 'gio-giang:tinh-gio',  true),
  ('to_truong', 'tao-tkb',             false),
  ('giao_vien', 'gio-giang',           false),
  ('giao_vien', 'gio-giang:cau-hinh',  false),
  ('giao_vien', 'gio-giang:tinh-gio',  true),
  ('giao_vien', 'tao-tkb',             false),
  ('can_bo',    'gio-giang',           false),
  ('can_bo',    'gio-giang:cau-hinh',  false),
  ('can_bo',    'gio-giang:tinh-gio',  true),
  ('can_bo',    'tao-tkb',             false),
  ('admin',     'tin-tuc',             true),
  ('admin',     'tin-tuc:dang-bai',    true),
  ('bgh',       'tin-tuc',             false),
  ('bgh',       'tin-tuc:dang-bai',    false),
  ('to_truong', 'tin-tuc',             false),
  ('to_truong', 'tin-tuc:dang-bai',    false),
  ('giao_vien', 'tin-tuc',             false),
  ('giao_vien', 'tin-tuc:dang-bai',    false),
  ('can_bo',    'tin-tuc',             false),
  ('can_bo',    'tin-tuc:dang-bai',    false)
) as v(role_key, module_key, can_access) on v.role_key = g.role_key
on conflict (group_id, module_key) do nothing;

-- Gán người dùng hiện có vào nhóm mặc định ứng với vai trò hiện tại.
-- Vai trò 'viewer' (cũ) và 'pending' chưa có nhóm tương ứng: để trống,
-- Admin chia nhóm cho họ ở tab "Danh sách người dùng" trên trang web.
update public.profiles p
set group_id = g.id
from public.user_groups g
where g.role_key = p.role
  and p.group_id is null;

-- ---------------------------------------------------------------------------
-- 7. CHÉP PHẠM VI DỮ LIỆU TỪ BẢNG module_permissions CŨ (NẾU ĐÃ CHẠY FILE CŨ)
--    Phạm vi cũ lưu theo từng module; mô hình mới lưu 1 phạm vi cho mỗi
--    người dùng — ưu tiên lấy dòng của module Thống kê điểm.
-- ---------------------------------------------------------------------------
do $$
begin
  if to_regclass('public.module_permissions') is not null then
    update public.profiles p
    set data_scope_type = mp.scope_type,
        data_scope_value = mp.scope_value
    from (
      select distinct on (user_id) user_id, scope_type, scope_value
      from public.module_permissions
      where module_key in ('thong-ke', 'doi-chieu')
      order by user_id, module_key desc
    ) mp
    where mp.user_id = p.id;
  end if;
end $$;

-- ---------------------------------------------------------------------------
-- 8. LẦN ĐĂNG NHẬP CUỐI: chép sẵn từ auth.users + trigger đồng bộ về sau
--    (profiles chỉ cho Admin sửa qua giao diện, nên phải đồng bộ ở tầng
--    cơ sở dữ liệu). Toàn bộ mục này nằm trong khối có bắt lỗi: nếu project
--    không cho tạo trigger trên auth.users thì các mục khác vẫn chạy xong,
--    chỉ riêng cột "Đăng nhập cuối" sẽ để trống.
-- ---------------------------------------------------------------------------
create or replace function public.sync_profile_last_sign_in()
returns trigger
language plpgsql security definer set search_path = public as $$
begin
  update public.profiles
  set last_sign_in_at = new.last_sign_in_at
  where id = new.id
    and last_sign_in_at is distinct from new.last_sign_in_at;
  return new;
end $$;

do $$
begin
  update public.profiles p
  set last_sign_in_at = u.last_sign_in_at
  from auth.users u
  where u.id = p.id and u.last_sign_in_at is not null;

  drop trigger if exists on_auth_user_sign_in on auth.users;
  create trigger on_auth_user_sign_in
    after update of last_sign_in_at on auth.users
    for each row execute function public.sync_profile_last_sign_in();
exception when others then
  raise notice 'Chưa đồng bộ được lần đăng nhập cuối từ auth.users (%). Bỏ qua mục 8, các mục khác vẫn áp dụng bình thường.', sqlerrm;
end $$;

-- ============================================================================
-- Xong. Quay lại trang "Người dùng & phân quyền" trên site, tải lại trang:
-- Admin sẽ thấy 4 tab — Quản lí nhóm người dùng / Danh sách người dùng /
-- Phân quyền module / Phân quyền chức năng — và làm việc theo nhóm.
-- ============================================================================