-- 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,
    payment_status TEXT DEFAULT 'pending',
    payment_id TEXT,
    amount_paid NUMERIC
);

-- 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);

-- Create app_settings table for dynamic toggles (payment on/off)
CREATE TABLE IF NOT EXISTS public.app_settings (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

-- Seed initial settings (payment enabled by default)
INSERT INTO public.app_settings (key, value) VALUES ('payment_enabled', 'true') ON CONFLICT (key) DO NOTHING;
INSERT INTO public.app_settings (key, value) VALUES ('membership_fee', '1000') ON CONFLICT (key) DO NOTHING;

-- Enable RLS on app_settings
ALTER TABLE public.app_settings ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow public read access to app_settings" ON public.app_settings FOR SELECT USING (true);
CREATE POLICY "Allow admin full access to app_settings" ON public.app_settings FOR ALL TO authenticated USING (true) WITH CHECK (true);

-- 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;

-- Create RPC function to safely reset counters bypassing RLS
CREATE OR REPLACE FUNCTION reset_page_stats()
RETURNS VOID AS $$
BEGIN
  UPDATE public.page_stats
  SET count = 0
  WHERE id IN ('visitors', 'clicks');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Enable RLS on page_stats and allow read/update access
ALTER TABLE public.page_stats ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow public read access to page_stats" ON public.page_stats FOR SELECT USING (true);
CREATE POLICY "Allow admin to update page_stats" ON public.page_stats FOR UPDATE TO authenticated USING (true) WITH CHECK (true);