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

export interface ReviewCreateData {
  product_id: string;
  creator_id: string;
  title?: string;
  content: string;
  rating: number;
  pros?: string[];
  cons?: string[];
  images?: any[];
  videos?: any[];
  would_recommend?: boolean;
  is_verified_purchase?: boolean;
}

export interface ReviewUpdateData extends Partial<ReviewCreateData> {
  helpful_count?: number;
  not_helpful_count?: number;
  status?: string;
}

export interface ReviewFilters {
  page?: number;
  limit?: number;
  product_id?: string;
  creator_id?: string;
  status?: string;
  min_rating?: number;
  max_rating?: number;
  is_verified_purchase?: boolean;
  would_recommend?: boolean;
  sort_by?: string;
  order?: "asc" | "desc";
  helpful?: boolean;
}

export const productReviewService = {
  // Create new review
  async createReview(data: ReviewCreateData) {
    const {
      product_id,
      creator_id,
      title,
      content,
      rating,
      pros = [],
      cons = [],
      images = [],
      videos = [],
      would_recommend,
      is_verified_purchase = false,
    } = data;

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

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

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

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

    if (rating < 0 || rating > 5) {
      throw new Error("Rating must be between 0 and 5");
    }

    // 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 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");
    }

    // Check if user has already reviewed this product
    const existingReview = await database.query(
      `SELECT id FROM products_reviews WHERE product_id = $1 AND creator_id = $2`,
      [product_id, creator_id],
    );

    if (existingReview.rows.length > 0) {
      throw new Error("You have already reviewed this product");
    }

    // Auto-determine would_recommend based on rating if not provided
    const autoWouldRecommend =
      would_recommend !== undefined ? would_recommend : rating >= 3.5;

    const query = `
      INSERT INTO products_reviews (
        product_id, creator_id, title, content, rating,
        pros, cons, images, videos, would_recommend,
        is_verified_purchase, status
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, 'pending')
      RETURNING *;
    `;

    const values = [
      product_id,
      creator_id,
      title?.trim(),
      content.trim(),
      rating,
      pros,
      cons,
      images,
      videos,
      autoWouldRecommend,
      is_verified_purchase,
    ];

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

  // Get review by ID
  async getReviewById(id: string) {
    const query = `
      SELECT 
        r.*,
        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,
        (
          SELECT COUNT(*)
          FROM products_reviews helpful
          WHERE helpful.creator_id = r.creator_id 
            AND helpful.status = 'approved'
        ) as creator_total_reviews,
        (
          SELECT AVG(rating)::DECIMAL(3,2)
          FROM products_reviews avg_rating
          WHERE avg_rating.creator_id = r.creator_id 
            AND avg_rating.status = 'approved'
        ) as creator_avg_rating
      FROM products_reviews r
      LEFT JOIN accounts creator ON r.creator_id = creator.id
      LEFT JOIN products product ON r.product_id = product.id
      WHERE r.id = $1;
    `;

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

  // Get review by product and user
  async getUserProductReview(productId: string, userId: string) {
    const query = `
      SELECT 
        r.*,
        creator.username as creator_username,
        creator.display_name as creator_display_name,
        creator.avatar as creator_avatar
      FROM products_reviews r
      LEFT JOIN accounts creator ON r.creator_id = creator.id
      WHERE r.product_id = $1 AND r.creator_id = $2;
    `;

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

  // Get all reviews with filters
  async getAllReviews(filters: ReviewFilters = {}) {
    const {
      page = 1,
      limit = 20,
      product_id,
      creator_id,
      status = "approved",
      min_rating = 0,
      max_rating = 5,
      is_verified_purchase,
      would_recommend,
      sort_by = "created_at",
      order = "desc",
      helpful = false,
    } = filters;

    const offset = (page - 1) * limit;
    const whereClauses: string[] = [];
    const values: any[] = [];
    let paramCount = 0;

    // Product filter
    if (product_id) {
      paramCount++;
      whereClauses.push(`r.product_id = $${paramCount}`);
      values.push(product_id);
    }

    // Creator filter
    if (creator_id) {
      paramCount++;
      whereClauses.push(`r.creator_id = $${paramCount}`);
      values.push(creator_id);
    }

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

    // Rating range filter
    if (min_rating > 0) {
      paramCount++;
      whereClauses.push(`r.rating >= $${paramCount}`);
      values.push(min_rating);
    }

    if (max_rating < 5) {
      paramCount++;
      whereClauses.push(`r.rating <= $${paramCount}`);
      values.push(max_rating);
    }

    // Verified purchase filter
    if (is_verified_purchase !== undefined) {
      paramCount++;
      whereClauses.push(`r.is_verified_purchase = $${paramCount}`);
      values.push(is_verified_purchase);
    }

    // Would recommend filter
    if (would_recommend !== undefined) {
      paramCount++;
      whereClauses.push(`r.would_recommend = $${paramCount}`);
      values.push(would_recommend);
    }

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

    // Validate sort field
    const validSortFields = [
      "created_at",
      "published_at",
      "rating",
      "helpful_count",
      "not_helpful_count",
      "updated_at",
    ];

    let sortField = validSortFields.includes(sort_by) ? sort_by : "created_at";

    // Sort by helpfulness score if helpful flag is true
    if (helpful) {
      sortField = "(r.helpful_count - r.not_helpful_count)";
    }

    const sortOrder = order === "asc" ? "ASC" : "DESC";

    // Count total reviews
    const countQuery = `
      SELECT COUNT(*) as total
      FROM products_reviews r
      ${whereClause};
    `;

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

    // Get paginated reviews
    paramCount = values.length;
    const dataQuery = `
      SELECT 
        r.*,
        creator.username as creator_username,
        creator.display_name as creator_display_name,
        creator.avatar as creator_avatar,
        creator.account_type as creator_account_type,
        product.name as product_name,
        product.slug as product_slug,
        product.logo as product_logo,
        (r.helpful_count - r.not_helpful_count) as helpfulness_score
      FROM products_reviews r
      LEFT JOIN accounts creator ON r.creator_id = creator.id
      LEFT JOIN products product ON r.product_id = product.id
      ${whereClause}
      ORDER BY ${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 product reviews summary/statistics
  async getProductReviewStats(productId: string) {
    const query = `
      SELECT 
        COUNT(*) as total_reviews,
        COUNT(CASE WHEN status = 'approved' THEN 1 END) as approved_reviews,
        COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_reviews,
        COUNT(CASE WHEN status = 'rejected' THEN 1 END) as rejected_reviews,
        AVG(rating)::DECIMAL(3,2) as average_rating,
        MIN(rating)::DECIMAL(3,2) as min_rating,
        MAX(rating)::DECIMAL(3,2) as max_rating,
        COUNT(CASE WHEN would_recommend = true THEN 1 END) as would_recommend_count,
        COUNT(CASE WHEN is_verified_purchase = true THEN 1 END) as verified_purchase_count,
        COUNT(CASE WHEN rating = 1 THEN 1 END) as one_star,
        COUNT(CASE WHEN rating = 2 THEN 1 END) as two_stars,
        COUNT(CASE WHEN rating = 3 THEN 1 END) as three_stars,
        COUNT(CASE WHEN rating = 4 THEN 1 END) as four_stars,
        COUNT(CASE WHEN rating = 5 THEN 1 END) as five_stars,
        SUM(helpful_count) as total_helpful,
        SUM(not_helpful_count) as total_not_helpful
      FROM products_reviews
      WHERE product_id = $1;
    `;

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

  // Get rating distribution for a product
  async getProductRatingDistribution(productId: string) {
    const query = `
      SELECT 
        rating,
        COUNT(*) as count,
        ROUND((COUNT(*) * 100.0 / SUM(COUNT(*)) OVER()), 2) as percentage
      FROM products_reviews
      WHERE product_id = $1 AND status = 'approved'
      GROUP BY rating
      ORDER BY rating DESC;
    `;

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

  // Update review
  async updateReview(id: string, data: ReviewUpdateData, userId?: string) {
    // Check if review exists
    const existingReview = await this.getReviewById(id);
    if (!existingReview) {
      throw new Error("Review not found");
    }

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

    // If status is changing to approved and wasn't approved before, set published_at
    let publishedAtClause = "";
    if (data.status === "approved" && existingReview.status !== "approved") {
      publishedAtClause = ", published_at = CURRENT_TIMESTAMP";
    }

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

    const fields = [
      "title",
      "content",
      "rating",
      "pros",
      "cons",
      "images",
      "videos",
      "would_recommend",
      "helpful_count",
      "not_helpful_count",
      "status",
      "is_verified_purchase",
    ];

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

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

    paramCount++;
    setClauses.push(`updated_at = CURRENT_TIMESTAMP${publishedAtClause}`);
    values.push(id);

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

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

  // Delete review
  async deleteReview(id: string, userId?: string) {
    // Check if review exists
    const existingReview = await this.getReviewById(id);
    if (!existingReview) {
      throw new Error("Review not found");
    }

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

    const query = `
      DELETE FROM products_reviews
      WHERE id = $1
      RETURNING *;
    `;

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

  // Mark review as helpful
  async markHelpful(reviewId: string, userId: string, helpful: boolean = true) {
    // Check if review exists
    const review = await this.getReviewById(reviewId);
    if (!review) {
      throw new Error("Review not found");
    }

    // Check if user is not the review creator
    if (review.creator_id === userId) {
      throw new Error("You cannot mark your own review as helpful");
    }

    // Here you would typically check if user has already voted
    // and track votes in a separate table
    // For simplicity, we'll just increment

    const field = helpful ? "helpful_count" : "not_helpful_count";
    const query = `
      UPDATE products_reviews
      SET ${field} = ${field} + 1, updated_at = CURRENT_TIMESTAMP
      WHERE id = $1
      RETURNING helpful_count, not_helpful_count;
    `;

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

  // Approve/reject review (admin/moderator)
  async moderateReview(
    reviewId: string,
    status: string,
    moderatorId: string,
    reason?: string,
  ) {
    if (!["approved", "rejected"].includes(status)) {
      throw new Error("Invalid status for moderation");
    }

    const query = `
      UPDATE products_reviews
      SET status = $1, updated_at = CURRENT_TIMESTAMP,
          published_at = CASE 
            WHEN $1 = 'approved' AND published_at IS NULL 
            THEN CURRENT_TIMESTAMP 
            ELSE published_at 
          END
      WHERE id = $2
      RETURNING *;
    `;

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

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

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

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

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

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

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

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

    // Data query
    paramCount = values.length;
    const dataQuery = `
      SELECT 
        r.*,
        product.name as product_name,
        product.slug as product_slug,
        product.logo as product_logo,
        product.creator_id as product_creator_id,
        (r.helpful_count - r.not_helpful_count) as helpfulness_score
      FROM products_reviews r
      LEFT JOIN products product ON r.product_id = product.id
      ${whereClause}
      ORDER BY r.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 recent reviews
  async getRecentReviews(limit: number = 10) {
    const query = `
      SELECT 
        r.*,
        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_reviews r
      LEFT JOIN accounts creator ON r.creator_id = creator.id
      LEFT JOIN products product ON r.product_id = product.id
      WHERE r.status = 'approved'
      ORDER BY r.published_at DESC NULLS LAST, r.created_at DESC
      LIMIT $1;
    `;

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

  // Get top helpful reviews
  async getTopHelpfulReviews(productId: string, limit: number = 5) {
    const query = `
      SELECT 
        r.*,
        creator.username as creator_username,
        creator.display_name as creator_display_name,
        creator.avatar as creator_avatar,
        (r.helpful_count - r.not_helpful_count) as helpfulness_score
      FROM products_reviews r
      LEFT JOIN accounts creator ON r.creator_id = creator.id
      WHERE r.product_id = $1 AND r.status = 'approved'
      ORDER BY helpfulness_score DESC, r.helpful_count DESC
      LIMIT $2;
    `;

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

  // Get verified purchase reviews
  async getVerifiedPurchaseReviews(productId: string, limit: number = 10) {
    const query = `
      SELECT 
        r.*,
        creator.username as creator_username,
        creator.display_name as creator_display_name,
        creator.avatar as creator_avatar
      FROM products_reviews r
      LEFT JOIN accounts creator ON r.creator_id = creator.id
      WHERE r.product_id = $1 
        AND r.status = 'approved' 
        AND r.is_verified_purchase = true
      ORDER BY r.created_at DESC
      LIMIT $2;
    `;

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

  // Get reviews by rating
  async getReviewsByRating(
    productId: string,
    rating: number,
    filters: any = {},
  ) {
    const { page = 1, limit = 20, status = "approved" } = filters;
    const offset = (page - 1) * limit;

    const whereClauses: string[] = [
      `r.product_id = $1`,
      `r.rating = $2`,
      `r.status = $3`,
    ];

    const values: any[] = [productId, rating, status];
    let paramCount = 3;

    // Count query
    const countQuery = `
      SELECT COUNT(*) as total
      FROM products_reviews r
      WHERE ${whereClauses.join(" AND ")};
    `;

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

    // Data query
    const dataQuery = `
      SELECT 
        r.*,
        creator.username as creator_username,
        creator.display_name as creator_display_name,
        creator.avatar as creator_avatar
      FROM products_reviews r
      LEFT JOIN accounts creator ON r.creator_id = creator.id
      WHERE ${whereClauses.join(" AND ")}
      ORDER BY r.helpful_count DESC, r.created_at DESC
      LIMIT $4 OFFSET $5;
    `;

    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),
      },
    };
  },

  // Check if user can review product
  async canUserReviewProduct(productId: string, userId: string) {
    // Check if user has already reviewed
    const existingReview = await database.query(
      `SELECT id FROM products_reviews WHERE product_id = $1 AND creator_id = $2`,
      [productId, userId],
    );

    if (existingReview.rows.length > 0) {
      return {
        can_review: false,
        reason: "You have already reviewed this product",
        existing_review_id: existingReview.rows[0].id,
      };
    }

    // Check if product exists and is active
    const product = await database.query(
      `SELECT id, status FROM products WHERE id = $1`,
      [productId],
    );

    if (product.rows.length === 0) {
      return {
        can_review: false,
        reason: "Product not found",
      };
    }

    if (product.rows[0].status !== "active") {
      return {
        can_review: false,
        reason: "Product is not active",
      };
    }

    // Additional checks can be added here
    // For example: check if user has purchased the product for verified reviews

    return {
      can_review: true,
      reason: "",
    };
  },

  // Get review summary for dashboard
  async getReviewSummary(days: number = 30) {
    const query = `
      SELECT 
        DATE(created_at) as date,
        COUNT(*) as total_reviews,
        COUNT(CASE WHEN status = 'approved' THEN 1 END) as approved_reviews,
        COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_reviews,
        AVG(rating)::DECIMAL(3,2) as avg_rating
      FROM products_reviews
      WHERE created_at >= CURRENT_DATE - INTERVAL '${days} days'
      GROUP BY DATE(created_at)
      ORDER BY date DESC;
    `;

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

  // Bulk update reviews (admin)
  async bulkUpdateReviews(
    reviewIds: string[],
    data: Partial<ReviewUpdateData>,
  ) {
    if (reviewIds.length === 0) {
      throw new Error("No review IDs provided");
    }

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

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

    const fields = ["status", "is_verified_purchase"];

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

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

    // Add updated_at
    setClauses.push("updated_at = CURRENT_TIMESTAMP");

    // Create placeholders for review IDs
    const idPlaceholders = reviewIds
      .map((_, index) => `$${paramCount + index + 1}`)
      .join(", ");

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

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