import { Injectable, Logger } from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource } from 'typeorm';
import {
  EngagementQueryDto,
  PageQueryDto,
  EventQueryDto,
  ScrollDepthQueryDto,
  UserFlowQueryDto,
  NavigationFlowsQueryDto,
  PageSortBy,
  EventCategory,
} from './dto/engagement-query.dto';

export interface EngagementOverview {
  engagementRate: number;
  avgSessionDuration: number;
  pagesPerSession: number;
  bounceRate: number;
  eventsPerSession: number;
  totalPageViews: number;
  uniquePageViews: number;
  totalEvents: number;
}

export interface PageMetrics {
  path: string;
  views: number;
  uniqueViews: number;
  avgTimeOnPage: number;
  entrances: number;
  exits: number;
  bounceRate: number;
  exitRate: number;
}

export interface EventMetrics {
  eventName: string;
  eventLabel: string | null;
  category: string;
  count: number;
  uniqueUsers: number;
}

export interface EventByPageMetrics {
  eventName: string;
  eventLabel: string | null;
  page: string;
  count: number;
  uniqueUsers: number;
}

export interface ScrollDepthMetrics {
  page: string;
  depth25: number;
  depth50: number;
  depth75: number;
  depth100: number;
  avgScrollDepth: number;
}

export interface UserFlowStep {
  page: string;
  sessions: number;
  dropOff: number;
  dropOffRate: number;
}

export interface UserFlow {
  startPage: string;
  totalSessions: number;
  steps: UserFlowStep[];
}

export interface NavigationTransition {
  to: string;
  sessions: number;
  percentage: number;
}

export interface NavigationFlowData {
  from: string;
  total: number;
  transitions: NavigationTransition[];
}

/**
 * Engagement service for user behavior analytics.
 * Queries raw_events for page and event metrics.
 */
@Injectable()
export class EngagementService {
  private readonly logger = new Logger(EngagementService.name);

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

  /**
   * Get engagement overview metrics
   */
  async getOverview(query: EngagementQueryDto): Promise<EngagementOverview> {
    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 overviewQuery = `
      WITH session_data AS (
        SELECT
          re."sessionId",
          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
        FROM raw_events re
        LEFT JOIN session_fingerprints sf ON re."sessionId" = sf."sessionId"
        WHERE ${whereClause}
        GROUP BY re."sessionId"
      )
      SELECT
        COUNT(*) as total_sessions,
        COUNT(CASE WHEN duration_seconds >= 10 OR page_views >= 2 OR has_conversion THEN 1 END) as engaged_sessions,
        AVG(duration_seconds) as avg_duration,
        AVG(page_views) as avg_page_views,
        AVG(total_events) as avg_events,
        COUNT(CASE WHEN page_views = 1 AND NOT has_conversion THEN 1 END) as bounced_sessions,
        SUM(page_views) as total_page_views,
        SUM(total_events) as total_events
      FROM session_data
    `;

    const uniqueViewsQuery = `
      SELECT COUNT(DISTINCT re."sessionId" || ':' || re."pageUrl") as unique_views
      FROM raw_events re
      LEFT JOIN session_fingerprints sf ON re."sessionId" = sf."sessionId"
      WHERE ${whereClause}
        AND re."eventType" IN ('pageView', 'pageview')
    `;

    try {
      const [overviewResult, uniqueResult] = await Promise.all([
        this.dataSource.query(overviewQuery, params),
        this.dataSource.query(uniqueViewsQuery, params),
      ]);

      const overview = overviewResult[0] || {};
      const totalSessions = Number(overview.total_sessions) || 0;
      const engagedSessions = Number(overview.engaged_sessions) || 0;
      const bouncedSessions = Number(overview.bounced_sessions) || 0;

      return {
        engagementRate: totalSessions > 0 ? engagedSessions / totalSessions : 0,
        avgSessionDuration: Number(overview.avg_duration) || 0,
        pagesPerSession: Number(overview.avg_page_views) || 0,
        bounceRate: totalSessions > 0 ? bouncedSessions / totalSessions : 0,
        eventsPerSession: Number(overview.avg_events) || 0,
        totalPageViews: Number(overview.total_page_views) || 0,
        uniquePageViews: Number(uniqueResult[0]?.unique_views) || 0,
        totalEvents: Number(overview.total_events) || 0,
      };
    } catch (error) {
      this.logger.error('Failed to get engagement overview', error);
      throw error;
    }
  }

  /**
   * Get page performance metrics
   */
  async getPages(query: PageQueryDto): Promise<PageMetrics[]> {
    const { startDate, endDate, pathPattern, sortBy, limit, trafficSource, deviceType, country } = query;

    const conditions: string[] = [
      'pv.timestamp BETWEEN $1 AND $2',
      "pv.\"eventType\" IN ('pageView', 'pageview')",
      'pv."pageUrl" IS NOT NULL',
    ];
    const params: (string | number)[] = [startDate, endDate];
    let paramIndex = 3;

    if (pathPattern) {
      conditions.push(`pv."pageUrl" LIKE $${paramIndex}`);
      params.push(`%${pathPattern}%`);
      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 ');
    const sortColumn = this.getPageSortColumn(sortBy);

    const pagesQuery = `
      WITH page_views AS (
        SELECT
          pv."sessionId",
          pv."pageUrl" as path,
          pv.timestamp,
          ROW_NUMBER() OVER (PARTITION BY pv."sessionId" ORDER BY pv.timestamp) as page_num,
          LAG(pv.timestamp) OVER (PARTITION BY pv."sessionId" ORDER BY pv.timestamp) as prev_timestamp,
          LEAD(pv.timestamp) OVER (PARTITION BY pv."sessionId" ORDER BY pv.timestamp) as next_timestamp,
          COUNT(*) OVER (PARTITION BY pv."sessionId") as session_page_count
        FROM raw_events pv
        LEFT JOIN session_fingerprints sf ON pv."sessionId" = sf."sessionId"
        WHERE ${whereClause}
      ),
      page_metrics AS (
        SELECT
          path,
          COUNT(*) as views,
          COUNT(DISTINCT "sessionId") as unique_views,
          AVG(CASE WHEN next_timestamp IS NOT NULL
              THEN EXTRACT(EPOCH FROM (next_timestamp - timestamp))
              ELSE 30 END) as avg_time_on_page,
          COUNT(CASE WHEN page_num = 1 THEN 1 END) as entrances,
          COUNT(CASE WHEN next_timestamp IS NULL THEN 1 END) as exits,
          COUNT(CASE WHEN session_page_count = 1 THEN 1 END) as bounces
        FROM page_views
        GROUP BY path
      )
      SELECT
        path,
        views,
        unique_views,
        avg_time_on_page,
        entrances,
        exits,
        CASE WHEN entrances > 0 THEN bounces::float / entrances ELSE 0 END as bounce_rate,
        CASE WHEN views > 0 THEN exits::float / views ELSE 0 END as exit_rate
      FROM page_metrics
      ORDER BY ${sortColumn} DESC
      LIMIT $${paramIndex}
    `;

    params.push(limit ?? 20);

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

      return result.map((row: Record<string, unknown>) => ({
        path: row.path as string,
        views: Number(row.views) || 0,
        uniqueViews: Number(row.unique_views) || 0,
        avgTimeOnPage: Number(row.avg_time_on_page) || 0,
        entrances: Number(row.entrances) || 0,
        exits: Number(row.exits) || 0,
        bounceRate: Number(row.bounce_rate) || 0,
        exitRate: Number(row.exit_rate) || 0,
      }));
    } catch (error) {
      this.logger.error('Failed to get page metrics', error);
      throw error;
    }
  }

  /**
   * Get event breakdown
   */
  async getEvents(query: EventQueryDto): Promise<EventMetrics[]> {
    const { startDate, endDate, category, eventType, limit, trafficSource, deviceType, country } = query;

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

    // Exclude page views
    conditions.push("re.\"eventType\" NOT IN ('pageView', 'pageview')");

    if (category && category !== EventCategory.ALL) {
      const categoryPatterns: Record<EventCategory, string> = {
        [EventCategory.CLICK]: 'click%',
        [EventCategory.SCROLL]: 'scroll%',
        [EventCategory.FORM]: 'form%',
        [EventCategory.VIDEO]: 'video%',
        [EventCategory.CUSTOM]: '%',
        [EventCategory.ALL]: '%',
      };
      conditions.push(`re."eventType" LIKE $${paramIndex}`);
      params.push(categoryPatterns[category]);
      paramIndex++;
    }
    if (eventType) {
      conditions.push(`re."eventType" = $${paramIndex}`);
      params.push(eventType);
      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 ');

    const eventsQuery = `
      SELECT
        COALESCE(re.metadata->>'eventName', re."eventType") as event_name,
        re.metadata->>'eventLabel' as event_label,
        CASE
          WHEN re."eventType" LIKE 'click%' THEN 'click'
          WHEN re."eventType" LIKE 'scroll%' THEN 'scroll'
          WHEN re."eventType" LIKE 'form%' THEN 'form'
          WHEN re."eventType" LIKE 'video%' THEN 'video'
          ELSE 'custom'
        END as category,
        COUNT(*) as count,
        COUNT(DISTINCT COALESCE(re."userId", re."sessionId")) as unique_users
      FROM raw_events re
      LEFT JOIN session_fingerprints sf ON re."sessionId" = sf."sessionId"
      WHERE ${whereClause}
      GROUP BY event_name, re.metadata->>'eventLabel', category
      ORDER BY count DESC
      LIMIT $${paramIndex}
    `;

    params.push(limit ?? 100);

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

      return result.map((row: Record<string, unknown>) => ({
        eventName: row.event_name as string,
        eventLabel: (row.event_label as string | null) ?? null,
        category: row.category as string,
        count: Number(row.count) || 0,
        uniqueUsers: Number(row.unique_users) || 0,
      }));
    } catch (error) {
      this.logger.error('Failed to get event metrics', error);
      throw error;
    }
  }

  /**
   * Get event breakdown grouped by page
   */
  async getEventsByPage(query: EventQueryDto): Promise<EventByPageMetrics[]> {
    const { startDate, endDate, category, eventType, limit, trafficSource, deviceType, country } = query;

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

    conditions.push("re.\"eventType\" NOT IN ('pageView', 'pageview')");

    if (category && category !== EventCategory.ALL) {
      const categoryPatterns: Record<EventCategory, string> = {
        [EventCategory.CLICK]: 'click%',
        [EventCategory.SCROLL]: 'scroll%',
        [EventCategory.FORM]: 'form%',
        [EventCategory.VIDEO]: 'video%',
        [EventCategory.CUSTOM]: '%',
        [EventCategory.ALL]: '%',
      };
      conditions.push(`re."eventType" LIKE $${paramIndex}`);
      params.push(categoryPatterns[category]);
      paramIndex++;
    }
    if (eventType) {
      conditions.push(`re."eventType" = $${paramIndex}`);
      params.push(eventType);
      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 ');

    const query_ = `
      SELECT
        COALESCE(re.metadata->>'eventName', re."eventType") as event_name,
        re.metadata->>'eventLabel' as event_label,
        COALESCE(
          regexp_replace(COALESCE(re."pageUrl", re.metadata->>'pageUrl'), '^https?://[^/]+', ''),
          '(unknown)'
        ) as page,
        COUNT(*) as count,
        COUNT(DISTINCT COALESCE(re."userId", re."sessionId")) as unique_users
      FROM raw_events re
      LEFT JOIN session_fingerprints sf ON re."sessionId" = sf."sessionId"
      WHERE ${whereClause}
      GROUP BY event_name, re.metadata->>'eventLabel', page
      ORDER BY count DESC
      LIMIT $${paramIndex}
    `;

    params.push(limit ?? 500);

    try {
      const result = await this.dataSource.query(query_, params);
      return result.map((row: Record<string, unknown>) => ({
        eventName: row.event_name as string,
        eventLabel: (row.event_label as string | null) ?? null,
        page: row.page as string,
        count: Number(row.count) || 0,
        uniqueUsers: Number(row.unique_users) || 0,
      }));
    } catch (error) {
      this.logger.error('Failed to get events by page', error);
      throw error;
    }
  }

  /**
   * Get scroll depth metrics
   */
  async getScrollDepth(query: ScrollDepthQueryDto): Promise<ScrollDepthMetrics[]> {
    const { startDate, endDate, page } = query;

    const conditions: string[] = [
      'timestamp BETWEEN $1 AND $2',
      "\"eventType\" LIKE 'scroll%'",
    ];
    const params: (string | number)[] = [startDate, endDate];
    let paramIndex = 3;

    if (page) {
      conditions.push(`"pageUrl" LIKE $${paramIndex}`);
      params.push(`%${page}%`);
    }

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

    const scrollQuery = `
      SELECT
        "pageUrl" as page,
        COUNT(CASE WHEN (metadata->>'depth')::int >= 25 THEN 1 END) as depth_25,
        COUNT(CASE WHEN (metadata->>'depth')::int >= 50 THEN 1 END) as depth_50,
        COUNT(CASE WHEN (metadata->>'depth')::int >= 75 THEN 1 END) as depth_75,
        COUNT(CASE WHEN (metadata->>'depth')::int >= 100 THEN 1 END) as depth_100,
        AVG(COALESCE((metadata->>'depth')::int, 0)) as avg_depth,
        COUNT(*) as total
      FROM raw_events
      WHERE ${whereClause}
      GROUP BY "pageUrl"
      ORDER BY total DESC
      LIMIT 20
    `;

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

      return result.map((row: Record<string, unknown>) => {
        const total = Number(row.total) || 1;
        return {
          page: row.page as string,
          depth25: (Number(row.depth_25) || 0) / total,
          depth50: (Number(row.depth_50) || 0) / total,
          depth75: (Number(row.depth_75) || 0) / total,
          depth100: (Number(row.depth_100) || 0) / total,
          avgScrollDepth: Number(row.avg_depth) || 0,
        };
      });
    } catch (error) {
      this.logger.error('Failed to get scroll depth', error);
      throw error;
    }
  }

  /**
   * Get user flow (path analysis)
   */
  async getUserFlow(query: UserFlowQueryDto): Promise<UserFlow[]> {
    const { startDate, endDate, startPage, steps, limit } = query;

    const conditions: string[] = [
      'timestamp BETWEEN $1 AND $2',
      "\"eventType\" IN ('pageView', 'pageview')",
      '"pageUrl" IS NOT NULL',
    ];
    const params: (string | number)[] = [startDate, endDate];
    let paramIndex = 3;

    if (startPage) {
      conditions.push(`"pageUrl" LIKE $${paramIndex}`);
      params.push(`%${startPage}%`);
      paramIndex++;
    }

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

    const stepsParamIndex = paramIndex;
    params.push(steps ?? 5);
    paramIndex++;

    const limitParamIndex = paramIndex;
    params.push(limit ?? 10);

    // This is a simplified user flow - full implementation would use recursive CTEs
    const flowQuery = `
      WITH session_pages AS (
        SELECT
          "sessionId",
          "pageUrl" as page,
          ROW_NUMBER() OVER (PARTITION BY "sessionId" ORDER BY timestamp) as step_num
        FROM raw_events
        WHERE ${whereClause}
      ),
      first_pages AS (
        SELECT page, COUNT(DISTINCT "sessionId") as sessions
        FROM session_pages
        WHERE step_num = 1
        GROUP BY page
        ORDER BY sessions DESC
        LIMIT $${limitParamIndex}
      )
      SELECT
        fp.page as start_page,
        fp.sessions as total_sessions,
        sp.step_num,
        sp.page as step_page,
        COUNT(DISTINCT sp."sessionId") as step_sessions
      FROM first_pages fp
      JOIN session_pages sp ON sp."sessionId" IN (
        SELECT "sessionId" FROM session_pages WHERE page = fp.page AND step_num = 1
      )
      WHERE sp.step_num <= $${stepsParamIndex}
      GROUP BY fp.page, fp.sessions, sp.step_num, sp.page
      ORDER BY fp.sessions DESC, sp.step_num ASC, step_sessions DESC
    `;

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

      // Group results by start page
      const flowMap = new Map<string, UserFlow>();

      for (const row of result) {
        const startPageKey = row.start_page as string;
        if (!flowMap.has(startPageKey)) {
          flowMap.set(startPageKey, {
            startPage: startPageKey,
            totalSessions: Number(row.total_sessions) || 0,
            steps: [],
          });
        }

        const flow = flowMap.get(startPageKey)!;
        const stepNum = Number(row.step_num);
        const stepSessions = Number(row.step_sessions) || 0;
        const prevSessions = stepNum > 1 && flow.steps.length > 0
          ? flow.steps[flow.steps.length - 1].sessions
          : flow.totalSessions;

        flow.steps.push({
          page: row.step_page as string,
          sessions: stepSessions,
          dropOff: prevSessions - stepSessions,
          dropOffRate: prevSessions > 0 ? (prevSessions - stepSessions) / prevSessions : 0,
        });
      }

      return Array.from(flowMap.values());
    } catch (error) {
      this.logger.error('Failed to get user flow', error);
      throw error;
    }
  }

  /**
   * Get page-to-page navigation flows: given a source page path, returns where
   * visitors went next within the same session, with session counts and percentages.
   * `from` is matched as a path segment against stored full URLs via LIKE.
   */
  async getNavigationFlows(query: NavigationFlowsQueryDto): Promise<NavigationFlowData> {
    const { from, startDate, endDate, limit = 10 } = query;
    const pattern = `%${from}%`;

    const sql = `
      WITH ordered AS (
        SELECT
          "sessionId",
          "pageUrl",
          LEAD("pageUrl") OVER (PARTITION BY "sessionId" ORDER BY timestamp) as next_url
        FROM raw_events
        WHERE timestamp BETWEEN $1 AND $2
          AND "eventType" IN ('pageView', 'pageview')
          AND "pageUrl" IS NOT NULL
      ),
      from_rows AS (
        SELECT
          COALESCE(
            CASE WHEN next_url IS NOT NULL
                 THEN regexp_replace(next_url, '^https?://[^/]+', '')
            END,
            '(exit)'
          ) as to_page,
          COUNT(DISTINCT "sessionId") as sessions
        FROM ordered
        WHERE "pageUrl" LIKE $3
        GROUP BY to_page
      ),
      total AS (
        SELECT COUNT(DISTINCT "sessionId") as n
        FROM ordered
        WHERE "pageUrl" LIKE $3
      )
      SELECT f.to_page, f.sessions,
             ROUND(100.0 * f.sessions / NULLIF(t.n, 0), 1) as percentage,
             t.n as total
      FROM from_rows f, total t
      ORDER BY f.sessions DESC
      LIMIT $4
    `;

    try {
      const rows: Array<{ to_page: string; sessions: string; percentage: string; total: string }> =
        await this.dataSource.query(sql, [startDate, endDate, pattern, limit]);
      const total = rows[0] ? Number(rows[0].total) : 0;
      return {
        from,
        total,
        transitions: rows.map((r) => ({
          to: r.to_page,
          sessions: Number(r.sessions),
          percentage: Number(r.percentage),
        })),
      };
    } catch (error) {
      this.logger.error('Failed to get navigation flows', error);
      throw error;
    }
  }

  private getPageSortColumn(sortBy?: PageSortBy): string {
    switch (sortBy) {
      case PageSortBy.UNIQUE_VIEWS:
        return 'unique_views';
      case PageSortBy.AVG_TIME:
        return 'avg_time_on_page';
      case PageSortBy.BOUNCE_RATE:
        return 'bounce_rate';
      case PageSortBy.ENGAGEMENT:
        return 'unique_views';
      case PageSortBy.VIEWS:
      default:
        return 'views';
    }
  }
}
