blackcanyontickets/supabase/migrations/007_add_premium_addons.sql

-- Create premium add-ons system for BCT platform
-- This allows monetizing features like seating maps, AI descriptions, etc.

-- Create add_on_types table for available premium features
CREATE TABLE add_on_types (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  slug TEXT UNIQUE NOT NULL, -- e.g., 'seating-maps', 'ai-description'
  name TEXT NOT NULL,
  description TEXT NOT NULL,
  pricing_type VARCHAR(20) NOT NULL DEFAULT 'per_event', -- 'per_event', 'monthly', 'annual', 'per_ticket'
  price_cents INTEGER NOT NULL, -- Price in cents
  category VARCHAR(50) NOT NULL DEFAULT 'feature', -- 'feature', 'service', 'analytics', 'marketing'
  is_active BOOLEAN DEFAULT true,
  requires_setup BOOLEAN DEFAULT false, -- Whether add-on needs admin setup
  auto_enable_conditions JSONB, -- Conditions for auto-enabling (e.g., event size)
  feature_flags JSONB, -- What features this unlocks
  sort_order INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create event_add_ons table to track purchased add-ons per event
CREATE TABLE event_add_ons (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  event_id UUID REFERENCES events(id) ON DELETE CASCADE NOT NULL,
  add_on_type_id UUID REFERENCES add_on_types(id) NOT NULL,
  organization_id UUID REFERENCES organizations(id) NOT NULL,
  purchase_price_cents INTEGER NOT NULL, -- Price paid (may differ from current price)
  status VARCHAR(20) DEFAULT 'active', -- 'active', 'cancelled', 'expired'
  purchased_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  expires_at TIMESTAMP WITH TIME ZONE, -- For time-limited add-ons
  metadata JSONB, -- Add-on specific configuration
  stripe_payment_intent_id TEXT, -- Link to Stripe payment
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create organization_subscriptions for monthly/annual add-ons
CREATE TABLE organization_subscriptions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE NOT NULL,
  add_on_type_id UUID REFERENCES add_on_types(id) NOT NULL,
  status VARCHAR(20) DEFAULT 'active', -- 'active', 'cancelled', 'expired', 'past_due'
  current_period_start TIMESTAMP WITH TIME ZONE NOT NULL,
  current_period_end TIMESTAMP WITH TIME ZONE NOT NULL,
  stripe_subscription_id TEXT,
  stripe_customer_id TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Insert default premium add-ons
INSERT INTO add_on_types (slug, name, description, pricing_type, price_cents, category, requires_setup, feature_flags, sort_order) VALUES

-- Event Setup & Management (Automated Features - Low Cost)
('ai-event-description', 'AI Event Description', 'Professional AI-generated event descriptions optimized for your venue and audience', 'per_event', 500, 'service', false, '{"ai_description": true}', 1),
('premium-setup-service', 'Premium Setup Service', 'Dedicated onboarding specialist helps create and optimize your event', 'per_event', 5000, 'service', true, '{"priority_support": true, "setup_assistance": true}', 2),
('custom-event-branding', 'Custom Event Branding', 'Custom colors, styling, and logo integration for your ticket pages', 'per_event', 1000, 'feature', false, '{"custom_branding": true}', 3),

-- Advanced Features (Automated - Low Cost)
('seating-maps', 'Visual Seating Management', 'Interactive venue maps with seat selection, table assignments, and VIP sections', 'per_event', 1500, 'feature', false, '{"seating_maps": true, "seat_selection": true}', 4),
('guest-list-pro', 'Guest List Pro', 'Advanced attendee management with check-in app, VIP flagging, and notes', 'per_event', 1000, 'feature', false, '{"advanced_guest_management": true, "checkin_app": true}', 5),
('premium-analytics', 'Premium Analytics', 'Advanced sales forecasting, customer insights, and marketing performance tracking', 'per_event', 1000, 'analytics', false, '{"advanced_analytics": true, "forecasting": true, "demographics": true}', 6),
('ticket-scanner', 'Professional Ticket Scanner', 'Advanced QR code scanning with offline support, guest check-in tracking, and real-time reports', 'per_event', 500, 'feature', false, '{"ticket_scanner": true, "offline_scanning": true, "checkin_reports": true}', 7),

-- Marketing & Promotion (Mostly Automated - Moderate Cost)
('email-marketing-suite', 'Email Marketing Suite', 'Professional email templates, automated sequences, and post-event follow-up', 'per_event', 2000, 'marketing', false, '{"email_marketing": true, "automated_sequences": true}', 8),
('social-media-package', 'Social Media Package', 'Auto-generated posts, Instagram templates, and Facebook event integration', 'per_event', 1500, 'marketing', false, '{"social_media_tools": true, "auto_posts": true}', 9),

-- White-Glove Services
('concierge-management', 'Concierge Event Management', 'Dedicated event manager with day-of coordination and real-time support', 'per_event', 50000, 'service', true, '{"dedicated_manager": true, "day_of_support": true}', 10),
('premium-support', 'Premium Customer Support', 'Priority phone/chat support with dedicated account manager', 'per_event', 20000, 'service', false, '{"priority_support": true, "dedicated_manager": true}', 11),

-- Subscriptions
('bct-pro-monthly', 'BCT Pro Monthly', 'All premium features included for unlimited events', 'monthly', 19900, 'subscription', false, '{"all_features": true, "unlimited_events": true}', 12),
('enterprise-package', 'Enterprise Package', 'Multi-venue management, white-label options, and custom development', 'monthly', 99900, 'subscription', true, '{"multi_venue": true, "white_label": true, "api_access": true}', 13);

-- Function to check if organization has specific add-on for event
CREATE OR REPLACE FUNCTION has_event_addon(p_event_id UUID, p_addon_slug TEXT)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 
    FROM event_add_ons ea
    JOIN add_on_types at ON ea.add_on_type_id = at.id
    WHERE ea.event_id = p_event_id 
    AND at.slug = p_addon_slug 
    AND ea.status = 'active'
    AND (ea.expires_at IS NULL OR ea.expires_at > NOW())
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to check if organization has subscription add-on
CREATE OR REPLACE FUNCTION has_subscription_addon(p_organization_id UUID, p_addon_slug TEXT)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 
    FROM organization_subscriptions os
    JOIN add_on_types at ON os.add_on_type_id = at.id
    WHERE os.organization_id = p_organization_id 
    AND at.slug = p_addon_slug 
    AND os.status = 'active'
    AND os.current_period_end > NOW()
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to check if organization/event has any add-on with specific feature flag
CREATE OR REPLACE FUNCTION has_feature_access(p_organization_id UUID, p_event_id UUID, p_feature_flag TEXT)
RETURNS BOOLEAN AS $$
BEGIN
  -- Check subscription add-ons
  IF EXISTS (
    SELECT 1 
    FROM organization_subscriptions os
    JOIN add_on_types at ON os.add_on_type_id = at.id
    WHERE os.organization_id = p_organization_id 
    AND os.status = 'active'
    AND os.current_period_end > NOW()
    AND at.feature_flags ? p_feature_flag
  ) THEN
    RETURN TRUE;
  END IF;

  -- Check event-specific add-ons
  IF p_event_id IS NOT NULL AND EXISTS (
    SELECT 1 
    FROM event_add_ons ea
    JOIN add_on_types at ON ea.add_on_type_id = at.id
    WHERE ea.event_id = p_event_id 
    AND ea.status = 'active'
    AND (ea.expires_at IS NULL OR ea.expires_at > NOW())
    AND at.feature_flags ? p_feature_flag
  ) THEN
    RETURN TRUE;
  END IF;

  RETURN FALSE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to get available add-ons for organization/event
CREATE OR REPLACE FUNCTION get_available_addons(p_organization_id UUID, p_event_id UUID DEFAULT NULL)
RETURNS TABLE(
  addon_id UUID,
  slug TEXT,
  name TEXT,
  description TEXT,
  pricing_type TEXT,
  price_cents INTEGER,
  category TEXT,
  has_access BOOLEAN,
  purchased_at TIMESTAMP WITH TIME ZONE
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    at.id as addon_id,
    at.slug,
    at.name,
    at.description,
    at.pricing_type,
    at.price_cents,
    at.category,
    CASE 
      WHEN has_subscription_addon(p_organization_id, at.slug) THEN TRUE
      WHEN p_event_id IS NOT NULL AND has_event_addon(p_event_id, at.slug) THEN TRUE
      ELSE FALSE
    END as has_access,
    COALESCE(
      (SELECT ea.purchased_at FROM event_add_ons ea WHERE ea.event_id = p_event_id AND ea.add_on_type_id = at.id AND ea.status = 'active' LIMIT 1),
      (SELECT os.created_at FROM organization_subscriptions os WHERE os.organization_id = p_organization_id AND os.add_on_type_id = at.id AND os.status = 'active' LIMIT 1)
    ) as purchased_at
  FROM add_on_types at
  WHERE at.is_active = true
  ORDER BY at.sort_order, at.name;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Enable RLS
ALTER TABLE add_on_types ENABLE ROW LEVEL SECURITY;
ALTER TABLE event_add_ons ENABLE ROW LEVEL SECURITY;
ALTER TABLE organization_subscriptions ENABLE ROW LEVEL SECURITY;

-- RLS Policies for add_on_types (everyone can read active add-ons)
CREATE POLICY "Anyone can view active add-on types" ON add_on_types
  FOR SELECT USING (is_active = true);

-- Admins can manage add-on types
CREATE POLICY "Admins can manage add-on types" ON add_on_types
  FOR ALL USING (auth.uid() IN (SELECT id FROM users WHERE role = 'admin'));

-- RLS Policies for event_add_ons
CREATE POLICY "Users can view their organization's event add-ons" ON event_add_ons
  FOR SELECT USING (
    organization_id IN (
      SELECT organization_id FROM users WHERE id = auth.uid()
    ) OR auth.uid() IN (SELECT id FROM users WHERE role = 'admin')
  );

CREATE POLICY "Users can purchase add-ons for their events" ON event_add_ons
  FOR INSERT WITH CHECK (
    event_id IN (
      SELECT id FROM events WHERE created_by = auth.uid()
    )
  );

-- RLS Policies for organization_subscriptions  
CREATE POLICY "Users can view their organization's subscriptions" ON organization_subscriptions
  FOR SELECT USING (
    organization_id IN (
      SELECT organization_id FROM users WHERE id = auth.uid()
    ) OR auth.uid() IN (SELECT id FROM users WHERE role = 'admin')
  );

-- Indexes for performance
CREATE INDEX idx_add_on_types_slug ON add_on_types(slug);
CREATE INDEX idx_add_on_types_active ON add_on_types(is_active);
CREATE INDEX idx_add_on_types_category ON add_on_types(category);
CREATE INDEX idx_event_add_ons_event_id ON event_add_ons(event_id);
CREATE INDEX idx_event_add_ons_organization_id ON event_add_ons(organization_id);
CREATE INDEX idx_event_add_ons_status ON event_add_ons(status);
CREATE INDEX idx_organization_subscriptions_org_id ON organization_subscriptions(organization_id);
CREATE INDEX idx_organization_subscriptions_status ON organization_subscriptions(status);

-- Audit logging for add-on purchases
CREATE OR REPLACE FUNCTION log_addon_purchase()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_logs (user_id, action, resource_type, resource_id, new_values)
  VALUES (
    auth.uid(),
    'purchase',
    'event_add_on',
    NEW.id,
    row_to_json(NEW)
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER event_add_ons_audit_trigger
  AFTER INSERT ON event_add_ons
  FOR EACH ROW EXECUTE FUNCTION log_addon_purchase();

-- Add add-on revenue tracking to platform_stats view
DROP VIEW IF EXISTS platform_stats;
CREATE VIEW platform_stats AS
SELECT 
  (SELECT COUNT(*) FROM users WHERE role = 'organizer' AND is_active = true) as active_organizers,
  (SELECT COUNT(*) FROM users WHERE role = 'admin') as admin_users,
  (SELECT COUNT(*) FROM organizations) as total_organizations,
  (SELECT COUNT(*) FROM events) as total_events,
  (SELECT COUNT(*) FROM events WHERE start_time >= NOW()) as upcoming_events,
  (SELECT COUNT(*) FROM tickets) as total_tickets_sold,
  (SELECT COALESCE(SUM(price), 0) FROM tickets) as total_revenue,
  (SELECT COALESCE(SUM(platform_fee_charged), 0) FROM tickets) as total_platform_fees,
  (SELECT COALESCE(SUM(purchase_price_cents), 0) FROM event_add_ons WHERE status = 'active') as total_addon_revenue,
  (SELECT COUNT(*) FROM event_add_ons WHERE status = 'active') as active_addons,
  (SELECT COUNT(*) FROM organization_subscriptions WHERE status = 'active') as active_subscriptions,
  (SELECT COUNT(DISTINCT DATE(created_at)) FROM tickets WHERE created_at >= NOW() - INTERVAL '30 days') as active_days_last_30,
  (SELECT COUNT(*) FROM users WHERE created_at >= NOW() - INTERVAL '7 days') as new_users_last_7_days;

-- Comments for documentation
COMMENT ON TABLE add_on_types IS 'Available premium add-ons and their pricing';
COMMENT ON TABLE event_add_ons IS 'Purchased add-ons for specific events';
COMMENT ON TABLE organization_subscriptions IS 'Monthly/annual subscriptions for organizations';
COMMENT ON FUNCTION has_event_addon IS 'Check if event has specific add-on purchased';
COMMENT ON FUNCTION has_subscription_addon IS 'Check if organization has subscription add-on';
COMMENT ON FUNCTION has_feature_access IS 'Check if organization/event has access to feature flag';