import { planOf, type PlanId, type Subscription, type SubscriptionStatus } from '@wellformed/shared';
import { config } from '../config.js';
import type { ControlDb } from './db.js';

/**
 * What a workspace is paying for.
 *
 * All of it lives on the `workspace` row: one workspace, one plan, one
 * subscription, one address the bill goes to. There is still no customer
 * *object*, because two flat tiers have nothing to model — but the billing
 * address is deliberately not an account. Accounts are global and never billed,
 * and the payer is usually a company rather than whoever holds the login.
 */
export const TRIAL_DAYS = 14;

interface BillingRow {
  plan: string;
  billing_status: string;
  trial_ends_at: string | null;
  period_end: string | null;
  cancel_at_period_end: number;
  provider: string | null;
  provider_customer_id: string | null;
  provider_subscription_id: string | null;
}

const COLUMNS =
  'plan, billing_status, trial_ends_at, period_end, cancel_at_period_end, provider, provider_customer_id, provider_subscription_id';

function toSubscription(row: BillingRow): Subscription {
  return {
    plan: planOf(row.plan).id,
    status: row.billing_status as SubscriptionStatus,
    trialEndsAt: row.trial_ends_at,
    periodEnd: row.period_end,
    cancelAtPeriodEnd: row.cancel_at_period_end === 1,
    provider: row.provider,
  };
}

export function getSubscription(control: ControlDb, workspaceId: string): Subscription | null {
  const row = control.sqlite
    .prepare<[string], BillingRow>(`SELECT ${COLUMNS} FROM workspace WHERE id = ?`)
    .get(workspaceId);
  return row ? toSubscription(row) : null;
}

/**
 * The plan whose limits actually apply right now.
 *
 * A live trial is the paid plan — the whole point of a trial is that the limits
 * are out of the way while someone decides. Everything else falls back to what
 * the workspace is on, and an expired trial falls back to free without anything
 * having to run on a timer.
 */
/**
 * The address this workspace's invoices are addressed to.
 *
 * Kept apart from `Subscription` on purpose: a workspace can have somewhere to
 * send a bill before it has anything to bill, and it keeps that address through
 * a cancellation. Null means nobody has said yet.
 */
export function getBillingEmail(control: ControlDb, workspaceId: string): string | null {
  const row = control.sqlite
    .prepare<[string], { billing_email: string | null }>(
      'SELECT billing_email FROM workspace WHERE id = ?',
    )
    .get(workspaceId);
  return row?.billing_email ?? null;
}

export function setBillingEmail(control: ControlDb, workspaceId: string, email: string): void {
  control.sqlite
    .prepare('UPDATE workspace SET billing_email = ? WHERE id = ?')
    .run(email.trim().toLowerCase(), workspaceId);
}

export function effectivePlan(subscription: Subscription | null): PlanId {
  if (!subscription) return 'free';
  if (subscription.status === 'trialing') {
    const ends = subscription.trialEndsAt ? new Date(subscription.trialEndsAt).getTime() : 0;
    return ends > Date.now() ? subscription.plan : 'free';
  }
  // A cancelled or unpaid subscription keeps its plan until the period it was
  // paid for runs out; after that it is a free workspace with a lot of rows.
  if (subscription.status === 'cancelled' || subscription.status === 'past_due') {
    const until = subscription.periodEnd ? new Date(subscription.periodEnd).getTime() : 0;
    return until > Date.now() ? subscription.plan : 'free';
  }
  return subscription.plan;
}

/**
 * Put a fresh workspace on a trial of the paid plan.
 *
 * Nothing happens on an unmetered install, and the guard lives here rather
 * than at the call sites: a trial there is a countdown to a limit that will
 * never arrive, and the banner counting it down is asking somebody to buy
 * something this install cannot sell. Every future caller gets that for free.
 */
export function startTrial(control: ControlDb, workspaceId: string, plan: PlanId = 'team'): void {
  if (!config.metered) return;
  const ends = new Date(Date.now() + TRIAL_DAYS * 24 * 60 * 60_000).toISOString();
  control.sqlite
    .prepare(
      `UPDATE workspace SET plan = ?, billing_status = 'trialing', trial_ends_at = ? WHERE id = ?`,
    )
    .run(plan, ends, workspaceId);
}

export interface SubscriptionUpdate {
  plan?: PlanId;
  status?: SubscriptionStatus;
  periodEnd?: string | null;
  cancelAtPeriodEnd?: boolean;
  provider?: string | null;
  providerCustomerId?: string | null;
  providerSubscriptionId?: string | null;
}

/** Write whatever the caller named, leaving the rest of the row alone. */
export function setSubscription(
  control: ControlDb,
  workspaceId: string,
  update: SubscriptionUpdate,
): void {
  const sets: string[] = [];
  const values: (string | number | null)[] = [];

  const put = (column: string, value: string | number | null) => {
    sets.push(`${column} = ?`);
    values.push(value);
  };

  if (update.plan !== undefined) put('plan', update.plan);
  if (update.status !== undefined) put('billing_status', update.status);
  if (update.periodEnd !== undefined) put('period_end', update.periodEnd);
  if (update.cancelAtPeriodEnd !== undefined) {
    put('cancel_at_period_end', update.cancelAtPeriodEnd ? 1 : 0);
  }
  if (update.provider !== undefined) put('provider', update.provider);
  if (update.providerCustomerId !== undefined) put('provider_customer_id', update.providerCustomerId);
  if (update.providerSubscriptionId !== undefined) {
    put('provider_subscription_id', update.providerSubscriptionId);
  }

  if (sets.length === 0) return;
  values.push(workspaceId);
  control.sqlite.prepare(`UPDATE workspace SET ${sets.join(', ')} WHERE id = ?`).run(...values);
}

export function findWorkspaceBySubscription(
  control: ControlDb,
  provider: string,
  subscriptionId: string,
): string | null {
  const row = control.sqlite
    .prepare<[string, string], { id: string }>(
      'SELECT id FROM workspace WHERE provider = ? AND provider_subscription_id = ?',
    )
    .get(provider, subscriptionId);
  return row?.id ?? null;
}

/**
 * Record a webhook, and say whether this is the first time it has been seen.
 *
 * False means "already applied" — the caller must then do nothing at all,
 * which is the entire reason this returns a boolean rather than throwing.
 */
export function recordBillingEvent(
  control: ControlDb,
  event: { id: string; provider: string; kind: string; workspaceId: string | null; payload: string },
): boolean {
  const info = control.sqlite
    .prepare(
      `INSERT OR IGNORE INTO billing_event (id, provider, kind, workspace_id, payload)
       VALUES (?, ?, ?, ?, ?)`,
    )
    .run(event.id, event.provider, event.kind, event.workspaceId, event.payload.slice(0, 20_000));
  return info.changes === 1;
}