import { randomBytes } from 'crypto';
import { sql } from '../db.js';
import type { Company } from '@wa-ticketing/shared';
import { PORTAL_CHANNEL_PHONE } from '@wa-ticketing/shared';

function normalizeCompanyName(name: string): string {
  return name.trim().toLowerCase().replace(/\s+/g, ' ');
}

/** Unique customer-facing code (uppercase), prefixed with C */
async function allocateAccountCode(): Promise<string> {
  for (let i = 0; i < 24; i++) {
    const code = `C${randomBytes(5).toString('hex').toUpperCase()}`;
    const [hit] = await sql<[{ n: bigint }]>`
      SELECT COUNT(*)::bigint AS n FROM companies WHERE account_code = ${code}
    `;
    if (hit && hit.n === 0n) return code;
  }
  throw Object.assign(new Error('Could not allocate account code'), { statusCode: 500 });
}

export const companyService = {
  normalizeName: normalizeCompanyName,

  async findByNormalizedName(normalized: string): Promise<Company | null> {
    const [row] = await sql<[Company]>`
      SELECT id, name, normalized_name, account_code, created_at FROM companies
      WHERE normalized_name = ${normalized}
    `;
    return row ?? null;
  },

  /** Case-insensitive match on account_code */
  async findByAccountCode(code: string): Promise<Company | null> {
    const c = code.trim().toUpperCase();
    if (!c) return null;
    const [row] = await sql<[Company]>`
      SELECT id, name, normalized_name, account_code, created_at FROM companies
      WHERE upper(account_code) = ${c}
    `;
    return row ?? null;
  },

  /** Resolve join input: try account code first, then exact normalized company name */
  async resolveForJoin(raw: string): Promise<Company | null> {
    const t = raw.trim();
    if (!t) return null;
    const byCode = await this.findByAccountCode(t);
    if (byCode) return byCode;
    const normalized = normalizeCompanyName(t);
    if (!normalized) return null;
    return this.findByNormalizedName(normalized);
  },

  async upsertByName(displayName: string): Promise<Company> {
    const name = displayName.trim();
    const normalized = normalizeCompanyName(name);
    if (!normalized) throw Object.assign(new Error('Company name is empty'), { statusCode: 400 });

    const existing = await this.findByNormalizedName(normalized);
    if (existing) {
      const [row] = await sql<[Company]>`
        UPDATE companies SET name = ${name}
        WHERE normalized_name = ${normalized}
        RETURNING id, name, normalized_name, account_code, created_at
      `;
      return row;
    }
    const code = await allocateAccountCode();
    const [row] = await sql<[Company]>`
      INSERT INTO companies (name, normalized_name, account_code)
      VALUES (${name}, ${normalized}, ${code})
      RETURNING id, name, normalized_name, account_code, created_at
    `;
    return row;
  },

  async linkContactToCompany(contactId: string, companyId: string): Promise<void> {
    await sql`
      UPDATE contacts SET company_id = ${companyId} WHERE id = ${contactId}
    `;
  },

  async list(): Promise<Company[]> {
    return sql<Company[]>`
      SELECT id, name, normalized_name, account_code, created_at FROM companies
      ORDER BY name ASC
    `;
  },

  async getById(id: string): Promise<Company | null> {
    const [row] = await sql<[Company]>`
      SELECT id, name, normalized_name, account_code, created_at FROM companies WHERE id = ${id}
    `;
    return row ?? null;
  },

  async create(name: string): Promise<Company> {
    const n = name.trim();
    if (!n) throw Object.assign(new Error('name is required'), { statusCode: 400 });
    return this.upsertByName(n);
  },

  async updateName(id: string, name: string): Promise<Company | null> {
    const n = name.trim();
    if (!n) throw Object.assign(new Error('name is required'), { statusCode: 400 });
    const normalized = normalizeCompanyName(n);
    const [row] = await sql<[Company]>`
      UPDATE companies SET name = ${n}, normalized_name = ${normalized}
      WHERE id = ${id}
      RETURNING id, name, normalized_name, account_code, created_at
    `;
    return row ?? null;
  },

  /** Channel row used for web portal tickets (seeded in migration). */
  async getPortalChannelId(): Promise<string> {
    const [row] = await sql<[{ id: string }]>`
      SELECT id FROM channels WHERE phone_number = ${PORTAL_CHANNEL_PHONE} LIMIT 1
    `;
    if (!row) throw Object.assign(new Error('Portal channel missing — run migrations'), { statusCode: 500 });
    return row.id;
  },

  async getDetailAggregates(companyId: string): Promise<{
    /** Contacts linked to this company (open contact detail from here); portal rows include company/user labels */
    linkedContacts: Array<{
      id: string;
      phone_number: string;
      display_name: string | null;
      portal_company_name: string | null;
      portal_user_email: string | null;
      portal_user_display_name: string | null;
    }>;
    channelPhones: string[];
    users: Array<{
      id: string;
      email: string;
      display_name: string | null;
      phone: string | null;
      role: string;
      is_active: boolean;
      created_at: string;
    }>;
  }> {
    const linkedContacts = await sql<
      Array<{
        id: string;
        phone_number: string;
        display_name: string | null;
        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,
        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
      WHERE c.company_id = ${companyId}
      ORDER BY c.phone_number ASC
    `;
    const chPhones = await sql<Array<{ phone_number: string }>>`
      SELECT DISTINCT ch.phone_number
      FROM tickets t
      INNER JOIN contacts c ON c.id = t.contact_id
      INNER JOIN channels ch ON ch.id = t.channel_id
      WHERE c.company_id = ${companyId}
      ORDER BY ch.phone_number
    `;
    type CompanyUserRow = {
      id: string;
      email: string;
      display_name: string | null;
      phone: string | null;
      role: string;
      is_active: boolean;
      created_at: string;
    };
    const users = await sql<CompanyUserRow[]>`
      SELECT id, email, display_name, phone, role::text AS role, is_active, created_at
      FROM users
      WHERE company_id = ${companyId}
      ORDER BY created_at DESC
    `;
    return {
      linkedContacts,
      channelPhones: chPhones.map(p => p.phone_number),
      users,
    };
  },
};