-- ══════════════════════════════════════════════════════
-- NOTIFICATION SYSTEM SQL
-- gametopupbd.top এর জন্য
-- phpMyAdmin এ import করুন
-- ══════════════════════════════════════════════════════

-- 1. Firebase Settings Table
CREATE TABLE IF NOT EXISTS `firebase_settings` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `server_key` text DEFAULT NULL,
  `sender_id` varchar(255) DEFAULT NULL,
  `api_url` varchar(255) NOT NULL DEFAULT 'https://fcm.googleapis.com/fcm/send',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default row
INSERT IGNORE INTO `firebase_settings` (`id`, `api_url`, `created_at`, `updated_at`)
VALUES (1, 'https://fcm.googleapis.com/fcm/send', NOW(), NOW());

-- 2. User FCM Tokens Table
CREATE TABLE IF NOT EXISTS `user_fcm_tokens` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `fcm_token` text NOT NULL,
  `device_type` enum('android','ios','web') NOT NULL DEFAULT 'android',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_fcm_tokens_user_id_foreign` (`user_id`),
  CONSTRAINT `user_fcm_tokens_user_id_foreign`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Notification Logs Table
CREATE TABLE IF NOT EXISTS `notification_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `send_to` enum('all','specific') NOT NULL DEFAULT 'all',
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `image_url` varchar(255) DEFAULT NULL,
  `custom_link` varchar(255) DEFAULT NULL,
  `status` enum('pending','sent','failed') NOT NULL DEFAULT 'pending',
  `response` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `notification_logs_user_id_foreign` (`user_id`),
  CONSTRAINT `notification_logs_user_id_foreign`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;