import type { ItemIdentifier } from '@roostorg/coop-types';

import type { IDataWarehouse } from '../../../storage/dataWarehouse/IDataWarehouse.js';
import type SafeTracer from '../../../utils/SafeTracer.js';
import { SIX_MONTHS_MS } from '../../../utils/time.js';
import { formatClickhouseQuery } from '../utils/clickhouseSql.js';
import {
  type ContentApiImageCountRecord,
  type ContentApiRequestByIpRecord,
  type ContentApiRequestCountRecord,
  type ContentApiRequestQueryOptions,
  type ContentApiRequestRecord,
  type IContentApiRequestsAdapter,
  type InferredUserIdentityFromCreatorsInput,
  type InferredUserIdentityFromCreatorsRecord,
} from './IContentApiRequestsAdapter.js';

interface ClickhouseContentApiRow {
  item_data: unknown;
  submission_id: string;
  ts: string;
  item_creator_id: string | null;
  item_creator_type_id: string | null;
  item_type_version: string;
  item_type_schema_variant: string;
}

interface ClickhouseContentApiByIpRow extends ClickhouseContentApiRow {
  item_id: string;
  item_type_id: string;
}

interface CountRow {
  date: string;
  count: number;
}

interface ClickhouseInferredUserRow {
  ts: string;
  item_creator_type_id: string | null;
}

export class ClickhouseContentApiRequestsAdapter implements IContentApiRequestsAdapter {
  constructor(
    private readonly warehouse: IDataWarehouse,
    private readonly tracer: SafeTracer,
  ) {}

  async getSuccessfulRequestsForItem(
    orgId: string,
    item: ItemIdentifier,
    options?: ContentApiRequestQueryOptions,
  ): Promise<ReadonlyArray<ContentApiRequestRecord>> {
    const { latestOnly = false, lookbackWindowMs = SIX_MONTHS_MS } =
      options ?? {};

    const lookbackStart = new Date(Date.now() - Math.max(1, lookbackWindowMs));
    const lookbackStartDate = lookbackStart.toISOString().slice(0, 10);

    const conditions = [
      'org_id = ?',
      "event = 'REQUEST_SUCCEEDED'",
      'item_id = ?',
      'item_type_id = ?',
      'ds >= toDate(?)',
    ];

    const params: unknown[] = [orgId, item.id, item.typeId, lookbackStartDate];

    const sql = `
      SELECT
        item_data,
        submission_id,
        ts,
        item_creator_id,
        item_creator_type_id,
        item_type_version,
        item_type_schema_variant
      FROM analytics.CONTENT_API_REQUESTS
      WHERE ${conditions.join(' AND ')}
      ORDER BY ts DESC
      ${latestOnly ? 'LIMIT 1' : ''}
    `;

    const rows = (await this.query(sql, params)) as ClickhouseContentApiRow[];

    return rows.map<ContentApiRequestRecord>((row) => ({
      submissionId: row.submission_id,
      itemData: row.item_data,
      itemTypeVersion: row.item_type_version,
      itemTypeSchemaVariant: row.item_type_schema_variant,
      itemCreatorId: row.item_creator_id,
      itemCreatorTypeId: row.item_creator_type_id,
      occurredAt: new Date(row.ts),
    }));
  }

  async getSuccessfulRequestsByIpAddress(
    orgId: string,
    ipAddress: string,
    options?: ContentApiRequestQueryOptions,
  ): Promise<ReadonlyArray<ContentApiRequestByIpRecord>> {
    const { lookbackWindowMs = SIX_MONTHS_MS, limit } = options ?? {};

    const lookbackStart = new Date(Date.now() - Math.max(1, lookbackWindowMs));
    const lookbackStartDate = lookbackStart.toISOString().slice(0, 10);

    // Bound the result set so a high-volume IP can't pull an unbounded number of
    // rows into memory. Only apply when the caller asks for a positive cap.
    const hasLimit =
      typeof limit === 'number' && Number.isFinite(limit) && limit > 0;

    const sql = `
      SELECT
        item_id,
        item_type_id,
        item_data,
        submission_id,
        ts,
        item_creator_id,
        item_creator_type_id,
        item_type_version,
        item_type_schema_variant
      FROM analytics.CONTENT_API_REQUESTS
      WHERE org_id = ?
        AND event = 'REQUEST_SUCCEEDED'
        AND item_ip_address = ?
        AND item_ip_address != ''
        AND ds >= toDate(?)
      ORDER BY ts DESC
      ${hasLimit ? 'LIMIT ?' : ''}
    `;

    const params: unknown[] = [orgId, ipAddress, lookbackStartDate];
    if (hasLimit) {
      params.push(Math.floor(limit));
    }

    const rows = (await this.query(
      sql,
      params,
    )) as ClickhouseContentApiByIpRow[];

    return rows.map<ContentApiRequestByIpRecord>((row) => ({
      itemId: row.item_id,
      itemTypeId: row.item_type_id,
      submissionId: row.submission_id,
      itemData: row.item_data,
      itemTypeVersion: row.item_type_version,
      itemTypeSchemaVariant: row.item_type_schema_variant,
      itemCreatorId: row.item_creator_id,
      itemCreatorTypeId: row.item_creator_type_id,
      occurredAt: new Date(row.ts),
    }));
  }

  async getSuccessfulRequestCountsByDay(
    orgId: string,
    start: Date,
    end: Date,
  ): Promise<ReadonlyArray<ContentApiRequestCountRecord>> {
    const sql = `
      SELECT
        ds AS date,
        count() AS count
      FROM analytics.CONTENT_API_REQUESTS
      WHERE org_id = ?
        AND event = 'REQUEST_SUCCEEDED'
        AND ds >= toDate(?)
        AND ds < toDate(?)
      GROUP BY date
      ORDER BY date
    `;

    const rows = (await this.query(sql, [
      orgId,
      start.toISOString().slice(0, 10),
      end.toISOString().slice(0, 10),
    ])) as CountRow[];

    return rows.map((row) => ({
      date: row.date,
      count: Number(row.count),
    }));
  }

  async getImageRequestCountsByDay(
    orgId: string,
    start: Date,
    end: Date,
  ): Promise<ReadonlyArray<ContentApiImageCountRecord>> {
    const sql = `
      SELECT
        ds AS date,
        countIf(
          JSONExtractString(item_type_schema, concat('$.', key, '.type')) = 'IMAGE'
          AND JSONExtractRaw(item_data, concat('$.', key)) IS NOT NULL
        ) AS count
      FROM analytics.CONTENT_API_REQUESTS
      ARRAY JOIN JSONExtractKeys(item_type_schema) AS key
      WHERE org_id = ?
        AND event = 'REQUEST_SUCCEEDED'
        AND ds >= toDate(?)
        AND ds < toDate(?)
      GROUP BY date
      ORDER BY date
    `;

    const rows = (await this.query(sql, [
      orgId,
      start.toISOString().slice(0, 10),
      end.toISOString().slice(0, 10),
    ])) as CountRow[];

    return rows.map((row) => ({
      date: row.date,
      count: Number(row.count),
    }));
  }

  async findInferredUserIdentityFromCreators(
    input: InferredUserIdentityFromCreatorsInput,
  ): Promise<InferredUserIdentityFromCreatorsRecord | null> {
    const { orgId, itemId, lookbackWindowMs = SIX_MONTHS_MS } = input;

    const lookbackStart = new Date(Date.now() - Math.max(1, lookbackWindowMs));
    const lookbackStartDate = lookbackStart.toISOString().slice(0, 10);

    const sql = `
      SELECT
        ts,
        item_creator_type_id
      FROM analytics.CONTENT_API_REQUESTS
      WHERE org_id = ?
        AND event = 'REQUEST_SUCCEEDED'
        AND ds >= toDate(?)
        AND lower(item_creator_id) = lower(?)
        AND item_creator_type_id IS NOT NULL
        AND item_creator_type_id != ''
      ORDER BY ts DESC
      LIMIT 1
    `;

    const rows = await this.query<ClickhouseInferredUserRow>(sql, [
      orgId,
      lookbackStartDate,
      itemId,
    ]);

    if (rows.length === 0) {
      return null;
    }

    const row = rows[0];
    if (!row.item_creator_type_id) {
      return null;
    }

    return {
      itemTypeId: row.item_creator_type_id,
      lastSeenAt: new Date(row.ts),
    };
  }

  private async query<T>(
    statement: string,
    params: readonly unknown[],
  ): Promise<readonly T[]> {
    const formatted = formatClickhouseQuery(statement, params);
    const result = await this.warehouse.query(formatted, this.tracer);
    return result as readonly T[];
  }
}
