import { DataSource, EntityManager } from "typeorm";

/**
 * Where a customer-facing SMS actually goes.
 *
 * The person who signs up, logs in, and receives OTPs is an IAM user, and
 * `iam.users.phone_number` is the number they control and can change themselves
 * (see the account settings flow). A company's own `phone` is business contact
 * data — often a landline, a shared desk, or a stale eTrade import — so it is
 * the fallback, not the source.
 *
 * `companies.contact_person_phone` is deliberately NOT consulted: the live write
 * path stores that value in the `attributes` jsonb and has never populated the
 * column, so every reader of it was silently falling through to `phone` anyway.
 */

/**
 * LEFT JOIN a company alias to its primary contact's IAM user, exposing
 * `pc.phone_number`.
 *
 * LATERAL + LIMIT 1 rather than a plain join: nothing in the schema stops a
 * company having two `is_primary_contact` rows, and a plain join would then
 * duplicate the company row — which in a fan-out query means sending the same
 * customer the same SMS twice.
 *
 * `alias` is always a code-controlled literal, never caller input.
 */
export function primaryContactUserJoin(alias: string): string {
  return `
    LEFT JOIN LATERAL (
      SELECT u.phone_number
        FROM freight.external_profiles ep
        JOIN iam.users u ON u.id = ep.user_id AND u.is_active = true
       WHERE ep.company_id = ${alias}.id
         AND ep.is_primary_contact = true
         AND ep.deleted_at IS NULL
       ORDER BY ep.created_at
       LIMIT 1
    ) pc ON true`;
}

/** SQL expression for the company's SMS number, given the joined `pc` alias. */
export function companyNotifyPhoneExpr(alias: string): string {
  return `COALESCE(pc.phone_number, ${alias}.phone)`;
}

/** The SMS number for one company, or null when neither source has one. */
export async function resolveCompanyNotifyPhone(
  db: DataSource | EntityManager,
  companyId: string,
): Promise<string | null> {
  const rows: Array<{ phone: string | null }> = await db.query(
    `SELECT ${companyNotifyPhoneExpr("co")} AS phone
       FROM freight.companies co
       ${primaryContactUserJoin("co")}
      WHERE co.id = $1 AND co.deleted_at IS NULL`,
    [companyId],
  );
  return rows[0]?.phone ?? null;
}
