/**
 * Database Service
 * Centralized Appwrite database operations
 */

import { Client, Databases, Query, ID } from 'node-appwrite'
import { config } from '../config/index.mjs'
import { NotFoundError } from '../middleware/errorHandler.mjs'

// Initialize Appwrite client
const client = new Client()
  .setEndpoint(config.appwrite.endpoint)
  .setProject(config.appwrite.projectId)
  .setKey(config.appwrite.apiKey)

const databases = new Databases(client)
const DB_ID = config.appwrite.databaseId

/**
 * Collection names
 */
export const Collections = {
  PRODUCTS: 'products',
  QUESTIONS: 'questions',
  SUBMISSIONS: 'submissions',
  ANSWERS: 'answers',
  ORDERS: 'orders',
  EMAIL_ACCOUNTS: 'email_accounts',
  EMAIL_STATS: 'email_stats',
  EMAIL_DIGESTS: 'email_digests',
  SUBSCRIPTIONS: 'subscriptions',
  USER_PREFERENCES: 'user_preferences',
  ONBOARDING_STATE: 'onboarding_state',
  EMAIL_USAGE: 'email_usage',
  REFERRALS: 'referrals',
  ANALYTICS_EVENTS: 'analytics_events',
}

/**
 * Generic database operations
 */
export const db = {
  /**
   * Create a document
   */
  async create(collection, data, id = ID.unique()) {
    return await databases.createDocument(DB_ID, collection, id, data)
  },

  /**
   * Get a document by ID
   */
  async get(collection, id) {
    try {
      return await databases.getDocument(DB_ID, collection, id)
    } catch (error) {
      if (error.code === 404) {
        throw new NotFoundError(collection)
      }
      throw error
    }
  },

  /**
   * Update a document
   */
  async update(collection, id, data) {
    return await databases.updateDocument(DB_ID, collection, id, data)
  },

  /**
   * Delete a document
   */
  async delete(collection, id) {
    return await databases.deleteDocument(DB_ID, collection, id)
  },

  /**
   * List documents with optional queries
   */
  async list(collection, queries = []) {
    const response = await databases.listDocuments(DB_ID, collection, queries)
    return response.documents
  },

  /**
   * Find one document matching queries
   */
  async findOne(collection, queries) {
    const docs = await this.list(collection, [...queries, Query.limit(1)])
    return docs[0] || null
  },

  /**
   * Check if document exists
   */
  async exists(collection, id) {
    try {
      await databases.getDocument(DB_ID, collection, id)
      return true
    } catch {
      return false
    }
  },

  /**
   * Count documents
   */
  async count(collection, queries = []) {
    const response = await databases.listDocuments(DB_ID, collection, [
      ...queries,
      Query.limit(1),
    ])
    return response.total
  },
}

/**
 * Product operations
 */
export const products = {
  async getBySlug(slug) {
    return db.findOne(Collections.PRODUCTS, [
      Query.equal('slug', slug),
      Query.equal('isActive', true),
    ])
  },

  async getActive() {
    return db.list(Collections.PRODUCTS, [Query.equal('isActive', true)])
  },
}

/**
 * Questions operations
 */
export const questions = {
  async getByProduct(productId) {
    return db.list(Collections.QUESTIONS, [
      Query.equal('productId', productId),
      Query.equal('isActive', true),
      Query.orderAsc('step'),
      Query.orderAsc('order'),
    ])
  },
}

/**
 * Submissions operations
 */
export const submissions = {
  async create(data) {
    return db.create(Collections.SUBMISSIONS, data)
  },

  async updateStatus(id, status) {
    return db.update(Collections.SUBMISSIONS, id, { status })
  },

  async getByUser(userId) {
    return db.list(Collections.SUBMISSIONS, [Query.equal('userId', userId)])
  },
}

/**
 * Email accounts operations
 */
export const emailAccounts = {
  async create(data) {
    return db.create(Collections.EMAIL_ACCOUNTS, data)
  },

  async getByUser(userId) {
    return db.list(Collections.EMAIL_ACCOUNTS, [
      Query.equal('userId', userId),
      Query.equal('isActive', true),
    ])
  },

  async get(id) {
    return db.get(Collections.EMAIL_ACCOUNTS, id)
  },

  async updateLastSync(id) {
    return db.update(Collections.EMAIL_ACCOUNTS, id, {
      lastSync: new Date().toISOString(),
    })
  },

  async deactivate(id) {
    return db.update(Collections.EMAIL_ACCOUNTS, id, { isActive: false })
  },
}

/**
 * Email stats operations
 */
export const emailStats = {
  async getByUser(userId) {
    return db.findOne(Collections.EMAIL_STATS, [Query.equal('userId', userId)])
  },

  async create(userId, data) {
    return db.create(Collections.EMAIL_STATS, { userId, ...data })
  },

  async increment(userId, counts) {
    const stats = await this.getByUser(userId)

    if (stats) {
      return db.update(Collections.EMAIL_STATS, stats.$id, {
        totalSorted: (stats.totalSorted || 0) + (counts.total || 0),
        todaySorted: (stats.todaySorted || 0) + (counts.today || 0),
        weekSorted: (stats.weekSorted || 0) + (counts.week || 0),
        timeSavedMinutes: (stats.timeSavedMinutes || 0) + (counts.timeSaved || 0),
      })
    } else {
      return this.create(userId, {
        totalSorted: counts.total || 0,
        todaySorted: counts.today || 0,
        weekSorted: counts.week || 0,
        timeSavedMinutes: counts.timeSaved || 0,
        categoriesJson: '{}',
      })
    }
  },

  async updateCategories(userId, categories) {
    const stats = await this.getByUser(userId)
    if (stats) {
      return db.update(Collections.EMAIL_STATS, stats.$id, {
        categoriesJson: JSON.stringify(categories),
      })
    }
    return null
  },

  async resetDaily() {
    // Reset daily counters - would be called by a cron job
    const allStats = await db.list(Collections.EMAIL_STATS, [])
    for (const stat of allStats) {
      await db.update(Collections.EMAIL_STATS, stat.$id, { todaySorted: 0 })
    }
  },

  async resetWeekly() {
    // Reset weekly counters - would be called by a cron job
    const allStats = await db.list(Collections.EMAIL_STATS, [])
    for (const stat of allStats) {
      await db.update(Collections.EMAIL_STATS, stat.$id, { 
        weekSorted: 0,
        categoriesJson: '{}',
      })
    }
  },
}

/**
 * Email usage operations
 */
export const emailUsage = {
  async getCurrentMonth(userId) {
    const month = new Date().toISOString().slice(0, 7) // "2026-01"
    return db.findOne(Collections.EMAIL_USAGE, [
      Query.equal('userId', userId),
      Query.equal('month', month),
    ])
  },

  async increment(userId, count) {
    const month = new Date().toISOString().slice(0, 7)
    const existing = await this.getCurrentMonth(userId)

    if (existing) {
      return db.update(Collections.EMAIL_USAGE, existing.$id, {
        emailsProcessed: (existing.emailsProcessed || 0) + count,
        lastReset: new Date().toISOString(),
      })
    }

    return db.create(Collections.EMAIL_USAGE, {
      userId,
      month,
      emailsProcessed: count,
      lastReset: new Date().toISOString(),
    })
  },

  async getUsage(userId) {
    const usage = await this.getCurrentMonth(userId)
    return {
      emailsProcessed: usage?.emailsProcessed || 0,
      month: new Date().toISOString().slice(0, 7),
    }
  },
}

/**
 * Subscriptions operations
 */
export const subscriptions = {
  async getByUser(userId) {
    const subscription = await db.findOne(Collections.SUBSCRIPTIONS, [Query.equal('userId', userId)])
    
    // If no subscription, user is on free tier
    if (!subscription) {
      const usage = await emailUsage.getUsage(userId)
      return {
        plan: 'free',
        status: 'active',
        isFreeTier: true,
        emailsUsedThisMonth: usage.emailsProcessed,
        emailsLimit: 500, // From config
      }
    }

    // Check if subscription is active
    const isActive = subscription.status === 'active'
    const isFreeTier = !isActive || subscription.plan === 'free'
    
    // Get usage for free tier users
    let emailsUsedThisMonth = 0
    let emailsLimit = -1 // Unlimited for paid
    
    if (isFreeTier) {
      const usage = await emailUsage.getUsage(userId)
      emailsUsedThisMonth = usage.emailsProcessed
      emailsLimit = 500 // From config
    }

    return {
      ...subscription,
      plan: subscription.plan || 'free',
      isFreeTier,
      emailsUsedThisMonth,
      emailsLimit,
    }
  },

  async getByStripeId(stripeSubscriptionId) {
    return db.findOne(Collections.SUBSCRIPTIONS, [
      Query.equal('stripeSubscriptionId', stripeSubscriptionId),
    ])
  },

  async create(data) {
    return db.create(Collections.SUBSCRIPTIONS, data)
  },

  async update(id, data) {
    return db.update(Collections.SUBSCRIPTIONS, id, data)
  },

  async upsertByUser(userId, data) {
    const existing = await this.getByUser(userId)
    if (existing) {
      return this.update(existing.$id, data)
    }
    return this.create({ userId, ...data })
  },
}

/**
 * User preferences operations
 */
export const userPreferences = {
  /**
   * Get default preferences structure
   */
  getDefaults() {
    return {
      vipSenders: [],
      enabledCategories: ['vip', 'customers', 'invoices', 'newsletters', 'promotions', 'social', 'security', 'calendar', 'review'],
      categoryActions: {},
      companyLabels: [],
      autoDetectCompanies: true,
      version: 1,
      categoryAdvanced: {},
      cleanup: {
        enabled: false,
        readItems: {
          enabled: false,
          action: 'archive_read',
          gracePeriodDays: 7,
        },
        promotions: {
          enabled: false,
          matchCategoriesOrLabels: ['promotions', 'newsletters'],
          action: 'archive_read',
          deleteAfterDays: 30,
        },
        safety: {
          requireConfirmForDelete: true,
          dryRun: false,
          maxDeletesPerRun: 100,
        },
      },
    }
  },

  /**
   * Merge preferences with defaults
   */
  mergeWithDefaults(preferences) {
    const defaults = this.getDefaults()
    return {
      ...defaults,
      ...preferences,
      vipSenders: preferences.vipSenders || defaults.vipSenders,
      enabledCategories: preferences.enabledCategories || defaults.enabledCategories,
      categoryActions: preferences.categoryActions || defaults.categoryActions,
      companyLabels: preferences.companyLabels || defaults.companyLabels,
      autoDetectCompanies: preferences.autoDetectCompanies !== undefined ? preferences.autoDetectCompanies : defaults.autoDetectCompanies,
    }
  },

  async getByUser(userId) {
    const pref = await db.findOne(Collections.USER_PREFERENCES, [
      Query.equal('userId', userId),
    ])
    if (pref?.preferencesJson) {
      const parsed = JSON.parse(pref.preferencesJson)
      return { ...pref, preferences: this.mergeWithDefaults(parsed) }
    }
    return { ...pref, preferences: this.getDefaults() }
  },

  async upsert(userId, preferences) {
    const existing = await db.findOne(Collections.USER_PREFERENCES, [
      Query.equal('userId', userId),
    ])

    // Merge with existing preferences if updating
    let mergedPreferences = preferences
    if (existing?.preferencesJson) {
      const existingPrefs = JSON.parse(existing.preferencesJson)
      mergedPreferences = { ...existingPrefs, ...preferences }
    }

    const data = { preferencesJson: JSON.stringify(mergedPreferences) }

    if (existing) {
      return db.update(Collections.USER_PREFERENCES, existing.$id, data)
    }
    return db.create(Collections.USER_PREFERENCES, { userId, ...data })
  },
}

/**
 * Onboarding state operations
 */
export const onboardingState = {
  async getByUser(userId) {
    const state = await db.findOne(Collections.ONBOARDING_STATE, [
      Query.equal('userId', userId),
    ])
    if (state?.completed_steps_json) {
      return {
        ...state,
        completedSteps: JSON.parse(state.completed_steps_json),
      }
    }
    return {
      ...state,
      completedSteps: [],
      onboarding_step: state?.onboarding_step || 'not_started',
    }
  },

  async updateStep(userId, step, completedSteps = []) {
    const existing = await db.findOne(Collections.ONBOARDING_STATE, [
      Query.equal('userId', userId),
    ])

    const data = {
      onboarding_step: step,
      completed_steps_json: JSON.stringify(completedSteps),
      last_updated: new Date().toISOString(),
    }

    if (existing) {
      return db.update(Collections.ONBOARDING_STATE, existing.$id, data)
    }
    return db.create(Collections.ONBOARDING_STATE, { userId, ...data })
  },

  async markValueSeen(userId) {
    const existing = await db.findOne(Collections.ONBOARDING_STATE, [
      Query.equal('userId', userId),
    ])

    const data = {
      first_value_seen_at: new Date().toISOString(),
      last_updated: new Date().toISOString(),
    }

    if (existing) {
      return db.update(Collections.ONBOARDING_STATE, existing.$id, data)
    }
    return db.create(Collections.ONBOARDING_STATE, {
      userId,
      onboarding_step: 'see_results',
      completed_steps_json: JSON.stringify(['connect', 'first_rule', 'see_results']),
      ...data,
    })
  },

  async skip(userId) {
    const existing = await db.findOne(Collections.ONBOARDING_STATE, [
      Query.equal('userId', userId),
    ])

    const data = {
      skipped_at: new Date().toISOString(),
      last_updated: new Date().toISOString(),
    }

    if (existing) {
      return db.update(Collections.ONBOARDING_STATE, existing.$id, data)
    }
    return db.create(Collections.ONBOARDING_STATE, {
      userId,
      onboarding_step: 'not_started',
      completed_steps_json: JSON.stringify([]),
      ...data,
    })
  },

  async resume(userId) {
    const existing = await db.findOne(Collections.ONBOARDING_STATE, [
      Query.equal('userId', userId),
    ])

    if (existing) {
      return db.update(Collections.ONBOARDING_STATE, existing.$id, {
        skipped_at: null,
        last_updated: new Date().toISOString(),
      })
    }
    // If no state exists, create initial state
    return db.create(Collections.ONBOARDING_STATE, {
      userId,
      onboarding_step: 'connect',
      completed_steps_json: JSON.stringify([]),
      last_updated: new Date().toISOString(),
    })
  },
}

/**
 * Referrals operations
 */
export const referrals = {
  async getOrCreateCode(userId) {
    const existing = await db.findOne(Collections.REFERRALS, [
      Query.equal('userId', userId),
    ])

    if (existing) {
      return existing
    }

    // Generate unique code: USER-ABC123
    const randomPart = Math.random().toString(36).substring(2, 8).toUpperCase()
    const code = `USER-${randomPart}`

    // Ensure uniqueness
    let uniqueCode = code
    let attempts = 0
    while (attempts < 10) {
      const existingCode = await db.findOne(Collections.REFERRALS, [
        Query.equal('referralCode', uniqueCode),
      ])
      if (!existingCode) break
      uniqueCode = `USER-${Math.random().toString(36).substring(2, 8).toUpperCase()}`
      attempts++
    }

    return db.create(Collections.REFERRALS, {
      userId,
      referralCode: uniqueCode,
      referralCount: 0,
      createdAt: new Date().toISOString(),
    })
  },

  async getByCode(code) {
    return db.findOne(Collections.REFERRALS, [
      Query.equal('referralCode', code),
    ])
  },

  async incrementCount(userId) {
    const referral = await db.findOne(Collections.REFERRALS, [
      Query.equal('userId', userId),
    ])

    if (referral) {
      return db.update(Collections.REFERRALS, referral.$id, {
        referralCount: (referral.referralCount || 0) + 1,
      })
    }
    return null
  },

  async getReferrals(userId) {
    return db.list(Collections.REFERRALS, [
      Query.equal('referredBy', userId),
    ])
  },
}

/**
 * Orders operations
 */
export const orders = {
  async create(submissionId, orderData) {
    return db.create(Collections.ORDERS, {
      submissionId,
      orderDataJson: JSON.stringify(orderData),
    })
  },
}

/**
 * Email digests operations
 */
export const emailDigests = {
  async create(userId, digestData) {
    const date = new Date().toISOString().split('T')[0] // YYYY-MM-DD
    return db.create(Collections.EMAIL_DIGESTS, {
      userId,
      date,
      statsJson: JSON.stringify(digestData.stats || {}),
      highlightsJson: JSON.stringify(digestData.highlights || []),
      suggestionsJson: JSON.stringify(digestData.suggestions || []),
      totalSorted: digestData.totalSorted || 0,
      inboxCleared: digestData.inboxCleared || 0,
      timeSavedMinutes: digestData.timeSavedMinutes || 0,
      createdAt: new Date().toISOString(),
    })
  },

  async getByUserToday(userId) {
    const today = new Date().toISOString().split('T')[0]
    const digest = await db.findOne(Collections.EMAIL_DIGESTS, [
      Query.equal('userId', userId),
      Query.equal('date', today),
    ])
    
    if (digest) {
      return {
        ...digest,
        stats: JSON.parse(digest.statsJson || '{}'),
        highlights: JSON.parse(digest.highlightsJson || '[]'),
        suggestions: JSON.parse(digest.suggestionsJson || '[]'),
      }
    }
    return null
  },

  async getByUserRecent(userId, days = 7) {
    const startDate = new Date()
    startDate.setDate(startDate.getDate() - days)
    
    const digests = await db.list(Collections.EMAIL_DIGESTS, [
      Query.equal('userId', userId),
      Query.greaterThanEqual('date', startDate.toISOString().split('T')[0]),
      Query.orderDesc('date'),
    ])

    return digests.map(d => ({
      ...d,
      stats: JSON.parse(d.statsJson || '{}'),
      highlights: JSON.parse(d.highlightsJson || '[]'),
      suggestions: JSON.parse(d.suggestionsJson || '[]'),
    }))
  },

  async updateToday(userId, updates) {
    const today = new Date().toISOString().split('T')[0]
    const existing = await db.findOne(Collections.EMAIL_DIGESTS, [
      Query.equal('userId', userId),
      Query.equal('date', today),
    ])

    if (existing) {
      const updateData = {}
      if (updates.stats) updateData.statsJson = JSON.stringify(updates.stats)
      if (updates.highlights) updateData.highlightsJson = JSON.stringify(updates.highlights)
      if (updates.suggestions) updateData.suggestionsJson = JSON.stringify(updates.suggestions)
      if (updates.totalSorted !== undefined) {
        updateData.totalSorted = (existing.totalSorted || 0) + updates.totalSorted
      }
      if (updates.inboxCleared !== undefined) {
        updateData.inboxCleared = (existing.inboxCleared || 0) + updates.inboxCleared
      }
      if (updates.timeSavedMinutes !== undefined) {
        updateData.timeSavedMinutes = (existing.timeSavedMinutes || 0) + updates.timeSavedMinutes
      }
      return db.update(Collections.EMAIL_DIGESTS, existing.$id, updateData)
    }

    // Create new digest for today
    return this.create(userId, {
      stats: updates.stats || {},
      highlights: updates.highlights || [],
      suggestions: updates.suggestions || [],
      totalSorted: updates.totalSorted || 0,
      inboxCleared: updates.inboxCleared || 0,
      timeSavedMinutes: updates.timeSavedMinutes || 0,
    })
  },
}

export { Query }

export default {
  db,
  products,
  questions,
  submissions,
  emailAccounts,
  emailStats,
  emailDigests,
  subscriptions,
  userPreferences,
  orders,
  Collections,
}