setup.sql

-- WoL Request Table Setup for WOLPU3H
-- Run this in Supabase SQL Editor

-- 1. Create the wol_requests table
CREATE TABLE IF NOT EXISTS public.wol_requests (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  target_mac text NOT NULL,                -- Target Machine MAC (e.g., AA:BB:CC:DD:EE:FF)
  status text DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'done')),
  device_id text,                          -- Optional: To target specific ESP32 devices
  created_at timestamp with time zone DEFAULT now(),
  processed_at timestamp with time zone
);

-- 2. Enable Row Level Security (RLS) for wol_requests
ALTER TABLE public.wol_requests ENABLE ROW LEVEL SECURITY;

-- 3. Create Policy for wol_requests (Allow All Access with Anon Key)
CREATE POLICY "Allow anon access" ON public.wol_requests
  FOR ALL
  USING (true)
  WITH CHECK (true);

-- 4. Create the Device Health table
CREATE TABLE IF NOT EXISTS public.device_health (
  device_id text PRIMARY KEY,
  last_seen timestamp with time zone DEFAULT now(),
  ip_address text,
  firmware_version text,
  free_heap int
);

-- 5. Enable RLS and Policies for device_health
ALTER TABLE public.device_health ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow anon access for device_health" ON public.device_health
  FOR ALL USING (true) WITH CHECK (true);

-- 6. Create a trigger to update last_seen on any update (Heartbeat)
CREATE OR REPLACE FUNCTION public.update_last_seen()
RETURNS TRIGGER AS $$
BEGIN
  NEW.last_seen = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_update_last_seen
  BEFORE UPDATE ON public.device_health
  FOR EACH ROW
  EXECUTE FUNCTION public.update_last_seen();

-- 7. Enable Realtime for Dashboard updates
ALTER PUBLICATION supabase_realtime ADD TABLE wol_requests;
ALTER PUBLICATION supabase_realtime ADD TABLE device_health;

-- 8. Create a View for status (Server-side online/offline calculation)
CREATE OR REPLACE VIEW public.device_status AS
SELECT 
    *,
    (last_seen > (now() - INTERVAL '3 minutes')) AS is_online
FROM public.device_health;

GRANT SELECT ON public.device_status TO anon;
GRANT SELECT ON public.device_status TO authenticated;

-- 9. Auto-Cleanup via pg_cron (Optional)
-- Note: 'pg_cron' extension must be enabled in the Dashboard: Database > Extensions
/*
select cron.schedule(
  'cleanup-old-requests', -- name of the cron job
  '0 3 * * *',            -- runs everyday at 3am
  $$
    DELETE FROM public.wol_requests
    WHERE status = 'done'
    AND created_at < now() - INTERVAL '30 days'
    AND id NOT IN (
        SELECT id FROM public.wol_requests
        ORDER BY created_at DESC
        LIMIT 10
    );
  $$
);
*/