import { sql, type ExpressionBuilder, type Selectable } from 'kysely'

import * as Id from '../../internal/Id.js'
import * as Viem from '../../internal/Viem.js'
import * as ApiKeyAdmissions from './apiKeyAdmissions.js'
import * as ApiKeyOwnerTombstones from './apiKeyOwnerTombstones.js'
import * as AuthAccounts from './authAccounts.js'
import * as RoutesDepositAddresses from './routesDepositAddresses.js'
import * as RoutesDeposits from './routesDeposits.js'
import * as RoutesIdempotency from './routesIdempotency.js'
import * as RoutesTransfers from './routesTransfers.js'
import * as SponsoredTransactions from './sponsoredTransactions.js'
import * as Users from './users.js'
import type * as Db from '../Db.js'
import type * as db_Schema from '../Schema.js'
import type * as EnabledBillingSources from './enabledBillingSources.js'
import type * as Memberships from './memberships.js'

/** Columns of the `organizations` table, derived from `Schema.Organization`. */
export type Table = db_Schema.Organization

/** A stored organization row. */
export type Record = Selectable<Table>

/**
 * Inserts an organization and returns the stored record. The `id` defaults to
 * a generated `org_…` handle when omitted.
 *
 * @param db - The database.
 * @param input - The organization to insert.
 * @returns The stored record.
 */
export function create(db: Db.Db, input: create.Input): Promise<Record> {
  const now = new Date().toISOString()
  return db.kysely
    .insertInto('organizations')
    .values({
      createdAt: now,
      createdBy: input.createdBy ?? null,
      id: input.id ?? Id.generate('org'),
      name: input.name,
      sponsorshipSubsidyDurationDays: null,
      sponsorshipSubsidyProjectSpendLimit: null,
      updatedAt: now,
      userId: input.userId ?? null,
    })
    .returningAll()
    .executeTakeFirstOrThrow()
}

export declare namespace create {
  /** Fields accepted when inserting an organization. */
  type Input = {
    /** Identity creating the organization (e.g. admin email), recorded for audit. */
    createdBy?: string | undefined
    /** Explicit id; a generated `org_…` handle is used when omitted. */
    id?: string | undefined
    /** Human-readable organization name. */
    name: string
    /** Owning user id (`usr_…`); null for organizations created by the super admin. */
    userId?: string | undefined
  }
}

/**
 * Inserts an organization, its owner membership, and initial billing sources
 * atomically.
 *
 * @param db - The database.
 * @param input - The organization to insert.
 * @returns The stored record.
 */
export function createOwned(db: Db.Db, input: createOwned.Input): Promise<Record> {
  const now = new Date().toISOString()
  return db.kysely.transaction().execute(async (kysely) => {
    const tx = { ...db, kysely }
    const userId = await (async () => {
      if (!input.walletAddress) return input.userId
      const addressUser = await Users.getByAddressForUpdate(tx, input.walletAddress)
      if (!addressUser) return input.userId
      return (await AuthAccounts.getWalletUserId(tx, input.walletAddress)) ?? input.userId
    })()
    const record = await tx.kysely
      .insertInto('organizations')
      .values({
        createdAt: now,
        createdBy: userId,
        id: Id.generate('org'),
        name: input.name,
        sponsorshipSubsidyDurationDays: null,
        sponsorshipSubsidyProjectSpendLimit: null,
        updatedAt: now,
        userId,
      })
      .returningAll()
      .executeTakeFirstOrThrow()
    await tx.kysely
      .insertInto('memberships')
      .values({
        createdAt: now,
        orgId: record.id,
        role: 'owner',
        updatedAt: now,
        userId,
      })
      .execute()
    if (input.enabledBillingSources?.length)
      await tx.kysely
        .insertInto('enabled_billing_sources')
        .values(
          input.enabledBillingSources.map((source) => ({
            createdAt: now,
            createdBy: userId,
            orgId: record.id,
            source,
          })),
        )
        .execute()
    return record
  })
}

export declare namespace createOwned {
  /** Fields accepted when inserting a user-owned organization. */
  type Input = {
    /** Billing sources enabled when the organization is created. */
    enabledBillingSources?: readonly EnabledBillingSources.Source[] | undefined
    /** Human-readable organization name. */
    name: string
    /** Owning user id (`usr_…`), granted the `owner` membership. */
    userId: string
    /** Current signing wallet address, used to serialize identity ownership. */
    walletAddress?: string | undefined
  }
}

/**
 * Deletes an organization and its owned rows atomically.
 *
 * @param db - The database.
 * @param id - The organization id (`org_…`).
 * @param options - Post-commit lifecycle hooks.
 * @returns Whether the organization existed and was deleted.
 */
export async function deleteOrganization(
  db: Db.Db,
  id: string,
  options: deleteOrganization.Options = {},
): Promise<boolean> {
  const result = await RoutesDeposits.withSubsidySettlement(db, {
    fn: async (tx) => {
      const failedSince = new Date(
        Date.now() - SponsoredTransactions.failedIntentRecoveryTtlMs,
      ).toISOString()
      const unreportedSponsorship = await tx.kysely
        .selectFrom('sponsored_transactions')
        .select('id')
        .where('orgId', '=', id)
        .where('billable', '=', true)
        .where('chainId', '=', Viem.chainId.mainnet)
        .where('meterReportedAt', 'is', null)
        .where(unreportedSponsorshipStatus(failedSince))
        .limit(1)
        .executeTakeFirst()
      const [unreportedDepositSubsidy, unreportedTransferSubsidy] = await Promise.all([
        RoutesDeposits.hasUnreportedSubsidies(tx, { orgId: id }),
        RoutesTransfers.hasUnreportedSubsidies(tx, id),
      ])
      if (unreportedSponsorship || unreportedDepositSubsidy || unreportedTransferSubsidy)
        throw new UnreportedMeteredUsageError()
      if (await RoutesIdempotency.hasUnattributedInFlightClaim(tx))
        throw new IrrevocableDepositAddressError()
      if (await RoutesIdempotency.hasInFlightDepositAddress(tx, id))
        throw new IrrevocableDepositAddressError()
      if (await RoutesDepositAddresses.hasSubsidizedRelayAddress(tx, id))
        throw new IrrevocableDepositAddressError()

      const previous = await RoutesDepositAddresses.listActiveByOrganization(tx, id)
      const deactivated = await RoutesDepositAddresses.deactivateByOrganization(tx, {
        now: new Date().toISOString(),
        orgId: id,
      })
      await ApiKeyOwnerTombstones.markDeleted(tx, { orgId: id })
      await ApiKeyAdmissions.releaseOwner(tx, { orgId: id })
      await tx.kysely
        .deleteFrom('webhook_subscriptions')
        .where('ownerType', '=', 'api_key')
        .where('ownerId', '=', id)
        .execute()
      await tx.kysely.deleteFrom('billing_settings').where('orgId', '=', id).execute()
      await tx.kysely.deleteFrom('enabled_billing_sources').where('orgId', '=', id).execute()
      await tx.kysely.deleteFrom('invitations').where('orgId', '=', id).execute()
      await tx.kysely.deleteFrom('invite_link_redemptions').where('orgId', '=', id).execute()
      await tx.kysely.deleteFrom('invite_links').where('orgId', '=', id).execute()
      await tx.kysely.deleteFrom('memberships').where('orgId', '=', id).execute()
      await tx.kysely.deleteFrom('projects').where('orgId', '=', id).execute()
      await tx.kysely.deleteFrom('request_usage_buckets').where('orgId', '=', id).execute()
      await tx.kysely.deleteFrom('request_usage_meter_events').where('orgId', '=', id).execute()
      await tx.kysely.deleteFrom('stripe_customers').where('orgId', '=', id).execute()
      const result = await tx.kysely
        .deleteFrom('organizations')
        .where('id', '=', id)
        .executeTakeFirst()
      const deactivatedById = new Map(deactivated.map((record) => [record.id, record]))
      return {
        deactivated: previous.flatMap((previous) => {
          const record = deactivatedById.get(previous.id)
          return record ? [{ previous, record }] : []
        }),
        deleted: result.numDeletedRows > 0n,
      }
    },
    orgId: id,
  })
  if (result.deleted && result.deactivated.length > 0)
    await options.onDepositAddressesDeactivated?.(result.deactivated)
  return result.deleted
}

export declare namespace deleteOrganization {
  /** Post-commit organization deletion hooks. */
  type Options = {
    /** Receives committed deposit-address deactivation transitions. */
    onDepositAddressesDeactivated?:
      | ((transitions: readonly DepositAddressDeactivation[]) => Promise<void> | void)
      | undefined
  }

  /** One committed deposit-address deactivation. */
  type DepositAddressDeactivation = {
    /** Address before organization deletion deactivated it. */
    previous: RoutesDepositAddresses.Record
    /** Committed deactivated address. */
    record: RoutesDepositAddresses.Record
  }
}

/** Whether an organization has metered usage awaiting Stripe settlement. */
export async function hasUnreportedMeteredUsage(db: Db.Db, id: string): Promise<boolean> {
  const failedSince = new Date(
    Date.now() - SponsoredTransactions.failedIntentRecoveryTtlMs,
  ).toISOString()
  const sponsorship = await db.kysely
    .selectFrom('sponsored_transactions')
    .select('id')
    .where('orgId', '=', id)
    .where('billable', '=', true)
    .where('chainId', '=', Viem.chainId.mainnet)
    .where('meterReportedAt', 'is', null)
    .where(unreportedSponsorshipStatus(failedSince))
    .limit(1)
    .executeTakeFirst()
  if (sponsorship) return true
  const [deposit, transfer] = await Promise.all([
    RoutesDeposits.hasUnreportedSubsidies(db, { orgId: id }),
    RoutesTransfers.hasUnreportedSubsidies(db, id),
  ])
  return deposit || transfer
}

/**
 * Reads an organization by id.
 *
 * @param db - The database.
 * @param id - The organization id (`org_…`).
 * @returns The record, or `undefined` when absent.
 */
export function get(db: Db.Db, id: string): Promise<Record | undefined> {
  return db.kysely.selectFrom('organizations').selectAll().where('id', '=', id).executeTakeFirst()
}

/**
 * Reads organizations by id for operational attribution displays.
 *
 * @param db - The database.
 * @param ids - Organization ids to read.
 * @returns Matching organization records.
 */
export function listByIds(db: Db.Db, ids: readonly string[]): Promise<Record[]> {
  if (ids.length === 0) return Promise.resolve([])
  return db.kysely
    .selectFrom('organizations')
    .selectAll()
    .where('id', 'in', [...ids])
    .execute()
}

/**
 * Reads an organization with current member and project counts.
 *
 * @param db - The database.
 * @param id - The organization id.
 * @returns The organization summary, or `undefined` when absent.
 */
export async function getSummary(db: Db.Db, id: string): Promise<search.Result | undefined> {
  const record = await db.kysely
    .selectFrom('organizations')
    .selectAll('organizations')
    .select((eb) => [
      eb
        .selectFrom('memberships')
        .select((builder) => builder.fn.countAll<number>().as('count'))
        .whereRef('memberships.orgId', '=', 'organizations.id')
        .as('memberCount'),
      eb
        .selectFrom('projects')
        .select((builder) => builder.fn.countAll<number>().as('count'))
        .whereRef('projects.orgId', '=', 'organizations.id')
        .as('projectCount'),
    ])
    .where('organizations.id', '=', id)
    .executeTakeFirst()
  if (!record) return undefined
  return {
    ...record,
    memberCount: Number(record.memberCount),
    projectCount: Number(record.projectCount),
  }
}

/**
 * Lists organizations, newest first.
 *
 * @param db - The database.
 * @returns The records.
 */
export function list(db: Db.Db): Promise<Record[]> {
  return db.kysely.selectFrom('organizations').selectAll().orderBy('createdAt', 'desc').execute()
}

/**
 * Searches organizations for the admin lookup surface, newest first. Results
 * include member and project counts and use a stable `(createdAt, id)` keyset.
 *
 * @param db - The database.
 * @param options - Search, keyset, and page-size options.
 * @returns At most `limit + 1` rows so the caller can derive a next cursor.
 */
export async function search(db: Db.Db, options: search.Options): Promise<search.Result[]> {
  const { cursor, limit, query: searchQuery } = options
  let query = db.kysely
    .selectFrom('organizations')
    .selectAll('organizations')
    .select((eb) => [
      eb
        .selectFrom('memberships')
        .select((builder) => builder.fn.countAll<number>().as('count'))
        .whereRef('memberships.orgId', '=', 'organizations.id')
        .as('memberCount'),
      eb
        .selectFrom('projects')
        .select((builder) => builder.fn.countAll<number>().as('count'))
        .whereRef('projects.orgId', '=', 'organizations.id')
        .as('projectCount'),
    ])

  if (searchQuery?.startsWith('org_')) query = query.where('organizations.id', '=', searchQuery)
  else if (searchQuery?.startsWith('usr_'))
    query = query.where((eb) =>
      eb.or([
        eb('organizations.userId', '=', searchQuery),
        eb(
          'organizations.id',
          'in',
          eb
            .selectFrom('memberships')
            .select('memberships.orgId')
            .where('memberships.userId', '=', searchQuery),
        ),
      ]),
    )
  else if (searchQuery) {
    const pattern = `${searchQuery
      .toLowerCase()
      .replaceAll('\\', '\\\\')
      .replaceAll('%', '\\%')
      .replaceAll('_', '\\_')}%`
    query = query.where((eb) =>
      eb.or([
        sql<boolean>`lower(${sql.ref('organizations.name')}) LIKE ${pattern} ESCAPE '\\'`,
        eb(
          'organizations.id',
          'in',
          eb
            .selectFrom('memberships')
            .innerJoin('users', 'users.id', 'memberships.userId')
            .select('memberships.orgId')
            .where(sql<boolean>`lower(${sql.ref('users.email')}) LIKE ${pattern} ESCAPE '\\'`),
        ),
      ]),
    )
  }

  if (cursor)
    query = query.where((eb) =>
      eb.or([
        eb('organizations.createdAt', '<', cursor.createdAt),
        eb.and([
          eb('organizations.createdAt', '=', cursor.createdAt),
          eb('organizations.id', '<', cursor.id),
        ]),
      ]),
    )

  const records = await query
    .orderBy('organizations.createdAt', 'desc')
    .orderBy('organizations.id', 'desc')
    .limit(limit + 1)
    .execute()
  return records.map((record) => ({
    ...record,
    memberCount: Number(record.memberCount),
    projectCount: Number(record.projectCount),
  }))
}

export declare namespace search {
  /** Cursor fields for the last organization returned by the previous page. */
  type Cursor = {
    /** Organization creation time. */
    createdAt: string
    /** Organization id, used as a deterministic tie-breaker. */
    id: string
  }

  /** Options for {@link search}. */
  type Options = {
    /** Last organization returned by the previous page. */
    cursor?: Cursor | undefined
    /** Requested page size. */
    limit: number
    /** Exact resource id or case-insensitive name/email prefix search. */
    query?: string | undefined
  }

  /** Organization summary returned by {@link search}. */
  type Result = Record & {
    /** Number of current organization members. */
    memberCount: number
    /** Number of projects owned by the organization. */
    projectCount: number
  }
}

/**
 * Lists organizations owned by `userId`, newest first.
 *
 * @param db - The database.
 * @param userId - The owning user id (`usr_…`).
 * @returns The records.
 */
export function listByUser(db: Db.Db, userId: string): Promise<Record[]> {
  return db.kysely
    .selectFrom('organizations')
    .selectAll()
    .where('userId', '=', userId)
    .orderBy('createdAt', 'desc')
    .execute()
}

/**
 * Lists organizations `userId` is a member of (any role), newest first. Each
 * row carries the caller's membership `role`.
 *
 * @param db - The database.
 * @param userId - The member user id (`usr_…`).
 * @returns The records.
 */
export function listByMember(
  db: Db.Db,
  userId: string,
): Promise<(Record & { role: Memberships.Role })[]> {
  return db.kysely
    .selectFrom('organizations')
    .innerJoin('memberships', 'memberships.orgId', 'organizations.id')
    .where('memberships.userId', '=', userId)
    .selectAll('organizations')
    .select('memberships.role')
    .orderBy('organizations.createdAt', 'desc')
    .execute()
}

/**
 * Renames an organization, bumping `updatedAt`.
 *
 * @param db - The database.
 * @param id - The organization id (`org_…`).
 * @param input - The fields to update.
 * @returns The updated record, or `undefined` when absent.
 */
export function update(db: Db.Db, id: string, input: update.Input): Promise<Record | undefined> {
  return db.kysely
    .updateTable('organizations')
    .set({ name: input.name, updatedAt: new Date().toISOString() })
    .where('id', '=', id)
    .returningAll()
    .executeTakeFirst()
}

export declare namespace update {
  /** Mutable organization fields. */
  type Input = {
    /** New human-readable organization name. */
    name: string
  }
}

/**
 * Replaces the organization's sponsorship subsidy policy. A null duration
 * disables the program; an enabled policy may omit its per-attribution spend cap.
 *
 * @param db - The database.
 * @param id - The organization id (`org_…`).
 * @param input - The replacement subsidy policy.
 * @returns The updated organization, or `undefined` when absent.
 */
export function setSponsorshipSubsidy(
  db: Db.Db,
  id: string,
  input: setSponsorshipSubsidy.Input,
): Promise<Record | undefined> {
  return db.kysely
    .updateTable('organizations')
    .set({
      sponsorshipSubsidyDurationDays: input.durationDays,
      sponsorshipSubsidyProjectSpendLimit: input.attributionSpendLimit,
      updatedAt: new Date().toISOString(),
    })
    .where('id', '=', id)
    .returningAll()
    .executeTakeFirst()
}

export declare namespace setSponsorshipSubsidy {
  /** Replacement organization subsidy policy. */
  type Input =
    | {
        /** Optional decimal USD spend cap applied independently to each attribution. */
        attributionSpendLimit: string | null
        /** Fixed promotion duration in days. */
        durationDays: 90
      }
    | {
        /** Null disables the promotion. */
        attributionSpendLimit: null
        /** Null disables the promotion. */
        durationDays: null
      }
}

/**
 * Inserts an organization when absent (`ON CONFLICT DO NOTHING`) — the
 * mint/backfill path, where a key's `orgId` must resolve to a real row. The
 * `name` defaults to the id for degenerate orgs created by the mint fallback.
 *
 * @param db - The database.
 * @param input - The organization to ensure.
 */
export async function upsert(db: Db.Db, input: upsert.Input): Promise<void> {
  const now = new Date().toISOString()
  await db.kysely
    .insertInto('organizations')
    .values({
      createdAt: now,
      createdBy: input.createdBy ?? null,
      id: input.id,
      name: input.name ?? input.id,
      sponsorshipSubsidyDurationDays: null,
      sponsorshipSubsidyProjectSpendLimit: null,
      updatedAt: now,
    })
    .onConflict((oc) => oc.column('id').doNothing())
    .execute()
}

export declare namespace upsert {
  /** Fields accepted when ensuring an organization exists. */
  type Input = {
    /** Identity creating the organization, recorded for audit. */
    createdBy?: string | undefined
    /** The organization id (`org_…`, or a mint-fallback key id). */
    id: string
    /** Human-readable organization name; defaults to the id. */
    name?: string | undefined
  }
}

/** Blocks rows that can still settle or recover into billable mainnet spend. */
function unreportedSponsorshipStatus(failedSince: string) {
  return (eb: ExpressionBuilder<Db.Database, 'sponsored_transactions'>) =>
    eb.or([
      eb('status', '!=', 'failed'),
      eb.and([
        eb('status', '=', 'failed'),
        eb('transactionHash', 'is', null),
        eb('finalizedAt', '>=', failedSince),
      ]),
    ])
}

/** Organization deletion is blocked by a provider subsidy that cannot be revoked. */
export class IrrevocableDepositAddressError extends Error {
  override name = 'Organizations.IrrevocableDepositAddressError'
}

/** Organization deletion is blocked until billable metered usage settles. */
export class UnreportedMeteredUsageError extends Error {
  override name = 'Organizations.UnreportedMeteredUsageError'
}