-- ============================================================
--  FALCON — Staff Members Login (Facial Recognition)
--  Supabase / PostgreSQL setup script (run once on a NEW project)
--
--  WHAT THIS CREATES
--    • staff_members      — enrolled staff + 128-d face descriptors
--    • staff_attendance   — face check-in / check-out events
--    • Storage bucket 'staff-photos' (+ public read / insert policies)
--    • touch_updated_at() — keeps updated_at current
--    • Row Level Security policies (anon access for the kiosk/enrolment POC)
--    • Realtime + indexes
--
--  HOW TO RUN
--    Supabase Dashboard → SQL Editor → paste this whole file → RUN.
--    Safe to re-run (idempotent).
--
--  SECURITY NOTE
--    anon read+write is enabled so the kiosk + enrolment page work without
--    a login (POC). Before production, move enrolment behind an admin login
--    and drop the anon INSERT/UPDATE/DELETE policies.
-- ============================================================

-- ─────────────────────────────────────────────────────────────
-- 0.  EXTENSIONS
-- ─────────────────────────────────────────────────────────────
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ─────────────────────────────────────────────────────────────
-- 1.  Helper: auto-update updated_at
-- ─────────────────────────────────────────────────────────────
CREATE OR REPLACE FUNCTION touch_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END; $$;

-- ─────────────────────────────────────────────────────────────
-- 2.  STAFF MEMBERS  (facial recognition — enrolled staff + descriptors)
--     Photos live in the 'staff-photos' bucket (folder 'staff/').
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS staff_members (
  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_staff_members_updated ON staff_members;
CREATE TRIGGER trg_staff_members_updated
  BEFORE UPDATE ON staff_members
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

CREATE INDEX IF NOT EXISTS idx_staff_members_active ON staff_members(is_active);
CREATE INDEX IF NOT EXISTS idx_staff_members_name   ON staff_members(full_name);

GRANT USAGE ON SCHEMA public TO anon, authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.staff_members TO anon, authenticated;

ALTER TABLE staff_members ENABLE ROW LEVEL SECURITY;

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

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

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

-- ─────────────────────────────────────────────────────────────
-- 3.  STAFF ATTENDANCE  (face check-in / check-out events)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS staff_attendance (
  id          UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
  staff_id    UUID         REFERENCES staff_members(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_satt_staff ON staff_attendance(staff_id, check_in DESC);
CREATE INDEX IF NOT EXISTS idx_satt_open  ON staff_attendance(staff_id) WHERE check_out IS NULL;

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

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

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

-- ─────────────────────────────────────────────────────────────
-- 4.  STORAGE BUCKET: staff photos
--     The enrolment page uploads a face photo and reads it back via
--     public URL (config.js → APP.photoBucket = 'staff-photos').
-- ─────────────────────────────────────────────────────────────
INSERT INTO storage.buckets (id, name, public)
VALUES ('staff-photos', 'staff-photos', TRUE)
ON CONFLICT (id) DO NOTHING;

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

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

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

-- ─────────────────────────────────────────────────────────────
-- END — Falcon Staff Members Login
-- ─────────────────────────────────────────────────────────────