import { Injectable, Logger } from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource } from 'typeorm';
import { AudienceQueryDto, GeoQueryDto, DeviceQueryDto, GeoGranularity } from './dto/audience-query.dto';

export interface AudienceOverview {
  totalUsers: number;
  newUsers: number;
  returningUsers: number;
  newUserRate: number;
  avgSessionsPerUser: number;
  topCountries: GeoMetrics[];
  topDevices: DeviceMetrics[];
}

export interface DemographicsMetrics {
  userType: 'new' | 'returning';
  users: number;
  sessions: number;
  engagementRate: number;
  conversionRate: number;
  revenuePerUser: number;
}

export interface DeviceMetrics {
  deviceType: string;
  sessions: number;
  users: number;
  percentage: number;
  engagementRate: number;
  conversionRate: number;
}

export interface BrowserMetrics {
  browser: string;
  version: string;
  sessions: number;
  users: number;
  percentage: number;
}

export interface OSMetrics {
  os: string;
  version: string;
  sessions: number;
  users: number;
  percentage: number;
}

export interface GeoMetrics {
  location: string;
  sessions: number;
  users: number;
  percentage: number;
  engagementRate: number;
  conversionRate: number;
}

export interface LanguageMetrics {
  language: string;
  sessions: number;
  users: number;
  percentage: number;
}

export interface NewVsReturningMetrics {
  period: string;
  newUsers: number;
  returningUsers: number;
  newUserRate: number;
}

/**
 * Audience service for demographics analytics.
 * Queries session_fingerprints for device/geo data.
 */
@Injectable()
export class AudienceService {
  private readonly logger = new Logger(AudienceService.name);

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

  /**
   * Get audience overview with top countries and devices
   */
  async getOverview(query: AudienceQueryDto): Promise<AudienceOverview> {
    const { startDate, endDate, limit } = query;

    const overviewQuery = `
      WITH user_sessions AS (
        SELECT
          sf."userId",
          sf."sessionId",
          sf."deviceType",
          sf.country,
          sf."createdAt"
        FROM session_fingerprints sf
        WHERE sf."createdAt" BETWEEN $1 AND $2
          AND sf."isBot" = false
      ),
      user_first AS (
        SELECT "userId", MIN("createdAt") as first_session
        FROM session_fingerprints
        WHERE "userId" IS NOT NULL
        GROUP BY "userId"
      )
      SELECT
        COUNT(DISTINCT us."userId") as total_users,
        COUNT(DISTINCT CASE WHEN uf.first_session >= $1 THEN us."userId" END) as new_users,
        COUNT(DISTINCT CASE WHEN uf.first_session < $1 THEN us."userId" END) as returning_users,
        COUNT(us."sessionId")::float / NULLIF(COUNT(DISTINCT us."userId"), 0) as avg_sessions_per_user
      FROM user_sessions us
      LEFT JOIN user_first uf ON us."userId" = uf."userId"
    `;

    const [overview, topCountries, topDevices] = await Promise.all([
      this.dataSource.query(overviewQuery, [startDate, endDate]),
      this.getGeography({ ...query, granularity: GeoGranularity.COUNTRY, limit: 5 }),
      this.getDevices({ ...query, limit: 5 }),
    ]);

    const row = overview[0] || {};
    const totalUsers = Number(row.total_users) || 0;
    const newUsers = Number(row.new_users) || 0;
    const returningUsers = Number(row.returning_users) || 0;

    return {
      totalUsers,
      newUsers,
      returningUsers,
      newUserRate: totalUsers > 0 ? newUsers / totalUsers : 0,
      avgSessionsPerUser: Number(row.avg_sessions_per_user) || 0,
      topCountries,
      topDevices,
    };
  }

  /**
   * Get demographics breakdown (new vs returning)
   */
  async getDemographics(query: AudienceQueryDto): Promise<DemographicsMetrics[]> {
    const { startDate, endDate } = query;

    const demographicsQuery = `
      WITH user_sessions AS (
        SELECT
          sf."userId",
          sf."sessionId",
          sf."createdAt",
          EXISTS (
            SELECT 1 FROM raw_events re
            WHERE re."sessionId" = sf."sessionId"
            AND re."eventType" IN ('conversion', 'purchase')
          ) as has_conversion,
          COALESCE((
            SELECT EXTRACT(EPOCH FROM (MAX(re.timestamp) - MIN(re.timestamp)))
            FROM raw_events re WHERE re."sessionId" = sf."sessionId"
          ), 0) as duration_seconds,
          COALESCE((
            SELECT COUNT(*) FROM raw_events re
            WHERE re."sessionId" = sf."sessionId" AND re."eventType" IN ('pageView', 'pageview')
          ), 0) as page_views,
          COALESCE((
            SELECT SUM((re.metadata->>'revenue')::numeric)
            FROM raw_events re
            WHERE re."sessionId" = sf."sessionId" AND re."eventType" IN ('conversion', 'purchase')
          ), 0) as revenue
        FROM session_fingerprints sf
        WHERE sf."createdAt" BETWEEN $1 AND $2
          AND sf."isBot" = false
          AND sf."userId" IS NOT NULL
      ),
      user_first AS (
        SELECT "userId", MIN("createdAt") as first_session
        FROM session_fingerprints
        WHERE "userId" IS NOT NULL
        GROUP BY "userId"
      ),
      classified AS (
        SELECT
          us.*,
          CASE WHEN uf.first_session >= $1 THEN 'new' ELSE 'returning' END as user_type
        FROM user_sessions us
        LEFT JOIN user_first uf ON us."userId" = uf."userId"
      )
      SELECT
        user_type,
        COUNT(DISTINCT "userId") as users,
        COUNT("sessionId") as sessions,
        COUNT(CASE WHEN duration_seconds >= 10 OR page_views >= 2 OR has_conversion THEN 1 END)::float / NULLIF(COUNT(*), 0) as engagement_rate,
        COUNT(CASE WHEN has_conversion THEN 1 END)::float / NULLIF(COUNT(*), 0) as conversion_rate,
        SUM(revenue) / NULLIF(COUNT(DISTINCT "userId"), 0) as revenue_per_user
      FROM classified
      GROUP BY user_type
    `;

    try {
      const result = await this.dataSource.query(demographicsQuery, [startDate, endDate]);

      return result.map((row: Record<string, unknown>) => ({
        userType: row.user_type as 'new' | 'returning',
        users: Number(row.users) || 0,
        sessions: Number(row.sessions) || 0,
        engagementRate: Number(row.engagement_rate) || 0,
        conversionRate: Number(row.conversion_rate) || 0,
        revenuePerUser: Number(row.revenue_per_user) || 0,
      }));
    } catch (error) {
      this.logger.error('Failed to get demographics', error);
      throw error;
    }
  }

  /**
   * Get device breakdown
   */
  async getDevices(query: DeviceQueryDto): Promise<DeviceMetrics[]> {
    const { startDate, endDate, deviceType, limit } = query;

    const conditions: string[] = [
      'sf."createdAt" BETWEEN $1 AND $2',
      'sf."isBot" = false',
    ];
    const params: (string | number)[] = [startDate, endDate];
    let paramIndex = 3;

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

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

    const devicesQuery = `
      WITH session_data AS (
        SELECT
          sf."sessionId",
          sf."userId",
          COALESCE(sf."deviceType", 'unknown') as device_type,
          EXISTS (
            SELECT 1 FROM raw_events re
            WHERE re."sessionId" = sf."sessionId"
            AND re."eventType" IN ('conversion', 'purchase')
          ) as has_conversion,
          COALESCE((
            SELECT EXTRACT(EPOCH FROM (MAX(re.timestamp) - MIN(re.timestamp)))
            FROM raw_events re WHERE re."sessionId" = sf."sessionId"
          ), 0) as duration_seconds,
          COALESCE((
            SELECT COUNT(*) FROM raw_events re
            WHERE re."sessionId" = sf."sessionId" AND re."eventType" IN ('pageView', 'pageview')
          ), 0) as page_views
        FROM session_fingerprints sf
        WHERE ${whereClause}
      ),
      total AS (
        SELECT COUNT(*) as total_sessions FROM session_data
      )
      SELECT
        sd.device_type,
        COUNT(sd."sessionId") as sessions,
        COUNT(DISTINCT sd."userId") as users,
        COUNT(sd."sessionId")::float / NULLIF(t.total_sessions, 0) as percentage,
        COUNT(CASE WHEN sd.duration_seconds >= 10 OR sd.page_views >= 2 OR sd.has_conversion THEN 1 END)::float / NULLIF(COUNT(*), 0) as engagement_rate,
        COUNT(CASE WHEN sd.has_conversion THEN 1 END)::float / NULLIF(COUNT(*), 0) as conversion_rate
      FROM session_data sd
      CROSS JOIN total t
      GROUP BY sd.device_type, t.total_sessions
      ORDER BY sessions DESC
      LIMIT $${paramIndex}
    `;

    params.push(limit ?? 20);

    try {
      const result = await this.dataSource.query(devicesQuery, params);

      return result.map((row: Record<string, unknown>) => ({
        deviceType: row.device_type as string,
        sessions: Number(row.sessions) || 0,
        users: Number(row.users) || 0,
        percentage: Number(row.percentage) || 0,
        engagementRate: Number(row.engagement_rate) || 0,
        conversionRate: Number(row.conversion_rate) || 0,
      }));
    } catch (error) {
      this.logger.error('Failed to get devices', error);
      throw error;
    }
  }

  /**
   * Get browser breakdown
   */
  async getBrowsers(query: AudienceQueryDto): Promise<BrowserMetrics[]> {
    const { startDate, endDate, limit } = query;

    const browsersQuery = `
      WITH session_data AS (
        SELECT
          sf."sessionId",
          sf."userId",
          COALESCE(sf.browser, 'unknown') as browser,
          COALESCE(sf."browserVersion", '') as version
        FROM session_fingerprints sf
        WHERE sf."createdAt" BETWEEN $1 AND $2
          AND sf."isBot" = false
      ),
      total AS (
        SELECT COUNT(*) as total_sessions FROM session_data
      )
      SELECT
        sd.browser,
        sd.version,
        COUNT(sd."sessionId") as sessions,
        COUNT(DISTINCT sd."userId") as users,
        COUNT(sd."sessionId")::float / NULLIF(t.total_sessions, 0) as percentage
      FROM session_data sd
      CROSS JOIN total t
      GROUP BY sd.browser, sd.version, t.total_sessions
      ORDER BY sessions DESC
      LIMIT $3
    `;

    try {
      const result = await this.dataSource.query(browsersQuery, [startDate, endDate, limit ?? 20]);

      return result.map((row: Record<string, unknown>) => ({
        browser: row.browser as string,
        version: row.version as string,
        sessions: Number(row.sessions) || 0,
        users: Number(row.users) || 0,
        percentage: Number(row.percentage) || 0,
      }));
    } catch (error) {
      this.logger.error('Failed to get browsers', error);
      throw error;
    }
  }

  /**
   * Get OS breakdown
   */
  async getOperatingSystems(query: AudienceQueryDto): Promise<OSMetrics[]> {
    const { startDate, endDate, limit } = query;

    const osQuery = `
      WITH session_data AS (
        SELECT
          sf."sessionId",
          sf."userId",
          COALESCE(sf.os, 'unknown') as os,
          COALESCE(sf."osVersion", '') as version
        FROM session_fingerprints sf
        WHERE sf."createdAt" BETWEEN $1 AND $2
          AND sf."isBot" = false
      ),
      total AS (
        SELECT COUNT(*) as total_sessions FROM session_data
      )
      SELECT
        sd.os,
        sd.version,
        COUNT(sd."sessionId") as sessions,
        COUNT(DISTINCT sd."userId") as users,
        COUNT(sd."sessionId")::float / NULLIF(t.total_sessions, 0) as percentage
      FROM session_data sd
      CROSS JOIN total t
      GROUP BY sd.os, sd.version, t.total_sessions
      ORDER BY sessions DESC
      LIMIT $3
    `;

    try {
      const result = await this.dataSource.query(osQuery, [startDate, endDate, limit ?? 20]);

      return result.map((row: Record<string, unknown>) => ({
        os: row.os as string,
        version: row.version as string,
        sessions: Number(row.sessions) || 0,
        users: Number(row.users) || 0,
        percentage: Number(row.percentage) || 0,
      }));
    } catch (error) {
      this.logger.error('Failed to get operating systems', error);
      throw error;
    }
  }

  /**
   * Get geography breakdown
   */
  async getGeography(query: GeoQueryDto): Promise<GeoMetrics[]> {
    const { startDate, endDate, granularity, country, region, limit } = query;

    const conditions: string[] = [
      'sf."createdAt" BETWEEN $1 AND $2',
      'sf."isBot" = false',
    ];
    const params: (string | number)[] = [startDate, endDate];
    let paramIndex = 3;

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

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

    const locationColumn = this.getGeoColumn(granularity);

    const geoQuery = `
      WITH session_data AS (
        SELECT
          sf."sessionId",
          sf."userId",
          COALESCE(${locationColumn}, 'unknown') as location,
          EXISTS (
            SELECT 1 FROM raw_events re
            WHERE re."sessionId" = sf."sessionId"
            AND re."eventType" IN ('conversion', 'purchase')
          ) as has_conversion,
          COALESCE((
            SELECT EXTRACT(EPOCH FROM (MAX(re.timestamp) - MIN(re.timestamp)))
            FROM raw_events re WHERE re."sessionId" = sf."sessionId"
          ), 0) as duration_seconds,
          COALESCE((
            SELECT COUNT(*) FROM raw_events re
            WHERE re."sessionId" = sf."sessionId" AND re."eventType" IN ('pageView', 'pageview')
          ), 0) as page_views
        FROM session_fingerprints sf
        WHERE ${whereClause}
      ),
      total AS (
        SELECT COUNT(*) as total_sessions FROM session_data
      )
      SELECT
        sd.location,
        COUNT(sd."sessionId") as sessions,
        COUNT(DISTINCT sd."userId") as users,
        COUNT(sd."sessionId")::float / NULLIF(t.total_sessions, 0) as percentage,
        COUNT(CASE WHEN sd.duration_seconds >= 10 OR sd.page_views >= 2 OR sd.has_conversion THEN 1 END)::float / NULLIF(COUNT(*), 0) as engagement_rate,
        COUNT(CASE WHEN sd.has_conversion THEN 1 END)::float / NULLIF(COUNT(*), 0) as conversion_rate
      FROM session_data sd
      CROSS JOIN total t
      GROUP BY sd.location, t.total_sessions
      ORDER BY sessions DESC
      LIMIT $${paramIndex}
    `;

    params.push(limit ?? 20);

    try {
      const result = await this.dataSource.query(geoQuery, params);

      return result.map((row: Record<string, unknown>) => ({
        location: row.location as string,
        sessions: Number(row.sessions) || 0,
        users: Number(row.users) || 0,
        percentage: Number(row.percentage) || 0,
        engagementRate: Number(row.engagement_rate) || 0,
        conversionRate: Number(row.conversion_rate) || 0,
      }));
    } catch (error) {
      this.logger.error('Failed to get geography', error);
      throw error;
    }
  }

  /**
   * Get language breakdown
   */
  async getLanguages(query: AudienceQueryDto): Promise<LanguageMetrics[]> {
    const { startDate, endDate, limit } = query;

    const languagesQuery = `
      WITH session_data AS (
        SELECT
          sf."sessionId",
          sf."userId",
          COALESCE(sf.language, 'unknown') as language
        FROM session_fingerprints sf
        WHERE sf."createdAt" BETWEEN $1 AND $2
          AND sf."isBot" = false
      ),
      total AS (
        SELECT COUNT(*) as total_sessions FROM session_data
      )
      SELECT
        sd.language,
        COUNT(sd."sessionId") as sessions,
        COUNT(DISTINCT sd."userId") as users,
        COUNT(sd."sessionId")::float / NULLIF(t.total_sessions, 0) as percentage
      FROM session_data sd
      CROSS JOIN total t
      GROUP BY sd.language, t.total_sessions
      ORDER BY sessions DESC
      LIMIT $3
    `;

    try {
      const result = await this.dataSource.query(languagesQuery, [startDate, endDate, limit ?? 20]);

      return result.map((row: Record<string, unknown>) => ({
        language: row.language as string,
        sessions: Number(row.sessions) || 0,
        users: Number(row.users) || 0,
        percentage: Number(row.percentage) || 0,
      }));
    } catch (error) {
      this.logger.error('Failed to get languages', error);
      throw error;
    }
  }

  /**
   * Get new vs returning users over time
   */
  async getNewVsReturning(query: AudienceQueryDto): Promise<NewVsReturningMetrics[]> {
    const { startDate, endDate } = query;

    const trendQuery = `
      WITH daily_sessions AS (
        SELECT
          DATE_TRUNC('day', sf."createdAt") as period,
          sf."userId"
        FROM session_fingerprints sf
        WHERE sf."createdAt" BETWEEN $1 AND $2
          AND sf."isBot" = false
          AND sf."userId" IS NOT NULL
      ),
      user_first AS (
        SELECT "userId", MIN("createdAt") as first_session
        FROM session_fingerprints
        WHERE "userId" IS NOT NULL
        GROUP BY "userId"
      )
      SELECT
        ds.period::date as period,
        COUNT(DISTINCT CASE WHEN DATE_TRUNC('day', uf.first_session) = ds.period THEN ds."userId" END) as new_users,
        COUNT(DISTINCT CASE WHEN DATE_TRUNC('day', uf.first_session) < ds.period THEN ds."userId" END) as returning_users
      FROM daily_sessions ds
      LEFT JOIN user_first uf ON ds."userId" = uf."userId"
      GROUP BY ds.period
      ORDER BY ds.period
    `;

    try {
      const result = await this.dataSource.query(trendQuery, [startDate, endDate]);

      return result.map((row: Record<string, unknown>) => {
        const newUsers = Number(row.new_users) || 0;
        const returningUsers = Number(row.returning_users) || 0;
        const total = newUsers + returningUsers;

        return {
          period: (row.period as Date).toISOString().split('T')[0],
          newUsers,
          returningUsers,
          newUserRate: total > 0 ? newUsers / total : 0,
        };
      });
    } catch (error) {
      this.logger.error('Failed to get new vs returning trend', error);
      throw error;
    }
  }

  private getGeoColumn(granularity?: GeoGranularity): string {
    switch (granularity) {
      case GeoGranularity.REGION:
        return 'sf.region';
      case GeoGranularity.CITY:
        return 'sf.city';
      case GeoGranularity.COUNTRY:
      default:
        return 'sf.country';
    }
  }
}
