28 lines
3.7 KiB
SQL
28 lines
3.7 KiB
SQL
ALTER TABLE players ADD COLUMN normalized_username text UNIQUE;
|
|
ALTER TABLE players ADD COLUMN password_hash text;
|
|
ALTER TABLE players ADD COLUMN coin_balance integer NOT NULL DEFAULT 0 CHECK(coin_balance BETWEEN 0 AND 1000000000);
|
|
ALTER TABLE players ADD COLUMN xp integer NOT NULL DEFAULT 0 CHECK(xp>=0);
|
|
ALTER TABLE players ADD COLUMN game_stats jsonb NOT NULL DEFAULT '{}'::jsonb;
|
|
ALTER TABLE plants ADD COLUMN game_eligible boolean NOT NULL DEFAULT false;
|
|
ALTER TABLE plants ADD COLUMN is_favorite boolean NOT NULL DEFAULT false;
|
|
ALTER TABLE plants ADD COLUMN is_protected boolean NOT NULL DEFAULT false;
|
|
ALTER TABLE plants ADD COLUMN first_discovery boolean NOT NULL DEFAULT false;
|
|
ALTER TABLE plants ADD COLUMN sold_at timestamptz;
|
|
ALTER TABLE breeding_events DROP CONSTRAINT breeding_events_idempotency_key_key;
|
|
ALTER TABLE breeding_events ADD CONSTRAINT breeding_owner_request UNIQUE(owner_id,idempotency_key);
|
|
CREATE TABLE sessions(token_hash text PRIMARY KEY,player_id uuid NOT NULL REFERENCES players(id),expires_at timestamptz NOT NULL);
|
|
CREATE INDEX sessions_owner ON sessions(player_id);
|
|
CREATE TABLE recovery_codes(player_id uuid REFERENCES players(id),code_hash text PRIMARY KEY,used_at timestamptz);
|
|
CREATE TABLE inventory_seeds(player_id uuid REFERENCES players(id),seed_type text NOT NULL,quantity integer NOT NULL CHECK(quantity>=0),PRIMARY KEY(player_id,seed_type));
|
|
CREATE TABLE greenhouses(id uuid PRIMARY KEY,player_id uuid NOT NULL REFERENCES players(id),name text NOT NULL,slots integer NOT NULL DEFAULT 4 CHECK(slots BETWEEN 4 AND 12),lamp boolean NOT NULL DEFAULT false,irrigation boolean NOT NULL DEFAULT false);
|
|
CREATE INDEX greenhouse_owner ON greenhouses(player_id);
|
|
CREATE TABLE plant_slots(id uuid PRIMARY KEY,greenhouse_id uuid NOT NULL REFERENCES greenhouses(id),slot_no integer NOT NULL,seed_type text,genome jsonb,visual_seed integer,planted_at timestamptz,ready_at timestamptz,growth_rules_version integer NOT NULL DEFAULT 1,UNIQUE(greenhouse_id,slot_no));
|
|
CREATE TABLE game_receipts(player_id uuid REFERENCES players(id),request_id uuid NOT NULL,operation text NOT NULL,body_hash text NOT NULL,response jsonb NOT NULL,created_at timestamptz NOT NULL DEFAULT now(),PRIMARY KEY(player_id,request_id));
|
|
CREATE TABLE coin_ledger(id uuid PRIMARY KEY,player_id uuid NOT NULL REFERENCES players(id),amount integer NOT NULL,balance_after integer NOT NULL CHECK(balance_after>=0),reason text NOT NULL,reference text NOT NULL,created_at timestamptz NOT NULL DEFAULT now());
|
|
CREATE INDEX ledger_owner_created ON coin_ledger(player_id,created_at DESC);
|
|
CREATE TABLE discoveries(player_id uuid REFERENCES players(id),signature text NOT NULL,plant_id uuid REFERENCES plants(id),created_at timestamptz NOT NULL DEFAULT now(),PRIMARY KEY(player_id,signature));
|
|
CREATE TABLE player_quests(player_id uuid REFERENCES players(id),quest_id text NOT NULL,claimed_at timestamptz NOT NULL DEFAULT now(),PRIMARY KEY(player_id,quest_id));
|
|
CREATE TABLE player_research(player_id uuid REFERENCES players(id),research_id text NOT NULL,created_at timestamptz NOT NULL DEFAULT now(),PRIMARY KEY(player_id,research_id));
|
|
CREATE TABLE workers(id uuid PRIMARY KEY,player_id uuid NOT NULL REFERENCES players(id),kind text NOT NULL CHECK(kind IN ('gardener','breeder')),greenhouse_id uuid REFERENCES greenhouses(id),enabled boolean NOT NULL DEFAULT false,policy jsonb NOT NULL DEFAULT '{}'::jsonb,last_processed_at timestamptz NOT NULL DEFAULT now());
|
|
CREATE INDEX worker_owner ON workers(player_id);
|
|
CREATE TABLE worker_runs(id uuid PRIMARY KEY,player_id uuid NOT NULL REFERENCES players(id),worker_id uuid NOT NULL REFERENCES workers(id),interval_start timestamptz NOT NULL,interval_end timestamptz NOT NULL,summary jsonb NOT NULL,created_at timestamptz NOT NULL DEFAULT now(),UNIQUE(worker_id,interval_start,interval_end));
|