/**
 * SQLite Database for Push Notifications Subscriptions
 * Stores user push subscription data for Web Push notifications
 */

import Database from 'better-sqlite3'
import path from 'path'
import fs from 'fs'

// Database file path
const dbPath = path.join(process.cwd(), 'data', 'push-subscriptions.db')

// Ensure data directory exists
const dataDir = path.dirname(dbPath)
if (!fs.existsSync(dataDir)) {
  fs.mkdirSync(dataDir, { recursive: true })
}

// Initialize database
const db = new Database(dbPath)

// Enable WAL mode for better concurrency
db.pragma('journal_mode = WAL')

// Create table on startup
db.exec(`
  CREATE TABLE IF NOT EXISTS push_subscriptions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    role_id INTEGER NOT NULL,
    endpoint TEXT UNIQUE NOT NULL,
    p256dh TEXT NOT NULL,
    auth TEXT NOT NULL,
    user_agent TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_used DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  CREATE INDEX IF NOT EXISTS idx_push_user ON push_subscriptions(user_id);
  CREATE INDEX IF NOT EXISTS idx_push_role ON push_subscriptions(role_id);
  CREATE INDEX IF NOT EXISTS idx_push_endpoint ON push_subscriptions(endpoint);
`)

console.log('[PushDB] ✅ SQLite database initialized at:', dbPath)

/**
 * Save or update a push subscription
 */
export const savePushSubscription = (userId: number, roleId: number, subscription: any, userAgent?: string) => {
  const stmt = db.prepare(`
    INSERT INTO push_subscriptions (user_id, role_id, endpoint, p256dh, auth, user_agent, last_used)
    VALUES (?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
    ON CONFLICT(endpoint) DO UPDATE SET
      user_id = excluded.user_id,
      role_id = excluded.role_id,
      last_used = CURRENT_TIMESTAMP
  `)

  try {
    const result = stmt.run(
      userId,
      roleId,
      subscription.endpoint,
      subscription.keys.p256dh,
      subscription.keys.auth,
      userAgent || null
    )
    console.log('[PushDB] ✅ Subscription saved:', { userId, roleId, endpoint: subscription.endpoint })
    return result
  } catch (error: any) {
    console.error('[PushDB] ❌ Error saving subscription:', error.message)
    throw error
  }
}

/**
 * Get all push subscriptions for a specific user
 */
export const getPushSubscriptionsByUser = (userId: number) => {
  const stmt = db.prepare(`
    SELECT * FROM push_subscriptions WHERE user_id = ? ORDER BY last_used DESC
  `)
  return stmt.all(userId)
}

/**
 * Get all push subscriptions for a specific role
 */
export const getPushSubscriptionsByRole = (roleId: number) => {
  const stmt = db.prepare(`
    SELECT * FROM push_subscriptions WHERE role_id = ? ORDER BY last_used DESC
  `)
  return stmt.all(roleId)
}

/**
 * Get all push subscriptions for multiple roles
 */
export const getPushSubscriptionsByRoles = (roleIds: number[]) => {
  if (roleIds.length === 0) return []

  const placeholders = roleIds.map(() => '?').join(',')
  const stmt = db.prepare(`
    SELECT * FROM push_subscriptions
    WHERE role_id IN (${placeholders})
    ORDER BY last_used DESC
  `)
  return stmt.all(...roleIds)
}

/**
 * Delete a push subscription by endpoint
 */
export const deletePushSubscription = (endpoint: string) => {
  const stmt = db.prepare('DELETE FROM push_subscriptions WHERE endpoint = ?')
  try {
    const result = stmt.run(endpoint)
    if (result.changes > 0) {
      console.log('[PushDB] ✅ Subscription deleted:', endpoint)
    }
    return result
  } catch (error: any) {
    console.error('[PushDB] ❌ Error deleting subscription:', error.message)
    throw error
  }
}

/**
 * Delete all subscriptions for a user
 */
export const deletePushSubscriptionsByUser = (userId: number) => {
  const stmt = db.prepare('DELETE FROM push_subscriptions WHERE user_id = ?')
  const result = stmt.run(userId)
  console.log('[PushDB] Deleted', result.changes, 'subscriptions for user', userId)
  return result
}

/**
 * Get statistics about subscriptions
 */
export const getPushSubscriptionStats = () => {
  const stmt = db.prepare(`
    SELECT
      COUNT(*) as total,
      COUNT(DISTINCT user_id) as unique_users,
      COUNT(DISTINCT role_id) as unique_roles
    FROM push_subscriptions
  `)
  return stmt.get()
}

/**
 * Clean up old subscriptions (older than 90 days)
 */
export const cleanupOldSubscriptions = () => {
  const stmt = db.prepare(`
    DELETE FROM push_subscriptions
    WHERE last_used < datetime('now', '-90 days')
  `)
  const result = stmt.run()
  if (result.changes > 0) {
    console.log('[PushDB] Cleaned up', result.changes, 'old subscriptions')
  }
  return result
}

// Export database instance for advanced queries
export const pushDb = db