import { Injectable, Logger } from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource } from 'typeorm';
import { SessionsQueryDto, SessionMetricsQueryDto, SessionSortBy } from './dto/sessions-query.dto';

export interface SessionRecord {
  sessionId: string;
  userId: string | null;
  startTime: Date;
  lastActivityTime: Date;
  durationSeconds: number;
  pageViews: number;
  totalEvents: number;
  hasConversion: boolean;
  trafficSource: string | null;
  deviceType: string | null;
  browser: string | null;
  os: string | null;
  country: string | null;
  city: string | null;
  landingPage: string | null;
}

export interface SessionMetrics {
  totalSessions: number;
  avgSessionDuration: number;
  avgPageViews: number;
  bounceRate: number;
  engagementRate: number;
  newUserRate: number;
  conversionRate: number;
}

export interface SessionsResult {
  sessions: SessionRecord[];
  total: number;
  limit: number;
  offset: number;
}

/**
 * Sessions service that queries session-level data from raw_events + session_fingerprints.
 * Sessions are computed on-demand rather than stored as separate entities.
 */
@Injectable()
export class SessionsService {
  private readonly logger = new Logger(SessionsService.name);

  constructor(
    @InjectDataSource()
    private readonly dataSource: DataSource,
  ) {}

  /**
   * Get list of sessions with computed metrics
   */
  async getSessions(query: SessionsQueryDto): Promise<SessionsResult> {
    const { startDate, endDate, userId, trafficSource, deviceType, country, sortBy, limit, offset } = query;

    // Build WHERE conditions
    const conditions: string[] = ['re.timestamp BETWEEN $1 AND $2'];
    const params: (string | number)[] = [startDate, endDate];
    let paramIndex = 3;

    if (userId) {
      conditions.push(`sf.userId = $${paramIndex}`);
      params.push(userId);
      paramIndex++;
    }
    if (trafficSource) {
      conditions.push(`sf.trafficSource = $${paramIndex}`);
      params.push(trafficSource);
      paramIndex++;
    }
    if (deviceType) {
      conditions.push(`sf.deviceType = $${paramIndex}`);
      params.push(deviceType);
      paramIndex++;
    }
    if (country) {
      conditions.push(`sf.country = $${paramIndex}`);
      params.push(country);
      paramIndex++;
    }

    const whereClause = conditions.join(' AND ');

    // Map sortBy to column
    const sortColumn = this.getSortColumn(sortBy);

    // Query sessions aggregated from raw_events + session_fingerprints
    const sessionsQuery = `
      WITH session_data AS (
        SELECT
          re."sessionId",
          sf."userId",
          MIN(re.timestamp) as start_time,
          MAX(re.timestamp) as last_activity_time,
          EXTRACT(EPOCH FROM (MAX(re.timestamp) - MIN(re.timestamp))) as duration_seconds,
          COUNT(CASE WHEN re."eventType" IN ('pageView', 'pageview') THEN 1 END) as page_views,
          COUNT(*) as total_events,
          BOOL_OR(re."eventType" IN ('conversion', 'purchase')) as has_conversion,
          sf."trafficSource",
          sf."deviceType",
          sf.browser,
          sf.os,
          sf.country,
          sf.city,
          sf."landingPage"
        FROM raw_events re
        LEFT JOIN session_fingerprints sf ON re."sessionId" = sf."sessionId"
        WHERE ${whereClause}
        GROUP BY re."sessionId", sf."userId", sf."trafficSource", sf."deviceType", sf.browser, sf.os, sf.country, sf.city, sf."landingPage"
      )
      SELECT * FROM session_data
      ORDER BY ${sortColumn} DESC
      LIMIT $${paramIndex} OFFSET $${paramIndex + 1}
    `;

    params.push(limit ?? 50, offset ?? 0);

    // Count query
    const countQuery = `
      SELECT COUNT(DISTINCT re."sessionId") as total
      FROM raw_events re
      LEFT JOIN session_fingerprints sf ON re."sessionId" = sf."sessionId"
      WHERE ${whereClause}
    `;

    try {
      const [sessionsResult, countResult] = await Promise.all([
        this.dataSource.query(sessionsQuery, params),
        this.dataSource.query(countQuery, params.slice(0, -2)),
      ]);

      const sessions: SessionRecord[] = sessionsResult.map((row: Record<string, unknown>) => ({
        sessionId: row.sessionId as string,
        userId: row.userId as string | null,
        startTime: new Date(row.start_time as string),
        lastActivityTime: new Date(row.last_activity_time as string),
        durationSeconds: Number(row.duration_seconds) || 0,
        pageViews: Number(row.page_views) || 0,
        totalEvents: Number(row.total_events) || 0,
        hasConversion: Boolean(row.has_conversion),
        trafficSource: row.trafficSource as string | null,
        deviceType: row.deviceType as string | null,
        browser: row.browser as string | null,
        os: row.os as string | null,
        country: row.country as string | null,
        city: row.city as string | null,
        landingPage: row.landingPage as string | null,
      }));

      return {
        sessions,
        total: Number(countResult[0]?.total) || 0,
        limit: limit ?? 50,
        offset: offset ?? 0,
      };
    } catch (error) {
      this.logger.error('Failed to query sessions', error);
      throw error;
    }
  }

  /**
   * Get aggregated session metrics
   */
  async getSessionMetrics(query: SessionMetricsQueryDto): Promise<SessionMetrics> {
    const { startDate, endDate, trafficSource, deviceType, country } = query;

    const conditions: string[] = ['re.timestamp BETWEEN $1 AND $2'];
    const params: (string | number)[] = [startDate, endDate];
    let paramIndex = 3;

    if (trafficSource) {
      conditions.push(`sf.trafficSource = $${paramIndex}`);
      params.push(trafficSource);
      paramIndex++;
    }
    if (deviceType) {
      conditions.push(`sf.deviceType = $${paramIndex}`);
      params.push(deviceType);
      paramIndex++;
    }
    if (country) {
      conditions.push(`sf.country = $${paramIndex}`);
      params.push(country);
    }

    const whereClause = conditions.join(' AND ');

    const metricsQuery = `
      WITH session_stats AS (
        SELECT
          re."sessionId",
          sf."userId",
          EXTRACT(EPOCH FROM (MAX(re.timestamp) - MIN(re.timestamp))) as duration_seconds,
          COUNT(CASE WHEN re."eventType" IN ('pageView', 'pageview') THEN 1 END) as page_views,
          BOOL_OR(re."eventType" IN ('conversion', 'purchase')) as has_conversion
        FROM raw_events re
        LEFT JOIN session_fingerprints sf ON re."sessionId" = sf."sessionId"
        WHERE ${whereClause}
        GROUP BY re."sessionId", sf."userId"
      ),
      user_first_session AS (
        SELECT sf."userId", MIN(sf."createdAt") as first_session
        FROM session_fingerprints sf
        WHERE sf."userId" IS NOT NULL
        GROUP BY sf."userId"
      )
      SELECT
        COUNT(DISTINCT ss."sessionId") as total_sessions,
        AVG(ss.duration_seconds) as avg_duration,
        AVG(ss.page_views) as avg_page_views,
        COUNT(CASE WHEN ss.page_views = 1 AND NOT ss.has_conversion THEN 1 END)::float / NULLIF(COUNT(*), 0) as bounce_rate,
        COUNT(CASE WHEN ss.duration_seconds >= 10 OR ss.page_views >= 2 OR ss.has_conversion THEN 1 END)::float / NULLIF(COUNT(*), 0) as engagement_rate,
        COUNT(CASE WHEN ufs.first_session >= $1 THEN 1 END)::float / NULLIF(COUNT(DISTINCT ss."userId"), 0) as new_user_rate,
        COUNT(CASE WHEN ss.has_conversion THEN 1 END)::float / NULLIF(COUNT(*), 0) as conversion_rate
      FROM session_stats ss
      LEFT JOIN user_first_session ufs ON ss."userId" = ufs."userId"
    `;

    try {
      const result = await this.dataSource.query(metricsQuery, params);
      const row = result[0] || {};

      return {
        totalSessions: Number(row.total_sessions) || 0,
        avgSessionDuration: Number(row.avg_duration) || 0,
        avgPageViews: Number(row.avg_page_views) || 0,
        bounceRate: Number(row.bounce_rate) || 0,
        engagementRate: Number(row.engagement_rate) || 0,
        newUserRate: Number(row.new_user_rate) || 0,
        conversionRate: Number(row.conversion_rate) || 0,
      };
    } catch (error) {
      this.logger.error('Failed to get session metrics', error);
      throw error;
    }
  }

  /**
   * Get session by ID with full event timeline
   */
  async getSession(sessionId: string): Promise<SessionRecord | null> {
    const query = `
      SELECT
        re."sessionId",
        sf."userId",
        MIN(re.timestamp) as start_time,
        MAX(re.timestamp) as last_activity_time,
        EXTRACT(EPOCH FROM (MAX(re.timestamp) - MIN(re.timestamp))) as duration_seconds,
        COUNT(CASE WHEN re."eventType" IN ('pageView', 'pageview') THEN 1 END) as page_views,
        COUNT(*) as total_events,
        BOOL_OR(re."eventType" IN ('conversion', 'purchase')) as has_conversion,
        sf."trafficSource",
        sf."deviceType",
        sf.browser,
        sf.os,
        sf.country,
        sf.city,
        sf."landingPage"
      FROM raw_events re
      LEFT JOIN session_fingerprints sf ON re."sessionId" = sf."sessionId"
      WHERE re."sessionId" = $1
      GROUP BY re."sessionId", sf."userId", sf."trafficSource", sf."deviceType", sf.browser, sf.os, sf.country, sf.city, sf."landingPage"
    `;

    try {
      const result = await this.dataSource.query(query, [sessionId]);
      if (result.length === 0) {
        return null;
      }

      const row = result[0];
      return {
        sessionId: row.sessionId,
        userId: row.userId,
        startTime: new Date(row.start_time),
        lastActivityTime: new Date(row.last_activity_time),
        durationSeconds: Number(row.duration_seconds) || 0,
        pageViews: Number(row.page_views) || 0,
        totalEvents: Number(row.total_events) || 0,
        hasConversion: Boolean(row.has_conversion),
        trafficSource: row.trafficSource,
        deviceType: row.deviceType,
        browser: row.browser,
        os: row.os,
        country: row.country,
        city: row.city,
        landingPage: row.landingPage,
      };
    } catch (error) {
      this.logger.error(`Failed to get session ${sessionId}`, error);
      throw error;
    }
  }

  private getSortColumn(sortBy?: SessionSortBy): string {
    switch (sortBy) {
      case SessionSortBy.DURATION:
        return 'duration_seconds';
      case SessionSortBy.PAGE_VIEWS:
        return 'page_views';
      case SessionSortBy.EVENTS:
        return 'total_events';
      case SessionSortBy.START_TIME:
      default:
        return 'start_time';
    }
  }
}
