CREATE POLICY "Allow all operations on members" ON members
  FOR ALL USING (true) WITH CHECK (true);

-- ============================================
-- SECURE RLS POLICIES FOR UDAAN
-- Run this AFTER your setup.sql
-- ============================================

-- First, enable RLS on all tables
ALTER TABLE members ENABLE ROW LEVEL SECURITY;
ALTER TABLE applicants ENABLE ROW LEVEL SECURITY;
ALTER TABLE registrations ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE announcements ENABLE ROW LEVEL SECURITY;
ALTER TABLE activity_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE division_members ENABLE ROW LEVEL SECURITY;

-- Drop existing overly-permissive policies
DROP POLICY IF EXISTS "Allow all operations on members" ON members;
DROP POLICY IF EXISTS "Allow all operations on applicants" ON applicants;

-- ============================================
-- MEMBERS TABLE POLICIES
-- ============================================

-- Public can only read non-sensitive member info (for team display)
CREATE POLICY "Public read non-sensitive member data" ON members
  FOR SELECT
  USING (
    status = 'approved' AND is_inactive = false
  );

-- Create a secure view for public member display (hides sensitive fields)
CREATE OR REPLACE VIEW public_members AS
SELECT 
  member_id,
  name,
  role,
  division,
  year,
  profile_pic,
  department
FROM members
WHERE status = 'approved' AND is_inactive = false;

-- Members can read their own full data
CREATE POLICY "Members read own data" ON members
  FOR SELECT
  USING (
    -- This requires you to pass member_id in request headers or use Supabase Auth
    member_id = current_setting('request.jwt.claims', true)::json->>'member_id'
  );

-- Only council (clearance >= 5) can modify members
CREATE POLICY "Council can manage members" ON members
  FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM members m 
      WHERE m.member_id = current_setting('request.jwt.claims', true)::json->>'member_id'
      AND m.clearance >= 5
    )
  );

-- ============================================
-- APPLICANTS TABLE POLICIES  
-- ============================================

-- Anyone can insert (submit application)
CREATE POLICY "Anyone can apply" ON applicants
  FOR INSERT
  WITH CHECK (true);

-- Only council can view/manage applicants
CREATE POLICY "Council can manage applicants" ON applicants
  FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM members m 
      WHERE m.member_id = current_setting('request.jwt.claims', true)::json->>'member_id'
      AND m.clearance >= 4
    )
  );

CREATE POLICY "Council can update applicants" ON applicants
  FOR UPDATE
  USING (
    EXISTS (
      SELECT 1 FROM members m 
      WHERE m.member_id = current_setting('request.jwt.claims', true)::json->>'member_id'
      AND m.clearance >= 4
    )
  );

-- ============================================
-- REGISTRATIONS TABLE POLICIES
-- ============================================

-- Anyone can register for events
CREATE POLICY "Anyone can register" ON registrations
  FOR INSERT
  WITH CHECK (true);

-- Only council can view registrations
CREATE POLICY "Council can view registrations" ON registrations
  FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM members m 
      WHERE m.member_id = current_setting('request.jwt.claims', true)::json->>'member_id'
      AND m.clearance >= 4
    )
  );

-- ============================================
-- TASKS TABLE POLICIES
-- ============================================

-- Members can only see their own tasks
CREATE POLICY "Members see own tasks" ON tasks
  FOR SELECT
  USING (
    assigned_to = current_setting('request.jwt.claims', true)::json->>'member_id'
    OR assigned_by = current_setting('request.jwt.claims', true)::json->>'member_id'
  );

-- Council can see all tasks
CREATE POLICY "Council sees all tasks" ON tasks
  FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM members m 
      WHERE m.member_id = current_setting('request.jwt.claims', true)::json->>'member_id'
      AND m.clearance >= 4
    )
  );

-- Secure login function that never exposes password hash
CREATE OR REPLACE FUNCTION secure_login(
  p_member_id TEXT,
  p_password TEXT
)
RETURNS JSON
LANGUAGE plpgsql
SECURITY DEFINER  -- Runs with elevated privileges
AS $$
DECLARE
  v_member RECORD;
  v_valid BOOLEAN;
BEGIN
  -- Get member (including password for verification)
  SELECT * INTO v_member
  FROM members
  WHERE member_id = p_member_id
  AND status = 'approved';
  
  IF NOT FOUND THEN
    RETURN json_build_object('success', false, 'error', 'Invalid credentials');
  END IF;
  
  -- Verify password using pgcrypto
  SELECT v_member.password = crypt(p_password, v_member.password) INTO v_valid;
  
  IF NOT v_valid THEN
    RETURN json_build_object('success', false, 'error', 'Invalid credentials');
  END IF;
  
  -- Return member data WITHOUT password
  RETURN json_build_object(
    'success', true,
    'member', json_build_object(
      'member_id', v_member.member_id,
      'name', v_member.name,
      'email', v_member.email,
      'role', v_member.role,
      'clearance', v_member.clearance,
      'division', v_member.division
    )
  );
END;
$$;

-- Grant execute to anon (but function controls what data is returned)
GRANT EXECUTE ON FUNCTION secure_login TO anon;