import database from "../../config/db";
import { sendEmail } from "../../utils/sendEmail";

export interface ContactMessageCreateData {
  name: string;
  email: string;
  phone?: string;
  subject: string;
  message: string;
  ip_address?: string;
  user_agent?: string;
  referrer_url?: string;
  category?: string;
}

export interface ContactMessageUpdateData {
  status?: "unread" | "read" | "replied" | "archived" | "spam";
  priority?: "low" | "normal" | "high" | "urgent";
  assigned_to?: string;
  tags?: string[];
  is_replied?: boolean;
  needs_follow_up?: boolean;
  follow_up_date?: Date;
  follow_up_notes?: string;
  is_spam?: boolean;
  spam_reason?: string;
}

export interface ContactMessageReplyData {
  message_id: string;
  reply_subject: string;
  reply_message: string;
  replied_by: string;
  internal_notes?: string;
  is_internal?: boolean;
}

export interface ContactSettingsUpdateData {
  company_name?: string;
  company_email?: string;
  company_phone?: string;
  company_address?: string;
  company_location_city?: string;
  company_location_country?: string;
  company_location_coordinates?: any;
  support_email?: string;
  info_email?: string;
  sales_email?: string;
  phone_numbers?: any[];
  alternative_contacts?: any[];
  working_hours?: any;
  social_media?: any;
  contact_form_enabled?: boolean;
  contact_subjects?: any[];
  quick_info?: any[];
  contact_reasons?: any[];
  map_enabled?: boolean;
  map_embed_url?: string;
  map_location_name?: string;
  map_coordinates?: any;
  contact_page_title?: string;
  contact_page_description?: string;
  contact_page_meta_keywords?: string[];
  auto_response_enabled?: boolean;
  auto_response_subject?: string;
  auto_response_message?: string;
  is_active?: boolean;
  maintenance_mode?: boolean;
  maintenance_message?: string;
}

export interface ContactMessageFilters {
  page?: number;
  limit?: number;
  status?: string;
  priority?: string;
  category?: string;
  assigned_to?: string;
  is_read?: boolean;
  is_replied?: boolean;
  is_spam?: boolean;
  needs_follow_up?: boolean;
  search?: string;
  start_date?: string;
  end_date?: string;
  sort_by?: string;
  order?: "asc" | "desc";
}

export const contactService = {
  // Get contact page data
  async getContactPageData() {
    try {
      const query = `SELECT * FROM get_contact_page_data() as data;`;
      const result = await database.query(query);
      return result.rows[0]?.data || {};
    } catch (error) {
      console.error("Error getting contact page data:", error);
      return {};
    }
  },

  // Create new contact message
  async createContactMessage(data: ContactMessageCreateData) {
    const {
      name,
      email,
      phone,
      subject,
      message,
      ip_address,
      user_agent,
      referrer_url,
      category = "general",
    } = data;

    // Validation
    if (!name || name.trim().length < 2) {
      throw new Error("Name must be at least 2 characters");
    }

    if (!email || !email.match(/^[^\s@]+@[^\s@]+\.[^\s@]+$/)) {
      throw new Error("Valid email is required");
    }

    if (!subject || subject.trim().length < 3) {
      throw new Error("Subject must be at least 3 characters");
    }

    if (!message || message.trim().length < 10) {
      throw new Error("Message must be at least 10 characters");
    }

    // Check spam score
    const spamScoreQuery = `
      SELECT check_spam_message($1, $2, $3, $4) as spam_score;
    `;
    const spamResult = await database.query(spamScoreQuery, [
      email,
      name,
      message,
      ip_address || '0.0.0.0'
    ]);
    const spamScore = parseFloat(spamResult.rows[0].spam_score);
    const isSpam = spamScore > 0.7;

    // Determine priority
    let priority: "low" | "normal" | "high" | "urgent" = "normal";
    const urgentKeywords = ["urgent", "emergency", "asap", "immediately", "critical"];
    const highKeywords = ["important", "priority", "help", "issue", "problem"];
    
    const messageLower = message.toLowerCase();
    if (urgentKeywords.some(keyword => messageLower.includes(keyword))) {
      priority = "urgent";
    } else if (highKeywords.some(keyword => messageLower.includes(keyword))) {
      priority = "high";
    }

    const query = `
      INSERT INTO contact_messages (
        name, email, phone, subject, message,
        ip_address, user_agent, referrer_url, category,
        spam_score, is_spam, priority,
        status, created_at
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, CURRENT_TIMESTAMP)
      RETURNING *;
    `;

    const values = [
      name.trim(),
      email.toLowerCase().trim(),
      phone?.trim(),
      subject.trim(),
      message.trim(),
      ip_address,
      user_agent,
      referrer_url,
      category,
      spamScore,
      isSpam,
      priority,
      isSpam ? "spam" : "unread"
    ];

    const result = await database.query(query, values);
    const savedMessage = result.rows[0];

    // Send auto-response if not spam and enabled
    if (!isSpam) {
      try {
        await this.sendAutoResponse(savedMessage);
      } catch (emailError) {
        console.error("Failed to send auto-response:", emailError);
      }
    }

    return savedMessage;
  },

  // Send auto-response email
  async sendAutoResponse(message: any) {
    // Get contact settings
    const settings = await this.getContactPageData();
    
    if (!settings.auto_response_enabled) {
      return;
    }

    const emailSubject = settings.auto_response_subject || "Thank you for contacting us";
    const emailMessage = settings.auto_response_message || `
      <!DOCTYPE html>
      <html>
      <head>
        <style>
          body { font-family: Arial, sans-serif; line-height: 1.6; color: #333; }
          .container { max-width: 600px; margin: 0 auto; padding: 20px; }
          .header { background: #3b82f6; color: white; padding: 20px; text-align: center; border-radius: 5px 5px 0 0; }
          .content { background: #f9fafb; padding: 20px; border-radius: 0 0 5px 5px; }
          .footer { margin-top: 20px; padding-top: 20px; border-top: 1px solid #e5e7eb; font-size: 12px; color: #6b7280; }
        </style>
      </head>
      <body>
        <div class="container">
          <div class="header">
            <h1>Thank you for contacting us!</h1>
          </div>
          <div class="content">
            <p>Hello ${message.name},</p>
            <p>We have received your message regarding "${message.subject}".</p>
            <p>Our team will review your inquiry and get back to you within 24 hours.</p>
            <p><strong>Your message reference:</strong> ${message.id}</p>
            <p><strong>Submitted on:</strong> ${new Date(message.created_at).toLocaleString()}</p>
            <br>
            <p>Best regards,<br>The ${settings.company_name || 'Toolffy'} Team</p>
          </div>
          <div class="footer">
            <p>This is an automated response. Please do not reply to this email.</p>
            <p>If you need immediate assistance, please call us at ${settings.company_phone || '+880 1234-567890'}</p>
          </div>
        </div>
      </body>
      </html>
    `;

    try {
      await sendEmail({
        email: message.email,
        subject: emailSubject,
        message: emailMessage,
      });
    } catch (error) {
      console.error("Failed to send auto-response email:", error);
    }
  },

  // Get message by ID
  async getMessageById(id: string) {
    const query = `
      SELECT 
        cm.*,
        a.username as assigned_to_username,
        a.display_name as assigned_to_name,
        r.username as read_by_username,
        r.display_name as read_by_name,
        s.username as reply_sent_by_username,
        s.display_name as reply_sent_by_name,
        (
          SELECT JSON_AGG(
            JSON_BUILD_OBJECT(
              'id', cr.id,
              'reply_subject', cr.reply_subject,
              'reply_message', cr.reply_message,
              'replied_by', cr.replied_by,
              'replied_by_name', ra.display_name,
              'replied_by_username', ra.username,
              'email_sent', cr.email_sent,
              'email_sent_at', cr.email_sent_at,
              'email_status', cr.email_status,
              'internal_notes', cr.internal_notes,
              'is_internal', cr.is_internal,
              'created_at', cr.created_at
            )
          )
          FROM contact_message_replies cr
          LEFT JOIN accounts ra ON cr.replied_by = ra.id
          WHERE cr.message_id = cm.id
          ORDER BY cr.created_at DESC
        ) as replies,
        (
          SELECT COUNT(*)
          FROM contact_messages
          WHERE email = cm.email AND id != cm.id
        ) as previous_messages_count
      FROM contact_messages cm
      LEFT JOIN accounts a ON cm.assigned_to = a.id
      LEFT JOIN accounts r ON cm.read_by = r.id
      LEFT JOIN accounts s ON cm.reply_sent_by = s.id
      WHERE cm.id = $1;
    `;

    const result = await database.query(query, [id]);
    return result.rows[0] || null;
  },

  // Get all messages with filters
  async getAllMessages(filters: ContactMessageFilters = {}) {
    const {
      page = 1,
      limit = 20,
      status,
      priority,
      category,
      assigned_to,
      is_read,
      is_replied,
      is_spam = false,
      needs_follow_up,
      search,
      start_date,
      end_date,
      sort_by = "created_at",
      order = "desc",
    } = filters;

    const offset = (page - 1) * limit;
    const whereClauses: string[] = ["cm.is_spam = $1"];
    const values: any[] = [is_spam];
    let paramCount = 1;

    // Status filter
    if (status) {
      paramCount++;
      whereClauses.push(`cm.status = $${paramCount}`);
      values.push(status);
    }

    // Priority filter
    if (priority) {
      paramCount++;
      whereClauses.push(`cm.priority = $${paramCount}`);
      values.push(priority);
    }

    // Category filter
    if (category) {
      paramCount++;
      whereClauses.push(`cm.category = $${paramCount}`);
      values.push(category);
    }

    // Assigned to filter
    if (assigned_to) {
      paramCount++;
      whereClauses.push(`cm.assigned_to = $${paramCount}`);
      values.push(assigned_to);
    }

    // Read filter
    if (is_read !== undefined) {
      paramCount++;
      whereClauses.push(`cm.is_read = $${paramCount}`);
      values.push(is_read);
    }

    // Replied filter
    if (is_replied !== undefined) {
      paramCount++;
      whereClauses.push(`cm.is_replied = $${paramCount}`);
      values.push(is_replied);
    }

    // Follow-up filter
    if (needs_follow_up !== undefined) {
      paramCount++;
      whereClauses.push(`cm.needs_follow_up = $${paramCount}`);
      values.push(needs_follow_up);
    }

    // Date range filter
    if (start_date) {
      paramCount++;
      whereClauses.push(`DATE(cm.created_at) >= $${paramCount}`);
      values.push(start_date);
    }

    if (end_date) {
      paramCount++;
      whereClauses.push(`DATE(cm.created_at) <= $${paramCount}`);
      values.push(end_date);
    }

    // Search filter
    if (search) {
      paramCount++;
      whereClauses.push(`
        (cm.name ILIKE $${paramCount} OR 
         cm.email ILIKE $${paramCount} OR 
         cm.subject ILIKE $${paramCount} OR 
         cm.message ILIKE $${paramCount})
      `);
      values.push(`%${search}%`);
    }

    const whereClause = whereClauses.length > 0 
      ? `WHERE ${whereClauses.join(" AND ")}` 
      : "";

    // Validate sort field
    const validSortFields = [
      "created_at", "updated_at", "name", "email", 
      "subject", "priority", "status", "spam_score"
    ];
    const sortField = validSortFields.includes(sort_by) ? sort_by : "created_at";
    const sortOrder = order === "asc" ? "ASC" : "DESC";

    // Count total messages
    const countQuery = `
      SELECT COUNT(*) as total
      FROM contact_messages cm
      ${whereClause};
    `;

    const countResult = await database.query(countQuery, values);
    const total = parseInt(countResult.rows[0].total);

    // Get paginated messages
    paramCount = values.length;
    const dataQuery = `
      SELECT 
        cm.*,
        a.username as assigned_to_username,
        a.display_name as assigned_to_name,
        (
          SELECT COUNT(*)
          FROM contact_message_replies
          WHERE message_id = cm.id
        ) as reply_count,
        EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - cm.created_at))/3600 as hours_since_creation
      FROM contact_messages cm
      LEFT JOIN accounts a ON cm.assigned_to = a.id
      ${whereClause}
      ORDER BY 
        CASE WHEN cm.priority = 'urgent' THEN 1
             WHEN cm.priority = 'high' THEN 2
             WHEN cm.priority = 'normal' THEN 3
             ELSE 4
        END ASC,
        cm.${sortField} ${sortOrder}
      LIMIT $${paramCount + 1} OFFSET $${paramCount + 2};
    `;

    const dataValues = [...values, limit, offset];
    const dataResult = await database.query(dataQuery, dataValues);

    return {
      data: dataResult.rows,
      pagination: {
        page,
        limit,
        total,
        total_pages: Math.ceil(total / limit),
        has_next: page * limit < total,
        has_prev: page > 1,
      },
    };
  },

  // Update message
  async updateMessage(id: string, data: ContactMessageUpdateData, userId?: string) {
    const existingMessage = await this.getMessageById(id);
    if (!existingMessage) {
      throw new Error("Message not found");
    }

    const setClauses: string[] = [];
    const values: any[] = [];
    let paramCount = 0;

    const fields = [
      "status", "priority", "assigned_to", "tags",
      "is_replied", "needs_follow_up", "follow_up_date", 
      "follow_up_notes", "is_spam", "spam_reason"
    ];

    fields.forEach((field) => {
      if (data[field as keyof ContactMessageUpdateData] !== undefined) {
        paramCount++;
        setClauses.push(`${field} = $${paramCount}`);
        values.push(data[field as keyof ContactMessageUpdateData]);
      }
    });

    // Handle status changes
    if (data.status === "read" && existingMessage.status !== "read") {
      paramCount++;
      setClauses.push(`is_read = true, read_at = CURRENT_TIMESTAMP, read_by = $${paramCount}`);
      values.push(userId || null);
    }

    if (data.status === "replied" && existingMessage.status !== "replied") {
      paramCount++;
      setClauses.push(`is_replied = true, replied_at = CURRENT_TIMESTAMP, reply_sent_by = $${paramCount}`);
      values.push(userId || null);
    }

    if (setClauses.length === 0) {
      throw new Error("No fields to update");
    }

    paramCount++;
    setClauses.push(`updated_at = CURRENT_TIMESTAMP`);
    values.push(id);

    const query = `
      UPDATE contact_messages
      SET ${setClauses.join(", ")}
      WHERE id = $${paramCount}
      RETURNING *;
    `;

    const result = await database.query(query, values);
    return result.rows[0];
  },

  // Delete message
  async deleteMessage(id: string) {
    const query = `
      DELETE FROM contact_messages
      WHERE id = $1
      RETURNING id, email, subject;
    `;

    const result = await database.query(query, [id]);
    return result.rows[0];
  },

  // Delete multiple messages
  async deleteMultipleMessages(ids: string[]) {
    if (!Array.isArray(ids) || ids.length === 0) {
      throw new Error("Message IDs array is required");
    }

    if (ids.length > 100) {
      throw new Error("Cannot delete more than 100 messages at once");
    }

    const placeholders = ids.map((_, index) => `$${index + 1}`).join(", ");
    const query = `
      DELETE FROM contact_messages
      WHERE id IN (${placeholders})
      RETURNING id, email, subject;
    `;

    const result = await database.query(query, ids);
    return result.rows;
  },

  // Reply to message
  async replyToMessage(data: ContactMessageReplyData) {
    const { message_id, reply_subject, reply_message, replied_by, internal_notes, is_internal = false } = data;

    // Validate
    if (!reply_subject || reply_subject.trim().length < 3) {
      throw new Error("Reply subject must be at least 3 characters");
    }

    if (!reply_message || reply_message.trim().length < 10) {
      throw new Error("Reply message must be at least 10 characters");
    }

    // Get original message
    const originalMessage = await this.getMessageById(message_id);
    if (!originalMessage) {
      throw new Error("Original message not found");
    }

    // Start transaction
    const client = await database.connect();
    
    try {
      await client.query('BEGIN');

      // Insert reply
      const replyQuery = `
        INSERT INTO contact_message_replies (
          message_id, replied_by, reply_subject, reply_message,
          internal_notes, is_internal
        ) VALUES ($1, $2, $3, $4, $5, $6)
        RETURNING *;
      `;

      const replyValues = [
        message_id,
        replied_by,
        reply_subject.trim(),
        reply_message.trim(),
        internal_notes?.trim(),
        is_internal
      ];

      const replyResult = await client.query(replyQuery, replyValues);
      const savedReply = replyResult.rows[0];

      // Update original message status
      const updateQuery = `
        UPDATE contact_messages
        SET 
          status = 'replied',
          is_replied = true,
          replied_at = CURRENT_TIMESTAMP,
          reply_sent_by = $1,
          updated_at = CURRENT_TIMESTAMP
        WHERE id = $2
        RETURNING *;
      `;

      await client.query(updateQuery, [replied_by, message_id]);

      // Send email reply if not internal
      if (!is_internal) {
        try {
          await this.sendReplyEmail(originalMessage, savedReply);
          
          // Update email status
          await client.query(`
            UPDATE contact_message_replies
            SET email_status = 'sent'
            WHERE id = $1
          `, [savedReply.id]);
        } catch (emailError) {
          console.error("Failed to send reply email:", emailError);
          const errorMessage = (emailError instanceof Error) ? emailError.message : String(emailError);
          await client.query(`
            UPDATE contact_message_replies
            SET email_status = 'failed', email_error = $1
            WHERE id = $2
          `, [errorMessage, savedReply.id]);
        }
      }

      await client.query('COMMIT');
      
      // Get updated reply with email status
      const finalReply = await client.query(
        'SELECT * FROM contact_message_replies WHERE id = $1',
        [savedReply.id]
      );

      return finalReply.rows[0];
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  },

  // Send reply email
  async sendReplyEmail(originalMessage: any, reply: any) {
    // Get contact settings
    const settings = await this.getContactPageData();
    
    const emailSubject = reply.reply_subject || `Re: ${originalMessage.subject}`;
    const emailMessage = `
      <!DOCTYPE html>
      <html>
      <head>
        <style>
          body { font-family: Arial, sans-serif; line-height: 1.6; color: #333; }
          .container { max-width: 600px; margin: 0 auto; padding: 20px; }
          .header { background: #3b82f6; color: white; padding: 20px; text-align: center; border-radius: 5px 5px 0 0; }
          .content { background: #f9fafb; padding: 20px; border-radius: 0 0 5px 5px; }
          .original-message { background: #e5e7eb; padding: 15px; border-radius: 5px; margin: 15px 0; }
          .footer { margin-top: 20px; padding-top: 20px; border-top: 1px solid #e5e7eb; font-size: 12px; color: #6b7280; }
        </style>
      </head>
      <body>
        <div class="container">
          <div class="header">
            <h1>Response to your inquiry</h1>
          </div>
          <div class="content">
            <p>Hello ${originalMessage.name},</p>
            <p>Thank you for contacting ${settings.company_name || 'us'}.</p>
            
            <div class="reply-message">
              <p><strong>Our response:</strong></p>
              <p>${reply.reply_message.replace(/\n/g, '<br>')}</p>
            </div>
            
            <div class="original-message">
              <p><strong>Your original message:</strong></p>
              <p><strong>Subject:</strong> ${originalMessage.subject}</p>
              <p><strong>Message:</strong> ${originalMessage.message}</p>
              <p><small>Submitted on: ${new Date(originalMessage.created_at).toLocaleString()}</small></p>
            </div>
            
            <br>
            <p>If you have any further questions, please don't hesitate to reply to this email.</p>
            <p>Best regards,<br>The ${settings.company_name || 'Toolffy'} Team</p>
          </div>
          <div class="footer">
            <p>This email was sent in response to your inquiry at ${settings.company_name || 'our website'}.</p>
            <p>Reference ID: ${originalMessage.id}</p>
          </div>
        </div>
      </body>
      </html>
    `;

    await sendEmail({
      email: originalMessage.email,
      subject: emailSubject,
      message: emailMessage,
    });
  },

  // Get message statistics
  async getMessageStats() {
    const query = `SELECT * FROM get_contact_message_stats();`;
    const result = await database.query(query);
    return result.rows[0];
  },

  // Update contact settings
  async updateContactSettings(data: ContactSettingsUpdateData) {
    // Check if settings exist
    const existingSettings = await database.query(
      `SELECT id FROM contact_settings LIMIT 1`
    );

    let result;
    
    if (existingSettings.rows.length === 0) {
      // Insert new settings
      const fields = Object.keys(data);
      const values = Object.values(data);
      const placeholders = fields.map((_, i) => `$${i + 1}`).join(", ");
      const fieldNames = fields.join(", ");

      const query = `
        INSERT INTO contact_settings (${fieldNames})
        VALUES (${placeholders})
        RETURNING *;
      `;

      result = await database.query(query, values);
    } else {
      // Update existing settings
      const fields = Object.keys(data);
      const values = Object.values(data);
      const setClauses = fields.map((field, i) => `${field} = $${i + 1}`).join(", ");

      const query = `
        UPDATE contact_settings
        SET ${setClauses}, updated_at = CURRENT_TIMESTAMP
        RETURNING *;
      `;

      result = await database.query(query, values);
    }

    return result.rows[0];
  },

  // Mark message as read
  async markMessageAsRead(id: string, userId: string) {
    const query = `
      SELECT update_message_status($1, 'read', $2) as result;
    `;

    const result = await database.query(query, [id, userId]);
    return result.rows[0]?.result;
  },

  // Mark message as spam
  async markMessageAsSpam(id: string, spamReason?: string) {
    const query = `
      UPDATE contact_messages
      SET 
        status = 'spam',
        is_spam = true,
        spam_reason = COALESCE($2, spam_reason),
        updated_at = CURRENT_TIMESTAMP
      WHERE id = $1
      RETURNING *;
    `;

    const result = await database.query(query, [id, spamReason]);
    return result.rows[0];
  },

  // Get messages by email
  async getMessagesByEmail(email: string, limit: number = 10) {
    const query = `
      SELECT 
        cm.*,
        (
          SELECT JSON_AGG(
            JSON_BUILD_OBJECT(
              'id', cr.id,
              'reply_subject', cr.reply_subject,
              'created_at', cr.created_at,
              'is_internal', cr.is_internal
            )
          )
          FROM contact_message_replies cr
          WHERE cr.message_id = cm.id
          ORDER BY cr.created_at DESC
          LIMIT 3
        ) as recent_replies
      FROM contact_messages cm
      WHERE cm.email = $1 AND cm.is_spam = false
      ORDER BY cm.created_at DESC
      LIMIT $2;
    `;

    const result = await database.query(query, [email.toLowerCase(), limit]);
    return result.rows;
  },

  // Get messages needing follow-up
  async getFollowUpMessages() {
    const query = `
      SELECT 
        cm.*,
        EXTRACT(DAY FROM (CURRENT_DATE - follow_up_date)) as days_overdue
      FROM contact_messages cm
      WHERE cm.needs_follow_up = true 
        AND (cm.follow_up_date IS NULL OR cm.follow_up_date <= CURRENT_DATE)
        AND cm.is_spam = false
        AND cm.status != 'archived'
      ORDER BY 
        CASE WHEN cm.priority = 'urgent' THEN 1
             WHEN cm.priority = 'high' THEN 2
             ELSE 3
        END ASC,
        cm.follow_up_date ASC;
    `;

    const result = await database.query(query);
    return result.rows;
  },

  // Bulk update message status
  async bulkUpdateMessageStatus(messageIds: string[], status: string, userId?: string) {
    if (!Array.isArray(messageIds) || messageIds.length === 0) {
      throw new Error("Message IDs array is required");
    }

    if (messageIds.length > 100) {
      throw new Error("Cannot update more than 100 messages at once");
    }

    const placeholders = messageIds.map((_, index) => `$${index + 1}`).join(", ");
    const values = [...messageIds];
    let setClause = `status = '${status}', updated_at = CURRENT_TIMESTAMP`;

    if (status === "read") {
      setClause += `, is_read = true, read_at = CURRENT_TIMESTAMP, read_by = $${messageIds.length + 1}`;
      values.push(userId ?? "");
    }

    if (status === "replied") {
      setClause += `, is_replied = true, replied_at = CURRENT_TIMESTAMP, reply_sent_by = $${messageIds.length + 1}`;
      values.push(userId ?? "");
    }

    const query = `
      UPDATE contact_messages
      SET ${setClause}
      WHERE id IN (${placeholders})
      RETURNING id, email, subject, status;
    `;

    const result = await database.query(query, values);
    return result.rows;
  },
};