import { EntrolyticsDrizzleClient } from '@entrolytics/drizzle-client'

const entrolytics = new EntrolyticsDrizzleClient({
  db: dbInstance, // Drizzle database instance
  websiteId: 'your-website-id', // required
  apiKey: 'your-api-key', // optional
  host: 'https://entrolytics.dev', // custom host
  schema: {
    // database schema
    events: analyticsEvents,
    users: analyticsUsers,
    sessions: analyticsSessions
  },
  options: {
    autoProcess: true, // auto-process queued events
    batchSize: 100, // batch size for processing
    flushInterval: 5000, // flush interval (ms)
    maxRetries: 3, // max retry attempts
    retryDelay: 1000, // retry delay (ms)
    debug: false, // debug mode
    enableLocalStorage: true, // store events locally
    localStorageKey: 'entrolytics_events', // localStorage key
    syncToRemote: true, // sync to remote API
    syncInterval: 10000 // sync interval (ms)
  }
})

# .env
DATABASE_URL=postgresql://user:password@localhost:5432/analytics
ENTROLYTICS_WEBSITE_ID=your-website-id
ENTROLYTICS_API_KEY=your-api-key
ENTROLYTICS_DEBUG=false
ENTROLYTICS_BATCH_SIZE=100
ENTROLYTICS_FLUSH_INTERVAL=5000

// drizzle.config.ts
import type { Config } from 'drizzle-kit'
import { env } from './env'

export default {
  dialect: 'postgresql',
  schema: './src/drizzle/schema.ts',
  out: './drizzle/migrations',
  dbCredentials: {
    url: env.DATABASE_URL
  },
  tablesFilter: ['analytics_*'],
  strict: true
} satisfies Config

class EntrolyticsDrizzleClient {
  constructor(config: EntrolyticsConfig)

  // Event tracking
  track(event: string, properties?: Record<string, any>): Promise<void>
  trackBatch(events: AnalyticsEvent[]): Promise<void>
  page(url: string, properties?: Record<string, any>): Promise<void>

  // User identification
  identify(userId: string, traits?: Record<string, any>): Promise<void>
  setUserProperties(properties: Record<string, any>): Promise<void>

  // Database operations
  getEvents(filters?: EventFilters): Promise<AnalyticsEvent[]>
  getEvent(id: number): Promise<AnalyticsEvent | null>
  getUser(userId: string): Promise<AnalyticsUser | null>
  getSession(sessionId: string): Promise<AnalyticsSession | null>

  // Analytics queries
  getEventCounts(timeRange?: TimeRange): Promise<Record<string, number>>
  getTopEvents(
    limit?: number,
    timeRange?: TimeRange
  ): Promise<Array<{ event: string; count: number }>>
  getUserActivity(
    userId: string,
    timeRange?: TimeRange
  ): Promise<AnalyticsEvent[]>
  getSessionActivity(sessionId: string): Promise<AnalyticsEvent[]>

  // Aggregations
  aggregateEvents(aggregation: EventAggregation): Promise<any>
  aggregateMetrics(metrics: MetricAggregation[]): Promise<any>

  // Processing
  processPendingEvents(): Promise<number>
  flush(): Promise<void>
  syncToRemote(): Promise<void>

  // Utilities
  createSession(
    userId?: string,
    properties?: Record<string, any>
  ): Promise<string>
  updateSession(
    sessionId: string,
    properties: Record<string, any>
  ): Promise<void>
  endSession(sessionId: string): Promise<void>

  // Configuration
  updateConfig(config: Partial<EntrolyticsConfig>): void
  getConfig(): EntrolyticsConfig
}

interface AnalyticsEvent {
  id: number
  websiteId: string
  sessionId: string
  userId?: string
  event: string
  properties?: Record<string, any>
  timestamp: Date
  processed: boolean
}

interface AnalyticsUser {
  id: string
  email?: string
  name?: string
  properties?: Record<string, any>
  createdAt: Date
  updatedAt: Date
}

interface AnalyticsSession {
  id: string
  userId?: string
  properties?: Record<string, any>
  startedAt: Date
  endedAt?: Date
  isActive: boolean
}

interface EntrolyticsConfig {
  db: DrizzleDB
  websiteId: string
  apiKey?: string
  host?: string
  schema: {
    events: any
    users: any
    sessions: any
  }
  options?: {
    autoProcess?: boolean
    batchSize?: number
    flushInterval?: number
    maxRetries?: number
    retryDelay?: number
    debug?: boolean
    enableLocalStorage?: boolean
    localStorageKey?: string
    syncToRemote?: boolean
    syncInterval?: number
  }
}

// Custom analytics queries
class AnalyticsQueries {
  constructor(private db: DrizzleDB) {}

  // Get user funnel data
  async getUserFunnel(steps: string[], timeRange: TimeRange) {
    const funnelQuery = this.db.select({
      step: sql<string>`step`,
      count: sql<number>`count`,
      conversionRate: sql<number>`conversion_rate`
    }).from(sql`
        (
          SELECT
            unnest(ARRAY[${steps.map((s) => `'${s}'`).join(',')}]) as step,
            COUNT(DISTINCT user_id) as count,
            LAG(COUNT(DISTINCT user_id)) OVER (ORDER BY step) as prev_count,
            CASE
              WHEN LAG(COUNT(DISTINCT user_id)) OVER (ORDER BY step) > 0
              THEN ROUND((COUNT(DISTINCT user_id)::float / LAG(COUNT(DISTINCT user_id)) OVER (ORDER BY step)) * 100, 2)
              ELSE 100
            END as conversion_rate
          FROM ${analyticsEvents}
          WHERE event = ANY(ARRAY[${steps.map((s) => `'${s}'`).join(',')}])
            AND timestamp >= ${timeRange.start}
            AND timestamp <= ${timeRange.end}
          GROUP BY step
          ORDER BY step
        ) as funnel_data
      `)

    return await funnelQuery
  }

  // Get retention data
  async getRetention(cohortPeriod: 'day' | 'week' | 'month', periods: number) {
    return await this.db.select({
      cohortDate: sql<Date>`cohort_date`,
      period: sql<number>`period`,
      retainedUsers: sql<number>`retained_users`,
      retentionRate: sql<number>`retention_rate`
    }).from(sql`
        (
          WITH cohorts AS (
            SELECT
              DATE_TRUNC('${cohortPeriod}', MIN(timestamp)) as cohort_date,
              user_id,
              MIN(timestamp) as first_event
            FROM ${analyticsEvents}
            WHERE user_id IS NOT NULL
            GROUP BY user_id
          ),
          retention AS (
            SELECT
              c.cohort_date,
              c.user_id,
              DATE_TRUNC('${cohortPeriod}', e.timestamp) as period_date,
              EXTRACT(EPOCH FROM (e.timestamp - c.first_event)) / (EXTRACT(EPOCH FROM (DATE_TRUNC('${cohortPeriod}', e.timestamp) - DATE_TRUNC('${cohortPeriod}', c.first_event)))) as period
            FROM cohorts c
            JOIN ${analyticsEvents} e ON c.user_id = e.user_id
            WHERE e.timestamp >= c.first_event
          )
          SELECT
            cohort_date,
            period,
            COUNT(DISTINCT user_id) as retained_users,
            ROUND(
              (COUNT(DISTINCT user_id)::float / FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (PARTITION BY cohort_date ORDER BY period)) * 100,
              2
            ) as retention_rate
          FROM retention
          WHERE period <= ${periods}
          GROUP BY cohort_date, period
          ORDER BY cohort_date, period
        ) as retention_data
      `)
  }

  // Get real-time metrics
  async getRealtimeMetrics(timeWindow: number = 300) {
    // 5 minutes default
    const cutoffTime = new Date(Date.now() - timeWindow * 1000)

    return await this.db
      .select({
        event: analyticsEvents.event,
        count: sql<number>`count`,
        uniqueUsers: sql<number>`unique_users`,
        avgProperties: sql<number>`avg_properties`
      })
      .from(analyticsEvents)
      .where(
        and(
          gt(analyticsEvents.timestamp, cutoffTime),
          eq(analyticsEvents.processed, false)
        )
      )
      .groupBy(analyticsEvents.event)
      .orderBy(desc(sql`count`))
  }
}

// Usage
const queries = new AnalyticsQueries(db)
const funnelData = await queries.getUserFunnel(
  ['page_view', 'add_to_cart', 'checkout', 'purchase'],
  { start: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000), end: new Date() }
)

// Express middleware with Drizzle
import { Request, Response, NextFunction } from 'express'
import entrolytics from './lib/entrolytics'

export const analyticsMiddleware = async (
  req: Request,
  res: Response,
  next: NextFunction
) => {
  // Get or create session
  const sessionId =
    req.cookies['analytics-session'] || (await entrolytics.createSession())

  // Track page view
  await entrolytics.page(req.path, {
    method: req.method,
    userAgent: req.get('User-Agent'),
    ip: req.ip,
    referer: req.get('Referer')
  })

  // Track API requests
  if (req.path.startsWith('/api/')) {
    await entrolytics.track('api_request', {
      endpoint: req.path,
      method: req.method,
      apiVersion: req.get('API-Version')
    })
  }

  // Identify authenticated users
  if (req.user) {
    await entrolytics.identify(req.user.id, {
      email: req.user.email,
      role: req.user.role
    })
  }

  // Set session cookie
  if (!req.cookies['analytics-session']) {
    res.cookie('analytics-session', sessionId, {
      httpOnly: true,
      secure: process.env.NODE_ENV === 'production',
      maxAge: 30 * 24 * 60 * 60 * 1000 // 30 days
    })
  }

  next()
}

// Error tracking middleware
export const errorTrackingMiddleware = (
  error: Error,
  req: Request,
  res: Response,
  next: NextFunction
) => {
  entrolytics
    .track('error_occurred', {
      error: error.message,
      stack: error.stack,
      url: req.url,
      method: req.method,
      userAgent: req.get('User-Agent')
    })
    .catch(console.error)

  next(error)
}

// pages/_app.tsx
import type { AppProps } from 'next/app'
import { useEffect } from 'react'
import entrolytics from '../lib/entrolytics'

export default function App({ Component, pageProps }: AppProps) {
  useEffect(() => {
    // Track page views
    const handleRouteChange = (url: string) => {
      entrolytics.page(url, {
        title: document.title,
        referrer: document.referrer,
      })
    }

    // Initial page view
    handleRouteChange(window.location.pathname)

    // Listen for route changes
    router.events.on('routeChangeComplete', handleRouteChange)

    return () => {
      router.events.off('routeChangeComplete', handleRouteChange)
    }
  }, [])

  return <Component {...pageProps} />
}

// API route example
// pages/api/track.ts
import type { NextApiRequest, NextApiResponse } from 'next'
import entrolytics from '../../lib/entrolytics'

export default async function handler(req: NextApiRequest, res: NextApiResponse) {
  if (req.method !== 'POST') {
    return res.status(405).json({ error: 'Method not allowed' })
  }

  try {
    const { event, properties } = req.body

    await entrolytics.track(event, {
      ...properties,
      source: 'api',
      timestamp: new Date().toISOString(),
    })

    res.status(200).json({ success: true })
  } catch (error) {
    console.error('Analytics tracking error:', error)
    res.status(500).json({ error: 'Failed to track event' })
  }
}

// Background event processor
class EventProcessor {
  private processing = false

  constructor(private entrolytics: EntrolyticsDrizzleClient) {}

  async start() {
    this.processing = true

    while (this.processing) {
      try {
        const processedCount = await this.entrolytics.processPendingEvents()

        if (processedCount > 0) {
          console.log(`Processed ${processedCount} events`)
        }

        // Sync to remote API
        await this.entrolytics.syncToRemote()

        // Wait before next batch
        await new Promise((resolve) => setTimeout(resolve, 5000))
      } catch (error) {
        console.error('Event processing error:', error)
        await new Promise((resolve) => setTimeout(resolve, 10000))
      }
    }
  }

  stop() {
    this.processing = false
  }
}

// Start background processor
const processor = new EventProcessor(entrolytics)
processor.start()

// Graceful shutdown
process.on('SIGTERM', async () => {
  console.log('Shutting down event processor...')
  processor.stop()
  await entrolytics.flush()
  process.exit(0)
})

// Scheduled aggregation jobs
class AggregationService {
  constructor(private db: DrizzleDB) {}

  // Daily event aggregation
  async aggregateDailyEvents(date: Date = new Date()) {
    const startOfDay = new Date(date)
    startOfDay.setHours(0, 0, 0, 0)

    const endOfDay = new Date(date)
    endOfDay.setHours(23, 59, 59, 999)

    return await this.db
      .insert(dailyEventAggregates)
      .values(
        this.db
          .select({
            date: sql<Date>`${startOfDay}`,
            event: analyticsEvents.event,
            count: sql<number>`count(*)`,
            uniqueUsers: sql<number>`count(DISTINCT user_id)`,
            uniqueSessions: sql<number>`count(DISTINCT session_id)`
          })
          .from(analyticsEvents)
          .where(
            and(
              gte(analyticsEvents.timestamp, startOfDay),
              lte(analyticsEvents.timestamp, endOfDay)
            )
          )
          .groupBy(analyticsEvents.event)
      )
      .onConflictDoUpdate({
        target: dailyEventAggregates.date,
        set: {
          count: sql`excluded.count`,
          uniqueUsers: sql`excluded.unique_users`,
          uniqueSessions: sql`excluded.unique_sessions`
        }
      })
  }

  // User activity aggregation
  async aggregateUserActivity(userId: string, timeRange: TimeRange) {
    return await this.db
      .select({
        totalEvents: sql<number>`count(*)`,
        uniqueEvents: sql<number>`count(DISTINCT event)`,
        firstEvent: sql<Date>`min(timestamp)`,
        lastEvent: sql<Date>`max(timestamp)`,
        avgEventsPerDay: sql<number>`count(*) / extract(days from max(timestamp) - min(timestamp))`
      })
      .from(analyticsEvents)
      .where(
        and(
          eq(analyticsEvents.userId, userId),
          gte(analyticsEvents.timestamp, timeRange.start),
          lte(analyticsEvents.timestamp, timeRange.end)
        )
      )
  }
}

// drizzle/migrations/0001_initial_schema.sql
CREATE TABLE IF NOT EXISTS "analytics_events" (
"id" serial PRIMARY KEY NOT NULL,
"website_id" text NOT NULL,
"session_id" text NOT NULL,
"user_id" text,
"event" text NOT NULL,
"properties" jsonb,
"timestamp" timestamp DEFAULT now() NOT NULL,
"processed" boolean DEFAULT false NOT NULL
);

--> CREATE INDEX "analytics_events_website_id_idx" ON "analytics_events" ("website_id");
--> CREATE INDEX "analytics_events_session_id_idx" ON "analytics_events" ("session_id");
--> CREATE INDEX "analytics_events_user_id_idx" ON "analytics_events" ("user_id");
--> CREATE INDEX "analytics_events_event_idx" ON "analytics_events" ("event");
--> CREATE INDEX "analytics_events_timestamp_idx" ON "analytics_events" ("timestamp");
--> CREATE INDEX "analytics_events_processed_idx" ON "analytics_events" ("processed");

CREATE TABLE IF NOT EXISTS "analytics_users" (
"id" text PRIMARY KEY NOT NULL,
"email" text,
"name" text,
"properties" jsonb,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL
);

--> CREATE INDEX "analytics_users_email_idx" ON "analytics_users" ("email");

CREATE TABLE IF NOT EXISTS "analytics_sessions" (
"id" text PRIMARY KEY NOT NULL,
"user_id" text,
"properties" jsonb,
"started_at" timestamp DEFAULT now() NOT NULL,
"ended_at" timestamp,
"is_active" boolean DEFAULT true NOT NULL
);

--> CREATE INDEX "analytics_sessions_user_id_idx" ON "analytics_sessions" ("user_id");
--> CREATE INDEX "analytics_sessions_started_at_idx" ON "analytics_sessions" ("started_at");
--> CREATE INDEX "analytics_sessions_is_active_idx" ON "analytics_sessions" ("is_active");

ALTER TABLE "analytics_events" ADD CONSTRAINT "analytics_events_user_id_fkey" FOREIGN KEY("user_id") REFERENCES "analytics_users"("id") ON DELETE no action ON UPDATE no action;
ALTER TABLE "analytics_events" ADD CONSTRAINT "analytics_events_session_id_fkey" FOREIGN KEY("session_id") REFERENCES "analytics_sessions"("id") ON DELETE no action ON UPDATE no action;
ALTER TABLE "analytics_sessions" ADD CONSTRAINT "analytics_sessions_user_id_fkey" FOREIGN KEY("user_id") REFERENCES "analytics_users"("id") ON DELETE no action ON UPDATE no action;

import { EntrolyticsDrizzleClient } from '@entrolytics/drizzle-client'
import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import { migrate } from 'drizzle-orm/postgres-js/migrator'
import * as schema from './drizzle/schema'

describe('EntrolyticsDrizzleClient', () => {
  let client: EntrolyticsDrizzleClient
  let db: ReturnType<typeof drizzle>

  beforeAll(async () => {
    // Setup test database
    const connectionString = process.env.TEST_DATABASE_URL!
    const migrationClient = postgres(connectionString, { max: 1 })
    db = drizzle(migrationClient, { schema })

    // Run migrations
    await migrate(db, { migrationsFolder: './drizzle/migrations' })

    // Initialize client
    client = new EntrolyticsDrizzleClient({
      db,
      websiteId: 'test-website-id',
      schema: {
        events: schema.analyticsEvents,
        users: schema.analyticsUsers,
        sessions: schema.analyticsSessions
      }
    })
  })

  afterAll(async () => {
    await db.execute(`DROP SCHEMA public CASCADE`)
    await db.execute(`CREATE SCHEMA public`)
  })

  beforeEach(async () => {
    await db.delete(schema.analyticsEvents)
    await db.delete(schema.analyticsUsers)
    await db.delete(schema.analyticsSessions)
  })

  test('should track events', async () => {
    await client.track('test_event', { property: 'value' })

    const events = await client.getEvents()
    expect(events).toHaveLength(1)
    expect(events[0].event).toBe('test_event')
    expect(events[0].properties).toEqual({ property: 'value' })
  })

  test('should identify users', async () => {
    await client.identify('user-123', { email: 'test@example.com' })

    const user = await client.getUser('user-123')
    expect(user).toBeTruthy()
    expect(user?.email).toBe('test@example.com')
  })

  test('should create sessions', async () => {
    const sessionId = await client.createSession('user-123', { source: 'web' })

    const session = await client.getSession(sessionId)
    expect(session).toBeTruthy()
    expect(session?.userId).toBe('user-123')
    expect(session?.properties?.source).toBe('web')
  })
})

describe('Analytics Integration', () => {
  let app: Express
  let client: EntrolyticsDrizzleClient

  beforeAll(async () => {
    client = new EntrolyticsDrizzleClient(testConfig)

    app = express()
    app.use(express.json())
    app.use(analyticsMiddleware)

    app.post('/api/track', async (req, res) => {
      await client.track(req.body.event, req.body.properties)
      res.json({ success: true })
    })
  })

  test('should track API requests', async () => {
    const response = await request(app)
      .post('/api/track')
      .send({ event: 'test_event', properties: { test: true } })
      .expect(200)

    expect(response.body.success).toBe(true)

    const events = await client.getEvents()
    expect(events).toHaveLength(1)
  })
})

-- Additional performance indexes
CREATE INDEX CONCURRENTLY "analytics_events_composite_idx"
ON "analytics_events" ("website_id", "timestamp", "event");

CREATE INDEX CONCURRENTLY "analytics_events_user_time_idx"
ON "analytics_events" ("user_id", "timestamp");

CREATE INDEX CONCURRENTLY "analytics_events_session_time_idx"
ON "analytics_events" ("session_id", "timestamp");

-- Partial indexes for unprocessed events
CREATE INDEX CONCURRENTLY "analytics_events_unprocessed_idx"
ON "analytics_events" ("timestamp")
WHERE "processed" = false;

// Optimized database connection
const connectionConfig = {
  max: 20, // Maximum connections
  idle_timeout: 30000, // 30 seconds
  connect_timeout: 10000 // 10 seconds
}

const client = postgres(process.env.DATABASE_URL!, connectionConfig)
export const db = drizzle(client, { schema })

// Efficient batch processing
class BatchProcessor {
  constructor(
    private client: EntrolyticsDrizzleClient,
    private batchSize: number = 1000
  ) {}

  async processBatch(events: AnalyticsEvent[]): Promise<void> {
    // Use transaction for batch insert
    await this.client.db.transaction(async (tx) => {
      await tx.insert(analyticsEvents).values(events)
    })
  }

  async processLargeDataset(): Promise<void> {
    let offset = 0
    const batchSize = this.batchSize

    while (true) {
      const events = await this.client.db
        .select()
        .from(analyticsEvents)
        .where(eq(analyticsEvents.processed, false))
        .limit(batchSize)
        .offset(offset)

      if (events.length === 0) break

      await this.processBatch(events)
      offset += batchSize
    }
  }
}

// Before - Direct Drizzle
import { db } from './drizzle/db'

await db.insert(analyticsEvents).values({
  event: 'user_action',
  properties: { type: 'click' },
  timestamp: new Date()
})

// After - Entrolytics Drizzle Client
import entrolytics from './lib/entrolytics'

await entrolytics.track('user_action', { type: 'click' })

// Before - Other analytics
import Analytics from 'other-analytics'

Analytics.track('event', properties)

// After - Entrolytics with Drizzle
import { EntrolyticsDrizzleClient } from '@entrolytics/drizzle-client'

const client = new EntrolyticsDrizzleClient(config)
await client.track('event', properties)

// Additional database features
const events = await client.getEvents({ timeRange: '1h' })
const user = await client.getUser('user-123')