-- ============================================================
--  TRANSVALIA HOËR SKOOL — Access Control
--  Supabase / PostgreSQL setup script (run once on a NEW project)
--
--  WHAT THIS CREATES
--    • profiles            — staff accounts (extends auth.users)
--    • visitor_logs        — visitor kiosk check-in / check-out
--    • teachers            — enrolled teachers + face descriptors (facial recognition)
--    • teacher_attendance  — teacher face check-in / check-out events
--    • audit_logs          — action audit trail
--    • departments         — lookup list
--    • search_visitor_profiles()  — returning-visitor search (kiosk)
--    • handle_new_user()   — auto-creates a profile on signup
--    • Row Level Security policies (incl. anonymous kiosk access)
--    • Realtime + indexes
--    • Storage bucket 'visitor-photos' (+ policies)
--
--  HOW TO RUN
--    Supabase Dashboard → SQL Editor → paste this whole file → RUN.
--    Safe to re-run (idempotent).
-- ============================================================

-- ─────────────────────────────────────────────────────────────
-- 0.  EXTENSIONS
-- ─────────────────────────────────────────────────────────────
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";   -- fuzzy name search

-- ─────────────────────────────────────────────────────────────
-- 1.  PROFILES  (extends auth.users)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS profiles (
  id                UUID         PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  full_name         TEXT         NOT NULL,
  email             TEXT         NOT NULL UNIQUE,
  role              TEXT         NOT NULL DEFAULT 'staff',   -- admin / staff / security / receptionist / teacher / ...
  department        TEXT,
  job_title         TEXT,
  employee_id       TEXT         UNIQUE,
  id_number         TEXT         UNIQUE,
  date_of_birth     DATE,
  phone             TEXT,
  emergency_contact_name         TEXT,
  emergency_contact_phone        TEXT,
  emergency_contact_relationship TEXT,
  home_address      TEXT,
  avatar_url        TEXT,
  is_active         BOOLEAN      NOT NULL DEFAULT TRUE,
  created_at        TIMESTAMPTZ  NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ  NOT NULL DEFAULT now()
);

-- Auto-update updated_at on any table that has the column
CREATE OR REPLACE FUNCTION touch_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END; $$;

DROP TRIGGER IF EXISTS trg_profiles_updated ON profiles;
CREATE TRIGGER trg_profiles_updated
  BEFORE UPDATE ON profiles
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

-- ─────────────────────────────────────────────────────────────
-- 2.  VISITOR LOGS  (visitor kiosk)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS visitor_logs (
  id              UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
  full_name       TEXT         NOT NULL,
  email           TEXT,
  phone           TEXT,
  company         TEXT,
  purpose         TEXT         NOT NULL,
  division        TEXT,
  host_id         UUID         REFERENCES profiles(id) ON DELETE SET NULL,

  -- Identification
  id_type         TEXT,        -- national_id / passport / drivers_license / company_id / other
  id_number       TEXT,

  -- Badge / photo
  badge_number    TEXT         UNIQUE NOT NULL,
  photo_url       TEXT,

  -- Visit times / status
  check_in        TIMESTAMPTZ  NOT NULL DEFAULT now(),
  check_out       TIMESTAMPTZ,
  status          TEXT         NOT NULL DEFAULT 'checked_in'
                  CHECK (status IN ('checked_in','checked_out','denied','pre_registered')),

  -- Vehicle fields (eNaTIS licence-disk scan — all optional)
  vehicle_reg       TEXT,
  vehicle_make      TEXT,
  vehicle_desc      TEXT,
  vehicle_vin       TEXT,
  vehicle_engine_no TEXT,
  vehicle_disc_no   TEXT,
  vehicle_reg_cert  TEXT,
  vehicle_colour    TEXT,
  vehicle_expiry    TEXT,
  vehicle_year      TEXT,
  equipment         TEXT,       -- laptop / equipment serial

  -- Driver fields (driver's-licence scan — all optional)
  driver_surname      TEXT,
  driver_first_names  TEXT,
  driver_dob          TEXT,
  driver_gender       TEXT,
  driver_licence_no   TEXT,

  -- Pre-registration reference
  pre_registered_by UUID       REFERENCES profiles(id),

  created_at      TIMESTAMPTZ  NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ  NOT NULL DEFAULT now()
);

DROP TRIGGER IF EXISTS trg_visitors_updated ON visitor_logs;
CREATE TRIGGER trg_visitors_updated
  BEFORE UPDATE ON visitor_logs
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

-- ─────────────────────────────────────────────────────────────
-- 3.  AUDIT LOGS
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS audit_logs (
  id          UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id     UUID        REFERENCES profiles(id) ON DELETE SET NULL,
  action      TEXT        NOT NULL,    -- 'clock_in', 'visitor_register', etc.
  resource    TEXT,
  details     JSONB,
  ip_address  INET,
  user_agent  TEXT,
  timestamp   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ─────────────────────────────────────────────────────────────
-- 4.  DEPARTMENTS  (lookup)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS departments (
  id          UUID  PRIMARY KEY DEFAULT uuid_generate_v4(),
  name        TEXT  NOT NULL UNIQUE,
  description TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO departments (name) VALUES
  ('Administration'),
  ('Academic'),
  ('Finance'),
  ('Human Resources'),
  ('Information Technology'),
  ('Operations'),
  ('Sport & Culture'),
  ('Security'),
  ('Facilities')
ON CONFLICT (name) DO NOTHING;

-- ─────────────────────────────────────────────────────────────
-- 5.  INDEXES
-- ─────────────────────────────────────────────────────────────
CREATE INDEX IF NOT EXISTS idx_profiles_role      ON profiles(role);
CREATE INDEX IF NOT EXISTS idx_profiles_is_active ON profiles(is_active);
CREATE INDEX IF NOT EXISTS idx_profiles_name_trgm ON profiles USING GIN (full_name gin_trgm_ops);

CREATE INDEX IF NOT EXISTS idx_vis_status    ON visitor_logs(status);
CREATE INDEX IF NOT EXISTS idx_vis_host      ON visitor_logs(host_id);
CREATE INDEX IF NOT EXISTS idx_vis_check_in  ON visitor_logs(check_in DESC);
CREATE INDEX IF NOT EXISTS idx_vis_badge     ON visitor_logs(badge_number);
CREATE INDEX IF NOT EXISTS idx_vis_name_trgm ON visitor_logs USING GIN (full_name gin_trgm_ops);

CREATE INDEX IF NOT EXISTS idx_audit_user      ON audit_logs(user_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_audit_action    ON audit_logs(action, timestamp DESC);

-- ─────────────────────────────────────────────────────────────
-- 6.  GRANTS  (required for PostgREST to expose tables)
-- ─────────────────────────────────────────────────────────────
GRANT USAGE  ON SCHEMA public                                 TO anon, authenticated;
GRANT SELECT ON TABLE  public.profiles                        TO anon, authenticated;
GRANT SELECT, INSERT, UPDATE ON TABLE public.visitor_logs     TO anon, authenticated;
GRANT SELECT, INSERT ON TABLE  public.audit_logs              TO authenticated;
GRANT SELECT ON TABLE  public.departments                     TO anon, authenticated;

-- ─────────────────────────────────────────────────────────────
-- 7.  ROW LEVEL SECURITY
-- ─────────────────────────────────────────────────────────────
ALTER TABLE profiles        ENABLE ROW LEVEL SECURITY;
ALTER TABLE visitor_logs    ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_logs      ENABLE ROW LEVEL SECURITY;
ALTER TABLE departments     ENABLE ROW LEVEL SECURITY;

-- Helper: current user's role (SECURITY DEFINER bypasses RLS recursion)
CREATE OR REPLACE FUNCTION current_user_role()
RETURNS TEXT LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT role FROM profiles WHERE id = auth.uid()
$$;

-- ── PROFILES ────────────────────────────────────────────────
DROP POLICY IF EXISTS "profiles: self read"              ON profiles;
DROP POLICY IF EXISTS "profiles: privileged read"        ON profiles;
DROP POLICY IF EXISTS "profiles: admin write"            ON profiles;
DROP POLICY IF EXISTS "profiles: self update"            ON profiles;
DROP POLICY IF EXISTS "profiles: anon read active staff" ON profiles;

CREATE POLICY "profiles: self read"
  ON profiles FOR SELECT USING (id = auth.uid());
CREATE POLICY "profiles: privileged read"
  ON profiles FOR SELECT USING (current_user_role() IN ('admin','security','receptionist'));
CREATE POLICY "profiles: admin write"
  ON profiles FOR ALL USING (current_user_role() = 'admin');
CREATE POLICY "profiles: self update"
  ON profiles FOR UPDATE USING (id = auth.uid()) WITH CHECK (id = auth.uid());
-- Kiosk (anonymous) may read active staff names for the host dropdown
CREATE POLICY "profiles: anon read active staff"
  ON profiles FOR SELECT TO anon USING (is_active = TRUE);

-- ── VISITOR LOGS ────────────────────────────────────────────
DROP POLICY IF EXISTS "visitors: public insert"          ON visitor_logs;
DROP POLICY IF EXISTS "visitors: public read for lookup" ON visitor_logs;
DROP POLICY IF EXISTS "visitors: public update"          ON visitor_logs;
DROP POLICY IF EXISTS "visitors: host read"              ON visitor_logs;
DROP POLICY IF EXISTS "visitors: privileged all"         ON visitor_logs;

-- Kiosk runs anonymously: it must be able to insert, look up and check out.
CREATE POLICY "visitors: public insert"
  ON visitor_logs FOR INSERT WITH CHECK (TRUE);
CREATE POLICY "visitors: public read for lookup"
  ON visitor_logs FOR SELECT TO anon USING (TRUE);
CREATE POLICY "visitors: public update"
  ON visitor_logs FOR UPDATE TO anon USING (TRUE) WITH CHECK (TRUE);
CREATE POLICY "visitors: host read"
  ON visitor_logs FOR SELECT USING (host_id = auth.uid());
CREATE POLICY "visitors: privileged all"
  ON visitor_logs FOR ALL USING (current_user_role() IN ('admin','security','receptionist'));

-- ── AUDIT LOGS ──────────────────────────────────────────────
DROP POLICY IF EXISTS "audit: admin read"  ON audit_logs;
DROP POLICY IF EXISTS "audit: self insert" ON audit_logs;

CREATE POLICY "audit: admin read"
  ON audit_logs FOR SELECT USING (current_user_role() = 'admin');
CREATE POLICY "audit: self insert"
  ON audit_logs FOR INSERT WITH CHECK (user_id = auth.uid() OR user_id IS NULL);

-- ── DEPARTMENTS ─────────────────────────────────────────────
DROP POLICY IF EXISTS "departments: all read"    ON departments;
DROP POLICY IF EXISTS "departments: admin write" ON departments;

CREATE POLICY "departments: all read"
  ON departments FOR SELECT USING (TRUE);
CREATE POLICY "departments: admin write"
  ON departments FOR ALL USING (current_user_role() = 'admin');

-- ─────────────────────────────────────────────────────────────
-- 8.  REALTIME
-- ─────────────────────────────────────────────────────────────
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_publication_tables
                 WHERE pubname = 'supabase_realtime' AND tablename = 'visitor_logs') THEN
    ALTER PUBLICATION supabase_realtime ADD TABLE visitor_logs;
  END IF;
END $$;

-- ─────────────────────────────────────────────────────────────
-- 9.  FUNCTION: returning-visitor search (used by the kiosk)
--     One deduplicated row per person (most recent visit).
--     SECURITY DEFINER so anon can call it without broad SELECT.
-- ─────────────────────────────────────────────────────────────
CREATE OR REPLACE FUNCTION public.search_visitor_profiles(
  p_query TEXT,
  p_limit INT DEFAULT 6
)
RETURNS TABLE (
  id          UUID,
  full_name   TEXT,
  email       TEXT,
  phone       TEXT,
  company     TEXT,
  id_number   TEXT,
  photo_url   TEXT,
  visit_count BIGINT
)
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  RETURN QUERY
  WITH ranked AS (
    SELECT
      vl.id, vl.full_name, vl.email, vl.phone, vl.company, vl.id_number, vl.photo_url,
      ROW_NUMBER() OVER (
        PARTITION BY LOWER(TRIM(vl.full_name)), COALESCE(vl.phone, '')
        ORDER BY vl.check_in DESC
      ) AS rn,
      COUNT(*) OVER (
        PARTITION BY LOWER(TRIM(vl.full_name)), COALESCE(vl.phone, '')
      ) AS visit_count
    FROM public.visitor_logs vl
    WHERE vl.full_name ILIKE '%' || p_query || '%'
       OR vl.phone     ILIKE '%' || p_query || '%'
       OR vl.email     ILIKE '%' || p_query || '%'
  )
  SELECT r.id, r.full_name, r.email, r.phone, r.company, r.id_number, r.photo_url, r.visit_count
  FROM ranked r
  WHERE r.rn = 1
  ORDER BY r.full_name ASC
  LIMIT p_limit;
END;
$$;

GRANT EXECUTE ON FUNCTION public.search_visitor_profiles(TEXT, INT) TO anon, authenticated;

-- ─────────────────────────────────────────────────────────────
-- 10. TRIGGER: auto-create a profile when an auth user signs up
-- ─────────────────────────────────────────────────────────────
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  INSERT INTO public.profiles (id, full_name, email, role, is_active)
  VALUES (
    NEW.id,
    COALESCE(NULLIF(TRIM(NEW.raw_user_meta_data->>'full_name'), ''), split_part(NEW.email, '@', 1)),
    NEW.email,
    COALESCE(NULLIF(TRIM(NEW.raw_user_meta_data->>'role'), ''), 'staff'),
    TRUE
  )
  ON CONFLICT (id) DO NOTHING;
  RETURN NEW;
END;
$$;

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 FUNCTION handle_new_user();

-- ─────────────────────────────────────────────────────────────
-- 11. STORAGE BUCKET: visitor photos
--     The kiosk uploads visitor photos anonymously and reads them
--     back via public URL (config.js → APP.photoBucket = 'visitor-photos').
-- ─────────────────────────────────────────────────────────────
INSERT INTO storage.buckets (id, name, public)
VALUES ('visitor-photos', 'visitor-photos', TRUE)
ON CONFLICT (id) DO NOTHING;

DROP POLICY IF EXISTS "visitor-photos: public read"   ON storage.objects;
DROP POLICY IF EXISTS "visitor-photos: public insert" ON storage.objects;

CREATE POLICY "visitor-photos: public read"
  ON storage.objects FOR SELECT
  USING (bucket_id = 'visitor-photos');

CREATE POLICY "visitor-photos: public insert"
  ON storage.objects FOR INSERT
  WITH CHECK (bucket_id = 'visitor-photos');

-- ─────────────────────────────────────────────────────────────
-- 12. TEACHERS  (facial recognition — enrolled staff + descriptors)
--     Photos reuse the visitor-photos bucket (folder 'teachers/').
--     SECURITY: anon read+write for the kiosk + enrolment POC. Before
--     production, move enrolment behind an admin login and drop anon writes.
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS teachers (
  id              UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
  full_name       TEXT         NOT NULL,
  email           TEXT,
  phone           TEXT,
  department      TEXT,
  photo_url       TEXT,
  face_descriptor JSONB,                 -- array of 128 floats (face-api)
  face_consent    BOOLEAN      NOT NULL DEFAULT FALSE,  -- POPIA: biometric consent
  is_active       BOOLEAN      NOT NULL DEFAULT TRUE,
  created_at      TIMESTAMPTZ  NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ  NOT NULL DEFAULT now()
);

DROP TRIGGER IF EXISTS trg_teachers_updated ON teachers;
CREATE TRIGGER trg_teachers_updated
  BEFORE UPDATE ON teachers
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

CREATE INDEX IF NOT EXISTS idx_teachers_active ON teachers(is_active);
CREATE INDEX IF NOT EXISTS idx_teachers_name   ON teachers(full_name);

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.teachers TO anon, authenticated;

ALTER TABLE teachers ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "teachers: read"   ON teachers;
DROP POLICY IF EXISTS "teachers: insert" ON teachers;
DROP POLICY IF EXISTS "teachers: update" ON teachers;
DROP POLICY IF EXISTS "teachers: delete" ON teachers;

CREATE POLICY "teachers: read"
  ON teachers FOR SELECT
  USING (is_active = TRUE OR auth.role() = 'authenticated');
CREATE POLICY "teachers: insert"
  ON teachers FOR INSERT WITH CHECK (TRUE);
CREATE POLICY "teachers: update"
  ON teachers FOR UPDATE USING (TRUE);
CREATE POLICY "teachers: delete"
  ON teachers FOR DELETE USING (TRUE);

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_publication_tables
                 WHERE pubname = 'supabase_realtime' AND tablename = 'teachers') THEN
    ALTER PUBLICATION supabase_realtime ADD TABLE teachers;
  END IF;
END $$;

-- ─────────────────────────────────────────────────────────────
-- 13. TEACHER ATTENDANCE  (face check-in / check-out events)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS teacher_attendance (
  id          UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
  teacher_id  UUID         REFERENCES teachers(id) ON DELETE SET NULL,
  full_name   TEXT         NOT NULL,
  status      TEXT         NOT NULL DEFAULT 'checked_in'
              CHECK (status IN ('checked_in','checked_out')),
  check_in    TIMESTAMPTZ  NOT NULL DEFAULT now(),
  check_out   TIMESTAMPTZ,
  created_at  TIMESTAMPTZ  NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_tatt_teacher ON teacher_attendance(teacher_id, check_in DESC);
CREATE INDEX IF NOT EXISTS idx_tatt_open    ON teacher_attendance(teacher_id) WHERE check_out IS NULL;

GRANT SELECT, INSERT, UPDATE ON TABLE public.teacher_attendance TO anon, authenticated;

ALTER TABLE teacher_attendance ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "tatt: read"   ON teacher_attendance;
DROP POLICY IF EXISTS "tatt: insert" ON teacher_attendance;
DROP POLICY IF EXISTS "tatt: update" ON teacher_attendance;
CREATE POLICY "tatt: read"   ON teacher_attendance FOR SELECT USING (TRUE);
CREATE POLICY "tatt: insert" ON teacher_attendance FOR INSERT WITH CHECK (TRUE);
CREATE POLICY "tatt: update" ON teacher_attendance FOR UPDATE USING (TRUE);

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_publication_tables
                 WHERE pubname = 'supabase_realtime' AND tablename = 'teacher_attendance') THEN
    ALTER PUBLICATION supabase_realtime ADD TABLE teacher_attendance;
  END IF;
END $$;

-- ─────────────────────────────────────────────────────────────
-- 14. FIRST ADMIN  (run AFTER creating a user in
--     Dashboard → Authentication → Users → Add User)
-- ─────────────────────────────────────────────────────────────
/*
UPDATE profiles
SET role = 'admin', is_active = TRUE
WHERE email = 'admin@transvalia.co.za';
*/

-- ─────────────────────────────────────────────────────────────
-- END — Transvalia Hoër Skool Access Control
-- ─────────────────────────────────────────────────────────────