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

export interface GalleryCreateData {
  product_id: string;
  url: any; // Cloudinary URL object
  thumbnail: any; // Cloudinary thumbnail object
  type: "image" | "video" | "youtube" | "vimeo" | "gif";
  media_format?: string;
  caption?: string;
  public_id: string;
  resource_type?: "image" | "video" | "raw";
  display_order?: number;
  is_featured?: boolean;
  is_primary?: boolean;
}

export interface GalleryUpdateData extends Partial<GalleryCreateData> {
  status?: string;
}

export interface GalleryFilters {
  page?: number;
  limit?: number;
  product_id?: string;
  type?: string;
  status?: string;
  is_featured?: boolean;
  is_primary?: boolean;
  sort_by?: string;
  order?: "asc" | "desc";
}

export const productGalleryService = {
  // Upload and create gallery item
  async uploadGalleryItem(data: GalleryCreateData) {
    const {
      product_id,
      url,
      thumbnail,
      type = "image",
      media_format,
      caption,
      public_id,
      resource_type = "image",
      display_order = 0,
      is_featured = false,
      is_primary = false,
    } = data;

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

    if (!url || !thumbnail) {
      throw new Error("URL and thumbnail are required");
    }

    if (!public_id) {
      throw new Error("Public ID is required");
    }

    const validTypes = ["image", "video", "youtube", "vimeo", "gif"];
    if (!validTypes.includes(type)) {
      throw new Error(`Invalid type. Must be one of: ${validTypes.join(", ")}`);
    }

    // Check if product exists
    const productCheck = await database.query(
      `SELECT 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");
    }

    // If setting as primary, check if another primary exists
    if (is_primary) {
      const existingPrimary = await database.query(
        `SELECT id FROM products_gallerys 
         WHERE product_id = $1 AND is_primary = true AND status = 'active'`,
        [product_id],
      );

      if (existingPrimary.rows.length > 0) {
        // Update existing primary to non-primary
        await database.query(
          `UPDATE products_gallerys 
           SET is_primary = false, updated_at = CURRENT_TIMESTAMP 
           WHERE id = $1`,
          [existingPrimary.rows[0].id],
        );
      }
    }

    // If setting as featured, check featured limit (max 3)
    if (is_featured) {
      const featuredCount = await database.query(
        `SELECT COUNT(*) as count FROM products_gallerys 
         WHERE product_id = $1 AND is_featured = true AND status = 'active'`,
        [product_id],
      );

      if (parseInt(featuredCount.rows[0].count) >= 3) {
        throw new Error("Maximum 3 featured items allowed per product");
      }
    }

    const query = `
      INSERT INTO products_gallerys (
        product_id, url, thumbnail, type, media_format, caption,
        public_id, resource_type, display_order, is_featured, is_primary
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
      RETURNING *;
    `;

    const values = [
      product_id,
      url,
      thumbnail,
      type,
      media_format,
      caption?.trim(),
      public_id,
      resource_type,
      display_order,
      is_featured,
      is_primary,
    ];

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

  // Get gallery item by ID
  async getGalleryItemById(id: string) {
    const query = `
      SELECT 
        g.*,
        product.name as product_name,
        product.slug as product_slug,
        product.creator_id as product_creator_id
      FROM products_gallerys g
      LEFT JOIN products product ON g.product_id = product.id
      WHERE g.id = $1;
    `;

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

  // Get all gallery items for a product
  async getProductGallery(productId: string, filters: GalleryFilters = {}) {
    const {
      page = 1,
      limit = 50,
      type,
      status = "active",
      is_featured,
      is_primary,
      sort_by = "display_order",
      order = "asc",
    } = filters;

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

    // Type filter
    if (type) {
      paramCount++;
      whereClauses.push(`g.type = $${paramCount}`);
      values.push(type);
    }

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

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

    // Primary filter
    if (is_primary !== undefined) {
      paramCount++;
      whereClauses.push(`g.is_primary = $${paramCount}`);
      values.push(is_primary);
    }

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

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

    // Count total items
    const countQuery = `
      SELECT COUNT(*) as total
      FROM products_gallerys g
      ${whereClause};
    `;

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

    // Get paginated items
    paramCount = values.length;
    const dataQuery = `
      SELECT g.*
      FROM products_gallerys g
      ${whereClause}
      ORDER BY 
        g.is_primary DESC,
        g.is_featured DESC,
        g.${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 featured gallery items
  async getFeaturedGallery(productId: string, limit: number = 3) {
    const query = `
      SELECT g.*
      FROM products_gallerys g
      WHERE g.product_id = $1 
        AND g.status = 'active' 
        AND g.is_featured = true
      ORDER BY g.display_order ASC, g.created_at ASC
      LIMIT $2;
    `;

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

  // Get primary image
  async getPrimaryImage(productId: string) {
    const query = `
      SELECT g.*
      FROM products_gallerys g
      WHERE g.product_id = $1 
        AND g.status = 'active' 
        AND g.is_primary = true
      LIMIT 1;
    `;

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

  // Update gallery item
  async updateGalleryItem(
    id: string,
    data: GalleryUpdateData,
    userId?: string,
  ) {
    // Check if gallery item exists
    const existingItem = await this.getGalleryItemById(id);
    if (!existingItem) {
      throw new Error("Gallery item not found");
    }

    // Check ownership if userId is provided
    if (userId) {
      const product = await database.query(
        `SELECT creator_id FROM products WHERE id = $1`,
        [existingItem.product_id],
      );

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

      const isProductOwner = product.rows[0].creator_id === userId;
      const isAdmin = false; // Implement admin check

      if (!isProductOwner && !isAdmin) {
        throw new Error(
          "You can only update gallery items for your own products",
        );
      }
    }

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

    const fields = [
      "caption",
      "display_order",
      "is_featured",
      "is_primary",
      "status",
    ];

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

    // Handle is_primary logic
    if (data.is_primary === true) {
      // Remove primary from other items
      await database.query(
        `UPDATE products_gallerys 
         SET is_primary = false, updated_at = CURRENT_TIMESTAMP 
         WHERE product_id = $1 AND id != $2`,
        [existingItem.product_id, id],
      );
    }

    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_gallerys
      SET ${setClauses.join(", ")}
      WHERE id = $${paramCount}
      RETURNING *;
    `;

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

  // Delete gallery item (soft delete)
  async deleteGalleryItem(id: string, userId?: string) {
    // Check if gallery item exists
    const existingItem = await this.getGalleryItemById(id);
    if (!existingItem) {
      throw new Error("Gallery item not found");
    }

    // Check ownership if userId is provided
    if (userId) {
      const product = await database.query(
        `SELECT creator_id FROM products WHERE id = $1`,
        [existingItem.product_id],
      );

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

      const isProductOwner = product.rows[0].creator_id === userId;
      const isAdmin = false; // Implement admin check

      if (!isProductOwner && !isAdmin) {
        throw new Error(
          "You can only delete gallery items for your own products",
        );
      }
    }

    // Delete from Cloudinary
    try {
      await cloudinary.uploader.destroy(existingItem.public_id, {
        resource_type: existingItem.resource_type,
      });
    } catch (cloudinaryError) {
      console.error("Cloudinary delete error:", cloudinaryError);
      // Continue with database deletion even if Cloudinary fails
    }

    // Soft delete from database
    const query = `
      UPDATE products_gallerys
      SET status = 'archived', updated_at = CURRENT_TIMESTAMP
      WHERE id = $1
      RETURNING *;
    `;

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

  // Reorder gallery items
  async reorderGalleryItems(productId: string, itemIds: string[]) {
    if (!Array.isArray(itemIds) || itemIds.length === 0) {
      throw new Error("Item IDs array is required");
    }

    // Verify all items belong to the same product
    const itemsCheck = await database.query(
      `SELECT COUNT(*) as count FROM products_gallerys 
       WHERE id = ANY($1) AND product_id = $2 AND status = 'active'`,
      [itemIds, productId],
    );

    if (parseInt(itemsCheck.rows[0].count) !== itemIds.length) {
      throw new Error("One or more items do not belong to this product");
    }

    const updates = [];
    for (let i = 0; i < itemIds.length; i++) {
      const updateQuery = `
        UPDATE products_gallerys
        SET display_order = $1, updated_at = CURRENT_TIMESTAMP
        WHERE id = $2
        RETURNING id, display_order;
      `;

      const result = await database.query(updateQuery, [i, itemIds[i]]);
      updates.push(result.rows[0]);
    }

    return updates;
  },

  // Set featured items
  async setFeaturedItems(
    productId: string,
    itemIds: string[],
    userId?: string,
  ) {
    if (!Array.isArray(itemIds)) {
      throw new Error("Item IDs array is required");
    }

    if (itemIds.length > 3) {
      throw new Error("Maximum 3 featured items allowed");
    }

    // Check ownership if userId is provided
    if (userId) {
      const product = await database.query(
        `SELECT creator_id FROM products WHERE id = $1`,
        [productId],
      );

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

      const isProductOwner = product.rows[0].creator_id === userId;
      const isAdmin = false; // Implement admin check

      if (!isProductOwner && !isAdmin) {
        throw new Error(
          "You can only set featured items for your own products",
        );
      }
    }

    // Clear all featured items first
    await database.query(
      `UPDATE products_gallerys 
       SET is_featured = false, updated_at = CURRENT_TIMESTAMP 
       WHERE product_id = $1`,
      [productId],
    );

    // Set new featured items
    if (itemIds.length > 0) {
      // Verify items belong to the product
      const itemsCheck = await database.query(
        `SELECT id FROM products_gallerys 
         WHERE id = ANY($1) AND product_id = $2 AND status = 'active'`,
        [itemIds, productId],
      );

      const validIds = itemsCheck.rows.map((row) => row.id);

      if (validIds.length > 0) {
        const placeholders = validIds
          .map((_, index) => `$${index + 2}`)
          .join(", ");

        const query = `
          UPDATE products_gallerys
          SET is_featured = true, updated_at = CURRENT_TIMESTAMP
          WHERE id IN (${placeholders}) AND product_id = $1
          RETURNING *;
        `;

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

    return [];
  },

  // Set primary image
  async setPrimaryImage(productId: string, itemId: string, userId?: string) {
    // Check ownership if userId is provided
    if (userId) {
      const product = await database.query(
        `SELECT creator_id FROM products WHERE id = $1`,
        [productId],
      );

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

      const isProductOwner = product.rows[0].creator_id === userId;
      const isAdmin = false; // Implement admin check

      if (!isProductOwner && !isAdmin) {
        throw new Error("You can only set primary image for your own products");
      }
    }

    // Verify item belongs to the product
    const itemCheck = await database.query(
      `SELECT id FROM products_gallerys 
       WHERE id = $1 AND product_id = $2 AND status = 'active'`,
      [itemId, productId],
    );

    if (itemCheck.rows.length === 0) {
      throw new Error("Item not found or does not belong to this product");
    }

    // Clear existing primary
    await database.query(
      `UPDATE products_gallerys 
       SET is_primary = false, updated_at = CURRENT_TIMESTAMP 
       WHERE product_id = $1 AND is_primary = true`,
      [productId],
    );

    // Set new primary
    const query = `
      UPDATE products_gallerys
      SET is_primary = true, updated_at = CURRENT_TIMESTAMP
      WHERE id = $1
      RETURNING *;
    `;

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

  // Upload multiple gallery items
  async uploadMultipleItems(
    productId: string,
    items: GalleryCreateData[],
    userId?: string,
  ) {
    if (!Array.isArray(items) || items.length === 0) {
      throw new Error("Items array is required");
    }

    if (items.length > 20) {
      throw new Error("Maximum 20 items can be uploaded at once");
    }

    // Check ownership if userId is provided
    if (userId) {
      const product = await database.query(
        `SELECT creator_id FROM products WHERE id = $1`,
        [productId],
      );

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

      const isProductOwner = product.rows[0].creator_id === userId;
      const isAdmin = false; // Implement admin check

      if (!isProductOwner && !isAdmin) {
        throw new Error(
          "You can only upload gallery items for your own products",
        );
      }
    }

    const uploadedItems = [];
    const errors = [];

    for (const item of items) {
      try {
        const uploadedItem = await this.uploadGalleryItem({
          ...item,
          product_id: productId,
        });
        uploadedItems.push(uploadedItem);
      } catch (error: any) {
        errors.push({
          item: item.public_id,
          error: error.message,
        });
      }
    }

    return {
      success: uploadedItems,
      errors: errors.length > 0 ? errors : undefined,
    };
  },

  // Get gallery statistics
  async getGalleryStats(productId: string) {
    const query = `
      SELECT 
        COUNT(*) as total_items,
        COUNT(CASE WHEN type = 'image' THEN 1 END) as total_images,
        COUNT(CASE WHEN type = 'video' THEN 1 END) as total_videos,
        COUNT(CASE WHEN type = 'youtube' THEN 1 END) as total_youtube,
        COUNT(CASE WHEN type = 'vimeo' THEN 1 END) as total_vimeo,
        COUNT(CASE WHEN type = 'gif' THEN 1 END) as total_gifs,
        COUNT(CASE WHEN is_featured = true THEN 1 END) as featured_items,
        COUNT(CASE WHEN is_primary = true THEN 1 END) as primary_item,
        MIN(created_at) as first_uploaded,
        MAX(created_at) as last_uploaded
      FROM products_gallerys
      WHERE product_id = $1 AND status = 'active';
    `;

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

  // Get all gallery items by type
  async getGalleryByType(productId: string, type: string) {
    const validTypes = ["image", "video", "youtube", "vimeo", "gif"];
    if (!validTypes.includes(type)) {
      throw new Error(`Invalid type. Must be one of: ${validTypes.join(", ")}`);
    }

    const query = `
      SELECT g.*
      FROM products_gallerys g
      WHERE g.product_id = $1 
        AND g.type = $2 
        AND g.status = 'active'
      ORDER BY g.display_order ASC, g.created_at ASC;
    `;

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

  // Update product logo from primary image
  async updateProductLogoFromPrimary(productId: string, userId?: string) {
    // Check ownership if userId is provided
    if (userId) {
      const product = await database.query(
        `SELECT creator_id FROM products WHERE id = $1`,
        [productId],
      );

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

      const isProductOwner = product.rows[0].creator_id === userId;
      const isAdmin = false; // Implement admin check

      if (!isProductOwner && !isAdmin) {
        throw new Error(
          "You can only update product logo for your own products",
        );
      }
    }

    // Get primary image
    const primaryImage = await this.getPrimaryImage(productId);
    if (!primaryImage) {
      throw new Error("No primary image found");
    }

    // Update product logo
    const updateQuery = `
      UPDATE products
      SET logo = $1, updated_at = CURRENT_TIMESTAMP
      WHERE id = $2
      RETURNING id, name, logo;
    `;

    const result = await database.query(updateQuery, [
      primaryImage.url,
      productId,
    ]);
    return {
      product: result.rows[0],
      gallery_item: primaryImage,
    };
  },

  // Bulk delete gallery items
  async bulkDeleteGalleryItems(itemIds: string[], userId?: string) {
    if (!Array.isArray(itemIds) || itemIds.length === 0) {
      throw new Error("Item IDs array is required");
    }

    if (itemIds.length > 50) {
      throw new Error("Maximum 50 items can be deleted at once");
    }

    const deletedItems = [];
    const errors = [];

    for (const itemId of itemIds) {
      try {
        const deletedItem = await this.deleteGalleryItem(itemId, userId);
        deletedItems.push(deletedItem);
      } catch (error: any) {
        errors.push({
          item_id: itemId,
          error: error.message,
        });
      }
    }

    return {
      success: deletedItems,
      errors: errors.length > 0 ? errors : undefined,
    };
  },
};
