-- Migration: Add FCM tokens to students table and create notifications table
-- Date: 2026-03-18
-- Purpose: Enable push notifications to students via FCM tokens stored in students table

-- Add fcm_token column to students table if it doesn't exist
ALTER TABLE `students` 
ADD COLUMN `fcm_token` VARCHAR(500) DEFAULT NULL COMMENT 'Firebase Cloud Messaging token for push notifications' AFTER `updated_at`,
ADD COLUMN `fcm_token_updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When FCM token was last updated' AFTER `fcm_token`;

-- Create indexes for faster queries
ALTER TABLE `students` 
ADD INDEX `idx_fcm_token` (`fcm_token`);

-- Create notifications table to store notification history
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255) NOT NULL,
  `body` TEXT NOT NULL,
  `type` ENUM('general', 'class', 'announcement', 'assignment', 'attendance', 'results', 'event') NOT NULL DEFAULT 'general',
  `priority` ENUM('low', 'medium', 'high') NOT NULL DEFAULT 'medium',
  `target_class_id` BIGINT(20) UNSIGNED DEFAULT NULL COMMENT 'If NULL, notification is for all users',
  `target_roles` VARCHAR(255) DEFAULT NULL COMMENT 'Comma-separated: student,parent,teacher,admin',
  `related_id` BIGINT(20) UNSIGNED DEFAULT NULL COMMENT 'ID of related record (announcement, assignment, etc)',
  `related_type` VARCHAR(50) DEFAULT NULL COMMENT 'Type of related record: announcement, assignment, etc',
  `sent_count` INT(11) DEFAULT 0 COMMENT 'Number of devices notified',
  `failed_count` INT(11) DEFAULT 0 COMMENT 'Number of failed sends',
  `created_by` BIGINT(20) UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=active, 0=deleted',
  
  INDEX `idx_type` (`type`),
  INDEX `idx_class` (`target_class_id`),
  INDEX `idx_created_at` (`created_at`),
  INDEX `idx_related` (`related_id`, `related_type`),
  CONSTRAINT `fk_notifications_class` FOREIGN KEY (`target_class_id`) REFERENCES `classes`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create notification_devices table to track which devices received which notifications
CREATE TABLE IF NOT EXISTS `notification_devices` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `notification_id` BIGINT(20) UNSIGNED NOT NULL,
  `student_id` BIGINT(20) UNSIGNED NOT NULL,
  `fcm_token` VARCHAR(500) NOT NULL,
  `firebase_message_id` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('sent', 'failed', 'invalid_token') NOT NULL DEFAULT 'sent',
  `error_message` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  INDEX `idx_notification` (`notification_id`),
  INDEX `idx_student` (`student_id`),
  INDEX `idx_status` (`status`),
  CONSTRAINT `fk_nd_notification` FOREIGN KEY (`notification_id`) REFERENCES `notifications`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_nd_student` FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FCM tokens are stored ONLY in students table, not in users table