import type { Server as SocketServer } from 'socket.io';
import { sql } from '../db.js';
import type {
  ChannelSettings,
  PortalParticipant,
  Ticket,
  TicketIntake,
  TicketPriority,
  TicketStatus,
} from '@wa-ticketing/shared';
import {
  mergeTicketIntake,
  defaultTicketIntake,
  isPortalChannelPhone,
  PORTAL_CHANNEL_PHONE,
} from '@wa-ticketing/shared';
import { auditService } from './auditService.js';
import { channelService } from './channelService.js';
import { messagingService } from './messagingService.js';
import { sendSmtpMail } from './emailService.js';

function mapTicketRow(
  row: Ticket & {
    updated_at?: string;
    portal_participant?: PortalParticipant | null;
    needs_escalation?: boolean;
  },
): Ticket {
  return {
    ...row,
    updated_at: row.updated_at ?? row.created_at,
    needs_escalation: row.needs_escalation ?? false,
    intake: mergeTicketIntake(row.intake),
    portal_participant: row.portal_participant ?? undefined,
  };
}

export const ticketService = {
  async countForContactChannel(contactId: string, channelId: string): Promise<number> {
    const [row] = await sql<[{ c: string }]>`
      SELECT COUNT(*)::text AS c FROM tickets
      WHERE contact_id = ${contactId} AND channel_id = ${channelId}
    `;
    return Number.parseInt(row.c, 10);
  },

  /** First-ever ticket on this channel for this contact → onboarding eligible */
  buildInitialIntake(channelSettings: ChannelSettings, isFirstEverOnChannel: boolean): TicketIntake {
    if (!channelSettings.onboarding.enabled || !isFirstEverOnChannel) return { step: 'done', answers: {} };
    return { step: 'idle', answers: {} };
  },

  async findOpenTicket(contactId: string, channelId: string): Promise<Ticket | null> {
    const [row] = await sql<[Ticket]>`
      SELECT * FROM tickets
      WHERE contact_id = ${contactId}
        AND channel_id  = ${channelId}
        AND status      = 'open'
      LIMIT 1
    `;
    return row ? mapTicketRow(row) : null;
  },

  async createTicket(contactId: string, channelId: string, intake: TicketIntake = defaultTicketIntake): Promise<Ticket> {
    const [ticket] = await sql<[Ticket]>`
      INSERT INTO tickets (contact_id, channel_id, intake)
      VALUES (${contactId}, ${channelId}, ${sql.json(intake as never)})
      RETURNING *
    `;
    await auditService.log(null, 'system', 'ticket.created', 'ticket', ticket.id, { contactId, channelId });
    return mapTicketRow(ticket);
  },

  async closeTicket(ticketId: string, actorId: string, io?: SocketServer): Promise<Ticket> {
    const [pre] = await sql<[{ channel_id: string; phone_number: string }]>`
      SELECT t.channel_id, c.phone_number
      FROM tickets t
      INNER JOIN contacts c ON c.id = t.contact_id
      WHERE t.id = ${ticketId} AND t.status = 'open'
    `;
    if (!pre) throw Object.assign(new Error('Ticket not found or already closed'), { statusCode: 404 });

    const channel = await channelService.getById(pre.channel_id);
    const closing = channel?.settings?.ticketClosing;
    const closingText =
      closing?.enabled && closing.message?.trim() ? closing.message.trim() : null;

    const [ticket] = await sql<[Ticket]>`
      UPDATE tickets
      SET status = 'closed', closed_at = NOW(), updated_at = NOW()
      WHERE id = ${ticketId} AND status = 'open'
      RETURNING *
    `;
    if (!ticket) throw Object.assign(new Error('Ticket not found or already closed'), { statusCode: 404 });
    await auditService.log(actorId, 'user', 'ticket.closed', 'ticket', ticketId, {});

    if (io && closingText && channel) {
      try {
        if (isPortalChannelPhone(channel.phone_number)) {
          const message = await messagingService.appendMessage(
            ticketId,
            'outbound',
            closingText,
            null,
            undefined,
            'portal',
          );
          await auditService.log(actorId, 'user', 'ticket.closing_portal_sent', 'message', message.id, { ticketId });
          io.to(`ticket:${ticketId}`).emit('message:new', { ticketId, message });
        } else if (channel.kind === 'email' && pre.phone_number.startsWith('email:') && pre.phone_number.includes('@')) {
          const to = pre.phone_number.slice(6);
          const sent = await sendSmtpMail({
            to,
            subject: 'Ticket closed',
            text: closingText,
          });
          if (!sent.ok) throw new Error(sent.error ?? 'SMTP failed');
          const message = await messagingService.appendMessage(
            ticketId,
            'outbound',
            closingText,
            null,
            undefined,
            'email',
          );
          await auditService.log(actorId, 'user', 'ticket.closing_email_sent', 'message', message.id, { ticketId });
          io.to(`ticket:${ticketId}`).emit('message:new', { ticketId, message });
        } else {
          const message = await messagingService.appendMessage(ticketId, 'outbound', closingText, null);
          const waMessageId = await channelService.sendMessage(
            pre.channel_id,
            pre.phone_number,
            closingText,
          );
          if (waMessageId) {
            await sql`UPDATE messages SET wa_message_id = ${waMessageId} WHERE id = ${message.id}`;
            message.wa_message_id = waMessageId;
          }
          await auditService.log(actorId, 'user', 'ticket.closing_whatsapp_sent', 'message', message.id, {
            ticketId,
          });
          io.to(`ticket:${ticketId}`).emit('message:new', { ticketId, message });
        }
      } catch (err) {
        await auditService.log(actorId, 'user', 'ticket.closing_delivery_failed', 'ticket', ticketId, {
          error: String(err),
        });
      }
    }

    return mapTicketRow(ticket);
  },

  async setPriority(ticketId: string, priority: TicketPriority, actorId: string): Promise<Ticket> {
    const [ticket] = await sql<[Ticket]>`
      UPDATE tickets SET priority = ${priority}, updated_at = NOW()
      WHERE id = ${ticketId}
      RETURNING *
    `;
    await auditService.log(actorId, 'user', 'ticket.priority_changed', 'ticket', ticketId, { priority });
    return mapTicketRow(ticket);
  },

  async setDueDate(ticketId: string, dueDate: string | null, actorId: string): Promise<Ticket> {
    const [ticket] = await sql<[Ticket]>`
      UPDATE tickets SET due_date = ${dueDate}, updated_at = NOW()
      WHERE id = ${ticketId}
      RETURNING *
    `;
    await auditService.log(actorId, 'user', 'ticket.due_date_set', 'ticket', ticketId, { dueDate });
    return mapTicketRow(ticket);
  },

  async assignTo(ticketId: string, userId: string | null, actorId: string): Promise<Ticket> {
    const [ticket] = await sql<[Ticket]>`
      UPDATE tickets SET assigned_to = ${userId}, updated_at = NOW()
      WHERE id = ${ticketId}
      RETURNING *
    `;
    await auditService.log(actorId, 'user', 'ticket.assigned', 'ticket', ticketId, { userId });
    return mapTicketRow(ticket);
  },

  async setNeedsEscalation(ticketId: string, value: boolean, actorId: string): Promise<Ticket> {
    const [ticket] = await sql<[Ticket]>`
      UPDATE tickets SET needs_escalation = ${value}, updated_at = NOW()
      WHERE id = ${ticketId}
      RETURNING *
    `;
    await auditService.log(actorId, 'user', 'ticket.escalation_flag', 'ticket', ticketId, { value });
    return mapTicketRow(ticket);
  },

  async getById(id: string): Promise<Ticket | null> {
    const portalPhone = PORTAL_CHANNEL_PHONE;
    const [row] = await sql<[Ticket]>`
      SELECT t.*,
        row_to_json(c.*) AS contact,
        row_to_json(ch.*) AS channel,
        json_build_object('id', u.id, 'email', u.email) AS assigned_user,
        CASE
          WHEN ch.phone_number = ${portalPhone}
            AND c.phone_number LIKE 'portal:%'
            AND portal_co.id IS NOT NULL
            AND portal_u.id IS NOT NULL
          THEN json_build_object(
            'company_name', portal_co.name,
            'user_email', portal_u.email,
            'user_display_name', portal_u.display_name
          )
          ELSE NULL
        END AS portal_participant
      FROM tickets t
      LEFT JOIN contacts c  ON c.id  = t.contact_id
      LEFT JOIN channels ch ON ch.id = t.channel_id
      LEFT JOIN users u     ON u.id  = t.assigned_to
      LEFT JOIN companies portal_co
        ON portal_co.id = c.company_id AND ch.phone_number = ${portalPhone}
      LEFT JOIN users portal_u
        ON ch.phone_number = ${portalPhone}
        AND c.phone_number LIKE 'portal:%'
        AND portal_u.id::text = substring(c.phone_number from 8 for 36)
      WHERE t.id = ${id}
    `;
    return row ? mapTicketRow(row) : null;
  },

  async list(filters: {
    status?: TicketStatus;
    priority?: TicketPriority;
    channelId?: string;
    assignedTo?: string;
    from?: string;
    to?: string;
    limit?: number;
    offset?: number;
    companyId?: string;
  }) {
    const { status, priority, channelId, assignedTo, from, to, companyId, limit = 50, offset = 0 } = filters;

    const portalPhone = PORTAL_CHANNEL_PHONE;
    const rows = await sql<Ticket[]>`
      SELECT t.*,
        row_to_json(c.*) AS contact,
        row_to_json(ch.*) AS channel,
        json_build_object('id', u.id, 'email', u.email) AS assigned_user,
        CASE
          WHEN ch.phone_number = ${portalPhone}
            AND c.phone_number LIKE 'portal:%'
            AND portal_co.id IS NOT NULL
            AND portal_u.id IS NOT NULL
          THEN json_build_object(
            'company_name', portal_co.name,
            'user_email', portal_u.email,
            'user_display_name', portal_u.display_name
          )
          ELSE NULL
        END AS portal_participant
      FROM tickets t
      LEFT JOIN contacts c  ON c.id  = t.contact_id
      LEFT JOIN channels ch ON ch.id = t.channel_id
      LEFT JOIN users u     ON u.id  = t.assigned_to
      LEFT JOIN companies portal_co
        ON portal_co.id = c.company_id AND ch.phone_number = ${portalPhone}
      LEFT JOIN users portal_u
        ON ch.phone_number = ${portalPhone}
        AND c.phone_number LIKE 'portal:%'
        AND portal_u.id::text = substring(c.phone_number from 8 for 36)
      WHERE TRUE
        ${status     ? sql`AND t.status      = ${status}`     : sql``}
        ${priority   ? sql`AND t.priority    = ${priority}`   : sql``}
        ${channelId  ? sql`AND t.channel_id  = ${channelId}`  : sql``}
        ${assignedTo ? sql`AND t.assigned_to = ${assignedTo}` : sql``}
        ${from       ? sql`AND t.created_at >= ${from}`       : sql``}
        ${to         ? sql`AND t.created_at <= ${to}`         : sql``}
        ${companyId  ? sql`AND c.company_id  = ${companyId}`  : sql``}
      ORDER BY t.updated_at DESC
      LIMIT ${limit} OFFSET ${offset}
    `;
    return rows.map(mapTicketRow);
  },
};