import type { SupabaseClient } from '@supabase/supabase-js'

export type Team = {
  id: string
  org_id: string
  owner_user_id: string
  name: string | null
  base_rate_usd: number
}

/**
 * Returns the team owned by `userId` in `orgId`, or null if they don't
 * have one. Each user has at most one team per org (unique constraint
 * in the schema).
 */
export async function getTeamForUser(
  supabase: SupabaseClient,
  orgId: string,
  userId: string,
): Promise<Team | null> {
  const { data } = await supabase
    .from('teams')
    .select('id, org_id, owner_user_id, name, base_rate_usd')
    .eq('org_id', orgId)
    .eq('owner_user_id', userId)
    .maybeSingle<{
      id: string
      org_id: string
      owner_user_id: string
      name: string | null
      base_rate_usd: number | string
    }>()
  if (!data) return null
  return {
    id: data.id,
    org_id: data.org_id,
    owner_user_id: data.owner_user_id,
    name: data.name,
    base_rate_usd: Number(data.base_rate_usd),
  }
}

/**
 * Given a team_member_id, return the base rate of that member's team.
 * Used when re-computing `billout_cost_usd` on entry edit / resolve.
 */
export async function getRateForTeamMember(
  supabase: SupabaseClient,
  teamMemberId: string,
): Promise<number | null> {
  const { data } = await supabase
    .from('team_members')
    .select('team:teams(base_rate_usd)')
    .eq('id', teamMemberId)
    .maybeSingle<{ team: { base_rate_usd: number | string } | null }>()
  if (!data?.team) return null
  return Number(data.team.base_rate_usd)
}

/**
 * Per-source-hour cost rate for a team_member. After the
 * eliminate-proportion migration this is the canonical cost basis (the
 * old team.base_rate × member.rate_proportion calc is gone). NULL when
 * cost_rate_usd is unset for that member.
 */
export async function getCostRateForTeamMember(
  supabase: SupabaseClient,
  teamMemberId: string,
): Promise<number | null> {
  const { data } = await supabase
    .from('team_members')
    .select('cost_rate_usd')
    .eq('id', teamMemberId)
    .maybeSingle<{ cost_rate_usd: number | string | null }>()
  if (!data) return null
  return data.cost_rate_usd == null ? null : Number(data.cost_rate_usd)
}

/**
 * Effective billout rate for an entry, accounting for project-level
 * overrides. Wraps the SQL helper `effective_billout_rate`.
 *
 * Precedence (matches the SQL function):
 *   1. user-specific override on the project
 *   2. project-wide override (team_member_id IS NULL)
 *   3. team_members.billout_rate_usd
 *
 * Returns NULL when nothing is set or when team_member_id is null.
 * Called by every entry-write path (import / edit / re-resolve /
 * manual create) right before computing billout_amount_usd.
 */
export async function getEffectiveBilloutRate(
  supabase: SupabaseClient,
  teamMemberId: string | null,
  projectId: string | null,
): Promise<number | null> {
  if (teamMemberId == null) return null
  const { data } = await supabase.rpc('effective_billout_rate', {
    p_team_member_id: teamMemberId,
    p_project_id: projectId,
  })
  return data == null ? null : Number(data)
}

export type TeamMemberOption = {
  id: string
  team_id: string
  team_name: string | null
  email: string
  display_name: string
  consolidate_as: string | null
  is_active: boolean
}

/**
 * All team_members the caller can see, with their team's name attached
 * for grouping in dropdowns. Used by /entries to populate the team-
 * member picker on the per-row edit form and the new-entry form.
 *
 * Returns members sorted by team, then is_active desc, then display_name.
 * The RLS policy on team_members + teams gates the rows; the caller is
 * responsible for being authenticated correctly.
 */
export async function listVisibleTeamMembers(
  supabase: SupabaseClient,
  orgId: string,
): Promise<TeamMemberOption[]> {
  const { data } = await supabase
    .from('team_members')
    .select(
      'id, team_id, email, display_name, consolidate_as, is_active, team:teams(name)',
    )
    .eq('org_id', orgId)
    .order('display_name', { ascending: true })
    .returns<
      {
        id: string
        team_id: string
        email: string
        display_name: string
        consolidate_as: string | null
        is_active: boolean
        team: { name: string | null } | null
      }[]
    >()
  return (data ?? []).map((m) => ({
    id: m.id,
    team_id: m.team_id,
    team_name: m.team?.name ?? null,
    email: m.email,
    display_name: m.display_name,
    consolidate_as: m.consolidate_as,
    is_active: m.is_active,
  }))
}

/**
 * All teams visible to the caller in the org. The teams RLS policy
 * gates which rows come back, so the caller is responsible for being
 * authenticated correctly.
 */
export async function listVisibleTeams(
  supabase: SupabaseClient,
  orgId: string,
): Promise<Team[]> {
  const { data } = await supabase
    .from('teams')
    .select('id, org_id, owner_user_id, name, base_rate_usd')
    .eq('org_id', orgId)
    .order('name', { ascending: true })
    .returns<
      {
        id: string
        org_id: string
        owner_user_id: string
        name: string | null
        base_rate_usd: number | string
      }[]
    >()
  return (data ?? []).map((t) => ({
    id: t.id,
    org_id: t.org_id,
    owner_user_id: t.owner_user_id,
    name: t.name,
    base_rate_usd: Number(t.base_rate_usd),
  }))
}