/**
 * Calendar query helpers — calendar-module.
 *
 * All helpers are tenant-scoped: every statement carries a tenant_id WHERE clause.
 * Routes MUST NOT import raw Drizzle tables — they call these helpers.
 *
 * Token bytes (access_token, refresh_token, api_key) are Uint8Array at this layer.
 * Encryption/decryption is done by the caller (route/sync engine) using @zync/calendar crypto.
 */
import { and, eq, gte, lte, sql } from 'drizzle-orm'
import type { Db } from '../client'
import {
  assertTenantOwnsOrThrow,
  assertTenantOwnsCustomer,
  assertTenantOwnsProject,
} from './tenant-guards'
import {
  calendarEvents,
  calendarConnections,
  schedulingConnections,
} from '../schema/calendar'
import { tasks } from '../schema/tasks'
import { customerContacts } from '../schema/customers'
import { tenantMemberships } from '../schema/rbac'
import { projectMilestones } from '../schema/project-milestones'
import type {
  NewCalendarEvent,
  NewCalendarConnection,
  NewSchedulingConnection,
  CalendarEventRow,
  CalendarConnectionRow,
  SchedulingConnectionRow,
} from '../schema/calendar'

// ── calendar_events ────────────────────────────────────────────────────────────

/**
 * List calendar_events rows within a date range for a tenant.
 * Returns rows only — caller merges with virtual task events.
 */
export async function listCalendarEvents(
  db: Db,
  tenantId: string,
  start: Date,
  end: Date,
): Promise<CalendarEventRow[]> {
  return db
    .select()
    .from(calendarEvents)
    .where(
      and(
        eq(calendarEvents.tenantId, tenantId),
        // events that overlap the range: start_at <= rangeEnd AND end_at >= rangeStart
        lte(calendarEvents.startAt, end),
        gte(calendarEvents.endAt, start),
      ),
    )
    .orderBy(calendarEvents.startAt)
}

/**
 * List task rows with due_date in a range (for virtual calendar events).
 * Columns selected: id, tenant_id, title, due_date, assignee_id, project_id.
 */
export async function listTasksDueInRange(
  db: Db,
  tenantId: string,
  start: Date,
  end: Date,
): Promise<Array<{
  id: string
  tenantId: string
  title: string
  dueDate: string
  assigneeId: string | null
  projectId: string | null
}>> {
  const startDate = start.toISOString().slice(0, 10)
  const endDate = end.toISOString().slice(0, 10)

  const rows = await db
    .select({
      id: tasks.id,
      tenantId: tasks.tenantId,
      title: tasks.title,
      dueDate: tasks.dueDate,
      assigneeId: tasks.assigneeId,
      projectId: tasks.projectId,
    })
    .from(tasks)
    .where(
      and(
        eq(tasks.tenantId, tenantId),
        gte(tasks.dueDate, startDate),
        lte(tasks.dueDate, endDate),
      ),
    )
    .orderBy(tasks.dueDate)

  return rows
    .filter((r) => r.dueDate != null)
    .map((r) => ({
      id: r.id,
      tenantId: r.tenantId,
      title: r.title,
      dueDate: r.dueDate!,
      assigneeId: r.assigneeId ?? null,
      projectId: r.projectId ?? null,
    }))
}

export async function listProjectMilestonesDueInRange(
  db: Db,
  tenantId: string,
  start: Date,
  end: Date,
): Promise<Array<{
  id: string
  tenantId: string
  projectId: string
  title: string
  dueDate: string
}>> {
  const startDate = start.toISOString().slice(0, 10)
  const endDate = end.toISOString().slice(0, 10)

  const rows = await db
    .select({
      id: projectMilestones.id,
      tenantId: projectMilestones.tenantId,
      projectId: projectMilestones.projectId,
      title: projectMilestones.name,
      dueDate: projectMilestones.dueDate,
    })
    .from(projectMilestones)
    .where(
      and(
        eq(projectMilestones.tenantId, tenantId),
        gte(projectMilestones.dueDate, startDate),
        lte(projectMilestones.dueDate, endDate),
      ),
    )
    .orderBy(projectMilestones.dueDate)

  return rows
    .filter((row) => row.dueDate != null)
    .map((row) => ({
      id: row.id,
      tenantId: row.tenantId,
      projectId: row.projectId,
      title: row.title,
      dueDate: row.dueDate!,
    }))
}

export async function getCalendarEvent(
  db: Db,
  tenantId: string,
  id: string,
): Promise<CalendarEventRow | undefined> {
  const rows = await db
    .select()
    .from(calendarEvents)
    .where(and(eq(calendarEvents.tenantId, tenantId), eq(calendarEvents.id, id)))
    .limit(1)
  return rows[0]
}

export async function createCalendarEvent(
  db: Db,
  data: NewCalendarEvent,
): Promise<CalendarEventRow> {
  assertTenantOwnsOrThrow(
    'customerId',
    await assertTenantOwnsCustomer(db, data.tenantId, data.customerId),
  )
  assertTenantOwnsOrThrow(
    'projectId',
    await assertTenantOwnsProject(db, data.tenantId, data.projectId),
  )

  const rows = await db.insert(calendarEvents).values(data).returning()
  return rows[0]!
}

export async function updateCalendarEvent(
  db: Db,
  tenantId: string,
  id: string,
  data: Partial<NewCalendarEvent>,
): Promise<CalendarEventRow | undefined> {
  if (data.customerId !== undefined) {
    assertTenantOwnsOrThrow(
      'customerId',
      await assertTenantOwnsCustomer(db, tenantId, data.customerId),
    )
  }
  if (data.projectId !== undefined) {
    assertTenantOwnsOrThrow(
      'projectId',
      await assertTenantOwnsProject(db, tenantId, data.projectId),
    )
  }

  const rows = await db
    .update(calendarEvents)
    .set({ ...data, updatedAt: new Date() })
    .where(and(eq(calendarEvents.tenantId, tenantId), eq(calendarEvents.id, id)))
    .returning()
  return rows[0]
}

export async function deleteCalendarEvent(
  db: Db,
  tenantId: string,
  id: string,
): Promise<boolean> {
  const rows = await db
    .delete(calendarEvents)
    .where(and(eq(calendarEvents.tenantId, tenantId), eq(calendarEvents.id, id)))
    .returning({ id: calendarEvents.id })
  return rows.length > 0
}

/**
 * Upsert an external event keyed on (tenant_id, source, external_id).
 * Used by sync webhook handlers and cron sync.
 * Never overwrites task/project-sourced Zync events.
 */
export async function upsertExternalCalendarEvent(
  db: Db,
  data: NewCalendarEvent & { externalId: string },
): Promise<CalendarEventRow> {
  const rows = await db
    .insert(calendarEvents)
    .values(data)
    .onConflictDoUpdate({
      target: [calendarEvents.tenantId, calendarEvents.source, calendarEvents.externalId],
      set: {
        title: data.title,
        description: data.description,
        startAt: data.startAt,
        endAt: data.endAt,
        allDay: data.allDay,
        location: data.location,
        externalCalendarId: data.externalCalendarId,
        syncedAt: data.syncedAt,
        syncStatus: data.syncStatus,
        updatedAt: new Date(),
      },
      where: sql`calendar_events.source NOT IN ('task','project')`,
    })
    .returning()
  return rows[0]!
}

// ── calendar_connections ───────────────────────────────────────────────────────

export async function listCalendarConnections(
  db: Db,
  tenantId: string,
  userId: string,
): Promise<CalendarConnectionRow[]> {
  return db
    .select()
    .from(calendarConnections)
    .where(
      and(
        eq(calendarConnections.tenantId, tenantId),
        eq(calendarConnections.userId, userId),
      ),
    )
}

export async function getCalendarConnection(
  db: Db,
  tenantId: string,
  id: string,
): Promise<CalendarConnectionRow | undefined> {
  const rows = await db
    .select()
    .from(calendarConnections)
    .where(
      and(
        eq(calendarConnections.tenantId, tenantId),
        eq(calendarConnections.id, id),
      ),
    )
    .limit(1)
  return rows[0]
}

/**
 * Cross-tenant lookup by id only — used by sync webhook handlers where the
 * tenantId is not known until after the connection row is loaded.
 * Security: callers MUST authenticate via other means (KV channel map, HMAC) first.
 */
export async function getCalendarConnectionById(
  db: Db,
  id: string,
): Promise<CalendarConnectionRow | undefined> {
  const rows = await db
    .select()
    .from(calendarConnections)
    .where(eq(calendarConnections.id, id))
    .limit(1)
  return rows[0]
}

export async function listSyncEnabledConnections(
  db: Db,
): Promise<CalendarConnectionRow[]> {
  return db
    .select()
    .from(calendarConnections)
    .where(eq(calendarConnections.syncEnabled, true))
}

export async function upsertCalendarConnection(
  db: Db,
  data: NewCalendarConnection,
): Promise<CalendarConnectionRow> {
  const rows = await db
    .insert(calendarConnections)
    .values(data)
    .onConflictDoUpdate({
      target: [calendarConnections.tenantId, calendarConnections.userId, calendarConnections.provider],
      set: {
        externalUserId: data.externalUserId,
        connectedEmail: data.connectedEmail,
        accessToken: data.accessToken,
        refreshToken: data.refreshToken,
        tokenExpiresAt: data.tokenExpiresAt,
        selectedCalendarId: data.selectedCalendarId,
        selectedCalendarName: data.selectedCalendarName,
        syncDirection: data.syncDirection,
        syncTaskDueDates: data.syncTaskDueDates,
        syncManualEvents: data.syncManualEvents,
        syncCustomerMeetings: data.syncCustomerMeetings,
        syncEnabled: data.syncEnabled,
        status: data.status,
        lastSyncError: data.lastSyncError,
      },
    })
    .returning()
  return rows[0]!
}

export async function updateCalendarConnectionTokens(
  db: Db,
  tenantId: string,
  id: string,
  accessToken: Uint8Array,
  tokenExpiresAt: Date,
): Promise<void> {
  await db
    .update(calendarConnections)
    .set({ accessToken, tokenExpiresAt })
    .where(and(eq(calendarConnections.tenantId, tenantId), eq(calendarConnections.id, id)))
}

export async function updateCalendarConnectionLastSynced(
  db: Db,
  tenantId: string,
  id: string,
): Promise<void> {
  await db
    .update(calendarConnections)
    .set({ lastSyncedAt: new Date() })
    .where(and(eq(calendarConnections.tenantId, tenantId), eq(calendarConnections.id, id)))
}

export async function updateCalendarConnection(
  db: Db,
  tenantId: string,
  id: string,
  data: Partial<Pick<NewCalendarConnection, 'selectedCalendarId' | 'syncEnabled'>>,
): Promise<CalendarConnectionRow | undefined> {
  const rows = await db
    .update(calendarConnections)
    .set(data)
    .where(and(eq(calendarConnections.tenantId, tenantId), eq(calendarConnections.id, id)))
    .returning()
  return rows[0]
}

export async function deleteCalendarConnection(
  db: Db,
  tenantId: string,
  id: string,
): Promise<CalendarConnectionRow | undefined> {
  const rows = await db
    .delete(calendarConnections)
    .where(
      and(
        eq(calendarConnections.tenantId, tenantId),
        eq(calendarConnections.id, id),
      ),
    )
    .returning()
  return rows[0]
}

// ── scheduling_connections ─────────────────────────────────────────────────────

export async function listSchedulingConnections(
  db: Db,
  tenantId: string,
): Promise<SchedulingConnectionRow[]> {
  return db
    .select()
    .from(schedulingConnections)
    .where(eq(schedulingConnections.tenantId, tenantId))
}

export async function getSchedulingConnection(
  db: Db,
  tenantId: string,
  provider: string,
): Promise<SchedulingConnectionRow | undefined> {
  const rows = await db
    .select()
    .from(schedulingConnections)
    .where(
      and(
        eq(schedulingConnections.tenantId, tenantId),
        eq(schedulingConnections.provider, provider),
      ),
    )
    .limit(1)
  return rows[0]
}

export async function getSchedulingConnectionById(
  db: Db,
  tenantId: string,
  id: string,
): Promise<SchedulingConnectionRow | undefined> {
  const rows = await db
    .select()
    .from(schedulingConnections)
    .where(
      and(
        eq(schedulingConnections.tenantId, tenantId),
        eq(schedulingConnections.id, id),
      ),
    )
    .limit(1)
  return rows[0]
}

export async function upsertSchedulingConnection(
  db: Db,
  data: NewSchedulingConnection,
): Promise<SchedulingConnectionRow> {
  const rows = await db
    .insert(schedulingConnections)
    .values(data)
    .onConflictDoUpdate({
      target: [schedulingConnections.tenantId, schedulingConnections.provider],
      set: {
        apiKey: data.apiKey,
        webhookUri: data.webhookUri,
        settings: data.settings,
      },
    })
    .returning()
  return rows[0]!
}

export async function deleteSchedulingConnection(
  db: Db,
  tenantId: string,
  provider: string,
): Promise<boolean> {
  const rows = await db
    .delete(schedulingConnections)
    .where(
      and(
        eq(schedulingConnections.tenantId, tenantId),
        eq(schedulingConnections.provider, provider),
      ),
    )
    .returning({ id: schedulingConnections.id })
  return rows.length > 0
}

/**
 * Look up a scheduling connection by tenant_id and provider for webhook routing.
 */
export async function getSchedulingConnectionByTenant(
  db: Db,
  tenantId: string,
  provider: string,
): Promise<SchedulingConnectionRow | undefined> {
  return getSchedulingConnection(db, tenantId, provider)
}

// ── Customer contact email match (for scheduling webhooks) ─────────────────────

/**
 * Find the customerId for a contact email within a tenant.
 * Used by scheduling webhook handlers to link bookings to CRM records.
 * Raw SQL query: columns referenced: customer_contacts.tenant_id, customer_contacts.email, customer_contacts.customer_id
 */
export async function findCustomerByContactEmail(
  db: Db,
  tenantId: string,
  email: string,
): Promise<string | null> {
  const rows = await db
    .select({ customerId: customerContacts.customerId })
    .from(customerContacts)
    .where(and(eq(customerContacts.tenantId, tenantId), eq(customerContacts.email, email)))
    .limit(1)
  return rows[0]?.customerId ?? null
}

// ── Tenant first-member lookup (for scheduling webhooks) ───────────────────────

/**
 * Get a userId from the first membership in a tenant.
 * Used by scheduling webhooks where no Zync session user is present.
 */
export async function getTenantFirstMemberId(
  db: Db,
  tenantId: string,
): Promise<string | null> {
  const rows = await db
    .select({ userId: tenantMemberships.userId })
    .from(tenantMemberships)
    .where(eq(tenantMemberships.tenantId, tenantId))
    .limit(1)
  return rows[0]?.userId ?? null
}