-- PWA Notifications and Push Subscriptions Schema
-- Run this after updating your main schema

-- Create notifications table
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `message` TEXT NOT NULL,
  `type` ENUM('info', 'alert', 'success', 'error') DEFAULT 'info',
  `icon` VARCHAR(500),
  `tag` VARCHAR(100),
  `data` JSON,
  `read_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user_read` (`user_id`, `read_at`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create push subscriptions table
CREATE TABLE IF NOT EXISTS `push_subscriptions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL UNIQUE,
  `endpoint` VARCHAR(1000) NOT NULL,
  `auth` VARCHAR(500),
  `p256dh` VARCHAR(500),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_endpoint` (`endpoint`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add offline_mode flag to users table (if not exists)
ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `offline_mode_enabled` BOOLEAN DEFAULT TRUE;

-- Add sync tracking to counts table (if not exists)
ALTER TABLE `counts` ADD COLUMN IF NOT EXISTS `synced_at` TIMESTAMP NULL;
ALTER TABLE `counts` ADD COLUMN IF NOT EXISTS `offline_created` BOOLEAN DEFAULT FALSE;

-- Create sync log table for debugging
CREATE TABLE IF NOT EXISTS `sync_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `operation` VARCHAR(50),
  `table_name` VARCHAR(100),
  `record_id` INT,
  `status` ENUM('pending', 'synced', 'failed') DEFAULT 'pending',
  `error_message` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `synced_at` TIMESTAMP NULL,
  
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_user_status` (`user_id`, `status`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create offline settings table
CREATE TABLE IF NOT EXISTS `offline_settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL UNIQUE,
  `cache_expires_at` TIMESTAMP NULL,
  `last_sync` TIMESTAMP NULL,
  `auto_sync_enabled` BOOLEAN DEFAULT TRUE,
  `notification_permission` ENUM('default', 'granted', 'denied') DEFAULT 'default',
  `push_enabled` BOOLEAN DEFAULT FALSE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
