import { db } from "@/db";
import {
  clients,
  projects,
  payments,
  phases,
  tasks,
  deliverables,
  comments,
  documents,
  notes,
  time_entries,
  quote_items,
  service_catalog,
  services,
  settings,
  offer_micros,
  offer_macros,
  project_offers,
  offer_phases,
  offer_phase_services,
  quotes,
  leads,
} from "@/db/schema";
import { eq, inArray, asc, isNull, sql, and } from "drizzle-orm";
import type {
  Client,
  Project,
  Phase,
  Task,
  Deliverable,
  Payment,
  Document,
  Note,
  Comment,
  ServiceCatalog,
  Service,
  OfferMicro,
  OfferMacro,
  ProjectOffer,
  OfferPhase,
  OfferPhaseService,
  Quote,
  Lead,
} from "@/db/schema";

// ── ClientWithPayments — used by /admin clients list ─────────────────────────

export type ClientWithPayments = {
  id: string;
  name: string;
  brand_name: string;
  token: string;
  slug: string | null;
  accepted_total: string;
  archived: boolean;
  created_at: Date;
  payments: Array<{ id: string; label: string; status: string; amount: string }>;
  activeTimerEntryId: string | null;
  activeTimerStartedAt: Date | null;
  totalTrackedSeconds: number;
  projectBrands: string[];
  ltv: string;
};

export async function getAllClientsWithPayments(
  includeArchived = false
): Promise<ClientWithPayments[]> {
  const allClients = await db
    .select()
    .from(clients)
    .orderBy(clients.created_at);

  const visible = includeArchived
    ? allClients
    : allClients.filter((c) => !c.archived);

  if (visible.length === 0) return [];

  const clientIds = visible.map((c) => c.id);

  // Get all projects for these clients (payments/time_entries now scoped to project)
  const clientProjects = await db
    .select({
      id: projects.id,
      client_id: projects.client_id,
      name: projects.name,
      accepted_total: projects.accepted_total,
      archived: projects.archived,
    })
    .from(projects)
    .where(inArray(projects.client_id, clientIds))
    .orderBy(asc(projects.created_at));

  const projectIds = clientProjects.map((p) => p.id);
  const projectToClient = new Map(clientProjects.map((p) => [p.id, p.client_id]));

  if (projectIds.length === 0) {
    return visible.map((c) => ({
      id: c.id,
      name: c.name,
      brand_name: c.brand_name,
      token: c.token,
      slug: c.slug ?? null,
      accepted_total: c.accepted_total ?? "0",
      archived: c.archived ?? false,
      created_at: c.created_at,
      payments: [],
      activeTimerEntryId: null,
      activeTimerStartedAt: null,
      totalTrackedSeconds: 0,
      projectBrands: [],
      ltv: "0.00",
    }));
  }

  const [allPayments, activeEntries, totals] = await Promise.all([
    db.select().from(payments).where(inArray(payments.project_id, projectIds)),

    db
      .select({
        id: time_entries.id,
        project_id: time_entries.project_id,
        started_at: time_entries.started_at,
      })
      .from(time_entries)
      .where(isNull(time_entries.ended_at)),

    db
      .select({
        project_id: time_entries.project_id,
        total: sql<string>`coalesce(sum(${time_entries.duration_seconds}), 0)`,
      })
      .from(time_entries)
      .where(inArray(time_entries.project_id, projectIds))
      .groupBy(time_entries.project_id),
  ]);

  // Aggregate project-scoped data back to client level
  const clientPaymentsMap = new Map<string, typeof allPayments>();
  for (const payment of allPayments) {
    const clientId = projectToClient.get(payment.project_id);
    if (!clientId) continue;
    if (!clientPaymentsMap.has(clientId)) clientPaymentsMap.set(clientId, []);
    clientPaymentsMap.get(clientId)!.push(payment);
  }

  const clientTimerMap = new Map<string, { id: string; started_at: Date }>();
  for (const entry of activeEntries) {
    const clientId = projectToClient.get(entry.project_id);
    if (!clientId || clientTimerMap.has(clientId)) continue;
    clientTimerMap.set(clientId, { id: entry.id, started_at: entry.started_at });
  }

  const clientTotalsMap = new Map<string, number>();
  for (const row of totals) {
    const clientId = projectToClient.get(row.project_id);
    if (!clientId) continue;
    clientTotalsMap.set(clientId, (clientTotalsMap.get(clientId) ?? 0) + parseInt(row.total));
  }

  return visible.map((c) => {
    const active = clientTimerMap.get(c.id);
    const clientPayments = clientPaymentsMap.get(c.id) ?? [];
    const clientProjectsList = clientProjects.filter((p) => p.client_id === c.id);
    const projectBrands = clientProjectsList
      .filter((p) => !p.archived)
      .map((p) => p.name);
    const ltv = clientProjectsList
      .reduce((sum, p) => sum + parseFloat(p.accepted_total ?? "0"), 0)
      .toFixed(2);
    return {
      id: c.id,
      name: c.name,
      brand_name: c.brand_name,
      token: c.token,
      slug: c.slug ?? null,
      accepted_total: c.accepted_total ?? "0",
      archived: c.archived ?? false,
      created_at: c.created_at,
      payments: clientPayments.map((p) => ({
        id: p.id,
        label: p.label,
        status: p.status,
        amount: String(p.amount),
      })),
      activeTimerEntryId: active?.id ?? null,
      activeTimerStartedAt: active?.started_at ?? null,
      totalTrackedSeconds: clientTotalsMap.get(c.id) ?? 0,
      projectBrands,
      ltv,
    };
  });
}

export async function getClientById(id: string) {
  const rows = await db.select().from(clients).where(eq(clients.id, id)).limit(1);
  return rows[0] ?? null;
}

// ── ProjectOfferWithMicro — used by OffersTab ────────────────────────────────

export type ProjectOfferWithMicro = {
  id: string;
  project_id: string;
  micro_id: string;
  micro_internal_name: string;
  micro_public_name: string;
  micro_duration_months: number;
  start_date: Date;
  accepted_total: string | null;
  created_at: Date;
};

// ── ClientFullDetail — used by /admin/clients/[id] workspace ─────────────────

// quote_items NEVER exposed via client API — admin workspace query only
export type QuoteItemWithLabel = {
  id: string;
  label: string;        // COALESCE(service_catalog.name, quote_items.custom_label)
  custom_label: string | null;
  service_id: string | null;
  quantity: string;
  unit_price: string;   // snapshotted — never joined back to service_catalog.unit_price
  subtotal: string;
};

export type ClientFullDetail = {
  client: Client;
  phases: Array<Phase & { tasks: Array<Task & { deliverables: Deliverable[] }> }>;
  payments: Payment[];
  documents: Document[];
  notes: Note[];
  comments: Comment[];
  quoteItems: QuoteItemWithLabel[];
  activeServices: Service[];
};

export async function getClientFullDetail(id: string): Promise<ClientFullDetail | null> {
  const clientRows = await db.select().from(clients).where(eq(clients.id, id)).limit(1);
  if (clientRows.length === 0) return null;
  const client = clientRows[0];

  // Get all projects for this client (data is now project-scoped)
  const projectRows = await db
    .select({ id: projects.id })
    .from(projects)
    .where(eq(projects.client_id, id));
  const projectIds = projectRows.map((p) => p.id);

  const activeServiceRows = await db
    .select()
    .from(services)
    .where(eq(services.active, true))
    .orderBy(asc(services.name));

  if (projectIds.length === 0) {
    return {
      client,
      phases: [],
      payments: [],
      documents: [],
      notes: [],
      comments: [],
      quoteItems: [],
      activeServices: activeServiceRows,
    };
  }

  const phasesRows = await db
    .select()
    .from(phases)
    .where(inArray(phases.project_id, projectIds))
    .orderBy(asc(phases.sort_order));

  const phaseIds = phasesRows.map((p) => p.id);

  const tasksRows =
    phaseIds.length === 0
      ? []
      : await db
          .select()
          .from(tasks)
          .where(inArray(tasks.phase_id, phaseIds))
          .orderBy(asc(tasks.sort_order));

  const taskIds = tasksRows.map((t) => t.id);

  const deliverablesRows =
    taskIds.length === 0
      ? []
      : await db.select().from(deliverables).where(inArray(deliverables.task_id, taskIds));

  const paymentsRows = await db
    .select()
    .from(payments)
    .where(inArray(payments.project_id, projectIds));

  const documentsRows = await db
    .select()
    .from(documents)
    .where(inArray(documents.project_id, projectIds))
    .orderBy(asc(documents.created_at));

  const notesRows = await db
    .select()
    .from(notes)
    .where(inArray(notes.project_id, projectIds))
    .orderBy(asc(notes.created_at));

  const allEntityIds = [id, ...taskIds, ...deliverablesRows.map((d) => d.id)];
  const commentsRows =
    allEntityIds.length === 0
      ? []
      : await db
          .select()
          .from(comments)
          .where(inArray(comments.entity_id, allEntityIds))
          .orderBy(asc(comments.created_at));

  const phasesWithTasks = phasesRows.map((phase) => ({
    ...phase,
    tasks: tasksRows
      .filter((t) => t.phase_id === phase.id)
      .map((task) => ({
        ...task,
        deliverables: deliverablesRows.filter((d) => d.task_id === task.id),
      })),
  }));

  // quote_items NEVER exposed via client API — admin workspace query only
  const quoteItemRows: QuoteItemWithLabel[] = await db
    .select({
      id: quote_items.id,
      label: sql<string>`COALESCE(${service_catalog.name}, ${quote_items.custom_label})`,
      custom_label: quote_items.custom_label,
      service_id: quote_items.service_id,
      quantity: quote_items.quantity,
      unit_price: quote_items.unit_price,
      subtotal: quote_items.subtotal,
    })
    .from(quote_items)
    .leftJoin(service_catalog, eq(quote_items.service_id, service_catalog.id))
    .where(inArray(quote_items.project_id, projectIds))
    .orderBy(asc(quote_items.id));

  return {
    client,
    phases: phasesWithTasks,
    payments: paymentsRows,
    documents: documentsRows,
    notes: notesRows,
    comments: commentsRows,
    quoteItems: quoteItemRows,
    activeServices: activeServiceRows,
  };
}

export async function getAllServices(): Promise<Service[]> {
  return db
    .select()
    .from(services)
    .orderBy(asc(services.name));
}

// ── ProjectWithPayments — used by /admin/projects list ───────────────────────

export type ProjectWithPayments = {
  id: string;
  name: string;
  client: { id: string; name: string; slug: string | null };
  accepted_total: string;
  archived: boolean;
  created_at: Date;
  payments: Array<{ id: string; label: string; status: string; amount: string }>;
  activeTimerEntryId: string | null;
  activeTimerStartedAt: Date | null;
  totalTrackedSeconds: number;
};

export async function getAllProjectsWithPayments(
  includeArchived = false
): Promise<ProjectWithPayments[]> {
  const allProjects = await db
    .select({
      id: projects.id,
      name: projects.name,
      client_id: projects.client_id,
      accepted_total: projects.accepted_total,
      archived: projects.archived,
      created_at: projects.created_at,
    })
    .from(projects)
    .orderBy(projects.created_at);

  const visible = includeArchived
    ? allProjects
    : allProjects.filter((p) => !p.archived);

  if (visible.length === 0) return [];

  const projectIds = visible.map((p) => p.id);
  const clientIds = [...new Set(visible.map((p) => p.client_id))];

  const [allPayments, activeEntries, totals, parentClients] = await Promise.all([
    db
      .select()
      .from(payments)
      .where(inArray(payments.project_id, projectIds)),

    db
      .select({
        id: time_entries.id,
        project_id: time_entries.project_id,
        started_at: time_entries.started_at,
      })
      .from(time_entries)
      .where(isNull(time_entries.ended_at)),

    db
      .select({
        project_id: time_entries.project_id,
        total: sql<string>`coalesce(sum(${time_entries.duration_seconds}), 0)`,
      })
      .from(time_entries)
      .where(inArray(time_entries.project_id, projectIds))
      .groupBy(time_entries.project_id),

    db
      .select({ id: clients.id, name: clients.name, slug: clients.slug })
      .from(clients)
      .where(inArray(clients.id, clientIds)),
  ]);

  return visible.map((project) => {
    const projectPayments = allPayments.filter((p) => p.project_id === project.id);
    const activeEntry = activeEntries.find((e) => e.project_id === project.id);
    const totalRow = totals.find((t) => t.project_id === project.id);
    const parentClient = parentClients.find((c) => c.id === project.client_id);

    return {
      id: project.id,
      name: project.name,
      client: parentClient ?? { id: project.client_id, name: "—", slug: null },
      accepted_total: project.accepted_total ?? "0",
      archived: project.archived,
      created_at: project.created_at,
      payments: projectPayments.map((p) => ({
        id: p.id,
        label: p.label,
        status: p.status,
        amount: String(p.amount),
      })),
      activeTimerEntryId: activeEntry?.id ?? null,
      activeTimerStartedAt: activeEntry?.started_at ?? null,
      totalTrackedSeconds: totalRow ? parseInt(totalRow.total) : 0,
    };
  });
}

// ── ProjectFullDetail — used by /admin/projects/[id] workspace ───────────────

export type ProjectFullDetail = {
  project: Project & { client: { id: string; name: string; brand_name: string; slug: string | null } };
  phases: Array<Phase & { tasks: Array<Task & { deliverables: Deliverable[] }> }>;
  payments: Payment[];
  documents: Document[];
  notes: Note[];
  comments: Comment[];
  quoteItems: QuoteItemWithLabel[];
  activeServices: Service[];
  activeTimerEntryId: string | null;
  activeTimerStartedAt: Date | null;
  totalTrackedSeconds: number;
  projectOffers: ProjectOfferWithMicro[];
  availableMicros: Array<{ id: string; internal_name: string; public_name: string; duration_months: number }>;
};

export async function getProjectFullDetail(id: string): Promise<ProjectFullDetail | null> {
  const projectRows = await db
    .select()
    .from(projects)
    .where(eq(projects.id, id))
    .limit(1);

  if (projectRows.length === 0) return null;
  const project = projectRows[0];

  const clientRows = await db
    .select({ id: clients.id, name: clients.name, brand_name: clients.brand_name, slug: clients.slug })
    .from(clients)
    .where(eq(clients.id, project.client_id))
    .limit(1);
  const client = clientRows[0] ?? { id: project.client_id, name: "—", brand_name: "—", slug: null };

  const phasesRows = await db
    .select()
    .from(phases)
    .where(eq(phases.project_id, id))
    .orderBy(asc(phases.sort_order));

  const phaseIds = phasesRows.map((p) => p.id);

  const tasksRows =
    phaseIds.length === 0
      ? []
      : await db
          .select()
          .from(tasks)
          .where(inArray(tasks.phase_id, phaseIds))
          .orderBy(asc(tasks.sort_order));

  const taskIds = tasksRows.map((t) => t.id);

  const deliverablesRows =
    taskIds.length === 0
      ? []
      : await db
          .select()
          .from(deliverables)
          .where(inArray(deliverables.task_id, taskIds));

  const [paymentsRows, documentsRows, notesRows, quoteItemRows, activeServiceRows, activeEntryRows, totalRes, projectOffersRows, availableMicrosRows] =
    await Promise.all([
      db.select().from(payments).where(eq(payments.project_id, id)),
      db.select().from(documents).where(eq(documents.project_id, id)).orderBy(asc(documents.created_at)),
      db.select().from(notes).where(eq(notes.project_id, id)).orderBy(asc(notes.created_at)),
      db
        .select({
          id: quote_items.id,
          label: sql<string>`COALESCE(${service_catalog.name}, ${quote_items.custom_label})`,
          custom_label: quote_items.custom_label,
          service_id: quote_items.service_id,
          quantity: quote_items.quantity,
          unit_price: quote_items.unit_price,
          subtotal: quote_items.subtotal,
        })
        .from(quote_items)
        .leftJoin(service_catalog, eq(quote_items.service_id, service_catalog.id))
        .where(eq(quote_items.project_id, id))
        .orderBy(asc(quote_items.id)),
      db.select().from(services).where(eq(services.active, true)).orderBy(asc(services.name)),
      db
        .select({ id: time_entries.id, started_at: time_entries.started_at })
        .from(time_entries)
        .where(and(eq(time_entries.project_id, id), isNull(time_entries.ended_at)))
        .limit(1),
      db
        .select({ total: sql<string>`coalesce(sum(${time_entries.duration_seconds}), 0)` })
        .from(time_entries)
        .where(eq(time_entries.project_id, id)),
      // Query A: project offers for this project joined with micro info
      db
        .select({
          id: project_offers.id,
          project_id: project_offers.project_id,
          micro_id: project_offers.micro_id,
          micro_internal_name: offer_micros.internal_name,
          micro_public_name: offer_micros.public_name,
          micro_duration_months: offer_micros.duration_months,
          start_date: project_offers.start_date,
          accepted_total: project_offers.accepted_total,
          created_at: project_offers.created_at,
        })
        .from(project_offers)
        .innerJoin(offer_micros, eq(project_offers.micro_id, offer_micros.id))
        .where(eq(project_offers.project_id, id))
        .orderBy(asc(project_offers.created_at)),
      // Query B: all active micro-offers (for the assignment dropdown)
      db
        .select({
          id: offer_micros.id,
          internal_name: offer_micros.internal_name,
          public_name: offer_micros.public_name,
          duration_months: offer_micros.duration_months,
        })
        .from(offer_micros)
        .orderBy(asc(offer_micros.internal_name)),
    ]);

  const allEntityIds = [id, ...taskIds, ...deliverablesRows.map((d) => d.id)];
  const commentsRows =
    allEntityIds.length === 0
      ? []
      : await db
          .select()
          .from(comments)
          .where(inArray(comments.entity_id, allEntityIds))
          .orderBy(asc(comments.created_at));

  const phasesWithTasks = phasesRows.map((phase) => ({
    ...phase,
    tasks: tasksRows
      .filter((t) => t.phase_id === phase.id)
      .map((task) => ({
        ...task,
        deliverables: deliverablesRows.filter((d) => d.task_id === task.id),
      })),
  }));

  return {
    project: { ...project, client } as ProjectFullDetail["project"],
    phases: phasesWithTasks,
    payments: paymentsRows,
    documents: documentsRows,
    notes: notesRows,
    comments: commentsRows,
    quoteItems: quoteItemRows as QuoteItemWithLabel[],
    activeServices: activeServiceRows,
    activeTimerEntryId: activeEntryRows[0]?.id ?? null,
    activeTimerStartedAt: activeEntryRows[0]?.started_at ?? null,
    totalTrackedSeconds: totalRes[0] ? parseInt(totalRes[0].total) : 0,
    projectOffers: projectOffersRows as ProjectOfferWithMicro[],
    availableMicros: availableMicrosRows,
  };
}

// ── ClientWithProjects — used by client detail showing project cards ──────────

export type ClientWithProjects = Client & {
  projects: Array<{
    id: string;
    name: string;
    accepted_total: string;
    archived: boolean;
    created_at: Date;
  }>;
};

export async function getClientWithProjects(clientId: string): Promise<ClientWithProjects | null> {
  const clientRows = await db
    .select()
    .from(clients)
    .where(eq(clients.id, clientId))
    .limit(1);

  if (clientRows.length === 0) return null;
  const client = clientRows[0];

  const projectRows = await db
    .select()
    .from(projects)
    .where(eq(projects.client_id, clientId))
    .orderBy(asc(projects.created_at));

  return {
    ...client,
    projects: projectRows.map((p) => ({
      id: p.id,
      name: p.name,
      accepted_total: p.accepted_total ?? "0",
      archived: p.archived,
      created_at: p.created_at,
    })),
  };
}

// ── ClientActiveOfferSummary — used by /admin/clients/[id] offers section ─────

export type ClientActiveOfferSummary = {
  offer_id: string;
  project_id: string;
  project_name: string;
  public_name: string;        // offer_micros.public_name — NEVER internal_name
  accepted_total: string | null;
};

export async function getClientActiveOffers(clientId: string): Promise<ClientActiveOfferSummary[]> {
  const projectRows = await db
    .select({ id: projects.id, name: projects.name })
    .from(projects)
    .where(eq(projects.client_id, clientId));

  if (projectRows.length === 0) return [];

  const projectIds = projectRows.map((p) => p.id);
  const projectNameMap = new Map(projectRows.map((p) => [p.id, p.name]));

  // Explicit column selection — public_name only, NEVER internal_name
  const rows = await db
    .select({
      offer_id: project_offers.id,
      project_id: project_offers.project_id,
      public_name: offer_micros.public_name,
      accepted_total: project_offers.accepted_total,
    })
    .from(project_offers)
    .innerJoin(offer_micros, eq(project_offers.micro_id, offer_micros.id))
    .where(inArray(project_offers.project_id, projectIds))
    .orderBy(asc(project_offers.created_at));

  return rows.map((r) => ({
    offer_id: r.offer_id,
    project_id: r.project_id,
    project_name: projectNameMap.get(r.project_id) ?? "—",
    public_name: r.public_name,
    accepted_total: r.accepted_total ? String(r.accepted_total) : null,
  }));
}

// ── Phase 8 Query Layer: Offer Phases & Quotes ──────────────────────────────

/**
 * Get all phases for a given offer micro (ordered by sort_order)
 * Used by: Quote builder, admin phase editor
 */
export async function getOfferPhases(microId: string): Promise<OfferPhase[]> {
  return await db
    .select()
    .from(offer_phases)
    .where(eq(offer_phases.micro_id, microId))
    .orderBy(asc(offer_phases.sort_order));
}

/**
 * Get all unified services assigned to a phase
 * Used by: Quote builder, public quote page
 */
export async function getOfferPhaseServices(phaseId: string): Promise<Service[]> {
  const phaseServices = await db
    .select({
      service_id: offer_phase_services.service_id,
    })
    .from(offer_phase_services)
    .where(eq(offer_phase_services.phase_id, phaseId));

  if (phaseServices.length === 0) return [];

  const serviceIds = phaseServices.map((ps) => ps.service_id);

  return await db
    .select()
    .from(services)
    .where(inArray(services.id, serviceIds));
}

/**
 * Get full quote by ID with all relations (header + items)
 * Used by: Quote detail page
 */
export async function getQuoteById(quoteId: string): Promise<Quote | null> {
  const quote = await db
    .select()
    .from(quotes)
    .where(eq(quotes.id, quoteId));

  return quote.length > 0 ? quote[0] : null;
}

/**
 * Get quote by public token (for /quote/[token] routes)
 * Used by: Public quote page access validation
 */
export async function getQuoteByToken(token: string): Promise<Quote | null> {
  const quote = await db
    .select()
    .from(quotes)
    .where(eq(quotes.token, token));

  return quote.length > 0 ? quote[0] : null;
}

/**
 * Get all quotes for a client (ordered by created_at DESC)
 * Used by: /admin/clients/[id] detail page
 */
export async function getQuotesByClient(clientId: string): Promise<Quote[]> {
  return await db
    .select()
    .from(quotes)
    .where(eq(quotes.client_id, clientId))
    .orderBy(sql`${quotes.created_at} DESC`);
}

/**
 * Get all offer macros with their micros (ordered by sort_order)
 * Used by: /admin/quotes/new form (OfferSelector dropdown)
 */
export async function getAllOfferMacrosWithMicros(): Promise<
  (OfferMacro & { micros: OfferMicro[] })[]
> {
  const macros = await db
    .select()
    .from(offer_macros)
    .orderBy(asc(offer_macros.sort_order));

  if (macros.length === 0) return [];

  const allMicros = await db.select().from(offer_micros);

  return macros.map((macro) => ({
    ...macro,
    micros: allMicros
      .filter((micro) => micro.macro_id === macro.id)
      .sort((a, b) => a.sort_order - b.sort_order),
  }));
}