/**
 * All SQL for the enquiry module — the public create path (PLANNING.md §9) and
 * the admin list / detail / reply / status writes (§4b). Free functions, ids
 * minted here; multi-table writes take a trailing executor so the caller can run
 * them in a transaction (mirrors src/modules/media/media.repository.ts).
 */
import type { Knex } from 'knex';
import { db } from '../../lib/db';
import { newId } from '../../lib/ulid';

type Executor = Knex | Knex.Transaction;

export type EnquiryStatus = 'new' | 'read' | 'replied' | 'spam' | 'archived';
export type EnquirySource = 'contact' | 'genesis' | 'hive' | 'home';

// ─── public create ─────────────────────────────────────────────────────────

export interface NewEnquiryRow {
  name: string;
  phone: string;
  email: string;
  message: string;
  source: EnquirySource;
  status: 'new' | 'spam';
  spam_score: number;
  spam_reason: string | null;
  ip_hash: string | null;
  user_agent: string | null;
  referer: string | null;
}

export async function insertEnquiry(row: NewEnquiryRow): Promise<string> {
  const id = newId();
  await db('enquiries').insert({ id, ...row });
  return id;
}

export async function markNotified(id: string): Promise<void> {
  await db('enquiries').where({ id }).update({ notified_at: db.fn.now() });
}

// ─── admin: rows ───────────────────────────────────────────────────────────

interface EnquiriesRow {
  id: string;
  name: string;
  phone: string;
  email: string;
  message: string;
  source: EnquirySource;
  status: EnquiryStatus;
  spam_score: number;
  spam_reason: string | null;
  ip_hash: string | null;
  user_agent: string | null;
  referer: string | null;
  notified_at: string | null;
  created_at: string;
  updated_at: string;
}

interface EnquiryRepliesRow {
  id: string;
  enquiry_id: string;
  admin_user_id: string;
  body: string;
  delivery_status: 'pending' | 'sent' | 'failed';
  delivery_error: string | null;
  sent_at: string | null;
  created_at: string;
}

export type EnquiryRow = EnquiriesRow;

export interface ReplyWithAuthor extends EnquiryRepliesRow {
  author_name: string | null;
}

export interface EnquiryFilter {
  status?: EnquiryStatus;
  source?: EnquirySource;
  q?: string;
  from?: string;
  to?: string;
}

/**
 * Shared WHERE builder for list + count. `q` is a substring match across the
 * fields the admin search box covers — deliberately not the `ft_enq_search`
 * FULLTEXT index, which can't do infix matches (`joh`→`john`) or index short
 * phone tokens. Swap to `MATCH(name,email,message) AGAINST(? IN BOOLEAN MODE)`
 * here if row counts ever make LIKE slow.
 */
function applyFilters(q: Knex.QueryBuilder, f: EnquiryFilter): Knex.QueryBuilder {
  if (f.status) q.where('status', f.status);
  if (f.source) q.where('source', f.source);
  if (f.from) q.where('created_at', '>=', f.from);
  if (f.to) {
    const to = /^\d{4}-\d{2}-\d{2}$/.test(f.to) ? `${f.to} 23:59:59` : f.to;
    q.where('created_at', '<=', to);
  }
  if (f.q) {
    const like = `%${f.q}%`;
    q.where((w) =>
      w
        .where('name', 'like', like)
        .orWhere('email', 'like', like)
        .orWhere('phone', 'like', like)
        .orWhere('message', 'like', like),
    );
  }
  return q;
}

export function listEnquiries(
  filter: EnquiryFilter,
  page: { limit: number; offset: number; sort: 'newest' | 'oldest' },
): Promise<EnquiriesRow[]> {
  const dir = page.sort === 'oldest' ? 'asc' : 'desc';
  return applyFilters(db<EnquiriesRow>('enquiries'), filter)
    .orderBy('created_at', dir)
    .orderBy('id', dir)
    .limit(page.limit)
    .offset(page.offset);
}

export async function countEnquiries(filter: EnquiryFilter): Promise<number> {
  const row = (await applyFilters(db('enquiries'), filter).count({ n: '*' }).first()) as
    { n: number | string } | undefined;
  return Number(row?.n ?? 0);
}

export function findEnquiryById(id: string): Promise<EnquiriesRow | undefined> {
  return db<EnquiriesRow>('enquiries').where({ id }).first();
}

/** Flip `new` -> `read` on first open (§4b). No-op for any other status. */
export async function markEnquiryRead(id: string): Promise<void> {
  await db('enquiries').where({ id, status: 'new' }).update({ status: 'read' });
}

export async function updateEnquiryStatus(
  id: string,
  status: EnquiryStatus,
  exec: Executor = db,
): Promise<void> {
  await exec('enquiries').where({ id }).update({ status });
}

// ─── admin: replies ───────────────────────────────────────────────────────

export interface NewReplyRow {
  enquiry_id: string;
  admin_user_id: string;
  body: string;
}

export async function insertReply(row: NewReplyRow, exec: Executor = db): Promise<string> {
  const id = newId();
  await exec('enquiry_replies').insert({ id, ...row });
  return id;
}

export async function updateReplyDelivery(
  id: string,
  patch: { delivery_status: 'sent' | 'failed'; delivery_error?: string | null; markSent?: boolean },
): Promise<void> {
  await db('enquiry_replies')
    .where({ id })
    .update({
      delivery_status: patch.delivery_status,
      delivery_error: patch.delivery_error ?? null,
      ...(patch.markSent ? { sent_at: db.fn.now() } : {}),
    });
}

export function listRepliesWithAuthor(enquiryId: string): Promise<ReplyWithAuthor[]> {
  return db<EnquiryRepliesRow>('enquiry_replies')
    .leftJoin('admin_users', 'admin_users.id', 'enquiry_replies.admin_user_id')
    .where('enquiry_replies.enquiry_id', enquiryId)
    .orderBy('enquiry_replies.created_at', 'asc')
    .select(
      'enquiry_replies.id',
      'enquiry_replies.enquiry_id',
      'enquiry_replies.admin_user_id',
      'enquiry_replies.body',
      'enquiry_replies.delivery_status',
      'enquiry_replies.delivery_error',
      'enquiry_replies.sent_at',
      'enquiry_replies.created_at',
      { author_name: 'admin_users.name' },
    ) as Promise<ReplyWithAuthor[]>;
}

/** { enquiryId -> reply count } for a page of ids (batched, no N+1). */
export async function replyCounts(enquiryIds: string[]): Promise<Map<string, number>> {
  if (enquiryIds.length === 0) return new Map();
  const rows = (await db('enquiry_replies')
    .whereIn('enquiry_id', enquiryIds)
    .groupBy('enquiry_id')
    .select('enquiry_id')
    .count({ n: '*' })) as Array<{ enquiry_id: string; n: number | string }>;
  return new Map(rows.map((r) => [r.enquiry_id, Number(r.n)]));
}

/** Exposed for services that need `db.transaction(...)`. */
export { db };