import {
  Injectable,
  HttpStatus,
  NotFoundException,
  BadRequestException,
  ForbiddenException,
  ConflictException,
} from '@nestjs/common';
import { Kysely, sql, type RawBuilder } from 'kysely';
import { DB } from 'src/database/database.type';
import { Logger } from 'pino-nestjs';
import { AddWebsiteDto, ScrapingLogsQueryDto, UpdateWebsiteDto } from './scraping-analytics.dto';
import { AiScraperClient } from './ai-scraper.client';

const LOG_PREVIEW_LENGTH = 140;

function slugify(name: string): string {
  return (
    name
      .toLowerCase()
      .replace(/[^a-z0-9]+/g, '_')
      .replace(/^_+|_+$/g, '')
      .slice(0, 80) || 'website'
  );
}

@Injectable()
export class ScrapingAnalyticsService {
  constructor(
    private db: Kysely<DB>,
    private logger: Logger,
    private readonly aiScraperClient: AiScraperClient,
  ) {}

  async listWebsites() {
    const result = await sql<any>`
      SELECT
        w.id, w.key, w.name, w.base_url, w.list_url, w.is_active,
        w.status, w.requested_by,
        w.scraper_timezone, w.scraper_run_hour, w.scraper_run_minute,
        w.next_scrape_at, w.last_scrape_started_at, w.last_scraped_at,
        w.last_scrape_status, w.last_scrape_message,
        w.created_at, w.updated_at,
        COALESCE(r.total_records, 0)::int AS total_records,
        COALESCE(r.records_30d, 0)::int AS records_30d,
        r.last_record_at
      FROM websites w
      LEFT JOIN (
        SELECT website_id,
          COUNT(*) AS total_records,
          COUNT(*) FILTER (WHERE scraped_at >= NOW() - INTERVAL '30 days') AS records_30d,
          MAX(scraped_at) AS last_record_at
        FROM scrapped_pet_report
        GROUP BY website_id
      ) r ON r.website_id = w.id
      ORDER BY w.name ASC
    `.execute(this.db);

    return { status: true, statusCode: HttpStatus.OK, data: result.rows };
  }

  async addWebsite(data: AddWebsiteDto, actingAdminId: string) {
    // New websites start as pending requests; a developer wires up the
    // scraper script (key) and activates them later.
    const baseKey = slugify(data.name);
    const existing = await this.db
      .selectFrom('websites')
      .select('id')
      .where('key', '=', baseKey)
      .executeTakeFirst();
    const key = existing ? `${baseKey}_${Math.random().toString(16).slice(2, 6)}` : baseKey;

    const website = await this.db
      .insertInto('websites')
      .values({
        key,
        name: data.name.trim(),
        base_url: data.base_url.trim(),
        list_url: (data.list_url || '').trim(),
        is_active: false,
        status: 'pending',
        requested_by: actingAdminId,
      })
      .returning(['id', 'key', 'name', 'base_url', 'list_url', 'is_active', 'status'])
      .executeTakeFirstOrThrow();

    this.logger.log(`Website ${website.name} requested by admin ${actingAdminId}`);

    return { status: true, statusCode: HttpStatus.CREATED, data: website };
  }

  async updateWebsite(
    websiteId: string,
    data: UpdateWebsiteDto,
    actingAdmin: { id: string; role: string },
  ) {
    const current = await this.db
      .selectFrom('websites')
      .select(['id', 'key', 'name', 'status', 'is_active'])
      .where('id', '=', websiteId)
      .executeTakeFirst();

    if (!current) throw new NotFoundException('Website not found');

    const developerFields =
      data.key !== undefined ||
      data.status !== undefined ||
      data.name !== undefined ||
      data.base_url !== undefined ||
      data.list_url !== undefined;

    if (developerFields && actingAdmin.role !== 'super_admin') {
      throw new ForbiddenException('Only a super admin can edit website setup');
    }

    const nextStatus = data.status ?? current.status;
    const nextActive = data.is_active ?? current.is_active;

    if (current.status === 'pending' && data.status === 'active' && !data.key) {
      throw new BadRequestException('A scraper script name (key) is required to activate a website');
    }
    if (nextActive && nextStatus !== 'active') {
      throw new BadRequestException('Cannot enable scraping while the website is pending');
    }

    if (data.key && data.key !== current.key) {
      const keyTaken = await this.db
        .selectFrom('websites')
        .select('id')
        .where('key', '=', data.key)
        .where('id', '!=', websiteId)
        .executeTakeFirst();
      if (keyTaken) throw new ConflictException('Another website already uses this script name');
    }

    const updates: Record<string, unknown> = { updated_at: new Date() };
    if (data.is_active !== undefined) updates.is_active = data.is_active;
    if (data.key !== undefined) updates.key = data.key;
    if (data.status !== undefined) updates.status = data.status;
    if (data.name !== undefined) updates.name = data.name.trim();
    if (data.base_url !== undefined) updates.base_url = data.base_url.trim();
    if (data.list_url !== undefined) updates.list_url = data.list_url.trim();

    const website = await this.db
      .updateTable('websites')
      .set(updates)
      .where('id', '=', websiteId)
      .returning(['id', 'key', 'name', 'base_url', 'list_url', 'is_active', 'status'])
      .executeTakeFirstOrThrow();

    this.logger.log(`Website ${website.key} updated by admin ${actingAdmin.id}`, data);

    return { status: true, statusCode: HttpStatus.OK, data: website };
  }

  async triggerWebsite(websiteId: string, actingAdminId: string) {
    const website = await this.db
      .selectFrom('websites')
      .select(['id', 'key', 'name', 'status'])
      .where('id', '=', websiteId)
      .executeTakeFirst();

    if (!website) throw new NotFoundException('Website not found');
    if (website.status !== 'active') {
      throw new BadRequestException('This website is pending setup and cannot be scraped yet');
    }

    this.logger.log(`Manual scrape of ${website.key} triggered by admin ${actingAdminId}`);
    const result = await this.aiScraperClient.runScraper(website.key);

    return {
      status: true,
      statusCode: HttpStatus.OK,
      data: { website: website.key, result },
    };
  }

  async triggerAll(actingAdminId: string) {
    this.logger.log(`Manual scrape of ALL websites triggered by admin ${actingAdminId}`);
    const result = await this.aiScraperClient.runAllScrapers();
    return { status: true, statusCode: HttpStatus.OK, data: result };
  }

  async listScripts() {
    const scrapers = await this.aiScraperClient.listScrapers();
    return { status: true, statusCode: HttpStatus.OK, data: scrapers };
  }

  async listLogs(query: ScrapingLogsQueryDto) {
    const conditions: RawBuilder<unknown>[] = [];

    if (query.status !== 'all') conditions.push(sql`l.status = ${query.status}`);
    if (query.trigger !== 'all') conditions.push(sql`l.trigger = ${query.trigger}`);
    if (query.scraper) conditions.push(sql`l.scraper_name = ${query.scraper}`);
    if (query.search) {
      const term = `%${query.search}%`;
      conditions.push(
        sql`(l.scraper_name ILIKE ${term} OR l.message ILIKE ${term} OR COALESCE(l.error_detail, '') ILIKE ${term})`,
      );
    }

    const whereClause =
      conditions.length > 0 ? sql`WHERE ${sql.join(conditions, sql` AND `)}` : sql``;
    const offset = (query.page - 1) * query.limit;

    const countResult = await sql<any>`
      SELECT COUNT(*)::int AS total FROM scraper_run_log l ${whereClause}
    `.execute(this.db);
    const total = countResult.rows[0]?.total ?? 0;

    const rowsResult = await sql<any>`
      SELECT
        l.id, l.scraper_name, w.name AS website_name, l.trigger, l.status,
        l.records_inserted, l.started_at, l.finished_at, l.created_at,
        LEFT(l.message, ${LOG_PREVIEW_LENGTH}) AS message_preview,
        (LENGTH(l.message) > ${LOG_PREVIEW_LENGTH}) AS message_truncated,
        (l.error_detail IS NOT NULL AND l.error_detail <> '') AS has_error,
        CASE
          WHEN l.started_at IS NOT NULL AND l.finished_at IS NOT NULL
          THEN EXTRACT(EPOCH FROM (l.finished_at - l.started_at))::int
          ELSE NULL
        END AS duration_seconds
      FROM scraper_run_log l
      LEFT JOIN websites w ON w.key = l.scraper_name
      ${whereClause}
      ORDER BY l.created_at DESC
      LIMIT ${query.limit} OFFSET ${offset}
    `.execute(this.db);

    const scrapersResult = await sql<any>`
      SELECT DISTINCT scraper_name FROM scraper_run_log ORDER BY scraper_name ASC
    `.execute(this.db);

    return {
      status: true,
      statusCode: HttpStatus.OK,
      data: {
        rows: rowsResult.rows,
        scrapers: scrapersResult.rows.map((r: any) => r.scraper_name),
        total,
        page: query.page,
        limit: query.limit,
        total_pages: Math.max(1, Math.ceil(total / query.limit)),
      },
    };
  }

  async getLog(logId: string) {
    const result = await sql<any>`
      SELECT
        l.id, l.scraper_name, w.name AS website_name, l.trigger, l.status,
        l.records_inserted, l.started_at, l.finished_at, l.created_at,
        l.message, l.error_detail,
        CASE
          WHEN l.started_at IS NOT NULL AND l.finished_at IS NOT NULL
          THEN EXTRACT(EPOCH FROM (l.finished_at - l.started_at))::int
          ELSE NULL
        END AS duration_seconds
      FROM scraper_run_log l
      LEFT JOIN websites w ON w.key = l.scraper_name
      WHERE l.id = ${logId}
    `.execute(this.db);

    const log = result.rows[0];
    if (!log) throw new NotFoundException('Log not found');

    return { status: true, statusCode: HttpStatus.OK, data: log };
  }

  async reportStats() {
    const totalsResult = await sql<any>`
      SELECT
        COUNT(*)::int AS total_scraped,
        COUNT(*) FILTER (WHERE scraped_at >= NOW() - INTERVAL '30 days')::int AS scraped_30d,
        COUNT(*) FILTER (WHERE scraped_at >= NOW() - INTERVAL '7 days')::int AS scraped_7d
      FROM scrapped_pet_report
    `.execute(this.db);

    const runsResult = await sql<any>`
      SELECT
        COUNT(*)::int AS runs_30d,
        COUNT(*) FILTER (WHERE status = 'succeeded')::int AS succeeded_30d,
        COUNT(*) FILTER (WHERE status = 'failed')::int AS failed_30d
      FROM scraper_run_log
      WHERE created_at >= NOW() - INTERVAL '30 days'
    `.execute(this.db);

    const perDayResult = await sql<any>`
      SELECT to_char(d.day, 'YYYY-MM-DD') AS date, COALESCE(s.count, 0)::int AS count
      FROM generate_series(CURRENT_DATE - INTERVAL '29 days', CURRENT_DATE, INTERVAL '1 day') AS d(day)
      LEFT JOIN (
        SELECT scraped_at::date AS day, COUNT(*) AS count
        FROM scrapped_pet_report
        WHERE scraped_at >= CURRENT_DATE - INTERVAL '29 days'
        GROUP BY 1
      ) s ON s.day = d.day::date
      ORDER BY d.day
    `.execute(this.db);

    const perWebsiteResult = await sql<any>`
      SELECT
        w.id, w.key, w.name, w.is_active, w.last_scraped_at, w.last_scrape_status,
        COALESCE(r.total_records, 0)::int AS total_records,
        COALESCE(r.records_30d, 0)::int AS records_30d,
        COALESCE(r.records_7d, 0)::int AS records_7d,
        COALESCE(e.runs_30d, 0)::int AS runs_30d,
        COALESCE(e.failed_30d, 0)::int AS failed_30d,
        COALESCE(e.records_inserted_30d, 0)::int AS records_inserted_30d
      FROM websites w
      LEFT JOIN (
        SELECT website_id,
          COUNT(*) AS total_records,
          COUNT(*) FILTER (WHERE scraped_at >= NOW() - INTERVAL '30 days') AS records_30d,
          COUNT(*) FILTER (WHERE scraped_at >= NOW() - INTERVAL '7 days') AS records_7d
        FROM scrapped_pet_report
        GROUP BY website_id
      ) r ON r.website_id = w.id
      LEFT JOIN (
        SELECT scraper_name,
          COUNT(*) AS runs_30d,
          COUNT(*) FILTER (WHERE status = 'failed') AS failed_30d,
          COALESCE(SUM(records_inserted), 0) AS records_inserted_30d
        FROM scraper_run_log
        WHERE created_at >= NOW() - INTERVAL '30 days'
        GROUP BY scraper_name
      ) e ON e.scraper_name = w.key
      ORDER BY total_records DESC
    `.execute(this.db);

    const recentErrorsResult = await sql<any>`
      SELECT
        l.id, l.scraper_name, w.name AS website_name, l.created_at,
        LEFT(l.message, 200) AS message_preview,
        LEFT(COALESCE(l.error_detail, ''), 300) AS error_preview
      FROM scraper_run_log l
      LEFT JOIN websites w ON w.key = l.scraper_name
      WHERE l.status = 'failed'
      ORDER BY l.created_at DESC
      LIMIT 10
    `.execute(this.db);

    return {
      status: true,
      statusCode: HttpStatus.OK,
      data: {
        ...totalsResult.rows[0],
        ...runsResult.rows[0],
        scraped_per_day: perDayResult.rows,
        per_website: perWebsiteResult.rows,
        recent_errors: recentErrorsResult.rows,
      },
    };
  }
}