-- phpMyAdmin SQL Dump
-- version 5.2.2
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Nov 16, 2025 at 06:18 PM
-- Server version: 11.4.8-MariaDB-cll-lve-log
-- PHP Version: 8.3.27

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `markxwyo_Asteroids`
--

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

--
-- Table structure for table `games`
--

CREATE TABLE `games` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `started_at` timestamp NULL DEFAULT current_timestamp(),
  `ended_at` timestamp NULL DEFAULT NULL,
  `final_score` int(11) DEFAULT 0,
  `final_round` int(11) DEFAULT 1,
  `is_completed` tinyint(1) DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `games`
--

INSERT INTO `games` (`id`, `user_id`, `started_at`, `ended_at`, `final_score`, `final_round`, `is_completed`) VALUES
(1, 3, '2025-11-06 02:07:09', NULL, 0, 1, 0),
(2, 3, '2025-11-06 02:09:27', NULL, 0, 1, 0),
(3, 3, '2025-11-06 00:27:53', NULL, 0, 1, 0),
(4, 3, '2025-11-13 04:19:21', '2025-11-13 04:19:47', 250, 1, 1),
(5, 3, '2025-11-13 23:23:16', '2025-11-13 23:24:28', 2770, 3, 1),
(6, 3, '2025-11-13 23:24:38', NULL, 0, 1, 0),
(7, 4, '2025-11-13 23:25:04', NULL, 0, 1, 0),
(8, 4, '2025-11-13 23:28:31', NULL, 0, 1, 0),
(9, 5, '2025-11-13 23:28:40', '2025-11-13 23:31:13', 3570, 4, 1),
(10, 4, '2025-11-13 23:31:23', '2025-11-13 23:31:56', 2120, 3, 1),
(11, 6, '2025-11-16 02:24:49', '2025-11-16 02:27:23', 10330, 6, 1),
(12, 6, '2025-11-16 05:10:20', '2025-11-16 05:12:46', 3710, 4, 1);

--
-- Triggers `games`
--
DELIMITER $$
CREATE TRIGGER `update_user_stats_on_game_end` AFTER UPDATE ON `games` FOR EACH ROW BEGIN
    IF NEW.ended_at IS NOT NULL AND OLD.ended_at IS NULL THEN
        INSERT INTO user_stats (
            user_id, 
            total_games_played, 
            total_score, 
            highest_score, 
            highest_round,
            last_played_at
        ) VALUES (
            NEW.user_id,
            1,
            NEW.final_score,
            NEW.final_score,
            NEW.final_round,
            NEW.ended_at
        )
        ON DUPLICATE KEY UPDATE
            total_games_played = total_games_played + 1,
            total_score = total_score + NEW.final_score,
            highest_score = GREATEST(highest_score, NEW.final_score),
            highest_round = GREATEST(highest_round, NEW.final_round),
            last_played_at = NEW.ended_at,
            updated_at = CURRENT_TIMESTAMP;
            
        -- Insert into high scores if it's a top score
        INSERT INTO high_scores (user_id, game_id, score, round_reached)
        VALUES (NEW.user_id, NEW.id, NEW.final_score, NEW.final_round);
    END IF;
END
$$
DELIMITER ;

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

--
-- Table structure for table `game_sessions`
--

CREATE TABLE `game_sessions` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `session_token` varchar(255) NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `expires_at` timestamp NULL DEFAULT (current_timestamp() + interval 24 hour)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

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

--
-- Table structure for table `high_scores`
--

CREATE TABLE `high_scores` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `game_id` int(11) NOT NULL,
  `score` int(11) NOT NULL,
  `round_reached` int(11) NOT NULL,
  `achieved_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `high_scores`
--

INSERT INTO `high_scores` (`id`, `user_id`, `game_id`, `score`, `round_reached`, `achieved_at`) VALUES
(1, 3, 4, 250, 1, '2025-11-13 04:19:47'),
(2, 3, 5, 2770, 3, '2025-11-13 23:24:28'),
(3, 5, 9, 3570, 4, '2025-11-13 23:31:13'),
(4, 4, 10, 2120, 3, '2025-11-13 23:31:56'),
(5, 6, 11, 10330, 6, '2025-11-16 02:27:23'),
(6, 6, 12, 3710, 4, '2025-11-16 05:12:46');

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

--
-- Stand-in structure for view `leaderboard`
-- (See below for the actual view)
--
CREATE TABLE `leaderboard` (
`username` varchar(50)
,`display_name` varchar(100)
,`score` int(11)
,`round_reached` int(11)
,`achieved_at` timestamp
,`rank` bigint(21)
);

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

--
-- Table structure for table `rounds`
--

CREATE TABLE `rounds` (
  `id` int(11) NOT NULL,
  `game_id` int(11) NOT NULL,
  `round_number` int(11) NOT NULL,
  `score` int(11) NOT NULL DEFAULT 0,
  `asteroids_destroyed` int(11) DEFAULT 0,
  `shots_fired` int(11) DEFAULT 0,
  `accuracy_percentage` decimal(5,2) DEFAULT 0.00,
  `completed_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `rounds`
--

INSERT INTO `rounds` (`id`, `game_id`, `round_number`, `score`, `asteroids_destroyed`, `shots_fired`, `accuracy_percentage`, `completed_at`) VALUES
(1, 1, 5, 8850, 0, 0, 0.00, '2025-11-06 02:09:22'),
(2, 2, 4, 7360, 0, 0, 0.00, '2025-11-06 02:10:30'),
(3, 4, 1, 250, 8, 54, 15.00, '2025-11-13 04:19:47'),
(4, 5, 0, 690, 19, 283, 7.00, '2025-11-13 23:24:12'),
(5, 5, 1, 1400, 35, 257, 14.00, '2025-11-13 23:24:21'),
(6, 5, 3, 2770, 75, 595, 13.00, '2025-11-13 23:24:28'),
(7, 7, 0, 1120, 28, 348, 8.00, '2025-11-13 23:25:27'),
(8, 7, 1, 1680, 42, 259, 16.00, '2025-11-13 23:25:36'),
(9, 7, 2, 1960, 49, 306, 16.00, '2025-11-13 23:25:58'),
(10, 8, 0, 1120, 28, 293, 10.00, '2025-11-13 23:28:42'),
(11, 9, 0, 920, 24, 228, 11.00, '2025-11-13 23:29:45'),
(12, 8, 1, 1530, 39, 234, 17.00, '2025-11-13 23:29:58'),
(13, 9, 1, 1330, 35, 125, 28.00, '2025-11-13 23:30:19'),
(14, 9, 2, 1200, 33, 207, 16.00, '2025-11-13 23:31:09'),
(15, 9, 4, 3570, 96, 573, 17.00, '2025-11-13 23:31:13'),
(16, 10, 0, 710, 18, 229, 8.00, '2025-11-13 23:31:34'),
(17, 10, 3, 2120, 56, 463, 12.00, '2025-11-13 23:31:56'),
(18, 10, 1, 1330, 35, 234, 15.00, '2025-11-13 23:31:56'),
(19, 11, 0, 640, 17, 51, 33.00, '2025-11-16 02:25:32'),
(20, 11, 1, 1680, 42, 369, 11.00, '2025-11-16 02:26:07'),
(21, 11, 2, 1910, 48, 514, 9.00, '2025-11-16 02:26:28'),
(22, 11, 3, 2190, 55, 400, 14.00, '2025-11-16 02:26:59'),
(23, 11, 4, 2390, 60, 417, 14.00, '2025-11-16 02:27:16'),
(24, 11, 6, 10330, 265, 1880, 14.00, '2025-11-16 02:27:23'),
(25, 12, 0, 1120, 28, 316, 9.00, '2025-11-16 05:10:36'),
(26, 12, 1, 680, 22, 210, 10.00, '2025-11-16 05:11:22'),
(27, 12, 2, 1910, 48, 558, 9.00, '2025-11-16 05:11:51'),
(28, 12, 4, 3710, 98, 1084, 9.00, '2025-11-16 05:12:46');

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

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `email` varchar(255) NOT NULL,
  `display_name` varchar(100) DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `username`, `email`, `display_name`, `password`, `created_at`, `updated_at`) VALUES
(1, 'admin', 'admin@asteroids.com', 'Administrator', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '2025-11-06 02:03:31', '2025-11-06 02:03:31'),
(2, 'player1', 'player@example.com', 'Space Cadet', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '2025-11-06 02:03:31', '2025-11-06 02:03:31'),
(3, 'CodeWiz1101', 'mgeden25@gmail.com', 'CodeWiz1101', '$2y$10$2q0hL5.95DuHVNzeFr6yPejvMY/w6TtEv/lmEZ1BCYJHaeQnxvu82', '2025-11-06 02:07:08', '2025-11-06 02:07:08'),
(4, 'rockhard', 'mgeden22@gmail.com', 'rockhard', '$2y$10$0yhzCaKn6FqK1UfDN2oqNOx9sBwjpkNH5lnyLmzI2DkfDlN0Kh6K2', '2025-11-13 23:25:04', '2025-11-13 23:25:04'),
(5, 'penispumper', 'penis@pumper.com', 'PenisPumper', '$2y$10$jcFn29iucLdjkLFXVjTh7eVqLBc/hAXaRTadCwvCCrYMGxvRnkbMi', '2025-11-13 23:28:40', '2025-11-13 23:28:40'),
(6, 'douchebag', 'killer12@gmial.com', 'douchebag', '$2y$10$FNVgbDmyfar937tWkyhNNuSLc4MrQwxbkIfEc6wd1AHNcpo3HAafS', '2025-11-16 02:24:49', '2025-11-16 02:24:49');

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

--
-- Stand-in structure for view `user_profiles`
-- (See below for the actual view)
--
CREATE TABLE `user_profiles` (
`id` int(11)
,`username` varchar(50)
,`display_name` varchar(100)
,`email` varchar(255)
,`member_since` timestamp
,`games_played` int(11)
,`best_score` int(11)
,`best_round` int(11)
,`accuracy` decimal(5,2)
);

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

--
-- Table structure for table `user_stats`
--

CREATE TABLE `user_stats` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `total_games_played` int(11) DEFAULT 0,
  `total_score` int(11) DEFAULT 0,
  `highest_score` int(11) DEFAULT 0,
  `highest_round` int(11) DEFAULT 1,
  `total_asteroids_destroyed` int(11) DEFAULT 0,
  `total_shots_fired` int(11) DEFAULT 0,
  `overall_accuracy` decimal(5,2) DEFAULT 0.00,
  `total_playtime_seconds` int(11) DEFAULT 0,
  `last_played_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `user_stats`
--

INSERT INTO `user_stats` (`id`, `user_id`, `total_games_played`, `total_score`, `highest_score`, `highest_round`, `total_asteroids_destroyed`, `total_shots_fired`, `overall_accuracy`, `total_playtime_seconds`, `last_played_at`, `created_at`, `updated_at`) VALUES
(1, 3, 2, 3020, 2770, 3, 0, 0, 0.00, 0, '2025-11-13 23:24:28', '2025-11-13 04:19:47', '2025-11-13 23:24:28'),
(2, 5, 1, 3570, 3570, 4, 0, 0, 0.00, 0, '2025-11-13 23:31:13', '2025-11-13 23:31:13', '2025-11-13 23:31:13'),
(3, 4, 1, 2120, 2120, 3, 0, 0, 0.00, 0, '2025-11-13 23:31:56', '2025-11-13 23:31:56', '2025-11-13 23:31:56'),
(4, 6, 2, 14040, 10330, 6, 0, 0, 0.00, 0, '2025-11-16 05:12:46', '2025-11-16 02:27:23', '2025-11-16 05:12:46');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `games`
--
ALTER TABLE `games`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user_games` (`user_id`),
  ADD KEY `idx_started_at` (`started_at`);

--
-- Indexes for table `game_sessions`
--
ALTER TABLE `game_sessions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `session_token` (`session_token`),
  ADD KEY `idx_session_token` (`session_token`),
  ADD KEY `idx_user_sessions` (`user_id`),
  ADD KEY `idx_expires_at` (`expires_at`);

--
-- Indexes for table `high_scores`
--
ALTER TABLE `high_scores`
  ADD PRIMARY KEY (`id`),
  ADD KEY `game_id` (`game_id`),
  ADD KEY `idx_user_high_scores` (`user_id`),
  ADD KEY `idx_score` (`score` DESC),
  ADD KEY `idx_achieved_at` (`achieved_at` DESC);

--
-- Indexes for table `rounds`
--
ALTER TABLE `rounds`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_game_round` (`game_id`,`round_number`),
  ADD KEY `idx_game_rounds` (`game_id`),
  ADD KEY `idx_round_number` (`round_number`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `idx_username` (`username`),
  ADD KEY `idx_email` (`email`);

--
-- Indexes for table `user_stats`
--
ALTER TABLE `user_stats`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `user_id` (`user_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `games`
--
ALTER TABLE `games`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- AUTO_INCREMENT for table `game_sessions`
--
ALTER TABLE `game_sessions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `high_scores`
--
ALTER TABLE `high_scores`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `rounds`
--
ALTER TABLE `rounds`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=29;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `user_stats`
--
ALTER TABLE `user_stats`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

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

--
-- Structure for view `leaderboard`
--
DROP TABLE IF EXISTS `leaderboard`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `leaderboard`  AS SELECT `u`.`username` AS `username`, `u`.`display_name` AS `display_name`, `hs`.`score` AS `score`, `hs`.`round_reached` AS `round_reached`, `hs`.`achieved_at` AS `achieved_at`, row_number() over ( order by `hs`.`score` desc) AS `rank` FROM (`high_scores` `hs` join `users` `u` on(`hs`.`user_id` = `u`.`id`)) ORDER BY `hs`.`score` DESC ;

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

--
-- Structure for view `user_profiles`
--
DROP TABLE IF EXISTS `user_profiles`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `user_profiles`  AS SELECT `u`.`id` AS `id`, `u`.`username` AS `username`, `u`.`display_name` AS `display_name`, `u`.`email` AS `email`, `u`.`created_at` AS `member_since`, coalesce(`us`.`total_games_played`,0) AS `games_played`, coalesce(`us`.`highest_score`,0) AS `best_score`, coalesce(`us`.`highest_round`,1) AS `best_round`, coalesce(`us`.`overall_accuracy`,0) AS `accuracy` FROM (`users` `u` left join `user_stats` `us` on(`u`.`id` = `us`.`user_id`)) ;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
