import { eq, and, sql, count, exists, type Column } from 'drizzle-orm'
import type { PostgresJsDatabase } from 'drizzle-orm/postgres-js'
import {
  accounts,
  members,
  memberIdentifiers,
  accountProcessorLinks,
  accountPaymentMethods,
  taxExemptions,
} from '../db/schema/accounts.js'
import type {
  AccountCreateInput,
  AccountUpdateInput,
  MemberIdentifierCreateInput,
  MemberIdentifierUpdateInput,
  ProcessorLinkCreateInput,
  ProcessorLinkUpdateInput,
  PaymentMethodCreateInput,
  PaymentMethodUpdateInput,
  TaxExemptionCreateInput,
  TaxExemptionUpdateInput,
  PaginationInput,
} from '@lunarfront/shared/schemas'
import { isMinor, normalizeStateCode } from '@lunarfront/shared/utils'
import {
  withPagination,
  withSort,
  buildSearchCondition,
  paginatedResponse,
} from '../utils/pagination.js'

async function generateUniqueNumber(
  db: PostgresJsDatabase<any>,
  table: typeof accounts | typeof members,
  column: typeof accounts.accountNumber | typeof members.memberNumber,
): Promise<string> {
  for (let attempt = 0; attempt < 10; attempt++) {
    const num = String(Math.floor(100000 + Math.random() * 900000))
    const [existing] = await db
      .select({ id: table.id })
      .from(table)
      .where(eq(column, num))
      .limit(1)
    if (!existing) return num
  }
  // Fallback to 8 digits if 6-digit space is crowded
  return String(Math.floor(10000000 + Math.random() * 90000000))
}

function normalizeAddress(address?: { street?: string; city?: string; state?: string; zip?: string } | null) {
  if (!address) return address
  return {
    ...address,
    state: address.state ? (normalizeStateCode(address.state) ?? address.state) : address.state,
  }
}

export const AccountService = {
  async create(db: PostgresJsDatabase<any>, input: AccountCreateInput) {
    const accountNumber = await generateUniqueNumber(db, accounts, accounts.accountNumber)

    const [account] = await db
      .insert(accounts)
      .values({
        accountNumber,
        name: input.name,
        email: input.email,
        phone: input.phone,
        address: normalizeAddress(input.address),
        billingMode: input.billingMode,
        notes: input.notes,
      })
      .returning()

    return account
  },

  async getById(db: PostgresJsDatabase<any>, id: string) {
    const [account] = await db
      .select()
      .from(accounts)
      .where(eq(accounts.id, id))
      .limit(1)

    return account ?? null
  },

  async update(db: PostgresJsDatabase<any>, id: string, input: AccountUpdateInput) {
    const [account] = await db
      .update(accounts)
      .set({ ...input, updatedAt: new Date() })
      .where(eq(accounts.id, id))
      .returning()

    return account ?? null
  },

  async softDelete(db: PostgresJsDatabase<any>, id: string) {
    const [account] = await db
      .update(accounts)
      .set({ isActive: false, updatedAt: new Date() })
      .where(eq(accounts.id, id))
      .returning()

    return account ?? null
  },

  async list(db: PostgresJsDatabase<any>, params: PaginationInput) {
    const baseWhere = eq(accounts.isActive, true)

    const accountSearch = params.q
      ? buildSearchCondition(params.q, [accounts.name, accounts.email, accounts.phone, accounts.accountNumber])
      : undefined

    // Also search across member names on this account
    const memberSearch = params.q
      ? exists(
          db.select({ id: members.id })
            .from(members)
            .where(and(
              eq(members.accountId, accounts.id),
              buildSearchCondition(params.q, [members.firstName, members.lastName, members.email])!,
            )),
        )
      : undefined

    const searchCondition = accountSearch && memberSearch
      ? sql`(${accountSearch} OR ${memberSearch})`
      : undefined

    const where = searchCondition ? and(baseWhere, searchCondition) : baseWhere

    const sortableColumns: Record<string, Column> = {
      name: accounts.name,
      email: accounts.email,
      created_at: accounts.createdAt,
      account_number: accounts.accountNumber,
    }

    let query = db.select().from(accounts).where(where).$dynamic()
    query = withSort(query, params.sort, params.order, sortableColumns, accounts.name)
    query = withPagination(query, params.page, params.limit)

    const [data, [{ total }]] = await Promise.all([
      query,
      db.select({ total: count() }).from(accounts).where(where),
    ])

    return paginatedResponse(data, total, params.page, params.limit)
  },
}

export const MemberService = {
  async create(
    db: PostgresJsDatabase<any>,
    input: {
      accountId: string
      firstName: string
      lastName: string
      dateOfBirth?: string
      isMinor?: boolean
      email?: string
      phone?: string
      address?: { street?: string; city?: string; state?: string; zip?: string }
      notes?: string
    },
  ) {
    // isMinor: explicit flag wins, else derive from DOB, else false
    const minor = input.isMinor ?? (input.dateOfBirth ? isMinor(input.dateOfBirth) : false)
    const memberNumber = await generateUniqueNumber(db, members, members.memberNumber)

    // Inherit email, phone, address from account if not provided
    const [account] = await db
      .select()
      .from(accounts)
      .where(eq(accounts.id, input.accountId))
      .limit(1)

    const email = input.email ?? account?.email ?? undefined
    const phone = input.phone ?? account?.phone ?? undefined
    const address = normalizeAddress(input.address ?? account?.address ?? undefined)

    const [member] = await db
      .insert(members)
      .values({
        memberNumber,
        accountId: input.accountId,
        firstName: input.firstName,
        lastName: input.lastName,
        dateOfBirth: input.dateOfBirth,
        isMinor: minor,
        email,
        phone,
        address,
        notes: input.notes,
      })
      .returning()
    if (account && !account.primaryMemberId) {
      await db
        .update(accounts)
        .set({ primaryMemberId: member.id, updatedAt: new Date() })
        .where(eq(accounts.id, input.accountId))
    }

    return member
  },

  async getById(db: PostgresJsDatabase<any>, id: string) {
    const [member] = await db
      .select()
      .from(members)
      .where(eq(members.id, id))
      .limit(1)

    return member ?? null
  },

  async list(db: PostgresJsDatabase<any>, params: PaginationInput) {
    const searchCondition = params.q
      ? buildSearchCondition(params.q, [members.firstName, members.lastName, members.email, members.phone])
      : undefined

    const sortableColumns: Record<string, Column> = {
      first_name: members.firstName,
      last_name: members.lastName,
      email: members.email,
      created_at: members.createdAt,
    }

    let query = db.select({
      id: members.id,
      accountId: members.accountId,
      firstName: members.firstName,
      lastName: members.lastName,
      dateOfBirth: members.dateOfBirth,
      isMinor: members.isMinor,
      email: members.email,
      phone: members.phone,
      notes: members.notes,
      legacyId: members.legacyId,
      createdAt: members.createdAt,
      updatedAt: members.updatedAt,
      accountName: accounts.name,
    })
      .from(members)
      .leftJoin(accounts, eq(members.accountId, accounts.id))
      .where(searchCondition)
      .$dynamic()
    query = withSort(query, params.sort, params.order, sortableColumns, members.lastName)
    query = withPagination(query, params.page, params.limit)

    const [data, [{ total }]] = await Promise.all([
      query,
      db.select({ total: count() }).from(members).where(searchCondition),
    ])

    return paginatedResponse(data, total, params.page, params.limit)
  },

  async listByAccount(
    db: PostgresJsDatabase<any>,
    accountId: string,
    params: PaginationInput,
  ) {
    const where = eq(members.accountId, accountId)

    const sortableColumns: Record<string, Column> = {
      first_name: members.firstName,
      last_name: members.lastName,
      created_at: members.createdAt,
    }

    let query = db.select().from(members).where(where).$dynamic()
    query = withSort(query, params.sort, params.order, sortableColumns, members.lastName)
    query = withPagination(query, params.page, params.limit)

    const [data, [{ total }]] = await Promise.all([
      query,
      db.select({ total: count() }).from(members).where(where),
    ])

    return paginatedResponse(data, total, params.page, params.limit)
  },

  async update(
    db: PostgresJsDatabase<any>,
    id: string,
    input: {
      firstName?: string
      lastName?: string
      dateOfBirth?: string
      isMinor?: boolean
      email?: string
      phone?: string
      notes?: string
    },
  ) {
    const updates: Record<string, unknown> = { ...input, updatedAt: new Date() }

    // isMinor: explicit flag wins, else derive from DOB if provided
    if (input.isMinor !== undefined) {
      updates.isMinor = input.isMinor
    } else if (input.dateOfBirth) {
      updates.isMinor = isMinor(input.dateOfBirth)
    }

    const [member] = await db
      .update(members)
      .set(updates)
      .where(eq(members.id, id))
      .returning()

    return member ?? null
  },

  async move(db: PostgresJsDatabase<any>, memberId: string, targetAccountId: string) {
    const member = await this.getById(db, memberId)
    if (!member) return null

    const [updated] = await db
      .update(members)
      .set({ accountId: targetAccountId, updatedAt: new Date() })
      .where(eq(members.id, memberId))
      .returning()

    // If target account has no primary, set this member
    const [targetAccount] = await db
      .select()
      .from(accounts)
      .where(eq(accounts.id, targetAccountId))
      .limit(1)
    if (targetAccount && !targetAccount.primaryMemberId) {
      await db
        .update(accounts)
        .set({ primaryMemberId: memberId, updatedAt: new Date() })
        .where(eq(accounts.id, targetAccountId))
    }

    return updated
  },

  async delete(db: PostgresJsDatabase<any>, id: string) {
    const [member] = await db
      .delete(members)
      .where(eq(members.id, id))
      .returning()

    return member ?? null
  },
}

export const ProcessorLinkService = {
  async create(db: PostgresJsDatabase<any>, input: ProcessorLinkCreateInput) {
    const [link] = await db
      .insert(accountProcessorLinks)
      .values({
        accountId: input.accountId,
        processor: input.processor,
        processorCustomerId: input.processorCustomerId,
      })
      .returning()
    return link
  },

  async getById(db: PostgresJsDatabase<any>, id: string) {
    const [link] = await db
      .select()
      .from(accountProcessorLinks)
      .where(eq(accountProcessorLinks.id, id))
      .limit(1)
    return link ?? null
  },

  async listByAccount(db: PostgresJsDatabase<any>, accountId: string, params: PaginationInput) {
    const baseWhere = eq(accountProcessorLinks.accountId, accountId)
    const searchCondition = params.q
      ? buildSearchCondition(params.q, [accountProcessorLinks.processorCustomerId, accountProcessorLinks.processor])
      : undefined
    const where = searchCondition ? and(baseWhere, searchCondition) : baseWhere

    const sortableColumns: Record<string, Column> = {
      processor: accountProcessorLinks.processor,
      created_at: accountProcessorLinks.createdAt,
    }

    let query = db.select().from(accountProcessorLinks).where(where).$dynamic()
    query = withSort(query, params.sort, params.order, sortableColumns, accountProcessorLinks.createdAt)
    query = withPagination(query, params.page, params.limit)

    const [data, [{ total }]] = await Promise.all([
      query,
      db.select({ total: count() }).from(accountProcessorLinks).where(where),
    ])

    return paginatedResponse(data, total, params.page, params.limit)
  },

  async update(db: PostgresJsDatabase<any>, id: string, input: ProcessorLinkUpdateInput) {
    const [link] = await db
      .update(accountProcessorLinks)
      .set(input)
      .where(eq(accountProcessorLinks.id, id))
      .returning()
    return link ?? null
  },

  async delete(db: PostgresJsDatabase<any>, id: string) {
    const [link] = await db
      .delete(accountProcessorLinks)
      .where(eq(accountProcessorLinks.id, id))
      .returning()
    return link ?? null
  },
}

export const PaymentMethodService = {
  async create(db: PostgresJsDatabase<any>, input: PaymentMethodCreateInput) {
    // If this is the default, unset any existing default for this account
    if (input.isDefault) {
      await db
        .update(accountPaymentMethods)
        .set({ isDefault: false })
        .where(
          and(
            eq(accountPaymentMethods.accountId, input.accountId),
            eq(accountPaymentMethods.isDefault, true),
          ),
        )
    }

    const [method] = await db
      .insert(accountPaymentMethods)
      .values({
        accountId: input.accountId,
        processor: input.processor,
        processorPaymentMethodId: input.processorPaymentMethodId,
        cardBrand: input.cardBrand,
        lastFour: input.lastFour,
        expMonth: input.expMonth,
        expYear: input.expYear,
        isDefault: input.isDefault,
      })
      .returning()
    return method
  },

  async getById(db: PostgresJsDatabase<any>, id: string) {
    const [method] = await db
      .select()
      .from(accountPaymentMethods)
      .where(eq(accountPaymentMethods.id, id))
      .limit(1)
    return method ?? null
  },

  async listByAccount(db: PostgresJsDatabase<any>, accountId: string, params: PaginationInput) {
    const baseWhere = eq(accountPaymentMethods.accountId, accountId)
    const searchCondition = params.q
      ? buildSearchCondition(params.q, [accountPaymentMethods.cardBrand, accountPaymentMethods.lastFour])
      : undefined
    const where = searchCondition ? and(baseWhere, searchCondition) : baseWhere

    const sortableColumns: Record<string, Column> = {
      card_brand: accountPaymentMethods.cardBrand,
      processor: accountPaymentMethods.processor,
      created_at: accountPaymentMethods.createdAt,
    }

    let query = db.select().from(accountPaymentMethods).where(where).$dynamic()
    query = withSort(query, params.sort, params.order, sortableColumns, accountPaymentMethods.createdAt)
    query = withPagination(query, params.page, params.limit)

    const [data, [{ total }]] = await Promise.all([
      query,
      db.select({ total: count() }).from(accountPaymentMethods).where(where),
    ])

    return paginatedResponse(data, total, params.page, params.limit)
  },

  async update(db: PostgresJsDatabase<any>, id: string, input: PaymentMethodUpdateInput) {
    // If setting as default, unset existing default
    if (input.isDefault) {
      const existing = await this.getById(db, id)
      if (existing) {
        await db
          .update(accountPaymentMethods)
          .set({ isDefault: false })
          .where(
            and(
              eq(accountPaymentMethods.accountId, existing.accountId),
              eq(accountPaymentMethods.isDefault, true),
            ),
          )
      }
    }

    const [method] = await db
      .update(accountPaymentMethods)
      .set(input)
      .where(eq(accountPaymentMethods.id, id))
      .returning()
    return method ?? null
  },

  async delete(db: PostgresJsDatabase<any>, id: string) {
    const [method] = await db
      .delete(accountPaymentMethods)
      .where(eq(accountPaymentMethods.id, id))
      .returning()
    return method ?? null
  },
}

export const TaxExemptionService = {
  async create(db: PostgresJsDatabase<any>, input: TaxExemptionCreateInput) {
    const [exemption] = await db
      .insert(taxExemptions)
      .values({
        accountId: input.accountId,
        certificateNumber: input.certificateNumber,
        certificateType: input.certificateType,
        issuingState: input.issuingState,
        expiresAt: input.expiresAt,
        notes: input.notes,
        status: 'pending',
      })
      .returning()
    return exemption
  },

  async getById(db: PostgresJsDatabase<any>, id: string) {
    const [exemption] = await db
      .select()
      .from(taxExemptions)
      .where(eq(taxExemptions.id, id))
      .limit(1)
    return exemption ?? null
  },

  async listByAccount(db: PostgresJsDatabase<any>, accountId: string, params: PaginationInput) {
    const baseWhere = eq(taxExemptions.accountId, accountId)
    const searchCondition = params.q
      ? buildSearchCondition(params.q, [taxExemptions.certificateNumber, taxExemptions.certificateType, taxExemptions.issuingState])
      : undefined
    const where = searchCondition ? and(baseWhere, searchCondition) : baseWhere

    const sortableColumns: Record<string, Column> = {
      certificate_number: taxExemptions.certificateNumber,
      status: taxExemptions.status,
      expires_at: taxExemptions.expiresAt,
      created_at: taxExemptions.createdAt,
    }

    let query = db.select().from(taxExemptions).where(where).$dynamic()
    query = withSort(query, params.sort, params.order, sortableColumns, taxExemptions.createdAt)
    query = withPagination(query, params.page, params.limit)

    const [data, [{ total }]] = await Promise.all([
      query,
      db.select({ total: count() }).from(taxExemptions).where(where),
    ])

    return paginatedResponse(data, total, params.page, params.limit)
  },

  async update(db: PostgresJsDatabase<any>, id: string, input: TaxExemptionUpdateInput) {
    const [exemption] = await db
      .update(taxExemptions)
      .set({ ...input, updatedAt: new Date() })
      .where(eq(taxExemptions.id, id))
      .returning()
    return exemption ?? null
  },

  async approve(db: PostgresJsDatabase<any>, id: string, approvedBy: string) {
    const [exemption] = await db
      .update(taxExemptions)
      .set({
        status: 'approved',
        approvedBy,
        approvedAt: new Date(),
        updatedAt: new Date(),
      })
      .where(eq(taxExemptions.id, id))
      .returning()
    return exemption ?? null
  },

  async revoke(db: PostgresJsDatabase<any>, id: string, revokedBy: string, reason: string) {
    const [exemption] = await db
      .update(taxExemptions)
      .set({
        status: 'none',
        revokedBy,
        revokedAt: new Date(),
        revokedReason: reason,
        updatedAt: new Date(),
      })
      .where(eq(taxExemptions.id, id))
      .returning()
    return exemption ?? null
  },
}

export const MemberIdentifierService = {
  async create(db: PostgresJsDatabase<any>, input: MemberIdentifierCreateInput) {
    // If setting as primary, unset existing primary for this member
    if (input.isPrimary) {
      await db
        .update(memberIdentifiers)
        .set({ isPrimary: false })
        .where(
          and(
            eq(memberIdentifiers.memberId, input.memberId),
            eq(memberIdentifiers.isPrimary, true),
          ),
        )
    }

    const [identifier] = await db
      .insert(memberIdentifiers)
      .values({
        memberId: input.memberId,
        type: input.type,
        label: input.label,
        value: input.value,
        issuingAuthority: input.issuingAuthority,
        issuedDate: input.issuedDate,
        expiresAt: input.expiresAt,
        imageFrontFileId: input.imageFrontFileId,
        imageBackFileId: input.imageBackFileId,
        notes: input.notes,
        isPrimary: input.isPrimary,
      })
      .returning()
    return identifier
  },

  async listByMember(db: PostgresJsDatabase<any>, memberId: string, params: PaginationInput) {
    const baseWhere = eq(memberIdentifiers.memberId, memberId)
    const searchCondition = params.q
      ? buildSearchCondition(params.q, [memberIdentifiers.value, memberIdentifiers.label, memberIdentifiers.issuingAuthority])
      : undefined
    const where = searchCondition ? and(baseWhere, searchCondition) : baseWhere

    const sortableColumns: Record<string, Column> = {
      type: memberIdentifiers.type,
      created_at: memberIdentifiers.createdAt,
    }

    let query = db.select().from(memberIdentifiers).where(where).$dynamic()
    query = withSort(query, params.sort, params.order, sortableColumns, memberIdentifiers.createdAt)
    query = withPagination(query, params.page, params.limit)

    const [data, [{ total }]] = await Promise.all([
      query,
      db.select({ total: count() }).from(memberIdentifiers).where(where),
    ])

    return paginatedResponse(data, total, params.page, params.limit)
  },

  async getById(db: PostgresJsDatabase<any>, id: string) {
    const [identifier] = await db
      .select()
      .from(memberIdentifiers)
      .where(eq(memberIdentifiers.id, id))
      .limit(1)
    return identifier ?? null
  },

  async update(db: PostgresJsDatabase<any>, id: string, input: MemberIdentifierUpdateInput) {
    if (input.isPrimary) {
      const existing = await this.getById(db, id)
      if (existing) {
        await db
          .update(memberIdentifiers)
          .set({ isPrimary: false })
          .where(
            and(
              eq(memberIdentifiers.memberId, existing.memberId),
              eq(memberIdentifiers.isPrimary, true),
            ),
          )
      }
    }

    const [identifier] = await db
      .update(memberIdentifiers)
      .set({ ...input, updatedAt: new Date() })
      .where(eq(memberIdentifiers.id, id))
      .returning()
    return identifier ?? null
  },

  async delete(db: PostgresJsDatabase<any>, id: string) {
    const [identifier] = await db
      .delete(memberIdentifiers)
      .where(eq(memberIdentifiers.id, id))
      .returning()
    return identifier ?? null
  },
}
