-- Trade (Internet exchange offers), shared report folder, end game (backbone landing sites, winner, final ranking). -- Times are integer ms since the epoch (UTC). Every game row carries world_id. -- An offer "give X of A for Y of B, max distance Z". The offered amount is ESCROWED: it left the POP when the offer was -- created and lives in this row until the offer is accepted (it then travels as a transport), cancelled or expired (it goes -- back to the POP). status: open | accepted | completed | cancelled | expired | void (the POP changed hands). CREATE TABLE trade_offers ( id INTEGER PRIMARY KEY, world_id INTEGER NOT NULL REFERENCES worlds(id), town_id INTEGER NOT NULL REFERENCES towns(id), -- the POP that posted it (the escrow came from there) operator_id INTEGER NOT NULL REFERENCES operators(id), give_res TEXT NOT NULL, give_amount INTEGER NOT NULL CHECK (give_amount > 0), want_res TEXT NOT NULL, want_amount INTEGER NOT NULL CHECK (want_amount > 0), max_distance INTEGER NOT NULL, status TEXT NOT NULL DEFAULT 'open', created_at INTEGER NOT NULL, expires_at INTEGER NOT NULL, expire_event_id INTEGER, accepted_by_operator INTEGER REFERENCES operators(id), accepted_town_id INTEGER REFERENCES towns(id), accepted_at INTEGER, goods_movement_id INTEGER, -- transport poster -> acceptor (the escrow) payment_movement_id INTEGER, -- transport acceptor -> poster legs_done INTEGER NOT NULL DEFAULT 0, closed_at INTEGER ); CREATE INDEX trade_offers_open ON trade_offers(world_id, status, id); CREATE INDEX trade_offers_town ON trade_offers(town_id, status); CREATE INDEX trade_offers_operator ON trade_offers(operator_id, id); -- Reports shared to an alliance (the shared report folder). Gone with the report (cleanup) or the alliance. CREATE TABLE report_shares ( report_id INTEGER NOT NULL REFERENCES reports(id) ON DELETE CASCADE, alliance_id INTEGER NOT NULL REFERENCES alliances(id) ON DELETE CASCADE, shared_by INTEGER NOT NULL REFERENCES operators(id), created_at INTEGER NOT NULL, PRIMARY KEY (report_id, alliance_id) ); CREATE INDEX report_shares_alliance ON report_shares(alliance_id, report_id); -- Backbone landing sites (end game). Positions are a pure function of the world seed (rules.backboneSites); a row is -- created lazily by whatever touches a site first once the world is at day D.ENDGAME.openDay (no timer event). holder_alliance_id NULL = still held by the incumbent -- (or the holder disbanded: the level stays with the site). `fiber/hardware/capital` is the stockpile shipped in by the holders -- for the next level. build_* describe the level being built (one at a time). CREATE TABLE landing_sites ( id INTEGER PRIMARY KEY, world_id INTEGER NOT NULL REFERENCES worlds(id), idx INTEGER NOT NULL, x INTEGER NOT NULL, y INTEGER NOT NULL, holder_alliance_id INTEGER REFERENCES alliances(id) ON DELETE SET NULL, holder_operator_id INTEGER REFERENCES operators(id), -- who took it taken_at INTEGER, level INTEGER NOT NULL DEFAULT 0, fiber INTEGER NOT NULL DEFAULT 0, hardware INTEGER NOT NULL DEFAULT 0, capital INTEGER NOT NULL DEFAULT 0, build_to INTEGER, build_event_id INTEGER, build_started_at INTEGER, build_due_at INTEGER, build_cost TEXT, UNIQUE (world_id, idx), UNIQUE (world_id, x, y) ); -- What each operator shipped into a site's stockpile (kept when the site changes hands: it is a ledger, not a balance). CREATE TABLE landing_contributions ( site_id INTEGER NOT NULL REFERENCES landing_sites(id), operator_id INTEGER NOT NULL REFERENCES operators(id), fiber INTEGER NOT NULL DEFAULT 0, hardware INTEGER NOT NULL DEFAULT 0, capital INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (site_id, operator_id) ); -- The winner and the end of the world. The ranking snapshot is written once, when the world enters status 'ended'. ALTER TABLE worlds ADD COLUMN winner_alliance_id INTEGER; ALTER TABLE worlds ADD COLUMN winner_name TEXT; ALTER TABLE worlds ADD COLUMN winner_tag TEXT; ALTER TABLE worlds ADD COLUMN winner_site_id INTEGER; ALTER TABLE worlds ADD COLUMN won_at INTEGER; CREATE TABLE world_rankings ( world_id INTEGER PRIMARY KEY REFERENCES worlds(id), created_at INTEGER NOT NULL, data TEXT NOT NULL );