-- ============================================
-- Vuelato: Sistema de cola de busquedas y seguimiento de precios
-- Tablas: search_cache, tracked_searches, search_runs, price_snapshots
-- ============================================

-- Cache de resultados de busqueda (publica, sin RLS)
-- TTL gestionado por el worker (limpia >2h)

CREATE TABLE IF NOT EXISTS search_cache (
  id SERIAL PRIMARY KEY,
  params_hash TEXT UNIQUE NOT NULL,
  search_params JSONB NOT NULL,
  trips JSONB NOT NULL DEFAULT '[]',
  cheapest_price NUMERIC(10,2),
  total_results INTEGER DEFAULT 0,
  fetched_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_search_cache_hash ON search_cache(params_hash);
CREATE INDEX IF NOT EXISTS idx_search_cache_fetched ON search_cache(fetched_at);

-- Busquedas recurrentes configuradas por el usuario

CREATE TABLE IF NOT EXISTS tracked_searches (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  search_params JSONB NOT NULL,
  route_summary TEXT NOT NULL,
  interval_hours INTEGER NOT NULL DEFAULT 24,
  is_active BOOLEAN DEFAULT true,
  next_run_at TIMESTAMPTZ DEFAULT now(),
  last_run_at TIMESTAMPTZ,
  run_count INTEGER DEFAULT 0,
  last_error TEXT,
  expires_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Log de cada ejecucion de una busqueda tracked

CREATE TABLE IF NOT EXISTS search_runs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tracked_search_id UUID NOT NULL REFERENCES tracked_searches(id) ON DELETE CASCADE,
  status TEXT NOT NULL DEFAULT 'pending',
  cheapest_price NUMERIC(10,2),
  total_trips_found INTEGER DEFAULT 0,
  top_trips JSONB,
  from_cache BOOLEAN DEFAULT false,
  error_message TEXT,
  started_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Snapshots de precio para graficos

CREATE TABLE IF NOT EXISTS price_snapshots (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tracked_search_id UUID NOT NULL REFERENCES tracked_searches(id) ON DELETE CASCADE,
  search_run_id UUID NOT NULL REFERENCES search_runs(id) ON DELETE CASCADE,
  cheapest_price NUMERIC(10,2) NOT NULL,
  avg_price NUMERIC(10,2),
  median_price NUMERIC(10,2),
  total_results INTEGER DEFAULT 0,
  recorded_at TIMESTAMPTZ DEFAULT now()
);

-- Indices

CREATE INDEX IF NOT EXISTS idx_tracked_searches_user ON tracked_searches(user_id);
CREATE INDEX IF NOT EXISTS idx_tracked_searches_next_run ON tracked_searches(next_run_at) WHERE is_active = true;
CREATE INDEX IF NOT EXISTS idx_search_runs_tracked ON search_runs(tracked_search_id);
CREATE INDEX IF NOT EXISTS idx_price_snapshots_tracked ON price_snapshots(tracked_search_id);
CREATE INDEX IF NOT EXISTS idx_price_snapshots_recorded ON price_snapshots(tracked_search_id, recorded_at);

-- RLS

ALTER TABLE tracked_searches ENABLE ROW LEVEL SECURITY;
ALTER TABLE search_runs ENABLE ROW LEVEL SECURITY;
ALTER TABLE price_snapshots ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users see own tracked_searches" ON tracked_searches
  FOR ALL USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users see own search_runs" ON search_runs
  FOR ALL USING (tracked_search_id IN (SELECT id FROM tracked_searches WHERE user_id = auth.uid()))
  WITH CHECK (tracked_search_id IN (SELECT id FROM tracked_searches WHERE user_id = auth.uid()));

CREATE POLICY "Users see own price_snapshots" ON price_snapshots
  FOR ALL USING (tracked_search_id IN (SELECT id FROM tracked_searches WHERE user_id = auth.uid()))
  WITH CHECK (tracked_search_id IN (SELECT id FROM tracked_searches WHERE user_id = auth.uid()));

-- Grants

GRANT SELECT ON search_cache TO anon, authenticated;
GRANT ALL ON search_cache TO service_role;
GRANT USAGE, SELECT ON SEQUENCE search_cache_id_seq TO service_role;

GRANT ALL ON tracked_searches, search_runs, price_snapshots TO authenticated;
GRANT ALL ON tracked_searches, search_runs, price_snapshots TO service_role;