import database from "../../config/db";
import cloudinary from "../../config/cloudinary";
import { UploadedFile } from "express-fileupload";
import { QueryResult } from "pg";

export interface ProductCreateData {
  name: string;
  slug?: string;
  bio?: string;
  logo?: any;
  description: string;
  category_id?: string;
  tags?: any[];
  version?: string;
  website?: string;
  github_url?: string;
  documentation_url?: string;
  status?: string;
  is_featured?: boolean;
  launch_date?: Date;
  creator_id: string;
  creator_type?: string;
  meta_title?: string;
  meta_description?: string;
  meta_keywords?: string[];
}

export interface ProductUpdateData extends Partial<ProductCreateData> {
  upvotes?: number;
  rating?: number;
  followers?: number;
  views?: number;
  review_count?: number;
  last_viewed_at?: Date;
}

export interface ProductFilters {
  page?: number;
  limit?: number;
  category_id?: string;
  creator_id?: string;
  creator_type?: string;
  status?: string;
  is_featured?: boolean;
  is_verified?: boolean;
  is_sponsored?: boolean;
  min_rating?: number;
  min_upvotes?: number;
  search?: string;
  tags?: string[];
  sort_by?: string;
  order?: "asc" | "desc";
}

// Helper function to upload logo to Cloudinary
async function uploadLogoToCloudinary(
  file: any,
  oldPublicId?: string,
): Promise<any> {
  try {
    // Delete old logo if exists
    if (oldPublicId) {
      await cloudinary.uploader.destroy(oldPublicId);
    }

    // Upload new logo
    const result = await cloudinary.uploader.upload(file.tempFilePath, {
      folder: "product_logos",
      width: 500, // You can adjust these dimensions
      height: 500,
      crop: "limit",
      quality: "auto",
      fetch_format: "auto",
    });

    return {
      public_id: result.public_id,
      url: result.secure_url,
    };
  } catch (error) {
    const errorMessage = error instanceof Error ? error.message : String(error);
    throw new Error(`Failed to upload logo: ${errorMessage}`);
  }
}

export const productService = {
  // Delete product (soft delete by changing status)
  // async deleteProduct(id: string, userId?: string) {
  //   // Check if product exists
  //   const existingProduct = await this.getProductById(id, false);
  //   if (!existingProduct) {
  //     throw new Error("Product not found");
  //   }

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

  //   const query = `
  //     UPDATE products
  //     SET status = 'archived', updated_at = CURRENT_TIMESTAMP
  //     WHERE id = $1
  //     RETURNING *;
  //   `;

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

  // Create new product
  async createProduct(data: ProductCreateData, logoFile?: any) {
    const {
      name,
      slug,
      bio,
      description,
      category_id,
      tags,
      version = "1.0.0",
      website,
      github_url,
      documentation_url,
      status = "active",
      is_featured = false,
      launch_date,
      creator_id,
      creator_type = "user",
      meta_title,
      meta_description,
      meta_keywords,
    } = data;

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

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

    // Generate slug if not provided
    const productSlug = slug || this.generateSlug(name);

    // Check if slug already exists
    const slugCheck = await database.query(
      `SELECT id FROM products WHERE slug = $1`,
      [productSlug],
    );

    if (slugCheck.rows.length > 0) {
      throw new Error("Slug already exists");
    }

    // Check category if provided
    if (category_id) {
      const categoryCheck = await database.query(
        `SELECT id FROM products_categories WHERE id = $1 AND is_active = true`,
        [category_id],
      );

      if (categoryCheck.rows.length === 0) {
        throw new Error("Invalid category");
      }
    }

    // Check creator
    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("Invalid creator");
    }

    // If creator_type is specified, verify it matches account type
    if (creator_type && creatorCheck.rows[0].account_type !== creator_type) {
      throw new Error(`Creator must be a ${creator_type} account`);
    }

    // Upload logo to Cloudinary if provided
    let logoData = null;
    if (logoFile) {
      logoData = await uploadLogoToCloudinary(logoFile);
    }

    const query = `
      INSERT INTO products (
        name, slug, bio, logo, description, category_id, tags, version,
        website, github_url, documentation_url, status, is_featured,
        launch_date, creator_id, creator_type, meta_title, meta_description,
        meta_keywords
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19)
      RETURNING *;
    `;

    const values = [
      name.trim(),
      productSlug,
      bio?.trim(),
      logoData ? JSON.stringify(logoData) : null, // Store as JSONB
      description.trim(),
      category_id,
      tags || [],
      version,
      website,
      github_url,
      documentation_url,
      status,
      is_featured,
      launch_date,
      creator_id,
      creator_type,
      meta_title,
      meta_description,
      meta_keywords || [],
    ];

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

  // Update product
  async updateProduct(
    id: string,
    data: ProductUpdateData,
    logoFile?: any,
    userId?: string,
  ) {
    // Check if product exists
    const existingProduct = await this.getProductById(id, true);
    if (!existingProduct) {
      throw new Error("Product not found");
    }

    // Check ownership if userId is provided
    if (userId && existingProduct.creator_id !== userId) {
      // Check if user is admin (you can implement admin check here)
      const isAdmin = false; // Implement admin check as needed
      if (!isAdmin) {
        throw new Error("You can only update your own products");
      }
    }

    // Handle logo upload if new logo is provided
    if (logoFile) {
      const oldPublicId = existingProduct.logo?.public_id;
      const newLogoData = await uploadLogoToCloudinary(logoFile, oldPublicId);
      data.logo = newLogoData;
    }

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

    const fields = [
      "name",
      "slug",
      "bio",
      "logo",
      "description",
      "category_id",
      "tags",
      "version",
      "website",
      "github_url",
      "documentation_url",
      "status",
      "is_featured",
      "is_verified",
      "is_sponsored",
      "launch_date",
      "upvotes",
      "rating",
      "followers",
      "views",
      "review_count",
      "last_viewed_at",
      "meta_title",
      "meta_description",
      "meta_keywords",
    ];

    fields.forEach((field) => {
      if (data[field as keyof ProductUpdateData] !== undefined) {
        paramCount++;
        let value = data[field as keyof ProductUpdateData];

        // Stringify JSONB fields if they are objects
        if (field === "logo" && value && typeof value === "object") {
          value = JSON.stringify(value);
        } else if (field === "tags" && value && Array.isArray(value)) {
          value = JSON.stringify(value);
        } else if (field === "meta_keywords" && value && Array.isArray(value)) {
          value = JSON.stringify(value);
        }

        setClauses.push(`${field} = $${paramCount}`);
        values.push(value);
      }
    });

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

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

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

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

  // Delete product (soft delete by changing status)
  async deleteProduct(id: string, userId?: string) {
    // Check if product exists
    const existingProduct = await this.getProductById(id, true);
    if (!existingProduct) {
      throw new Error("Product not found");
    }

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

    // Optional: Delete logo from Cloudinary when product is permanently deleted
    // This is for soft delete, so we might not want to delete the logo yet
    // If you want to delete logo on soft delete, uncomment the lines below
    /*
    if (existingProduct.logo?.public_id) {
      await cloudinary.uploader.destroy(existingProduct.logo.public_id);
    }
    */

    const query = `
      UPDATE products
      SET status = 'archived', updated_at = CURRENT_TIMESTAMP
      WHERE id = $1
      RETURNING *;
    `;

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

  // Permanently delete product (hard delete)
  async permanentlyDeleteProduct(id: string, userId?: string) {
    // Check if product exists
    const existingProduct = await this.getProductById(id, true);
    if (!existingProduct) {
      throw new Error("Product not found");
    }

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

    // Delete logo from Cloudinary
    if (existingProduct.logo?.public_id) {
      await cloudinary.uploader.destroy(existingProduct.logo.public_id);
    }

    const query = `DELETE FROM products WHERE id = $1 RETURNING *;`;
    const result = await database.query(query, [id]);
    return result.rows[0];
  },

  // Get product by ID
  async getProductById(id: string, incrementViews: boolean = true) {
    const query = `
      SELECT 
        p.*,
        c.name as category_name,
        c.slug as category_slug,
        creator.username as creator_username,
        creator.display_name as creator_display_name,
        creator.avatar as creator_avatar,
        creator.account_type as creator_account_type,
                (
          SELECT JSON_AGG(
            JSON_BUILD_OBJECT(
              'id', pu.id,
              'version', pu.version,
              'title', pu.title,
              'release_date', pu.release_date,
              'is_latest', pu.is_latest
            )
          )
          FROM (
            SELECT *
            FROM products_updates pu
            WHERE pu.product_id = p.id
              AND pu.status = 'published'
              AND pu.is_public = true
            ORDER BY pu.release_date DESC
            LIMIT 5
          ) pu
        ) AS recent_updates,
        (
          SELECT AVG(rating)::DECIMAL(3,2)
          FROM products_reviews pr
          WHERE pr.product_id = p.id 
            AND pr.status = 'approved'
        ) as avg_rating,
        (
          SELECT COUNT(*)
          FROM products_reviews pr
          WHERE pr.product_id = p.id 
            AND pr.status = 'approved'
        ) as total_reviews,
        (
          SELECT COUNT(*)
          FROM products_comments pc
          WHERE pc.product_id = p.id 
            AND pc.status = 'active'
        ) as total_comments,
        (
  SELECT JSON_AGG(
    JSON_BUILD_OBJECT(
      'id', g.id,
      'url', g.url,
      'thumbnail', g.thumbnail,
      'type', g.type,
      'is_primary', g.is_primary
    )
  )
  FROM (
    SELECT *
    FROM products_gallerys pg
    WHERE pg.product_id = p.id
      AND pg.status = 'active'
    ORDER BY pg.is_primary DESC, pg.display_order ASC
  ) g
) AS gallery
      FROM products p
      LEFT JOIN products_categories c ON p.category_id = c.id
      LEFT JOIN accounts creator ON p.creator_id = creator.id
      WHERE p.id = $1;
    `;

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

    if (product && incrementViews) {
      // Increment view count
      await database.query(
        `UPDATE products SET views = views + 1, last_viewed_at = CURRENT_TIMESTAMP WHERE id = $1`,
        [id],
      );
      product.views += 1;
    }

    return product || null;
  },

  // Get product by slug
  async getProductBySlug(slug: string, incrementViews: boolean = true) {
    const query = `
      SELECT 
        p.*,
        c.name as category_name,
        c.slug as category_slug,
        creator.username as creator_username,
        creator.display_name as creator_display_name,
        creator.avatar as creator_avatar,
        creator.account_type as creator_account_type
      FROM products p
      LEFT JOIN products_categories c ON p.category_id = c.id
      LEFT JOIN accounts creator ON p.creator_id = creator.id
      WHERE p.slug = $1;
    `;

    const result = await database.query(query, [slug]);
    const product = result.rows[0];

    if (product && incrementViews) {
      await database.query(
        `UPDATE products SET views = views + 1, last_viewed_at = CURRENT_TIMESTAMP WHERE id = $1`,
        [product.id],
      );
      product.views += 1;
    }

    return product || null;
  },

  // Get all products with filters
  async getAllProducts(filters: ProductFilters = {}) {
    const {
      page = 1,
      limit = 20,
      category_id,
      creator_id,
      creator_type,
      status = "active",
      is_featured,
      is_verified,
      is_sponsored,
      min_rating = 0,
      min_upvotes = 0,
      search,
      tags = [],
      sort_by = "created_at",
      order = "desc",
    } = filters;

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

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

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

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

    // Creator type filter
    if (creator_type) {
      paramCount++;
      whereClauses.push(`p.creator_type = $${paramCount}`);
      values.push(creator_type);
    }

    // Featured filter
    if (is_featured !== undefined) {
      paramCount++;
      whereClauses.push(`p.is_featured = $${paramCount}`);
      values.push(is_featured);
    }

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

    // Sponsored filter
    if (is_sponsored !== undefined) {
      paramCount++;
      whereClauses.push(`p.is_sponsored = $${paramCount}`);
      values.push(is_sponsored);
    }

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

    // Upvotes filter
    if (min_upvotes > 0) {
      paramCount++;
      whereClauses.push(`p.upvotes >= $${paramCount}`);
      values.push(min_upvotes);
    }

    // Search filter
    if (search) {
      paramCount++;
      whereClauses.push(`
        (p.name ILIKE $${paramCount} OR 
         p.description ILIKE $${paramCount} OR 
         p.bio ILIKE $${paramCount})
      `);
      values.push(`%${search}%`);
    }

    // Tags filter
    if (tags.length > 0) {
      paramCount++;
      whereClauses.push(`p.tags @> $${paramCount}::jsonb`);
      values.push(JSON.stringify(tags));
    }

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

    // Validate sort field
    const validSortFields = [
      "name",
      "created_at",
      "updated_at",
      "rating",
      "upvotes",
      "followers",
      "views",
      "rank",
      "launch_date",
    ];
    const sortField = validSortFields.includes(sort_by)
      ? sort_by
      : "created_at";
    const sortOrder = order === "asc" ? "ASC" : "DESC";

    // Count total records
    const countQuery = `
      SELECT COUNT(*) as total
      FROM products p
      ${whereClause};
    `;

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

    // Get paginated data
    paramCount = values.length;
    const dataQuery = `
      SELECT 
        p.*,
        c.name as category_name,
        c.slug as category_slug,
        creator.username as creator_username,
        creator.display_name as creator_display_name,
        creator.avatar as creator_avatar
      FROM products p
      LEFT JOIN products_categories c ON p.category_id = c.id
      LEFT JOIN accounts creator ON p.creator_id = creator.id
      ${whereClause}
      ORDER BY p.${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,
      },
    };
  },

  // Search products with advanced filtering
  async searchProducts(query: string, filters: any = {}) {
    const { category_id, min_rating, min_upvotes, limit = 10 } = filters;

    const searchQuery = `
      SELECT 
        p.*,
        c.name as category_name,
        ts_rank(
          setweight(to_tsvector('english', p.name), 'A') || 
          setweight(to_tsvector('english', COALESCE(p.bio, '')), 'B') || 
          setweight(to_tsvector('english', p.description), 'C'),
          plainto_tsquery('english', $1)
        ) as rank
      FROM products p
      LEFT JOIN products_categories c ON p.category_id = c.id
      WHERE p.status = 'active'
        AND (
          setweight(to_tsvector('english', p.name), 'A') || 
          setweight(to_tsvector('english', COALESCE(p.bio, '')), 'B') || 
          setweight(to_tsvector('english', p.description), 'C')
        ) @@ plainto_tsquery('english', $1)
      ${category_id ? `AND p.category_id = $2` : ""}
      ${min_rating ? `AND p.rating >= ${min_rating}` : ""}
      ${min_upvotes ? `AND p.upvotes >= ${min_upvotes}` : ""}
      ORDER BY rank DESC, p.views DESC
      LIMIT ${limit};
    `;

    const values = category_id ? [query, category_id] : [query];
    const result = await database.query(searchQuery, values);
    return result.rows;
  },

  // Get trending products
  async getTrendingProducts(limit: number = 10, days: number = 7) {
    const query = `
      SELECT 
        p.*,
        c.name as category_name,
        creator.username as creator_username,
        creator.display_name as creator_display_name,
        (
          p.views * 0.3 + 
          p.upvotes * 0.4 + 
          p.followers * 0.3
        ) as trending_score
      FROM products p
      LEFT JOIN products_categories c ON p.category_id = c.id
      LEFT JOIN accounts creator ON p.creator_id = creator.id
      WHERE p.status = 'active'
        AND p.created_at >= CURRENT_DATE - INTERVAL '${days} days'
      ORDER BY trending_score DESC, p.created_at DESC
      LIMIT $1;
    `;

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

  // Get products by creator
  async getProductsByCreator(creatorId: string, filters: any = {}) {
    const { status = "active", is_featured, limit = 20, page = 1 } = filters;
    const offset = (page - 1) * limit;

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

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

    if (is_featured !== undefined) {
      paramCount++;
      whereClauses.push(`p.is_featured = $${paramCount}`);
      values.push(is_featured);
    }

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

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

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

    // Data query
    paramCount = values.length;
    const dataQuery = `
      SELECT p.*, c.name as category_name, c.slug as category_slug
      FROM products p
      LEFT JOIN products_categories c ON p.category_id = c.id
      ${whereClause}
      ORDER BY p.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),
      },
    };
  },

  // Upvote a product
  async upvoteProduct(productId: string, userId: string) {
    // Check if product exists
    const product = await this.getProductById(productId, false);
    if (!product) {
      throw new Error("Product not found");
    }

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

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

  // Follow a product
  async followProduct(productId: string, userId: string) {
    const product = await this.getProductById(productId, false);
    if (!product) {
      throw new Error("Product not found");
    }

    const query = `
      UPDATE products
      SET followers = followers + 1, updated_at = CURRENT_TIMESTAMP
      WHERE id = $1
      RETURNING followers;
    `;

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

  // Generate slug from name
  generateSlug(name: string): string {
    return name
      .toLowerCase()
      .replace(/[^a-z0-9\s-]/g, "")
      .replace(/\s+/g, "-")
      .replace(/--+/g, "-")
      .trim();
  },

  // Get statistics
  async getProductStats(productId: string) {
    const query = `
      SELECT 
        p.id,
        p.name,
        p.views,
        p.upvotes,
        p.followers,
        p.rating,
        (
          SELECT COUNT(*) 
          FROM products_reviews 
          WHERE product_id = p.id AND status = 'approved'
        ) as review_count,
        (
          SELECT COUNT(*) 
          FROM products_comments 
          WHERE product_id = p.id AND status = 'active'
        ) as comment_count,
        (
          SELECT COUNT(*) 
          FROM products_updates 
          WHERE product_id = p.id AND status = 'published'
        ) as update_count,
        (
          SELECT JSON_AGG(
            JSON_BUILD_OBJECT(
              'date', DATE(created_at),
              'views', COUNT(*)
            )
          )
          FROM (
            SELECT DATE(created_at) as created_at
            FROM product_analytics --假设你有 analytics table
            WHERE product_id = p.id 
              AND created_at >= CURRENT_DATE - INTERVAL '30 days'
            GROUP BY DATE(created_at)
          ) daily_views
        ) as views_last_30_days
      FROM products p
      WHERE p.id = $1;
    `;

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

  // Verify product (admin only)
  async verifyProduct(productId: string) {
    const query = `
      UPDATE products
      SET is_verified = true, updated_at = CURRENT_TIMESTAMP
      WHERE id = $1
      RETURNING *;
    `;

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

  // Feature product (admin only)
  async featureProduct(productId: string, featured: boolean = true) {
    const query = `
      UPDATE products
      SET is_featured = $1, updated_at = CURRENT_TIMESTAMP
      WHERE id = $2
      RETURNING *;
    `;

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