import { HttpError, type RequestContext } from '../../core/http.js';
import { intParam, readJsonBody, requiredString, stringParam } from '../../core/request.js';
import { query, transaction } from '../../core/db.js';
import {
  hasTenantPermission,
  requireTenantAdmin,
  requireTenantPermission,
  type TenantAdminAuth,
} from './auth.js';

type JsonBody = Record<string, unknown>;

const TASK_TYPES = [
  'daily_check_in',
  'feedback_submit',
  'feedback_resolved',
  'practice_complete',
  'vocabulary_review',
  'mock_exam_submit',
  'manual',
];
const PERIOD_TYPES = ['once', 'daily', 'weekly', 'monthly', 'unlimited'];
const TASK_STATUSES = ['active', 'disabled', 'archived'];
const EXCHANGE_ITEM_TYPES = ['coupon', 'manual', 'asset', 'custom'];
const EXCHANGE_ITEM_STATUSES = ['active', 'disabled', 'archived'];
const EXCHANGE_ORDER_STATUSES = ['completed', 'pending_fulfillment', 'cancelled'];
const RISK_RANGES: Record<string, number> = {
  '7d': 7,
  '30d': 30,
  '90d': 90,
};

function nullableString(value: unknown) {
  return typeof value === 'string' && value.trim() ? value.trim() : null;
}

function objectValue(value: unknown): Record<string, unknown> {
  return value && typeof value === 'object' && !Array.isArray(value) ? value as Record<string, unknown> : {};
}

function intValue(value: unknown, fallback: number) {
  const parsed = Number(value ?? fallback);
  return Number.isFinite(parsed) ? Math.trunc(parsed) : fallback;
}

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

function optionalChoice(value: unknown, allowed: string[], fallback: string) {
  const candidate = nullableString(value) || fallback;
  if (!allowed.includes(candidate)) {
    throw new HttpError(400, `Invalid value: ${candidate}`, 'INVALID_FIELD_VALUE');
  }
  return candidate;
}

function boundedIntParam(ctx: RequestContext, name: string, fallback: number, min: number, max: number) {
  const parsed = Number(ctx.url.searchParams.get(name) || fallback);
  if (!Number.isFinite(parsed)) return fallback;
  return Math.max(min, Math.min(max, Math.trunc(parsed)));
}

function shanghaiDateParts(date = new Date()) {
  const formatter = new Intl.DateTimeFormat('en-US', {
    timeZone: 'Asia/Shanghai',
    year: 'numeric',
    month: '2-digit',
    day: '2-digit',
  });
  return Object.fromEntries(formatter.formatToParts(date).map(part => [part.type, part.value])) as {
    year: string;
    month: string;
    day: string;
  };
}

function shanghaiDateKey(date = new Date()) {
  const parts = shanghaiDateParts(date);
  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 pointsRiskRange(value: string) {
  const timeRange = value || '30d';
  const days = RISK_RANGES[timeRange];
  if (!days) throw new HttpError(400, 'Unsupported points risk report range', 'INVALID_POINTS_RISK_RANGE');
  const endDate = shanghaiDateKey();
  const startDate = addDaysKey(endDate, -(days - 1));
  return {
    timeRange,
    days,
    startDate,
    endDate,
    startAt: shanghaiDayStartIso(startDate),
    endAt: shanghaiDayStartIso(addDaysKey(endDate, 1)),
  };
}

function optionalUuidString(value: unknown, key: string) {
  const candidate = nullableString(value);
  if (!candidate) return null;
  if (!/^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i.test(candidate)) {
    throw new HttpError(400, `${key} must be a UUID`, 'INVALID_UUID');
  }
  return candidate;
}

function codeValue(body: JsonBody) {
  return requiredString(body, 'code').trim().toLowerCase().replace(/[^a-z0-9_-]+/g, '-').replace(/^-+|-+$/g, '');
}

function optionalDateTimeText(value: unknown, key: string) {
  const candidate = nullableString(value);
  if (!candidate) return null;
  const time = new Date(candidate).getTime();
  if (!Number.isFinite(time)) throw new HttpError(400, `${key} must be a valid datetime`, 'INVALID_DATETIME');
  return candidate;
}

async function recordAudit(
  client: { query: (sql: string, params?: unknown[]) => Promise<unknown> },
  auth: TenantAdminAuth,
  action: string,
  targetType: string,
  targetId: string | null,
  details: Record<string, unknown> = {},
) {
  await client.query(
    `
    insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
    values ($1, $2, $3, $4, $5, $6::jsonb)
    `,
    [auth.tenantId, auth.userId, action, targetType, targetId, JSON.stringify(details)],
  );
}

function requirePointMarketingPermission(auth: TenantAdminAuth, permission: 'read' | 'write') {
  const specific = `marketing:points:${permission}`;
  if (hasTenantPermission(auth, specific) || hasTenantPermission(auth, `marketing:${permission}`)) return;
  requireTenantPermission(auth, specific);
}

function requirePointsRiskPermission(auth: TenantAdminAuth) {
  if (
    hasTenantPermission(auth, 'marketing:points:risk:read')
    || hasTenantPermission(auth, 'marketing:points:read')
    || hasTenantPermission(auth, 'marketing:read')
  ) {
    return;
  }
  requireTenantPermission(auth, 'marketing:points:risk:read');
}

function totalRows(rows: Record<string, unknown>[]) {
  return intValue(rows[0]?.totalCount, rows.length);
}

function stripTotalCount(rows: Record<string, unknown>[]) {
  return rows.map(({ totalCount: _totalCount, ...item }) => item);
}

export async function pointsRiskReportRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requirePointsRiskPermission(auth);
  const range = pointsRiskRange(stringParam(ctx, 'timeRange'));
  const limit = intParam(ctx, 'limit', 20, 100);
  const highEarnedThreshold = boundedIntParam(ctx, 'highEarnedThreshold', 100, 1, 1000000);
  const highClaimThreshold = boundedIntParam(ctx, 'highClaimThreshold', 5, 1, 10000);
  const highEventPointsThreshold = boundedIntParam(ctx, 'highEventPointsThreshold', 100, 1, 1000000);
  const highRedeemThreshold = boundedIntParam(ctx, 'highRedeemThreshold', 100, 1, 10000000);
  const baseParams = [auth.tenantId, range.startAt, range.endAt];

  const [
    summary,
    eventBreakdown,
    suspiciousUsers,
    suspiciousEvents,
    suspiciousTasks,
    suspiciousExchanges,
    dailyTrend,
  ] = await Promise.all([
    query<Record<string, unknown>>(
      `
      select
        count(*)::int as "eventCount",
        count(distinct user_id)::int as "affectedUsers",
        coalesce(sum(points) filter (where points > 0), 0)::int as "earnedPoints",
        coalesce(abs(sum(points) filter (where points < 0)), 0)::int as "spentPoints",
        max(points) filter (where points > 0)::int as "maxEarnedEvent",
        min(points) filter (where points < 0)::int as "maxSpentEvent"
      from public.user_score_events
      where tenant_id = $1
        and created_at >= $2::timestamptz
        and created_at < $3::timestamptz
      `,
      baseParams,
    ),
    query<Record<string, unknown>>(
      `
      select event_type as "eventType",
             count(*)::int as "eventCount",
             count(distinct user_id)::int as "userCount",
             coalesce(sum(points), 0)::int as "netPoints",
             coalesce(sum(points) filter (where points > 0), 0)::int as "earnedPoints",
             coalesce(abs(sum(points) filter (where points < 0)), 0)::int as "spentPoints"
      from public.user_score_events
      where tenant_id = $1
        and created_at >= $2::timestamptz
        and created_at < $3::timestamptz
      group by event_type
      order by "eventCount" desc
      `,
      baseParams,
    ),
    query<Record<string, unknown>>(
      `
      with user_points as (
        select e.user_id,
               count(*)::int as event_count,
               count(*) filter (where e.points > 0)::int as earn_event_count,
               coalesce(sum(e.points) filter (where e.points > 0), 0)::int as earned_points,
               coalesce(abs(sum(e.points) filter (where e.points < 0)), 0)::int as spent_points,
               max(e.points) filter (where e.points > 0)::int as max_earned_event,
               max(abs(e.points)) filter (where e.points < 0)::int as max_spent_event,
               max(e.created_at) as latest_event_at
        from public.user_score_events e
        where e.tenant_id = $1
          and e.created_at >= $2::timestamptz
          and e.created_at < $3::timestamptz
        group by e.user_id
      ),
      user_claims as (
        select c.user_id, count(*)::int as claim_count
        from public.user_point_activity_claims c
        where c.tenant_id = $1
          and c.claimed_at >= $2::timestamptz
          and c.claimed_at < $3::timestamptz
          and c.status = 'claimed'
        group by c.user_id
      ),
      user_exchanges as (
        select o.user_id,
               count(*)::int as exchange_count,
               coalesce(sum(o.cost_points) filter (where o.status <> 'cancelled'), 0)::int as exchange_points
        from public.user_point_exchange_orders o
        where o.tenant_id = $1
          and o.exchanged_at >= $2::timestamptz
          and o.exchanged_at < $3::timestamptz
        group by o.user_id
      )
      select (count(*) over())::int as "totalCount",
             up.user_id as "userId", u.name as "userName", u.phone as "userPhone", u.primary_role as "primaryRole",
             up.event_count as "eventCount", up.earn_event_count as "earnEventCount",
             coalesce(uc.claim_count, 0)::int as "claimCount",
             coalesce(ue.exchange_count, 0)::int as "exchangeCount",
             up.earned_points as "earnedPoints", up.spent_points as "spentPoints",
             coalesce(ue.exchange_points, 0)::int as "exchangePoints",
             up.max_earned_event as "maxEarnedEvent", up.max_spent_event as "maxSpentEvent",
             up.latest_event_at as "latestEventAt",
             array_remove(array[
               case when up.earned_points >= $4::int then 'high_earned_points' end,
               case when coalesce(uc.claim_count, 0) >= $5::int then 'high_claim_frequency' end,
               case when coalesce(ue.exchange_points, 0) >= $6::int then 'high_exchange_points' end,
               case when coalesce(up.max_earned_event, 0) >= $7::int then 'large_single_earn_event' end
             ], null) as "riskFlags"
      from user_points up
      left join user_claims uc on uc.user_id = up.user_id
      left join user_exchanges ue on ue.user_id = up.user_id
      left join public.platform_users u on u.id = up.user_id
      where up.earned_points >= $4::int
         or coalesce(uc.claim_count, 0) >= $5::int
         or coalesce(ue.exchange_points, 0) >= $6::int
         or coalesce(up.max_earned_event, 0) >= $7::int
      order by array_length(array_remove(array[
                 case when up.earned_points >= $4::int then 'high_earned_points' end,
                 case when coalesce(uc.claim_count, 0) >= $5::int then 'high_claim_frequency' end,
                 case when coalesce(ue.exchange_points, 0) >= $6::int then 'high_exchange_points' end,
                 case when coalesce(up.max_earned_event, 0) >= $7::int then 'large_single_earn_event' end
               ], null), 1) desc nulls last,
               up.earned_points desc, coalesce(uc.claim_count, 0) desc, up.latest_event_at desc
      limit $8
      `,
      [
        ...baseParams,
        highEarnedThreshold,
        highClaimThreshold,
        highRedeemThreshold,
        highEventPointsThreshold,
        limit,
      ],
    ),
    query<Record<string, unknown>>(
      `
      select (count(*) over())::int as "totalCount",
             e.id, e.user_id as "userId", u.name as "userName", u.phone as "userPhone",
             e.event_type as "eventType", e.points, e.balance_after as "balanceAfter",
             e.source_type as "sourceType", e.source_id as "sourceId",
             e.metadata, e.created_at as "createdAt"
      from public.user_score_events e
      left join public.platform_users u on u.id = e.user_id
      where e.tenant_id = $1
        and e.created_at >= $2::timestamptz
        and e.created_at < $3::timestamptz
        and abs(e.points) >= $4::int
      order by abs(e.points) desc, e.created_at desc
      limit $5
      `,
      [...baseParams, highEventPointsThreshold, limit],
    ),
    query<Record<string, unknown>>(
      `
      select (count(*) over())::int as "totalCount",
             t.id as "taskId", t.code::text as "taskCode", t.title,
             t.task_type as "taskType", t.reward_points as "rewardPoints",
             count(c.id)::int as "claimCount",
             count(distinct c.user_id)::int as "claimUserCount",
             coalesce(sum(t.reward_points), 0)::int as "issuedPoints",
             max(c.claimed_at) as "latestClaimedAt"
      from public.point_activity_tasks t
      join public.user_point_activity_claims c
        on c.tenant_id = t.tenant_id
       and c.task_id = t.id
       and c.status = 'claimed'
       and c.claimed_at >= $2::timestamptz
       and c.claimed_at < $3::timestamptz
      where t.tenant_id = $1
      group by t.id, t.code, t.title, t.task_type, t.reward_points
      having count(c.id) >= $4::int
          or coalesce(sum(t.reward_points), 0) >= $5::int
      order by "claimCount" desc, "issuedPoints" desc, "latestClaimedAt" desc
      limit $6
      `,
      [
        ...baseParams,
        highClaimThreshold,
        highEarnedThreshold,
        limit,
      ],
    ),
    query<Record<string, unknown>>(
      `
      select (count(*) over())::int as "totalCount",
             o.id, o.user_id as "userId", u.name as "userName", u.phone as "userPhone",
             o.item_id as "itemId", i.code::text as "itemCode", i.title as "itemTitle",
             i.item_type as "itemType", o.status, o.cost_points as "costPoints",
             o.coupon_redemption_id as "couponRedemptionId", cr.coupon_code as "couponCode",
             o.asset_id as "assetId", o.metadata, o.exchanged_at as "exchangedAt"
      from public.user_point_exchange_orders o
      join public.point_exchange_items i on i.tenant_id = o.tenant_id and i.id = o.item_id
      left join public.platform_users u on u.id = o.user_id
      left join public.coupon_redemptions cr on cr.tenant_id = o.tenant_id and cr.id = o.coupon_redemption_id
      where o.tenant_id = $1
        and o.exchanged_at >= $2::timestamptz
        and o.exchanged_at < $3::timestamptz
        and o.cost_points >= $4::int
      order by o.cost_points desc, o.exchanged_at desc
      limit $5
      `,
      [...baseParams, highRedeemThreshold, limit],
    ),
    query<Record<string, unknown>>(
      `
      select (created_at at time zone 'Asia/Shanghai')::date::text as date,
             count(*)::int as "eventCount",
             coalesce(sum(points) filter (where points > 0), 0)::int as "earnedPoints",
             coalesce(abs(sum(points) filter (where points < 0)), 0)::int as "spentPoints"
      from public.user_score_events
      where tenant_id = $1
        and created_at >= $2::timestamptz
        and created_at < $3::timestamptz
      group by (created_at at time zone 'Asia/Shanghai')::date
      order by date asc
      `,
      baseParams,
    ),
  ]);

  const summaryRow = summary[0] || {};
  const suspiciousUserCount = totalRows(suspiciousUsers);
  const suspiciousEventCount = totalRows(suspiciousEvents) + totalRows(suspiciousTasks) + totalRows(suspiciousExchanges);
  return {
    item: {
      range,
      thresholds: {
        highEarnedThreshold,
        highClaimThreshold,
        highEventPointsThreshold,
        highRedeemThreshold,
      },
      summary: {
        eventCount: intValue(summaryRow.eventCount, 0),
        affectedUsers: intValue(summaryRow.affectedUsers, 0),
        earnedPoints: intValue(summaryRow.earnedPoints, 0),
        spentPoints: intValue(summaryRow.spentPoints, 0),
        netPoints: intValue(summaryRow.earnedPoints, 0) - intValue(summaryRow.spentPoints, 0),
        maxEarnedEvent: intValue(summaryRow.maxEarnedEvent, 0),
        maxSpentEvent: Math.abs(intValue(summaryRow.maxSpentEvent, 0)),
        suspiciousUserCount,
        suspiciousEventCount,
        riskScore: Math.min(100, Math.trunc(
          suspiciousUserCount * 10
          + suspiciousEventCount * 5
          + numberValue(summaryRow.earnedPoints, 0) / Math.max(highEarnedThreshold, 1),
        )),
      },
      eventBreakdown,
      suspiciousUsers: stripTotalCount(suspiciousUsers),
      suspiciousEvents: stripTotalCount(suspiciousEvents),
      suspiciousTasks: stripTotalCount(suspiciousTasks),
      suspiciousExchanges: stripTotalCount(suspiciousExchanges),
      dailyTrend,
    },
  };
}

export async function pointActivityTasksRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requirePointMarketingPermission(auth, 'read');
  const limit = intParam(ctx, 'limit', 100, 500);
  const status = stringParam(ctx, 'status');
  const taskType = stringParam(ctx, 'taskType');
  const params: unknown[] = [auth.tenantId];
  const filters = ['t.tenant_id = $1'];
  if (status) {
    if (!TASK_STATUSES.includes(status)) throw new HttpError(400, 'status is invalid', 'INVALID_STATUS');
    params.push(status);
    filters.push(`t.status = $${params.length}`);
  }
  if (taskType) {
    if (!TASK_TYPES.includes(taskType)) throw new HttpError(400, 'taskType is invalid', 'INVALID_TASK_TYPE');
    params.push(taskType);
    filters.push(`t.task_type = $${params.length}`);
  }
  params.push(limit);

  const items = await query(
    `
    select t.id, t.legacy_id as "legacyId", t.code::text, t.title, t.description,
           t.task_type as "taskType", t.reward_points as "rewardPoints",
           t.claim_limit_per_user as "claimLimitPerUser", t.period_type as "periodType",
           t.valid_from as "validFrom", t.valid_to as "validTo", t.status,
           t.sort_order as "order", t.metadata, t.created_by as "createdBy",
           count(c.id)::int as "claimCount",
           count(distinct c.user_id)::int as "claimUserCount",
           t.created_at as "createdAt", t.updated_at as "updatedAt"
    from public.point_activity_tasks t
    left join public.user_point_activity_claims c
      on c.tenant_id = t.tenant_id
     and c.task_id = t.id
     and c.status = 'claimed'
    where ${filters.join(' and ')}
    group by t.id
    order by t.sort_order asc, t.created_at desc
    limit $${params.length}
    `,
    params,
  );
  return { items };
}

export async function upsertPointActivityTaskRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requirePointMarketingPermission(auth, 'write');
  const body = await readJsonBody(ctx);
  const taskType = optionalChoice(body.taskType, TASK_TYPES, 'manual');
  const periodType = optionalChoice(body.periodType, PERIOD_TYPES, taskType === 'daily_check_in' ? 'daily' : 'once');
  const status = optionalChoice(body.status, TASK_STATUSES, 'active');
  const rewardPoints = Math.max(1, Math.min(100000, intValue(body.rewardPoints, 1)));
  const claimLimitPerUser = Math.max(1, Math.min(100000, intValue(body.claimLimitPerUser, 1)));
  const code = codeValue(body);

  const item = await transaction(async client => {
    const result = await client.query(
      `
      insert into public.point_activity_tasks (
        id, tenant_id, legacy_id, code, title, description, task_type,
        reward_points, claim_limit_per_user, period_type, valid_from, valid_to,
        status, sort_order, metadata, created_by
      )
      values (
        coalesce($2::uuid, gen_random_uuid()), $1, $3, $4, $5, $6, $7,
        $8, $9, $10, $11::timestamptz, $12::timestamptz,
        $13, $14, $15::jsonb, $16
      )
      on conflict (tenant_id, code)
      do update set legacy_id = coalesce(excluded.legacy_id, public.point_activity_tasks.legacy_id),
                    title = excluded.title,
                    description = excluded.description,
                    task_type = excluded.task_type,
                    reward_points = excluded.reward_points,
                    claim_limit_per_user = excluded.claim_limit_per_user,
                    period_type = excluded.period_type,
                    valid_from = excluded.valid_from,
                    valid_to = excluded.valid_to,
                    status = excluded.status,
                    sort_order = excluded.sort_order,
                    metadata = excluded.metadata,
                    updated_at = now()
      returning id, legacy_id as "legacyId", code::text, title, description,
                task_type as "taskType", reward_points as "rewardPoints",
                claim_limit_per_user as "claimLimitPerUser", period_type as "periodType",
                valid_from as "validFrom", valid_to as "validTo", status,
                sort_order as "order", metadata, created_by as "createdBy",
                created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        optionalUuidString(body.id, 'id'),
        nullableString(body.legacyId),
        code,
        requiredString(body, 'title'),
        nullableString(body.description),
        taskType,
        rewardPoints,
        claimLimitPerUser,
        periodType,
        optionalDateTimeText(body.validFrom, 'validFrom'),
        optionalDateTimeText(body.validTo, 'validTo'),
        status,
        intValue(body.order ?? body.sortOrder, 0),
        JSON.stringify(objectValue(body.metadata)),
        auth.userId,
      ],
    );
    await recordAudit(client, auth, 'tenant.point_activity_task.upserted', 'point_activity_tasks', result.rows[0].id, {
      code,
      taskType,
      rewardPoints,
      status,
    });
    return result.rows[0];
  });

  return { item };
}

export async function pointActivityClaimsRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requirePointMarketingPermission(auth, 'read');
  const limit = intParam(ctx, 'limit', 100, 500);
  const taskId = optionalUuidString(stringParam(ctx, 'taskId'), 'taskId');
  const userId = optionalUuidString(stringParam(ctx, 'userId'), 'userId');
  const params: unknown[] = [auth.tenantId];
  const filters = ['c.tenant_id = $1'];
  if (taskId) {
    params.push(taskId);
    filters.push(`c.task_id = $${params.length}::uuid`);
  }
  if (userId) {
    params.push(userId);
    filters.push(`c.user_id = $${params.length}::uuid`);
  }
  params.push(limit);

  const items = await query(
    `
    select c.id, c.user_id as "userId", u.name as "userName", u.phone as "userPhone",
           c.task_id as "taskId", t.code::text as "taskCode", t.title as "taskTitle",
           t.reward_points as "rewardPoints", c.period_key as "periodKey",
           c.score_event_id as "scoreEventId", c.source_type as "sourceType",
           c.source_id as "sourceId", c.status, c.metadata, c.claimed_at as "claimedAt",
           c.created_at as "createdAt", c.updated_at as "updatedAt"
    from public.user_point_activity_claims c
    join public.point_activity_tasks t on t.tenant_id = c.tenant_id and t.id = c.task_id
    left join public.platform_users u on u.id = c.user_id
    where ${filters.join(' and ')}
    order by c.claimed_at desc
    limit $${params.length}
    `,
    params,
  );
  return { items };
}

export async function pointExchangeItemsRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requirePointMarketingPermission(auth, 'read');
  const limit = intParam(ctx, 'limit', 100, 500);
  const status = stringParam(ctx, 'status');
  const itemType = stringParam(ctx, 'itemType');
  const params: unknown[] = [auth.tenantId];
  const filters = ['i.tenant_id = $1'];
  if (status) {
    if (!EXCHANGE_ITEM_STATUSES.includes(status)) throw new HttpError(400, 'status is invalid', 'INVALID_STATUS');
    params.push(status);
    filters.push(`i.status = $${params.length}`);
  }
  if (itemType) {
    if (!EXCHANGE_ITEM_TYPES.includes(itemType)) throw new HttpError(400, 'itemType is invalid', 'INVALID_EXCHANGE_ITEM_TYPE');
    params.push(itemType);
    filters.push(`i.item_type = $${params.length}`);
  }
  params.push(limit);

  const items = await query(
    `
    select i.id, i.legacy_id as "legacyId", i.code::text, i.title, i.description,
           i.cost_points as "costPoints", i.item_type as "itemType",
           i.coupon_id as "couponId", c.code::text as "couponCode",
           i.asset_id as "assetId", i.stock_total as "stockTotal",
           i.stock_used as "stockUsed",
           case when i.stock_total is null then null else greatest(i.stock_total - i.stock_used, 0) end as "stockRemaining",
           i.per_user_limit as "perUserLimit", i.valid_from as "validFrom",
           i.valid_to as "validTo", i.status, i.sort_order as "order",
           i.metadata, i.created_by as "createdBy",
           count(o.id)::int as "orderCount",
           i.created_at as "createdAt", i.updated_at as "updatedAt"
    from public.point_exchange_items i
    left join public.coupons c on c.tenant_id = i.tenant_id and c.id = i.coupon_id
    left join public.user_point_exchange_orders o
      on o.tenant_id = i.tenant_id
     and o.item_id = i.id
     and o.status <> 'cancelled'
    where ${filters.join(' and ')}
    group by i.id, c.code
    order by i.sort_order asc, i.created_at desc
    limit $${params.length}
    `,
    params,
  );
  return { items };
}

export async function upsertPointExchangeItemRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requirePointMarketingPermission(auth, 'write');
  const body = await readJsonBody(ctx);
  const code = codeValue(body);
  const itemType = optionalChoice(body.itemType, EXCHANGE_ITEM_TYPES, 'manual');
  const status = optionalChoice(body.status, EXCHANGE_ITEM_STATUSES, 'active');
  const couponId = optionalUuidString(body.couponId, 'couponId');
  const assetId = optionalUuidString(body.assetId, 'assetId');
  const stockTotal = body.stockTotal === undefined || body.stockTotal === null || body.stockTotal === ''
    ? null
    : Math.max(0, intValue(body.stockTotal, 0));
  const costPoints = Math.max(1, Math.min(10000000, intValue(body.costPoints, 1)));
  const perUserLimit = Math.max(1, Math.min(100000, intValue(body.perUserLimit, 1)));

  if (itemType === 'coupon' && !couponId) {
    throw new HttpError(400, 'couponId is required for coupon exchange item', 'POINT_EXCHANGE_COUPON_REQUIRED');
  }
  if (itemType === 'asset' && !assetId) {
    throw new HttpError(400, 'assetId is required for asset exchange item', 'POINT_EXCHANGE_ASSET_REQUIRED');
  }

  const item = await transaction(async client => {
    if (couponId) {
      const coupon = await client.query(
        'select id from public.coupons where tenant_id = $1 and id = $2 limit 1',
        [auth.tenantId, couponId],
      );
      if (!coupon.rows[0]) throw new HttpError(404, 'Coupon not found for this tenant', 'COUPON_NOT_FOUND');
    }
    if (assetId) {
      const asset = await client.query(
        'select id from public.content_assets where tenant_id = $1 and id = $2 limit 1',
        [auth.tenantId, assetId],
      );
      if (!asset.rows[0]) throw new HttpError(404, 'Asset not found for this tenant', 'ASSET_NOT_FOUND');
    }

    const result = await client.query(
      `
      insert into public.point_exchange_items (
        id, tenant_id, legacy_id, code, title, description, cost_points,
        item_type, coupon_id, asset_id, stock_total, per_user_limit,
        valid_from, valid_to, status, sort_order, metadata, created_by
      )
      values (
        coalesce($2::uuid, gen_random_uuid()), $1, $3, $4, $5, $6, $7,
        $8, $9::uuid, $10::uuid, $11, $12,
        $13::timestamptz, $14::timestamptz, $15, $16, $17::jsonb, $18
      )
      on conflict (tenant_id, code)
      do update set legacy_id = coalesce(excluded.legacy_id, public.point_exchange_items.legacy_id),
                    title = excluded.title,
                    description = excluded.description,
                    cost_points = excluded.cost_points,
                    item_type = excluded.item_type,
                    coupon_id = excluded.coupon_id,
                    asset_id = excluded.asset_id,
                    stock_total = excluded.stock_total,
                    per_user_limit = excluded.per_user_limit,
                    valid_from = excluded.valid_from,
                    valid_to = excluded.valid_to,
                    status = excluded.status,
                    sort_order = excluded.sort_order,
                    metadata = excluded.metadata,
                    updated_at = now()
      returning id, legacy_id as "legacyId", code::text, title, description,
                cost_points as "costPoints", item_type as "itemType",
                coupon_id as "couponId", asset_id as "assetId",
                stock_total as "stockTotal", stock_used as "stockUsed",
                per_user_limit as "perUserLimit", valid_from as "validFrom",
                valid_to as "validTo", status, sort_order as "order",
                metadata, created_by as "createdBy", created_at as "createdAt",
                updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        optionalUuidString(body.id, 'id'),
        nullableString(body.legacyId),
        code,
        requiredString(body, 'title'),
        nullableString(body.description),
        costPoints,
        itemType,
        couponId,
        assetId,
        stockTotal,
        perUserLimit,
        optionalDateTimeText(body.validFrom, 'validFrom'),
        optionalDateTimeText(body.validTo, 'validTo'),
        status,
        intValue(body.order ?? body.sortOrder, 0),
        JSON.stringify(objectValue(body.metadata)),
        auth.userId,
      ],
    );
    await recordAudit(client, auth, 'tenant.point_exchange_item.upserted', 'point_exchange_items', result.rows[0].id, {
      code,
      itemType,
      costPoints,
      status,
    });
    return result.rows[0];
  });

  return { item };
}

export async function pointExchangeOrdersRoute(ctx: RequestContext) {
  const auth = await requireTenantAdmin(ctx);
  requirePointMarketingPermission(auth, 'read');
  const limit = intParam(ctx, 'limit', 100, 500);
  const itemId = optionalUuidString(stringParam(ctx, 'itemId'), 'itemId');
  const userId = optionalUuidString(stringParam(ctx, 'userId'), 'userId');
  const status = stringParam(ctx, 'status');
  const params: unknown[] = [auth.tenantId];
  const filters = ['o.tenant_id = $1'];
  if (itemId) {
    params.push(itemId);
    filters.push(`o.item_id = $${params.length}::uuid`);
  }
  if (userId) {
    params.push(userId);
    filters.push(`o.user_id = $${params.length}::uuid`);
  }
  if (status) {
    if (!EXCHANGE_ORDER_STATUSES.includes(status)) throw new HttpError(400, 'status is invalid', 'INVALID_STATUS');
    params.push(status);
    filters.push(`o.status = $${params.length}`);
  }
  params.push(limit);

  const items = await query(
    `
    select o.id, o.user_id as "userId", u.name as "userName", u.phone as "userPhone",
           o.item_id as "itemId", i.code::text as "itemCode", i.title as "itemTitle",
           i.item_type as "itemType", o.status, o.cost_points as "costPoints",
           o.score_event_id as "scoreEventId", o.coupon_redemption_id as "couponRedemptionId",
           cr.coupon_code as "couponCode", o.asset_id as "assetId",
           o.metadata, o.exchanged_at as "exchangedAt",
           o.created_at as "createdAt", o.updated_at as "updatedAt"
    from public.user_point_exchange_orders o
    join public.point_exchange_items i on i.tenant_id = o.tenant_id and i.id = o.item_id
    left join public.platform_users u on u.id = o.user_id
    left join public.coupon_redemptions cr on cr.tenant_id = o.tenant_id and cr.id = o.coupon_redemption_id
    where ${filters.join(' and ')}
    order by o.exchanged_at desc
    limit $${params.length}
    `,
    params,
  );
  return { items };
}
