-- ENUMS
CREATE TYPE public.app_role AS ENUM ('admin','organizer','user');
CREATE TYPE public.event_status AS ENUM ('pending','approved','rejected','cancelled','expired');
CREATE TYPE public.verification_level AS ENUM ('none','needs_review','auto_verified','verified','organizer_verified');
CREATE TYPE public.attendance_status AS ENUM ('going','attended','did_not_attend','cancelled');

CREATE OR REPLACE FUNCTION public.set_updated_at() RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END; $$ LANGUAGE plpgsql SET search_path = public;

-- PLACES
CREATE TABLE public.countries (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL UNIQUE,
  code text NOT NULL UNIQUE,
  currency text,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE public.regions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  country_id uuid NOT NULL REFERENCES public.countries(id) ON DELETE CASCADE,
  name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (country_id, name)
);
CREATE TABLE public.cities (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  region_id uuid NOT NULL REFERENCES public.regions(id) ON DELETE CASCADE,
  country_id uuid NOT NULL REFERENCES public.countries(id) ON DELETE CASCADE,
  name text NOT NULL,
  timezone text NOT NULL DEFAULT 'Africa/Accra',
  latitude double precision,
  longitude double precision,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (region_id, name)
);
CREATE TABLE public.categories (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  slug text NOT NULL UNIQUE,
  name text NOT NULL,
  emoji text,
  active boolean NOT NULL DEFAULT true,
  sort_order int NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- USERS
CREATE TABLE public.profiles (
  id uuid PRIMARY KEY,
  full_name text,
  avatar_url text,
  bio text,
  country_id uuid REFERENCES public.countries(id) ON DELETE SET NULL,
  region_id uuid REFERENCES public.regions(id) ON DELETE SET NULL,
  city_id uuid REFERENCES public.cities(id) ON DELETE SET NULL,
  interests text[] NOT NULL DEFAULT '{}',
  activity_visibility text NOT NULL DEFAULT 'only_me',
  email_reminders boolean NOT NULL DEFAULT true,
  push_reminders boolean NOT NULL DEFAULT true,
  inapp_reminders boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE public.user_roles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  role public.app_role NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (user_id, role)
);
CREATE OR REPLACE FUNCTION public.has_role(_user_id uuid, _role public.app_role)
RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT EXISTS (SELECT 1 FROM public.user_roles WHERE user_id = _user_id AND role = _role);
$$;

CREATE OR REPLACE FUNCTION public.handle_new_user() RETURNS TRIGGER
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  INSERT INTO public.profiles (id, full_name, avatar_url)
  VALUES (NEW.id, COALESCE(NEW.raw_user_meta_data->>'full_name', NEW.raw_user_meta_data->>'name', split_part(NEW.email,'@',1)), NEW.raw_user_meta_data->>'avatar_url')
  ON CONFLICT (id) DO NOTHING;
  INSERT INTO public.user_roles (user_id, role) VALUES (NEW.id, 'user') ON CONFLICT DO NOTHING;
  RETURN NEW;
END; $$;
CREATE TRIGGER on_auth_user_created AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- ORGANIZERS
CREATE TABLE public.organizers (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id uuid,
  name text NOT NULL,
  logo_url text,
  description text,
  website text,
  contact_email text,
  verified boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

-- SOURCES
CREATE TABLE public.event_sources (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  website_url text,
  api_endpoint text,
  country_id uuid REFERENCES public.countries(id) ON DELETE SET NULL,
  active boolean NOT NULL DEFAULT true,
  trusted boolean NOT NULL DEFAULT false,
  reliability int NOT NULL DEFAULT 50,
  fetch_frequency_minutes int NOT NULL DEFAULT 720,
  last_success_at timestamptz,
  last_error_at timestamptz,
  last_error text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

-- EVENTS
CREATE TABLE public.events (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  slug text UNIQUE,
  title text NOT NULL,
  description text,
  category_id uuid REFERENCES public.categories(id) ON DELETE SET NULL,
  organizer_id uuid REFERENCES public.organizers(id) ON DELETE SET NULL,
  source_id uuid REFERENCES public.event_sources(id) ON DELETE SET NULL,
  submitted_by uuid,
  country_id uuid REFERENCES public.countries(id) ON DELETE SET NULL,
  region_id uuid REFERENCES public.regions(id) ON DELETE SET NULL,
  city_id uuid REFERENCES public.cities(id) ON DELETE SET NULL,
  venue text,
  address text,
  latitude double precision,
  longitude double precision,
  starts_at timestamptz NOT NULL,
  ends_at timestamptz,
  timezone text NOT NULL DEFAULT 'Africa/Accra',
  is_free boolean NOT NULL DEFAULT true,
  price_min numeric,
  price_max numeric,
  currency text DEFAULT 'GHS',
  ticket_url text,
  image_url text,
  source_url text,
  status public.event_status NOT NULL DEFAULT 'pending',
  verification public.verification_level NOT NULL DEFAULT 'none',
  confidence int NOT NULL DEFAULT 0,
  verified_at timestamptz,
  featured boolean NOT NULL DEFAULT false,
  view_count int NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX events_starts_at_idx ON public.events (starts_at);
CREATE INDEX events_city_idx ON public.events (city_id);
CREATE INDEX events_category_idx ON public.events (category_id);
CREATE INDEX events_status_idx ON public.events (status);
CREATE INDEX events_search_idx ON public.events USING gin (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(description,'') || ' ' || coalesce(venue,'')));

CREATE TABLE public.event_likes (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id uuid NOT NULL REFERENCES public.events(id) ON DELETE CASCADE,
  user_id uuid NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (event_id, user_id)
);
CREATE TABLE public.event_saves (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id uuid NOT NULL REFERENCES public.events(id) ON DELETE CASCADE,
  user_id uuid NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (event_id, user_id)
);
CREATE TABLE public.event_attendance (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id uuid NOT NULL REFERENCES public.events(id) ON DELETE CASCADE,
  user_id uuid NOT NULL,
  status public.attendance_status NOT NULL DEFAULT 'going',
  reminder_minutes_before int NOT NULL DEFAULT 1440,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (event_id, user_id)
);
CREATE TABLE public.event_ratings (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id uuid NOT NULL REFERENCES public.events(id) ON DELETE CASCADE,
  user_id uuid NOT NULL,
  rating int NOT NULL CHECK (rating BETWEEN 1 AND 5),
  review text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (event_id, user_id)
);

CREATE TABLE public.app_settings (
  key text PRIMARY KEY,
  value jsonb NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
);

-- TRIGGERS
CREATE TRIGGER t_profiles_updated BEFORE UPDATE ON public.profiles FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
CREATE TRIGGER t_events_updated BEFORE UPDATE ON public.events FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
CREATE TRIGGER t_attendance_updated BEFORE UPDATE ON public.event_attendance FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
CREATE TRIGGER t_ratings_updated BEFORE UPDATE ON public.event_ratings FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
CREATE TRIGGER t_organizers_updated BEFORE UPDATE ON public.organizers FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
CREATE TRIGGER t_sources_updated BEFORE UPDATE ON public.event_sources FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- GRANTS
GRANT SELECT ON public.countries, public.regions, public.cities, public.categories, public.events, public.organizers, public.event_ratings, public.app_settings TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.countries, public.regions, public.cities, public.categories, public.profiles, public.user_roles, public.organizers, public.event_sources, public.events, public.event_likes, public.event_saves, public.event_attendance, public.event_ratings, public.app_settings TO authenticated;
GRANT ALL ON public.countries, public.regions, public.cities, public.categories, public.profiles, public.user_roles, public.organizers, public.event_sources, public.events, public.event_likes, public.event_saves, public.event_attendance, public.event_ratings, public.app_settings TO service_role;

-- RLS
ALTER TABLE public.countries ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.regions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.cities ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.categories ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.user_roles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.organizers ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.event_sources ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.events ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.event_likes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.event_saves ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.event_attendance ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.event_ratings ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.app_settings ENABLE ROW LEVEL SECURITY;

CREATE POLICY "read countries" ON public.countries FOR SELECT USING (true);
CREATE POLICY "admin countries" ON public.countries FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));
CREATE POLICY "read regions" ON public.regions FOR SELECT USING (true);
CREATE POLICY "admin regions" ON public.regions FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));
CREATE POLICY "read cities" ON public.cities FOR SELECT USING (true);
CREATE POLICY "admin cities" ON public.cities FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));
CREATE POLICY "read categories" ON public.categories FOR SELECT USING (true);
CREATE POLICY "admin categories" ON public.categories FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));

CREATE POLICY "own profile read" ON public.profiles FOR SELECT TO authenticated USING (auth.uid() = id);
CREATE POLICY "own profile write" ON public.profiles FOR UPDATE TO authenticated USING (auth.uid() = id) WITH CHECK (auth.uid() = id);
CREATE POLICY "own profile insert" ON public.profiles FOR INSERT TO authenticated WITH CHECK (auth.uid() = id);
CREATE POLICY "admin profiles" ON public.profiles FOR SELECT TO authenticated USING (public.has_role(auth.uid(),'admin'));

CREATE POLICY "own roles read" ON public.user_roles FOR SELECT TO authenticated USING (auth.uid() = user_id OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "admin roles write" ON public.user_roles FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));

CREATE POLICY "read organizers" ON public.organizers FOR SELECT USING (true);
CREATE POLICY "own organizer" ON public.organizers FOR ALL TO authenticated USING (auth.uid() = owner_id OR public.has_role(auth.uid(),'admin')) WITH CHECK (auth.uid() = owner_id OR public.has_role(auth.uid(),'admin'));

CREATE POLICY "admin sources" ON public.event_sources FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));

CREATE POLICY "read approved events" ON public.events FOR SELECT USING (status IN ('approved','cancelled','expired'));
CREATE POLICY "read own submissions" ON public.events FOR SELECT TO authenticated USING (auth.uid() = submitted_by);
CREATE POLICY "submit events" ON public.events FOR INSERT TO authenticated WITH CHECK (auth.uid() = submitted_by);
CREATE POLICY "edit own submissions" ON public.events FOR UPDATE TO authenticated USING (auth.uid() = submitted_by AND status = 'pending') WITH CHECK (auth.uid() = submitted_by);
CREATE POLICY "admin events" ON public.events FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));

CREATE POLICY "own likes" ON public.event_likes FOR ALL TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY "own saves" ON public.event_saves FOR ALL TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY "own attendance" ON public.event_attendance FOR ALL TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY "read ratings" ON public.event_ratings FOR SELECT USING (true);
CREATE POLICY "own ratings" ON public.event_ratings FOR ALL TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

CREATE POLICY "read settings" ON public.app_settings FOR SELECT USING (true);
CREATE POLICY "admin settings" ON public.app_settings FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));

-- COUNTS (public aggregates, no identities)
CREATE OR REPLACE FUNCTION public.event_counts(_event_id uuid)
RETURNS TABLE (likes bigint, going bigint, ratings bigint, avg_rating numeric)
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT
    (SELECT count(*) FROM public.event_likes WHERE event_id = _event_id),
    (SELECT count(*) FROM public.event_attendance WHERE event_id = _event_id AND status IN ('going','attended')),
    (SELECT count(*) FROM public.event_ratings WHERE event_id = _event_id),
    (SELECT round(avg(rating)::numeric,1) FROM public.event_ratings WHERE event_id = _event_id);
$$;
GRANT EXECUTE ON FUNCTION public.event_counts(uuid) TO anon, authenticated;

-- SEED
INSERT INTO public.countries (name, code, currency) VALUES
 ('Ghana','GH','GHS'),('Nigeria','NG','NGN'),('Kenya','KE','KES'),('South Africa','ZA','ZAR');

INSERT INTO public.regions (country_id, name)
SELECT id,'Greater Accra' FROM public.countries WHERE code='GH'
UNION ALL SELECT id,'Ashanti' FROM public.countries WHERE code='GH'
UNION ALL SELECT id,'Lagos' FROM public.countries WHERE code='NG'
UNION ALL SELECT id,'Nairobi County' FROM public.countries WHERE code='KE'
UNION ALL SELECT id,'Gauteng' FROM public.countries WHERE code='ZA';

INSERT INTO public.cities (region_id, country_id, name, timezone, latitude, longitude)
SELECT r.id, r.country_id, 'Accra', 'Africa/Accra', 5.6037, -0.1870 FROM public.regions r WHERE r.name='Greater Accra'
UNION ALL SELECT r.id, r.country_id, 'Tema', 'Africa/Accra', 5.6698, -0.0166 FROM public.regions r WHERE r.name='Greater Accra'
UNION ALL SELECT r.id, r.country_id, 'Kumasi', 'Africa/Accra', 6.6885, -1.6244 FROM public.regions r WHERE r.name='Ashanti'
UNION ALL SELECT r.id, r.country_id, 'Lagos', 'Africa/Lagos', 6.5244, 3.3792 FROM public.regions r WHERE r.name='Lagos'
UNION ALL SELECT r.id, r.country_id, 'Nairobi', 'Africa/Nairobi', -1.2921, 36.8219 FROM public.regions r WHERE r.name='Nairobi County'
UNION ALL SELECT r.id, r.country_id, 'Johannesburg', 'Africa/Johannesburg', -26.2041, 28.0473 FROM public.regions r WHERE r.name='Gauteng';

INSERT INTO public.categories (slug,name,emoji,sort_order) VALUES
('music','Music','🎵',1),('concerts','Concerts','🎤',2),('festivals','Festivals','🎉',3),
('sports','Sports','🏅',4),('football','Football','⚽',5),('business','Business','💼',6),
('technology','Technology','💻',7),('conferences','Conferences','🎙️',8),('education','Education','📚',9),
('networking','Networking','🤝',10),('food','Food','🍲',11),('fashion','Fashion','👗',12),
('comedy','Comedy','😂',13),('religion','Religion','🙏',14),('family','Family','👨‍👩‍👧',15),
('arts','Arts','🎨',16),('culture','Culture','🥁',17),('nightlife','Nightlife','🌙',18),
('community','Community','🏘️',19),('career','Career','📈',20),('charity','Charity','💝',21),
('government','Government','🏛️',22),('health','Health','🩺',23),('other','Other','✨',24);

INSERT INTO public.organizers (name, description, website, verified) VALUES
('Accra Live Collective','Independent live music promoters in Greater Accra.','https://example.com/accra-live',true),
('Ghana Tech Network','Community of developers, designers and founders.','https://example.com/gtn',true),
('Naija Culture Works','Cultural and fashion experiences across Lagos.','https://example.com/ncw',false),
('East Africa Sports Union','Grassroots sport across East Africa.','https://example.com/easu',false);

INSERT INTO public.event_sources (name, website_url, active, trusted, reliability) VALUES
('Organizer RSS Feeds','https://example.com/feeds', true, true, 90),
('Public City Calendars','https://example.com/calendars', true, false, 60);

INSERT INTO public.app_settings (key, value) VALUES
('automation','{"auto_fetch":true,"auto_verification":true,"auto_approval":true,"min_confidence":75,"duplicate_detection":true,"continuous_verification":true,"auto_expire":true,"trusted_auto_publish":true,"min_ratings_to_display":3}'::jsonb);

INSERT INTO public.events (title, slug, description, category_id, organizer_id, country_id, region_id, city_id, venue, address, latitude, longitude, starts_at, ends_at, timezone, is_free, price_min, currency, ticket_url, image_url, source_url, status, verification, confidence, verified_at, featured)
SELECT v.title, v.slug, v.description, c.id, o.id, ci.country_id, ci.region_id, ci.id, v.venue, v.address, ci.latitude, ci.longitude,
       now() + (v.days || ' days')::interval, now() + (v.days || ' days')::interval + interval '4 hours', ci.timezone,
       v.is_free, v.price_min, v.currency, v.ticket_url, v.image_url, v.source_url, 'approved', v.verification::public.verification_level, v.confidence, now(), v.featured
FROM (VALUES
 ('Accra Music Festival','accra-music-festival','Three stages of highlife, afrobeats and alte across one night at the stadium.','music','Accra Live Collective','Accra','Accra Sports Stadium','Ring Road East, Accra',2,false,120,'GHS','https://example.com/tickets/amf','https://images.unsplash.com/photo-1470229722913-7ea0d7ea3d0f?w=1200&q=70','https://example.com/amf','verified',96,true),
 ('Live Music Night at Bloom Bar','live-music-night-bloom','Intimate live sets from Accra''s best emerging bands.','music','Accra Live Collective','Accra','Bloom Bar','Osu, Accra',1,true,NULL,'GHS',NULL,'https://images.unsplash.com/photo-1514525253161-7a46d19cd819?w=1200&q=70','https://example.com/bloom','auto_verified',82,false),
 ('Accra Tech Summit','accra-tech-summit','Founders, engineers and investors shaping Ghana''s tech decade.','technology','Ghana Tech Network','Accra','Kempinski Hotel','Gamel Abdul Nasser Ave, Accra',6,false,250,'GHS','https://example.com/tickets/ats','https://images.unsplash.com/photo-1540575467063-178a50c2df87?w=1200&q=70','https://example.com/ats','verified',94,true),
 ('Developer Meetup: Building for Africa','dev-meetup-building-africa','Monthly meetup for developers building African products.','networking','Ghana Tech Network','Accra','Impact Hub Accra','North Ridge, Accra',4,true,NULL,'GHS',NULL,'https://images.unsplash.com/photo-1515169067868-5387ec356754?w=1200&q=70','https://example.com/meetup','auto_verified',78,false),
 ('Accra Street Food Festival','accra-street-food-festival','Waakye, kelewele, jollof and chefs from across the city.','food','Accra Live Collective','Accra','Efua Sutherland Park','Ridge, Accra',3,false,40,'GHS','https://example.com/tickets/food','https://images.unsplash.com/photo-1504674900247-0877df9cc836?w=1200&q=70','https://example.com/food','verified',91,false),
 ('Sunday League Football Finals','sunday-league-finals','Community league finals with food stalls and live commentary.','football','East Africa Sports Union','Kumasi','Baba Yara Stadium','Kumasi',5,true,NULL,'GHS',NULL,'https://images.unsplash.com/photo-1431324155629-1a6deb1dec8d?w=1200&q=70','https://example.com/football','auto_verified',80,false),
 ('Kumasi Arts & Culture Fair','kumasi-arts-fair','Kente weaving, drumming and contemporary Ashanti art.','culture','Naija Culture Works','Kumasi','Centre for National Culture','Kumasi',9,false,30,'GHS','https://example.com/tickets/kaf','https://images.unsplash.com/photo-1528605248644-14dd04022da1?w=1200&q=70','https://example.com/kaf','auto_verified',77,false),
 ('Lagos Fashion Night','lagos-fashion-night','Runway showcase of West African designers.','fashion','Naija Culture Works','Lagos','Eko Hotel','Victoria Island, Lagos',7,false,15000,'NGN','https://example.com/tickets/lfn','https://images.unsplash.com/photo-1483985988355-763728e1935b?w=1200&q=70','https://example.com/lfn','verified',93,true),
 ('Lagos Comedy Live','lagos-comedy-live','A night of stand-up from Nigeria''s funniest.','comedy','Naija Culture Works','Lagos','Terra Kulture','Victoria Island, Lagos',11,false,8000,'NGN','https://example.com/tickets/lcl','https://images.unsplash.com/photo-1527224857830-43a7acc85260?w=1200&q=70','https://example.com/lcl','auto_verified',81,false),
 ('Nairobi Startup Mixer','nairobi-startup-mixer','Meet the builders behind Kenya''s fastest growing startups.','business','Ghana Tech Network','Nairobi','iHub','Senteu Plaza, Nairobi',8,true,NULL,'KES',NULL,'https://images.unsplash.com/photo-1511795409834-ef04bbd61622?w=1200&q=70','https://example.com/mixer','auto_verified',79,false),
 ('Nairobi Half Marathon','nairobi-half-marathon','21km through the city with 5km family fun run.','sports','East Africa Sports Union','Nairobi','Uhuru Gardens','Langata Rd, Nairobi',14,false,2500,'KES','https://example.com/tickets/nhm','https://images.unsplash.com/photo-1452626038306-9aae5e071dd3?w=1200&q=70','https://example.com/nhm','verified',90,false),
 ('Joburg Rooftop Sessions','joburg-rooftop-sessions','Deep house and amapiano as the sun goes down.','nightlife','Accra Live Collective','Johannesburg','Living Room Maboneng','Maboneng, Johannesburg',3,false,200,'ZAR','https://example.com/tickets/jrs','https://images.unsplash.com/photo-1566737236500-c8ac43014a67?w=1200&q=70','https://example.com/jrs','auto_verified',84,true),
 ('Community Health Screening Day','community-health-day','Free screening, wellness talks and family activities.','health','East Africa Sports Union','Accra','Accra Mall Forecourt','Spintex Rd, Accra',10,true,NULL,'GHS',NULL,'https://images.unsplash.com/photo-1576091160550-2173dba999ef?w=1200&q=70','https://example.com/health','auto_verified',76,false),
 ('Gospel Praise Concert','gospel-praise-concert','An evening of worship with leading Ghanaian gospel artists.','religion','Accra Live Collective','Accra','Fantasy Dome','Trade Fair, Accra',16,false,80,'GHS','https://example.com/tickets/gospel','https://images.unsplash.com/photo-1438232992991-995b7058bbb3?w=1200&q=70','https://example.com/gospel','verified',88,false),
 ('Tema Family Fun Day','tema-family-fun-day','Games, bouncy castles and food trucks for the whole family.','family','Accra Live Collective','Tema','Tema Community Park','Tema',12,true,NULL,'GHS',NULL,'https://images.unsplash.com/photo-1533174072545-7a4b6ad7a6c3?w=1200&q=70','https://example.com/tema','auto_verified',75,false)
) AS v(title,slug,description,cat,org,city,venue,address,days,is_free,price_min,currency,ticket_url,image_url,source_url,verification,confidence,featured)
JOIN public.categories c ON c.slug = v.cat
JOIN public.organizers o ON o.name = v.org
JOIN public.cities ci ON ci.name = v.city;

INSERT INTO public.events (title, slug, description, category_id, organizer_id, country_id, region_id, city_id, venue, address, starts_at, ends_at, timezone, is_free, image_url, source_url, status, verification, confidence)
SELECT 'Unverified Pop-Up Party','unverified-popup-party','Submitted by an unknown source, awaiting admin review.', c.id, NULL, ci.country_id, ci.region_id, ci.id, 'Unknown venue','Accra', now() + interval '5 days', now() + interval '5 days' + interval '3 hours', ci.timezone, true, NULL, 'https://example.com/unknown', 'pending','needs_review',34
FROM public.categories c, public.cities ci WHERE c.slug='nightlife' AND ci.name='Accra';