import type { Selectable } from 'kysely'

import * as Id from '../../internal/Id.js'
import type * as Db from '../Db.js'
import type * as db_Schema from '../Schema.js'
import * as Memberships from './memberships.js'

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

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

/**
 * Inserts an invitation and returns the stored record. Emails are stored
 * lowercase so matching against verified emails is case-insensitive. Re-inviting
 * a live (pending) (org, email) refreshes that row — role, inviter, expiry —
 * rather than accumulating duplicates, matching the `invitations_pending_unique`
 * partial index.
 *
 * @param db - The database.
 * @param input - The invitation 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('invitations')
    .values({
      acceptedAt: null,
      createdAt: now,
      email: input.email.toLowerCase(),
      expiresAt: input.expiresAt,
      // Keep the legacy column inert until it can be removed safely.
      grantsEarlyAccess: false,
      id: Id.generate('inv'),
      invitedBy: input.invitedBy,
      orgId: input.orgId,
      revokedAt: null,
      role: input.role,
    })
    .onConflict((oc) =>
      oc
        .columns(['orgId', 'email'])
        .where('acceptedAt', 'is', null)
        .where('revokedAt', 'is', null)
        .doUpdateSet({
          createdAt: now,
          expiresAt: input.expiresAt,
          grantsEarlyAccess: false,
          invitedBy: input.invitedBy,
          role: input.role,
        }),
    )
    .returningAll()
    .executeTakeFirstOrThrow()
}

export declare namespace create {
  /** Fields accepted when inserting an invitation. */
  type Input = {
    /** Invitee email. */
    email: string
    /** When the invitation expires (ISO 8601). */
    expiresAt: string
    /** User or API key id creating the invitation. */
    invitedBy: string
    /** Organization id (`org_…`) the invitation joins. */
    orgId: string
    /** Role granted on accept. */
    role: Memberships.Role
  }
}

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

/**
 * Lists an organization's pending invitations (unaccepted, unrevoked,
 * unexpired), newest first.
 *
 * @param db - The database.
 * @param orgId - The organization id (`org_…`).
 * @returns The records.
 */
export function listByOrg(db: Db.Db, orgId: string): Promise<Record[]> {
  return pending(db).where('orgId', '=', orgId).orderBy('createdAt', 'desc').execute()
}

/**
 * Lists pending invitations addressed to an email, joined with the inviting
 * organization's name, newest first.
 *
 * @param db - The database.
 * @param email - The invitee email (matched lowercase).
 * @returns The records with `orgName` from the organization row.
 */
export function listByEmail(db: Db.Db, email: string): Promise<(Record & { orgName: string })[]> {
  return db.kysely
    .selectFrom('invitations')
    .innerJoin('organizations', 'organizations.id', 'invitations.orgId')
    .where('invitations.acceptedAt', 'is', null)
    .where('invitations.revokedAt', 'is', null)
    .where('invitations.expiresAt', '>', new Date().toISOString())
    .where('invitations.email', '=', email.toLowerCase())
    .selectAll('invitations')
    .select('organizations.name as orgName')
    .orderBy('invitations.createdAt', 'desc')
    .execute()
}

/**
 * Revokes a pending invitation.
 *
 * @param db - The database.
 * @param id - The invitation id (`inv_…`).
 * @returns The revoked record, or `undefined` when absent or already settled.
 */
export function revoke(db: Db.Db, id: string): Promise<Record | undefined> {
  const now = new Date().toISOString()
  return db.kysely
    .updateTable('invitations')
    .set({ revokedAt: now })
    .where('id', '=', id)
    .where('acceptedAt', 'is', null)
    .where('revokedAt', 'is', null)
    .returningAll()
    .executeTakeFirst()
}

/**
 * Accepts a pending invitation into a membership, atomically: the conditional
 * update settles the invitation exactly once (double accepts lose the race).
 * A new member is inserted at the invited role; an existing member is only ever
 * elevated to it — accepting never demotes (which could strip the last owner).
 *
 * @param db - The database.
 * @param id - The invitation id (`inv_…`).
 * @param userId - The accepting user id (`usr_…`).
 * @returns The accepted record, or `undefined` when absent, settled, or expired.
 */
export function accept(db: Db.Db, id: string, userId: string): Promise<Record | undefined> {
  const now = new Date().toISOString()
  return db.kysely.transaction().execute(async (trx) => {
    const invitation = await trx
      .updateTable('invitations')
      .set({ acceptedAt: now })
      .where('id', '=', id)
      .where('acceptedAt', 'is', null)
      .where('revokedAt', 'is', null)
      .where('expiresAt', '>', now)
      .returningAll()
      .executeTakeFirst()
    if (!invitation) return undefined
    const existing = await trx
      .selectFrom('memberships')
      .selectAll()
      .where('orgId', '=', invitation.orgId)
      .where('userId', '=', userId)
      .executeTakeFirst()
    if (!existing)
      await trx
        .insertInto('memberships')
        .values({
          createdAt: now,
          orgId: invitation.orgId,
          role: invitation.role,
          updatedAt: now,
          userId,
        })
        // A concurrent accept/join could insert first; keep that membership.
        .onConflict((oc) => oc.columns(['orgId', 'userId']).doNothing())
        .execute()
    else if (Memberships.rank[invitation.role] > Memberships.rank[existing.role])
      await trx
        .updateTable('memberships')
        .set({ role: invitation.role, updatedAt: now })
        .where('orgId', '=', invitation.orgId)
        .where('userId', '=', userId)
        .execute()
    return invitation
  })
}

/** Base query selecting pending invitations (unaccepted, unrevoked, unexpired). */
function pending(db: Db.Db) {
  return db.kysely
    .selectFrom('invitations')
    .selectAll()
    .where('acceptedAt', 'is', null)
    .where('revokedAt', 'is', null)
    .where('expiresAt', '>', new Date().toISOString())
}