import { and, eq, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { customers, customerMergeSuggestions } from '../schema/customers'
import { auditLog } from './_audit-forward'

export interface MergeSuggestionCustomer {
  id: string
  name: string
  company: string | null
  email: string | null
  invoiceCount: number
  projectCount: number
}

export interface MergeSuggestion {
  id: string
  reason: 'email_match' | 'name_similarity'
  similarity: number | null
  customerA: MergeSuggestionCustomer
  customerB: MergeSuggestionCustomer
}

export interface MergeSuggestionsPage {
  suggestions: MergeSuggestion[]
  total: number
}

export interface MergeRequest {
  keepId: string
  deleteId: string
}

export interface MergeResult {
  keptCustomerId: string
  archivedCustomerId: string
  reassigned: {
    invoices: number
    projects: number
    contacts: number
    tickets: number
    portalUsers: number
    activities: number
  }
}

export class MergeNotFoundError extends Error {
  constructor(message = 'Customer merge target not found') {
    super(message)
    this.name = 'MergeNotFoundError'
  }
}

export class AlreadyMergedError extends Error {
  constructor(message = 'Customer already archived') {
    super(message)
    this.name = 'AlreadyMergedError'
  }
}

function parseSimilarity(matchMetadata: unknown): number | null {
  if (!matchMetadata || typeof matchMetadata !== 'object') return null
  const value = (matchMetadata as { similarityScore?: unknown }).similarityScore
  return typeof value === 'number' ? value : null
}

function rowCount(result: unknown): number {
  if (result && typeof result === 'object' && 'rowCount' in result) {
    const value = (result as { rowCount?: unknown }).rowCount
    if (typeof value === 'number') return value
  }
  if (Array.isArray(result)) return result.length
  return 0
}

async function updateCustomerReference(
  tx: {
    execute: (query: ReturnType<typeof sql>) => Promise<unknown>
  },
  tenantId: string,
  tableName: string,
  keepId: string,
  deleteId: string,
): Promise<number> {
  const result = await tx.execute(sql`
    UPDATE ${sql.raw(tableName)}
       SET customer_id = ${keepId}::uuid
     WHERE tenant_id = ${tenantId}::uuid
       AND customer_id = ${deleteId}::uuid
  `)
  return rowCount(result)
}

export async function scanForDuplicates(
  db: Db,
  tenantId: string,
): Promise<number> {
  const emailInsert = await db.execute(sql`
    INSERT INTO customer_merge_suggestions (
      tenant_id, primary_customer_id, duplicate_customer_id,
      reason, match_metadata, status
    )
    SELECT
      ${tenantId}::uuid,
      CASE WHEN a.id < b.id THEN a.id ELSE b.id END,
      CASE WHEN a.id < b.id THEN b.id ELSE a.id END,
      'email_match',
      jsonb_build_object('emailMatch', true, 'matchedFields', ARRAY['email'])::jsonb,
      'pending'
    FROM customers a
    JOIN customers b ON a.tenant_id = b.tenant_id
      AND lower(a.email) = lower(b.email)
      AND a.id <> b.id
      AND a.id < b.id
    WHERE a.tenant_id = ${tenantId}::uuid
      AND a.status = 'active'
      AND b.status = 'active'
      AND a.email IS NOT NULL
    ON CONFLICT (tenant_id, primary_customer_id, duplicate_customer_id) DO NOTHING
  `)

  const trigramInsert = await db.execute(sql`
    INSERT INTO customer_merge_suggestions (
      tenant_id, primary_customer_id, duplicate_customer_id,
      reason, match_metadata, status
    )
    SELECT
      ${tenantId}::uuid,
      CASE WHEN a.id < b.id THEN a.id ELSE b.id END,
      CASE WHEN a.id < b.id THEN b.id ELSE a.id END,
      'name_similarity',
      jsonb_build_object(
        'similarityScore', ROUND(similarity(
          coalesce(a.name,'') || ' ' || coalesce(a.company,''),
          coalesce(b.name,'') || ' ' || coalesce(b.company,'')
        )::numeric, 4),
        'matchedFields', ARRAY['name']
      )::jsonb,
      'pending'
    FROM customers a
    JOIN customers b ON a.tenant_id = b.tenant_id
      AND a.id <> b.id
      AND a.id < b.id
    WHERE a.tenant_id = ${tenantId}::uuid
      AND a.status = 'active'
      AND b.status = 'active'
      AND similarity(
        coalesce(a.name,'') || ' ' || coalesce(a.company,''),
        coalesce(b.name,'') || ' ' || coalesce(b.company,'')
      ) >= 0.85
      AND NOT EXISTS (
        SELECT 1
          FROM customer_merge_suggestions cms
         WHERE cms.tenant_id = ${tenantId}::uuid
           AND cms.primary_customer_id = CASE WHEN a.id < b.id THEN a.id ELSE b.id END
           AND cms.duplicate_customer_id = CASE WHEN a.id < b.id THEN b.id ELSE a.id END
      )
    ON CONFLICT (tenant_id, primary_customer_id, duplicate_customer_id) DO NOTHING
  `)

  return rowCount(emailInsert) + rowCount(trigramInsert)
}

export async function listMergeSuggestions(
  db: Db,
  tenantId: string,
  opts: { limit?: number } = {},
): Promise<MergeSuggestionsPage> {
  const limit = Math.min(opts.limit ?? 50, 100)
  const rows = await db.execute(sql`
    SELECT
      cms.id,
      cms.reason,
      cms.match_metadata,
      a.id AS customer_a_id,
      a.name AS customer_a_name,
      a.company AS customer_a_company,
      a.email AS customer_a_email,
      COALESCE((
        SELECT count(*)::int FROM invoices i
         WHERE i.tenant_id = ${tenantId}::uuid
           AND i.customer_id = a.id
      ), 0) AS customer_a_invoice_count,
      COALESCE((
        SELECT count(*)::int FROM projects p
         WHERE p.tenant_id = ${tenantId}::uuid
           AND p.customer_id = a.id
      ), 0) AS customer_a_project_count,
      b.id AS customer_b_id,
      b.name AS customer_b_name,
      b.company AS customer_b_company,
      b.email AS customer_b_email,
      COALESCE((
        SELECT count(*)::int FROM invoices i
         WHERE i.tenant_id = ${tenantId}::uuid
           AND i.customer_id = b.id
      ), 0) AS customer_b_invoice_count,
      COALESCE((
        SELECT count(*)::int FROM projects p
         WHERE p.tenant_id = ${tenantId}::uuid
           AND p.customer_id = b.id
      ), 0) AS customer_b_project_count
    FROM customer_merge_suggestions cms
    JOIN customers a ON a.id = cms.primary_customer_id
    JOIN customers b ON b.id = cms.duplicate_customer_id
    WHERE cms.tenant_id = ${tenantId}::uuid
      AND cms.status = 'pending'
    ORDER BY cms.created_at DESC, cms.id DESC
    LIMIT ${limit}
  `) as Array<{
    id: string
    reason: 'email_match' | 'name_similarity'
    match_metadata: unknown
    customer_a_id: string
    customer_a_name: string
    customer_a_company: string | null
    customer_a_email: string | null
    customer_a_invoice_count: number | string
    customer_a_project_count: number | string
    customer_b_id: string
    customer_b_name: string
    customer_b_company: string | null
    customer_b_email: string | null
    customer_b_invoice_count: number | string
    customer_b_project_count: number | string
  }>

  const totalRows = await db.execute(sql`
    SELECT count(*)::int AS total
      FROM customer_merge_suggestions
     WHERE tenant_id = ${tenantId}::uuid
       AND status = 'pending'
  `) as Array<{ total: number | string }>

  return {
    suggestions: rows.map((row) => ({
      id: row.id,
      reason: row.reason,
      similarity: parseSimilarity(row.match_metadata),
      customerA: {
        id: row.customer_a_id,
        name: row.customer_a_name,
        company: row.customer_a_company,
        email: row.customer_a_email,
        invoiceCount: Number(row.customer_a_invoice_count),
        projectCount: Number(row.customer_a_project_count),
      },
      customerB: {
        id: row.customer_b_id,
        name: row.customer_b_name,
        company: row.customer_b_company,
        email: row.customer_b_email,
        invoiceCount: Number(row.customer_b_invoice_count),
        projectCount: Number(row.customer_b_project_count),
      },
    })),
    total: Number(totalRows[0]?.total ?? 0),
  }
}

export async function dismissSuggestion(
  db: Db,
  tenantId: string,
  suggestionId: string,
  actorId: string,
): Promise<void> {
  await db
    .update(customerMergeSuggestions)
    .set({
      status: 'dismissed',
      resolvedBy: actorId,
      resolvedAt: new Date(),
    })
    .where(
      and(
        eq(customerMergeSuggestions.tenantId, tenantId),
        eq(customerMergeSuggestions.id, suggestionId),
        eq(customerMergeSuggestions.status, 'pending'),
      ),
    )
}

export async function mergeCustomers(
  db: Db,
  tenantId: string,
  actor: { userId: string; name: string | null; email: string | null },
  req: MergeRequest,
): Promise<MergeResult> {
  return db.transaction(async (tx) => {
    const rows = await tx
      .select({
        id: customers.id,
        status: customers.status,
        name: customers.name,
        email: customers.email,
      })
      .from(customers)
      .where(and(eq(customers.tenantId, tenantId), sql`${customers.id} IN (${req.keepId}::uuid, ${req.deleteId}::uuid)`))
      .for('update')

    const keepCustomer = rows.find((row) => row.id === req.keepId)
    const deleteCustomer = rows.find((row) => row.id === req.deleteId)

    if (!keepCustomer || !deleteCustomer) {
      throw new MergeNotFoundError()
    }
    if (deleteCustomer.status === 'archived') {
      throw new AlreadyMergedError()
    }

    const invoices = await updateCustomerReference(tx, tenantId, 'invoices', req.keepId, req.deleteId)
    const projects = await updateCustomerReference(tx, tenantId, 'projects', req.keepId, req.deleteId)
    const contacts = await updateCustomerReference(tx, tenantId, 'customer_contacts', req.keepId, req.deleteId)
    const tickets = await updateCustomerReference(tx, tenantId, 'tickets', req.keepId, req.deleteId)
    const portalUsers = await updateCustomerReference(tx, tenantId, 'customer_portal_users', req.keepId, req.deleteId)
    const activities = await updateCustomerReference(tx, tenantId, 'customer_activities', req.keepId, req.deleteId)

    await tx.execute(sql`
      UPDATE customer_communications
         SET customer_id = ${req.keepId}::uuid
       WHERE tenant_id = ${tenantId}::uuid
         AND customer_id = ${req.deleteId}::uuid
    `)

    await tx
      .update(customerMergeSuggestions)
      .set({
        status: 'merged',
        resolvedBy: actor.userId,
        resolvedAt: new Date(),
      })
      .where(and(
        eq(customerMergeSuggestions.tenantId, tenantId),
        sql`(
          (${customerMergeSuggestions.primaryCustomerId} = ${req.keepId}::uuid
            AND ${customerMergeSuggestions.duplicateCustomerId} = ${req.deleteId}::uuid)
          OR
          (${customerMergeSuggestions.primaryCustomerId} = ${req.deleteId}::uuid
            AND ${customerMergeSuggestions.duplicateCustomerId} = ${req.keepId}::uuid)
        )`,
      ))

    await tx.insert(auditLog).values({
      tenantId,
      actorId: actor.userId,
      actorType: 'user',
      entityType: 'customer',
      entityId: req.keepId,
      action: 'customer.merged',
      changes: null,
    })

    await tx
      .update(customers)
      .set({
        status: 'archived',
        updatedAt: new Date(),
      })
      .where(and(eq(customers.tenantId, tenantId), eq(customers.id, req.deleteId)))

    return {
      keptCustomerId: req.keepId,
      archivedCustomerId: req.deleteId,
      reassigned: {
        invoices,
        projects,
        contacts,
        tickets,
        portalUsers,
        activities,
      },
    }
  })
}