-- ═══════════════════════════════════════════════════════
-- The Clover Club TX — Supabase Database Migration
-- ═══════════════════════════════════════════════════════

-- Enable UUID extension
create extension if not exists "uuid-ossp";

-- ── PROFILES ──
create table if not exists public.profiles (
  id uuid references auth.users on delete cascade primary key,
  role text not null default 'staff' check (role in ('super_admin', 'admin', 'staff')),
  full_name text,
  created_at timestamptz not null default now()
);

alter table public.profiles enable row level security;

create policy "Public profiles are viewable by everyone"
  on public.profiles for select using (true);

create policy "Users can update own profile"
  on public.profiles for update using (auth.uid() = id);

-- Auto-create profile on signup
create or replace function public.handle_new_user()
returns trigger as $$
begin
  insert into public.profiles (id, full_name)
  values (new.id, new.raw_user_meta_data->>'full_name');
  return new;
end;
$$ language plpgsql security definer;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

-- ── EVENTS ──
create table if not exists public.events (
  id uuid primary key default uuid_generate_v4(),
  title text not null,
  description text,
  category text not null default 'music' check (category in ('music', 'comedy', 'dj', 'art')),
  color text not null default 'green' check (color in ('green', 'blue', 'purple')),
  recurrence text not null default 'none' check (recurrence in ('none', 'daily', 'weekly', 'monthly')),
  recurrence_days int[],
  start_time time,
  doors_time time,
  show_time time,
  end_time time,
  ticket_url text,
  ticket_label text,
  image_url text,
  active boolean not null default true,
  created_at timestamptz not null default now()
);

alter table public.events enable row level security;

create policy "Events are viewable by everyone"
  on public.events for select using (true);

create policy "Admins can insert events"
  on public.events for insert with check (
    exists (select 1 from public.profiles where id = auth.uid() and role in ('super_admin', 'admin'))
  );

create policy "Admins can update events"
  on public.events for update using (
    exists (select 1 from public.profiles where id = auth.uid() and role in ('super_admin', 'admin'))
  );

create policy "Super admins can delete events"
  on public.events for delete using (
    exists (select 1 from public.profiles where id = auth.uid() and role = 'super_admin')
  );

-- ── SPOTLIGHT ──
create table if not exists public.spotlight (
  id uuid primary key default uuid_generate_v4(),
  event_id uuid references public.events on delete cascade not null,
  headline text,
  active boolean not null default true,
  updated_at timestamptz not null default now()
);

alter table public.spotlight enable row level security;

create policy "Spotlight is viewable by everyone"
  on public.spotlight for select using (true);

create policy "Admins can manage spotlight"
  on public.spotlight for all using (
    exists (select 1 from public.profiles where id = auth.uid() and role in ('super_admin', 'admin'))
  );

-- ── MAILING LIST ──
create table if not exists public.mailing_list (
  id uuid primary key default uuid_generate_v4(),
  email text unique not null,
  name text,
  source text,
  subscribed_at timestamptz not null default now()
);

alter table public.mailing_list enable row level security;

create policy "Admins can view mailing list"
  on public.mailing_list for select using (
    exists (select 1 from public.profiles where id = auth.uid() and role = 'super_admin')
  );

create policy "Anyone can subscribe"
  on public.mailing_list for insert with check (true);

-- ── CONTACT SUBMISSIONS ──
create table if not exists public.contact_submissions (
  id uuid primary key default uuid_generate_v4(),
  firstname text,
  lastname text,
  email text,
  phone text,
  inquiry_type text,
  message text,
  created_at timestamptz not null default now()
);

alter table public.contact_submissions enable row level security;

create policy "Admins can view contact submissions"
  on public.contact_submissions for select using (
    exists (select 1 from public.profiles where id = auth.uid() and role = 'super_admin')
  );

create policy "Anyone can submit contact form"
  on public.contact_submissions for insert with check (true);

-- ── SEED EVENTS ──
insert into public.events (title, description, category, color, recurrence, recurrence_days, start_time, doors_time, show_time, ticket_label, active) values
  ('Live Music Night', 'San Antonio''s best emerging talent takes the stage. From folk to hip-hop — every genre welcome. Come support local artists and enjoy a great night out.', 'music', 'green', 'weekly', '{5}', '19:00', '19:00', '20:00', 'Free Entry', true),
  ('Comedy Night', 'Live stand-up comedy every Wednesday. Local legends and touring acts bringing the laughs mid-week. Grab your favorites and get ready to laugh.', 'comedy', 'blue', 'weekly', '{3}', '19:00', '19:00', '20:00', 'Free Entry', true),
  ('After Hours', 'When the shows end, the party starts. House music, electronic sets, and nonstop energy deep into the night. The best late night spot in San Antonio.', 'dj', 'purple', 'daily', '{0,1,2,3,4,5,6}', '00:00', '00:00', '00:00', 'Free Entry', true),
  ('Puff & Paint', 'Unwind with a paintbrush in hand and good vibes in the air. Our monthly Puff & Paint nights bring together creativity, community, and a chill atmosphere.', 'art', 'green', 'monthly', '{}', '18:00', '17:30', '18:00', 'RSVP', true);
