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

import * as Id from '../../internal/Id.js'
import * as Viem from '../../internal/Viem.js'
import * as SponsoredTransactions from './sponsoredTransactions.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 (trx) => {
    const record = await trx
      .insertInto('organizations')
      .values({
        createdAt: now,
        createdBy: input.userId,
        id: Id.generate('org'),
        name: input.name,
        sponsorshipSubsidyDurationDays: null,
        sponsorshipSubsidyProjectSpendLimit: null,
        updatedAt: now,
        userId: input.userId,
      })
      .returningAll()
      .executeTakeFirstOrThrow()
    await trx
      .insertInto('memberships')
      .values({
        createdAt: now,
        orgId: record.id,
        role: 'owner',
        updatedAt: now,
        userId: input.userId,
      })
      .execute()
    if (input.enabledBillingSources?.length)
      await trx
        .insertInto('enabled_billing_sources')
        .values(
          input.enabledBillingSources.map((source) => ({
            createdAt: now,
            createdBy: input.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
  }
}

/**
 * Deletes an organization and its owned rows atomically.
 *
 * @param db - The database.
 * @param id - The organization id (`org_…`).
 * @returns Whether the organization existed and was deleted.
 */
export function deleteOrganization(db: Db.Db, id: string): Promise<boolean> {
  return db.kysely.transaction().execute(async (trx) => {
    const failedSince = new Date(
      Date.now() - SponsoredTransactions.failedIntentRecoveryTtlMs,
    ).toISOString()
    const unreported = await trx
      .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 (unreported) throw new UnreportedSponsorshipsError()

    await trx
      .deleteFrom('webhook_subscriptions')
      .where('ownerType', '=', 'api_key')
      .where('ownerId', '=', id)
      .execute()
    await trx.deleteFrom('billing_settings').where('orgId', '=', id).execute()
    await trx.deleteFrom('enabled_billing_sources').where('orgId', '=', id).execute()
    await trx.deleteFrom('invitations').where('orgId', '=', id).execute()
    await trx.deleteFrom('invite_link_redemptions').where('orgId', '=', id).execute()
    await trx.deleteFrom('invite_links').where('orgId', '=', id).execute()
    await trx.deleteFrom('memberships').where('orgId', '=', id).execute()
    await trx.deleteFrom('projects').where('orgId', '=', id).execute()
    await trx.deleteFrom('request_usage_buckets').where('orgId', '=', id).execute()
    await trx.deleteFrom('request_usage_meter_events').where('orgId', '=', id).execute()
    await trx.deleteFrom('stripe_customers').where('orgId', '=', id).execute()
    const result = await trx.deleteFrom('organizations').where('id', '=', id).executeTakeFirst()
    return result.numDeletedRows > 0n
  })
}

/** Whether an organization has billable sponsorships awaiting settlement. */
export async function hasUnreportedSponsorships(db: Db.Db, id: string): Promise<boolean> {
  const failedSince = new Date(
    Date.now() - SponsoredTransactions.failedIntentRecoveryTtlMs,
  ).toISOString()
  const record = 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()
  return record !== undefined
}

/**
 * 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-project 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.projectSpendLimit,
      updatedAt: new Date().toISOString(),
    })
    .where('id', '=', id)
    .returningAll()
    .executeTakeFirst()
}

export declare namespace setSponsorshipSubsidy {
  /** Replacement organization subsidy policy. */
  type Input =
    | {
        /** Fixed promotion duration in days. */
        durationDays: 90
        /** Optional decimal USD spend cap applied independently to each project. */
        projectSpendLimit: string | null
      }
    | {
        /** Null disables the promotion. */
        durationDays: null
        /** Null disables the promotion. */
        projectSpendLimit: 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 until billable sponsorships settle. */
export class UnreportedSponsorshipsError extends Error {
  override name = 'Organizations.UnreportedSponsorshipsError'
}