/**
 * Database Helper Functions for Advanced Features
 * Provides query builders for retention campaigns, activity feed, and forecasting
 */

import { getDb } from './db.ts';

/**
 * Retention Campaign Helpers
 */
export async function createRetentionCampaign(data: {
  name: string;
  targetSegment: string;
  status: string;
  startDate: Date;
  endDate: Date;
  message?: string;
}) {
  const db = await getDb();
  const result = await db.insert({
    table: 'retention_campaigns',
    data: {
      name: data.name,
      target_segment: data.targetSegment,
      status: data.status,
      start_date: data.startDate,
      end_date: data.endDate,
      message: data.message,
    },
  });
  return result;
}

export async function getRetentionCampaigns(status?: string) {
  const db = await getDb();
  let query = 'SELECT * FROM retention_campaigns';
  if (status) {
    query += ` WHERE status = '${status}'`;
  }
  query += ' ORDER BY created_at DESC';
  const result = await db.query(query);
  return result;
}

export async function updateCampaignStatus(campaignId: number, status: string) {
  const db = await getDb();
  const result = await db.update({
    table: 'retention_campaigns',
    where: { id: campaignId },
    data: { status },
  });
  return result;
}

export async function addCampaignOffer(campaignId: number, offer: string, type: string) {
  const db = await getDb();
  const result = await db.insert({
    table: 'campaign_offers',
    data: {
      campaign_id: campaignId,
      offer_text: offer,
      offer_type: type,
    },
  });
  return result;
}

export async function getCampaignOffers(campaignId: number) {
  const db = await getDb();
  const result = await db.query(
    `SELECT * FROM campaign_offers WHERE campaign_id = ${campaignId}`
  );
  return result;
}

export async function addCampaignChannel(campaignId: number, channel: string) {
  const db = await getDb();
  const result = await db.insert({
    table: 'campaign_channels',
    data: {
      campaign_id: campaignId,
      channel,
    },
  });
  return result;
}

export async function updateChannelMetrics(
  campaignId: number,
  channel: string,
  metrics: {
    sentCount?: number;
    openedCount?: number;
    clickedCount?: number;
    convertedCount?: number;
    revenue?: number;
  }
) {
  const db = await getDb();
  const updateData: any = {};
  if (metrics.sentCount !== undefined) updateData.sent_count = metrics.sentCount;
  if (metrics.openedCount !== undefined) updateData.opened_count = metrics.openedCount;
  if (metrics.clickedCount !== undefined) updateData.clicked_count = metrics.clickedCount;
  if (metrics.convertedCount !== undefined) updateData.converted_count = metrics.convertedCount;
  if (metrics.revenue !== undefined) updateData.revenue = metrics.revenue;

  const result = await db.update({
    table: 'campaign_channels',
    where: { campaign_id: campaignId, channel },
    data: updateData,
  });
  return result;
}

/**
 * Activity Feed Helpers
 */
export async function logPlayerActivity(data: {
  id: string;
  userId: number;
  eventType: string;
  gameId?: string;
  gameName?: string;
  amount?: number;
  ipAddress: string;
  deviceType: string;
  userAgent?: string;
}) {
  const db = await getDb();
  const result = await db.insert({
    table: 'player_activities',
    data: {
      id: data.id,
      user_id: data.userId,
      event_type: data.eventType,
      game_id: data.gameId,
      game_name: data.gameName,
      amount: data.amount,
      ip_address: data.ipAddress,
      device_type: data.deviceType,
      user_agent: data.userAgent,
    },
  });
  return result;
}

export async function getPlayerActivities(limit: number = 100, filters?: any) {
  const db = await getDb();
  let query = 'SELECT * FROM player_activities';
  const conditions = [];

  if (filters?.eventType) {
    conditions.push(`event_type = '${filters.eventType}'`);
  }
  if (filters?.userId) {
    conditions.push(`user_id = ${filters.userId}`);
  }
  if (filters?.startDate) {
    conditions.push(`created_at >= '${filters.startDate.toISOString()}'`);
  }
  if (filters?.endDate) {
    conditions.push(`created_at <= '${filters.endDate.toISOString()}'`);
  }

  if (conditions.length > 0) {
    query += ' WHERE ' + conditions.join(' AND ');
  }

  query += ` ORDER BY created_at DESC LIMIT ${limit}`;
  const result = await db.query(query);
  return result;
}

export async function searchActivities(query: string, limit: number = 100) {
  const db = await getDb();
  const result = await db.query(
    `SELECT * FROM player_activities WHERE user_id LIKE '%${query}%' OR ip_address LIKE '%${query}%' ORDER BY created_at DESC LIMIT ${limit}`
  );
  return result;
}

export async function getActivityMetrics(startDate: Date, endDate: Date) {
  const db = await getDb();
  const result = await db.query(
    `SELECT * FROM activity_metrics WHERE metric_hour BETWEEN '${startDate.toISOString()}' AND '${endDate.toISOString()}' ORDER BY metric_hour ASC`
  );
  return result;
}

export async function recordActivityMetric(data: {
  metricHour: Date;
  logins: number;
  deposits: number;
  withdrawals: number;
  wins: number;
  gamePlays: number;
  totalRevenue: number;
}) {
  const db = await getDb();
  const result = await db.insert({
    table: 'activity_metrics',
    data: {
      metric_hour: data.metricHour,
      logins: data.logins,
      deposits: data.deposits,
      withdrawals: data.withdrawals,
      wins: data.wins,
      game_plays: data.gamePlays,
      total_revenue: data.totalRevenue,
    },
  });
  return result;
}

export async function getTopPlayers(limit: number = 10) {
  const db = await getDb();
  const result = await db.query(
    `SELECT user_id, COUNT(*) as activity_count, MAX(created_at) as last_activity FROM player_activities GROUP BY user_id ORDER BY activity_count DESC LIMIT ${limit}`
  );
  return result;
}

/**
 * Revenue Forecasting Helpers
 */
export async function createRevenueForecast(data: {
  forecastDate: Date;
  actualRevenue?: number;
  forecastedRevenue: number;
  confidenceHigh: number;
  confidenceLow: number;
  confidenceLevel: number;
  modelType: string;
  accuracy: number;
  trend: string;
}) {
  const db = await getDb();
  const result = await db.insert({
    table: 'revenue_forecasts',
    data: {
      forecast_date: data.forecastDate,
      actual_revenue: data.actualRevenue,
      forecasted_revenue: data.forecastedRevenue,
      confidence_high: data.confidenceHigh,
      confidence_low: data.confidenceLow,
      confidence_level: data.confidenceLevel,
      model_type: data.modelType,
      accuracy: data.accuracy,
      trend: data.trend,
    },
  });
  return result;
}

export async function getRevenueForecasts(modelType?: string, daysAhead: number = 30) {
  const db = await getDb();
  let query = 'SELECT * FROM revenue_forecasts';
  if (modelType) {
    query += ` WHERE model_type = '${modelType}'`;
  }
  query += ` AND forecast_date >= CURDATE() ORDER BY forecast_date ASC LIMIT ${daysAhead}`;
  const result = await db.query(query);
  return result;
}

export async function getForecastMetrics(modelType: string) {
  const db = await getDb();
  const result = await db.query(
    `SELECT * FROM forecast_metrics WHERE model_type = '${modelType}' ORDER BY metric_date DESC LIMIT 1`
  );
  return result;
}

export async function recordForecastMetric(data: {
  metricDate: Date;
  modelType: string;
  accuracy: number;
  mape: number;
  rmse: number;
}) {
  const db = await getDb();
  const result = await db.insert({
    table: 'forecast_metrics',
    data: {
      metric_date: data.metricDate,
      model_type: data.modelType,
      accuracy: data.accuracy,
      mape: data.mape,
      rmse: data.rmse,
    },
  });
  return result;
}

export async function createForecastInsight(data: {
  insightType: string;
  title: string;
  description: string;
  recommendation: string;
  priority: string;
}) {
  const db = await getDb();
  const result = await db.insert({
    table: 'forecast_insights',
    data: {
      insight_type: data.insightType,
      title: data.title,
      description: data.description,
      recommendation: data.recommendation,
      priority: data.priority,
    },
  });
  return result;
}

export async function getForecastInsights(priority?: string) {
  const db = await getDb();
  let query = 'SELECT * FROM forecast_insights';
  if (priority) {
    query += ` WHERE priority = '${priority}'`;
  }
  query += ' ORDER BY created_at DESC';
  const result = await db.query(query);
  return result;
}