import { query } from '../db/client.js'
import type {
  MessageDeliveryListQueryResult,
  MessageDeliveryRow,
} from '../types/repository-rows.js'

type MessageDeliveryCreateInput = {
  provider: string
  platform: string
  shopId?: string
  shopName?: string
  channel: string
  orderId?: number | string | null
  taskId?: number | string | null
  platformOrderId?: string
  recipientKey?: string
  messageContent?: string
  claimUrl?: string
  status?: string
  requestUrl?: string
  requestHeadersJson?: string | Record<string, unknown>
  requestBodyJson?: string | Record<string, unknown>
  responseStatus?: number | string
  responseJson?: string | Record<string, unknown>
  errorMessage?: string
  sentAt?: string | null
  createdAt: string
  updatedAt: string
}

type MessageDeliveryPatch = Partial<Pick<
  MessageDeliveryRow,
  | 'status'
  | 'request_url'
  | 'request_headers_json'
  | 'request_body_json'
  | 'response_status'
  | 'response_json'
  | 'error_message'
  | 'sent_at'
  | 'updated_at'
>>

type LatestSuccessfulMessageDeliveryQuery = {
  provider?: string
  platform?: string
  shopId?: string
  platformOrderId?: string
  channel?: string
  claimUrl?: string
}

type MessageDeliveryListQuery = {
  page?: number
  pageSize?: number
  provider?: string
  platform?: string
  status?: string
  shopId?: string
  platformOrderId?: string
  taskNo?: string
  dateFrom?: string
  dateTo?: string
}

export async function createMessageDelivery(
  input: MessageDeliveryCreateInput,
): Promise<MessageDeliveryRow | null> {
  const result = await query<MessageDeliveryRow>(
    `
      INSERT INTO message_deliveries (
        provider,
        platform,
        shop_id,
        shop_name,
        channel,
        order_id,
        task_id,
        platform_order_id,
        recipient_key,
        message_content,
        claim_url,
        status,
        request_url,
        request_headers_json,
        request_body_json,
        response_status,
        response_json,
        error_message,
        sent_at,
        created_at,
        updated_at
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14::jsonb, $15::jsonb, $16, $17::jsonb, $18, $19, $20, $21)
      RETURNING *
    `,
    [
      input.provider,
      input.platform,
      input.shopId || '',
      input.shopName || '',
      input.channel,
      input.orderId || null,
      input.taskId || null,
      input.platformOrderId || '',
      input.recipientKey || '',
      input.messageContent || '',
      input.claimUrl || '',
      input.status || 'pending',
      input.requestUrl || '',
      input.requestHeadersJson || '{}',
      input.requestBodyJson || '{}',
      Number(input.responseStatus || 0),
      input.responseJson || '{}',
      input.errorMessage || '',
      input.sentAt || null,
      input.createdAt,
      input.updatedAt,
    ],
  )

  return result.rows[0] || null
}

export async function getMessageDeliveryById(deliveryId: number | string): Promise<MessageDeliveryRow | null> {
  const result = await query<MessageDeliveryRow>('SELECT * FROM message_deliveries WHERE id = $1 LIMIT 1', [
    Number(deliveryId),
  ])
  return result.rows[0] || null
}

export async function updateMessageDelivery(
  deliveryId: number | string,
  patch: MessageDeliveryPatch = {},
): Promise<MessageDeliveryRow | null> {
  const current = await getMessageDeliveryById(deliveryId)
  if (!current) {
    return null
  }

  const next = { ...current, ...patch }
  const result = await query<MessageDeliveryRow>(
    `
      UPDATE message_deliveries
      SET
        status = $1,
        request_url = $2,
        request_headers_json = $3::jsonb,
        request_body_json = $4::jsonb,
        response_status = $5,
        response_json = $6::jsonb,
        error_message = $7,
        sent_at = $8,
        updated_at = $9
      WHERE id = $10
      RETURNING *
    `,
    [
      next.status,
      next.request_url || '',
      next.request_headers_json || '{}',
      next.request_body_json || '{}',
      Number(next.response_status || 0),
      next.response_json || '{}',
      next.error_message || '',
      next.sent_at || null,
      next.updated_at,
      Number(deliveryId),
    ],
  )

  return result.rows[0] || null
}

export async function findLatestSuccessfulMessageDeliveryByTask(
  taskId: number | string,
  channel: string,
): Promise<MessageDeliveryRow | null> {
  const result = await query<MessageDeliveryRow>(
    `
      SELECT *
      FROM message_deliveries
      WHERE task_id = $1 AND channel = $2 AND status = 'success'
      ORDER BY id DESC
      LIMIT 1
    `,
    [Number(taskId), channel],
  )

  return result.rows[0] || null
}

export async function findLatestSuccessfulMessageDelivery({
  provider = '',
  platform = '',
  shopId = '',
  platformOrderId = '',
  channel = '',
  claimUrl = '',
}: LatestSuccessfulMessageDeliveryQuery = {}): Promise<MessageDeliveryRow | null> {
  const result = await query<MessageDeliveryRow>(
    `
      SELECT *
      FROM message_deliveries
      WHERE provider = $1
        AND platform = $2
        AND shop_id = $3
        AND platform_order_id = $4
        AND channel = $5
        AND claim_url = $6
        AND status = 'success'
      ORDER BY id DESC
      LIMIT 1
    `,
    [
      provider,
      platform,
      shopId,
      platformOrderId,
      channel,
      claimUrl,
    ],
  )

  return result.rows[0] || null
}

export async function listMessageDeliveries({
  page = 1,
  pageSize = 20,
  provider = '',
  platform = '',
  status = '',
  shopId = '',
  platformOrderId = '',
  taskNo = '',
  dateFrom = '',
  dateTo = '',
}: MessageDeliveryListQuery = {}): Promise<MessageDeliveryListQueryResult> {
  const offset = (page - 1) * pageSize
  const filters: string[] = []
  const params: unknown[] = []

  if (provider) {
    params.push(provider)
    filters.push(`md.provider = $${params.length}`)
  }

  if (platform) {
    params.push(platform)
    filters.push(`md.platform = $${params.length}`)
  }

  if (status) {
    params.push(status)
    filters.push(`md.status = $${params.length}`)
  }

  if (shopId) {
    params.push(shopId)
    filters.push(`md.shop_id = $${params.length}`)
  }

  if (platformOrderId) {
    params.push(`%${platformOrderId}%`)
    filters.push(`md.platform_order_id ILIKE $${params.length}`)
  }

  if (taskNo) {
    params.push(`%${taskNo}%`)
    filters.push(`ft.task_no ILIKE $${params.length}`)
  }

  if (dateFrom) {
    params.push(dateFrom)
    filters.push(`md.created_at >= $${params.length}`)
  }

  if (dateTo) {
    params.push(dateTo)
    filters.push(`md.created_at <= $${params.length}`)
  }

  const whereClause = filters.length > 0 ? `WHERE ${filters.join(' AND ')}` : ''
  const fromClause = `
    FROM message_deliveries md
    LEFT JOIN fulfillment_tasks ft ON ft.id = md.task_id
  `

  const totalResult = await query<{ [column: string]: unknown, total: number }>(
    `SELECT COUNT(*)::int AS total ${fromClause} ${whereClause}`,
    params,
  )

  params.push(pageSize)
  params.push(offset)
  const itemsResult = await query<MessageDeliveryRow>(
    `
      SELECT
        md.*,
        ft.task_no,
        ft.task_status
      ${fromClause}
      ${whereClause}
      ORDER BY md.id DESC
      LIMIT $${params.length - 1} OFFSET $${params.length}
    `,
    params,
  )

  return {
    items: itemsResult.rows,
    total: Number(totalResult.rows[0]?.total || 0),
  }
}
