import { HttpError, type RequestContext } from '../../core/http.js';
import { intParam, stringParam } from '../../core/request.js';
import { query, queryOne } from '../../core/db.js';
import { requireTenantContentPermission } from './auth.js';

const ANALYTICS_RANGES: Record<string, number> = {
  '7d': 7,
  '30d': 30,
  '90d': 90,
};

const ASSET_ACCESS_TYPES = new Set(['download', 'preview', 'admin_download', 'admin_preview', 'upload_sign', 'upload_confirm']);
const ASSET_RESULTS = new Set(['granted', 'denied']);
const VIDEO_STATUSES = new Set(['issued', 'started', 'completed', 'expired', 'revoked']);
const VIDEO_ACCESS_MODES = new Set(['free', 'svip', 'video_quota']);
const UUID_RE = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;
const TRACE_RE = /^[a-z0-9_-]{6,64}$/i;

const shanghaiDateFormatter = new Intl.DateTimeFormat('en-US', {
  timeZone: 'Asia/Shanghai',
  year: 'numeric',
  month: '2-digit',
  day: '2-digit',
});

type NumericRow = Record<string, unknown>;

function numberValue(value: unknown) {
  const parsed = Number(value ?? 0);
  return Number.isFinite(parsed) ? parsed : 0;
}

function intValue(value: unknown) {
  return Math.trunc(numberValue(value));
}

function optionalUuid(value: string, label: string) {
  if (!value) return null;
  if (!UUID_RE.test(value)) throw new HttpError(400, `${label} must be a valid UUID`, 'INVALID_UUID');
  return value;
}

function optionalTraceId(value: string) {
  if (!value) return null;
  if (!TRACE_RE.test(value)) throw new HttpError(400, 'traceId must be a safe trace token', 'INVALID_TRACE_ID');
  return value.toUpperCase();
}

function choiceParam(value: string, allowed: Set<string>, label: string) {
  if (!value) return null;
  if (!allowed.has(value)) throw new HttpError(400, `Invalid ${label}: ${value}`, 'INVALID_FIELD_VALUE');
  return value;
}

function shanghaiDateKey(date: Date) {
  const parts = Object.fromEntries(
    shanghaiDateFormatter.formatToParts(date).map(part => [part.type, part.value]),
  );
  return `${parts.year}-${parts.month}-${parts.day}`;
}

function addDaysKey(dateKey: string, days: number) {
  const date = new Date(`${dateKey}T00:00:00+08:00`);
  date.setUTCDate(date.getUTCDate() + days);
  return shanghaiDateKey(date);
}

function shanghaiDayStartIso(dateKey: string) {
  return new Date(`${dateKey}T00:00:00+08:00`).toISOString();
}

function parseRange(value: string) {
  const timeRange = value || '30d';
  const days = ANALYTICS_RANGES[timeRange];
  if (!days) throw new HttpError(400, 'Unsupported analytics time range', 'INVALID_ANALYTICS_RANGE');

  const endDate = shanghaiDateKey(new Date());
  const startDate = addDaysKey(endDate, -(days - 1));
  const endExclusiveDate = addDaysKey(endDate, 1);
  return {
    timeRange,
    days,
    startDate,
    endDate,
    startAt: shanghaiDayStartIso(startDate),
    endAt: shanghaiDayStartIso(endExclusiveDate),
  };
}

function emptyDailySeries(startDate: string, days: number) {
  return Array.from({ length: days }, (_, index) => ({
    date: addDaysKey(startDate, index),
    assetEvents: 0,
    assetDenied: 0,
    videoPlays: 0,
    videoQuotaConsumed: 0,
  }));
}

function applyDailyRows(
  series: ReturnType<typeof emptyDailySeries>,
  rows: NumericRow[],
  mapper: (target: ReturnType<typeof emptyDailySeries>[number], row: NumericRow) => void,
) {
  const map = new Map(series.map(item => [item.date, item]));
  for (const row of rows) {
    const target = map.get(String(row.date || ''));
    if (target) mapper(target, row);
  }
}

function buildAssetFilters(input: {
  tenantId: string;
  startAt?: string;
  endAt?: string;
  assetId?: string | null;
  userId?: string | null;
  accessType?: string | null;
  result?: string | null;
  traceId?: string | null;
}) {
  const params: unknown[] = [input.tenantId];
  const filters = ['e.tenant_id = $1'];
  if (input.startAt) {
    params.push(input.startAt);
    filters.push(`e.created_at >= $${params.length}::timestamptz`);
  }
  if (input.endAt) {
    params.push(input.endAt);
    filters.push(`e.created_at < $${params.length}::timestamptz`);
  }
  if (input.assetId) {
    params.push(input.assetId);
    filters.push(`e.asset_id = $${params.length}::uuid`);
  }
  if (input.userId) {
    params.push(input.userId);
    filters.push(`e.user_id = $${params.length}::uuid`);
  }
  if (input.accessType) {
    params.push(input.accessType);
    filters.push(`e.access_type = $${params.length}`);
  }
  if (input.result) {
    params.push(input.result);
    filters.push(`e.result = $${params.length}`);
  }
  if (input.traceId) {
    params.push(input.traceId);
    filters.push(`upper(e.metadata #>> '{watermark,traceId}') = $${params.length}`);
  }
  return { params, whereSql: filters.join(' and ') };
}

function buildVideoFilters(input: {
  tenantId: string;
  startAt?: string;
  endAt?: string;
  videoId?: string | null;
  userId?: string | null;
  accessMode?: string | null;
  status?: string | null;
  traceId?: string | null;
}) {
  const params: unknown[] = [input.tenantId];
  const filters = ['e.tenant_id = $1'];
  if (input.startAt) {
    params.push(input.startAt);
    filters.push(`e.created_at >= $${params.length}::timestamptz`);
  }
  if (input.endAt) {
    params.push(input.endAt);
    filters.push(`e.created_at < $${params.length}::timestamptz`);
  }
  if (input.videoId) {
    params.push(input.videoId);
    filters.push(`e.video_id = $${params.length}::uuid`);
  }
  if (input.userId) {
    params.push(input.userId);
    filters.push(`e.user_id = $${params.length}::uuid`);
  }
  if (input.accessMode) {
    params.push(input.accessMode);
    filters.push(`e.access_mode = $${params.length}`);
  }
  if (input.status) {
    params.push(input.status);
    filters.push(`e.status = $${params.length}`);
  }
  if (input.traceId) {
    params.push(input.traceId);
    filters.push(`upper(e.metadata #>> '{watermark,traceId}') = $${params.length}`);
  }
  return { params, whereSql: filters.join(' and ') };
}

function actorLabelSql(alias: string) {
  return `coalesce(nullif(${alias}.name, ''), nullif(${alias}.username, ''), concat('user:', left(${alias}.id::text, 8)))`;
}

function jsonbIntegerExpr(pathSql: string) {
  return `case when ${pathSql} ~ '^\\d+$' then (${pathSql})::integer else 0 end`;
}

function jsonbRateExpr(pathSql: string) {
  return `case when ${pathSql} ~ '^(0(\\.\\d+)?|1(\\.0+)?)$' then (${pathSql})::numeric else null end`;
}

export async function mediaAnalyticsSummaryRoute(ctx: RequestContext) {
  const auth = await requireTenantContentPermission(ctx, 'content:analytics:read');
  const range = parseRange(stringParam(ctx, 'timeRange'));
  const limit = intParam(ctx, 'limit', 10, 50);
  const assetId = optionalUuid(stringParam(ctx, 'assetId'), 'assetId');
  const videoId = optionalUuid(stringParam(ctx, 'videoId'), 'videoId');
  const userId = optionalUuid(stringParam(ctx, 'userId'), 'userId');
  const traceId = optionalTraceId(stringParam(ctx, 'traceId'));

  const assetFilter = buildAssetFilters({ tenantId: auth.tenantId, startAt: range.startAt, endAt: range.endAt, assetId, userId, traceId });
  const videoFilter = buildVideoFilters({ tenantId: auth.tenantId, startAt: range.startAt, endAt: range.endAt, videoId, userId, traceId });
  const daily = emptyDailySeries(range.startDate, range.days);

  const [
    assetTotals,
    videoTotals,
    assetTop,
    videoTop,
    assetDailyRows,
    videoDailyRows,
    traceAssetCount,
    traceVideoCount,
  ] = await Promise.all([
    queryOne<NumericRow>(
      `
      select
        count(*)::integer as "totalEvents",
        count(*) filter (where e.result = 'granted')::integer as "grantedEvents",
        count(*) filter (where e.result = 'denied')::integer as "deniedEvents",
        count(*) filter (where e.access_type in ('download', 'admin_download') and e.result = 'granted')::integer as downloads,
        count(*) filter (where e.access_type in ('preview', 'admin_preview') and e.result = 'granted')::integer as previews,
        count(*) filter (where e.access_type = 'upload_sign')::integer as "uploadSigns",
        count(*) filter (where e.access_type = 'upload_confirm')::integer as "uploadConfirms",
        count(distinct e.user_id) filter (where e.user_id is not null)::integer as "uniqueUsers",
        count(*) filter (where e.metadata #>> '{watermark,traceId}' is not null)::integer as "watermarkEvents"
      from public.content_asset_access_events e
      where ${assetFilter.whereSql}
      `,
      assetFilter.params,
    ),
    queryOne<NumericRow>(
      `
      select
        count(*)::integer as "totalEvents",
        count(*) filter (where e.status = 'issued')::integer as "issuedEvents",
        count(*) filter (where e.status = 'started')::integer as "startedEvents",
        count(*) filter (where e.status = 'completed')::integer as "completedEvents",
        count(*) filter (where e.access_mode = 'free')::integer as "freePlays",
        count(*) filter (where e.access_mode = 'svip')::integer as "svipPlays",
        count(*) filter (where e.access_mode = 'video_quota')::integer as "quotaPlays",
        coalesce(sum(e.consumed_quota), 0)::integer as "consumedQuota",
        coalesce(sum(${jsonbIntegerExpr("e.metadata #>> '{playback,watchedSeconds}'")}), 0)::integer as "watchedSeconds",
        round(avg(nullif(${jsonbRateExpr("e.metadata #>> '{playback,completionRate}'")}, 0)), 4) as "avgCompletionRate",
        count(distinct e.user_id)::integer as "uniqueUsers",
        count(*) filter (where e.metadata #>> '{watermark,traceId}' is not null)::integer as "watermarkEvents"
      from public.video_play_events e
      where ${videoFilter.whereSql}
      `,
      videoFilter.params,
    ),
    query(
      `
      select e.asset_id as "assetId",
             coalesce(a.title, '已删除资源') as title,
             coalesce(a.asset_type, e.asset_type) as "assetType",
             coalesce(a.visibility, e.visibility) as visibility,
             count(*)::integer as "totalEvents",
             count(*) filter (where e.result = 'granted')::integer as "grantedEvents",
             count(*) filter (where e.result = 'denied')::integer as "deniedEvents",
             count(*) filter (where e.access_type in ('download', 'admin_download') and e.result = 'granted')::integer as downloads,
             count(*) filter (where e.access_type in ('preview', 'admin_preview') and e.result = 'granted')::integer as previews,
             count(distinct e.user_id) filter (where e.user_id is not null)::integer as "uniqueUsers",
             max(e.created_at) as "lastAccessAt"
      from public.content_asset_access_events e
      left join public.content_assets a on a.tenant_id = e.tenant_id and a.id = e.asset_id
      where ${assetFilter.whereSql}
        and e.asset_id is not null
      group by e.asset_id, a.title, a.asset_type, a.visibility, e.asset_type, e.visibility
      order by "totalEvents" desc, "lastAccessAt" desc
      limit $${assetFilter.params.length + 1}
      `,
      [...assetFilter.params, limit],
    ),
    query(
      `
      select e.video_id as "videoId",
             coalesce(v.title, '已删除视频') as title,
             coalesce(v.access_mode, e.access_mode) as "accessMode",
             v.asset_id as "assetId",
             count(*)::integer as "totalEvents",
             count(distinct e.user_id)::integer as "uniqueUsers",
             coalesce(sum(e.consumed_quota), 0)::integer as "consumedQuota",
             coalesce(sum(${jsonbIntegerExpr("e.metadata #>> '{playback,watchedSeconds}'")}), 0)::integer as "watchedSeconds",
             round(avg(nullif(${jsonbRateExpr("e.metadata #>> '{playback,completionRate}'")}, 0)), 4) as "avgCompletionRate",
             count(*) filter (where e.status = 'issued')::integer as "issuedEvents",
             count(*) filter (where e.status = 'completed')::integer as "completedEvents",
             max(e.created_at) as "lastPlayAt"
      from public.video_play_events e
      left join public.video_explanations v on v.tenant_id = e.tenant_id and v.id = e.video_id
      where ${videoFilter.whereSql}
      group by e.video_id, v.title, v.access_mode, e.access_mode, v.asset_id
      order by "totalEvents" desc, "lastPlayAt" desc
      limit $${videoFilter.params.length + 1}
      `,
      [...videoFilter.params, limit],
    ),
    query<NumericRow>(
      `
      select to_char(e.created_at at time zone 'Asia/Shanghai', 'YYYY-MM-DD') as date,
             count(*)::integer as events,
             count(*) filter (where e.result = 'denied')::integer as denied
      from public.content_asset_access_events e
      where ${assetFilter.whereSql}
      group by date
      order by date asc
      `,
      assetFilter.params,
    ),
    query<NumericRow>(
      `
      select to_char(e.created_at at time zone 'Asia/Shanghai', 'YYYY-MM-DD') as date,
             count(*)::integer as events,
             coalesce(sum(e.consumed_quota), 0)::integer as quota
      from public.video_play_events e
      where ${videoFilter.whereSql}
      group by date
      order by date asc
      `,
      videoFilter.params,
    ),
    traceId ? queryOne<NumericRow>(
      `
      select count(*)::integer as count
      from public.content_asset_access_events e
      where e.tenant_id = $1 and upper(e.metadata #>> '{watermark,traceId}') = $2
      `,
      [auth.tenantId, traceId],
    ) : Promise.resolve(null),
    traceId ? queryOne<NumericRow>(
      `
      select count(*)::integer as count
      from public.video_play_events e
      where e.tenant_id = $1 and upper(e.metadata #>> '{watermark,traceId}') = $2
      `,
      [auth.tenantId, traceId],
    ) : Promise.resolve(null),
  ]);

  applyDailyRows(daily, assetDailyRows, (target, row) => {
    target.assetEvents = intValue(row.events);
    target.assetDenied = intValue(row.denied);
  });
  applyDailyRows(daily, videoDailyRows, (target, row) => {
    target.videoPlays = intValue(row.events);
    target.videoQuotaConsumed = intValue(row.quota);
  });

  return {
    scope: {
      timeRange: range.timeRange,
      startAt: range.startAt,
      endAt: range.endAt,
      assetId,
      videoId,
      userId,
      traceId,
      limit,
    },
    assetAccess: {
      totalEvents: intValue(assetTotals?.totalEvents),
      grantedEvents: intValue(assetTotals?.grantedEvents),
      deniedEvents: intValue(assetTotals?.deniedEvents),
      downloads: intValue(assetTotals?.downloads),
      previews: intValue(assetTotals?.previews),
      uploadSigns: intValue(assetTotals?.uploadSigns),
      uploadConfirms: intValue(assetTotals?.uploadConfirms),
      uniqueUsers: intValue(assetTotals?.uniqueUsers),
      watermarkEvents: intValue(assetTotals?.watermarkEvents),
    },
    videoPlay: {
      totalEvents: intValue(videoTotals?.totalEvents),
      issuedEvents: intValue(videoTotals?.issuedEvents),
      startedEvents: intValue(videoTotals?.startedEvents),
      completedEvents: intValue(videoTotals?.completedEvents),
      freePlays: intValue(videoTotals?.freePlays),
      svipPlays: intValue(videoTotals?.svipPlays),
      quotaPlays: intValue(videoTotals?.quotaPlays),
      consumedQuota: intValue(videoTotals?.consumedQuota),
      watchedSeconds: intValue(videoTotals?.watchedSeconds),
      avgCompletionRate: numberValue(videoTotals?.avgCompletionRate),
      uniqueUsers: intValue(videoTotals?.uniqueUsers),
      watermarkEvents: intValue(videoTotals?.watermarkEvents),
    },
    assetTop,
    videoTop,
    daily,
    traceMatches: traceId
      ? { assets: intValue(traceAssetCount?.count), videos: intValue(traceVideoCount?.count) }
      : null,
  };
}

export async function mediaAssetAccessEventsRoute(ctx: RequestContext) {
  const auth = await requireTenantContentPermission(ctx, 'content:analytics:read');
  const range = parseRange(stringParam(ctx, 'timeRange'));
  const limit = intParam(ctx, 'limit', 100, 500);
  const assetId = optionalUuid(stringParam(ctx, 'assetId'), 'assetId');
  const userId = optionalUuid(stringParam(ctx, 'userId'), 'userId');
  const accessType = choiceParam(stringParam(ctx, 'accessType'), ASSET_ACCESS_TYPES, 'accessType');
  const result = choiceParam(stringParam(ctx, 'result'), ASSET_RESULTS, 'result');
  const traceId = optionalTraceId(stringParam(ctx, 'traceId'));
  const filters = buildAssetFilters({ tenantId: auth.tenantId, startAt: range.startAt, endAt: range.endAt, assetId, userId, accessType, result, traceId });

  const items = await query(
    `
    select e.id, e.asset_id as "assetId",
           coalesce(a.title, e.metadata ->> 'title', '已删除资源') as "assetTitle",
           coalesce(a.asset_type, e.asset_type) as "assetType",
           e.user_id as "userId",
           case when e.user_id is null then null else ${actorLabelSql('u')} end as "userLabel",
           e.actor_role as "actorRole", e.access_type as "accessType",
           e.visibility, e.storage_provider as "storageProvider",
           e.disposition, e.expires_in_sec as "expiresInSec",
           e.signature_mode as "signatureMode", e.result,
           e.deny_code as "denyCode", e.ip_address as "ipAddress",
           e.user_agent as "userAgent",
           e.metadata #>> '{watermark,traceId}' as "watermarkTraceId",
           e.metadata -> 'watermark' as watermark,
           e.created_at as "createdAt"
    from public.content_asset_access_events e
    left join public.content_assets a on a.tenant_id = e.tenant_id and a.id = e.asset_id
    left join public.platform_users u on u.id = e.user_id
    where ${filters.whereSql}
    order by e.created_at desc
    limit $${filters.params.length + 1}
    `,
    [...filters.params, limit],
  );

  return { scope: { timeRange: range.timeRange, startAt: range.startAt, endAt: range.endAt, limit, assetId, userId, accessType, result, traceId }, items };
}

export async function mediaVideoPlayEventsRoute(ctx: RequestContext) {
  const auth = await requireTenantContentPermission(ctx, 'content:analytics:read');
  const range = parseRange(stringParam(ctx, 'timeRange'));
  const limit = intParam(ctx, 'limit', 100, 500);
  const videoId = optionalUuid(stringParam(ctx, 'videoId'), 'videoId');
  const userId = optionalUuid(stringParam(ctx, 'userId'), 'userId');
  const accessMode = choiceParam(stringParam(ctx, 'accessMode'), VIDEO_ACCESS_MODES, 'accessMode');
  const status = choiceParam(stringParam(ctx, 'status'), VIDEO_STATUSES, 'status');
  const traceId = optionalTraceId(stringParam(ctx, 'traceId'));
  const filters = buildVideoFilters({ tenantId: auth.tenantId, startAt: range.startAt, endAt: range.endAt, videoId, userId, accessMode, status, traceId });

  const items = await query(
    `
    select e.id, e.video_id as "videoId", coalesce(v.title, '已删除视频') as "videoTitle",
           v.asset_id as "assetId", e.question_id as "questionId",
           e.user_id as "userId", ${actorLabelSql('u')} as "userLabel",
           e.status, e.access_mode as "accessMode",
           e.consumed_quota as "consumedQuota",
           e.metadata -> 'playback' as playback,
           e.signed_url_expires_at as "signedUrlExpiresAt",
           e.ip_address as "ipAddress", e.user_agent as "userAgent",
           e.metadata ->> 'signatureMode' as "signatureMode",
           e.metadata ->> 'provider' as provider,
           e.metadata #>> '{watermark,traceId}' as "watermarkTraceId",
           e.metadata -> 'watermark' as watermark,
           e.created_at as "createdAt", e.updated_at as "updatedAt"
    from public.video_play_events e
    left join public.video_explanations v on v.tenant_id = e.tenant_id and v.id = e.video_id
    join public.platform_users u on u.id = e.user_id
    where ${filters.whereSql}
    order by e.created_at desc
    limit $${filters.params.length + 1}
    `,
    [...filters.params, limit],
  );

  return { scope: { timeRange: range.timeRange, startAt: range.startAt, endAt: range.endAt, limit, videoId, userId, accessMode, status, traceId }, items };
}
