import { Op } from "sequelize";
import { initDb } from "@/lib/db";
import type { Contact } from "@/lib/db/models/contact";
import type { ContactList } from "@/lib/db/models/contact-list";
import type { ContactListMember } from "@/lib/db/models/contact-list-member";

export interface ContactRow {
  id: string;
  name: string;
  email: string;
  phone: string | null;
  tags: string[];
  notes: string | null;
  lastInvitedAt: Date | null;
  inviteCount: number;
  listIds: string[];
  createdAt: Date;
}

/** All contacts for a workspace, with the list IDs they belong to. */
export async function getWorkspaceContacts(
  workspaceId: string,
  search?: string
): Promise<ContactRow[]> {
  const { Contact, ContactList } = initDb();

  const where: Record<string | symbol, unknown> = { workspaceId };
  if (search?.trim()) {
    const term = `%${search.trim()}%`;
    where[Op.or] = [
      { name: { [Op.like]: term } },
      { email: { [Op.like]: term } },
    ];
  }

  const contacts = await Contact.findAll({
    where,
    order: [["createdAt", "DESC"]],
    include: [{ model: ContactList, as: "lists", attributes: ["id"] }],
  });

  return contacts.map((c) => {
    const lists = (c.get("lists") as ContactList[] | undefined) ?? [];
    return {
      id: c.id,
      name: c.name,
      email: c.email,
      phone: c.phone,
      tags: c.tags ?? [],
      notes: c.notes,
      lastInvitedAt: c.lastInvitedAt,
      inviteCount: c.inviteCount,
      listIds: lists.map((l) => l.id),
      createdAt: c.createdAt,
    };
  });
}

export interface ContactListRow {
  id: string;
  name: string;
  description: string | null;
  contactCount: number;
  createdAt: Date;
}

/** All contact lists for a workspace with member counts. */
export async function getWorkspaceContactLists(
  workspaceId: string
): Promise<ContactListRow[]> {
  const { ContactList, ContactListMember } = initDb();

  const lists = await ContactList.findAll({
    where: { workspaceId },
    order: [["createdAt", "DESC"]],
    include: [
      {
        model: ContactListMember,
        as: "members",
        attributes: ["id"],
        required: false,
      },
    ],
  });

  return lists.map((l) => {
    const members =
      (l.get("members") as ContactListMember[] | undefined) ?? [];
    return {
      id: l.id,
      name: l.name,
      description: l.description,
      contactCount: members.length,
      createdAt: l.createdAt,
    };
  });
}

export interface ContactListDetail extends ContactListRow {
  contacts: ContactRow[];
}

/** A single contact list with its full member roster. */
export async function getContactListDetail(
  listId: string,
  workspaceId: string
): Promise<ContactListDetail | null> {
  const { ContactList, Contact } = initDb();

  const list = await ContactList.findOne({
    where: { id: listId, workspaceId },
    include: [{ model: Contact, as: "contacts" }],
  });
  if (!list) return null;

  const contacts = (list.get("contacts") as Contact[] | undefined) ?? [];

  return {
    id: list.id,
    name: list.name,
    description: list.description,
    contactCount: contacts.length,
    createdAt: list.createdAt,
    contacts: contacts.map((c) => ({
      id: c.id,
      name: c.name,
      email: c.email,
      phone: c.phone,
      tags: c.tags ?? [],
      notes: c.notes,
      lastInvitedAt: c.lastInvitedAt,
      inviteCount: c.inviteCount,
      listIds: [list.id],
      createdAt: c.createdAt,
    })),
  };
}

export interface AudienceStats {
  totalContacts: number;
  totalLists: number;
  totalInvitesSent: number;
}

export async function getAudienceStats(
  workspaceId: string
): Promise<AudienceStats> {
  const { Contact, ContactList } = initDb();

  const [totalContacts, totalLists, invitesSum] = await Promise.all([
    Contact.count({ where: { workspaceId } }),
    ContactList.count({ where: { workspaceId } }),
    Contact.sum("inviteCount", { where: { workspaceId } }),
  ]);

  return {
    totalContacts,
    totalLists,
    totalInvitesSent: invitesSum ?? 0,
  };
}

/** Active surveys available to send invites for. */
export async function getInvitableSurveys(workspaceId: string) {
  const { Survey } = initDb();
  const surveys = await Survey.findAll({
    where: { workspaceId, status: "active" },
    order: [["createdAt", "DESC"]],
    attributes: ["id", "title"],
  });
  return surveys.map((s) => ({ id: s.id, title: s.title }));
}