-- SQL Schema to create the 'members' table and stats counter in Supabase
-- Paste this in your Supabase SQL Editor and click 'Run'

CREATE TABLE IF NOT EXISTS public.members (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    name TEXT NOT NULL,
    phone TEXT NOT NULL CONSTRAINT unique_phone UNIQUE,
    aadhaar TEXT NOT NULL CONSTRAINT unique_aadhaar UNIQUE,
    qualification TEXT,
    state TEXT NOT NULL DEFAULT 'Karnataka',
    district TEXT NOT NULL,
    taluk TEXT NOT NULL,
    village TEXT,
    photo_url TEXT,
    approved BOOLEAN DEFAULT FALSE NOT NULL,
    member_id TEXT
);

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

-- Create Policies
-- 1. Allow anyone to insert (sign up)
CREATE POLICY "Allow public inserts" 
ON public.members FOR INSERT 
WITH CHECK (true);

-- 2. Allow authenticated administrators to read, update, delete
CREATE POLICY "Allow admin full access" 
ON public.members FOR ALL 
TO authenticated 
USING (true) 
WITH CHECK (true);

-- 3. Allow public read access to verify membership status
CREATE POLICY "Allow public select" 
ON public.members FOR SELECT 
USING (true);

-- 3. Create page_stats table for visitor & click counts
CREATE TABLE IF NOT EXISTS public.page_stats (
    id TEXT PRIMARY KEY,
    count INT DEFAULT 0 NOT NULL
);

-- Seed initial counter records
INSERT INTO public.page_stats (id, count) VALUES ('visitors', 0) ON CONFLICT (id) DO NOTHING;
INSERT INTO public.page_stats (id, count) VALUES ('clicks', 0) ON CONFLICT (id) DO NOTHING;

-- Create RPC function to safely increment counters bypassing RLS
CREATE OR REPLACE FUNCTION increment_counter(counter_id TEXT)
RETURNS INT AS $$
DECLARE
  new_count INT;
BEGIN
  INSERT INTO public.page_stats (id, count)
  VALUES (counter_id, 1)
  ON CONFLICT (id)
  DO UPDATE SET count = public.page_stats.count + 1
  RETURNING count INTO new_count;
  RETURN new_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;