database-schema-fixes.sql

-- ===============================================
-- PRODUCTION READINESS: DATABASE SCHEMA FIXES
-- ===============================================
-- Run this SQL in Supabase SQL Editor to fix all database issues

-- 1. Create notification_templates table
CREATE TABLE IF NOT EXISTS public.notification_templates (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL UNIQUE,
  notification_type TEXT NOT NULL,
  channel TEXT NOT NULL CHECK (channel IN ('email', 'sms', 'push', 'in_app')),
  language TEXT DEFAULT 'en',
  subject TEXT,
  body_text TEXT NOT NULL,
  body_html TEXT,
  active BOOLEAN DEFAULT true,
  version INTEGER DEFAULT 1,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. Add missing columns to notifications table
ALTER TABLE public.notifications ADD COLUMN IF NOT EXISTS title TEXT;
ALTER TABLE public.notifications ADD COLUMN IF NOT EXISTS content JSONB NOT NULL DEFAULT '{}';
ALTER TABLE public.notifications ADD COLUMN IF NOT EXISTS channels TEXT[] DEFAULT ARRAY['email'];
ALTER TABLE public.notifications ADD COLUMN IF NOT EXISTS priority TEXT DEFAULT 'normal' CHECK (priority IN ('low', 'normal', 'high', 'critical'));
ALTER TABLE public.notifications ADD COLUMN IF NOT EXISTS scheduled_for TIMESTAMPTZ DEFAULT NOW();
ALTER TABLE public.notifications ADD COLUMN IF NOT EXISTS booking_id UUID;

-- 3. Create missing queue tables if they don't exist
CREATE TABLE IF NOT EXISTS public.notification_queue (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  message JSONB NOT NULL,
  priority INTEGER DEFAULT 1,
  scheduled_for TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  processed_at TIMESTAMPTZ,
  retry_count INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS public.critical_notification_queue (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  message JSONB NOT NULL,
  priority INTEGER DEFAULT 1,
  scheduled_for TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  processed_at TIMESTAMPTZ,
  retry_count INTEGER DEFAULT 0
);

-- 4. Create events table for event sourcing
CREATE TABLE IF NOT EXISTS public.events (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  type TEXT NOT NULL,
  payload JSONB NOT NULL,
  user_id UUID REFERENCES auth.users(id),
  booking_id UUID,
  source TEXT DEFAULT 'system',
  occurred_at TIMESTAMPTZ DEFAULT NOW(),
  processed_at TIMESTAMPTZ,
  status TEXT DEFAULT 'new' CHECK (status IN ('new', 'processing', 'processed', 'failed'))
);

-- 5. Create notification_deliveries table for tracking
CREATE TABLE IF NOT EXISTS public.notification_deliveries (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  notification_id UUID NOT NULL REFERENCES public.notifications(id) ON DELETE CASCADE,
  channel TEXT NOT NULL,
  provider TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('queued', 'sending', 'sent', 'delivered', 'failed', 'bounced')),
  attempt_count INTEGER DEFAULT 1,
  provider_response JSONB,
  error_message TEXT,
  sent_at TIMESTAMPTZ,
  delivered_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 6. Create duffel_webhook_events table
CREATE TABLE IF NOT EXISTS public.duffel_webhook_events (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  event_id TEXT NOT NULL UNIQUE,
  event_type TEXT NOT NULL,
  order_id TEXT,
  payload JSONB NOT NULL,
  processed BOOLEAN DEFAULT false,
  processing_error TEXT,
  received_at TIMESTAMPTZ DEFAULT NOW(),
  processed_at TIMESTAMPTZ
);

-- 7. Create performance indexes
CREATE INDEX IF NOT EXISTS idx_templates_type_channel ON public.notification_templates(notification_type, channel, active);
CREATE INDEX IF NOT EXISTS idx_notifications_priority ON public.notifications(priority);
CREATE INDEX IF NOT EXISTS idx_notifications_scheduled ON public.notifications(scheduled_for);
CREATE INDEX IF NOT EXISTS idx_notifications_user_unread ON public.notifications(user_id, is_read, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_deliveries_status ON public.notification_deliveries(status, created_at);
CREATE INDEX IF NOT EXISTS idx_deliveries_notification_id ON public.notification_deliveries(notification_id);
CREATE INDEX IF NOT EXISTS idx_events_type_occurred ON public.events(type, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_events_user_id ON public.events(user_id);
CREATE INDEX IF NOT EXISTS idx_queue_scheduled ON public.notification_queue(scheduled_for) WHERE processed_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_critical_queue_scheduled ON public.critical_notification_queue(scheduled_for) WHERE processed_at IS NULL;

-- 8. Insert default notification templates
INSERT INTO public.notification_templates (name, notification_type, channel, subject, body_text, body_html) VALUES
('booking_success_email', 'booking_success', 'email', '✈️ Your flight is booked!', 
 'Booking Confirmed! Your flight booking is confirmed. Thank you for booking with us!',
 '<h1>Booking Confirmed!</h1><p>Your flight booking is confirmed.</p><p><b>Details:</b></p><p><b>Origin:</b> {{origin}}</p><p><b>Destination:</b> {{destination}}</p><p><b>Date:</b> {{departure_date}}</p><p><b>Reference:</b> {{booking_reference}}</p><p>Thank you for booking with us!</p>'),

('booking_success_sms', 'booking_success', 'sms', null,
 '✈️ Flight booked! {{passenger_name}}, your flight from {{origin}} to {{destination}} on {{departure_date}} is confirmed. Ref: {{booking_reference}}', null),

('price_alert_email', 'price_alert', 'email', '💸 Price Drop Alert!',
 'Great news! The price for your watched flight has dropped.',
 '<h1>Price Drop Alert!</h1><p>Great news! The price for your watched flight has dropped.</p><p><b>Route:</b> {{origin}} → {{destination}}</p><p><b>New Price:</b> ${{new_price}} (was ${{old_price}})</p><p><b>Savings:</b> ${{savings}}</p><p><a href="{{booking_url}}">Book Now</a></p>'),

('price_alert_sms', 'price_alert', 'sms', null,
 '💸 Price Drop! Flight {{origin}} → {{destination}} now ${{new_price}} (was ${{old_price}}). Save ${{savings}}! Book: {{booking_url}}', null),

('booking_failure_email', 'booking_failure', 'email', '⚠️ Flight Booking Issue',
 'We encountered an issue with your flight booking attempt. Please contact support or try again.',
 '<h1>Booking Issue</h1><p>We encountered an issue with your flight booking attempt.</p><p><b>Details:</b> {{error}}</p><p>Please contact support or try again.</p>'),

('booking_failure_sms', 'booking_failure', 'sms', null,
 'Booking failed: {{error}}. Please try again or contact support.', null)
ON CONFLICT (name) DO NOTHING;

-- 9. Enable Row Level Security (RLS) on new tables
ALTER TABLE public.notification_templates ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.notification_deliveries ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.events ENABLE ROW LEVEL SECURITY;

-- 10. Create RLS policies for service role access
CREATE POLICY IF NOT EXISTS "Service role can manage all templates" ON public.notification_templates
  FOR ALL USING (auth.role() = 'service_role');

CREATE POLICY IF NOT EXISTS "Service role can manage all deliveries" ON public.notification_deliveries
  FOR ALL USING (auth.role() = 'service_role');

CREATE POLICY IF NOT EXISTS "Service role can manage all events" ON public.events
  FOR ALL USING (auth.role() = 'service_role');

-- 11. Grant permissions to authenticated users
GRANT SELECT ON public.notification_templates TO authenticated;
GRANT SELECT ON public.notification_deliveries TO authenticated;

-- ===============================================
-- SCHEMA FIXES COMPLETE
-- ===============================================
-- All tables, columns, indexes, and permissions are now set up
-- Your notification system is ready for production!