import { sql, 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'

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

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

/**
 * Inserts a project and returns the stored record.
 *
 * @param db - The database.
 * @param input - The project 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('projects')
    .values({
      createdAt: now,
      id: Id.generate('prj'),
      name: input.name,
      orgId: input.orgId,
      sponsorshipSubsidyEndsAt: null,
      sponsorshipSubsidyStartsAt: null,
      sponsorshipSpendLimit: null,
      updatedAt: now,
    })
    .returningAll()
    .executeTakeFirstOrThrow()
}

export declare namespace create {
  /** Fields accepted when inserting a project. */
  type Input = {
    /** Human-readable project name. */
    name: string
    /** Owning organization id (`org_…`). */
    orgId: string
  }
}

/**
 * Deletes a project.
 *
 * @param db - The database.
 * @param id - The project id (`prj_…`).
 * @returns Whether the project existed and was deleted.
 */
export async function deleteProject(db: Db.Db, id: string): Promise<boolean> {
  const result = await db.kysely.deleteFrom('projects').where('id', '=', id).executeTakeFirst()
  return result.numDeletedRows > 0n
}

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

/**
 * Sets a project's sponsorship activation time once. Concurrent first
 * requests converge on the same stored timestamp.
 *
 * @param db - The database.
 * @param options - Project ownership and activation time.
 * @returns The project with its stable activation time, or `undefined` when absent.
 */
export function activateSponsorship(
  db: Db.Db,
  options: activateSponsorship.Options,
): Promise<Record | undefined> {
  return db.kysely
    .updateTable('projects')
    .set({
      sponsorshipSpendLimit: sql`case when sponsorship_subsidy_starts_at is null then ${options.spendLimit} else sponsorship_spend_limit end`,
      sponsorshipSubsidyEndsAt: sql`coalesce(sponsorship_subsidy_ends_at, ${options.endsAt})`,
      sponsorshipSubsidyStartsAt: sql`coalesce(sponsorship_subsidy_starts_at, ${options.at})`,
      updatedAt: sql`case when sponsorship_subsidy_starts_at is null then ${options.at} else updated_at end`,
    })
    .where('id', '=', options.id)
    .where('orgId', '=', options.orgId)
    .returningAll()
    .executeTakeFirst()
}

export declare namespace activateSponsorship {
  /** Project activation inputs. */
  type Options = {
    /** Activation time used only when the project has not started its window. */
    at: string
    /** End of the promotion window, stored with the first activation. */
    endsAt: string
    /** Project id (`prj_…`). */
    id: string
    /** Owning organization id (`org_…`). */
    orgId: string
    /** Optional promotion cap in fee-token base units, stored only on first activation. */
    spendLimit: string | null
  }
}

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

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

/**
 * Lists a bounded page of an organization's projects, newest 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 listByOrgPage(
  db: Db.Db,
  orgId: string,
  options: listByOrgPage.Options,
): Promise<Record[]> {
  const cursor = options.cursor
  let query = db.kysely.selectFrom('projects').selectAll().where('orgId', '=', orgId)
  if (cursor)
    query = query.where((eb) =>
      eb.or([
        eb('createdAt', '<', cursor.createdAt),
        eb.and([eb('createdAt', '=', cursor.createdAt), eb('id', '<', cursor.id)]),
      ]),
    )
  return query
    .orderBy('createdAt', 'desc')
    .orderBy('id', 'desc')
    .limit(options.limit + 1)
    .execute()
}

export declare namespace listByOrgPage {
  /** Cursor fields for the last project on the previous page. */
  type Cursor = {
    /** Project creation time. */
    createdAt: string
    /** Project id, used as a deterministic tie-breaker. */
    id: string
  }

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

/**
 * Renames a project, bumping `updatedAt`.
 *
 * @param db - The database.
 * @param id - The project id (`prj_…`).
 * @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('projects')
    .set({ name: input.name, updatedAt: new Date().toISOString() })
    .where('id', '=', id)
    .returningAll()
    .executeTakeFirst()
}

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