import { sql } from 'drizzle-orm'
import type { Db } from '../client'
import { getEnabledModuleIds } from './tenant-modules'

export interface DashboardModules {
  invoices: boolean
  tasks: boolean
  projects: boolean
  customers: boolean
  calendar: boolean
  time: boolean
}

export interface ActivityEvent {
  id: string
  type:
    | 'task.created'
    | 'task.completed'
    | 'invoice.sent'
    | 'invoice.paid'
    | 'ticket.opened'
    | 'project.started'
    | 'customer.added'
  actor_id: string
  actor_name: string
  actor_initials: string
  entity_id: string
  entity_name: string
  entity_url: string
  amount?: number
  currency?: string
  occurred_at: string
}

export interface CalendarEvent {
  id: string
  title: string
  start_time: string
  end_time: string
}

export interface TaskDue {
  id: string
  title: string
  project_name: string | null
  is_terminal: boolean
}

export interface ChecklistItem {
  key: 'business_info' | 'integration' | 'team_member' | 'first_customer' | 'first_invoice'
  label: string
  completed: boolean
  link: string
}

export interface DashboardResponse {
  kpis: {
    revenue_this_month: { amount: number; currency: string } | null
    open_invoices: { count: number; outstanding: number; currency: string } | null
    active_projects: { count: number } | null
    pending_tasks: { count: number } | null
    overdue: { tasks: number; invoices: number; total: number } | null
  }
  recent_activity: ActivityEvent[]
  upcoming: {
    events: CalendarEvent[]
    tasks_today: TaskDue[]
    tasks_tomorrow: TaskDue[]
  }
  setup_checklist: {
    shown: boolean
    dismissed: boolean
    items: ChecklistItem[]
  } | null
  modules: DashboardModules
}

export interface DashboardDataContext {
  tenantId: string
  userId: string
  role: string
  permissions: string[]
}

type AuditRow = {
  id: string
  user_id: string | null
  actor_name: string | null
  event_type: string
  entity_type: string | null
  entity_id: string | null
  entity_label: string | null
  metadata: Record<string, unknown> | null
  before_state: Record<string, unknown> | null
  after_state: Record<string, unknown> | null
  created_at: Date
}

function hasPermission(permissions: string[], permission: string): boolean {
  return permissions.includes(permission)
}

function toIso(value: Date | string): string {
  return value instanceof Date ? value.toISOString() : new Date(value).toISOString()
}

function getInitials(name: string): string {
  const parts = name.trim().split(/\s+/).filter(Boolean).slice(0, 2)
  if (parts.length === 0) return 'ZA'
  return parts.map((part) => part[0]!.toUpperCase()).join('')
}

function formatDateInZone(date: Date, timeZone: string): string {
  let formatter: Intl.DateTimeFormat
  try {
    formatter = new Intl.DateTimeFormat('en-CA', {
      timeZone,
      year: 'numeric',
      month: '2-digit',
      day: '2-digit',
    })
  } catch {
    formatter = new Intl.DateTimeFormat('en-CA', {
      timeZone: 'Asia/Jerusalem',
      year: 'numeric',
      month: '2-digit',
      day: '2-digit',
    })
  }
  const parts = formatter.formatToParts(date)
  const year = parts.find((part) => part.type === 'year')?.value ?? '1970'
  const month = parts.find((part) => part.type === 'month')?.value ?? '01'
  const day = parts.find((part) => part.type === 'day')?.value ?? '01'
  return `${year}-${month}-${day}`
}

function addDays(dateIso: string, days: number): string {
  const [year = 1970, month = 1, day = 1] = dateIso.split('-').map(Number)
  const next = new Date(Date.UTC(year, month - 1, day + days))
  return next.toISOString().slice(0, 10)
}

function mapModules(enabledIds: string[]): DashboardModules {
  return {
    invoices: enabledIds.includes('invoices'),
    tasks: enabledIds.includes('tasks'),
    projects: enabledIds.includes('projects'),
    customers: enabledIds.includes('customers'),
    calendar: enabledIds.includes('calendar'),
    time: enabledIds.includes('time_management'),
  }
}

async function getUserTimezone(db: Db, tenantId: string, userId: string): Promise<string> {
  const rows = await db.execute(sql`
    SELECT coalesce(
      (SELECT timezone FROM user_preferences WHERE tenant_id = ${tenantId}::uuid AND user_id = ${userId}::uuid),
      (SELECT default_timezone FROM tenants WHERE id = ${tenantId}::uuid),
      'Asia/Jerusalem'
    ) AS timezone
  `) as unknown as Array<{ timezone: string | null }>
  return rows[0]?.timezone ?? 'Asia/Jerusalem'
}

async function getRevenueThisMonth(db: Db, tenantId: string, timezone: string) {
  const rows = await db.execute(sql`
    SELECT
      coalesce(sum(total)::float8, 0) AS amount,
      coalesce(max(currency), 'ILS') AS currency
    FROM invoices
    WHERE tenant_id = ${tenantId}::uuid
      AND status = 'PAID'
      AND paid_at >= (date_trunc('month', timezone(${timezone}, now())) AT TIME ZONE ${timezone})
      AND paid_at < ((date_trunc('month', timezone(${timezone}, now())) + interval '1 month') AT TIME ZONE ${timezone})
  `) as unknown as Array<{ amount: number | null; currency: string | null }>
  return {
    amount: Number(rows[0]?.amount ?? 0),
    currency: rows[0]?.currency ?? 'ILS',
  }
}

async function getOpenInvoices(db: Db, tenantId: string) {
  const rows = await db.execute(sql`
    SELECT
      count(*)::int AS count,
      coalesce(sum(total)::float8, 0) AS outstanding,
      coalesce(max(currency), 'ILS') AS currency
    FROM invoices
    WHERE tenant_id = ${tenantId}::uuid
      AND status IN ('SENT', 'APPROVED', 'TAX_ISSUED')
  `) as unknown as Array<{ count: number | null; outstanding: number | null; currency: string | null }>
  return {
    count: Number(rows[0]?.count ?? 0),
    outstanding: Number(rows[0]?.outstanding ?? 0),
    currency: rows[0]?.currency ?? 'ILS',
  }
}

async function getActiveProjects(db: Db, tenantId: string) {
  const rows = await db.execute(sql`
    SELECT count(*)::int AS count
    FROM projects
    WHERE tenant_id = ${tenantId}::uuid
      AND status = 'active'
  `) as unknown as Array<{ count: number | null }>
  return { count: Number(rows[0]?.count ?? 0) }
}

async function getPendingTasks(db: Db, tenantId: string, userId: string) {
  const rows = await db.execute(sql`
    SELECT count(*)::int AS count
    FROM tasks t
    INNER JOIN task_statuses ts ON ts.id = t.status_id
    WHERE t.tenant_id = ${tenantId}::uuid
      AND t.assignee_id = ${userId}::uuid
      AND ts.name IN ('TODO', 'IN_PROGRESS')
  `) as unknown as Array<{ count: number | null }>
  return { count: Number(rows[0]?.count ?? 0) }
}

async function getOverdueCounts(
  db: Db,
  tenantId: string,
  userId: string,
  modules: DashboardModules,
  today: string,
) {
  const taskPromise = modules.tasks
    ? db.execute(sql`
        SELECT count(*)::int AS count
        FROM tasks t
        INNER JOIN task_statuses ts ON ts.id = t.status_id
        WHERE t.tenant_id = ${tenantId}::uuid
          AND t.assignee_id = ${userId}::uuid
          AND t.due_date < ${today}::date
          AND ts.is_terminal = false
      `).then((rows) => Number((rows as unknown as Array<{ count: number | null }>)[0]?.count ?? 0))
    : Promise.resolve(0)

  const invoicePromise = modules.invoices
    ? db.execute(sql`
        SELECT count(*)::int AS count
        FROM invoices
        WHERE tenant_id = ${tenantId}::uuid
          AND due_date < ${today}::date
          AND status IN ('SENT', 'APPROVED', 'TAX_ISSUED')
      `).then((rows) => Number((rows as unknown as Array<{ count: number | null }>)[0]?.count ?? 0))
    : Promise.resolve(0)

  const [tasks, invoices] = await Promise.all([taskPromise, invoicePromise])
  return { tasks, invoices, total: tasks + invoices }
}

function mapActivityRow(row: AuditRow): ActivityEvent | null {
  const actorName = row.actor_name ?? 'Zync (auto)'
  const entityName = row.entity_label ?? 'Untitled'
  const entityId = row.entity_id ?? ''
  const metadata = row.metadata ?? {}

  if (row.event_type === 'task.created' && row.entity_type === 'task') {
    return {
      id: row.id,
      type: 'task.created',
      actor_id: row.user_id ?? 'system',
      actor_name: actorName,
      actor_initials: getInitials(actorName),
      entity_id: entityId,
      entity_name: entityName,
      entity_url: `/tasks/${entityId}`,
      occurred_at: toIso(row.created_at),
    }
  }

  const nextStatus = String((row.after_state ?? metadata)?.status_name ?? (row.after_state ?? metadata)?.status ?? '')
  const terminal = Boolean((row.after_state ?? metadata)?.is_terminal)
  if (
    row.entity_type === 'task' &&
    (row.event_type === 'task.completed' ||
      (row.event_type === 'task.updated' && (terminal || nextStatus === 'DONE' || nextStatus === 'COMPLETED')))
  ) {
    return {
      id: row.id,
      type: 'task.completed',
      actor_id: row.user_id ?? 'system',
      actor_name: actorName,
      actor_initials: getInitials(actorName),
      entity_id: entityId,
      entity_name: entityName,
      entity_url: `/tasks/${entityId}`,
      occurred_at: toIso(row.created_at),
    }
  }

  if (row.event_type === 'invoice.sent' && row.entity_type === 'invoice') {
    return {
      id: row.id,
      type: 'invoice.sent',
      actor_id: row.user_id ?? 'system',
      actor_name: actorName,
      actor_initials: getInitials(actorName),
      entity_id: entityId,
      entity_name: entityName,
      entity_url: `/invoices/${entityId}`,
      occurred_at: toIso(row.created_at),
    }
  }

  if (
    row.entity_type === 'invoice' &&
    (row.event_type === 'invoice.payment_recorded' || row.event_type === 'invoice.paid')
  ) {
    const amount = Number(metadata.amount ?? metadata.total ?? 0)
    const currency = typeof metadata.currency === 'string' ? metadata.currency : undefined
    return {
      id: row.id,
      type: 'invoice.paid',
      actor_id: row.user_id ?? 'system',
      actor_name: actorName,
      actor_initials: getInitials(actorName),
      entity_id: entityId,
      entity_name: entityName,
      entity_url: `/invoices/${entityId}`,
      amount,
      currency,
      occurred_at: toIso(row.created_at),
    }
  }

  if (
    row.entity_type === 'ticket' &&
    (row.event_type === 'ticket.created' || row.event_type === 'ticket.created_via_portal')
  ) {
    return {
      id: row.id,
      type: 'ticket.opened',
      actor_id: row.user_id ?? 'system',
      actor_name: actorName,
      actor_initials: getInitials(actorName),
      entity_id: entityId,
      entity_name: entityName,
      entity_url: `/support/${entityId}`,
      occurred_at: toIso(row.created_at),
    }
  }

  if (row.event_type === 'project.created' && row.entity_type === 'project') {
    return {
      id: row.id,
      type: 'project.started',
      actor_id: row.user_id ?? 'system',
      actor_name: actorName,
      actor_initials: getInitials(actorName),
      entity_id: entityId,
      entity_name: entityName,
      entity_url: `/projects/${entityId}`,
      occurred_at: toIso(row.created_at),
    }
  }

  if (row.event_type === 'customer.created' && row.entity_type === 'customer') {
    return {
      id: row.id,
      type: 'customer.added',
      actor_id: row.user_id ?? 'system',
      actor_name: actorName,
      actor_initials: getInitials(actorName),
      entity_id: entityId,
      entity_name: entityName,
      entity_url: `/customers/${entityId}`,
      occurred_at: toIso(row.created_at),
    }
  }

  return null
}

function canSeeActivity(
  event: ActivityEvent,
  modules: DashboardModules,
  permissions: string[],
): boolean {
  switch (event.type) {
    case 'task.created':
    case 'task.completed':
      return modules.tasks && hasPermission(permissions, 'tasks:read')
    case 'invoice.sent':
    case 'invoice.paid':
      return modules.invoices && hasPermission(permissions, 'invoices:read')
    case 'project.started':
      return modules.projects && hasPermission(permissions, 'projects:read')
    case 'customer.added':
      return modules.customers && hasPermission(permissions, 'customers:read')
    case 'ticket.opened':
      return true
  }
}

async function getRecentActivity(
  db: Db,
  context: DashboardDataContext,
  modules: DashboardModules,
): Promise<ActivityEvent[]> {
  const rows = context.role === 'CONTRACTOR'
    ? await db.execute(sql`
        SELECT
          tal.id,
          tal.user_id,
          tal.actor_name,
          tal.event_type,
          tal.entity_type,
          tal.entity_id,
          tal.entity_label,
          tal.metadata,
          tal.before_state,
          tal.after_state,
          tal.created_at
        FROM tenant_audit_log tal
        INNER JOIN tasks t ON t.id = tal.entity_id
        WHERE tal.tenant_id = ${context.tenantId}::uuid
          AND t.tenant_id = ${context.tenantId}::uuid
          AND tal.entity_type = 'task'
          AND t.assignee_id = ${context.userId}::uuid
        ORDER BY tal.created_at DESC
        LIMIT 80
      `)
    : await db.execute(sql`
        SELECT
          id,
          user_id,
          actor_name,
          event_type,
          entity_type,
          entity_id,
          entity_label,
          metadata,
          before_state,
          after_state,
          created_at
        FROM tenant_audit_log
        WHERE tenant_id = ${context.tenantId}::uuid
        ORDER BY created_at DESC
        LIMIT 80
      `)

  return (rows as unknown as AuditRow[])
    .map(mapActivityRow)
    .filter((event): event is ActivityEvent => event !== null)
    .filter((event) => canSeeActivity(event, modules, context.permissions))
    .slice(0, 30)
}

async function getCalendarEvents(
  db: Db,
  tenantId: string,
  userId: string,
  timezone: string,
): Promise<CalendarEvent[]> {
  const rows = await db.execute(sql`
    SELECT id, title, start_at, end_at
    FROM calendar_events
    WHERE tenant_id = ${tenantId}::uuid
      AND created_by = ${userId}::uuid
      AND start_at >= (date_trunc('day', timezone(${timezone}, now())) AT TIME ZONE ${timezone})
      AND start_at < ((date_trunc('day', timezone(${timezone}, now())) + interval '1 day') AT TIME ZONE ${timezone})
    ORDER BY start_at ASC
    LIMIT 6
  `) as unknown as Array<{ id: string; title: string; start_at: Date; end_at: Date }>

  return rows.slice(0, 5).map((row) => ({
    id: row.id,
    title: row.title,
    start_time: toIso(row.start_at),
    end_time: toIso(row.end_at),
  }))
}

async function getDueTasks(
  db: Db,
  tenantId: string,
  userId: string,
  dueDate: string,
): Promise<TaskDue[]> {
  const rows = await db.execute(sql`
    SELECT
      t.id,
      t.title,
      p.name AS project_name,
      ts.is_terminal
    FROM tasks t
    INNER JOIN task_statuses ts ON ts.id = t.status_id
    LEFT JOIN projects p ON p.id = t.project_id
    WHERE t.tenant_id = ${tenantId}::uuid
      AND t.assignee_id = ${userId}::uuid
      AND t.due_date = ${dueDate}::date
      AND ts.is_terminal = false
    ORDER BY t.title ASC
  `) as unknown as Array<{ id: string; title: string; project_name: string | null; is_terminal: boolean }>

  return rows.map((row) => ({
    id: row.id,
    title: row.title,
    project_name: row.project_name,
    is_terminal: row.is_terminal,
  }))
}

async function getSetupChecklist(
  db: Db,
  tenantId: string,
  role: string,
): Promise<DashboardResponse['setup_checklist']> {
  if (role !== 'OWNER' && role !== 'ADMIN') return null

  const rows = await db.execute(sql`
    SELECT
      name,
      checklist_dismissed_at,
      onboarding_completed,
      settings->>'address' AS address
    FROM tenants
    WHERE id = ${tenantId}::uuid
    LIMIT 1
  `) as unknown as Array<{
    name: string | null
    checklist_dismissed_at: Date | null
    onboarding_completed: boolean
    address: string | null
  }>

  const tenant = rows[0]
  if (!tenant) return null

  const [
    integrationCountRows,
    memberCountRows,
    customerCountRows,
    invoiceCountRows,
  ] = await Promise.all([
    db.execute(sql`
      SELECT (
        (SELECT count(*) FROM calendar_connections WHERE tenant_id = ${tenantId}::uuid)
        + (SELECT count(*) FROM scheduling_connections WHERE tenant_id = ${tenantId}::uuid)
      )::int AS count
    `) as Promise<unknown>,
    db.execute(sql`
      SELECT count(*)::int AS count
      FROM tenant_memberships
      WHERE tenant_id = ${tenantId}::uuid
        AND status = 'active'
    `) as Promise<unknown>,
    db.execute(sql`
      SELECT count(*)::int AS count
      FROM customers
      WHERE tenant_id = ${tenantId}::uuid
    `) as Promise<unknown>,
    db.execute(sql`
      SELECT count(*)::int AS count
      FROM invoices
      WHERE tenant_id = ${tenantId}::uuid
        AND status <> 'DRAFT'
    `) as Promise<unknown>,
  ])

  const integrationCount = Number((integrationCountRows as Array<{ count: number | null }>)[0]?.count ?? 0)
  const memberCount = Number((memberCountRows as Array<{ count: number | null }>)[0]?.count ?? 0)
  const customerCount = Number((customerCountRows as Array<{ count: number | null }>)[0]?.count ?? 0)
  const invoiceCount = Number((invoiceCountRows as Array<{ count: number | null }>)[0]?.count ?? 0)

  const items: ChecklistItem[] = [
    {
      key: 'business_info',
      label: 'Add your business info',
      completed: Boolean(tenant.name && tenant.address),
      link: '/settings/business',
    },
    {
      key: 'integration',
      label: 'Set up your first integration',
      completed: integrationCount > 0,
      link: '/settings/integrations',
    },
    {
      key: 'team_member',
      label: 'Invite a team member',
      completed: memberCount > 1,
      link: '/settings/users',
    },
    {
      key: 'first_customer',
      label: 'Create your first customer',
      completed: customerCount > 0,
      link: '/customers/new',
    },
    {
      key: 'first_invoice',
      label: 'Send your first invoice',
      completed: invoiceCount > 0,
      link: '/invoices/new',
    },
  ]

  return {
    shown: tenant.checklist_dismissed_at == null && tenant.onboarding_completed === false,
    dismissed: tenant.checklist_dismissed_at != null,
    items,
  }
}

export async function getDashboardData(
  db: Db,
  context: DashboardDataContext,
): Promise<DashboardResponse> {
  const enabledModuleIds = await getEnabledModuleIds(db, context.tenantId)
  const modules = mapModules(enabledModuleIds)
  const timezone = await getUserTimezone(db, context.tenantId, context.userId)
  const today = formatDateInZone(new Date(), timezone)
  const tomorrow = addDays(today, 1)

  const canReadInvoices = modules.invoices && hasPermission(context.permissions, 'invoices:read')
  const canReadTasks = modules.tasks && hasPermission(context.permissions, 'tasks:read')
  const canReadProjects = modules.projects && hasPermission(context.permissions, 'projects:read')
  const canReadCalendar = modules.calendar && hasPermission(context.permissions, 'calendar:read')

  const [
    revenueThisMonth,
    openInvoices,
    activeProjects,
    pendingTasks,
    overdue,
    recentActivity,
    calendarEvents,
    tasksToday,
    tasksTomorrow,
    setupChecklist,
  ] = await Promise.all([
    canReadInvoices ? getRevenueThisMonth(db, context.tenantId, timezone) : Promise.resolve(null),
    canReadInvoices ? getOpenInvoices(db, context.tenantId) : Promise.resolve(null),
    canReadProjects ? getActiveProjects(db, context.tenantId) : Promise.resolve(null),
    canReadTasks ? getPendingTasks(db, context.tenantId, context.userId) : Promise.resolve(null),
    canReadInvoices || canReadTasks
      ? getOverdueCounts(
          db,
          context.tenantId,
          context.userId,
          { ...modules, invoices: canReadInvoices, tasks: canReadTasks },
          today,
        )
      : Promise.resolve(null),
    getRecentActivity(db, context, modules),
    canReadCalendar ? getCalendarEvents(db, context.tenantId, context.userId, timezone) : Promise.resolve([]),
    canReadTasks ? getDueTasks(db, context.tenantId, context.userId, today) : Promise.resolve([]),
    canReadTasks ? getDueTasks(db, context.tenantId, context.userId, tomorrow) : Promise.resolve([]),
    getSetupChecklist(db, context.tenantId, context.role),
  ])

  return {
    kpis: {
      revenue_this_month: revenueThisMonth,
      open_invoices: openInvoices,
      active_projects: activeProjects,
      pending_tasks: pendingTasks,
      overdue,
    },
    recent_activity: recentActivity,
    upcoming: {
      events: calendarEvents,
      tasks_today: tasksToday,
      tasks_tomorrow: tasksTomorrow,
    },
    setup_checklist: setupChecklist,
    modules,
  }
}

export async function dismissChecklist(db: Db, tenantId: string): Promise<void> {
  await db.execute(sql`
    UPDATE tenants
    SET checklist_dismissed_at = now()
    WHERE id = ${tenantId}::uuid
      AND checklist_dismissed_at IS NULL
  `)
}