lawless/database/migrations/003_expand_manuals.up.sql

-- T003: 扩展功法系统 - 新增神通/秘术功法类型
-- 对齐GDD-04 v1.12 新增决策✅22-✅26

-- 1. 扩展manuals表的category字段值（原有universal/profession/racial/unique）
-- 新增：divine（神通）、forbidden（秘术）

-- 2. 创建功法类型配置表
CREATE TABLE IF NOT EXISTS manual_types (
    id              VARCHAR(32) PRIMARY KEY,
    name            VARCHAR(64) NOT NULL,
    description     TEXT,
    max_equipped     SMALLINT NOT NULL DEFAULT 0, -- 0=不限（自然约束）
    acquisition_req JSONB NOT NULL DEFAULT '{}',  -- 获取条件
    usage_cost      JSONB NOT NULL DEFAULT '{}',  -- 使用代价
    cooldown_config JSONB NOT NULL DEFAULT '{}',  -- 冷却配置
    conflict_rules  JSONB NOT NULL DEFAULT '[]',  -- 冲突规则
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

-- 3. 插入功法类型数据
INSERT INTO manual_types (id, name, description, max_equipped, acquisition_req, usage_cost, cooldown_config, conflict_rules) VALUES
-- 基础功法类型
('heart_method', '主修心法', '决定内力积累效率、内力上限、内力纯度基线', 0, '{}', '{}', '{}', '[]'),
('combat_skill', '战技功法', '修炼后解锁战斗招式（普攻强化/主动技/被动）', 0, '{}', '{}', '{}', '[]'),
('auxiliary', '辅助功法', '提供修炼/游历/纯化等增益', 0, '{}', '{}', '{}', '[]'),
('production', '生产联动功法', '与生活技能联动', 0, '{}', '{}', '{}', '[]'),
-- 新增功法类型（T002审阅）
('divine_power', '神通功法', '修炼到高境界后领悟的超凡能力，威力远超普通战技', 0,
 '{"min_realm":4,"min_manual_layer":7,"require_epiphany":true}',
 '{"energy_cost":"high","special_resource":"精血/寿元","battle_cooldown":"24-72h"}',
 '{"battle_cooldown_hours":48}',
 '[]'),
('forbidden_art', '秘术功法', '禁忌之术，代价高昂但威力极大', 0,
 '{"min_realm":5,"require_special_bloodline":true,"require_special_item":true}',
 '{"energy_cost":"extreme","karma_cost":"10-50业力","lifespan_cost":"数年寿元"}',
 '{"battle_cooldown_hours":168}',
 '[]');

-- 4. 扩展manuals表的category约束
-- 注意：原有category字段是VARCHAR(32)，无需修改，只需确保新值被接受

-- 5. 创建法宝对战系统表（GDD-03 附.A）
CREATE TABLE IF NOT EXISTS artifacts (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    character_id    UUID NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
    item_id         VARCHAR(64) NOT NULL REFERENCES items(id),
    artifact_type   VARCHAR(32) NOT NULL, -- weapon/armor/accessory
    grade           VARCHAR(16) NOT NULL, -- mortal/yellow/xuan/di/celestial/immortal
    refinement_level SMALLINT NOT NULL DEFAULT 0,
    spirit_level    SMALLINT NOT NULL DEFAULT 0, -- 器灵等级
    spirit_name     VARCHAR(64),
    bound           BOOLEAN NOT NULL DEFAULT false,
    stats           JSONB NOT NULL DEFAULT '{}',
    skills          JSONB NOT NULL DEFAULT '[]',
    durability      INT NOT NULL DEFAULT 100,
    max_durability  INT NOT NULL DEFAULT 100,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_artifacts_character ON artifacts(character_id);
CREATE INDEX idx_artifacts_grade ON artifacts(grade, artifact_type);

-- 6. 创建飞剑系统表（GDD-03 附.D）
CREATE TABLE IF NOT EXISTS flying_swords (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    character_id    UUID NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
    item_id         VARCHAR(64) NOT NULL REFERENCES items(id),
    grade           VARCHAR(16) NOT NULL, -- mortal/spirit/celestial/immortal/sacred
    refinement_level SMALLINT NOT NULL DEFAULT 0,
    sword_art_level SMALLINT NOT NULL DEFAULT 0, -- 御剑术等级
    stats           JSONB NOT NULL DEFAULT '{}',
    skills          JSONB NOT NULL DEFAULT '[]',
    bound           BOOLEAN NOT NULL DEFAULT false,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_flying_swords_character ON flying_swords(character_id);

-- 7. 创建阵法系统表（GDD-03 附.C / GDD-27 二）
CREATE TABLE IF NOT EXISTS formations (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    character_id    UUID REFERENCES characters(id) ON DELETE SET NULL,
    guild_id        UUID REFERENCES guilds(id) ON DELETE SET NULL,
    zone_id         UUID REFERENCES zones(id) ON DELETE SET NULL,
    formation_type  VARCHAR(32) NOT NULL, -- attack/defense/control/encapsulate/support/guild_defense
    name            VARCHAR(128) NOT NULL,
    grade           VARCHAR(16) NOT NULL, -- mortal/yellow/xuan/di/celestial/immortal
    level           SMALLINT NOT NULL DEFAULT 1,
    materials       JSONB NOT NULL DEFAULT '[]',
    stats           JSONB NOT NULL DEFAULT '{}',
    duration_ticks  INT NOT NULL DEFAULT 0, -- 0=永久
    maintenance_cost NUMERIC(20,4) NOT NULL DEFAULT 0,
    is_active       BOOLEAN NOT NULL DEFAULT false,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    expires_at      TIMESTAMPTZ
);

CREATE INDEX idx_formations_character ON formations(character_id);
CREATE INDEX idx_formations_guild ON formations(guild_id);
CREATE INDEX idx_formations_zone ON formations(zone_id);

-- 8. 创建符箓系统表（GDD-03 附.E）
CREATE TABLE IF NOT EXISTS talismans (
    id              VARCHAR(64) PRIMARY KEY,
    name            VARCHAR(128) NOT NULL,
    talisman_type   VARCHAR(32) NOT NULL, -- attack/defense/auxiliary/control/special
    grade           VARCHAR(16) NOT NULL, -- mortal/yellow/xuan/di/celestial/immortal
    effect_data     JSONB NOT NULL DEFAULT '{}',
    crafting_recipe JSONB NOT NULL DEFAULT '{}',
    usage_limit     SMALLINT NOT NULL DEFAULT 1, -- 每场战斗使用次数
    cooldown_ticks  INT NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS character_talismans (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    character_id    UUID NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
    talisman_id     VARCHAR(64) NOT NULL REFERENCES talismans(id),
    quantity        INT NOT NULL DEFAULT 1,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_character_talismans_character ON character_talismans(character_id);

-- 9. 创建法宝套装系统表（GDD-05 4.9）
CREATE TABLE IF NOT EXISTS artifact_sets (
    id              VARCHAR(64) PRIMARY KEY,
    name            VARCHAR(128) NOT NULL,
    description     TEXT,
    piece_count     SMALLINT NOT NULL DEFAULT 6,
    set_bonus_2     JSONB NOT NULL DEFAULT '{}',
    set_bonus_4     JSONB NOT NULL DEFAULT '{}',
    set_bonus_6     JSONB NOT NULL DEFAULT '{}',
    grade           VARCHAR(16) NOT NULL,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

-- 10. 扩展energy_purity和karma系统（GDD-04/GDD-02）
-- 在characters表新增字段（通过ALTER TABLE）
ALTER TABLE characters ADD COLUMN IF NOT EXISTS karma_accrued INT NOT NULL DEFAULT 0;
ALTER TABLE characters ADD COLUMN IF NOT EXISTS energy_purity NUMERIC(5,4) NOT NULL DEFAULT 1.0;
ALTER TABLE characters ADD COLUMN IF NOT EXISTS dantox_level INT NOT NULL DEFAULT 0;
ALTER TABLE characters ADD COLUMN IF NOT EXISTS lifespan_current INT NOT NULL DEFAULT 1000;
ALTER TABLE characters ADD COLUMN IF NOT EXISTS lifespan_max INT NOT NULL DEFAULT 1000;

-- 11. 创建修炼瓶颈记录表（GDD-04 8.1.4）
CREATE TABLE IF NOT EXISTS cultivation_bottlenecks (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    character_id    UUID NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
    manual_id       VARCHAR(64) NOT NULL,
    bottleneck_layer SMALLINT NOT NULL,
    is_breakthrough BOOLEAN NOT NULL DEFAULT false,
    breakthrough_method VARCHAR(32), -- pill/event/mentor/meditation/epiphany
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    breakthrough_at TIMESTAMPTZ
);

CREATE INDEX idx_cultivation_bottlenecks_character ON cultivation_bottlenecks(character_id, is_breakthrough);

-- 12. 创建走火入魔记录表（GDD-04 8.1.5）
CREATE TABLE IF NOT EXISTS qi_deviation_records (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    character_id    UUID NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
    severity        VARCHAR(16) NOT NULL, -- mild/moderate/severe/extreme
    cause           VARCHAR(32) NOT NULL, -- incompatible_manual/high_dantox/low_sanity/long_cultivation/bottleneck_fail
    stat_penalty    JSONB NOT NULL DEFAULT '{}',
    duration_hours  INT NOT NULL DEFAULT 4,
    recovery_method VARCHAR(32),
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    recovered_at    TIMESTAMPTZ
);

CREATE INDEX idx_qi_deviation_character ON qi_deviation_records(character_id, severity);

-- 13. 创建闭关修炼记录表（GDD-04 8.1.6）
CREATE TABLE IF NOT EXISTS closed_door_sessions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    character_id    UUID NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
    session_type    VARCHAR(16) NOT NULL, -- short/medium/long
    location_id     UUID, -- 洞府/宗门闭关室/福地
    duration_hours  INT NOT NULL,
    cost_spirit_stones NUMERIC(20,4) NOT NULL DEFAULT 0,
    proficiency_bonus NUMERIC(5,4) NOT NULL DEFAULT 0,
    epiphany_bonus  NUMERIC(5,4) NOT NULL DEFAULT 0,
    bottleneck_bonus NUMERIC(5,4) NOT NULL DEFAULT 0,
    status          VARCHAR(16) NOT NULL DEFAULT 'active', -- active/completed/interrupted
    special_event   JSONB,
    started_at      TIMESTAMPTZ DEFAULT NOW(),
    ends_at         TIMESTAMPTZ NOT NULL,
    completed_at    TIMESTAMPTZ
);

CREATE INDEX idx_closed_door_character ON closed_door_sessions(character_id, status);