-- Roles
CREATE TYPE public.app_role AS ENUM ('admin', 'user');

CREATE TABLE public.profiles (
  id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  nama text NOT NULL DEFAULT '',
  email text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE ON public.profiles TO authenticated;
GRANT ALL ON public.profiles TO service_role;
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

CREATE TABLE public.user_roles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role public.app_role NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (user_id, role)
);
GRANT SELECT ON public.user_roles TO authenticated;
GRANT ALL ON public.user_roles TO service_role;
ALTER TABLE public.user_roles ENABLE ROW LEVEL SECURITY;

CREATE OR REPLACE FUNCTION public.has_role(_user_id uuid, _role public.app_role)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.user_roles
    WHERE user_id = _user_id AND role = _role
  )
$$;

CREATE POLICY "Lihat profil sendiri atau admin" ON public.profiles
  FOR SELECT TO authenticated
  USING (id = auth.uid() OR public.has_role(auth.uid(), 'admin'));
CREATE POLICY "Ubah profil sendiri" ON public.profiles
  FOR UPDATE TO authenticated
  USING (id = auth.uid()) WITH CHECK (id = auth.uid());

CREATE POLICY "Lihat peran sendiri atau admin" ON public.user_roles
  FOR SELECT TO authenticated
  USING (user_id = auth.uid() OR public.has_role(auth.uid(), 'admin'));

-- updated_at trigger
CREATE OR REPLACE FUNCTION public.update_updated_at_column()
RETURNS TRIGGER
LANGUAGE plpgsql
SET search_path = public
AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER update_profiles_updated_at
  BEFORE UPDATE ON public.profiles
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

-- Auto profile + role on signup (first user becomes admin/owner)
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  jumlah_admin int;
BEGIN
  INSERT INTO public.profiles (id, nama, email)
  VALUES (
    NEW.id,
    COALESCE(NEW.raw_user_meta_data ->> 'nama', NEW.raw_user_meta_data ->> 'full_name', split_part(NEW.email, '@', 1)),
    NEW.email
  )
  ON CONFLICT (id) DO NOTHING;

  SELECT count(*) INTO jumlah_admin FROM public.user_roles WHERE role = 'admin';

  INSERT INTO public.user_roles (user_id, role)
  VALUES (NEW.id, CASE WHEN jumlah_admin = 0 THEN 'admin'::public.app_role ELSE 'user'::public.app_role END)
  ON CONFLICT DO NOTHING;

  RETURN NEW;
END;
$$;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- Transaksi: tambah pemilik + jenis kas
ALTER TABLE public.transaksi
  ADD COLUMN user_id uuid REFERENCES auth.users(id) ON DELETE SET NULL,
  ADD COLUMN kas text NOT NULL DEFAULT 'operasional';

UPDATE public.transaksi SET kas = 'operasional';

CREATE INDEX idx_transaksi_kas ON public.transaksi (kas);

-- Ganti policy publik dengan policy berbasis peran
DROP POLICY IF EXISTS "Semua bisa hapus transaksi" ON public.transaksi;
DROP POLICY IF EXISTS "Semua bisa lihat transaksi" ON public.transaksi;
DROP POLICY IF EXISTS "Semua bisa tambah transaksi" ON public.transaksi;
DROP POLICY IF EXISTS "Semua bisa ubah transaksi" ON public.transaksi;

REVOKE ALL ON public.transaksi FROM anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.transaksi TO authenticated;
GRANT ALL ON public.transaksi TO service_role;

CREATE POLICY "Lihat kas operasional atau admin lihat semua" ON public.transaksi
  FOR SELECT TO authenticated
  USING (kas = 'operasional' OR public.has_role(auth.uid(), 'admin'));

CREATE POLICY "Tambah transaksi sesuai peran" ON public.transaksi
  FOR INSERT TO authenticated
  WITH CHECK (
    user_id = auth.uid()
    AND (kas = 'operasional' OR public.has_role(auth.uid(), 'admin'))
  );

CREATE POLICY "Ubah transaksi sendiri atau admin" ON public.transaksi
  FOR UPDATE TO authenticated
  USING (user_id = auth.uid() OR public.has_role(auth.uid(), 'admin'))
  WITH CHECK (user_id = auth.uid() OR public.has_role(auth.uid(), 'admin'));

CREATE POLICY "Hapus transaksi sendiri atau admin" ON public.transaksi
  FOR DELETE TO authenticated
  USING (user_id = auth.uid() OR public.has_role(auth.uid(), 'admin'));

-- Storage bukti: hanya pengguna yang sudah masuk
DROP POLICY IF EXISTS "Semua bisa unggah bukti" ON storage.objects;
DROP POLICY IF EXISTS "Semua bisa lihat bukti" ON storage.objects;
DROP POLICY IF EXISTS "Semua bisa hapus bukti" ON storage.objects;

CREATE POLICY "Pengguna masuk bisa unggah bukti" ON storage.objects
  FOR INSERT TO authenticated WITH CHECK (bucket_id = 'bukti');
CREATE POLICY "Pengguna masuk bisa lihat bukti" ON storage.objects
  FOR SELECT TO authenticated USING (bucket_id = 'bukti');
CREATE POLICY "Pengguna masuk bisa hapus bukti" ON storage.objects
  FOR DELETE TO authenticated USING (bucket_id = 'bukti');