-- Push Notifications System Migration
-- This migration adds support for push notifications with Firebase Cloud Messaging (FCM)
-- Generated: March 18, 2026

-- ============================================
-- Step 1: Update announcements table
-- ============================================
-- Add status and updated_at columns if they don't exist
ALTER TABLE announcements ADD COLUMN IF NOT EXISTS status tinyint(1) DEFAULT 1 COMMENT '1=active, 0=deleted';
ALTER TABLE announcements ADD COLUMN IF NOT EXISTS updated_at timestamp DEFAULT current_timestamp() ON UPDATE current_timestamp();

-- ============================================
-- Step 2: Create notifications table
-- ============================================
-- Stores all push notifications sent to users (separate from announcements)
CREATE TABLE IF NOT EXISTS notifications (
  id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  
  -- Core data
  title varchar(200) NOT NULL,
  body text NOT NULL,
  
  -- Targeting
  target_type enum('all','class','role','specific_user') DEFAULT 'all' COMMENT 'Who this notification targets',
  target_class_id bigint(20) UNSIGNED DEFAULT NULL COMMENT 'If target_type=class',
  target_role enum('admin','teacher','parent','guardian','student') DEFAULT NULL COMMENT 'If target_type=role',
  target_user_id bigint(20) UNSIGNED DEFAULT NULL COMMENT 'If target_type=specific_user',
  
  -- Notification data
  notification_type enum('announcement','event','urgent','info','reminder') DEFAULT 'info',
  data_type varchar(50) DEFAULT NULL COMMENT 'announcement, assignment, fee, etc.',
  data_id bigint(20) UNSIGNED DEFAULT NULL COMMENT 'ID of related announcement/assignment/etc',
  
  -- Priority and delivery
  priority enum('low','normal','high') DEFAULT 'normal',
  delivery_status enum('pending','sent','failed','delivered') DEFAULT 'pending',
  
  -- Timestamps and tracking
  scheduled_at datetime DEFAULT NULL COMMENT 'If NULL, send immediately',
  sent_at datetime DEFAULT NULL,
  failure_reason text DEFAULT NULL,
  
  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(),
  
  -- Indexes for performance
  INDEX idx_target_type (target_type),
  INDEX idx_target_class_id (target_class_id),
  INDEX idx_delivery_status (delivery_status),
  INDEX idx_created_at (created_at),
  INDEX idx_scheduled_at (scheduled_at),
  INDEX idx_data (data_type, data_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Push notifications sent to users';

-- ============================================
-- Step 3: Create notification_reads table
-- ============================================
-- Tracks which users have read which notifications (optional, for read receipts)
CREATE TABLE IF NOT EXISTS notification_reads (
  id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  
  notification_id bigint(20) UNSIGNED NOT NULL,
  user_id bigint(20) UNSIGNED NOT NULL,
  
  read_at datetime DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT current_timestamp(),
  
  UNIQUE KEY unique_notification_user (notification_id, user_id),
  KEY idx_read_at (read_at),
  FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Track which users have read notifications';

-- ============================================
-- Step 4: Verify FCM tokens are in users table
-- ============================================
-- The fcm_token column should already exist in users table
-- If not, uncomment these lines:
-- ALTER TABLE users ADD COLUMN IF NOT EXISTS fcm_token varchar(300) NOT NULL DEFAULT '';
-- ALTER TABLE users ADD COLUMN IF NOT EXISTS fcm_token_updated_at timestamp NOT NULL DEFAULT current_timestamp();

-- ============================================
-- Step 5: Create indexes for FCM lookups
-- ============================================
ALTER TABLE users ADD INDEX IF NOT EXISTS idx_role_fcm (role, fcm_token);
ALTER TABLE users ADD INDEX IF NOT EXISTS idx_assigned_class_fcm (assigned_class, fcm_token);
ALTER TABLE users ADD INDEX IF NOT EXISTS idx_fcm_token (fcm_token);

-- ============================================
-- Migration Complete
-- ============================================
-- To verify the migration:
-- 1. Check announcements table: DESCRIBE announcements;
-- 2. Check notifications table: DESCRIBE notifications;
-- 3. Check notification_reads table: DESCRIBE notification_reads;
-- 4. Check users FCM indexes: SHOW INDEXES FROM users WHERE Key_name LIKE '%fcm%';