CREATE TABLE IF NOT EXISTS `game_saves` (
`campaign_id` VARCHAR(64) NOT NULL,
`player_name` VARCHAR(128) NOT NULL,
`difficulty` VARCHAR(16) NOT NULL,
`roster` JSON NOT NULL,
`missions_completed` INT DEFAULT 0,
`total_xp` INT DEFAULT 0,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`campaign_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
CREATE TABLE IF NOT EXISTS `game_replays` (
`replay_id` VARCHAR(64) NOT NULL,
`campaign_id` VARCHAR(64) NOT NULL,
`mission_id` VARCHAR(64) NOT NULL,
`player_name` VARCHAR(128) NOT NULL,
`difficulty` VARCHAR(16) NOT NULL,
`completed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`duration_seconds` INT,
`result` VARCHAR(16) NOT NULL,
`units_killed` INT DEFAULT 0,
`allies_killed` INT DEFAULT 0,
`xp_earned` INT DEFAULT 0,
`turns_taken` INT DEFAULT 0,
`actions` JSON NOT NULL,
`final_state` JSON NOT NULL,
PRIMARY KEY (`replay_id`),
KEY `idx_game_replays_campaign` (`campaign_id`),
CONSTRAINT `fk_game_replays_campaign`
FOREIGN KEY (`campaign_id`) REFERENCES `game_saves` (`campaign_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
CREATE TABLE IF NOT EXISTS `game_progression` (
`unit_id` VARCHAR(128) NOT NULL,
`campaign_id` VARCHAR(64) NOT NULL,
`name` VARCHAR(128) NOT NULL,
`class_name` VARCHAR(64) NOT NULL,
`level` INT DEFAULT 1,
`experience` INT DEFAULT 0,
`health_upgrade` INT DEFAULT 0,
`armor_upgrade` INT DEFAULT 0,
`ap_upgrade` INT DEFAULT 0,
`unlocked_abilities` JSON DEFAULT ('[]'),
`perma_death` BOOLEAN DEFAULT FALSE,
`death_mission_id` VARCHAR(64),
`death_round` INT,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`unit_id`),
KEY `idx_game_progression_campaign` (`campaign_id`),
CONSTRAINT `fk_game_progression_campaign`
FOREIGN KEY (`campaign_id`) REFERENCES `game_saves` (`campaign_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
INSERT INTO game_saves
(campaign_id, player_name, difficulty, roster)
VALUES
('{campaign_id}', '{player_name}', '{difficulty}', '{roster}')
SELECT * FROM game_saves WHERE campaign_id = '{campaign_id}'
INSERT INTO game_replays
(replay_id, campaign_id, mission_id, player_name, difficulty,
duration_seconds, result, units_killed, allies_killed, xp_earned,
turns_taken, actions, final_state)
VALUES
('{replay_id}', '{campaign_id}', '{mission_id}', '{player_name}',
'{difficulty}', {duration_seconds}, '{result}', {units_killed},
{allies_killed}, {xp_earned}, {turns_taken}, '{actions}', '{final_state}')
SELECT * FROM game_progression WHERE unit_id = '{unit_id}'
INSERT INTO game_progression
(unit_id, campaign_id, name, class_name, level, experience,
unlocked_abilities)
VALUES
('{unit_id}', '{campaign_id}', '{name}', '{class_name}',
{level}, {experience}, '{unlocked_abilities}')
ON DUPLICATE KEY UPDATE
level = VALUES(level),
experience = VALUES(experience),
unlocked_abilities = VALUES(unlocked_abilities)