Card & map tables

-- Game tables: creature cards, the shared map, battle history, and the season.

CREATE TABLE cards (
  token_id TEXT PRIMARY KEY,
  owner TEXT NOT NULL,
  element TEXT NOT NULL,        -- 'fire' | 'water' | 'grass'
  power INTEGER NOT NULL,       -- 1..9
  name TEXT NOT NULL,
  block_height INTEGER NOT NULL
);
CREATE INDEX cards_owner_index ON cards(owner);

CREATE TABLE map_cells (
  x INTEGER NOT NULL,
  y INTEGER NOT NULL,
  token_id TEXT NOT NULL UNIQUE REFERENCES cards(token_id),
  owner TEXT NOT NULL,
  placed_at_height INTEGER NOT NULL,
  PRIMARY KEY (x, y)
);

CREATE TABLE battle_log (
  id SERIAL PRIMARY KEY,
  x INTEGER NOT NULL,
  y INTEGER NOT NULL,
  attacker TEXT NOT NULL,
  defender TEXT NOT NULL,
  attacker_token TEXT NOT NULL,
  defender_token TEXT NOT NULL,
  attacker_total REAL NOT NULL,
  defender_total REAL NOT NULL,
  attacker_won BOOLEAN NOT NULL,
  block_height INTEGER NOT NULL
);

-- Single-row table: the current season (set from the Midnight counter).
CREATE TABLE season (
  id INTEGER PRIMARY KEY CHECK (id = 1),
  element TEXT NOT NULL,        -- 'none' | 'fire' | 'water' | 'grass'
  counter_value INTEGER NOT NULL,
  block_height INTEGER NOT NULL
);
INSERT INTO season (id, element, counter_value, block_height) VALUES (1, 'none', 0, 0);

import type { DBMigrations } from "@effectstream/runtime";
import databaseSql from "./migrations/000-init.sql" with { type: "text" };
import gameSql from "./migrations/001-game.sql" with { type: "text" };

export const migrationTable: DBMigrations[] = [
  {
    name: "000-init.sql",
    sql: databaseSql,
  },
  {
    name: "001-game.sql",
    sql: gameSql,
  },
];

/* @name cardsTableExists */
SELECT EXISTS (
    SELECT FROM information_schema.tables
    WHERE  table_schema = 'public'
    AND    table_name   = 'cards'
);

/* @name insertCard */
INSERT INTO cards
    (token_id, owner, element, power, name, block_height)
VALUES
    (:token_id!, :owner!, :element!, :power!, :name!, :block_height!)
ON CONFLICT (token_id) DO NOTHING;

/* @name updateCardOwner */
UPDATE cards SET owner = :owner! WHERE token_id = :token_id!;

/* @name getCardByTokenId */
SELECT * FROM cards WHERE token_id = :token_id!;

/* @name getCards */
SELECT * FROM cards ORDER BY block_height DESC, token_id DESC;

/* @name getMap */
SELECT m.x, m.y, m.token_id, m.owner, m.placed_at_height,
       c.element, c.power, c.name
FROM map_cells m
JOIN cards c ON c.token_id = m.token_id
ORDER BY m.y, m.x;

/* @name getCell */
SELECT m.x, m.y, m.token_id, m.owner,
       c.element, c.power, c.name
FROM map_cells m
JOIN cards c ON c.token_id = m.token_id
WHERE m.x = :x! AND m.y = :y!;

/* @name getNeighbors */
SELECT m.x, m.y, m.owner,
       c.element, c.power
FROM map_cells m
JOIN cards c ON c.token_id = m.token_id
WHERE (ABS(m.x - :x!) + ABS(m.y - :y!)) = 1;

/* @name isTokenPlaced */
SELECT token_id FROM map_cells WHERE token_id = :token_id!;

/* @name placeCard */
INSERT INTO map_cells
    (x, y, token_id, owner, placed_at_height)
VALUES
    (:x!, :y!, :token_id!, :owner!, :placed_at_height!)
ON CONFLICT (x, y)
DO UPDATE SET
    token_id = EXCLUDED.token_id,
    owner = EXCLUDED.owner,
    placed_at_height = EXCLUDED.placed_at_height;

/* @name decrementCardPower */
UPDATE cards
SET power = GREATEST(:min_power!, power - :amount!)
WHERE token_id = :token_id!;

/* @name insertBattle */
INSERT INTO battle_log
    (x, y, attacker, defender, attacker_token, defender_token,
     attacker_total, defender_total, attacker_won, block_height)
VALUES
    (:x!, :y!, :attacker!, :defender!, :attacker_token!, :defender_token!,
     :attacker_total!, :defender_total!, :attacker_won!, :block_height!);

/* @name getBattles */
SELECT * FROM battle_log ORDER BY id DESC LIMIT 20;

/* @name getLeaderboard */
SELECT owner, COUNT(*)::int AS cells
FROM map_cells
GROUP BY owner
ORDER BY cells DESC;

/* @name getSeason */
SELECT * FROM season WHERE id = 1;

/* @name updateSeason */
UPDATE season
SET element = :element!, counter_value = :counter_value!, block_height = :block_height!
WHERE id = 1;

export * from "./sql/sm_example.queries.ts";
export * from "./sql/game.queries.ts";
export { migrationTable } from "./migration-order.ts";