import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, Between } from 'typeorm';
import { AggregatedMetric, MetricType, TimeGranularity } from '../entities/aggregated-metric.entity';
import { TrendsQueryDto } from './dto/trends-query.dto';

export interface TrendDataPoint {
  timestamp: Date;
  value: number;
  count: number;
}

export interface TrendsResult {
  metric: string;
  granularity: string;
  data: TrendDataPoint[];
  summary: {
    total: number;
    average: number;
    min: number;
    max: number;
  };
}

const VALID_GRANULARITIES = new Set<string>(Object.values(TimeGranularity));

@Injectable()
export class TrendsService {
  constructor(
    @InjectRepository(AggregatedMetric)
    private readonly metricsRepository: Repository<AggregatedMetric>,
  ) {}

  async getTrends(query: TrendsQueryDto): Promise<TrendsResult> {
    const { metric, startDate, endDate, granularity = 'day' } = query;

    // Whitelist granularity before interpolating into SQL
    const safeBucket = VALID_GRANULARITIES.has(granularity) ? granularity : 'day';
    const bucketExpr = `date_trunc('${safeBucket}', timestamp AT TIME ZONE 'UTC')`;

    let rawSql: string | null = null;

    if (metric === MetricType.PAGE_VIEWS) {
      rawSql = `
        SELECT ${bucketExpr} AS timestamp, COUNT(*)::bigint AS value
        FROM raw_events
        WHERE "eventType" IN ('pageview', 'pageView')
          AND timestamp BETWEEN $1 AND $2
        GROUP BY ${bucketExpr}
        ORDER BY timestamp ASC
      `;
    } else if (metric === MetricType.SESSIONS) {
      rawSql = `
        SELECT ${bucketExpr} AS timestamp, COUNT(DISTINCT "sessionId")::bigint AS value
        FROM raw_events
        WHERE timestamp BETWEEN $1 AND $2
        GROUP BY ${bucketExpr}
        ORDER BY timestamp ASC
      `;
    } else if (metric === MetricType.UNIQUE_VISITORS) {
      rawSql = `
        SELECT ${bucketExpr} AS timestamp,
               COUNT(DISTINCT COALESCE("userId", "sessionId"))::bigint AS value
        FROM raw_events
        WHERE timestamp BETWEEN $1 AND $2
        GROUP BY ${bucketExpr}
        ORDER BY timestamp ASC
      `;
    }

    if (rawSql) {
      const rows = await this.metricsRepository.query(rawSql, [startDate, endDate]) as Array<{
        timestamp: Date;
        value: string;
      }>;
      const values = rows.map((r) => Number(r.value));
      const total = values.reduce((sum, v) => sum + v, 0);
      return {
        metric,
        granularity: safeBucket,
        data: rows.map((r) => ({ timestamp: r.timestamp, value: Number(r.value), count: Number(r.value) })),
        summary: {
          total,
          average: values.length > 0 ? total / values.length : 0,
          min: values.length > 0 ? Math.min(...values) : 0,
          max: values.length > 0 ? Math.max(...values) : 0,
        },
      };
    }

    // Fallback: pre-aggregated table for other metrics (bounce_rate, avg_session_duration, etc.)
    const data = await this.metricsRepository.find({
      where: {
        metricType: metric as MetricType,
        granularity: safeBucket as TimeGranularity,
        timestamp: Between(new Date(startDate), new Date(endDate)),
      },
      order: { timestamp: 'ASC' },
    });

    const values = data.map((d) => Number(d.value));
    const total = values.reduce((sum, v) => sum + v, 0);

    return {
      metric,
      granularity: safeBucket,
      data: data.map((d) => ({
        timestamp: d.timestamp,
        value: Number(d.value),
        count: Number(d.count),
      })),
      summary: {
        total,
        average: values.length > 0 ? total / values.length : 0,
        min: values.length > 0 ? Math.min(...values) : 0,
        max: values.length > 0 ? Math.max(...values) : 0,
      },
    };
  }

  async compareTrends(
    query: TrendsQueryDto,
    compareStartDate: string,
    compareEndDate: string,
  ): Promise<{ current: TrendsResult; previous: TrendsResult; change: number }> {
    const current = await this.getTrends(query);
    const previous = await this.getTrends({
      ...query,
      startDate: compareStartDate,
      endDate: compareEndDate,
    });

    const currentTotal = current.summary.total;
    const previousTotal = previous.summary.total;
    const change =
      previousTotal > 0 ? ((currentTotal - previousTotal) / previousTotal) * 100 : 0;

    return { current, previous, change };
  }
}
