import type { Selectable, Transaction } from 'kysely'

import type * as Db from '../Db.js'
import type * as db_Schema from '../Schema.js'

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

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

/** A membership with the member's resolved user identity. */
export type DetailedRecord = Record & { address: string | null; email: string | null }

/** A membership role. */
export type Role = Record['role']

/** Fixed membership roles, least to most privileged. */
export const roles = ['member', 'admin', 'owner'] as const satisfies readonly Role[]

/** Privilege rank per role; higher ranks satisfy lower minimums. */
export const rank = { admin: 1, member: 0, owner: 2 } as const satisfies { [role in Role]: number }

/**
 * Inserts a membership and returns the stored record.
 *
 * @param db - The database.
 * @param input - The membership 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('memberships')
    .values({
      createdAt: now,
      orgId: input.orgId,
      role: input.role,
      updatedAt: now,
      userId: input.userId,
    })
    .returningAll()
    .executeTakeFirstOrThrow()
}

export declare namespace create {
  /** Fields accepted when inserting a membership. */
  type Input = {
    /** Organization id (`org_…`). */
    orgId: string
    /** Granted role. */
    role: Role
    /** Member user id (`usr_…`). */
    userId: string
  }
}

/**
 * Reads one user's membership in an organization.
 *
 * @param db - The database.
 * @param orgId - The organization id (`org_…`).
 * @param userId - The member user id (`usr_…`).
 * @returns The record, or `undefined` when absent.
 */
export function get(db: Db.Db, orgId: string, userId: string): Promise<Record | undefined> {
  return db.kysely
    .selectFrom('memberships')
    .selectAll()
    .where('orgId', '=', orgId)
    .where('userId', '=', userId)
    .executeTakeFirst()
}

/**
 * Checks whether a user holds any organization membership.
 *
 * @param db - The database.
 * @param userId - The member user id (`usr_…`).
 * @returns Whether a membership exists.
 */
export async function existsForUser(db: Db.Db, userId: string): Promise<boolean> {
  const membership = await db.kysely
    .selectFrom('memberships')
    .select('userId')
    .where('userId', '=', userId)
    .executeTakeFirst()
  return membership !== undefined
}

/**
 * Lists an organization's memberships, oldest first (stable member ordering).
 *
 * @param db - The database.
 * @param orgId - The organization id (`org_…`).
 * @returns The records.
 */
export function listByOrg(db: Db.Db, orgId: string): Promise<Record[]> {
  return db.kysely
    .selectFrom('memberships')
    .selectAll()
    .where('orgId', '=', orgId)
    .orderBy('createdAt', 'asc')
    .execute()
}

/**
 * Lists an organization's memberships joined with member identity (address,
 * email), oldest first.
 *
 * @param db - The database.
 * @param orgId - The organization id (`org_…`).
 * @returns The records with `address`/`email` from the member's user row.
 */
export function listByOrgDetailed(db: Db.Db, orgId: string): Promise<DetailedRecord[]> {
  return db.kysely
    .selectFrom('memberships')
    .innerJoin('users', 'users.id', 'memberships.userId')
    .where('memberships.orgId', '=', orgId)
    .selectAll('memberships')
    .select(['users.address', 'users.email'])
    .orderBy('memberships.createdAt', 'asc')
    .execute()
}

/**
 * Lists a bounded page of an organization's memberships with member identity,
 * oldest first.
 *
 * @param db - The database.
 * @param orgId - The organization id.
 * @param options - Keyset and page-size options.
 * @returns At most `limit + 1` rows for next-cursor derivation.
 */
export function listByOrgDetailedPage(
  db: Db.Db,
  orgId: string,
  options: listByOrgDetailedPage.Options,
): Promise<DetailedRecord[]> {
  const cursor = options.cursor
  let query = db.kysely
    .selectFrom('memberships')
    .innerJoin('users', 'users.id', 'memberships.userId')
    .where('memberships.orgId', '=', orgId)
    .selectAll('memberships')
    .select(['users.address', 'users.email'])
  if (cursor)
    query = query.where((eb) =>
      eb.or([
        eb('memberships.createdAt', '>', cursor.createdAt),
        eb.and([
          eb('memberships.createdAt', '=', cursor.createdAt),
          eb('memberships.userId', '>', cursor.userId),
        ]),
      ]),
    )
  return query
    .orderBy('memberships.createdAt', 'asc')
    .orderBy('memberships.userId', 'asc')
    .limit(options.limit + 1)
    .execute()
}

export declare namespace listByOrgDetailedPage {
  /** Cursor fields for the last membership on the previous page. */
  type Cursor = {
    /** Membership creation time. */
    createdAt: string
    /** Member user id, used as a deterministic tie-breaker. */
    userId: string
  }

  /** Options for {@link listByOrgDetailedPage}. */
  type Options = {
    /** Last membership returned by the previous page. */
    cursor?: Cursor | undefined
    /** Requested page size. */
    limit: number
  }
}

/**
 * Changes a member's role atomically, refusing to demote the last owner. The
 * org's owner rows are locked for the transaction, so a concurrent demotion or
 * removal cannot race two guards into leaving the organization ownerless.
 *
 * @param db - The database.
 * @param orgId - The organization id (`org_…`).
 * @param userId - The member user id (`usr_…`).
 * @param input - The fields to update.
 * @returns The updated record, or an `error` when absent or the last owner.
 */
export function update(
  db: Db.Db,
  orgId: string,
  userId: string,
  input: update.Input,
): Promise<update.Result> {
  const now = new Date().toISOString()
  return db.kysely.transaction().execute(async (trx) => {
    const owners = await trx
      .selectFrom('memberships')
      .select('userId')
      .where('orgId', '=', orgId)
      .where('role', '=', 'owner')
      .forUpdate()
      .execute()
    const current = await trx
      .selectFrom('memberships')
      .innerJoin('users', 'users.id', 'memberships.userId')
      .selectAll('memberships')
      .select('users.email')
      .where('orgId', '=', orgId)
      .where('userId', '=', userId)
      .executeTakeFirst()
    if (!current) return { error: 'not_found' as const }
    if (current.role === 'owner' && input.role !== 'owner' && owners.length <= 1)
      return { error: 'last_owner' as const }
    const record = await trx
      .updateTable('memberships')
      .set({ role: input.role, updatedAt: now })
      .where('orgId', '=', orgId)
      .where('userId', '=', userId)
      .returningAll()
      .executeTakeFirstOrThrow()
    if (rank[input.role] < rank[current.role])
      await revokePendingInvitations(trx, {
        email: current.email,
        invitedBy: userId,
        now,
        orgId,
        ...(input.role === 'admin' ? { roleAbove: input.role } : {}),
      })
    return { record }
  })
}

export declare namespace update {
  /** Mutable membership fields. */
  type Input = {
    /** New role. */
    role: Role
  }

  /** Outcome of a guarded role change. */
  type Result =
    | { error: 'last_owner' | 'not_found'; record?: undefined }
    | { error?: undefined; record: Record }
}

/**
 * Deletes a membership atomically, refusing to remove the last owner. Locks
 * the org's owner rows for the transaction (see {@link update}) so concurrent
 * removals cannot race the organization ownerless.
 *
 * @param db - The database.
 * @param orgId - The organization id (`org_…`).
 * @param userId - The member user id (`usr_…`).
 * @returns `removed` on success, otherwise why the removal was refused.
 */
export function remove(db: Db.Db, orgId: string, userId: string): Promise<remove.Result> {
  return db.kysely.transaction().execute(async (trx) => {
    const owners = await trx
      .selectFrom('memberships')
      .select('userId')
      .where('orgId', '=', orgId)
      .where('role', '=', 'owner')
      .forUpdate()
      .execute()
    const current = await trx
      .selectFrom('memberships')
      .innerJoin('users', 'users.id', 'memberships.userId')
      .selectAll('memberships')
      .select('users.email')
      .where('orgId', '=', orgId)
      .where('userId', '=', userId)
      .executeTakeFirst()
    if (!current) return 'not_found'
    if (current.role === 'owner' && owners.length <= 1) return 'last_owner'
    await trx
      .deleteFrom('memberships')
      .where('orgId', '=', orgId)
      .where('userId', '=', userId)
      .execute()
    await revokePendingInvitations(trx, {
      email: current.email,
      invitedBy: userId,
      now: new Date().toISOString(),
      orgId,
    })
    return 'removed'
  })
}

export declare namespace remove {
  /** Outcome of a guarded removal. */
  type Result = 'last_owner' | 'not_found' | 'removed'
}

function revokePendingInvitations(
  db: Transaction<Db.Database>,
  options: revokePendingInvitations.Options,
) {
  let query = db
    .updateTable('invitations')
    .set({ revokedAt: options.now })
    .where('acceptedAt', 'is', null)
    .where('orgId', '=', options.orgId)
    .where('revokedAt', 'is', null)
    .where((eb) =>
      eb.or([
        eb('invitedBy', '=', options.invitedBy),
        ...(options.email ? [eb('email', '=', options.email.toLowerCase())] : []),
      ]),
    )
  const roleAbove = options.roleAbove
  if (roleAbove !== undefined)
    query = query.where(
      'role',
      'in',
      roles.filter((role) => rank[role] > rank[roleAbove]),
    )
  return query.execute()
}

declare namespace revokePendingInvitations {
  /** Pending-invitation cleanup after a member loses authority. */
  type Options = {
    /** Email whose invitations could restore the member's former authority. */
    email: string | null
    /** User id whose issued invitations depend on the former authority. */
    invitedBy: string
    /** Revocation timestamp. */
    now: string
    /** Organization containing the membership and invitations. */
    orgId: string
    /** When present, revoke only invitations above this retained role. */
    roleAbove?: Role | undefined
  }
}