import { query, queryOne } from '../../config/database.js';
import { NotFoundError } from '../../shared/errors/index.js';

export interface Message {
  id: string;
  tenant_id: string;
  model: string;
  record_id: string;
  message_type: 'comment' | 'note' | 'email' | 'notification' | 'system';
  subject?: string;
  body: string;
  author_id?: string;
  author_name?: string;
  author_email?: string;
  parent_id?: string;
  attachment_ids: string[];
  created_at: Date;
}

export interface CreateMessageDto {
  model: string;
  record_id: string;
  message_type?: 'comment' | 'note' | 'email' | 'notification' | 'system';
  subject?: string;
  body: string;
  parent_id?: string;
}

export interface MessageFilters {
  model?: string;
  record_id?: string;
  message_type?: string;
  author_id?: string;
  search?: string;
  page?: number;
  limit?: number;
}

export interface Follower {
  id: string;
  model: string;
  record_id: string;
  partner_id?: string;
  user_id?: string;
  user_name?: string;
  partner_name?: string;
  email_notifications: boolean;
  created_at: Date;
}

export interface AddFollowerDto {
  model: string;
  record_id: string;
  user_id?: string;
  partner_id?: string;
  email_notifications?: boolean;
}

class MessagesService {
  async findAll(tenantId: string, filters: MessageFilters = {}): Promise<{ data: Message[]; total: number }> {
    const { model, record_id, message_type, author_id, search, page = 1, limit = 50 } = filters;
    const offset = (page - 1) * limit;

    let whereClause = 'WHERE m.tenant_id = $1';
    const params: any[] = [tenantId];
    let paramIndex = 2;

    if (model) {
      whereClause += ` AND m.model = $${paramIndex++}`;
      params.push(model);
    }

    if (record_id) {
      whereClause += ` AND m.record_id = $${paramIndex++}`;
      params.push(record_id);
    }

    if (message_type) {
      whereClause += ` AND m.message_type = $${paramIndex++}`;
      params.push(message_type);
    }

    if (author_id) {
      whereClause += ` AND m.author_id = $${paramIndex++}`;
      params.push(author_id);
    }

    if (search) {
      whereClause += ` AND (m.subject ILIKE $${paramIndex} OR m.body ILIKE $${paramIndex})`;
      params.push(`%${search}%`);
      paramIndex++;
    }

    const countResult = await queryOne<{ count: string }>(
      `SELECT COUNT(*) as count FROM system.messages m ${whereClause}`,
      params
    );

    params.push(limit, offset);
    const data = await query<Message>(
      `SELECT m.*,
              u.first_name || ' ' || u.last_name as author_name,
              u.email as author_email
       FROM system.messages m
       LEFT JOIN auth.users u ON m.author_id = u.id
       ${whereClause}
       ORDER BY m.created_at DESC
       LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`,
      params
    );

    return {
      data,
      total: parseInt(countResult?.count || '0', 10),
    };
  }

  async findByRecord(model: string, recordId: string, tenantId: string): Promise<Message[]> {
    const messages = await query<Message>(
      `SELECT m.*,
              u.first_name || ' ' || u.last_name as author_name,
              u.email as author_email
       FROM system.messages m
       LEFT JOIN auth.users u ON m.author_id = u.id
       WHERE m.model = $1 AND m.record_id = $2 AND m.tenant_id = $3
       ORDER BY m.created_at DESC`,
      [model, recordId, tenantId]
    );

    return messages;
  }

  async findById(id: string, tenantId: string): Promise<Message> {
    const message = await queryOne<Message>(
      `SELECT m.*,
              u.first_name || ' ' || u.last_name as author_name,
              u.email as author_email
       FROM system.messages m
       LEFT JOIN auth.users u ON m.author_id = u.id
       WHERE m.id = $1 AND m.tenant_id = $2`,
      [id, tenantId]
    );

    if (!message) {
      throw new NotFoundError('Mensaje no encontrado');
    }

    return message;
  }

  async create(dto: CreateMessageDto, tenantId: string, userId: string): Promise<Message> {
    // Get user info for author fields
    const user = await queryOne<{ first_name: string; last_name: string; email: string }>(
      `SELECT first_name, last_name, email FROM auth.users WHERE id = $1`,
      [userId]
    );

    const message = await queryOne<Message>(
      `INSERT INTO system.messages (
        tenant_id, model, record_id, message_type, subject, body,
        author_id, author_name, author_email, parent_id
      )
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
       RETURNING *`,
      [
        tenantId, dto.model, dto.record_id,
        dto.message_type || 'comment', dto.subject, dto.body,
        userId, user ? `${user.first_name} ${user.last_name}` : null,
        user?.email, dto.parent_id
      ]
    );

    return message!;
  }

  async delete(id: string, tenantId: string): Promise<void> {
    await this.findById(id, tenantId);

    await query(
      `DELETE FROM system.messages WHERE id = $1 AND tenant_id = $2`,
      [id, tenantId]
    );
  }

  // ========== FOLLOWERS ==========
  async getFollowers(model: string, recordId: string): Promise<Follower[]> {
    const followers = await query<Follower>(
      `SELECT mf.*,
              u.first_name || ' ' || u.last_name as user_name,
              p.name as partner_name
       FROM system.message_followers mf
       LEFT JOIN auth.users u ON mf.user_id = u.id
       LEFT JOIN core.partners p ON mf.partner_id = p.id
       WHERE mf.model = $1 AND mf.record_id = $2
       ORDER BY mf.created_at DESC`,
      [model, recordId]
    );

    return followers;
  }

  async addFollower(dto: AddFollowerDto): Promise<Follower> {
    const follower = await queryOne<Follower>(
      `INSERT INTO system.message_followers (
        model, record_id, user_id, partner_id, email_notifications
      )
       VALUES ($1, $2, $3, $4, $5)
       ON CONFLICT (model, record_id, COALESCE(user_id, partner_id)) DO UPDATE
       SET email_notifications = EXCLUDED.email_notifications
       RETURNING *`,
      [dto.model, dto.record_id, dto.user_id, dto.partner_id, dto.email_notifications ?? true]
    );

    return follower!;
  }

  async removeFollower(model: string, recordId: string, userId?: string, partnerId?: string): Promise<void> {
    if (userId) {
      await query(
        `DELETE FROM system.message_followers
         WHERE model = $1 AND record_id = $2 AND user_id = $3`,
        [model, recordId, userId]
      );
    } else if (partnerId) {
      await query(
        `DELETE FROM system.message_followers
         WHERE model = $1 AND record_id = $2 AND partner_id = $3`,
        [model, recordId, partnerId]
      );
    }
  }
}

export const messagesService = new MessagesService();
