-- https://www.phpmyadmin.net/
--

START TRANSACTION;



-- --------------------------------------------------------

--
-- Table structure for table `characters`
--

CREATE TABLE `characters` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `account_id` bigint(20) UNSIGNED NOT NULL,
  `character_name` varchar(32) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `last_played_at` datetime DEFAULT NULL,
  `level` int(10) UNSIGNED NOT NULL DEFAULT 1,
  `experience` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `position_x` float NOT NULL DEFAULT 0,
  `position_y` float NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `characters`
--

INSERT INTO `characters` (`id`, `account_id`, `character_name`, `created_at`, `last_played_at`, `level`, `experience`, `position_x`, `position_y`) VALUES
(1, 1, 'TestChar', '2026-06-17 15:48:58', NULL, 1, 0, 10, 10),
(2, 2, 'Unity Tester', '2026-06-17 23:13:08', NULL, 1, 0, 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `character_skills`
--

CREATE TABLE `character_skills` (
  `character_id` bigint(20) UNSIGNED NOT NULL,
  `skill_id` int(10) UNSIGNED NOT NULL,
  `level` int(10) UNSIGNED NOT NULL DEFAULT 1,
  `experience` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `character_skills`
--

INSERT INTO `character_skills` (`character_id`, `skill_id`, `level`, `experience`, `updated_at`) VALUES
(1, 1, 1, 0, '2026-06-17 15:48:58'),
(1, 2, 1, 0, '2026-06-17 15:48:58'),
(1, 3, 1, 0, '2026-06-17 15:48:58'),
(1, 4, 1, 0, '2026-06-17 15:48:58'),
(1, 5, 1, 0, '2026-06-17 15:48:58'),
(1, 6, 1, 0, '2026-06-17 15:48:58'),
(2, 1, 1, 0, '2026-06-17 23:13:08'),
(2, 2, 1, 0, '2026-06-17 23:13:08'),
(2, 3, 1, 0, '2026-06-17 23:13:08'),
(2, 4, 1, 0, '2026-06-17 23:13:08'),
(2, 5, 1, 0, '2026-06-17 23:13:08'),
(2, 6, 1, 0, '2026-06-17 23:13:08');

-- --------------------------------------------------------

--
-- Table structure for table `skills`
--

CREATE TABLE `skills` (
  `id` int(10) UNSIGNED NOT NULL,
  `skill_key` varchar(50) NOT NULL,
  `display_name` varchar(50) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `skills`
--

INSERT INTO `skills` (`id`, `skill_key`, `display_name`, `created_at`) VALUES
(1, 'melee', 'Melee', '2026-06-17 15:30:01'),
(2, 'ranged', 'Ranged', '2026-06-17 15:30:01'),
(3, 'defence', 'Defence', '2026-06-17 15:30:01'),
(4, 'mining', 'Mining', '2026-06-17 15:30:01'),
(5, 'woodcutting', 'Woodcutting', '2026-06-17 15:30:01'),
(6, 'fishing', 'Fishing', '2026-06-17 15:30:01');

--
-- Indexes for table `characters`
--
ALTER TABLE `characters`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_characters_name` (`character_name`),
  ADD KEY `idx_characters_account_id` (`account_id`);

--
-- Indexes for table `character_skills`
--
ALTER TABLE `character_skills`
  ADD PRIMARY KEY (`character_id`,`skill_id`),
  ADD KEY `fk_character_skills_skill` (`skill_id`);

--
-- Indexes for table `skills`
--
ALTER TABLE `skills`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_skills_skill_key` (`skill_key`);

--
-- AUTO_INCREMENT for table `characters`
--
ALTER TABLE `characters`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `skills`
--
ALTER TABLE `skills`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- Constraints for table `characters`
--
ALTER TABLE `characters`
  ADD CONSTRAINT `fk_characters_account` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `character_skills`
--
ALTER TABLE `character_skills`
  ADD CONSTRAINT `fk_character_skills_character` FOREIGN KEY (`character_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_character_skills_skill` FOREIGN KEY (`skill_id`) REFERENCES `skills` (`id`) ON DELETE CASCADE;
COMMIT;

