j'"dZddlmZdZdZdZdS)z Game Design Matrix Schema & DDL for FreeExile. Implements SQLite tables with strict foreign keys, CHECK constraints, and cross-cutting views. ) annotationszS PRAGMA foreign_keys = ON; PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; u -- 1. Story Acts (Hồi Cốt Truyện) CREATE TABLE IF NOT EXISTS story_acts ( act_id TEXT PRIMARY KEY, act_order INTEGER NOT NULL UNIQUE, title TEXT NOT NULL, subtitle TEXT NOT NULL, level_min INTEGER NOT NULL CHECK (level_min >= 1), level_max INTEGER NOT NULL CHECK (level_max >= level_min), primary_zone_id TEXT NOT NULL, climax_boss_id TEXT NOT NULL, climax_boss_name TEXT NOT NULL, description TEXT NOT NULL ); -- 2. Zones & Maps (Bản Đồ, Doanh Trại & Vùng Hoang Dã) CREATE TABLE IF NOT EXISTS zones ( zone_id TEXT PRIMARY KEY, act_id TEXT NOT NULL REFERENCES story_acts(act_id) ON DELETE CASCADE, name TEXT NOT NULL, zone_type TEXT NOT NULL, environment TEXT NOT NULL, min_level INTEGER NOT NULL CHECK (min_level >= 1), max_level INTEGER NOT NULL CHECK (max_level >= min_level), bounds_width REAL NOT NULL DEFAULT 800.0, bounds_height REAL NOT NULL DEFAULT 800.0, default_spawn_x REAL NOT NULL DEFAULT 0.0, default_spawn_y REAL NOT NULL DEFAULT 0.0 ); -- 3. NPCs & Dialogue Anchors CREATE TABLE IF NOT EXISTS npcs ( npc_id TEXT PRIMARY KEY, name TEXT NOT NULL, title TEXT NOT NULL, role TEXT NOT NULL, zone_id TEXT NOT NULL REFERENCES zones(zone_id) ON DELETE CASCADE, x REAL NOT NULL, y REAL NOT NULL, dialogue_tree_id TEXT NOT NULL ); -- 4. Monster Archetypes & Genus CREATE TABLE IF NOT EXISTS monster_archetypes ( archetype_id TEXT PRIMARY KEY, genus TEXT NOT NULL, sub_species_code TEXT NOT NULL, name TEXT NOT NULL, base_hp REAL NOT NULL CHECK (base_hp > 0), base_dmg REAL NOT NULL CHECK (base_dmg > 0), base_scale REAL NOT NULL CHECK (base_scale > 0), native_element TEXT NOT NULL, min_level INTEGER NOT NULL DEFAULT 1 ); -- 5. Quests & Milestones (Strict Level & Prerequisite Gating) CREATE TABLE IF NOT EXISTS quests ( quest_id TEXT PRIMARY KEY, act_id TEXT NOT NULL REFERENCES story_acts(act_id) ON DELETE CASCADE, title TEXT NOT NULL, quest_type TEXT NOT NULL, required_level INTEGER NOT NULL CHECK (required_level BETWEEN 1 AND 100), zone_id TEXT REFERENCES zones(zone_id) ON DELETE SET NULL, prerequisite_quest_id TEXT REFERENCES quests(quest_id) ON DELETE RESTRICT, target_monster_id TEXT, target_count INTEGER NOT NULL DEFAULT 1, reward_exp INTEGER NOT NULL CHECK (reward_exp >= 0), reward_meridian_points INTEGER NOT NULL DEFAULT 0, reward_title TEXT, reward_items_json TEXT NOT NULL DEFAULT '[]' ); -- 6. Weapon Bases & Equipment Blueprints CREATE TABLE IF NOT EXISTS weapon_bases ( weapon_id TEXT PRIMARY KEY, name_vi TEXT NOT NULL, name_en TEXT NOT NULL, weapon_class TEXT NOT NULL, weapon_category TEXT NOT NULL, grip_style TEXT NOT NULL, sect TEXT NOT NULL, clan TEXT NOT NULL, element TEXT NOT NULL, required_level INTEGER NOT NULL CHECK (required_level >= 1), base_dmg_min REAL NOT NULL CHECK (base_dmg_min >= 0), base_dmg_max REAL NOT NULL CHECK (base_dmg_max >= base_dmg_min), attack_speed REAL NOT NULL CHECK (attack_speed > 0), crit_chance REAL NOT NULL CHECK (crit_chance >= 0) ); -- 7. Active Martial Skills (Chân Võ Đan) CREATE TABLE IF NOT EXISTS skills ( skill_id INTEGER PRIMARY KEY, skill_name TEXT NOT NULL, element TEXT NOT NULL, sect TEXT NOT NULL, weapon_requirement TEXT NOT NULL, base_damage REAL NOT NULL CHECK (base_damage >= 0), energy_cost INTEGER NOT NULL CHECK (energy_cost >= 0), cooldown_ms INTEGER NOT NULL CHECK (cooldown_ms >= 0), combo_role TEXT NOT NULL, tags_json TEXT NOT NULL ); -- 8. 15-Tier Affix Pool & Weighting Sinks CREATE TABLE IF NOT EXISTS affix_tiers ( mod_id TEXT PRIMARY KEY, name TEXT NOT NULL, affix_type TEXT NOT NULL, tier INTEGER NOT NULL CHECK (tier BETWEEN 1 AND 15), required_ilvl INTEGER NOT NULL CHECK (required_ilvl >= 1), min_value REAL NOT NULL, max_value REAL NOT NULL CHECK (max_value >= min_value), weight INTEGER NOT NULL CHECK (weight > 0), allowed_slots_json TEXT NOT NULL ); -- 9. Mathematical Progression Benchmarks per Level CREATE TABLE IF NOT EXISTS progression_benchmarks ( level INTEGER PRIMARY KEY CHECK (level BETWEEN 1 AND 100), target_exp INTEGER NOT NULL CHECK (target_exp >= 0), exp_to_next_level INTEGER NOT NULL CHECK (exp_to_next_level >= 0), cumulative_exp INTEGER NOT NULL CHECK (cumulative_exp >= 0), player_base_hp REAL NOT NULL CHECK (player_base_hp > 0), player_benchmark_dps REAL NOT NULL CHECK (player_benchmark_dps > 0), monster_base_hp REAL NOT NULL CHECK (monster_base_hp > 0), monster_base_dps REAL NOT NULL CHECK (monster_base_dps > 0), max_affix_tier_allowed INTEGER NOT NULL CHECK (max_affix_tier_allowed BETWEEN 1 AND 15), death_penalty_ratio REAL NOT NULL DEFAULT 0.0 CHECK (death_penalty_ratio BETWEEN 0.0 AND 1.0), level_gap_safe_range INTEGER NOT NULL DEFAULT 5 CHECK (level_gap_safe_range >= 0), level_gap_penalty_exp REAL NOT NULL DEFAULT 0.60 CHECK (level_gap_penalty_exp > 0), monster_benchmark_exp INTEGER NOT NULL DEFAULT 25 CHECK (monster_benchmark_exp > 0) ); -- 10. Cross-System Relational Graph CREATE TABLE IF NOT EXISTS cross_relationships ( relation_id TEXT PRIMARY KEY, source_type TEXT NOT NULL, source_id TEXT NOT NULL, relation_type TEXT NOT NULL, target_type TEXT NOT NULL, target_id TEXT NOT NULL, metadata_json TEXT NOT NULL DEFAULT '{}' ); -- Indexes for lightning-fast cross-referencing CREATE INDEX IF NOT EXISTS idx_zones_act ON zones(act_id); CREATE INDEX IF NOT EXISTS idx_npcs_zone ON npcs(zone_id); CREATE INDEX IF NOT EXISTS idx_quests_act ON quests(act_id); CREATE INDEX IF NOT EXISTS idx_quests_prereq ON quests(prerequisite_quest_id); CREATE INDEX IF NOT EXISTS idx_quests_zone ON quests(zone_id); CREATE INDEX IF NOT EXISTS idx_affix_tier ON affix_tiers(tier, required_ilvl); CREATE INDEX IF NOT EXISTS idx_rel_source ON cross_relationships(source_type, source_id); CREATE INDEX IF NOT EXISTS idx_rel_target ON cross_relationships(target_type, target_id); -- 11. Party Scaling Matrix (PoE2 6-Player Scaled Modifiers) CREATE TABLE IF NOT EXISTS party_scaling_matrix ( party_size INTEGER PRIMARY KEY CHECK (party_size BETWEEN 1 AND 6), hp_multiplier REAL NOT NULL CHECK (hp_multiplier >= 1.0), exp_bonus_pct REAL NOT NULL CHECK (exp_bonus_pct >= 0.0), item_quantity_bonus_pct REAL NOT NULL CHECK (item_quantity_bonus_pct >= 0.0), currency_quantity_bonus_pct REAL NOT NULL CHECK (currency_quantity_bonus_pct >= 0.0), rarity_bonus_pct REAL NOT NULL CHECK (rarity_bonus_pct >= 0.0), nearby_radius REAL NOT NULL DEFAULT 15.0 CHECK (nearby_radius > 0.0), description TEXT NOT NULL ); -- 12. Loot Allocation Modes (3 Canonical Modes) CREATE TABLE IF NOT EXISTS loot_allocation_modes ( mode_id TEXT PRIMARY KEY, name TEXT NOT NULL, timeout_seconds REAL NOT NULL DEFAULT 0.0 CHECK (timeout_seconds >= 0.0), is_permanent INTEGER NOT NULL CHECK (is_permanent IN (0, 1)), description TEXT NOT NULL ); -- 13. Teleport Configurations (Safe Channeling & Interrupt Rules) CREATE TABLE IF NOT EXISTS teleport_configs ( action_type TEXT PRIMARY KEY, cast_time_seconds REAL NOT NULL CHECK (cast_time_seconds >= 0.0), cooldown_seconds REAL NOT NULL CHECK (cooldown_seconds >= 0.0), cancel_on_movement INTEGER NOT NULL CHECK (cancel_on_movement IN (0, 1)), cancel_on_damage INTEGER NOT NULL CHECK (cancel_on_damage IN (0, 1)), description TEXT NOT NULL ); -- 14. Portal Quarantine Rules (Anti-Exploit Boss & Secret Chamber Lockout) CREATE TABLE IF NOT EXISTS portal_quarantine_rules ( rule_id TEXT PRIMARY KEY, zone_type TEXT NOT NULL, boss_lockout INTEGER NOT NULL CHECK (boss_lockout IN (0, 1)), secret_chamber_lockout INTEGER NOT NULL CHECK (secret_chamber_lockout IN (0, 1)), max_portals INTEGER NOT NULL CHECK (max_portals >= 0), description TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_portal_quarantine_zone ON portal_quarantine_rules(zone_type); a\ -- View 1: Quest Ecosystem (Combines Act, Zone, Prereq, and Rewards) CREATE VIEW IF NOT EXISTS v_quest_ecosystem AS SELECT q.quest_id, q.title AS quest_title, q.act_id, a.title AS act_title, q.required_level, q.zone_id, z.name AS zone_name, z.min_level AS zone_min_level, z.max_level AS zone_max_level, q.prerequisite_quest_id, pq.title AS prerequisite_quest_title, q.target_monster_id, q.target_count, q.reward_exp, q.reward_meridian_points, q.reward_title FROM quests q JOIN story_acts a ON q.act_id = a.act_id LEFT JOIN zones z ON q.zone_id = z.zone_id LEFT JOIN quests pq ON q.prerequisite_quest_id = pq.quest_id; -- View 2: Zone Narrative & Entity Summary CREATE VIEW IF NOT EXISTS v_zone_summary AS SELECT z.zone_id, z.name AS zone_name, z.act_id, a.title AS act_title, z.min_level, z.max_level, COUNT(DISTINCT n.npc_id) AS npc_count, COUNT(DISTINCT q.quest_id) AS quest_count FROM zones z JOIN story_acts a ON z.act_id = a.act_id LEFT JOIN npcs n ON z.zone_id = n.zone_id LEFT JOIN quests q ON z.zone_id = q.zone_id GROUP BY z.zone_id; -- View 3: Act Narrative Flow & Progression CREATE VIEW IF NOT EXISTS v_act_narrative_flow AS SELECT a.act_id, a.act_order, a.title, a.level_min, a.level_max, a.primary_zone_id, z.name AS primary_zone_name, a.climax_boss_id, a.climax_boss_name, COUNT(DISTINCT q.quest_id) AS total_quests FROM story_acts a LEFT JOIN zones z ON a.primary_zone_id = z.zone_id LEFT JOIN quests q ON a.act_id = q.act_id GROUP BY a.act_id ORDER BY a.act_order ASC; N)__doc__ __future__rSQL_PRAGMA_SETUPSQL_CREATE_TABLESSQL_CREATE_VIEWS?C:\Projects\FreeExile\server\world\game_design_matrix_schema.pyr sC #""""" EN<r