/**
 * Query helpers for accountant-export tables — accountant-export (wave-15).
 *
 * Covers: coa_accounts, coa_mappings, accountant_export_jobs
 */
import { eq, and, desc, sql } from 'drizzle-orm'
import type { Db } from '../client'
import {
  coaAccounts,
  coaMappings,
  accountantExportJobs,
} from '../schema/accountant-export'
import type {
  CoaAccountRow,
  NewCoaAccount,
  CoaMappingRow,
  NewCoaMapping,
  AccountantExportJobRow,
  NewAccountantExportJob,
} from '../schema/accountant-export'

export {
  coaAccounts,
  coaMappings,
  accountantExportJobs,
}
export type {
  CoaAccountRow,
  NewCoaAccount,
  CoaMappingRow,
  NewCoaMapping,
  AccountantExportJobRow,
  NewAccountantExportJob,
}

// ── CoA Accounts ──────────────────────────────────────────────────────────────

export async function listCoaAccounts(
  db: Db,
  tenantId: string,
): Promise<CoaAccountRow[]> {
  return db
    .select()
    .from(coaAccounts)
    .where(eq(coaAccounts.tenantId, tenantId))
    .orderBy(coaAccounts.code)
}

export async function upsertCoaAccounts(
  db: Db,
  tenantId: string,
  accounts: Array<{ code: string; name: string; type: string; form6111Code?: string | null }>,
): Promise<number> {
  if (accounts.length === 0) return 0
  await db
    .insert(coaAccounts)
    .values(accounts.map((a) => {
      const trimmedForm6111 = a.form6111Code?.trim()
      return {
        tenantId,
        code: a.code.trim(),
        name: a.name,
        type: a.type,
        form6111Code: trimmedForm6111 ? trimmedForm6111 : null,
      }
    }))
    .onConflictDoUpdate({
      target: [coaAccounts.tenantId, coaAccounts.code],
      set: {
        name: sql`excluded.name`,
        type: sql`excluded.type`,
        form6111Code: sql`excluded.form6111_code`,
      },
    })
  return accounts.length
}

// ── CoA Mappings ──────────────────────────────────────────────────────────────

export async function listCoaMappings(
  db: Db,
  tenantId: string,
): Promise<CoaMappingRow[]> {
  return db
    .select()
    .from(coaMappings)
    .where(eq(coaMappings.tenantId, tenantId))
    .orderBy(coaMappings.sourceKind)
}

export async function upsertCoaMappings(
  db: Db,
  tenantId: string,
  mappings: Array<{ sourceKind: string; accountCode: string }>,
): Promise<number> {
  if (mappings.length === 0) return 0
  await db
    .insert(coaMappings)
    .values(mappings.map((m) => ({
      tenantId,
      sourceKind: m.sourceKind,
      accountCode: m.accountCode,
    })))
    .onConflictDoUpdate({
      target: [coaMappings.tenantId, coaMappings.sourceKind],
      set: { accountCode: coaMappings.accountCode },
    })
  return mappings.length
}

// ── Accountant Export Jobs ────────────────────────────────────────────────────

export async function createAccountantExportJob(
  db: Db,
  input: Omit<NewAccountantExportJob, 'id' | 'createdAt'>,
): Promise<AccountantExportJobRow> {
  const [job] = await db
    .insert(accountantExportJobs)
    .values(input)
    .returning()
  if (!job) throw new Error('Failed to create accountant export job')
  return job
}

export async function getAccountantExportJob(
  db: Db,
  tenantId: string,
  jobId: string,
): Promise<AccountantExportJobRow | undefined> {
  const [job] = await db
    .select()
    .from(accountantExportJobs)
    .where(
      and(
        eq(accountantExportJobs.id, jobId),
        eq(accountantExportJobs.tenantId, tenantId),
      ),
    )
    .limit(1)
  return job
}

export async function listAccountantExportJobs(
  db: Db,
  tenantId: string,
  limit = 20,
): Promise<AccountantExportJobRow[]> {
  return db
    .select()
    .from(accountantExportJobs)
    .where(eq(accountantExportJobs.tenantId, tenantId))
    .orderBy(desc(accountantExportJobs.createdAt))
    .limit(limit)
}

export async function setAccountantExportJobDone(
  db: Db,
  tenantId: string,
  jobId: string,
  r2Key: string,
  downloadExpiresAt: Date,
): Promise<void> {
  await db
    .update(accountantExportJobs)
    .set({ status: 'done', r2Key, downloadExpiresAt })
    .where(
      and(
        eq(accountantExportJobs.tenantId, tenantId),
        eq(accountantExportJobs.id, jobId),
      ),
    )
}

export async function setAccountantExportJobError(
  db: Db,
  tenantId: string,
  jobId: string,
): Promise<void> {
  await db
    .update(accountantExportJobs)
    .set({ status: 'error' })
    .where(
      and(
        eq(accountantExportJobs.tenantId, tenantId),
        eq(accountantExportJobs.id, jobId),
      ),
    )
}

export async function updateAccountantExportJobDownloadExpiry(
  db: Db,
  tenantId: string,
  jobId: string,
  downloadExpiresAt: Date,
): Promise<void> {
  await db
    .update(accountantExportJobs)
    .set({ downloadExpiresAt })
    .where(
      and(
        eq(accountantExportJobs.id, jobId),
        eq(accountantExportJobs.tenantId, tenantId),
      ),
    )
}