-- ═══════════════════════════════════════════
-- CONDUCTOR AI · SUPABASE SCHEMA
-- Run this entire block in SQL Editor
-- ═══════════════════════════════════════════

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_cron";

-- ─── FARE REPORTS ───
-- Stores crowdsourced fare data from users
CREATE TABLE fare_reports (
  id            uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
  city          text NOT NULL,
  from_area     text NOT NULL,
  to_area       text NOT NULL,
  transport_mode text NOT NULL,
  fare_amount   integer NOT NULL,
  time_slot     text,
  duration_label text,
  duration_mins integer,
  travel_date   date,
  note          text,
  user_id       uuid REFERENCES auth.users(id),
  username      text,
  verified      boolean DEFAULT false,
  created_at    timestamptz DEFAULT now()
);

-- ─── TRAFFIC UPDATES ───
-- AI-generated and crowdsourced traffic data
CREATE TABLE traffic_updates (
  id          uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
  city        text NOT NULL,
  level       text NOT NULL CHECK (level IN ('low', 'medium', 'high', 'severe')),
  alert       text,
  routes_affected text[],
  source      text DEFAULT 'ai',
  expires_at  timestamptz DEFAULT now() + INTERVAL '2 hours',
  created_at  timestamptz DEFAULT now()
);

-- ─── CITY ALERTS ───
-- Persistent alerts shown on homepage
CREATE TABLE city_alerts (
  id          uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
  city        text NOT NULL,
  type        text,
  severity    text DEFAULT 'info',
  title       text,
  body        text,
  active      boolean DEFAULT true,
  expires_at  timestamptz,
  created_at  timestamptz DEFAULT now()
);

-- ─── FEED POSTS ───
-- Community road updates
CREATE TABLE feed_posts (
  id          bigserial PRIMARY KEY,
  content     text NOT NULL,
  city        text NOT NULL,
  type        text DEFAULT 'general',
  username    text,
  user_id     uuid REFERENCES auth.users(id),
  likes       integer DEFAULT 0,
  moderated   boolean DEFAULT false,
  approved    boolean DEFAULT true,
  created_at  timestamptz DEFAULT now()
);

-- ─── POST LIKES ───
CREATE TABLE post_likes (
  user_id   uuid REFERENCES auth.users(id),
  post_id   bigint REFERENCES feed_posts(id) ON DELETE CASCADE,
  created_at timestamptz DEFAULT now(),
  PRIMARY KEY (user_id, post_id)
);

-- ─── FARE INDEX ───
-- Aggregated fare data per route (updated by AI agent)
CREATE TABLE fare_index (
  id              uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
  city            text NOT NULL,
  from_area       text NOT NULL,
  to_area         text NOT NULL,
  transport_mode  text NOT NULL,
  avg_fare        integer,
  min_fare        integer,
  max_fare        integer,
  sample_count    integer DEFAULT 0,
  trend           text DEFAULT 'same',
  last_updated    timestamptz DEFAULT now(),
  UNIQUE(city, from_area, to_area, transport_mode)
);

-- ─── USER PROFILES ───
CREATE TABLE user_profiles (
  id           uuid REFERENCES auth.users(id) PRIMARY KEY,
  username     text,
  home_city    text DEFAULT 'Lagos',
  points       integer DEFAULT 0,
  reports_count integer DEFAULT 0,
  streak_days  integer DEFAULT 0,
  last_active  date DEFAULT CURRENT_DATE,
  created_at   timestamptz DEFAULT now()
);

-- ─── CHAT LOGS ───
CREATE TABLE chat_logs (
  id         bigserial PRIMARY KEY,
  user_id    uuid REFERENCES auth.users(id),
  message    text,
  response   text,
  created_at timestamptz DEFAULT now()
);

-- ─── AGENT RUNS LOG ───
CREATE TABLE agent_runs (
  id          bigserial PRIMARY KEY,
  agent_name  text,
  model       text,
  tokens_used integer,
  status      text,
  output      jsonb,
  created_at  timestamptz DEFAULT now()
);

-- ─── USEFUL VIEWS ───
CREATE VIEW fare_summary AS
SELECT
  city,
  from_area,
  to_area,
  transport_mode,
  ROUND(AVG(fare_amount)) AS avg_fare,
  MIN(fare_amount) AS min_fare,
  MAX(fare_amount) AS max_fare,
  COUNT(*) AS sample_count
FROM fare_reports
WHERE created_at > now() - INTERVAL '30 days'
GROUP BY city, from_area, to_area, transport_mode
ORDER BY sample_count DESC;

-- Auto-create profile on signup
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.user_profiles (id, username)
  VALUES (NEW.id, SPLIT_PART(NEW.email, '@', 1));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

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

-- Award points on fare report
CREATE OR REPLACE FUNCTION award_report_points()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.user_id IS NOT NULL THEN
    UPDATE user_profiles
    SET
      points = points + 50,
      reports_count = reports_count + 1
    WHERE id = NEW.user_id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_fare_report_created
  AFTER INSERT ON fare_reports
  FOR EACH ROW EXECUTE award_report_points();

-- Indexes for performance
CREATE INDEX idx_fare_reports_city ON fare_reports(city);
CREATE INDEX idx_fare_reports_route ON fare_reports(from_area, to_area);
CREATE INDEX idx_traffic_city ON traffic_updates(city, created_at DESC);
CREATE INDEX idx_feed_city ON feed_posts(city, created_at DESC);

-- Enable RLS on all tables
ALTER TABLE fare_reports ENABLE ROW LEVEL SECURITY;
ALTER TABLE traffic_updates ENABLE ROW LEVEL SECURITY;
ALTER TABLE city_alerts ENABLE ROW LEVEL SECURITY;
ALTER TABLE feed_posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE post_likes ENABLE ROW LEVEL SECURITY;
ALTER TABLE fare_index ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE chat_logs ENABLE ROW LEVEL SECURITY;

-- fare_reports: anyone can read, authenticated users can insert
CREATE POLICY "Public read fare_reports" ON fare_reports
  FOR SELECT USING (true);
CREATE POLICY "Auth users insert fare_reports" ON fare_reports
  FOR INSERT WITH CHECK (auth.uid() IS NOT NULL OR submitted_by = 'anonymous');

-- traffic_updates: public read
CREATE POLICY "Public read traffic" ON traffic_updates
  FOR SELECT USING (true);

-- city_alerts: public read active alerts
CREATE POLICY "Public read alerts" ON city_alerts
  FOR SELECT USING (active = true);

-- feed_posts: public read approved, auth users insert
CREATE POLICY "Public read feed" ON feed_posts
  FOR SELECT USING (moderated = true AND removed = false AND flagged = false);
CREATE POLICY "Users insert feed" ON feed_posts
  FOR INSERT WITH CHECK (auth.uid() IS NOT NULL);
CREATE POLICY "Users update own feed" ON feed_posts
  FOR UPDATE USING (auth.uid() = user_id);

-- post_likes: users manage their own likes
CREATE POLICY "Anyone can view likes" ON post_likes
  FOR SELECT USING (true);
CREATE POLICY "Authenticated users can like" ON post_likes
  FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can unlike" ON post_likes
  FOR DELETE USING (auth.uid() = user_id);

-- fare_index: public read
CREATE POLICY "Public read fare index" ON fare_index
  FOR SELECT USING (true);

-- user_profiles: public read, users manage own
CREATE POLICY "Users can view all profiles" ON user_profiles
  FOR SELECT USING (true);
CREATE POLICY "Users can update own profile" ON user_profiles
  FOR UPDATE USING (auth.uid() = id);
CREATE POLICY "Users can insert own profile" ON user_profiles
  FOR INSERT WITH CHECK (auth.uid() = id);

-- chat_logs: users read own logs
CREATE POLICY "Users read own chats" ON chat_logs
  FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users insert own chats" ON chat_logs
  FOR INSERT WITH CHECK (auth.uid() = user_id);

ALTER PUBLICATION supabase_realtime ADD TABLE
  fare_reports, traffic_updates, feed_posts, city_alerts;

# Install via npm
npm install -g supabase

# Login to Supabase
supabase login

# Initialize in your project folder
mkdir conductor-ai && cd conductor-ai
supabase init

# Link to your remote project (get project-ref from dashboard URL)
supabase link --project-ref YOUR_PROJECT_REF

# Add your OpenRouter API key as a secret
supabase secrets set OPENROUTER_API_KEY=sk-or-v1-your-key-here

# Verify secrets are set
supabase secrets list

import { serve } from "https://deno.land/std@0.168.0/http/server.ts"
import { createClient } from "https://esm.sh/@supabase/supabase-js@2"

const OPENROUTER_URL = "https://openrouter.ai/api/v1/chat/completions"
const MODEL = "meta-llama/llama-3.1-8b-instruct:free"

serve(async (req) => {
  const supabase = createClient(
    Deno.env.get("SUPABASE_URL")!,
    Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!
  )

  const now = new Date()
  const hour = now.getUTCHours() + 1 // WAT = UTC+1
  const day = now.toLocaleDateString("en-NG", { weekday: "long" })

  const prompt = `
    Current time in Nigeria: ${hour}:00 WAT, ${day}.
    Generate realistic traffic updates for Lagos, Abuja, Port Harcourt, Kano, Ibadan.
    Use real Nigerian road knowledge. For example:
    - Morning rush (7-9am): Third Mainland, Oshodi, Ojota are always severe
    - Evening rush (4-7pm): Lekki, VI, Ikorodu are always severe
    - Friday afternoons: everywhere is worse
    - Weekends: lighter traffic overall

    Respond ONLY with this JSON (no other text):
    {
      "updates": [
        {
          "city": "Lagos",
          "level": "severe",
          "alert": "Third Mainland Bridge go-slow! Avoid until 9pm",
          "routes_affected": ["Third Mainland", "Ozumba Mbadiwe"]
        }
      ]
    }
  `

  try {
    const res = await fetch(OPENROUTER_URL, {
      method: "POST",
      headers: {
        "Authorization": `Bearer ${Deno.env.get("OPENROUTER_API_KEY")}`,
        "Content-Type": "application/json",
        "HTTP-Referer": "https://conductor-ai.ng",
        "X-Title": "Conductor AI"
      },
      body: JSON.stringify({
        model: MODEL,
        max_tokens: 600,
        messages: [{ role: "user", content: prompt }]
      })
    })

    const data = await res.json()
    const text = data.choices?.[0]?.message?.content || "{}"
    const clean = text.replace(/```json\n?|\n?```/g, "").trim()
    const parsed = JSON.parse(clean)

    // Delete old traffic data (keep last 2 hours)
    await supabase
      .from("traffic_updates")
      .delete()
      .lt("created_at", new Date(Date.now() - 2 * 60 * 60 * 1000).toISOString())

    // Insert new traffic updates
    const { error } = await supabase
      .from("traffic_updates")
      .insert(parsed.updates.map((u: any) => ({
        city: u.city,
        level: u.level,
        alert: u.alert,
        routes_affected: u.routes_affected,
        source: "ai"
      })))

    // Log the agent run
    await supabase.from("agent_runs").insert({
      agent_name: "traffic-agent",
      model: MODEL,
      tokens_used: data.usage?.total_tokens || 0,
      status: error ? "error" : "success",
      output: parsed
    })

    return new Response(
      JSON.stringify({ success: !error, updates: parsed.updates?.length }),
      { headers: { "Content-Type": "application/json" } }
    )
  } catch (err) {
    return new Response(
      JSON.stringify({ error: err.message }),
      { status: 500, headers: { "Content-Type": "application/json" } }
    )
  }
})

import { serve } from "https://deno.land/std@0.168.0/http/server.ts"
import { createClient } from "https://esm.sh/@supabase/supabase-js@2"

const MODEL = "google/gemma-2-9b-it:free"

serve(async () => {
  const supabase = createClient(
    Deno.env.get("SUPABASE_URL")!,
    Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!
  )

  // Get recent fare reports from the last 7 days
  const { data: reports } = await supabase
    .from("fare_reports")
    .select("city, from_area, to_area, transport_mode, fare_amount, time_slot")
    .gt("created_at", new Date(Date.now() - 7 * 24 * 60 * 60 * 1000).toISOString())
    .limit(100)

  if (!reports?.length) {
    return new Response(JSON.stringify({ message: "No recent reports" }))
  }

  const prompt = `
    You are a Nigerian transport fare analyst. Here are recent fare reports:
    ${JSON.stringify(reports)}

    Analyze these reports and identify:
    1. Routes where fares have changed significantly
    2. Which transport modes are most expensive right now
    3. Any surge patterns (rush hour, fuel scarcity, etc.)

    Respond ONLY with this JSON:
    {
      "fare_updates": [
        {
          "city": "Lagos",
          "from_area": "Lekki",
          "to_area": "Victoria Island",
          "transport_mode": "uber",
          "avg_fare": 3500,
          "min_fare": 2800,
          "max_fare": 5000,
          "trend": "up",
          "reason": "Surge due to rain"
        }
      ],
      "summary": "Brief summary of fare trends"
    }
  `

  const res = await fetch("https://openrouter.ai/api/v1/chat/completions", {
    method: "POST",
    headers: {
      "Authorization": `Bearer ${Deno.env.get("OPENROUTER_API_KEY")}`,
      "Content-Type": "application/json",
      "HTTP-Referer": "https://conductor-ai.ng"
    },
    body: JSON.stringify({ model: MODEL, max_tokens: 800, messages: [{ role: "user", content: prompt }] })
  })

  const data = await res.json()
  const text = data.choices?.[0]?.message?.content || "{}"
  const parsed = JSON.parse(text.replace(/```json\n?|\n?```/g, "").trim())

  // Upsert fare index (update or insert per route)
  for (const update of parsed.fare_updates || []) {
    await supabase.from("fare_index").upsert({
      city: update.city,
      from_area: update.from_area,
      to_area: update.to_area,
      transport_mode: update.transport_mode,
      avg_fare: update.avg_fare,
      min_fare: update.min_fare,
      max_fare: update.max_fare,
      trend: update.trend,
      last_updated: new Date().toISOString()
    }, { onConflict: "city,from_area,to_area,transport_mode" })
  }

  return new Response(
    JSON.stringify({ success: true, updated: parsed.fare_updates?.length }),
    { headers: { "Content-Type": "application/json" } }
  )
})

import { serve } from "https://deno.land/std@0.168.0/http/server.ts"
import { createClient } from "https://esm.sh/@supabase/supabase-js@2"

serve(async () => {
  const supabase = createClient(
    Deno.env.get("SUPABASE_URL")!,
    Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!
  )

  // Step 1: Moderate unmoderated feed posts
  const { data: posts } = await supabase
    .from("feed_posts")
    .select("id, content")
    .eq("moderated", false)
    .limit(10)

  if (posts?.length) {
    const modPrompt = `
      Moderate these Nigerian transport app community posts.
      Remove spam, scams, profanity, and off-topic content.
      Keep road updates, fare reports, and helpful info.

      Posts: ${JSON.stringify(posts.map(p => ({ id: p.id, text: p.content })))}

      Respond ONLY with JSON:
      {"results": [{"id": 1, "approve": true, "reason": "Valid traffic update"}]}
    `

    const modRes = await fetch("https://openrouter.ai/api/v1/chat/completions", {
      method: "POST",
      headers: {
        "Authorization": `Bearer ${Deno.env.get("OPENROUTER_API_KEY")}`,
        "Content-Type": "application/json"
      },
      body: JSON.stringify({
        model: "mistralai/mistral-7b-instruct:free",
        max_tokens: 400,
        messages: [{ role: "user", content: modPrompt }]
      })
    })

    const modData = await modRes.json()
    const modText = modData.choices?.[0]?.message?.content || "{}"
    const modResult = JSON.parse(modText.replace(/```json\n?|\n?```/g, "").trim())

    // Apply moderation decisions
    for (const result of modResult.results || []) {
      await supabase.from("feed_posts").update({
        approved: result.approve,
        moderated: true
      }).eq("id", result.id)
    }
  }

  // Step 2: Generate new city alerts
  const hour = new Date().getUTCHours() + 1
  const alertPrompt = `
    Generate 2-3 actionable road alerts for Nigerian cities at ${hour}:00 WAT.
    Be realistic based on Nigerian road patterns.

    Respond ONLY with JSON:
    {
      "alerts": [
        {
          "city": "Lagos",
          "type": "traffic",
          "severity": "warning",
          "title": "Oshodi Go-Slow",
          "body": "Heavy traffic on Oshodi-Apapa expressway. Use alternative routes.",
          "expires_hours": 3
        }
      ]
    }
  `

  const alertRes = await fetch("https://openrouter.ai/api/v1/chat/completions", {
    method: "POST",
    headers: {
      "Authorization": `Bearer ${Deno.env.get("OPENROUTER_API_KEY")}`,
      "Content-Type": "application/json"
    },
    body: JSON.stringify({
      model: "qwen/qwen-2-7b-instruct:free",
      max_tokens: 500,
      messages: [{ role: "user", content: alertPrompt }]
    })
  })

  const alertData = await alertRes.json()
  const alertText = alertData.choices?.[0]?.message?.content || "{}"
  const alertParsed = JSON.parse(alertText.replace(/```json\n?|\n?```/g, "").trim())

  // Deactivate old alerts
  await supabase.from("city_alerts")
    .update({ active: false })
    .lt("expires_at", new Date().toISOString())

  // Insert new alerts
  await supabase.from("city_alerts").insert(
    (alertParsed.alerts || []).map((a: any) => ({
      city: a.city,
      type: a.type,
      severity: a.severity,
      title: a.title,
      body: a.body,
      active: true,
      expires_at: new Date(Date.now() + (a.expires_hours || 2) * 3600000).toISOString()
    }))
  )

  return new Response(JSON.stringify({ success: true }), {
    headers: { "Content-Type": "application/json" }
  })
})

-- Traffic agent: every 30 minutes
SELECT cron.schedule(
  'traffic-agent-30min',
  '*/30 * * * *',
  $$
    SELECT net.http_post(
      url := current_setting('app.supabase_url') || '/functions/v1/traffic-agent',
      headers := jsonb_build_object(
        'X-CRON-SECRET', current_setting('app.cron_secret'),
        'Content-Type', 'application/json'
      ),
      body := '{}'::jsonb
    );
  $$
);

-- Fare agent: every 2 hours
SELECT cron.schedule(
  'fare-agent-2hr',
  '0 */2 * * *',
  $$
    SELECT net.http_post(
      url := current_setting('app.supabase_url') || '/functions/v1/fare-agent',
      headers := jsonb_build_object(
        'X-CRON-SECRET', current_setting('app.cron_secret'),
        'Content-Type', 'application/json'
      ),
      body := '{}'::jsonb
    );
  $$
);

-- Alert + moderation agent: every hour
SELECT cron.schedule(
  'alerts-agent-1hr',
  '0 * * * *',
  $$
    SELECT net.http_post(
      url := current_setting('app.supabase_url') || '/functions/v1/alerts-agent',
      headers := jsonb_build_object(
        'X-CRON-SECRET', current_setting('app.cron_secret'),
        'Content-Type', 'application/json'
      ),
      body := '{}'::jsonb
    );
  $$
);

-- Clean up old data: daily at 2am WAT (1am UTC)
SELECT cron.schedule(
  'daily-cleanup',
  '0 1 * * *',
  $$
    DELETE FROM traffic_updates WHERE created_at < now() - INTERVAL '24 hours';
    DELETE FROM city_alerts WHERE expires_at < now() - INTERVAL '1 hour';
    DELETE FROM chat_logs WHERE created_at < now() - INTERVAL '30 days';
  $$
);

-- View all scheduled jobs
SELECT * FROM cron.job;

# Deploy all Edge Functions at once
supabase functions deploy traffic-agent
supabase functions deploy fare-agent
supabase functions deploy alerts-agent
supabase functions deploy moderator-agent
supabase functions deploy content-agent
supabase functions deploy chat
supabase functions deploy admin-stats
supabase functions deploy admin-users
supabase functions deploy admin-fares
supabase functions deploy admin-content
supabase functions deploy admin-alerts
supabase functions deploy admin-feed

# Test a function manually
supabase functions invoke traffic-agent --no-verify-jwt

# Check function logs
supabase functions logs traffic-agent

# Set app URL and cron secret for pg_cron
# (Replace with your actual values)
ALTER DATABASE postgres SET app.supabase_url = 'https://xxxx.supabase.co';
ALTER DATABASE postgres SET app.cron_secret = 'your-long-random-secret';