-- Migration: Create public.users and public.settings tables
CREATE TABLE IF NOT EXISTS public.users (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL DEFAULT 'partner',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Enable RLS
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;

-- Policies for public.users
DROP POLICY IF EXISTS "Users can view their own role" ON public.users;
DROP POLICY IF EXISTS "Admins can view all roles" ON public.users;
CREATE POLICY "Users can view their own role" ON public.users FOR SELECT USING (auth.uid() = id);
CREATE POLICY "Admins can view all roles" ON public.users FOR SELECT USING (auth.role() = 'authenticated');
CREATE POLICY "Enable all for service_role" ON public.users FOR ALL USING (true) WITH CHECK (true);

-- Create public.settings table
CREATE TABLE IF NOT EXISTS public.settings (
  id TEXT PRIMARY KEY,
  host TEXT,
  port INTEGER,
  secure BOOLEAN,
  "user" TEXT,
  pass TEXT,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Enable RLS
ALTER TABLE public.settings ENABLE ROW LEVEL SECURITY;

-- Policies for public.settings
DROP POLICY IF EXISTS "Allow authenticated read on settings" ON public.settings;
DROP POLICY IF EXISTS "Allow authenticated write on settings" ON public.settings;
CREATE POLICY "Allow authenticated read on settings" ON public.settings FOR SELECT USING (auth.role() = 'authenticated');
CREATE POLICY "Allow authenticated write on settings" ON public.settings FOR ALL USING (auth.role() = 'authenticated') WITH CHECK (auth.role() = 'authenticated');

-- Update user creation trigger function to also insert into public.users
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id)
  VALUES (new.id)
  ON CONFLICT (id) DO NOTHING;
  
  INSERT INTO public.users (id, role)
  VALUES (new.id, CASE WHEN new.email = 'info@hephex.de' THEN 'admin' ELSE 'partner' END)
  ON CONFLICT (id) DO NOTHING;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Backfill public.users for existing auth.users
INSERT INTO public.users (id, role)
SELECT id, CASE WHEN email = 'info@hephex.de' THEN 'admin' ELSE 'partner' END
FROM auth.users
ON CONFLICT (id) DO NOTHING;

-- Reload Schema Cache
NOTIFY pgrst, 'reload schema';
