import { sql, type SQL } from 'drizzle-orm'
import type { Db } from './db.js'

/**
 * Catalog quality checks. One SQL predicate per issue type, evaluated against products resolved
 * for a store view (store-view values, falling back to the default scope). Shared by the Catalog API
 * today and by feeds/reporting later, so every consumer agrees on what "missing images" means.
 */
export const QUALITY_CHECKS = {
  missing_images: {
    label: 'Individually visible products with no enabled image',
    where: sql`r.visibility <> 1 AND NOT EXISTS (
      SELECT 1 FROM catalog.product_images i WHERE i.product_id = r.product_id AND NOT i.disabled)`,
  },
  missing_description: {
    label: 'Individually visible products with an empty description',
    where: sql`r.visibility <> 1 AND coalesce(btrim(r.description), '') = ''`,
  },
  missing_required_attributes: {
    label: 'Products missing a value for an attribute their attribute set marks as required',
    where: sql`cardinality(r.missing_required) > 0`,
  },
  no_category: {
    label: 'Products visible in catalog but assigned to no category',
    where: sql`r.visibility IN (2, 4) AND NOT EXISTS (
      SELECT 1 FROM catalog.product_categories c WHERE c.product_id = r.product_id)`,
  },
  invalid_price: {
    label: 'Simple/virtual products with no or zero price, or a special price not below the base price',
    where: sql`r.type_id IN ('simple', 'virtual', 'downloadable') AND (
      r.price IS NULL OR r.price <= 0 OR r.special_price <= 0 OR r.special_price >= r.price)`,
  },
  enabled_not_visible: {
    label: 'Enabled, "Not Visible Individually", and not a child of any configurable/bundle/grouped product',
    where: sql`r.status = 1 AND r.visibility = 1 AND NOT EXISTS (
      SELECT 1 FROM catalog.product_relations pr WHERE pr.child_sku = r.sku)`,
  },
  not_in_website: {
    label: 'Enabled products not assigned to any website',
    where: sql`r.status = 1 AND cardinality(r.website_ids) = 0`,
  },
  out_of_stock_enabled: {
    label: 'Enabled stock-managed products with no quantity in any source',
    where: sql`r.status = 1 AND r.type_id IN ('simple', 'virtual')
      AND EXISTS (SELECT 1 FROM catalog.inventory_source_items s WHERE s.sku = r.sku)
      AND NOT EXISTS (SELECT 1 FROM catalog.inventory_source_items s WHERE s.sku = r.sku AND s.in_stock AND s.quantity > 0)`,
  },
  configurable_without_children: {
    label: 'Configurable products with no linked variants',
    where: sql`r.type_id = 'configurable' AND NOT EXISTS (
      SELECT 1 FROM catalog.product_relations pr WHERE pr.parent_id = r.product_id AND pr.kind = 'configurable')`,
  },
} satisfies Record<string, { label: string; where: SQL }>

export type QualityIssueType = keyof typeof QUALITY_CHECKS
export const QUALITY_ISSUE_TYPES = Object.keys(QUALITY_CHECKS) as QualityIssueType[]
/** Checks that only make sense when stock is managed in Magento. */
export const STOCK_ISSUE_TYPES: QualityIssueType[] = ['out_of_stock_enabled']

export function qualityIssueTypes(opts: { stock: boolean }): QualityIssueType[] {
  return opts.stock ? QUALITY_ISSUE_TYPES : QUALITY_ISSUE_TYPES.filter(t => !STOCK_ISSUE_TYPES.includes(t))
}

/** Attributes held in dedicated Magento columns, never in custom_attributes. */
const CORE_FIELDS = ['name', 'sku', 'price', 'status', 'visibility', 'weight', 'quantity_and_stock_status', 'category_ids', 'tier_price', 'media_gallery', 'gallery']

/** Products resolved for a store view, plus the list of required attributes they lack. */
function resolved(storeId: number): SQL {
  return sql`
    SELECT p.product_id, p.sku, p.type_id, p.price, p.special_price, p.website_ids,
           sd.name, sd.status, sd.visibility, sd.description,
           ARRAY(
             SELECT asa.attribute_code FROM catalog.attribute_set_attributes asa
             WHERE asa.attribute_set_id = p.attribute_set_id AND asa.is_required
               AND asa.attribute_code <> ALL(${sql.param(CORE_FIELDS)}::text[])
               AND coalesce(sd.attributes ->> asa.attribute_code, '') = ''
             ORDER BY asa.attribute_code
           ) AS missing_required
    FROM catalog.products p
    JOIN LATERAL (
      SELECT * FROM catalog.product_store_data x
      WHERE x.product_id = p.product_id AND x.store_id IN (${storeId}, 0)
      ORDER BY x.store_id DESC LIMIT 1
    ) sd ON true
    WHERE p.deleted_at IS NULL`
}

export async function qualitySummary(db: Db, storeId: number, types: QualityIssueType[] = QUALITY_ISSUE_TYPES) {
  const counts = types.map(
    type => sql`SELECT ${type}::text AS type, count(*)::int AS count FROM r WHERE ${QUALITY_CHECKS[type].where}`,
  )
  const rows = await db.execute<{ type: QualityIssueType; count: number }>(
    sql`WITH r AS MATERIALIZED (${resolved(storeId)}) ${sql.join(counts, sql` UNION ALL `)}`,
  )
  const [total] = await db.execute<{ n: number }>(sql`SELECT count(*)::int AS n FROM catalog.products WHERE deleted_at IS NULL`)
  return {
    totalProducts: total?.n ?? 0,
    issues: rows.map(r => ({ type: r.type, label: QUALITY_CHECKS[r.type].label, count: r.count })),
  }
}

export type QualityIssueRow = {
  sku: string
  name: string
  type_id: string
  status: number
  visibility: number
  missing_required: string[]
}

export async function qualityIssues(db: Db, type: QualityIssueType, storeId: number, limit: number, offset: number) {
  const where = QUALITY_CHECKS[type].where
  const [rows, [count]] = await Promise.all([
    db.execute<QualityIssueRow>(
      sql`WITH r AS (${resolved(storeId)})
          SELECT sku, name, type_id, status, visibility, missing_required FROM r WHERE ${where}
          ORDER BY sku LIMIT ${limit} OFFSET ${offset}`,
    ),
    db.execute<{ n: number }>(sql`WITH r AS (${resolved(storeId)}) SELECT count(*)::int AS n FROM r WHERE ${where}`),
  ])
  return { type, label: QUALITY_CHECKS[type].label, total: count?.n ?? 0, items: [...rows] }
}