-- Birleştirilmiş Slot Game Veritabanı
-- Oluşturma Tarihi: 2025-06-09

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 */;

--
-- Veritabanı: `slot_game`
--
CREATE DATABASE IF NOT EXISTS `slot_game` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `slot_game`;

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

--
-- Tablo için tablo yapısı `app_settings`
--

CREATE TABLE `app_settings` (
  `id` int(11) UNSIGNED NOT NULL,
  `setting_name` varchar(50) NOT NULL,
  `setting_value` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_name` (`setting_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `app_settings` (`id`, `setting_name`, `setting_value`) VALUES
(1, 'db_version', '3'),
(2, 'min_bet', '1'),
(3, 'max_bet', '1000'),
(4, 'auto_spin_enabled', '1'),
(5, 'default_bet', '10');

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

--
-- Tablo için tablo yapısı `game_logs`
--

CREATE TABLE `game_logs` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `symbols_result` varchar(255) NOT NULL,
  `win_amount` decimal(10,2) NOT NULL,
  `bet_amount` decimal(10,2) NOT NULL,
  `played_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `deleted` tinyint(1) NOT NULL DEFAULT 0,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `deleted_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `deleted_by` (`deleted_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Tablo için tablo yapısı `number_assets`
--

CREATE TABLE `number_assets` (
  `id` int(11) NOT NULL,
  `number` int(11) NOT NULL,
  `image_path` varchar(255) NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `number` (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Tablo için tablo yapısı `sounds`
--

CREATE TABLE `sounds` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `type` enum('spin','win','lose','background') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `sounds` (`id`, `name`, `file_path`, `type`) VALUES
(1, 'Çevirme Sesi', 'assets/sound/spin.mp3', 'spin'),
(2, 'Kazanma Sesi', 'assets/sound/win.mp3', 'win'),
(3, 'Kaybetme Sesi', 'assets/sound/lose.mp3', 'lose'),
(4, 'Arkaplan Müziği', 'assets/sound/background.mp3', 'background');

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

--
-- Tablo için tablo yapısı `symbols`
--

CREATE TABLE `symbols` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `symbol` varchar(10) DEFAULT NULL,
  `image_path` varchar(255) NOT NULL,
  `animation_data` text DEFAULT NULL,
  `value` decimal(10,2) NOT NULL,
  `probability` decimal(5,2) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `reward_type` enum('pap','ticket') NOT NULL DEFAULT 'pap',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `symbols` (`id`, `name`, `symbol`, `image_path`, `animation_data`, `value`, `probability`, `is_active`, `reward_type`, `created_at`) VALUES
(1, 'Kiraz', '🍒', 'assets/img/cherry.png', NULL, 10.00, 20.00, 1, 'pap', '2025-06-09 01:24:54'),
(2, 'Limon', '🍋', 'assets/img/lemon.png', NULL, 15.00, 15.00, 1, 'pap', '2025-06-09 01:24:54'),
(3, 'Portakal', '🍊', 'assets/img/orange.png', NULL, 20.00, 12.00, 1, 'pap', '2025-06-09 01:24:54'),
(4, 'Üzüm', '🍇', 'assets/img/grape.png', NULL, 30.00, 8.00, 1, 'pap', '2025-06-09 01:24:54'),
(5, 'Karpuz', '🍉', 'assets/img/watermelon.png', NULL, 50.00, 5.00, 1, 'pap', '2025-06-09 01:24:54'),
(6, 'Yedili', '7️⃣', 'assets/img/seven.png', NULL, 75.00, 3.00, 1, 'ticket', '2025-06-09 01:24:54'),
(7, 'Elmas', '💎', 'assets/img/diamond.png', NULL, 100.00, 2.00, 1, 'ticket', '2025-06-09 01:24:54'),
(8, 'Slot', '🎰', 'assets/img/slot.png', NULL, 150.00, 1.00, 1, 'ticket', '2025-06-09 01:24:54');

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

--
-- Tablo için tablo yapısı `tokens`
--

CREATE TABLE `tokens` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `token` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `expires_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Tablo için tablo yapısı `transactions`
--

CREATE TABLE `transactions` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `type` enum('deposit','withdraw','win','lose') NOT NULL,
  `date` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Tablo için tablo yapısı `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `telegram_id` bigint(20) NOT NULL,
  `username` varchar(255) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `last_login` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `telegram_id` (`telegram_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Tablo için tablo yapısı `user_stats`
--

CREATE TABLE `user_stats` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `credits` int(11) NOT NULL DEFAULT 1000,
  `tickets` int(11) NOT NULL DEFAULT 10,
  `total_games` int(11) NOT NULL DEFAULT 0,
  `total_wins` int(11) NOT NULL DEFAULT 0,
  `total_losses` int(11) NOT NULL DEFAULT 0,
  `highest_win` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Tablo için tablo yapısı `verify_requests`
--

CREATE TABLE `verify_requests` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `type` enum('email','phone','password_reset') NOT NULL,
  `token_hash` varchar(255) NOT NULL,
  `new_value` varchar(255) NOT NULL,
  `current_value` varchar(255) DEFAULT NULL,
  `is_verified` tinyint(1) NOT NULL DEFAULT 0,
  `is_cancelled` tinyint(1) NOT NULL DEFAULT 0,
  `expires_at` datetime NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Dökümü yapılmış tablolar için kısıtlamalar
--

--
-- Tablo kısıtlamaları `game_logs`
--
ALTER TABLE `game_logs`
  ADD CONSTRAINT `game_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `game_logs_ibfk_2` FOREIGN KEY (`deleted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Tablo kısıtlamaları `tokens`
--
ALTER TABLE `tokens`
  ADD CONSTRAINT `tokens_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Tablo kısıtlamaları `transactions`
--
ALTER TABLE `transactions`
  ADD CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Tablo kısıtlamaları `user_stats`
--
ALTER TABLE `user_stats`
  ADD CONSTRAINT `user_stats_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Tablo kısıtlamaları `verify_requests`
--
ALTER TABLE `verify_requests`
  ADD CONSTRAINT `verify_requests_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

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 */; 