-- Monaco USA Portal 2026 - Initial Database Schema
-- Run this migration to set up all tables, views, triggers, and RLS policies

-- ============================================
-- MEMBERSHIP STATUSES (Admin-configurable)
-- ============================================
CREATE TABLE public.membership_statuses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  color TEXT NOT NULL DEFAULT '#6b7280',
  description TEXT,
  is_default BOOLEAN DEFAULT FALSE,
  sort_order INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Default statuses
INSERT INTO public.membership_statuses (name, display_name, color, description, is_default, sort_order) VALUES
  ('pending', 'Pending', '#eab308', 'New member, awaiting dues payment', true, 1),
  ('active', 'Active', '#22c55e', 'Dues paid, full access', false, 2),
  ('inactive', 'Inactive', '#6b7280', 'Lapsed membership or suspended', false, 3),
  ('expired', 'Expired', '#ef4444', 'Membership terminated', false, 4);

-- ============================================
-- MEMBERSHIP TYPES (Admin-configurable pricing)
-- ============================================
CREATE TABLE public.membership_types (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  annual_dues DECIMAL(10,2) NOT NULL,
  description TEXT,
  is_default BOOLEAN DEFAULT FALSE,
  is_active BOOLEAN DEFAULT TRUE,
  sort_order INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Default membership types
INSERT INTO public.membership_types (name, display_name, annual_dues, description, is_default, sort_order) VALUES
  ('regular', 'Regular Member', 50.00, 'Standard individual membership', true, 1),
  ('student', 'Student', 25.00, 'For students with valid ID', false, 2),
  ('senior', 'Senior (65+)', 35.00, 'For members 65 years and older', false, 3),
  ('family', 'Family', 75.00, 'Household membership', false, 4),
  ('honorary', 'Honorary Member', 0.00, 'Granted by the board', false, 5);

-- ============================================
-- MEMBERS TABLE
-- ============================================
CREATE TABLE public.members (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  member_id TEXT UNIQUE NOT NULL,

  -- Personal Info
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  phone TEXT NOT NULL,
  date_of_birth DATE NOT NULL,
  address TEXT NOT NULL,
  nationality TEXT[] NOT NULL DEFAULT '{}',

  -- Membership
  role TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('member', 'board', 'admin')),
  membership_status_id UUID REFERENCES public.membership_statuses(id),
  membership_type_id UUID REFERENCES public.membership_types(id),
  member_since DATE DEFAULT CURRENT_DATE,

  -- Profile
  avatar_url TEXT,

  -- Admin
  notes TEXT,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Auto-generate member_id trigger
CREATE OR REPLACE FUNCTION generate_member_id()
RETURNS TRIGGER AS $$
DECLARE
  next_num INTEGER;
BEGIN
  SELECT COALESCE(MAX(CAST(SUBSTRING(member_id FROM 6) AS INTEGER)), 0) + 1
  INTO next_num
  FROM public.members;

  NEW.member_id := 'MUSA-' || LPAD(next_num::TEXT, 4, '0');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_member_id
  BEFORE INSERT ON public.members
  FOR EACH ROW
  WHEN (NEW.member_id IS NULL)
  EXECUTE FUNCTION generate_member_id();

-- Update timestamp trigger
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER members_updated_at
  BEFORE UPDATE ON public.members
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

-- ============================================
-- DUES PAYMENTS
-- ============================================
CREATE TABLE public.dues_payments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  member_id UUID NOT NULL REFERENCES public.members(id) ON DELETE CASCADE,

  amount DECIMAL(10,2) NOT NULL,
  currency TEXT DEFAULT 'EUR',
  payment_date DATE NOT NULL,
  due_date DATE NOT NULL,
  payment_method TEXT DEFAULT 'bank_transfer',
  reference TEXT,
  notes TEXT,

  recorded_by UUID NOT NULL REFERENCES public.members(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Auto-calculate due_date (1 year from payment)
CREATE OR REPLACE FUNCTION calculate_due_date()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.due_date IS NULL THEN
    NEW.due_date := NEW.payment_date + INTERVAL '1 year';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_due_date
  BEFORE INSERT ON public.dues_payments
  FOR EACH ROW
  EXECUTE FUNCTION calculate_due_date();

-- Auto-update member status to active after payment
CREATE OR REPLACE FUNCTION update_member_status_on_payment()
RETURNS TRIGGER AS $$
DECLARE
  active_status_id UUID;
BEGIN
  SELECT id INTO active_status_id
  FROM public.membership_statuses
  WHERE name = 'active';

  UPDATE public.members
  SET membership_status_id = active_status_id,
      updated_at = NOW()
  WHERE id = NEW.member_id;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER activate_member_on_payment
  AFTER INSERT ON public.dues_payments
  FOR EACH ROW
  EXECUTE FUNCTION update_member_status_on_payment();

-- ============================================
-- EVENT TYPES (Admin-configurable)
-- ============================================
CREATE TABLE public.event_types (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  color TEXT NOT NULL DEFAULT '#3b82f6',
  icon TEXT,
  description TEXT,
  is_active BOOLEAN DEFAULT TRUE,
  sort_order INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Default event types
INSERT INTO public.event_types (name, display_name, color, icon, sort_order) VALUES
  ('social', 'Social Event', '#10b981', 'party-popper', 1),
  ('meeting', 'Meeting', '#6366f1', 'users', 2),
  ('fundraiser', 'Fundraiser', '#f59e0b', 'heart-handshake', 3),
  ('workshop', 'Workshop', '#8b5cf6', 'graduation-cap', 4),
  ('gala', 'Gala/Formal', '#ec4899', 'sparkles', 5),
  ('other', 'Other', '#6b7280', 'calendar', 6);

-- ============================================
-- EVENTS
-- ============================================
CREATE TABLE public.events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Basic Info
  title TEXT NOT NULL,
  description TEXT,
  event_type_id UUID REFERENCES public.event_types(id),

  -- Date/Time
  start_datetime TIMESTAMPTZ NOT NULL,
  end_datetime TIMESTAMPTZ NOT NULL,
  all_day BOOLEAN DEFAULT FALSE,
  timezone TEXT DEFAULT 'Europe/Monaco',

  -- Location
  location TEXT,
  location_url TEXT,

  -- Capacity
  max_attendees INTEGER,
  max_guests_per_member INTEGER DEFAULT 1,

  -- Pricing
  is_paid BOOLEAN DEFAULT FALSE,
  member_price DECIMAL(10,2) DEFAULT 0,
  non_member_price DECIMAL(10,2) DEFAULT 0,
  pricing_notes TEXT,

  -- Visibility
  visibility TEXT NOT NULL DEFAULT 'members'
    CHECK (visibility IN ('public', 'members', 'board', 'admin')),
  status TEXT NOT NULL DEFAULT 'published'
    CHECK (status IN ('draft', 'published', 'cancelled', 'completed')),

  -- Media
  cover_image_url TEXT,

  -- Meta
  created_by UUID NOT NULL REFERENCES public.members(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TRIGGER events_updated_at
  BEFORE UPDATE ON public.events
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

-- ============================================
-- EVENT RSVPs (Members)
-- ============================================
CREATE TABLE public.event_rsvps (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id UUID NOT NULL REFERENCES public.events(id) ON DELETE CASCADE,
  member_id UUID NOT NULL REFERENCES public.members(id) ON DELETE CASCADE,

  status TEXT NOT NULL DEFAULT 'confirmed'
    CHECK (status IN ('confirmed', 'declined', 'maybe', 'waitlist', 'cancelled')),
  guest_count INTEGER DEFAULT 0,
  guest_names TEXT[],
  notes TEXT,

  -- Payment
  payment_status TEXT DEFAULT 'not_required'
    CHECK (payment_status IN ('not_required', 'pending', 'paid')),
  payment_reference TEXT,
  payment_amount DECIMAL(10,2),

  -- Attendance
  attended BOOLEAN DEFAULT FALSE,
  checked_in_at TIMESTAMPTZ,
  checked_in_by UUID REFERENCES public.members(id),

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(event_id, member_id)
);

CREATE TRIGGER event_rsvps_updated_at
  BEFORE UPDATE ON public.event_rsvps
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

-- ============================================
-- EVENT RSVPs (Public/Non-members)
-- ============================================
CREATE TABLE public.event_rsvps_public (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id UUID NOT NULL REFERENCES public.events(id) ON DELETE CASCADE,

  full_name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT,

  status TEXT NOT NULL DEFAULT 'confirmed'
    CHECK (status IN ('confirmed', 'declined', 'maybe', 'waitlist', 'cancelled')),
  guest_count INTEGER DEFAULT 0,
  guest_names TEXT[],

  -- Payment
  payment_status TEXT DEFAULT 'not_required'
    CHECK (payment_status IN ('not_required', 'pending', 'paid')),
  payment_reference TEXT,
  payment_amount DECIMAL(10,2),

  -- Attendance
  attended BOOLEAN DEFAULT FALSE,

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(event_id, email)
);

CREATE TRIGGER event_rsvps_public_updated_at
  BEFORE UPDATE ON public.event_rsvps_public
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

-- ============================================
-- DOCUMENT CATEGORIES
-- ============================================
CREATE TABLE public.document_categories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  description TEXT,
  icon TEXT,
  sort_order INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Default categories
INSERT INTO public.document_categories (name, display_name, icon, sort_order) VALUES
  ('meeting_minutes', 'Meeting Minutes', 'file-text', 1),
  ('governance', 'Governance & Bylaws', 'scale', 2),
  ('legal', 'Legal Documents', 'briefcase', 3),
  ('financial', 'Financial Reports', 'dollar-sign', 4),
  ('member_resources', 'Member Resources', 'book-open', 5),
  ('forms', 'Forms & Templates', 'clipboard', 6),
  ('other', 'Other Documents', 'file', 7);

-- ============================================
-- DOCUMENTS
-- ============================================
CREATE TABLE public.documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  title TEXT NOT NULL,
  description TEXT,
  category_id UUID REFERENCES public.document_categories(id),

  -- File Info
  file_path TEXT NOT NULL,
  file_name TEXT NOT NULL,
  file_size INTEGER NOT NULL,
  mime_type TEXT NOT NULL,

  -- Visibility
  visibility TEXT NOT NULL DEFAULT 'members'
    CHECK (visibility IN ('public', 'members', 'board', 'admin')),
  allowed_member_ids UUID[],

  -- Version tracking
  version INTEGER DEFAULT 1,
  replaces_document_id UUID REFERENCES public.documents(id),

  -- Meeting-specific fields
  meeting_date DATE,
  meeting_attendees UUID[],

  -- Meta
  uploaded_by UUID NOT NULL REFERENCES public.members(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TRIGGER documents_updated_at
  BEFORE UPDATE ON public.documents
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

-- ============================================
-- APP SETTINGS (Unified key-value store)
-- ============================================
CREATE TABLE public.app_settings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  category TEXT NOT NULL,
  setting_key TEXT NOT NULL,
  setting_value JSONB NOT NULL,
  setting_type TEXT NOT NULL DEFAULT 'text'
    CHECK (setting_type IN ('text', 'number', 'boolean', 'json', 'array')),
  display_name TEXT NOT NULL,
  description TEXT,
  is_public BOOLEAN DEFAULT FALSE,
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  updated_by UUID REFERENCES public.members(id),

  UNIQUE(category, setting_key)
);

-- Default settings
INSERT INTO public.app_settings (category, setting_key, setting_value, setting_type, display_name, description, is_public) VALUES
  -- Organization
  ('organization', 'association_name', '"Monaco USA"', 'text', 'Association Name', 'Official name of the association', true),
  ('organization', 'tagline', '"Americans in Monaco"', 'text', 'Tagline', 'Association tagline shown on login', true),
  ('organization', 'contact_email', '"contact@monacousa.org"', 'text', 'Contact Email', 'Public contact email', true),
  ('organization', 'primary_color', '"#dc2626"', 'text', 'Primary Color', 'Brand primary color (hex)', true),

  -- Dues
  ('dues', 'payment_iban', '"MC58 1756 9000 0104 0050 1001 860"', 'text', 'Payment IBAN', 'Bank IBAN for dues', false),
  ('dues', 'payment_account_holder', '"ASSOCIATION MONACO USA"', 'text', 'Account Holder', 'Bank account holder name', false),
  ('dues', 'payment_bank_name', '"Credit Foncier de Monaco"', 'text', 'Bank Name', 'Name of the bank', false),
  ('dues', 'reminder_days_before', '[30, 7, 1]', 'array', 'Reminder Days', 'Days before due to send reminders', false),
  ('dues', 'grace_period_days', '30', 'number', 'Grace Period', 'Days after due before auto-inactive', false),
  ('dues', 'auto_inactive_enabled', 'true', 'boolean', 'Auto Inactive', 'Auto set inactive after grace period', false),

  -- System
  ('system', 'maintenance_mode', 'false', 'boolean', 'Maintenance Mode', 'Put portal in maintenance mode', false),
  ('system', 'max_upload_size_mb', '50', 'number', 'Max Upload Size', 'Maximum file upload size in MB', false);

-- ============================================
-- EMAIL TEMPLATES
-- ============================================
CREATE TABLE public.email_templates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  template_key TEXT UNIQUE NOT NULL,
  template_name TEXT NOT NULL,
  category TEXT NOT NULL,
  subject TEXT NOT NULL,
  body_html TEXT NOT NULL,
  body_text TEXT,
  is_active BOOLEAN DEFAULT TRUE,
  is_system BOOLEAN DEFAULT FALSE,
  variables_schema JSONB,
  preview_data JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  updated_by UUID REFERENCES public.members(id)
);

CREATE TRIGGER email_templates_updated_at
  BEFORE UPDATE ON public.email_templates
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

-- ============================================
-- EMAIL LOGS
-- ============================================
CREATE TABLE public.email_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  recipient_id UUID REFERENCES public.members(id),
  recipient_email TEXT NOT NULL,
  recipient_name TEXT,
  template_key TEXT,
  subject TEXT NOT NULL,
  email_type TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'queued'
    CHECK (status IN ('queued', 'sent', 'delivered', 'opened', 'clicked', 'bounced', 'failed')),
  provider TEXT,
  provider_message_id TEXT,
  opened_at TIMESTAMPTZ,
  clicked_at TIMESTAMPTZ,
  error_message TEXT,
  retry_count INTEGER DEFAULT 0,
  template_variables JSONB,
  sent_by UUID REFERENCES public.members(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  sent_at TIMESTAMPTZ,
  delivered_at TIMESTAMPTZ
);

-- ============================================
-- VIEWS
-- ============================================

-- Members with dues status
CREATE VIEW public.members_with_dues AS
SELECT
  m.*,
  ms.name as status_name,
  ms.display_name as status_display_name,
  ms.color as status_color,
  mt.display_name as membership_type_name,
  mt.annual_dues,
  dp.last_payment_date,
  dp.current_due_date,
  CASE
    WHEN dp.current_due_date IS NULL THEN 'never_paid'
    WHEN dp.current_due_date < CURRENT_DATE THEN 'overdue'
    WHEN dp.current_due_date < CURRENT_DATE + INTERVAL '30 days' THEN 'due_soon'
    ELSE 'current'
  END as dues_status,
  CASE
    WHEN dp.current_due_date < CURRENT_DATE
    THEN (CURRENT_DATE - dp.current_due_date)::INTEGER
    ELSE NULL
  END as days_overdue,
  CASE
    WHEN dp.current_due_date >= CURRENT_DATE
    THEN (dp.current_due_date - CURRENT_DATE)::INTEGER
    ELSE NULL
  END as days_until_due
FROM public.members m
LEFT JOIN public.membership_statuses ms ON m.membership_status_id = ms.id
LEFT JOIN public.membership_types mt ON m.membership_type_id = mt.id
LEFT JOIN LATERAL (
  SELECT
    payment_date as last_payment_date,
    due_date as current_due_date
  FROM public.dues_payments
  WHERE member_id = m.id
  ORDER BY due_date DESC
  LIMIT 1
) dp ON true;

-- Events with attendee counts
CREATE VIEW public.events_with_counts AS
SELECT
  e.*,
  et.display_name as event_type_name,
  et.color as event_type_color,
  et.icon as event_type_icon,
  COALESCE(member_rsvps.confirmed_count, 0) +
    COALESCE(member_rsvps.guest_count, 0) +
    COALESCE(public_rsvps.confirmed_count, 0) +
    COALESCE(public_rsvps.guest_count, 0) as total_attendees,
  COALESCE(member_rsvps.confirmed_count, 0) as member_count,
  COALESCE(public_rsvps.confirmed_count, 0) as non_member_count,
  COALESCE(member_rsvps.waitlist_count, 0) +
    COALESCE(public_rsvps.waitlist_count, 0) as waitlist_count,
  CASE
    WHEN e.max_attendees IS NULL THEN FALSE
    WHEN (COALESCE(member_rsvps.confirmed_count, 0) +
          COALESCE(member_rsvps.guest_count, 0) +
          COALESCE(public_rsvps.confirmed_count, 0) +
          COALESCE(public_rsvps.guest_count, 0)) >= e.max_attendees THEN TRUE
    ELSE FALSE
  END as is_full
FROM public.events e
LEFT JOIN public.event_types et ON e.event_type_id = et.id
LEFT JOIN LATERAL (
  SELECT
    COUNT(*) FILTER (WHERE status = 'confirmed') as confirmed_count,
    COALESCE(SUM(guest_count) FILTER (WHERE status = 'confirmed'), 0) as guest_count,
    COUNT(*) FILTER (WHERE status = 'waitlist') as waitlist_count
  FROM public.event_rsvps
  WHERE event_id = e.id
) member_rsvps ON true
LEFT JOIN LATERAL (
  SELECT
    COUNT(*) FILTER (WHERE status = 'confirmed') as confirmed_count,
    COALESCE(SUM(guest_count) FILTER (WHERE status = 'confirmed'), 0) as guest_count,
    COUNT(*) FILTER (WHERE status = 'waitlist') as waitlist_count
  FROM public.event_rsvps_public
  WHERE event_id = e.id
) public_rsvps ON true;

-- ============================================
-- ROW LEVEL SECURITY
-- ============================================

-- Enable RLS on all tables
ALTER TABLE public.members ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.dues_payments ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.events ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.event_rsvps ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.event_rsvps_public ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.app_settings ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.email_templates ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.email_logs ENABLE ROW LEVEL SECURITY;

-- MEMBERS POLICIES
CREATE POLICY "Members viewable by authenticated users"
  ON public.members FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Users can update own profile"
  ON public.members FOR UPDATE
  TO authenticated
  USING (auth.uid() = id);

CREATE POLICY "Admins can insert members"
  ON public.members FOR INSERT
  TO authenticated
  WITH CHECK (
    EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin')
    OR auth.uid() = id
  );

CREATE POLICY "Admins can delete members"
  ON public.members FOR DELETE
  TO authenticated
  USING (
    EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin')
  );

-- DUES PAYMENTS POLICIES
CREATE POLICY "Own payments viewable"
  ON public.dues_payments FOR SELECT
  TO authenticated
  USING (
    member_id = auth.uid()
    OR EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin'))
  );

CREATE POLICY "Board can record payments"
  ON public.dues_payments FOR INSERT
  TO authenticated
  WITH CHECK (
    EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin'))
  );

-- EVENTS POLICIES
CREATE POLICY "Events viewable based on visibility"
  ON public.events FOR SELECT
  TO authenticated
  USING (
    visibility = 'members'
    OR visibility = 'public'
    OR (visibility = 'board' AND EXISTS (
      SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin')
    ))
    OR (visibility = 'admin' AND EXISTS (
      SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin'
    ))
  );

CREATE POLICY "Public events viewable by anyone"
  ON public.events FOR SELECT
  TO anon
  USING (visibility = 'public' AND status = 'published');

CREATE POLICY "Board can manage events"
  ON public.events FOR ALL
  TO authenticated
  USING (
    EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin'))
  );

-- EVENT RSVPs POLICIES
CREATE POLICY "RSVPs viewable by member and board"
  ON public.event_rsvps FOR SELECT
  TO authenticated
  USING (
    member_id = auth.uid()
    OR EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin'))
  );

CREATE POLICY "Members can manage own RSVPs"
  ON public.event_rsvps FOR ALL
  TO authenticated
  USING (member_id = auth.uid())
  WITH CHECK (member_id = auth.uid());

CREATE POLICY "Board can manage all RSVPs"
  ON public.event_rsvps FOR ALL
  TO authenticated
  USING (
    EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin'))
  );

-- PUBLIC RSVPs POLICIES
CREATE POLICY "Public RSVPs viewable by board"
  ON public.event_rsvps_public FOR SELECT
  TO authenticated
  USING (
    EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin'))
  );

CREATE POLICY "Anyone can create public RSVP"
  ON public.event_rsvps_public FOR INSERT
  TO anon, authenticated
  WITH CHECK (true);

CREATE POLICY "Board can manage public RSVPs"
  ON public.event_rsvps_public FOR ALL
  TO authenticated
  USING (
    EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin'))
  );

-- DOCUMENTS POLICIES
CREATE POLICY "Documents viewable based on visibility"
  ON public.documents FOR SELECT
  TO authenticated
  USING (
    visibility = 'members'
    OR visibility = 'public'
    OR (visibility = 'board' AND EXISTS (
      SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin')
    ))
    OR (visibility = 'admin' AND EXISTS (
      SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin'
    ))
    OR (allowed_member_ids IS NOT NULL AND auth.uid() = ANY(allowed_member_ids))
  );

CREATE POLICY "Board can upload documents"
  ON public.documents FOR INSERT
  TO authenticated
  WITH CHECK (
    EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin'))
  );

CREATE POLICY "Admin can manage all documents"
  ON public.documents FOR ALL
  TO authenticated
  USING (
    EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin')
  );

-- APP SETTINGS POLICIES
CREATE POLICY "Public settings viewable by anyone"
  ON public.app_settings FOR SELECT
  USING (is_public = true);

CREATE POLICY "All settings viewable by admin"
  ON public.app_settings FOR SELECT
  TO authenticated
  USING (
    EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin')
  );

CREATE POLICY "Admin can manage settings"
  ON public.app_settings FOR ALL
  TO authenticated
  USING (
    EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin')
  );

-- EMAIL TEMPLATES POLICIES
CREATE POLICY "Admin can manage email templates"
  ON public.email_templates FOR ALL
  TO authenticated
  USING (
    EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin')
  );

-- EMAIL LOGS POLICIES
CREATE POLICY "Own email logs viewable"
  ON public.email_logs FOR SELECT
  TO authenticated
  USING (
    recipient_id = auth.uid()
    OR EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin')
  );

CREATE POLICY "Admin can manage email logs"
  ON public.email_logs FOR ALL
  TO authenticated
  USING (
    EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin')
  );

-- ============================================
-- INDEXES
-- ============================================
CREATE INDEX idx_members_email ON public.members(email);
CREATE INDEX idx_members_member_id ON public.members(member_id);
CREATE INDEX idx_members_role ON public.members(role);
CREATE INDEX idx_members_status ON public.members(membership_status_id);

CREATE INDEX idx_dues_payments_member ON public.dues_payments(member_id);
CREATE INDEX idx_dues_payments_date ON public.dues_payments(payment_date DESC);

CREATE INDEX idx_events_start ON public.events(start_datetime);
CREATE INDEX idx_events_visibility ON public.events(visibility);
CREATE INDEX idx_events_status ON public.events(status);

CREATE INDEX idx_event_rsvps_event ON public.event_rsvps(event_id);
CREATE INDEX idx_event_rsvps_member ON public.event_rsvps(member_id);

CREATE INDEX idx_documents_category ON public.documents(category_id);
CREATE INDEX idx_documents_visibility ON public.documents(visibility);

CREATE INDEX idx_app_settings_category ON public.app_settings(category, setting_key);

CREATE INDEX idx_email_logs_recipient ON public.email_logs(recipient_id);
CREATE INDEX idx_email_logs_status ON public.email_logs(status);
CREATE INDEX idx_email_logs_created ON public.email_logs(created_at DESC);