import 'server-only'

import { createAdminClient } from '@/lib/supabase/admin'
import { isMissingColumn } from '@/lib/db-compat'
import { VERIFICATION_LEVELS } from '@/lib/domain/company'
import { emailPattern } from '@/lib/like'
import type {
  Company, CompanyAuditEvent, CompanyClaim, CompanyMembership, CompanyRole,
  CompanyVerificationStatus,
} from '@/types/db'

/**
 * Company verification data access (Tasks 88–94).
 *
 * Every read tolerates migration 0013 not being applied yet (project rule
 * since Tasks 39–48): a missing table reads as "no company", never as a 500.
 */

/** Postgres/PostgREST error for a table that does not exist yet. */
function tableMissing(error: { message?: string; code?: string } | null | undefined): boolean {
  if (!error) return false
  return (
    error.code === '42P01' ||
    error.code === 'PGRST205' ||
    /does not exist|schema cache|could not find the table/i.test(error.message ?? '')
  )
}

export interface CompanyContext {
  /** False when migration 0013 is not applied — pages show the "needs migration" note. */
  available: boolean
  company: Company | null
  membership: CompanyMembership | null
  claim: CompanyClaim | null
}

/** What THIS user's company situation is: the newest claim and its company. */
export async function getCompanyContext(userId: string): Promise<CompanyContext> {
  const admin = createAdminClient()
  const claims = await admin
    .from('company_claims')
    .select('*')
    .eq('user_id', userId)
    .order('created_at', { ascending: false })
    .limit(1)
  if (tableMissing(claims.error)) {
    return { available: false, company: null, membership: null, claim: null }
  }
  const claim = ((claims.data ?? [])[0] ?? null) as CompanyClaim | null

  // An approved membership wins over an older claim (e.g. seeded owners).
  const memberships = await admin
    .from('company_memberships')
    .select('*')
    .eq('user_id', userId)
    .eq('status', 'approved')
    .order('created_at', { ascending: false })
    .limit(1)
  const approved = ((memberships.data ?? [])[0] ?? null) as CompanyMembership | null

  const companyId = approved?.company_id ?? claim?.company_id ?? null
  if (!companyId) return { available: true, company: null, membership: null, claim }

  const [{ data: company }, { data: membership }] = await Promise.all([
    admin.from('companies').select('*').eq('id', companyId).maybeSingle(),
    admin
      .from('company_memberships')
      .select('*')
      .eq('user_id', userId)
      .eq('company_id', companyId)
      .maybeSingle(),
  ])
  return {
    available: true,
    company: (company ?? null) as Company | null,
    membership: (membership ?? approved ?? null) as CompanyMembership | null,
    claim: claim && claim.company_id === companyId ? claim : approved ? null : claim,
  }
}

export async function getCompany(companyId: string): Promise<Company | null> {
  const admin = createAdminClient()
  const { data } = await admin.from('companies').select('*').eq('id', companyId).maybeSingle()
  return (data ?? null) as Company | null
}

/** Does the company already have an approved owner/admin? (§17 routing, §24 first-owner rule) */
export async function companyHasAdmin(companyId: string): Promise<boolean> {
  const admin = createAdminClient()
  const { count } = await admin
    .from('company_memberships')
    .select('id', { count: 'exact', head: true })
    .eq('company_id', companyId)
    .eq('status', 'approved')
    .in('role', ['company_owner', 'company_admin'])
  return (count ?? 0) > 0
}

/** Article §21 audit log — every sensitive company action lands here. */
export async function logCompanyEvent(params: {
  companyId: string | null
  actorUserId?: string | null
  actorLabel: string
  eventType: string
  oldValue?: Record<string, unknown> | null
  newValue?: Record<string, unknown> | null
  ip?: string | null
}) {
  const admin = createAdminClient()
  const { error } = await admin.from('company_audit_log').insert({
    company_id: params.companyId,
    actor_user_id: params.actorUserId ?? null,
    actor_label: params.actorLabel,
    event_type: params.eventType,
    old_value: params.oldValue ?? null,
    new_value: params.newValue ?? null,
    ip_address: params.ip ?? null,
  })
  if (error && !tableMissing(error)) console.error('company audit failed', error.message)
}

export async function listCompanyAudit(companyId: string, limit = 100): Promise<CompanyAuditEvent[]> {
  const admin = createAdminClient()
  const { data } = await admin
    .from('company_audit_log')
    .select('*')
    .eq('company_id', companyId)
    .order('created_at', { ascending: false })
    .limit(limit)
  return (data ?? []) as CompanyAuditEvent[]
}

/** Raise a company's verification level; never lowers it. */
export async function raiseVerification(companyId: string, to: CompanyVerificationStatus) {
  const admin = createAdminClient()
  const { data } = await admin
    .from('companies')
    .select('verification_status')
    .eq('id', companyId)
    .maybeSingle()
  const current = (data?.verification_status ?? 'unverified') as CompanyVerificationStatus
  if (VERIFICATION_LEVELS[to] <= VERIFICATION_LEVELS[current]) return
  await admin
    .from('companies')
    .update({
      verification_status: to,
      verification_level: VERIFICATION_LEVELS[to],
      updated_at: new Date().toISOString(),
    })
    .eq('id', companyId)
}

/**
 * Approve a claim: membership → approved with the requested role (or owner
 * when the company has nobody yet — §24, only via corporate email / manual
 * review), claim → approved, verification raised, audit written.
 */
export async function approveClaim(params: {
  claim: CompanyClaim
  approverLabel: string
  approverUserId?: string | null
  approverEmail?: string | null
  ip?: string | null
  /** How this approval happened — decides the level it proves (§23). */
  via: 'corporate_email' | 'company_admin' | 'manual'
}): Promise<{ ok: true; role: CompanyRole } | { ok: false; error: string }> {
  const { claim } = params
  const admin = createAdminClient()
  const hasAdmin = await companyHasAdmin(claim.company_id)

  // §24: the first person to represent a company becomes its owner, but only
  // through the stronger paths. An in-app admin approval cannot mint an owner.
  let role: CompanyRole = claim.requested_role
  if (!hasAdmin) {
    if (params.via === 'company_admin') {
      return {
        ok: false,
        error: 'First company owner must be approved by corporate email or manual review.',
      }
    }
    role = 'company_owner'
  }

  const now = new Date().toISOString()
  await admin.from('company_memberships').upsert(
    {
      user_id: claim.user_id,
      company_id: claim.company_id,
      role,
      status: 'approved',
      approved_by: params.approverLabel,
      approved_at: now,
      revoked_by: null,
      revoked_at: null,
    },
    { onConflict: 'user_id,company_id' },
  )
  await admin
    .from('company_claims')
    .update({
      claim_status: 'approved',
      approver_name: params.approverLabel,
      approver_email: params.approverEmail ?? null,
      approver_ip: params.ip ?? null,
      decided_at: now,
      updated_at: now,
    })
    .eq('id', claim.id)

  await raiseVerification(
    claim.company_id,
    params.via === 'manual'
      ? 'manual_verified'
      : params.via === 'company_admin'
        ? 'company_admin_approved'
        : 'corporate_contact_confirmed',
  )
  await admin
    .from('companies')
    .update({ is_placeholder: false, updated_at: now })
    .eq('id', claim.company_id)
    .eq('is_placeholder', true)
    .then(() => undefined)

  await logCompanyEvent({
    companyId: claim.company_id,
    actorUserId: params.approverUserId ?? null,
    actorLabel: params.approverLabel,
    eventType: 'claim_approved',
    newValue: { claim_id: claim.id, user_id: claim.user_id, role, via: params.via },
    ip: params.ip,
  })
  return { ok: true, role }
}

export async function denyClaim(params: {
  claim: CompanyClaim
  approverLabel: string
  approverUserId?: string | null
  approverEmail?: string | null
  ip?: string | null
  reported?: boolean
}) {
  const { claim } = params
  const admin = createAdminClient()
  const now = new Date().toISOString()
  await admin
    .from('company_claims')
    .update({
      claim_status: params.reported ? 'admin_review_required' : 'rejected',
      review_reason: params.reported ? 'reported_unknown_person' : claim.review_reason,
      approver_name: params.approverLabel,
      approver_email: params.approverEmail ?? null,
      approver_ip: params.ip ?? null,
      decided_at: now,
      updated_at: now,
    })
    .eq('id', claim.id)
  await admin
    .from('company_memberships')
    .update({ status: 'rejected' })
    .eq('user_id', claim.user_id)
    .eq('company_id', claim.company_id)
    .eq('status', 'pending')
  await logCompanyEvent({
    companyId: claim.company_id,
    actorUserId: params.approverUserId ?? null,
    actorLabel: params.approverLabel,
    eventType: params.reported ? 'claim_reported' : 'claim_denied',
    newValue: { claim_id: claim.id, user_id: claim.user_id },
    ip: params.ip,
  })
}

/** Members + their profiles for the Company Access panel (§28). */
export async function listCompanyMembers(companyId: string) {
  const admin = createAdminClient()
  const [{ data: memberships }, { data: claims }] = await Promise.all([
    admin.from('company_memberships').select('*').eq('company_id', companyId).order('created_at'),
    admin
      .from('company_claims')
      .select('*')
      .eq('company_id', companyId)
      .order('created_at', { ascending: false }),
  ])
  const rows = (memberships ?? []) as CompanyMembership[]
  const claimRows = (claims ?? []) as CompanyClaim[]
  const userIds = [...new Set([...rows.map((m) => m.user_id), ...claimRows.map((c) => c.user_id)])]
  const { data: profiles } =
    userIds.length > 0
      ? await admin.from('profiles').select('id, full_name, email, phone').in('id', userIds)
      : { data: [] }
  const profileMap = new Map(
    ((profiles ?? []) as { id: string; full_name: string; email: string; phone: string | null }[]).map(
      (p) => [p.id, p],
    ),
  )
  return { memberships: rows, claims: claimRows, profiles: profileMap }
}

/** Whether the missing-column helper applies to a company-table error. */
export function isCompanySchemaMissing(error: { message?: string; code?: string } | null | undefined) {
  return tableMissing(error) || isMissingColumn(error, 'company_id', 'companies')
}

/* ---------------- Corporate contact approvals in-app (Task 83) ---------------- */

export interface AwaitingApproval {
  claim: CompanyClaim
  company: Company
  requester: { name: string; email: string; phone: string | null }
}

/**
 * Requests waiting for THIS signed-in person because they ARE the corporate
 * contact: their account email equals the company's corporate email (or is
 * one of its notification recipients). Nash (2026-09-08): "If I'm logged in
 * with an email that is proven to be the email of the company that has a
 * pending request for verification of an agent, should I have an approve or
 * deny ability?" — yes. Same §8 Method 2 decision as the emailed link, made
 * from inside the app instead of from the mailbox.
 *
 * Only claims still `pending_corporate_approval` are returned. Tolerates
 * migration 0013 not being applied (empty list).
 */
export async function listClaimsAwaitingMyApproval(email: string): Promise<AwaitingApproval[]> {
  const admin = createAdminClient()
  const mine = email.trim().toLowerCase()
  if (!mine) return []
  const companies = await admin
    .from('companies')
    .select('*')
    .is('merged_into', null)
    // Exact email: an unescaped `_` would list another company's pending
    // requests — names, emails, phones — to the wrong person.
    .or(`corporate_email.ilike.${emailPattern(mine)},notification_recipients.cs.{${mine}}`)
  if (tableMissing(companies.error) || !companies.data?.length) return []
  const byId = new Map((companies.data as Company[]).map((c) => [c.id, c]))

  const { data: claims } = await admin
    .from('company_claims')
    .select('*')
    .in('company_id', [...byId.keys()])
    .eq('claim_status', 'pending_corporate_approval')
    .gt('expires_at', new Date().toISOString())
    .order('created_at', { ascending: false })
  const rows = (claims ?? []) as CompanyClaim[]
  if (rows.length === 0) return []

  const { data: profiles } = await admin
    .from('profiles')
    .select('id, full_name, email, phone')
    .in('id', [...new Set(rows.map((c) => c.user_id))])
  const people = new Map(
    ((profiles ?? []) as { id: string; full_name: string; email: string; phone: string | null }[]).map((p) => [p.id, p]),
  )
  return rows
    .filter((c) => byId.has(c.company_id))
    .map((c) => {
      const p = people.get(c.user_id)
      return {
        claim: c,
        company: byId.get(c.company_id)!,
        requester: { name: p?.full_name || '—', email: p?.email || '—', phone: p?.phone ?? null },
      }
    })
}

/** True when this email is the corporate contact on file for the company. */
export function isCorporateContact(email: string, company: Company): boolean {
  const mine = email.trim().toLowerCase()
  if (!mine) return false
  if (company.corporate_email?.trim().toLowerCase() === mine) return true
  return company.notification_recipients.some((r) => r.trim().toLowerCase() === mine)
}