import { Injectable, Logger } from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource } from 'typeorm';
import { AcquisitionQueryDto, AcquisitionCompareDto, ReferrerQueryDto } from './dto/acquisition-query.dto';

export interface ChannelMetrics {
  channel: string;
  sessions: number;
  users: number;
  newUsers: number;
  engagedSessions: number;
  engagementRate: number;
  conversions: number;
  conversionRate: number;
  revenue: number;
}

export interface SourceMetrics {
  source: string;
  medium: string;
  sessions: number;
  users: number;
  engagementRate: number;
  conversions: number;
  conversionRate: number;
}

export interface CampaignMetrics {
  campaign: string;
  source: string;
  medium: string;
  sessions: number;
  users: number;
  engagementRate: number;
  conversions: number;
  revenue: number;
  costPerConversion?: number;
}

export interface ReferrerMetrics {
  referrer: string;
  domain: string;
  sessions: number;
  users: number;
  engagementRate: number;
  conversions: number;
}

export interface AcquisitionOverview {
  totalSessions: number;
  totalUsers: number;
  newUsers: number;
  returningUsers: number;
  newUserRate: number;
  topChannels: ChannelMetrics[];
}

export interface AcquisitionComparison {
  current: AcquisitionOverview;
  previous: AcquisitionOverview;
  sessionsChange: number;
  usersChange: number;
  newUsersChange: number;
}

/**
 * Acquisition service for traffic source analytics.
 * Queries session_fingerprints for attribution data.
 */
@Injectable()
export class AcquisitionService {
  private readonly logger = new Logger(AcquisitionService.name);

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

  /**
   * Get acquisition overview with top channels
   */
  async getOverview(query: AcquisitionQueryDto): Promise<AcquisitionOverview> {
    const { startDate, endDate, limit } = query;

    const overviewQuery = `
      WITH session_data AS (
        SELECT
          sf."sessionId",
          sf."userId",
          sf."trafficSource" as channel,
          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
        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 sd."sessionId") as total_sessions,
        COUNT(DISTINCT sd."userId") as total_users,
        COUNT(DISTINCT CASE WHEN uf.first_session >= $1 THEN sd."userId" END) as new_users,
        COUNT(DISTINCT CASE WHEN uf.first_session < $1 THEN sd."userId" END) as returning_users
      FROM session_data sd
      LEFT JOIN user_first uf ON sd."userId" = uf."userId"
    `;

    const channelsQuery = `
      WITH session_data AS (
        SELECT
          sf."sessionId",
          sf."userId",
          COALESCE(sf."trafficSource", 'direct') as channel,
          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
      ),
      user_first AS (
        SELECT "userId", MIN("createdAt") as first_session
        FROM session_fingerprints
        WHERE "userId" IS NOT NULL
        GROUP BY "userId"
      )
      SELECT
        sd.channel,
        COUNT(DISTINCT sd."sessionId") as sessions,
        COUNT(DISTINCT sd."userId") as users,
        COUNT(DISTINCT CASE WHEN uf.first_session >= $1 THEN sd."userId" END) as new_users,
        COUNT(DISTINCT CASE WHEN sd.duration_seconds >= 10 OR sd.page_views >= 2 OR sd.has_conversion THEN sd."sessionId" END) as engaged_sessions,
        COUNT(DISTINCT CASE WHEN sd.has_conversion THEN sd."sessionId" END) as conversions,
        SUM(sd.revenue) as revenue
      FROM session_data sd
      LEFT JOIN user_first uf ON sd."userId" = uf."userId"
      GROUP BY sd.channel
      ORDER BY sessions DESC
      LIMIT $3
    `;

    try {
      const [overviewResult, channelsResult] = await Promise.all([
        this.dataSource.query(overviewQuery, [startDate, endDate]),
        this.dataSource.query(channelsQuery, [startDate, endDate, limit ?? 20]),
      ]);

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

      const topChannels: ChannelMetrics[] = channelsResult.map((row: Record<string, unknown>) => {
        const sessions = Number(row.sessions) || 0;
        const engagedSessions = Number(row.engaged_sessions) || 0;
        const conversions = Number(row.conversions) || 0;

        return {
          channel: row.channel as string,
          sessions,
          users: Number(row.users) || 0,
          newUsers: Number(row.new_users) || 0,
          engagedSessions,
          engagementRate: sessions > 0 ? engagedSessions / sessions : 0,
          conversions,
          conversionRate: sessions > 0 ? conversions / sessions : 0,
          revenue: Number(row.revenue) || 0,
        };
      });

      return {
        totalSessions,
        totalUsers,
        newUsers,
        returningUsers,
        newUserRate: totalUsers > 0 ? newUsers / totalUsers : 0,
        topChannels,
      };
    } catch (error) {
      this.logger.error('Failed to get acquisition overview', error);
      throw error;
    }
  }

  /**
   * Get channel breakdown
   */
  async getChannels(query: AcquisitionQueryDto): Promise<ChannelMetrics[]> {
    const overview = await this.getOverview(query);
    return overview.topChannels;
  }

  /**
   * Get source/medium breakdown.
   * Prefers aggregated_metrics traffic_source dimension; falls back to
   * deriving source from raw_events.referrer when no aggregated data exists.
   */
  async getSources(query: AcquisitionQueryDto): Promise<SourceMetrics[]> {
    const { startDate, endDate, limit } = query;

    // First try the aggregated_metrics traffic_source dimension (populated by processor)
    const aggregatedQuery = `
      WITH source_totals AS (
        SELECT
          "dimensionValue" AS source,
          SUM(value) AS sessions
        FROM aggregated_metrics
        WHERE "metricType" = 'page_views'
          AND dimension = 'traffic_source'
          AND "dimensionValue" IS NOT NULL
          AND timestamp BETWEEN $1 AND $2
        GROUP BY "dimensionValue"
      )
      SELECT source, sessions FROM source_totals
      ORDER BY sessions DESC
      LIMIT $3
    `;

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

      if (aggregated.length > 0) {
        return aggregated.map((row: Record<string, unknown>) => ({
          source: row.source as string,
          medium: 'none',
          sessions: Number(row.sessions) || 0,
          users: 0,
          engagementRate: 0,
          conversions: 0,
          conversionRate: 0,
        }));
      }

      // Fallback: derive traffic source from raw_events.referrer per unique session
      const rawQuery = `
        WITH first_event AS (
          SELECT DISTINCT ON ("sessionId")
            "sessionId",
            referrer,
            "deviceType",
            CASE
              WHEN referrer IS NULL OR referrer = '' THEN 'direct'
              WHEN referrer LIKE '%google.%' OR referrer LIKE '%bing.%'
                OR referrer LIKE '%yahoo.%' OR referrer LIKE '%duckduckgo.%' THEN 'organic'
              WHEN referrer LIKE '%twitter.%' OR referrer LIKE '%t.co%' THEN 'twitter'
              WHEN referrer LIKE '%facebook.%' OR referrer LIKE '%fb.com%' THEN 'facebook'
              WHEN referrer LIKE '%instagram.%' THEN 'instagram'
              WHEN referrer LIKE '%reddit.%' THEN 'reddit'
              WHEN referrer LIKE '%onlyfans.%' THEN 'onlyfans'
              WHEN referrer IS NOT NULL AND referrer != ''
                THEN COALESCE(substring(referrer FROM 'https?://([^/]+)'), 'referral')
              ELSE 'direct'
            END AS source
          FROM raw_events
          WHERE "eventType" IN ('pageview', 'pageView')
            AND "deviceType" IS DISTINCT FROM 'bot'
            AND timestamp BETWEEN $1 AND $2
          ORDER BY "sessionId", timestamp ASC
        )
        SELECT
          source,
          'none' AS medium,
          COUNT(DISTINCT "sessionId") AS sessions,
          0::int AS users,
          0::int AS conversions
        FROM first_event
        GROUP BY source
        ORDER BY sessions DESC
        LIMIT $3
      `;

      const raw = await this.dataSource.query(rawQuery, [startDate, endDate, limit ?? 20]);

      return raw.map((row: Record<string, unknown>) => ({
        source: row.source as string,
        medium: row.medium as string,
        sessions: Number(row.sessions) || 0,
        users: 0,
        engagementRate: 0,
        conversions: 0,
        conversionRate: 0,
      }));
    } catch (error) {
      this.logger.error('Failed to get sources', error);
      throw error;
    }
  }

  /**
   * Get campaign performance
   */
  async getCampaigns(query: AcquisitionQueryDto): Promise<CampaignMetrics[]> {
    const { startDate, endDate, source, limit } = query;

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

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

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

    const campaignsQuery = `
      WITH session_data AS (
        SELECT
          sf."sessionId",
          sf."userId",
          sf."utmCampaign" as campaign,
          COALESCE(sf."utmSource", 'direct') as source,
          COALESCE(sf."utmMedium", 'none') as medium,
          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 ${whereClause}
      )
      SELECT
        sd.campaign,
        sd.source,
        sd.medium,
        COUNT(DISTINCT sd."sessionId") as sessions,
        COUNT(DISTINCT sd."userId") as users,
        COUNT(DISTINCT CASE WHEN sd.duration_seconds >= 10 OR sd.page_views >= 2 OR sd.has_conversion THEN sd."sessionId" END) as engaged_sessions,
        COUNT(DISTINCT CASE WHEN sd.has_conversion THEN sd."sessionId" END) as conversions,
        SUM(sd.revenue) as revenue
      FROM session_data sd
      GROUP BY sd.campaign, sd.source, sd.medium
      ORDER BY sessions DESC
      LIMIT $${paramIndex}
    `;

    params.push(limit ?? 20);

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

      return result.map((row: Record<string, unknown>) => {
        const sessions = Number(row.sessions) || 0;
        const engagedSessions = Number(row.engaged_sessions) || 0;

        return {
          campaign: row.campaign as string,
          source: row.source as string,
          medium: row.medium as string,
          sessions,
          users: Number(row.users) || 0,
          engagementRate: sessions > 0 ? engagedSessions / sessions : 0,
          conversions: Number(row.conversions) || 0,
          revenue: Number(row.revenue) || 0,
        };
      });
    } catch (error) {
      this.logger.error('Failed to get campaigns', error);
      throw error;
    }
  }

  /**
   * Get top referrers
   */
  async getReferrers(query: ReferrerQueryDto): Promise<ReferrerMetrics[]> {
    const { startDate, endDate, excludeInternal, limit } = query;

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

    if (excludeInternal) {
      conditions.push("sf.referrer NOT LIKE '%atlilith.%'");
      conditions.push("sf.referrer NOT LIKE '%trustedmeet.%'");
    }

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

    const referrersQuery = `
      WITH session_data AS (
        SELECT
          sf."sessionId",
          sf."userId",
          sf.referrer,
          substring(sf.referrer from 'https?://([^/]+)') as domain,
          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}
      )
      SELECT
        sd.referrer,
        sd.domain,
        COUNT(DISTINCT sd."sessionId") as sessions,
        COUNT(DISTINCT sd."userId") as users,
        COUNT(DISTINCT CASE WHEN sd.duration_seconds >= 10 OR sd.page_views >= 2 OR sd.has_conversion THEN sd."sessionId" END) as engaged_sessions,
        COUNT(DISTINCT CASE WHEN sd.has_conversion THEN sd."sessionId" END) as conversions
      FROM session_data sd
      WHERE sd.domain IS NOT NULL
      GROUP BY sd.referrer, sd.domain
      ORDER BY sessions DESC
      LIMIT $3
    `;

    params.push(limit ?? 20);

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

      return result.map((row: Record<string, unknown>) => {
        const sessions = Number(row.sessions) || 0;
        const engagedSessions = Number(row.engaged_sessions) || 0;

        return {
          referrer: row.referrer as string,
          domain: row.domain as string,
          sessions,
          users: Number(row.users) || 0,
          engagementRate: sessions > 0 ? engagedSessions / sessions : 0,
          conversions: Number(row.conversions) || 0,
        };
      });
    } catch (error) {
      this.logger.error('Failed to get referrers', error);
      throw error;
    }
  }

  /**
   * Compare acquisition metrics between two periods
   */
  async compare(query: AcquisitionCompareDto): Promise<AcquisitionComparison> {
    const current = await this.getOverview({
      startDate: query.startDate,
      endDate: query.endDate,
      limit: query.limit,
    });

    const previous = await this.getOverview({
      startDate: query.compareStartDate,
      endDate: query.compareEndDate,
      limit: query.limit,
    });

    const calcChange = (curr: number, prev: number) =>
      prev > 0 ? ((curr - prev) / prev) * 100 : curr > 0 ? 100 : 0;

    return {
      current,
      previous,
      sessionsChange: calcChange(current.totalSessions, previous.totalSessions),
      usersChange: calcChange(current.totalUsers, previous.totalUsers),
      newUsersChange: calcChange(current.newUsers, previous.newUsers),
    };
  }
}
