-- 001_initial_schema.sql
-- Xyvera game database — initial schema
-- Applied automatically to the xyvera PostgreSQL container on first boot.
--
-- This schema covers Steps 1–3 of the implementation sequence:
--   - player profiles and accounts
--   - resource inventory and currency ledger
--   - character roster
--   - gacha pull history and pity counters
--
-- Nakama has its own tables in CockroachDB. This Postgres DB stores
-- game-specific state that Nakama does not model natively:
--   - economy ledger (full audit trail)
--   - character progression state
--   - village assignment history
--   - content configuration cache (loaded from YAML by the content importer)

BEGIN;

-- ── Extensions ──────────────────────────────────────────────────────────────

CREATE EXTENSION IF NOT EXISTS "pgcrypto";  -- gen_random_uuid()

-- ── Players ──────────────────────────────────────────────────────────────────
-- Mirrors Nakama user IDs. All economy and progression data links here.

CREATE TABLE players (
    id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    nakama_user_id  TEXT        NOT NULL UNIQUE,
    display_name    TEXT        NOT NULL DEFAULT 'Traveller',
    region          TEXT        NOT NULL DEFAULT 'heartwood_vale',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_players_nakama_user_id ON players(nakama_user_id);

-- ── Resource Ledger ───────────────────────────────────────────────────────────
-- Every grant and spend recorded. Never update; only insert.
-- This makes the economy fully auditable (Economy Rule from spec).

CREATE TABLE resource_ledger (
    id              BIGSERIAL   PRIMARY KEY,
    player_id       UUID        NOT NULL REFERENCES players(id) ON DELETE CASCADE,
    resource_id     TEXT        NOT NULL,           -- matches resource schema id
    delta           INTEGER     NOT NULL,           -- positive = grant, negative = spend
    balance_after   INTEGER     NOT NULL,           -- denormalized for easy dashboards
    source          TEXT        NOT NULL,           -- e.g. 'lumber_camp_collect', 'gacha_pull'
    source_ref      TEXT,                           -- optional FK to another table (e.g. pull_id)
    notes           JSONB,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_resource_ledger_player   ON resource_ledger(player_id, resource_id);
CREATE INDEX idx_resource_ledger_source   ON resource_ledger(source);
CREATE INDEX idx_resource_ledger_created  ON resource_ledger(created_at);

-- ── Character Roster ─────────────────────────────────────────────────────────

CREATE TABLE roster (
    id                  UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    player_id           UUID        NOT NULL REFERENCES players(id) ON DELETE CASCADE,
    character_id        TEXT        NOT NULL,       -- matches character schema id
    presentation        TEXT        NOT NULL DEFAULT 'female' CHECK (presentation IN ('female', 'male')),
    level               INTEGER     NOT NULL DEFAULT 1 CHECK (level >= 1 AND level <= 100),
    ascension_tier      INTEGER     NOT NULL DEFAULT 0 CHECK (ascension_tier >= 0 AND ascension_tier <= 6),
    bond_level          INTEGER     NOT NULL DEFAULT 0,
    assignment_mastery  JSONB       NOT NULL DEFAULT '{}', -- {job_id: hours_accumulated}
    skills              JSONB       NOT NULL DEFAULT '{}', -- {skill_id: current_level}
    obtained_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
    obtained_source     TEXT        NOT NULL,       -- 'gacha_standard', 'gacha_event', 'starter_seed'
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX idx_roster_player_char ON roster(player_id, character_id);
CREATE INDEX idx_roster_player ON roster(player_id);

-- ── Village Assignment State ──────────────────────────────────────────────────

CREATE TABLE village_assignments (
    id                  UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    player_id           UUID        NOT NULL REFERENCES players(id) ON DELETE CASCADE,
    roster_id           UUID        NOT NULL REFERENCES roster(id) ON DELETE CASCADE,
    job_id              TEXT        NOT NULL,       -- matches job schema id
    assigned_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_collected_at   TIMESTAMPTZ NOT NULL DEFAULT now(),

    UNIQUE (player_id, roster_id)                  -- one assignment per character
);

CREATE INDEX idx_village_assignments_player ON village_assignments(player_id);

-- ── Manual Work State ────────────────────────────────────────────────────────
-- Tracks daily manual work action counts for diminishing-returns enforcement.

CREATE TABLE manual_work_state (
    player_id               UUID        PRIMARY KEY REFERENCES players(id) ON DELETE CASCADE,
    actions_today           INTEGER     NOT NULL DEFAULT 0,
    actions_reset_at        TIMESTAMPTZ NOT NULL DEFAULT now(),  -- midnight UTC of current day
    total_actions_lifetime  BIGINT      NOT NULL DEFAULT 0,
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ── Gacha Pull History ────────────────────────────────────────────────────────

CREATE TABLE pulls (
    id                  UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    player_id           UUID        NOT NULL REFERENCES players(id) ON DELETE CASCADE,
    banner_id           TEXT        NOT NULL,
    character_id        TEXT        NOT NULL,
    rarity              TEXT        NOT NULL CHECK (rarity IN ('R', 'SR', 'SSR', 'UR')),
    pity_count_at_pull  INTEGER     NOT NULL,
    was_soft_pity       BOOLEAN     NOT NULL DEFAULT false,
    was_hard_pity       BOOLEAN     NOT NULL DEFAULT false,
    was_featured        BOOLEAN     NOT NULL DEFAULT false,
    pulled_at           TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_pulls_player      ON pulls(player_id, pulled_at DESC);
CREATE INDEX idx_pulls_banner      ON pulls(banner_id, pulled_at DESC);

-- ── Pity Counters ─────────────────────────────────────────────────────────────
-- One row per (player, banner). Updated after every pull.

CREATE TABLE pity_counters (
    player_id               UUID    NOT NULL REFERENCES players(id) ON DELETE CASCADE,
    banner_id               TEXT    NOT NULL,
    current_pity            INTEGER NOT NULL DEFAULT 0,
    featured_guarantee_due  BOOLEAN NOT NULL DEFAULT false,  -- lost the 50/50; next SSR guaranteed
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT now(),

    PRIMARY KEY (player_id, banner_id)
);

-- ── Content Cache Tables ──────────────────────────────────────────────────────
-- These are populated by tools/import_content.py from the YAML content files.
-- They are NOT the source of truth (YAML files are); they are the runtime cache.

CREATE TABLE content_characters (
    id              TEXT        PRIMARY KEY,
    data            JSONB       NOT NULL,
    schema_version  TEXT        NOT NULL DEFAULT 'v1',
    imported_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE content_jobs (
    id              TEXT        PRIMARY KEY,
    data            JSONB       NOT NULL,
    schema_version  TEXT        NOT NULL DEFAULT 'v1',
    imported_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE content_resources (
    id              TEXT        PRIMARY KEY,
    data            JSONB       NOT NULL,
    schema_version  TEXT        NOT NULL DEFAULT 'v1',
    imported_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE content_banners (
    id              TEXT        PRIMARY KEY,
    data            JSONB       NOT NULL,
    active          BOOLEAN     NOT NULL DEFAULT false,
    schema_version  TEXT        NOT NULL DEFAULT 'v1',
    imported_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE content_events (
    id              TEXT        PRIMARY KEY,
    data            JSONB       NOT NULL,
    active          BOOLEAN     NOT NULL DEFAULT false,
    schema_version  TEXT        NOT NULL DEFAULT 'v1',
    imported_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ── Schema Version Tracking ───────────────────────────────────────────────────

CREATE TABLE schema_migrations (
    version     TEXT        PRIMARY KEY,
    applied_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO schema_migrations(version) VALUES ('001_initial_schema');

COMMIT;
