-- ============================================
-- Vuelato: Schema completo
-- Se ejecuta despues de que la imagen supabase/postgres
-- haya creado los roles y el schema auth internamente.
-- ============================================

-- Tablas de cache (publicas)

CREATE TABLE IF NOT EXISTS airports (
  iata TEXT PRIMARY KEY,
  icao TEXT,
  name TEXT NOT NULL,
  lat DOUBLE PRECISION,
  lon DOUBLE PRECISION,
  city_id TEXT,
  city_name TEXT,
  country_code TEXT,
  country_name TEXT,
  region_slug TEXT,
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS countries (
  iso_code2 TEXT PRIMARY KEY,
  iso_code3 TEXT,
  name_eng TEXT,
  name_native TEXT,
  phone_prefix TEXT,
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS regions (
  slug TEXT PRIMARY KEY,
  name TEXT,
  name_localized TEXT,
  airport_codes TEXT[],
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS inspirations_cache (
  id SERIAL PRIMARY KEY,
  from_airport TEXT NOT NULL,
  to_airports TEXT[] NOT NULL,
  min_price NUMERIC(10,2),
  min_stops INTEGER,
  fetched_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(from_airport, to_airports)
);

CREATE TABLE IF NOT EXISTS multi_city_cache (
  id SERIAL PRIMARY KEY,
  origin_codes TEXT[] NOT NULL,
  from_airport TEXT,
  stops TEXT[] NOT NULL,
  min_price NUMERIC(10,2),
  fetched_at TIMESTAMPTZ DEFAULT now()
);

-- Tablas de usuario (con RLS)

CREATE TABLE IF NOT EXISTS profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  home_airports TEXT[] DEFAULT '{}',
  default_adults INTEGER DEFAULT 1,
  default_children INTEGER DEFAULT 0,
  default_infants INTEGER DEFAULT 0,
  locale TEXT DEFAULT 'es',
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS watchlist (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  booking_token TEXT NOT NULL,
  route_summary TEXT NOT NULL,
  departure_code TEXT,
  arrival_code TEXT,
  departure_date TEXT,
  original_price NUMERIC(10,2) NOT NULL,
  current_price NUMERIC(10,2),
  price_status TEXT DEFAULT 'saved',
  passengers_adult INTEGER DEFAULT 1,
  passengers_child INTEGER DEFAULT 0,
  passengers_infant INTEGER DEFAULT 0,
  last_checked_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS recent_searches (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  search_params JSONB NOT NULL,
  route_summary TEXT,
  search_mode TEXT DEFAULT 'roundtrip',
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS price_alerts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  watchlist_id UUID REFERENCES watchlist(id) ON DELETE CASCADE,
  target_price NUMERIC(10,2),
  is_active BOOLEAN DEFAULT true,
  last_notified_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Indices
CREATE INDEX IF NOT EXISTS idx_airports_country ON airports(country_code);
CREATE INDEX IF NOT EXISTS idx_airports_city ON airports(city_id);
CREATE INDEX IF NOT EXISTS idx_inspirations_from ON inspirations_cache(from_airport);
CREATE INDEX IF NOT EXISTS idx_watchlist_user ON watchlist(user_id);
CREATE INDEX IF NOT EXISTS idx_recent_searches_user ON recent_searches(user_id);
CREATE INDEX IF NOT EXISTS idx_price_alerts_user ON price_alerts(user_id);

-- RLS
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE watchlist ENABLE ROW LEVEL SECURITY;
ALTER TABLE recent_searches ENABLE ROW LEVEL SECURITY;
ALTER TABLE price_alerts ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users see own profile" ON profiles
  FOR ALL USING (auth.uid() = id);
CREATE POLICY "Users see own watchlist" ON watchlist
  FOR ALL USING (auth.uid() = user_id);
CREATE POLICY "Users see own searches" ON recent_searches
  FOR ALL USING (auth.uid() = user_id);
CREATE POLICY "Users see own alerts" ON price_alerts
  FOR ALL USING (auth.uid() = user_id);

-- Grants
GRANT USAGE ON SCHEMA public TO anon, authenticated;
GRANT SELECT ON airports, countries, regions, inspirations_cache, multi_city_cache
  TO anon, authenticated;
GRANT ALL ON profiles, watchlist, recent_searches, price_alerts
  TO authenticated;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public
  TO anon, authenticated;

-- Trigger: crear profile automaticamente al registrarse
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id) VALUES (NEW.id);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();