/**
 * Database Schema Additions for Advanced Admin Features
 * These tables support retention campaigns, activity feed, and revenue forecasting
 */

import { mysqlTable, int, varchar, text, timestamp, decimal, mysqlEnum, json, tinyint, index } from 'drizzle-orm/mysql-core';

// Retention Campaigns Tables
export const retentionCampaigns = mysqlTable('retention_campaigns', {
  id: int().autoincrement().notNull().primaryKey(),
  name: varchar({ length: 255 }).notNull(),
  targetSegment: mysqlEnum(['dormant', 'casual', 'vip', 'new', 'atrisk']).notNull(),
  status: mysqlEnum(['draft', 'scheduled', 'active', 'completed', 'paused']).default('draft').notNull(),
  startDate: timestamp().notNull(),
  endDate: timestamp().notNull(),
  targetAudience: int().default(0).notNull(),
  reachedAudience: int().default(0).notNull(),
  conversionRate: decimal({ precision: 5, scale: 2 }).default('0.00').notNull(),
  revenue: decimal({ precision: 15, scale: 2 }).default('0.00').notNull(),
  message: text(),
  createdAt: timestamp().defaultNow().notNull(),
  updatedAt: timestamp().defaultNow().onUpdateNow().notNull(),
  createdBy: int().notNull(),
}, (table) => [
  index('idx_campaigns_status').on(table.status),
  index('idx_campaigns_segment').on(table.targetSegment),
  index('idx_campaigns_dates').on(table.startDate, table.endDate),
]);

export const campaignOffers = mysqlTable('campaign_offers', {
  id: int().autoincrement().notNull().primaryKey(),
  campaignId: int().notNull(),
  offerType: varchar({ length: 100 }).notNull(),
  offerValue: varchar({ length: 100 }).notNull(),
  description: text(),
  createdAt: timestamp().defaultNow().notNull(),
}, (table) => [
  index('idx_offers_campaign').on(table.campaignId),
]);

export const campaignChannels = mysqlTable('campaign_channels', {
  id: int().autoincrement().notNull().primaryKey(),
  campaignId: int().notNull(),
  channelType: mysqlEnum(['email', 'sms', 'push', 'ingame', 'social']).notNull(),
  sent: int().default(0).notNull(),
  opened: int().default(0).notNull(),
  clicked: int().default(0).notNull(),
  converted: int().default(0).notNull(),
  createdAt: timestamp().defaultNow().notNull(),
}, (table) => [
  index('idx_channels_campaign').on(table.campaignId),
]);

// Player Activity Feed Tables
export const playerActivities = mysqlTable('player_activities', {
  id: int().autoincrement().notNull().primaryKey(),
  userId: int().notNull(),
  eventType: mysqlEnum(['login', 'logout', 'deposit', 'withdrawal', 'win', 'game_play']).notNull(),
  gameId: varchar({ length: 255 }),
  gameName: varchar({ length: 255 }),
  amount: decimal({ precision: 15, scale: 2 }),
  ipAddress: varchar({ length: 45 }).notNull(),
  deviceType: varchar({ length: 50 }).notNull(),
  userAgent: text(),
  timestamp: timestamp().defaultNow().notNull(),
}, (table) => [
  index('idx_activities_user').on(table.userId),
  index('idx_activities_type').on(table.eventType),
  index('idx_activities_timestamp').on(table.timestamp),
]);

export const activityMetrics = mysqlTable('activity_metrics', {
  id: int().autoincrement().notNull().primaryKey(),
  timestamp: timestamp().notNull(),
  logins: int().default(0).notNull(),
  deposits: int().default(0).notNull(),
  withdrawals: int().default(0).notNull(),
  wins: int().default(0).notNull(),
  gamePlays: int().default(0).notNull(),
  totalRevenue: decimal({ precision: 15, scale: 2 }).default('0.00').notNull(),
  createdAt: timestamp().defaultNow().notNull(),
}, (table) => [
  index('idx_metrics_timestamp').on(table.timestamp),
]);

// Revenue Forecasting Tables
export const revenueForecasts = mysqlTable('revenue_forecasts', {
  id: int().autoincrement().notNull().primaryKey(),
  forecastDate: timestamp().notNull(),
  actualRevenue: decimal({ precision: 15, scale: 2 }),
  forecastedRevenue: decimal({ precision: 15, scale: 2 }).notNull(),
  confidenceHigh: decimal({ precision: 15, scale: 2 }).notNull(),
  confidenceLow: decimal({ precision: 15, scale: 2 }).notNull(),
  confidenceLevel: int().default(95).notNull(),
  modelType: varchar({ length: 50 }).default('ml').notNull(),
  accuracy: decimal({ precision: 5, scale: 2 }).default('0.00').notNull(),
  trend: varchar({ length: 50 }).notNull(),
  createdAt: timestamp().defaultNow().notNull(),
}, (table) => [
  index('idx_forecast_date').on(table.forecastDate),
  index('idx_forecast_model').on(table.modelType),
]);

export const forecastMetrics = mysqlTable('forecast_metrics', {
  id: int().autoincrement().notNull().primaryKey(),
  forecastId: int().notNull(),
  source: varchar({ length: 100 }).notNull(),
  revenue: decimal({ precision: 15, scale: 2 }).notNull(),
  percentage: decimal({ precision: 5, scale: 2 }).notNull(),
  forecast: decimal({ precision: 15, scale: 2 }).notNull(),
  createdAt: timestamp().defaultNow().notNull(),
}, (table) => [
  index('idx_metrics_forecast').on(table.forecastId),
]);

export const forecastInsights = mysqlTable('forecast_insights', {
  id: int().autoincrement().notNull().primaryKey(),
  forecastId: int().notNull(),
  insightType: varchar({ length: 100 }).notNull(),
  title: varchar({ length: 255 }).notNull(),
  description: text().notNull(),
  recommendation: text(),
  priority: mysqlEnum(['low', 'medium', 'high', 'critical']).default('medium').notNull(),
  createdAt: timestamp().defaultNow().notNull(),
}, (table) => [
  index('idx_insights_forecast').on(table.forecastId),
]);