import database from "../../config/db";
import { QueryResult } from "pg";

export interface CommentCreateData {
  product_id: string;
  creator_id: string;
  comment: string;
  parent_id?: string;
  status?: string;
  is_owner_reply?: boolean;
  mentioned_users?: string[];
}

export interface CommentUpdateData extends Partial<CommentCreateData> {
  is_edited?: boolean;
  edit_history?: any[];
  reactions?: any;
  upvotes?: number;
  downvotes?: number;
}

export interface CommentFilters {
  page?: number;
  limit?: number;
  product_id?: string;
  creator_id?: string;
  parent_id?: string | null;
  status?: string;
  sort_by?: string;
  order?: "asc" | "desc";
  include_replies?: boolean;
  depth?: number;
}

export const productCommentService = {
  // Create new comment
  async createComment(data: CommentCreateData) {
    const {
      product_id,
      creator_id,
      comment,
      parent_id,
      status = "active",
      is_owner_reply = false,
      mentioned_users = [],
    } = data;

    // Validation
    if (!product_id) {
      throw new Error("Product ID is required");
    }

    if (!creator_id) {
      throw new Error("Creator ID is required");
    }

    if (!comment || comment.trim().length < 1) {
      throw new Error("Comment cannot be empty");
    }

    if (comment.length > 5000) {
      throw new Error("Comment is too long (max 5000 characters)");
    }

    // Check if product exists
    const productCheck = await database.query(
      `SELECT id, creator_id FROM products WHERE id = $1 AND status = 'active'`,
      [product_id]
    );

    if (productCheck.rows.length === 0) {
      throw new Error("Product not found or not active");
    }

    // Check if parent comment exists and belongs to same product
    if (parent_id) {
      const parentCheck = await database.query(
        `SELECT id, product_id FROM products_comments WHERE id = $1 AND status = 'active'`,
        [parent_id]
      );

      if (parentCheck.rows.length === 0) {
        throw new Error("Parent comment not found");
      }

      if (parentCheck.rows[0].product_id !== product_id) {
        throw new Error("Parent comment must belong to the same product");
      }
    }

    // Check if creator exists
    const creatorCheck = await database.query(
      `SELECT id, account_type FROM accounts WHERE id = $1 AND is_active = true`,
      [creator_id]
    );

    if (creatorCheck.rows.length === 0) {
      throw new Error("Creator not found");
    }

    // Determine if this is an owner reply
    const productCreatorId = productCheck.rows[0].creator_id;
    const actualIsOwnerReply = creator_id === productCreatorId || is_owner_reply;

    const query = `
      INSERT INTO products_comments (
        product_id, creator_id, comment, parent_id, 
        status, is_owner_reply, mentioned_users
      ) VALUES ($1, $2, $3, $4, $5, $6, $7)
      RETURNING *;
    `;

    const values = [
      product_id,
      creator_id,
      comment.trim(),
      parent_id,
      status,
      actualIsOwnerReply,
      mentioned_users,
    ];

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

  // Get comment by ID with details
  async getCommentById(id: string) {
    const query = `
      SELECT 
        c.*,
        creator.username as creator_username,
        creator.display_name as creator_display_name,
        creator.avatar as creator_avatar,
        product.name as product_name,
        product.slug as product_slug,
        (
          SELECT COUNT(*) 
          FROM products_comments rc 
          WHERE rc.parent_id = c.id AND rc.status = 'active'
        ) as replies_count,
        (
          SELECT JSON_AGG(
            JSON_BUILD_OBJECT(
              'id', r.id,
              'comment', r.comment,
              'created_at', r.created_at,
              'creator_id', r.creator_id,
              'creator_username', cr.username,
              'creator_display_name', cr.display_name,
              'creator_avatar', cr.avatar,
              'upvotes', r.upvotes,
              'downvotes', r.downvotes
            )
          )
          FROM products_comments r
          LEFT JOIN accounts cr ON r.creator_id = cr.id
          WHERE r.parent_id = c.id 
            AND r.status = 'active'
          ORDER BY r.created_at ASC
          LIMIT 10
        ) as recent_replies
      FROM products_comments c
      LEFT JOIN accounts creator ON c.creator_id = creator.id
      LEFT JOIN products product ON c.product_id = product.id
      WHERE c.id = $1;
    `;

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

  // Get all comments for a product
  async getProductComments(productId: string, filters: CommentFilters = {}) {
    const {
      page = 1,
      limit = 20,
      parent_id = null,
      status = "active",
      sort_by = "created_at",
      order = "desc",
      include_replies = false,
    } = filters;

    const offset = (page - 1) * limit;
    const whereClauses: string[] = [`c.product_id = $1`];
    const values: any[] = [productId];
    let paramCount = 1;

    // Parent filter
    if (parent_id === null) {
      whereClauses.push(`c.parent_id IS NULL`);
    } else if (parent_id !== undefined) {
      paramCount++;
      whereClauses.push(`c.parent_id = $${paramCount}`);
      values.push(parent_id);
    }

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

    const whereClause = `WHERE ${whereClauses.join(" AND ")}`;

    // Validate sort field
    const validSortFields = [
      "created_at", "updated_at", "upvotes", 
      "downvotes", "last_interaction_at"
    ];
    const sortField = validSortFields.includes(sort_by) ? sort_by : "created_at";
    const sortOrder = order === "asc" ? "ASC" : "DESC";

    // Count total comments
    const countQuery = `
      SELECT COUNT(*) as total
      FROM products_comments c
      ${whereClause};
    `;

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

    // Get paginated comments
    paramCount = values.length;
    const dataQuery = `
      SELECT 
        c.*,
        creator.username as creator_username,
        creator.display_name as creator_display_name,
        creator.avatar as creator_avatar,
        (
          SELECT COUNT(*) 
          FROM products_comments rc 
          WHERE rc.parent_id = c.id AND rc.status = 'active'
        ) as replies_count,
        (
          SELECT JSON_AGG(
            JSON_BUILD_OBJECT(
              'id', r.id,
              'comment', r.comment,
              'created_at', r.created_at,
              'creator_id', r.creator_id,
              'creator_username', cr.username,
              'creator_display_name', cr.display_name,
              'creator_avatar', cr.avatar,
              'upvotes', r.upvotes,
              'downvotes', r.downvotes
            )
          )
          FROM products_comments r
          LEFT JOIN accounts cr ON r.creator_id = cr.id
          WHERE r.parent_id = c.id 
            AND r.status = 'active'
            ${include_replies ? "" : "LIMIT 3"}
        ) as ${include_replies ? "replies" : "preview_replies"}
      FROM products_comments c
      LEFT JOIN accounts creator ON c.creator_id = creator.id
      ${whereClause}
      ORDER BY 
        c.is_pinned DESC,
        c.${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,
      },
    };
  },

  // Get comment thread/replies
  async getCommentThread(commentId: string, depth: number = 3) {
    const query = `
      WITH RECURSIVE comment_thread AS (
        -- Start with the requested comment
        SELECT 
          c.*,
          creator.username as creator_username,
          creator.display_name as creator_display_name,
          creator.avatar as creator_avatar,
          0 as level,
          ARRAY[c.id] as path
        FROM products_comments c
        LEFT JOIN accounts creator ON c.creator_id = creator.id
        WHERE c.id = $1 AND c.status = 'active'
        
        UNION ALL
        
        -- Recursively get replies
        SELECT 
          rc.*,
          rcreator.username as creator_username,
          rcreator.display_name as creator_display_name,
          rcreator.avatar as creator_avatar,
          ct.level + 1,
          ct.path || rc.id
        FROM products_comments rc
        LEFT JOIN accounts rcreator ON rc.creator_id = rcreator.id
        INNER JOIN comment_thread ct ON rc.parent_id = ct.id
        WHERE rc.status = 'active' AND ct.level < $2
      )
      SELECT 
        *,
        (SELECT COUNT(*) FROM products_comments WHERE parent_id = comment_thread.id AND status = 'active') as replies_count
      FROM comment_thread
      ORDER BY path, created_at ASC;
    `;

    const result = await database.query(query, [commentId, depth - 1]);
    return result.rows;
  },

  // Update comment
  async updateComment(id: string, data: CommentUpdateData, userId?: string) {
    // Check if comment exists
    const existingComment = await this.getCommentById(id);
    if (!existingComment) {
      throw new Error("Comment not found");
    }

    // Check ownership if userId is provided
    if (userId && existingComment.creator_id !== userId) {
      const isAdmin = false; // Implement admin check
      if (!isAdmin) {
        throw new Error("You can only update your own comments");
      }
    }

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

    const fields = [
      "comment", "status", "is_edited", "edit_history",
      "reactions", "mentioned_users", "upvotes", "downvotes",
      "is_pinned", "is_owner_reply"
    ];

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

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

    // Add edit history if comment is being edited
    if (data.comment && existingComment.comment !== data.comment) {
      const editHistory = existingComment.edit_history || [];
      editHistory.push({
        comment: existingComment.comment,
        edited_at: new Date().toISOString(),
      });

      paramCount++;
      setClauses.push(`edit_history = $${paramCount}`);
      values.push(editHistory);

      paramCount++;
      setClauses.push(`is_edited = true`);
    }

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

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

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

  // Delete comment (soft delete)
  async deleteComment(id: string, userId?: string) {
    // Check if comment exists
    const existingComment = await this.getCommentById(id);
    if (!existingComment) {
      throw new Error("Comment not found");
    }

    // Check ownership if userId is provided
    if (userId && existingComment.creator_id !== userId) {
      const isAdmin = false; // Implement admin check
      if (!isAdmin) {
        throw new Error("You can only delete your own comments");
      }
    }

    // Check if comment has replies
    if (existingComment.replies_count > 0) {
      // Soft delete by changing status
      const query = `
        UPDATE products_comments
        SET status = 'deleted', updated_at = CURRENT_TIMESTAMP
        WHERE id = $1
        RETURNING *;
      `;

      const result = await database.query(query, [id]);
      return result.rows[0];
    } else {
      // Hard delete if no replies
      const query = `
        DELETE FROM products_comments
        WHERE id = $1
        RETURNING *;
      `;

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

  // Upvote a comment
  async upvoteComment(commentId: string, userId: string) {
    // Check if comment exists
    const comment = await this.getCommentById(commentId);
    if (!comment) {
      throw new Error("Comment not found");
    }

    // Check if user has already voted (you might want to track this in a separate table)
    // For now, we'll just increment
    const query = `
      UPDATE products_comments
      SET upvotes = upvotes + 1, last_interaction_at = CURRENT_TIMESTAMP
      WHERE id = $1
      RETURNING upvotes;
    `;

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

  // Downvote a comment
  async downvoteComment(commentId: string, userId: string) {
    const comment = await this.getCommentById(commentId);
    if (!comment) {
      throw new Error("Comment not found");
    }

    const query = `
      UPDATE products_comments
      SET downvotes = downvotes + 1, last_interaction_at = CURRENT_TIMESTAMP
      WHERE id = $1
      RETURNING downvotes;
    `;

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

  // Pin/unpin a comment (product owner or admin only)
  async pinComment(commentId: string, pinned: boolean = true) {
    const query = `
      UPDATE products_comments
      SET is_pinned = $1, updated_at = CURRENT_TIMESTAMP
      WHERE id = $2
      RETURNING *;
    `;

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

  // Get user's comments
  async getUserComments(userId: string, filters: any = {}) {
    const { page = 1, limit = 20, product_id, status = "active" } = filters;
    const offset = (page - 1) * limit;

    const whereClauses: string[] = [`c.creator_id = $1`];
    const values: any[] = [userId];
    let paramCount = 1;

    if (status) {
      paramCount++;
      whereClauses.push(`c.status = $${paramCount}`);
      values.push(status);
    }

    if (product_id) {
      paramCount++;
      whereClauses.push(`c.product_id = $${paramCount}`);
      values.push(product_id);
    }

    const whereClause = `WHERE ${whereClauses.join(" AND ")}`;

    // Count query
    const countQuery = `
      SELECT COUNT(*) as total
      FROM products_comments c
      ${whereClause};
    `;

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

    // Data query
    paramCount = values.length;
    const dataQuery = `
      SELECT 
        c.*,
        product.name as product_name,
        product.slug as product_slug,
        product.logo as product_logo,
        (
          SELECT COUNT(*) 
          FROM products_comments rc 
          WHERE rc.parent_id = c.id AND rc.status = 'active'
        ) as replies_count
      FROM products_comments c
      LEFT JOIN products product ON c.product_id = product.id
      ${whereClause}
      ORDER BY c.created_at DESC
      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),
      },
    };
  },

  // Get comment statistics for a product
  async getProductCommentStats(productId: string) {
    const query = `
      SELECT 
        COUNT(*) as total_comments,
        COUNT(DISTINCT creator_id) as unique_commenters,
        SUM(upvotes) as total_upvotes,
        SUM(downvotes) as total_downvotes,
        AVG(
          CASE 
            WHEN upvotes + downvotes > 0 
            THEN upvotes::FLOAT / (upvotes + downvotes) 
            ELSE 0 
          END
        ) as avg_sentiment,
        MAX(created_at) as latest_comment
      FROM products_comments
      WHERE product_id = $1 AND status = 'active';
    `;

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

  // Get recent comments across all products
  async getRecentComments(limit: number = 10) {
    const query = `
      SELECT 
        c.*,
        creator.username as creator_username,
        creator.display_name as creator_display_name,
        creator.avatar as creator_avatar,
        product.name as product_name,
        product.slug as product_slug,
        product.logo as product_logo
      FROM products_comments c
      LEFT JOIN accounts creator ON c.creator_id = creator.id
      LEFT JOIN products product ON c.product_id = product.id
      WHERE c.status = 'active' AND c.parent_id IS NULL
      ORDER BY c.created_at DESC
      LIMIT $1;
    `;

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

  // Report a comment
  async reportComment(commentId: string, userId: string, reason: string) {
    // Check if comment exists
    const comment = await this.getCommentById(commentId);
    if (!comment) {
      throw new Error("Comment not found");
    }

    // Check if user is not the comment owner
    if (comment.creator_id === userId) {
      throw new Error("You cannot report your own comment");
    }

    // Insert report (you might have a separate reports table)
    // For now, we'll update the comment status
    const query = `
      UPDATE products_comments
      SET status = 'pending', updated_at = CURRENT_TIMESTAMP
      WHERE id = $1
      RETURNING *;
    `;

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

  // Get comments with mentions
  async getCommentsWithMentions(userId: string, filters: any = {}) {
    const { page = 1, limit = 20 } = filters;
    const offset = (page - 1) * limit;

    const query = `
      SELECT 
        c.*,
        creator.username as creator_username,
        creator.display_name as creator_display_name,
        creator.avatar as creator_avatar,
        product.name as product_name,
        product.slug as product_slug
      FROM products_comments c
      LEFT JOIN accounts creator ON c.creator_id = creator.id
      LEFT JOIN products product ON c.product_id = product.id
      WHERE $1 = ANY(c.mentioned_users) AND c.status = 'active'
      ORDER BY c.created_at DESC
      LIMIT $2 OFFSET $3;
    `;

    const result = await database.query(query, [userId, limit, offset]);

    // Count query
    const countQuery = `
      SELECT COUNT(*) as total
      FROM products_comments
      WHERE $1 = ANY(mentioned_users) AND status = 'active';
    `;

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

    return {
      data: result.rows,
      pagination: {
        page,
        limit,
        total,
        total_pages: Math.ceil(total / limit),
      },
    };
  },
};