-- Migration: Add SaaS Multi-tenant Features
-- Date: 2025-01-30
-- Description: Add columns for multi-tenant support, QR codes, and Socket.IO rooms

-- Add QR code storage to devices table
-- MySQL note:
-- Many MySQL/MariaDB versions do NOT support "ADD COLUMN IF NOT EXISTS" or "CREATE INDEX IF NOT EXISTS".
-- This migration uses INFORMATION_SCHEMA checks + dynamic SQL to be re-runnable.

SET @db_name := DATABASE();

-- qr_code
SET @col_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'devices' AND COLUMN_NAME = 'qr_code'
);
SET @sql := IF(
  @col_exists = 0,
  "ALTER TABLE devices ADD COLUMN qr_code TEXT NULL COMMENT 'QR code for device pairing'",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- qr_generated_at
SET @col_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'devices' AND COLUMN_NAME = 'qr_generated_at'
);
SET @sql := IF(
  @col_exists = 0,
  "ALTER TABLE devices ADD COLUMN qr_generated_at TIMESTAMP NULL DEFAULT NULL COMMENT 'When QR code was generated'",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- user_room
SET @col_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'devices' AND COLUMN_NAME = 'user_room'
);
SET @sql := IF(
  @col_exists = 0,
  "ALTER TABLE devices ADD COLUMN user_room VARCHAR(255) NULL DEFAULT NULL COMMENT 'Socket.IO room for user notifications'",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- last_connected_at
SET @col_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'devices' AND COLUMN_NAME = 'last_connected_at'
);
SET @sql := IF(
  @col_exists = 0,
  "ALTER TABLE devices ADD COLUMN last_connected_at TIMESTAMP NULL DEFAULT NULL COMMENT 'Last successful connection timestamp'",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- connection_attempts
SET @col_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'devices' AND COLUMN_NAME = 'connection_attempts'
);
SET @sql := IF(
  @col_exists = 0,
  "ALTER TABLE devices ADD COLUMN connection_attempts INT NOT NULL DEFAULT 0 COMMENT 'Number of failed connection attempts'",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Indexes
SET @idx_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'devices' AND INDEX_NAME = 'idx_devices_user_id'
);
SET @sql := IF(@idx_exists = 0, 'CREATE INDEX idx_devices_user_id ON devices(user_id)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'devices' AND INDEX_NAME = 'idx_devices_status'
);
SET @sql := IF(@idx_exists = 0, 'CREATE INDEX idx_devices_status ON devices(status)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'devices' AND INDEX_NAME = 'idx_devices_sender'
);
SET @sql := IF(@idx_exists = 0, 'CREATE INDEX idx_devices_sender ON devices(sender)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Create table for session metadata (SaaS feature)
-- Note: Using sender/user_id directly instead of foreign keys for compatibility
CREATE TABLE IF NOT EXISTS device_sessions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  device_sender VARCHAR(255) NOT NULL COMMENT 'Device sender/phone number',
  user_id INT NOT NULL,
  session_start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  session_end TIMESTAMP NULL,
  messages_sent INT DEFAULT 0,
  messages_received INT DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE,
  INDEX idx_device_sessions_sender (device_sender),
  INDEX idx_device_sessions_user (user_id),
  INDEX idx_device_sessions_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create table for multi-tenant billing/usage tracking (optional for SaaS)
CREATE TABLE IF NOT EXISTS usage_metrics (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  device_sender VARCHAR(255) NOT NULL COMMENT 'Device sender/phone number',
  metric_date DATE NOT NULL,
  messages_sent INT DEFAULT 0,
  messages_received INT DEFAULT 0,
  api_calls INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_daily_metrics (user_id, device_sender, metric_date),
  INDEX idx_usage_user_date (user_id, metric_date),
  INDEX idx_usage_device_date (device_sender, metric_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add webhook endpoints for multi-tenant notifications
CREATE TABLE IF NOT EXISTS user_webhooks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  webhook_url VARCHAR(500) NOT NULL,
  webhook_secret VARCHAR(255) DEFAULT NULL COMMENT 'Secret for signing webhook payloads',
  events TEXT NOT NULL COMMENT 'JSON array of events to subscribe to: ["message.received", "connection.update", etc]',
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_webhooks_user (user_id),
  INDEX idx_webhooks_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Update existing devices to set default user_room
UPDATE devices SET user_room = CONCAT('user_', user_id) WHERE user_room IS NULL;
