import { Injectable, Logger, NotFoundException, ConflictException } from '@nestjs/common';
import { InjectRepository, InjectDataSource } from '@nestjs/typeorm';
import { Repository, DataSource } from 'typeorm';
import { Segment } from './segment.entity';
import {
  CreateSegmentDto,
  UpdateSegmentDto,
  ApplySegmentQueryDto,
  CompareSegmentsQueryDto,
  SegmentConditionDto,
  SegmentOperator,
  ConditionOperator,
} from './dto/segment.dto';

export interface SegmentMetrics {
  segmentId: string;
  segmentName: string;
  sessions: number;
  users: number;
  engagementRate: number;
  conversionRate: number;
  avgSessionDuration: number;
  pagesPerSession: number;
  revenue: number;
}

export interface SegmentComparison {
  segments: SegmentMetrics[];
  metric: string;
  winner: string;
}

/**
 * Built-in segment definitions
 */
const BUILT_IN_SEGMENTS: CreateSegmentDto[] = [
  {
    name: 'Mobile Users',
    description: 'Users accessing from mobile devices',
    conditions: [{ dimension: 'device_type', operator: ConditionOperator.EQUALS, values: ['mobile'] }],
    operator: SegmentOperator.AND,
    isBuiltIn: true,
  },
  {
    name: 'Desktop Users',
    description: 'Users accessing from desktop devices',
    conditions: [{ dimension: 'device_type', operator: ConditionOperator.EQUALS, values: ['desktop'] }],
    operator: SegmentOperator.AND,
    isBuiltIn: true,
  },
  {
    name: 'Tablet Users',
    description: 'Users accessing from tablet devices',
    conditions: [{ dimension: 'device_type', operator: ConditionOperator.EQUALS, values: ['tablet'] }],
    operator: SegmentOperator.AND,
    isBuiltIn: true,
  },
  {
    name: 'Organic Traffic',
    description: 'Users from organic search',
    conditions: [{ dimension: 'traffic_source', operator: ConditionOperator.EQUALS, values: ['organic'] }],
    operator: SegmentOperator.AND,
    isBuiltIn: true,
  },
  {
    name: 'Paid Traffic',
    description: 'Users from paid campaigns',
    conditions: [{ dimension: 'traffic_source', operator: ConditionOperator.EQUALS, values: ['paid'] }],
    operator: SegmentOperator.AND,
    isBuiltIn: true,
  },
  {
    name: 'Social Traffic',
    description: 'Users from social media',
    conditions: [{ dimension: 'traffic_source', operator: ConditionOperator.EQUALS, values: ['social'] }],
    operator: SegmentOperator.AND,
    isBuiltIn: true,
  },
  {
    name: 'Direct Traffic',
    description: 'Users who came directly',
    conditions: [{ dimension: 'traffic_source', operator: ConditionOperator.EQUALS, values: ['direct'] }],
    operator: SegmentOperator.AND,
    isBuiltIn: true,
  },
  {
    name: 'Engaged Users',
    description: 'Users with engaged sessions (10s+ duration OR 2+ pages OR conversion)',
    conditions: [{ dimension: 'is_engaged', operator: ConditionOperator.EQUALS, values: ['true'] }],
    operator: SegmentOperator.AND,
    isBuiltIn: true,
  },
  {
    name: 'Converters',
    description: 'Users who completed a conversion',
    conditions: [{ dimension: 'has_conversion', operator: ConditionOperator.EQUALS, values: ['true'] }],
    operator: SegmentOperator.AND,
    isBuiltIn: true,
  },
  {
    name: 'New Users',
    description: 'First-time visitors',
    conditions: [{ dimension: 'is_new_user', operator: ConditionOperator.EQUALS, values: ['true'] }],
    operator: SegmentOperator.AND,
    isBuiltIn: true,
  },
  {
    name: 'Returning Users',
    description: 'Users who have visited before',
    conditions: [{ dimension: 'is_new_user', operator: ConditionOperator.EQUALS, values: ['false'] }],
    operator: SegmentOperator.AND,
    isBuiltIn: true,
  },
];

/**
 * Segments service for segment CRUD and evaluation
 */
@Injectable()
export class SegmentsService {
  private readonly logger = new Logger(SegmentsService.name);

  constructor(
    @InjectRepository(Segment)
    private readonly segmentRepository: Repository<Segment>,
    @InjectDataSource()
    private readonly dataSource: DataSource,
  ) {}

  /**
   * Initialize built-in segments
   */
  async onModuleInit(): Promise<void> {
    for (const segmentDef of BUILT_IN_SEGMENTS) {
      const existing = await this.segmentRepository.findOne({
        where: { name: segmentDef.name, isBuiltIn: true },
      });
      if (!existing) {
        await this.segmentRepository.save(this.segmentRepository.create(segmentDef));
        this.logger.log(`Created built-in segment: ${segmentDef.name}`);
      }
    }
  }

  /**
   * Get all segments
   */
  async findAll(): Promise<Segment[]> {
    return this.segmentRepository.find({
      order: { isBuiltIn: 'DESC', name: 'ASC' },
    });
  }

  /**
   * Get segment by ID
   */
  async findById(id: string): Promise<Segment> {
    const segment = await this.segmentRepository.findOne({ where: { id } });
    if (!segment) {
      throw new NotFoundException(`Segment ${id} not found`);
    }
    return segment;
  }

  /**
   * Create new segment
   */
  async create(dto: CreateSegmentDto): Promise<Segment> {
    const existing = await this.segmentRepository.findOne({
      where: { name: dto.name },
    });
    if (existing) {
      throw new ConflictException(`Segment with name "${dto.name}" already exists`);
    }

    const segment = this.segmentRepository.create(dto);
    return this.segmentRepository.save(segment);
  }

  /**
   * Update segment
   */
  async update(id: string, dto: UpdateSegmentDto): Promise<Segment> {
    const segment = await this.findById(id);

    if (segment.isBuiltIn) {
      throw new ConflictException('Cannot modify built-in segments');
    }

    if (dto.name && dto.name !== segment.name) {
      const existing = await this.segmentRepository.findOne({
        where: { name: dto.name },
      });
      if (existing) {
        throw new ConflictException(`Segment with name "${dto.name}" already exists`);
      }
    }

    Object.assign(segment, dto);
    return this.segmentRepository.save(segment);
  }

  /**
   * Delete segment
   */
  async delete(id: string): Promise<void> {
    const segment = await this.findById(id);

    if (segment.isBuiltIn) {
      throw new ConflictException('Cannot delete built-in segments');
    }

    await this.segmentRepository.remove(segment);
  }

  /**
   * Apply segment to get filtered metrics
   */
  async applySegment(segmentId: string, query: ApplySegmentQueryDto): Promise<SegmentMetrics> {
    const segment = await this.findById(segmentId);
    const { startDate, endDate } = query;

    const whereClause = this.buildWhereClause(segment.conditions, segment.operator);

    const metricsQuery = `
      WITH session_data AS (
        SELECT
          sf."sessionId",
          sf."userId",
          sf."deviceType",
          sf."trafficSource",
          sf.country,
          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,
          EXISTS (
            SELECT 1 FROM raw_events re
            WHERE re."sessionId" = sf."sessionId"
            AND re."eventType" IN ('conversion', 'purchase')
          ) as has_conversion,
          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,
          (
            COALESCE((SELECT EXTRACT(EPOCH FROM (MAX(re.timestamp) - MIN(re.timestamp))) FROM raw_events re WHERE re."sessionId" = sf."sessionId"), 0) >= 10
            OR COALESCE((SELECT COUNT(*) FROM raw_events re WHERE re."sessionId" = sf."sessionId" AND re."eventType" IN ('pageView', 'pageview')), 0) >= 2
            OR EXISTS (SELECT 1 FROM raw_events re WHERE re."sessionId" = sf."sessionId" AND re."eventType" IN ('conversion', 'purchase'))
          ) as is_engaged
        FROM session_fingerprints sf
        WHERE sf."createdAt" BETWEEN $1 AND $2
          AND sf."isBot" = false
          ${whereClause ? `AND ${whereClause}` : ''}
      )
      SELECT
        COUNT(DISTINCT "sessionId") as sessions,
        COUNT(DISTINCT "userId") as users,
        COUNT(CASE WHEN is_engaged 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,
        AVG(duration_seconds) as avg_duration,
        AVG(page_views) as avg_page_views,
        SUM(revenue) as total_revenue
      FROM session_data
    `;

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

      return {
        segmentId: segment.id,
        segmentName: segment.name,
        sessions: Number(row.sessions) || 0,
        users: Number(row.users) || 0,
        engagementRate: Number(row.engagement_rate) || 0,
        conversionRate: Number(row.conversion_rate) || 0,
        avgSessionDuration: Number(row.avg_duration) || 0,
        pagesPerSession: Number(row.avg_page_views) || 0,
        revenue: Number(row.total_revenue) || 0,
      };
    } catch (error) {
      this.logger.error(`Failed to apply segment ${segmentId}`, error);
      throw error;
    }
  }

  /**
   * Compare multiple segments
   */
  async compareSegments(query: CompareSegmentsQueryDto): Promise<SegmentComparison> {
    const { segmentIds, metric = 'sessions' } = query;

    const segments = await Promise.all(
      segmentIds.map((id) => this.applySegment(id, query)),
    );

    // Determine winner based on metric
    const metricKey = this.getMetricKey(metric);
    const sorted = [...segments].sort((a, b) => {
      const aValue = a[metricKey as keyof SegmentMetrics] as number;
      const bValue = b[metricKey as keyof SegmentMetrics] as number;
      return bValue - aValue;
    });

    return {
      segments,
      metric,
      winner: sorted[0]?.segmentName || '',
    };
  }

  /**
   * Build SQL WHERE clause from segment conditions
   */
  private buildWhereClause(conditions: SegmentConditionDto[], operator: SegmentOperator): string {
    if (conditions.length === 0) {
      return '';
    }

    const clauses = conditions.map((condition) => this.buildConditionClause(condition));
    const joinOperator = operator === SegmentOperator.AND ? ' AND ' : ' OR ';

    return `(${clauses.join(joinOperator)})`;
  }

  /**
   * Build SQL clause for single condition
   */
  private buildConditionClause(condition: SegmentConditionDto): string {
    const { dimension, operator, values } = condition;
    const column = this.getDimensionColumn(dimension);

    switch (operator) {
      case ConditionOperator.EQUALS:
        return `${column} = '${this.escapeValue(values[0])}'`;
      case ConditionOperator.NOT_EQUALS:
        return `${column} != '${this.escapeValue(values[0])}'`;
      case ConditionOperator.CONTAINS:
        return `${column} LIKE '%${this.escapeValue(values[0])}%'`;
      case ConditionOperator.NOT_CONTAINS:
        return `${column} NOT LIKE '%${this.escapeValue(values[0])}%'`;
      case ConditionOperator.IN:
        const inValues = values.map((v) => `'${this.escapeValue(v)}'`).join(', ');
        return `${column} IN (${inValues})`;
      case ConditionOperator.NOT_IN:
        const notInValues = values.map((v) => `'${this.escapeValue(v)}'`).join(', ');
        return `${column} NOT IN (${notInValues})`;
      case ConditionOperator.GREATER_THAN:
        return `${column} > '${this.escapeValue(values[0])}'`;
      case ConditionOperator.LESS_THAN:
        return `${column} < '${this.escapeValue(values[0])}'`;
      case ConditionOperator.BETWEEN:
        return `${column} BETWEEN '${this.escapeValue(values[0])}' AND '${this.escapeValue(values[1])}'`;
      default:
        return 'TRUE';
    }
  }

  /**
   * Map dimension name to SQL column
   */
  private getDimensionColumn(dimension: string): string {
    const columnMap: Record<string, string> = {
      device_type: 'sf."deviceType"',
      traffic_source: 'sf."trafficSource"',
      country: 'sf.country',
      region: 'sf.region',
      city: 'sf.city',
      browser: 'sf.browser',
      os: 'sf.os',
      language: 'sf.language',
      utm_source: 'sf."utmSource"',
      utm_medium: 'sf."utmMedium"',
      utm_campaign: 'sf."utmCampaign"',
      is_engaged: 'is_engaged',
      has_conversion: 'has_conversion',
      is_new_user: '(uf.first_session >= $1)',
    };

    return columnMap[dimension] || `sf."${dimension}"`;
  }

  /**
   * Map metric name to object key
   */
  private getMetricKey(metric: string): string {
    const metricMap: Record<string, string> = {
      sessions: 'sessions',
      users: 'users',
      engagement_rate: 'engagementRate',
      conversion_rate: 'conversionRate',
      revenue: 'revenue',
      avg_duration: 'avgSessionDuration',
      pages_per_session: 'pagesPerSession',
    };
    return metricMap[metric] || 'sessions';
  }

  /**
   * Escape SQL value to prevent injection
   */
  private escapeValue(value: string): string {
    return value.replace(/'/g, "''");
  }
}
