使用 Firebase SQL Connect (網頁版) 建構即時交易應用程式

git clone https://github.com/firebaseextended/codelab-dataconnect-web
cd codelab-dataconnect-web
git switch emoji-init
npm install

npm uninstall -g firebase-tools
npm install -g firebase-tools
firebase login
firebase use your-project-id
firebase init

firebase dataconnect:sdk:generate

const firebaseConfig = {
  apiKey: "API_KEY",
  authDomain: "PROJECT_ID.firebaseapp.com",
  projectId: "PROJECT_ID",
  storageBucket: "PROJECT_ID.firebasestorage.app",
  messagingSenderId: "SENDER_ID",
  appId: "APP_ID"
};

npm run dev

# Users
# user-stockOwnership is a one-to-many relationship, user-events is a one-to-many relationship
# Utilizes the Firebase Auth uid expression as the primary key
type User @table {
  id: String! @default(expr: "auth.uid")
  username: String!
  profileImage: String
  role: String! @default(value: "USER")
  points: Float! @default(value: 100.0)
  city: String @default(value: "Las Vegas")
  latitude: Float @default(value: 36.1699)
  longitude: Float @default(value: -115.1398)
}

# Emojis
# emoji-stockOwnership is a one-to-many relationship, emoji-priceHistory is a one-to-many relationship
# Implements @searchable directives for full-text search
type Emoji @table {
  id: UUID! @default(expr: "uuidV4()")
  symbol: String!
  name: String! @searchable
  tags: [String!]
  description: String! @searchable
  currentPrice: Float! @default(value: 10.0)
  trend: Float! @default(value: 0.0)
}

# Join table for many-to-many relationship between users and emojis
# The 'key' param signifies the primary key(s) of this table
# In this case, the keys are [user, emoji], the generated fields of the reference types
type StockOwnership @table(key: ["user", "emoji"]) {
  user: User!
  emoji: Emoji!
  shares: Int! @default(value: 0)
}

# Events
# Event-User is a many-to-one relationship, Event-Emoji is a many-to-one relationship
# Evaluates the createdAt timestamp purely on the server side using the request.time expression
type Event @table {
  id: UUID! @default(expr: "uuidV4()")
  user: User!
  emoji: Emoji!
  impact: Float!
  description: String!
  createdAt: Timestamp! @default(expr: "request.time")
}

# Price History
# PriceHistory-Emoji is a many-to-one relationship
type PriceHistory @table {
  id: UUID! @default(expr: "uuidV4()")
  emoji: Emoji!
  price: Float!
  recordedAt: Timestamp! @default(expr: "request.time")
}

# Get dashboard data including top emojis by price and recent market events
query GetDashboardData
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
) {
  emojis(orderBy: [{ currentPrice: DESC }]) {
    id
    symbol
    name
    description
    currentPrice
    trend
  }
  events(orderBy: [{ createdAt: DESC }], limit: 15) {
    id
    description
    impact
    createdAt
    user {
      username
      profileImage
    }
    emoji {
      symbol
    }
  }
}

# Get current authenticated user profile and their stock ownership using auth.uid
query GetUserProfile @auth(level: USER) {
  user(id_expr: "auth.uid") {
    points
    username
    profileImage
    role
    stockOwnerships_on_user {
      shares
      emoji {
        id
        symbol
        currentPrice
        name
      }
    }
    city
    latitude
    longitude
  }
}

# Get price history for a specific emoji ordered by time
query GetPriceHistory($emojiId: UUID!, $limit: Int)
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
) {
  priceHistories(
    where: { emojiId: { eq: $emojiId } }
    orderBy: [{ recordedAt: ASC }]
    limit: $limit
  ) {
    price
    recordedAt
  }
}

# Rank users on a leaderboard based on their total net worth
type TopTrader
@view(
  sql: """
  SELECT
    u.id,
    u.username,
    u.profile_image,
    (u.points + COALESCE(SUM(so.shares * e.current_price), 0)) AS net_worth,
    RANK() OVER (ORDER BY (u.points + COALESCE(SUM(so.shares * e.current_price), 0)) DESC) AS rank
  FROM "user" u
  LEFT JOIN stock_ownership so ON u.id = so.user_id
  LEFT JOIN emoji e ON so.emoji_id = e.id
  WHERE u.id != 'system_market_maker'
  GROUP BY u.id, u.username, u.profile_image, u.points
  """
) {
  id: String
  username: String
  profileImage: String
  netWorth: Float
  rank: Int
}

# Identify the top shareholder (whale) for each emoji and their total ownership percentage
type EmojiWhaleStat
  @view(
    sql: """
    WITH total_shares AS (
      SELECT emoji_id, SUM(shares) AS total_supply
      FROM stock_ownership WHERE shares > 0 GROUP BY emoji_id
    ),
    ranked_holders AS (
      SELECT
        so.emoji_id, u.username AS whale_username, u.profile_image AS whale_profile_image,
        so.shares AS whale_shares, ts.total_supply,
        ROUND((so.shares::DECIMAL / NULLIF(ts.total_supply, 0)) * 100, 2) AS whale_percentage,
        RANK() OVER (PARTITION BY so.emoji_id ORDER BY so.shares DESC) AS holder_rank
      FROM stock_ownership so
      JOIN "user" u ON u.id = so.user_id
      JOIN total_shares ts ON ts.emoji_id = so.emoji_id
      WHERE so.shares > 0
    )
    SELECT emoji_id, whale_username, whale_profile_image, whale_shares, total_supply, whale_percentage
    FROM ranked_holders WHERE holder_rank = 1
    """
  ) {
  emojiId: UUID
  whaleUsername: String
  whaleProfileImage: String
  whaleShares: Int
  totalSupply: Int
  whalePercentage: Float
}

# Calculate the moving average of historical prices for each emoji
type EmojiHistoryStat
  @view(
    sql: """
    SELECT
      emoji_id, price, recorded_at,
      AVG(price) OVER (PARTITION BY emoji_id ORDER BY recorded_at ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) as moving_average
    FROM price_history
    """
  ) {
  emojiId: UUID
  price: Float
  recordedAt: Timestamp
  movingAverage: Float
}

# Combine recent price updates and major news events into a single chronological feed
type TickerFeed
  @view(
    sql: """
    WITH latest_prices AS (
      SELECT emoji_id, MAX(recorded_at) as last_trade_time
      FROM price_history GROUP BY emoji_id
    )
    SELECT
      'PRICE' as type, e.symbol, e.name, e.current_price, e.trend,
      '' as description, lp.last_trade_time as event_time
    FROM emoji e JOIN latest_prices lp ON e.id = lp.emoji_id
    UNION ALL
    SELECT
      'NEWS' as type, e.symbol, '' as name, 0 as current_price, 0 as trend,
      ev.description, ev.created_at as event_time
    FROM event ev JOIN emoji e ON ev.emoji_id = e.id
    """
  ) {
  type: String
  symbol: String
  name: String
  currentPrice: Float
  trend: Float
  description: String
  eventTime: Timestamp
}

# Retrieve the 15 most recent price points for each emoji to render sparkline charts
type EmojiSparkline
  @view(
    sql: """
    WITH RankedPrices AS (
      SELECT
        emoji_id, price, recorded_at,
        ROW_NUMBER() OVER(PARTITION BY emoji_id ORDER BY recorded_at DESC) as rn
      FROM price_history
    )
    SELECT emoji_id, price, recorded_at
    FROM RankedPrices WHERE rn <= 15 ORDER BY recorded_at ASC
    """
  ) {
  emojiId: UUID
  price: Float
  recordedAt: Timestamp
}

# Get emoji whale statistics to identify top shareholders from emojiWhaleStats view
query GetEmojiWhaleStats
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
) {
  emojiWhaleStats {
    emojiId
    whaleUsername
    whaleProfileImage
    whaleShares
    totalSupply
    whalePercentage
  }
}

# Get historical price and moving average stats for a specific emoji from emojiHistoryStats view
query GetEmojiHistoryStats($emojiId: UUID!)
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
) {
  emojiHistoryStats(
    where: { emojiId: { eq: $emojiId } }
    orderBy: [{ recordedAt: ASC }]
    limit: 50
  ) {
    price
    movingAverage
    recordedAt
  }
}

# List top traders ordered by rank from topTraders view
query GetTopTraders
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
) {
  topTraders(orderBy: [{ rank: ASC }]) {
    id
    username
    profileImage
    netWorth
    rank
  }
}

# Get chronological market ticker feed of recent events from tickerFeeds view
query GetChronologicalTicker
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
) {
  tickerFeeds(orderBy: [{ eventTime: DESC }], limit: 30) {
    type
    symbol
    name
    currentPrice
    trend
    description
    eventTime
  }
}

# Get simple price points for rendering emoji sparkline charts from emojiSparklines view
query GetEmojiSparklines
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
) {
  emojiSparklines {
    emojiId
    price
    recordedAt
  }
}

# Search emojis using full-text search query
query SearchEmojis($query: String)
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
) {
  emojis_search(query: $query) {
    id
    symbol
    name
    description
    currentPrice
    trend
  }
}

firebase dataconnect:sdk:generate

import { subscribe } from "@firebase/data-connect";
import {
  getDashboardDataRef,
  searchEmojisRef,
  getChronologicalTickerRef,
  getUserProfileRef,
} from "@dataconnect/generated";

// Inside the Home component:
  useEffect(() => {
    // Subscribe to realtime updates for the main market dashboard data including top emojis and recent events
    const unsubscribe = subscribe(
      getDashboardDataRef(),
      (res) => {
        if (res.data) setDashboardData(res.data);
        setIsDashboardLoading(false);
      },
      (err) => {
        console.error("Dashboard Realtime Error:", err);
        setIsDashboardLoading(false);
      },
    );
    return () => unsubscribe();
  }, [user]);

  useEffect(() => {
    // Subscribe to a realtime chronological ticker feed combining recent price updates and major news events
    const unsubscribe = subscribe(
      getChronologicalTickerRef(),
      (res) => {
        if (res.data) setTickerData(res.data);
      },
      (err) => console.error("Ticker Realtime Error:", err),
    );
    return () => unsubscribe();
  }, []);

  useEffect(() => {
    if (loading || !user) return;
    // Subscribe to realtime updates for the authenticated user's profile and stock ownership
    const unsubscribe = subscribe(
      getUserProfileRef(),
      (res) => {
        if (res.data) setProfileData(res.data);
      },
      (err) => console.error("Profile Error:", err),
    );
    return () => unsubscribe();
  }, [user, loading]);

  useEffect(() => {
    if (!debouncedSearch) {
      setSearchData(null);
      return;
    }
    
    // Subscribe to realtime full-text search results for emojis based on user input
    const unsubscribe = subscribe(
      searchEmojisRef({ query: debouncedSearch }),
      (res) => {
        if (res.data) setSearchData(res.data.emojis_search);
        setIsSearchLoading(false);
      },
      (err) => {
        console.error("Text Search Error:", err);
        setIsSearchLoading(false);
      },
    );

    return () => unsubscribe();
  }, [debouncedSearch]);

import { subscribe } from "@firebase/data-connect";
import { getUserProfileRef } from "@dataconnect/generated";

useEffect(() => {
  // Subscribe to realtime updates for the authenticated user's profile and stock ownership
  const unsubscribe = subscribe(
    getUserProfileRef(),
    (res) => {
      if (res.data) {
        setData(res.data);
      }
      setIsLoading(false);
    },
    (err) => {
      console.error("Profile Realtime Error:", err);
      setIsLoading(false);
    },
  );
  return () => unsubscribe();
}, []);

 useEffect(() => {
    // Subscribe to realtime updates for the authenticated user's profile and stock ownership
    const unsub = subscribe(
      getUserProfileRef(),
      (res) => {
        if (res.data) setData(res.data);
      },
      (err) => console.error("Navbar Balance Realtime Error:", err),
    );
    return () => unsub();
  }, []);

const { data, refetch: refetchDashboard } = useGetDashboardData();

 useEffect(() => {
    if (!user) return;
    // Subscribe to realtime updates for the authenticated user's profile
    const unsub = subscribe(getUserProfileRef(), (res) => {
      if (res.data) {
        setProfileData(res.data);
        setOptimisticRole(null);
      }
    });
    return () => unsub();
  }, [user]);

import { subscribe } from "@firebase/data-connect";
import { getDashboardDataRef, getEmojiSparklinesRef } from "@dataconnect/generated";

// Inside the MarketPanel component:
  useEffect(() => {
    // Subscribe to realtime updates for the main market dashboard data including top emojis and recent events
    const unsub = subscribe(
      getDashboardDataRef(),
      (res) => {
        if (res.data) setData(res.data);
      },
      (err) => console.error("Market Panel Realtime Error:", err)
    );
    return () => unsub();
  }, []);

  useEffect(() => {
    // Subscribe to realtime price history updates to render emoji sparkline charts
    const unsub = subscribe(
      getEmojiSparklinesRef(),
      (res) => {
        if (res.data?.emojiSparklines) {
          setSparklineRawData(res.data.emojiSparklines);
        }
      },
      (err) => console.error("Global Sparklines Error:", err)
    );
    return () => unsub();
  }, []);

import { subscribe } from "@firebase/data-connect";
import { getTopTradersRef } from "@dataconnect/generated";

// Inside the Leaderboard component:
  useEffect(() => {
    // Subscribe to realtime updates for the global leaderboard ranking top traders by net worth
    const unsubscribe = subscribe(
      getTopTradersRef(),
      (res) => {
        if (res.data) setData(res.data);
        setIsLoading(false);
      },
      (err) => {
        console.error("Leaderboard Realtime Error:", err);
        setIsLoading(false);
      },
    );
    return () => unsubscribe();
  }, []);

import { subscribe } from "@firebase/data-connect";
import {
  getEmojiHistoryStatsRef,
  getEmojiWhaleStatsRef,
} from "@dataconnect/generated";

// Inside the EmojiModal component:
  useEffect(() => {
    if (!emoji?.id) return;

    setStatsLoading(true);
    // Subscribe to realtime historical price and moving average statistics for the selected emoji
    const unsub = subscribe(
      getEmojiHistoryStatsRef({ emojiId: emoji.id }),
      (res) => {
        if (res.data) setStatsData(res.data);
        setStatsLoading(false);
      },
      (err) => {
        console.error("History Realtime Error:", err);
        setStatsLoading(false);
      },
    );
    return () => unsub();
  }, [emoji?.id]);

  useEffect(() => {
    // Subscribe to realtime whale statistics to identify the top shareholder for the selected emoji
    const unsub = subscribe(
      getEmojiWhaleStatsRef(),
      (res) => {
        if (res.data) setWhaleData(res.data);
      },
      (err) => console.error("Whale Realtime Error:", err),
    );
    return () => unsub();
  }, []);

# Upserts a user record using the Firebase Auth uid expression as the primary key
# Upsert (update or insert) a user's profile information
mutation UpsertUser($username: String!, $profileImage: String!)
@auth(level: USER) {
  user_upsert(
    data: {
      id_expr: "auth.uid"
      username: $username
      profileImage: $profileImage
    }
  )
}

# Update a user's role
mutation UpdateUserRole($role: String!) @auth(level: USER) {
  user_update(key: { id_expr: "auth.uid" }, data: { role: $role })
}

# Update a user's location
mutation UpdateUserLocation(
  $city: String!
  $latitude: Float!
  $longitude: Float!
) @auth(level: USER) {
  user_update(
    key: { id_expr: "auth.uid" }
    data: { city: $city, latitude: $latitude, longitude: $longitude }
  )
}

# Trigger a new market event for an emoji
mutation TriggerEvent(
  $emojiId: UUID!
  $impact: Float!
  $description: String!
  $now: Timestamp!
) @auth(level: USER) {
  event_insert(
    data: {
      userId_expr: "auth.uid"
      emojiId: $emojiId
      impact: $impact
      description: $description
      createdAt: $now
    }
  )
}

# Execute a market maker trade to adjust emoji price and shares
mutation MarketMakerTrade(
  $emojiId: UUID!
  $priceImpact: Float!
  $shareDelta: Int!
  $eventDesc: String!
  $newPrice: Float!
)
@auth(
  level: USER
  insecureReason: "This operation is safe to expose to any user."
)
@transaction {
  query @redact {
    user(key: { id_expr: "auth.uid" })
      @check(
        expr: "this != null && this.role == 'ADMIN'",
        message: "Access Denied: You must have the ADMIN role to deploy the Market Maker bot."
      ) {
      role
    }
  }
  stockOwnership_upsert(
    data: {
      userId: "system_market_maker"
      emojiId: $emojiId
      shares_update: { inc: $shareDelta }
    }
  )
  emoji_update(
    id: $emojiId
    data: { currentPrice_update: { inc: $priceImpact }, trend: $priceImpact }
  )
  event_insert(
    data: {
      userId: "system_market_maker"
      emojiId: $emojiId
      impact: $priceImpact
      description: $eventDesc
    }
  )
  priceHistory_insert(data: { emojiId: $emojiId, price: $newPrice })
}

firebase dataconnect:sdk:generate

import {
  upsertUser,
  updateUserLocation,
  marketMakerTrade,
  updateUserRole,
  triggerMarketCrash,
} from "@dataconnect/generated";

// Upsert (update or insert) a user's profile information and log the event
export const executeUpsertUser = async (
  username: string,
  profileImage: string,
  logEvent: (key: LogEventKey, params?: any) => void,
): Promise<void> => {
  logEvent("UPSERT_USER_MUTATION", { username });
  await upsertUser({ username, profileImage });
};

// Update a user's role and log the event
export const executeUpdateRole = async (
  role: string,
  logEvent: (key: LogEventKey, params?: any) => void
): Promise<void> => {
  logEvent("UPDATE_USER_ROLE_MUTATION", { role });
  await updateUserRole({ role });
};

// Update a user's city and geographic coordinates
export const executeUpdateLocation = async (
  city: string,
  latitude: number,
  longitude: number,
): Promise<void> => {
  await updateUserLocation({ city, latitude, longitude });
};

// Execute a random market maker trade and adjust an emoji's stock price
export const executeManualBotTrade = async (
  randomEmoji: any,
  username: string,
  logEvent: (key: LogEventKey, params?: any) => void,
): Promise<{ isBuy: boolean; tradeAmount: number }> => {
  logEvent("MARKET_MAKER_TRADE");
  const isBuy = Math.random() > 0.5;
  const tradeAmount = Number((Math.random() * (10 - 2) + 2).toFixed(2));

  await marketMakerTrade({
    emojiId: randomEmoji.id,
    priceImpact: isBuy ? tradeAmount : -tradeAmount,
    shareDelta: isBuy ? 10 : -10,
    eventDesc: `Admin ${username} triggered market event: ${randomEmoji.symbol} went ${isBuy ? "up" : "down"} by $${tradeAmount.toFixed(2)}.`,
    newPrice: Math.max(0.01, randomEmoji.currentPrice + (isBuy ? tradeAmount : -tradeAmount)),
  });

  return { isBuy, tradeAmount };
};

CREATE EXTENSION IF NOT EXISTS postgis;

# Get top trending emojis partitioned by user city using native SQL
query GetTopEmojisByCity
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
) {
  cityTrends: _select(
    sql: """
    WITH city_shares AS (
      SELECT
        u.city,
        AVG(u.latitude) as latitude,
        AVG(u.longitude) as longitude,
        e.id as emoji_id,
        e.symbol,
        e.name,
        SUM(so.shares) as total_shares,
        RANK() OVER (PARTITION BY u.city ORDER BY SUM(so.shares) DESC) as rank
      FROM stock_ownership so
      JOIN "user" u ON so.user_id = u.id
      JOIN emoji e ON so.emoji_id = e.id
      WHERE u.city IS NOT NULL AND u.latitude IS NOT NULL AND so.shares > 0
      GROUP BY u.city, e.id, e.symbol, e.name
    )
    SELECT city, latitude, longitude, emoji_id, symbol, name, total_shares
    FROM city_shares
    WHERE rank = 1
    ORDER BY city ASC
    """
    params: []
  )
}

# Get trending emojis within a geographic radius using native SQL and PostGIS extension
query GetTrendingEmojisNearMe(
  $userLng: Float!
  $userLat: Float!
  $radiusMeters: Float!
)
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
) {
  regionalTrends: _select(
    sql: """
    SELECT
      e.id,
      e.symbol,
      e.name,
      e.current_price,
      e.trend,
      COUNT(so.shares)   AS regional_holders,
      SUM(so.shares)     AS regional_shares
    FROM emoji e
    JOIN stock_ownership so ON so.emoji_id = e.id
    JOIN "user" u            ON u.id = so.user_id
    WHERE u.latitude  IS NOT NULL
      AND u.longitude IS NOT NULL
      AND so.shares > 0
      AND ST_DWithin(
        ST_MakePoint(u.longitude, u.latitude)::geography,
        ST_MakePoint($1, $2)::geography,
        $3
      )
    GROUP BY e.id, e.symbol, e.name, e.current_price, e.trend
    ORDER BY regional_shares DESC
    LIMIT 10
    """
    params: [$userLng, $userLat, $radiusMeters]
  )
}

# Buy shares of an emoji stock
mutation BuyStock($emojiId: UUID!, $amount: Int!, $isDiscounted: Boolean!)
@auth(level: USER) {
  buyStock: _execute(
    sql: """
    WITH validated_params AS (
      SELECT
        $1::uuid AS emoji_id,
        $2::int AS amount,
        $3::boolean AS is_discounted,
        $4::text AS user_id
    ),
    target_emoji AS (
      SELECT
        e.id,
        (e.current_price * (CASE WHEN vp.is_discounted THEN 0.5 ELSE 1.0 END) * vp.amount) AS total_cost
      FROM emoji e
      CROSS JOIN validated_params vp
      WHERE e.id = vp.emoji_id
        AND vp.amount > 0
        AND vp.amount <= 100
    ),
    deduct_funds AS (
      UPDATE "user" u
      SET points = u.points - te.total_cost
      FROM target_emoji te, validated_params vp
      WHERE u.id = vp.user_id AND u.points >= te.total_cost
      RETURNING u.id
    ),
    upsert_ownership AS (
      INSERT INTO stock_ownership (user_id, emoji_id, shares)
      SELECT vp.user_id, vp.emoji_id, vp.amount
      FROM validated_params vp
      WHERE EXISTS (SELECT 1 FROM deduct_funds)
      ON CONFLICT (user_id, emoji_id) DO UPDATE
      SET shares = stock_ownership.shares + EXCLUDED.shares
      RETURNING stock_ownership.emoji_id
    ),
    update_emoji AS (
      UPDATE emoji e
      SET
        current_price = GREATEST(0.01, e.current_price + (e.current_price * 0.01 * vp.amount)),
        trend = GREATEST(0.01, e.current_price + (e.current_price * 0.01 * vp.amount)) - e.current_price
      FROM validated_params vp
      WHERE e.id = vp.emoji_id AND EXISTS (SELECT 1 FROM deduct_funds)
      RETURNING e.id, e.current_price, e.trend
    )
    INSERT INTO price_history (id, emoji_id, price, recorded_at)
    SELECT gen_random_uuid(), ue.id, ue.current_price, NOW()
    FROM update_emoji ue;
    """
    params: [$emojiId, $amount, $isDiscounted, { _expr: "auth.uid" }]
  )
}

# Sell shares of an emoji stock
mutation SellStock($emojiId: UUID!, $amount: Int!) @auth(level: USER) {
  sellStock: _execute(
    sql: """
    WITH validated_params AS (
      SELECT
        $1::uuid AS emoji_id,
        $2::int AS amount,
        $3::text AS user_id
    ),
    target_emoji AS (
      SELECT
        e.id,
        (e.current_price * vp.amount) AS total_revenue,
        GREATEST(0.01, e.current_price * POWER(0.99, vp.amount)) AS new_price
      FROM emoji e
      CROSS JOIN validated_params vp
      WHERE e.id = vp.emoji_id
        AND vp.amount > 0
        AND vp.amount <= 100
    ),
    check_shares AS (
      SELECT so.user_id
      FROM stock_ownership so
      CROSS JOIN validated_params vp
      WHERE so.user_id = vp.user_id
        AND so.emoji_id = vp.emoji_id
        AND so.shares >= vp.amount
    ),
    add_funds AS (
      UPDATE "user" u
      SET points = u.points + te.total_revenue
      FROM target_emoji te, validated_params vp
      WHERE u.id = vp.user_id AND EXISTS (SELECT 1 FROM check_shares)
      RETURNING u.id
    ),
    update_ownership AS (
      UPDATE stock_ownership so
      SET shares = so.shares - vp.amount
      FROM validated_params vp
      WHERE so.user_id = vp.user_id
        AND so.emoji_id = vp.emoji_id
        AND EXISTS (SELECT 1 FROM check_shares)
        AND EXISTS (SELECT 1 FROM add_funds)
    ),
    update_emoji AS (
      UPDATE emoji e
      SET
        current_price = te.new_price,
        trend = te.new_price - e.current_price
      FROM target_emoji te, validated_params vp
      WHERE e.id = vp.emoji_id
        AND EXISTS (SELECT 1 FROM check_shares)
        AND EXISTS (SELECT 1 FROM add_funds)
      RETURNING e.id, e.current_price, e.trend
    )
    INSERT INTO price_history (id, emoji_id, price, recorded_at)
    SELECT gen_random_uuid(), ue.id, ue.current_price, NOW()
    FROM update_emoji ue;
    """
    params: [$emojiId, $amount, { _expr: "auth.uid" }]
  )
}

firebase dataconnect:sdk:generate

import { buyStock, sellStock, generateTradeHeadline, triggerEvent } from "@dataconnect/generated";
import { LogEventKey } from "./InspectorContext";

// Execute a stock purchase, validating limits and potentially generating an AI news headline for large trades
export const executeBuyStock = async (
  emoji: any,
  amount: number,
  isDiscounted: boolean,
  user: any,
  logEvent: (key: LogEventKey, params?: any) => void,
): Promise<void> => {
  const MAX_AMOUNT = 100;

  if (!Number.isInteger(amount) || amount <= 0 || amount > MAX_AMOUNT) {
    throw new Error(`Amount must be an integer between 1 and ${MAX_AMOUNT}.`);
  }
  const singleSharePrice = isDiscounted
    ? emoji.currentPrice * 0.5
    : emoji.currentPrice;
  const estimatedCost = singleSharePrice * amount;
  const estimatedImpact = emoji.currentPrice * 0.05 * amount;

  logEvent("BUY_STOCK_TRANSACTION", { amount, symbol: emoji.symbol });

  const response = await buyStock({
    emojiId: emoji.id,
    amount: amount,
    isDiscounted: isDiscounted,
  });

  const buyResult = response.data?.buyStock as any;

  if (
    !buyResult ||
    buyResult === 0 ||
    (Array.isArray(buyResult) && buyResult.length === 0)
  ) {
    throw new Error(
      "Transaction denied: Insufficient funds or price mismatch.",
    );
  }

  const actualCost = Array.isArray(buyResult)
    ? buyResult[0].actual_cost
    : estimatedCost;
  const actualImpact = Array.isArray(buyResult)
    ? buyResult[0].actual_impact
    : estimatedImpact;

// TODO: Optionally add a custom resolver to call AI to generate headline for this purchase
};

// Execute a stock sale, validating ownership and potentially generating an AI news headline for large trades
export const executeSellStock = async (
  emoji: any,
  amount: number,
  ownedShares: number,
  user: any,
  logEvent: (key: LogEventKey, params?: any) => void,
): Promise<void> => {
  const MAX_AMOUNT = 100;

  if (!Number.isInteger(amount) || amount <= 0 || amount > MAX_AMOUNT) {
    throw new Error(`Amount must be an integer between 1 and ${MAX_AMOUNT}.`);
  }
  if (amount > ownedShares) {
    throw new Error(
      "INSUFFICIENT SHARES: You cannot sell more shares than you own.",
    );
  }

  const estimatedRevenue = emoji.currentPrice * amount;
  const dropRatePerShare = 0.05;
  const targetPrice =
    emoji.currentPrice * Math.pow(1 - dropRatePerShare, amount);
  const estimatedImpact = Math.max(0.01, targetPrice) - emoji.currentPrice;

  logEvent("SELL_STOCK_TRANSACTION", { amount, symbol: emoji.symbol });

  const response = await sellStock({
    emojiId: emoji.id,
    amount: amount,
  });

  const sellResult = response.data?.sellStock as any;

  if (
    !sellResult ||
    sellResult === 0 ||
    (Array.isArray(sellResult) && sellResult.length === 0)
  ) {
    throw new Error("Transaction denied: Insufficient shares.");
  }

  const actualRevenue = Array.isArray(sellResult)
    ? sellResult[0].actual_revenue
    : estimatedRevenue;
  const actualImpact = Array.isArray(sellResult)
    ? sellResult[0].actual_impact
    : estimatedImpact;
// TODO: Optionally add a custom resolver to call AI to generate headline for this sale

};

import { subscribe } from "@firebase/data-connect";
import { getTrendingEmojisNearMeRef } from "@dataconnect/generated";

// ... inside the component
  useEffect(() => {
    if (!location) return;

    setIsLoadingTrends(true);

    // Subscribe to realtime updates for trending emojis within a 50km radius
    const unsub = subscribe(
      getTrendingEmojisNearMeRef({
        userLat: location.lat,
        userLng: location.lng,
        radiusMeters: 50000, // 50km
      }),
      (res) => {
        if (res.data) setLocalData(res.data);
        setIsLoadingTrends(false);
      },
      (err) => {
        console.error("Local Radar Realtime Error:", err);
        setIsLoadingTrends(false);
      },
    );

    return () => unsub();
  }, [location?.lat, location?.lng]);

import { subscribe } from "@firebase/data-connect";
import { getTopEmojisByCityRef, getTrendingEmojisNearMeRef, getUserProfileRef } from "@dataconnect/generated";

// ... inside the component
  useEffect(() => {
    // Subscribe to realtime updates for the authenticated user's profile and stock ownership
    const unsub = subscribe(getUserProfileRef(), (res) => {
      if (res.data) setProfileData(res.data);
    });
    return () => unsub();
  }, []);

  useEffect(() => {
    // Subscribe to realtime updates for top trending emojis partitioned by user city
    const unsub = subscribe(getTopEmojisByCityRef(), (res) => {
      if (res.data) setCityData(res.data);
    });
    return () => unsub();
  }, []);

  useEffect(() => {
    setRadarLoading(true);
    // Subscribe to realtime updates for trending emojis within a specified geographic radius
    const unsub = subscribe(
      getTrendingEmojisNearMeRef({
        userLat: coords.lat,
        userLng: coords.lng,
        radiusMeters: radiusKm * 1000,
      }),
      (res) => {
        if (res.data) setRadarData(res.data);
        setRadarLoading(false);
      },
    );
    return () => unsub();
  }, [coords.lat, coords.lng, radiusKm]);

# Get dashboard data including top emojis by price and recent market events
query GetDashboardData
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
)
@refresh(onMutationExecuted: { operation: "BuyStock" })
@refresh(onMutationExecuted: { operation: "SellStock" })
@refresh(onMutationExecuted: { operation: "TriggerEvent" })
@refresh(onMutationExecuted: { operation: "MarketMakerTrade" }) {
  emojis(orderBy: [{ currentPrice: DESC }]) {
    id
    symbol
    name
    description
    currentPrice
    trend
  }
  events(orderBy: [{ createdAt: DESC }], limit: 15) {
    id
    description
    impact
    createdAt
    user {
      username
      profileImage
    }
    emoji {
      symbol
    }
  }
}

# Get current authenticated user profile and their stock ownership using auth.uid
query GetUserProfile
@auth(level: USER)
@refresh(onMutationExecuted: { operation: "BuyStock" })
@refresh(onMutationExecuted: { operation: "SellStock" })
@refresh(onMutationExecuted: { operation: "UpdateUserLocation" })
@refresh(onMutationExecuted: { operation: "UpdateUserRole" }) {
  user(id_expr: "auth.uid") {
    points
    username
    profileImage
    role
    stockOwnerships_on_user {
      shares
      emoji {
        id
        symbol
        currentPrice
        name
      }
    }
    city
    latitude
    longitude
  }
}

firebase dataconnect:sdk:generate

import { subscribe } from "@firebase/data-connect";
import { getDashboardDataRef } from "@dataconnect/generated";

// ... inside the component
  useEffect(() => {
    const queryRef = getDashboardDataRef();

    // The subscribe function registers the QueryRef and callbacks
    const unsubscribe = subscribe(
      queryRef,
      (res) => {
        // onNext: Fires initially, AND whenever a @refresh trigger occurs
        if (res.data) setDashboardData(res.data);
        setIsDashboardLoading(false);
      },
      (err) => {
        // onError: Handles any server or permission errors
        console.error("Dashboard Realtime Error:", err);
        setIsDashboardLoading(false);
      }
    );

    // onComplete/Cleanup: Unregisters the callbacks when the component unmounts
    return () => unsubscribe();
  }, [user]);

# Required for the Geo Map page
CREATE EXTENSION IF NOT EXISTS postgis;
# Required for Vector Search
CREATE EXTENSION IF NOT EXISTS "vector";
# Required for automatic Vector Search embedding generation
CREATE EXTENSION IF NOT EXISTS "google_ml_integration";

firebase dataconnect:sdk:generate

npm run build

specVersion: v1
serviceId: your-project-id-service
location: us-west4
schemas:
  - source: ./schema
    datasource:
      postgresql:
        database: your-project-id-database
        cloudSql:
          instanceId: your-project-id-instance
connectorDirs:
  - ./friendly-exchange

firebase deploy --only dataconnect,hosting

firebase dataconnect:sql:diff

firebase dataconnect:sql:migrate

# Emojis
# emoji-stockOwnership is a one-to-many relationship, emoji-priceHistory is a one-to-many relationship
# Implements @searchable directives for full-text search
# Optional: implements Vector type for semantic search
type Emoji @table {
  id: UUID! @default(expr: "uuidV4()")
  symbol: String!
  name: String! @searchable
  tags: [String!]
  description: String! @searchable
  descriptionEmbedding: Vector @col(size: 768)
  currentPrice: Float! @default(value: 10.0)
  trend: Float! @default(value: 0.0)
}

# Search emoji descriptions using Vertex AI embeddings
query VectorSearchEmojis($query: String!)
@auth(
  level: PUBLIC
  insecureReason: "This operation is safe to expose to the public."
)
@refresh(onMutationExecuted: { operation: "BuyStock" })
@refresh(onMutationExecuted: { operation: "SellStock" })
@refresh(onMutationExecuted: { operation: "TriggerEvent" })
@refresh(onMutationExecuted: { operation: "MarketMakerTrade" }) {
  emojis_descriptionEmbedding_similarity(
    compare_embed: { model: "text-multilingual-embedding-002", text: $query }
    method: COSINE
    within: 2
    limit: 15
  ) {
    id
    symbol
    name
    description
    currentPrice
    trend
    _metadata {
      distance
    }
  }
}

firebase dataconnect:sdk:generate

import { subscribe } from "@firebase/data-connect";
import {
  getDashboardDataRef,
  searchEmojisRef,
  vectorSearchEmojisRef, // <-- Add this!
  getChronologicalTickerRef,
  getUserProfileRef,
} from "@dataconnect/generated";

// Inside Home component, find the search useeffect
useEffect(() => {
    if (!debouncedSearch) {
      setSearchData(null);
      return;
    }

    let unsubscribe: () => void;

    if (searchMode === "TEXT") {
      // Subscribe to realtime full-text search results for emojis based on user input
      unsubscribe = subscribe(
        searchEmojisRef({ query: debouncedSearch }),
        (res) => {
          if (res.data) setSearchData(res.data.emojis_search);
          setIsSearchLoading(false);
        },
        (err) => {
          console.error("Text Search Error:", err);
          setIsSearchLoading(false);
        },
      );
    } else {
      // Subscribe to realtime vector search results using semantic similarity for emojis based on user input
      unsubscribe = subscribe(
        vectorSearchEmojisRef({ query: debouncedSearch }),
        (res) => {
          if (res.data)
            setSearchData(res.data.emojis_descriptionEmbedding_similarity);
          setIsSearchLoading(false);
        },
        (err) => {
          console.error("Vector Search Error:", err);
          setIsSearchLoading(false);
        },
      );
    }

    return () => {
      if (unsubscribe) unsubscribe();
    };
  }, [debouncedSearch, searchMode]);

firebase init dataconnect:resolver

# Custom resolver fields can be defined on root Query and Mutation types.
type Mutation {
  # This field will be backed by your Cloud Function.
  generateTradeHeadline(
    emojiSymbol: String!
    emojiName: String!
    username: String!
    tradeAmount: Int!
    tradeCost: Float!
    tradeType: String!
  ): String!
}

import { setGlobalOptions } from "firebase-functions";
import {
  FirebaseContext,
  onGraphRequest,
} from "firebase-functions/dataconnect/graphql";
import { initializeApp, getApps } from "firebase-admin/app";
import { GoogleGenAI } from "@google/genai";

setGlobalOptions({
  maxInstances: 10,
  region: "us-west4",
});

if (getApps().length === 0) {
  initializeApp();
}

const ai = new GoogleGenAI({
  vertexai: true,
  project: process.env.GCLOUD_PROJECT || "your-project-id",
  location: process.env.GCLOUD_LOCATION || "us-west4",
});

const headlineOpts = {
  // Points to the schema you defined earlier
  schemaFilePath: "dataconnect/schema_generateTradeHeadline/schema.gql",
  resolvers: {
    mutation: {
      // Generate a satirical financial news headline for a stock trade using Vertex AI
      async generateTradeHeadline(
        _parent: unknown,
        args: Record<string, unknown>,
        _contextValue: FirebaseContext,
        _info: unknown,
      ): Promise<string> {
        const {
          emojiSymbol,
          emojiName,
          username,
          tradeAmount,
          tradeCost,
          tradeType,
        } = args;

        try {
          const prompt = `You are a hype-driven, satirical financial news bot. 
          A user named '${username}' just executed a massive ${tradeType} of ${tradeAmount} shares of ${emojiSymbol} (${emojiName}) for $${tradeCost}. 
          Write a single, punchy, dramatic news headline (under 12 words) about this market move, use puns wherever possible, but don't round or exagerate the numbers. Include the asset symbol.`;
          
          const response = await ai.models.generateContent({
            model: "gemini-2.5-flash-lite",
            contents: prompt,
          });

          if (!response.text) {
            throw new Error("No text returned from Vertex AI");
          }

          return response.text.trim();
        } catch (error) {
          console.error("Vertex AI generation failed:", error);
          return `BREAKING: Massive ${tradeType} detected on ${emojiSymbol}! Market reacting.`;
        }
      },
    },
  },
};

export const generateTradeHeadline = onGraphRequest(headlineOpts);