import { Prisma } from '@prisma/client'
import { prismaClient } from './prisma.mjs'

function firstJson(rows, key = 'item') {
  return rows?.[0]?.[key] ?? null
}

function productRowJson(row) {
  if (!row) return null
  return {
    id: row.id,
    slug: row.slug,
    type: row.type,
    title: row.title,
    summary: row.summary,
    priceAmount: row.priceAmount,
    purchaseFlow: row.purchaseFlow,
    fulfillmentFlow: row.fulfillmentFlow,
    status: row.status,
  }
}

export async function listPublishedProducts(type = '') {
  const prisma = prismaClient()
  const rows = await prisma.$queryRaw`
    select
      p.id,
      p.slug,
      p.type,
      p.title,
      p.summary,
      p.price_amount as "priceAmount",
      p.purchase_flow as "purchaseFlow",
      p.fulfillment_flow as "fulfillmentFlow",
      p.status
    from products p
    where p.status = 'published'
      and p.id <> 'data-service-custom'
      and (${String(type || '')} = '' or p.type = ${String(type || '')})
    order by p.created_at
  `
  return rows.map(productRowJson)
}

export async function findPublishedProduct(slugOrId) {
  const prisma = prismaClient()
  const key = String(slugOrId || '').trim()
  if (!key) return null
  const rows = await prisma.$queryRaw`
    select
      p.id,
      p.slug,
      p.type,
      p.title,
      p.summary,
      p.price_amount as "priceAmount",
      p.purchase_flow as "purchaseFlow",
      p.fulfillment_flow as "fulfillmentFlow",
      p.status
    from products p
    where p.status = 'published'
      and (p.slug = ${key} or p.id = ${key})
    limit 1
  `
  return productRowJson(rows[0])
}

export async function listMembershipCatalogRecord() {
  const prisma = prismaClient()
  const rows = await prisma.$queryRaw`
    select json_build_object(
      'plans', coalesce((
        select json_agg(json_build_object(
          'productId', mp.product_id,
          'slug', p.slug,
          'level', mp.level,
          'name', mp.name,
          'badge', mp.badge,
          'description', mp.description,
          'ctaLabel', mp.cta_label,
          'tone', mp.tone,
          'monthlyPriceAmount', mp.monthly_price_amount,
          'yearlyPriceAmount', mp.yearly_price_amount,
          'recommended', mp.recommended,
          'sortOrder', mp.sort_order,
          'status', mp.status,
          'groups', coalesce((
            select json_agg(json_build_object(
              'id', g.id,
              'title', g.title,
              'items', coalesce((
                select json_agg(i.label order by i.sort_order)
                from membership_benefit_items i
                where i.group_id = g.id
              ), '[]'::json)
            ) order by g.sort_order)
            from membership_benefit_groups g
            where g.plan_product_id = mp.product_id
          ), '[]'::json)
        ) order by mp.sort_order)
        from membership_plans mp
        join products p on p.id = mp.product_id
        where mp.status = 'published' and p.status = 'published'
      ), '[]'::json),
      'compareRows', coalesce((
        select json_agg(json_build_object(
          'id', id,
          'label', label,
          'registeredValue', registered_value,
          'juniorValue', junior_value,
          'seniorValue', senior_value
        ) order by sort_order)
        from membership_compare_rows
      ), '[]'::json),
      'faqs', coalesce((
        select json_agg(json_build_object(
          'id', id,
          'question', question,
          'answer', answer
        ) order by sort_order)
        from membership_faqs
        where status = 'published'
      ), '[]'::json)
    ) as item
  `
  return firstJson(rows) || { plans: [], compareRows: [], faqs: [] }
}

export async function listAgentCatalogRecord() {
  const prisma = prismaClient()
  const rows = await prisma.$queryRaw`
    select json_build_object(
      'categories', coalesce((
        select json_agg(json_build_object(
          'id', id,
          'name', name,
          'sortOrder', sort_order
        ) order by sort_order)
        from agent_categories
        where status = 'published'
      ), '[]'::json),
      'workflow', (
        select json_build_object(
          'id', c.id,
          'title', c.title,
          'scene', c.scene,
          'result', c.result,
          'imageUrl', c.image_url,
          'nodes', coalesce((
            select json_agg(json_build_object(
              'id', n.id,
              'label', n.label,
              'title', n.title,
              'detail', n.detail,
              'x', n.x,
              'y', n.y,
              'size', n.size
            ) order by n.sort_order)
            from agent_workflow_nodes n
            where n.case_id = c.id
          ), '[]'::json)
        )
        from agent_workflow_cases c
        where c.status = 'published'
        order by c.sort_order
        limit 1
      ),
      'items', coalesce((
        select json_agg(agent_product_json(p, d) order by d.sort_order, p.created_at)
        from products p
        join agent_product_details d on d.product_id = p.id
        where p.type = 'agent'
          and p.status = 'published'
          and d.status = 'published'
      ), '[]'::json)
    ) as item
  `
  return firstJson(rows) || { categories: [], workflow: null, items: [] }
}

export async function findAgentProductRecord(slugOrId) {
  const prisma = prismaClient()
  const key = String(slugOrId || '').trim()
  if (!key) return null
  const rows = await prisma.$queryRaw`
    select agent_product_json(p, d) as item
    from products p
    join agent_product_details d on d.product_id = p.id
    where p.type = 'agent'
      and p.status = 'published'
      and d.status = 'published'
      and (p.slug = ${key} or p.id = ${key})
    limit 1
  `
  return firstJson(rows)
}

const dataProductJsonSql = Prisma.sql`
  json_build_object(
    'id', p.id,
    'productId', p.id,
    'slug', p.slug,
    'type', p.type,
    'title', p.title,
    'summary', p.summary,
    'priceAmount', p.price_amount,
    'purchaseFlow', p.purchase_flow,
    'fulfillmentFlow', p.fulfillment_flow,
    'productStatus', p.status,
    'status', d.status,
    'category', d.category,
    'fileFormat', d.file_format,
    'coverUrl', d.cover_url,
    'fileUrl', d.file_url,
    'fileName', d.file_name,
    'fileSize', d.file_size,
    'tags', coalesce(to_json(d.tags), '[]'::json),
    'caseGoal', d.case_goal,
    'requestOutline', d.request_outline,
    'deliveryOutline', d.delivery_outline,
    'serviceNote', d.service_note,
    'featuredPosition', d.featured_position,
    'sortOrder', d.sort_order
  )
`

export async function listDataCatalogRecord() {
  const prisma = prismaClient()
  const rows = await prisma.$queryRaw(Prisma.sql`
    select json_build_object(
      'items', coalesce((
        select json_agg(${dataProductJsonSql} order by d.featured_position nulls last, d.sort_order, p.created_at)
        from products p
        join data_product_details d on d.product_id = p.id
        where p.type = 'data'
          and p.status = 'published'
          and d.status = 'published'
      ), '[]'::json)
    ) as item
  `)
  return firstJson(rows) || { items: [] }
}

export async function findDataProductRecord(slugOrId) {
  const prisma = prismaClient()
  const key = String(slugOrId || '').trim()
  if (!key) return null
  const rows = await prisma.$queryRaw(Prisma.sql`
    select ${dataProductJsonSql} as item
    from products p
    join data_product_details d on d.product_id = p.id
    where p.type = 'data'
      and p.status = 'published'
      and d.status = 'published'
      and (p.slug = ${key} or p.id = ${key})
    limit 1
  `)
  return firstJson(rows)
}

const eventProductJsonSql = Prisma.sql`
  json_build_object(
    'id', p.id,
    'productId', p.id,
    'slug', p.slug,
    'type', p.type,
    'title', p.title,
    'summary', p.summary,
    'priceAmount', p.price_amount,
    'purchaseFlow', p.purchase_flow,
    'fulfillmentFlow', p.fulfillment_flow,
    'productStatus', p.status,
    'status', d.status,
    'eventType', d.event_type,
    'topic', d.topic,
    'eventFormat', d.event_format,
    'coverUrl', d.cover_url,
    'coverLabel', d.cover_label,
    'audience', d.audience,
    'highlights', coalesce(to_json(d.highlights), '[]'::json),
    'agenda', coalesce(to_json(d.agenda), '[]'::json),
    'startsAt', d.starts_at,
    'endsAt', d.ends_at,
    'location', d.location,
    'isOnline', d.is_online,
    'onlineUrl', d.online_url,
    'capacity', d.capacity,
    'registeredCount', d.initial_registered_count + coalesce((
      select count(*)::int
      from event_registrations er
      where er.product_id = p.id
        and er.status <> 'cancelled'
    ), 0),
    'realRegisteredCount', coalesce((
      select count(*)::int
      from event_registrations er
      where er.product_id = p.id
        and er.status <> 'cancelled'
    ), 0),
    'checkedInCount', coalesce((
      select count(*)::int
      from event_registrations er
      where er.product_id = p.id
        and er.status = 'checked_in'
    ), 0),
    'registrationStatus', case
      when d.status = 'registration_closed' then 'closed'
      when d.status <> 'published' or p.status <> 'published' then 'closed'
      when d.ends_at is not null and d.ends_at < now() then 'finished'
      when d.capacity > 0 and coalesce((
        select count(*)::int
        from event_registrations er
        where er.product_id = p.id
          and er.status <> 'cancelled'
      ), 0) >= d.capacity then 'full'
      else 'open'
    end,
    'initialRegisteredCount', d.initial_registered_count,
    'registeredAccess', d.registered_access,
    'juniorAccess', d.junior_access,
    'seniorAccess', d.senior_access,
    'registeredPriceAmount', d.registered_price_amount,
    'juniorPriceAmount', d.junior_price_amount,
    'seniorPriceAmount', d.senior_price_amount,
    'memberLevelRequired', d.member_level_required,
    'recommended', d.recommended,
    'sortOrder', d.sort_order
  )
`

export async function listEventCatalogRecord() {
  const prisma = prismaClient()
  const rows = await prisma.$queryRaw(Prisma.sql`
    select json_build_object(
      'items', coalesce((
        select json_agg(${eventProductJsonSql} order by d.recommended desc, d.sort_order, p.created_at)
        from products p
        join event_product_details d on d.product_id = p.id
        where p.type = 'event'
          and p.status = 'published'
          and d.status in ('published', 'registration_closed')
      ), '[]'::json)
    ) as item
  `)
  return firstJson(rows) || { items: [] }
}

export async function findEventProductRecord(slugOrId, { includeUnavailable = false } = {}) {
  const prisma = prismaClient()
  const key = String(slugOrId || '').trim()
  if (!key) return null
  const rows = await prisma.$queryRaw(Prisma.sql`
    select ${eventProductJsonSql} as item
    from products p
    join event_product_details d on d.product_id = p.id
    where p.type = 'event'
      and d.status <> 'deleted'
      and (${Boolean(includeUnavailable)}::boolean or (p.status = 'published' and d.status in ('published', 'registration_closed')))
      and (p.slug = ${key} or p.id = ${key})
    limit 1
  `)
  return firstJson(rows)
}

const courseProductJsonSql = Prisma.sql`
  json_build_object(
    'id', p.id,
    'productId', p.id,
    'slug', p.slug,
    'type', p.type,
    'title', p.title,
    'summary', p.summary,
    'priceAmount', p.price_amount,
    'purchaseFlow', p.purchase_flow,
    'fulfillmentFlow', p.fulfillment_flow,
    'productStatus', p.status,
    'status', d.status,
    'instructor', d.instructor,
    'courseType', d.course_type,
    'memberLevelRequired', d.member_level_required,
    'lessonCount', d.lesson_count,
    'startsAt', d.starts_at,
    'location', d.location,
    'courseStatus', d.course_status,
    'direction', d.direction,
    'courseFormat', d.course_format,
    'accessPolicy', d.access_policy,
    'registeredAccess', d.registered_access,
    'juniorAccess', d.junior_access,
    'seniorAccess', d.senior_access,
    'registeredPriceAmount', d.registered_price_amount,
    'juniorPriceAmount', d.junior_price_amount,
    'seniorPriceAmount', d.senior_price_amount,
    'coverUrl', d.cover_url,
    'liveUrl', d.live_url,
    'replayUrl', d.replay_url,
    'capacity', d.capacity,
    'registeredCount', coalesce((
      select count(*)::int
      from course_registrations cr
      where cr.product_id = p.id
        and cr.status <> 'cancelled'
    ), 0),
    'checkedInCount', coalesce((
      select count(*)::int
      from course_registrations cr
      where cr.product_id = p.id
        and cr.status = 'checked_in'
    ), 0),
    'checkinRequired', d.checkin_required,
    'recommended', d.recommended,
    'coverLabel', d.cover_label,
    'audience', d.audience,
    'outcomes', coalesce(to_json(d.outcomes), '[]'::json),
    'modules', coalesce(to_json(d.modules), '[]'::json),
    'lessonModules', coalesce((
      select json_agg(json_build_object(
        'id', cm.id,
        'title', cm.title,
        'description', cm.description,
        'videoUrl', cm.video_url,
        'materialUrl', cm.material_url,
        'durationMinutes', cm.duration_minutes,
        'sortOrder', cm.sort_order,
        'status', cm.status
      ) order by cm.sort_order)
      from course_modules cm
      where cm.product_id = p.id
        and cm.status <> 'deleted'
    ), '[]'::json),
    'accessNote', d.access_note,
    'sortOrder', d.sort_order
  )
`

export async function listCourseCatalogRecord() {
  const prisma = prismaClient()
  const rows = await prisma.$queryRaw(Prisma.sql`
    select json_build_object(
      'items', coalesce((
        select json_agg(${courseProductJsonSql} order by d.recommended desc, d.sort_order, p.created_at)
        from products p
        join course_product_details d on d.product_id = p.id
        where p.type = 'course'
          and p.status = 'published'
          and d.status = 'published'
      ), '[]'::json)
    ) as item
  `)
  return firstJson(rows) || { items: [] }
}

export async function findCourseProductRecord(slugOrId) {
  const prisma = prismaClient()
  const key = String(slugOrId || '').trim()
  if (!key) return null
  const rows = await prisma.$queryRaw(Prisma.sql`
    select ${courseProductJsonSql} as item
    from products p
    join course_product_details d on d.product_id = p.id
    where p.type = 'course'
      and (p.slug = ${key} or p.id = ${key})
    limit 1
  `)
  return firstJson(rows)
}
