import { sql } from '../db.js';
import type { Company, Contact } from '@wa-ticketing/shared';
import { emailContactKey } from '@wa-ticketing/shared';

function normalizePhone(raw: string): string {
  const t = raw.trim();
  // Synthetic portal identities — not E.164
  if (t.startsWith('portal:')) return t;
  if (t.toLowerCase().startsWith('email:')) {
    return emailContactKey(t.slice(6));
  }
  // Strip all non-digit characters, keep leading +
  const digits = raw.replace(/[^\d+]/g, '');
  // Ensure E.164 format: starts with +
  return digits.startsWith('+') ? digits : `+${digits}`;
}

export const contactService = {
  normalize: normalizePhone,

  async findOrCreate(rawPhone: string, displayName?: string): Promise<Contact> {
    const phone = normalizePhone(rawPhone);

    const [existing] = await sql<[Contact]>`
      SELECT id, phone_number, display_name, company_id, created_at
      FROM contacts WHERE phone_number = ${phone}
    `;
    if (existing) return existing;

    const [created] = await sql<[Contact]>`
      INSERT INTO contacts (phone_number, display_name)
      VALUES (${phone}, ${displayName ?? null})
      ON CONFLICT (phone_number) DO UPDATE SET display_name = EXCLUDED.display_name
      RETURNING id, phone_number, display_name, company_id, created_at
    `;
    return created;
  },

  /** One synthetic contact per company portal login — ties tickets to company_id. */
  async ensurePortalUser(userId: string, companyId: string): Promise<Contact> {
    const phone = `portal:${userId}`;
    const [existing] = await sql<[Contact]>`
      SELECT id, phone_number, display_name, company_id, created_at
      FROM contacts WHERE phone_number = ${phone}
    `;
    if (existing) {
      if (existing.company_id && existing.company_id !== companyId) {
        throw Object.assign(new Error('Forbidden'), { statusCode: 403 });
      }
      if (!existing.company_id) {
        await sql`UPDATE contacts SET company_id = ${companyId} WHERE id = ${existing.id}`;
        return { ...existing, company_id: companyId };
      }
      return existing;
    }
    const [created] = await sql<[Contact]>`
      INSERT INTO contacts (phone_number, display_name, company_id)
      VALUES (${phone}, NULL, ${companyId})
      RETURNING id, phone_number, display_name, company_id, created_at
    `;
    return created;
  },

  async list(): Promise<Contact[]> {
    const rows = await sql<
      Array<{
        id: string;
        phone_number: string;
        display_name: string | null;
        company_id: string | null;
        created_at: string;
        portal_company_name: string | null;
        portal_user_email: string | null;
        portal_user_display_name: string | null;
      }>
    >`
      SELECT
        c.id,
        c.phone_number,
        c.display_name,
        c.company_id,
        c.created_at,
        co.name AS portal_company_name,
        pu.email AS portal_user_email,
        pu.display_name AS portal_user_display_name
      FROM contacts c
      LEFT JOIN users pu
        ON c.phone_number LIKE 'portal:%'
        AND pu.id::text = substring(c.phone_number from 8 for 36)
      LEFT JOIN companies co
        ON c.phone_number LIKE 'portal:%'
        AND co.id = c.company_id
      ORDER BY c.created_at DESC
    `;
    return rows.map(r => ({
      id: r.id,
      phone_number: r.phone_number,
      display_name: r.display_name,
      company_id: r.company_id,
      created_at: r.created_at,
      portal_company_name: r.portal_company_name,
      portal_user_email: r.portal_user_email,
      portal_user_display_name: r.portal_user_display_name,
    }));
  },

  /** Portal synthetic contact: load portal user email/display_name for detail API */
  async getPortalUserFields(phone: string): Promise<{
    portal_user_email: string | null;
    portal_user_display_name: string | null;
  }> {
    if (!phone.startsWith('portal:')) {
      return { portal_user_email: null, portal_user_display_name: null };
    }
    const idPart = phone.slice(7).trim();
    if (idPart.length < 32) return { portal_user_email: null, portal_user_display_name: null };
    const [u] = await sql<[{ email: string; display_name: string | null }]>`
      SELECT email, display_name FROM users WHERE id = ${idPart}::uuid
    `;
    return {
      portal_user_email: u?.email ?? null,
      portal_user_display_name: u?.display_name ?? null,
    };
  },

  async getById(id: string): Promise<Contact | null> {
    const [row] = await sql<[Contact]>`
      SELECT id, phone_number, display_name, company_id, created_at
      FROM contacts WHERE id = ${id}
    `;
    return row ?? null;
  },

  async getTicketHistory(contactId: string) {
    return sql`
      SELECT t.*, ch.name AS channel_name, u.email AS assigned_email
      FROM tickets t
      LEFT JOIN channels ch ON ch.id = t.channel_id
      LEFT JOIN users u ON u.id = t.assigned_to
      WHERE t.contact_id = ${contactId}
      ORDER BY t.updated_at DESC NULLS LAST, t.created_at DESC
    `;
  },

  /** Contact row + linked company + ticket rows for detail screen */
  async getDetail(id: string): Promise<null | (Contact & { company: Company | null; tickets: unknown[] })> {
    const contact = await this.getById(id);
    if (!contact) return null;
    let company: Company | null = null;
    if (contact.company_id) {
      const [row] = await sql<[Company]>`
        SELECT id, name, normalized_name, created_at
        FROM companies WHERE id = ${contact.company_id}
      `;
      company = row ?? null;
    }
    const tickets = await this.getTicketHistory(id);
    const portalFields = await this.getPortalUserFields(contact.phone_number);
    const portal_company_name = company?.name ?? null;
    return { ...contact, company, tickets, ...portalFields, portal_company_name };
  },

  /** Staff: link WhatsApp contact to a company (portal:* contacts are fixed). */
  async setCompanyId(contactId: string, companyId: string | null): Promise<Contact> {
    const contact = await this.getById(contactId);
    if (!contact) throw Object.assign(new Error('Contact not found'), { statusCode: 404 });
    if (contact.phone_number.startsWith('portal:')) {
      throw Object.assign(
        new Error('Portal contacts are tied to their company via portal users and cannot be reassigned here'),
        { statusCode: 400 },
      );
    }
    if (companyId) {
      const [exists] = await sql<[{ n: string }]>`
        SELECT COUNT(*)::text AS n FROM companies WHERE id = ${companyId}
      `;
      if (!exists || Number.parseInt(exists.n, 10) === 0) {
        throw Object.assign(new Error('Company not found'), { statusCode: 404 });
      }
    }
    const [updated] = await sql<[Contact]>`
      UPDATE contacts SET company_id = ${companyId}
      WHERE id = ${contactId}
      RETURNING id, phone_number, display_name, company_id, created_at
    `;
    return updated;
  },
};