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

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 corpId = await resolveCorpId(this.dataSource, query.corp);
    const overviewCorpClause = corpSessionFilter(3, corpId);
    const channelsCorpClause = corpSessionFilter(4, corpId);

    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${overviewCorpClause}
      ),
      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${channelsCorpClause}
      ),
      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
    `;

    const overviewParams: (string | number)[] = [startDate, endDate];
    if (corpId !== null) overviewParams.push(corpId);
    const channelsParams: (string | number)[] = [startDate, endDate, limit ?? 20];
    if (corpId !== null) channelsParams.push(corpId);

    try {
      const [overviewResult, channelsResult] = await Promise.all([
        this.dataSource.query(overviewQuery, overviewParams),
        this.dataSource.query(channelsQuery, channelsParams),
      ]);

      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 from session_fingerprints.
   * Groups by (utmSource | trafficSource) + (utmMedium | classification),
   * computing engaged_sessions and conversions inline against raw_events.
   * Optional channel filter narrows by sf."trafficSource".
   */
  async getSources(query: AcquisitionQueryDto): Promise<SourceMetrics[]> {
    const { startDate, endDate, channel, limit } = query;
    const corpId = await resolveCorpId(this.dataSource, query.corp);

    const params: (string | number)[] = [startDate, endDate];
    let paramIndex = 3;
    const extraConditions: string[] = [];

    if (channel) {
      extraConditions.push(`sf."trafficSource" = $${paramIndex}`);
      params.push(channel);
      paramIndex++;
    }

    if (corpId !== null) {
      extraConditions.push(corpSessionFilter(paramIndex, corpId).replace(/^\s*AND\s+/i, ''));
      params.push(corpId);
      paramIndex++;
    }

    const extraWhere = extraConditions.length > 0
      ? ' AND ' + extraConditions.join(' AND ')
      : '';

    const sourcesQuery = `
      WITH session_data AS (
        SELECT
          sf."sessionId",
          sf."userId",
          COALESCE(sf."utmSource", sf."trafficSource", 'direct') AS source,
          COALESCE(
            sf."utmMedium",
            CASE
              WHEN sf."trafficSource" IN ('organic', 'paid', 'social', 'referral', 'email', 'display') THEN sf."trafficSource"
              ELSE 'none'
            END
          ) 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
        FROM session_fingerprints sf
        WHERE sf."createdAt" BETWEEN $1 AND $2
          AND sf."isBot" = false${extraWhere}
      )
      SELECT
        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
      FROM session_data sd
      GROUP BY sd.source, sd.medium
      ORDER BY sessions DESC
      LIMIT $${paramIndex}
    `;

    params.push(limit ?? 20);

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

      return result.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 {
          source: row.source as string,
          medium: row.medium as string,
          sessions,
          users: Number(row.users) || 0,
          engagementRate: sessions > 0 ? engagedSessions / sessions : 0,
          conversions,
          conversionRate: sessions > 0 ? conversions / sessions : 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 corpId = await resolveCorpId(this.dataSource, query.corp);

    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++;
    }

    if (corpId !== null) {
      // Strip leading ' AND ' so the fragment slots into the AND-joined conditions list.
      conditions.push(corpSessionFilter(paramIndex, corpId).replace(/^\s*AND\s+/i, ''));
      params.push(corpId);
      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 corpId = await resolveCorpId(this.dataSource, query.corp);

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

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

    if (corpId !== null) {
      conditions.push(corpSessionFilter(paramIndex, corpId).replace(/^\s*AND\s+/i, ''));
      params.push(corpId);
      paramIndex++;
    }

    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 $${paramIndex}
    `;

    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,
      corp: query.corp,
    });

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

    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),
    };
  }
}
