-- Remote Desktop Application Schema
-- Creates profiles, machines, session_codes, and sessions tables with RLS

-- Profiles table (extends Supabase auth.users)
CREATE TABLE IF NOT EXISTS public.profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  full_name TEXT,
  company TEXT,
  role TEXT DEFAULT 'user',
  avatar_url TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "profiles_select_own" ON public.profiles;
CREATE POLICY "profiles_select_own" ON public.profiles 
  FOR SELECT USING (auth.uid() = id);

DROP POLICY IF EXISTS "profiles_insert_own" ON public.profiles;
CREATE POLICY "profiles_insert_own" ON public.profiles 
  FOR INSERT WITH CHECK (auth.uid() = id);

DROP POLICY IF EXISTS "profiles_update_own" ON public.profiles;
CREATE POLICY "profiles_update_own" ON public.profiles 
  FOR UPDATE USING (auth.uid() = id);

-- Machines table (registered remote machines)
CREATE TABLE IF NOT EXISTS public.machines (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  hostname TEXT,
  os TEXT,
  os_version TEXT,
  last_seen TIMESTAMPTZ,
  agent_version TEXT,
  is_online BOOLEAN DEFAULT FALSE,
  access_key TEXT UNIQUE DEFAULT encode(gen_random_bytes(32), 'hex'),
  ip_address TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE public.machines ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "machines_select_own" ON public.machines;
CREATE POLICY "machines_select_own" ON public.machines 
  FOR SELECT USING (auth.uid() = user_id);

DROP POLICY IF EXISTS "machines_insert_own" ON public.machines;
CREATE POLICY "machines_insert_own" ON public.machines 
  FOR INSERT WITH CHECK (auth.uid() = user_id);

DROP POLICY IF EXISTS "machines_update_own" ON public.machines;
CREATE POLICY "machines_update_own" ON public.machines 
  FOR UPDATE USING (auth.uid() = user_id);

DROP POLICY IF EXISTS "machines_delete_own" ON public.machines;
CREATE POLICY "machines_delete_own" ON public.machines 
  FOR DELETE USING (auth.uid() = user_id);

-- Session codes table (for quick connect)
CREATE TABLE IF NOT EXISTS public.session_codes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code TEXT UNIQUE NOT NULL,
  machine_id UUID REFERENCES public.machines(id) ON DELETE CASCADE,
  created_by UUID REFERENCES public.profiles(id) ON DELETE CASCADE,
  expires_at TIMESTAMPTZ NOT NULL,
  used_at TIMESTAMPTZ,
  used_by UUID REFERENCES public.profiles(id),
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE public.session_codes ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "session_codes_select_all" ON public.session_codes;
CREATE POLICY "session_codes_select_all" ON public.session_codes 
  FOR SELECT USING (TRUE);

DROP POLICY IF EXISTS "session_codes_insert_auth" ON public.session_codes;
CREATE POLICY "session_codes_insert_auth" ON public.session_codes 
  FOR INSERT WITH CHECK (auth.uid() IS NOT NULL);

DROP POLICY IF EXISTS "session_codes_update_auth" ON public.session_codes;
CREATE POLICY "session_codes_update_auth" ON public.session_codes 
  FOR UPDATE USING (auth.uid() IS NOT NULL);

-- Sessions table (connection history)
CREATE TABLE IF NOT EXISTS public.sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  machine_id UUID REFERENCES public.machines(id) ON DELETE SET NULL,
  machine_name TEXT,
  viewer_user_id UUID REFERENCES public.profiles(id) ON DELETE SET NULL,
  started_at TIMESTAMPTZ DEFAULT NOW(),
  ended_at TIMESTAMPTZ,
  duration_seconds INTEGER,
  connection_type TEXT,
  session_code TEXT,
  notes TEXT,
  quality_avg INTEGER,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE public.sessions ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "sessions_select_own" ON public.sessions;
CREATE POLICY "sessions_select_own" ON public.sessions 
  FOR SELECT USING (auth.uid() = viewer_user_id);

DROP POLICY IF EXISTS "sessions_insert_own" ON public.sessions;
CREATE POLICY "sessions_insert_own" ON public.sessions 
  FOR INSERT WITH CHECK (auth.uid() = viewer_user_id);

DROP POLICY IF EXISTS "sessions_update_own" ON public.sessions;
CREATE POLICY "sessions_update_own" ON public.sessions 
  FOR UPDATE USING (auth.uid() = viewer_user_id);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_machines_user_id ON public.machines(user_id);
CREATE INDEX IF NOT EXISTS idx_machines_is_online ON public.machines(is_online);
CREATE INDEX IF NOT EXISTS idx_session_codes_code ON public.session_codes(code);
CREATE INDEX IF NOT EXISTS idx_sessions_viewer ON public.sessions(viewer_user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_started ON public.sessions(started_at DESC);