import { query } from './db';
import type { Sneaker, ShopOffer } from '../data/mock';

/**
 * Fetch products from DB and map to the Sneaker interface used by components.
 */

interface DBProduct {
  id: number;
  name: string;
  slug: string;
  brand: string;
  colorway: string | null;
  gender: string | null;
  image_url: string | null;
  original_price: number | null;
  lowest_price: number;
  offer_count: number;
}

interface DBOffer {
  product_id: number;
  price: number;
  original_price: number | null;
  product_url: string;
  in_stock: boolean;
  feed_name: string;
  merchant_name: string | null;
  sizes: { size: string; price: number; ean?: string; url?: string }[] | null;
}

function brandSlug(brand: string): string {
  return (brand || 'unknown').toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/(^-|-$)/g, '');
}

function mapToSneaker(p: DBProduct, offers: DBOffer[]): Sneaker {
  const shopOffers: ShopOffer[] = offers.map(o => ({
    shop: o.merchant_name || o.feed_name,
    price: Number(o.price),
    originalPrice: o.original_price ? Number(o.original_price) : undefined,
    url: o.product_url,
    inStock: o.in_stock,
    sizes: (o.sizes || []).map(s => s.size),
  }));

  const lowestPrice = Number(p.lowest_price) || shopOffers.reduce((min, o) => o.price < min ? o.price : min, Infinity);
  const maxOfferOriginal = shopOffers.reduce((max, o) => (o.originalPrice && o.originalPrice > max) ? o.originalPrice : max, 0);
  const originalPrice = Number(p.original_price) || maxOfferOriginal || lowestPrice;

  // Collect all unique sizes across all offers
  const allSizes = [...new Set(shopOffers.flatMap(o => o.sizes))].filter(Boolean);

  return {
    id: String(p.id),
    slug: p.slug,
    name: p.name,
    brand: p.brand || 'Unknown',
    brandSlug: brandSlug(p.brand),
    colorway: p.colorway || '',
    image: p.image_url || 'https://placehold.co/600x600/0D0B10/D69F7E?text=No+Image',
    lowestPrice,
    originalPrice: originalPrice > lowestPrice ? originalPrice : lowestPrice,
    offers: shopOffers,
    sizes: allSizes,
    gender: (p.gender as any) || 'unisex',
    releaseDate: '',
    trending: false,
    priceDrop: originalPrice > lowestPrice,
    priceHistory: [],
  };
}

export async function getProductsFromDB(options: {
  limit?: number;
  offset?: number;
  orderBy?: 'popular' | 'newest' | 'price_asc' | 'price_desc' | 'discount';
  brand?: string;
  brands?: string[];
  gender?: string;
  minPrice?: number;
  maxPrice?: number;
  model?: string;
  size?: string;
} = {}): Promise<Sneaker[]> {
  const { limit = 20, offset = 0, orderBy = 'popular', brand, brands, gender, minPrice, maxPrice, model, size } = options;

  let orderClause: string;
  switch (orderBy) {
    case 'newest': orderClause = 'p.created_at DESC NULLS LAST'; break;
    case 'price_asc': orderClause = 'min_price ASC'; break;
    case 'price_desc': orderClause = 'min_price DESC'; break;
    case 'discount': orderClause = 'discount_pct DESC NULLS LAST'; break;
    default: orderClause = 'offer_count DESC, p.clicks_7d DESC NULLS LAST'; break;
  }

  const params: any[] = [limit, offset];
  let brandFilter = '';
  if (brand) {
    params.push(brand);
    brandFilter = `AND (LOWER(p.brand) = LOWER($${params.length}) OR LOWER(REGEXP_REPLACE(REGEXP_REPLACE(p.brand, '[^a-zA-Z0-9]+', '-', 'g'), '(^-|-$)', '', 'g')) = LOWER($${params.length}))`;
  }
  if (brands && brands.length > 0) {
    params.push(brands.map(b => b.toLowerCase()));
    brandFilter += ` AND LOWER(p.brand) = ANY($${params.length})`;
  }
  let genderFilter = '';
  if (gender) {
    params.push(gender.toLowerCase());
    genderFilter = `AND LOWER(p.gender) = $${params.length}`;
  }
  let priceFilter = '';
  let modelFilter = '';
  if (model) {
    params.push(model);
    modelFilter = `AND p.model = $${params.length}`;
  }
  let sizeFilter = '';
  if (size) {
    params.push(size);
    sizeFilter = `AND EXISTS (SELECT 1 FROM jsonb_array_elements(po.sizes) s WHERE s->>'size' = $${params.length})`;
  }
  if (minPrice !== undefined) {
    params.push(minPrice);
    priceFilter += ` AND po.price >= $${params.length}`;
  }
  if (maxPrice !== undefined && maxPrice < 500) {
    params.push(maxPrice);
    priceFilter += ` AND po.price <= $${params.length}`;
  }

  const productResult = await query<any>(`
    SELECT p.id, p.name, p.slug, p.brand, p.colorway, p.gender, p.image_url, p.original_price,
           MIN(po.price) as lowest_price,
           COUNT(po.id) as offer_count,
           CASE WHEN MAX(po.original_price) > 0 AND MIN(po.price) < MAX(po.original_price)
                THEN ROUND(((MAX(po.original_price) - MIN(po.price)) / MAX(po.original_price) * 100))
                ELSE 0 END as discount_pct
    FROM products p
    JOIN product_offers po ON po.product_id = p.id AND po.in_stock = true
    WHERE p.is_sneaker = true AND p.image_url IS NOT NULL AND p.image_url != ''
      ${brandFilter}
      ${genderFilter}
      ${modelFilter}
      ${sizeFilter}
      ${priceFilter}
    GROUP BY p.id
    HAVING MIN(po.price) > 0
    ORDER BY ${orderClause}
    LIMIT $1 OFFSET $2
  `, params);

  if (productResult.rows.length === 0) return [];

  const productIds = productResult.rows.map((r: any) => r.id);

  const offersResult = await query<any>(`
    SELECT po.product_id, po.price, po.original_price, po.product_url, po.in_stock,
           f.name as feed_name, f.merchant_name, po.sizes
    FROM product_offers po
    JOIN feeds f ON f.id = po.feed_id
    WHERE po.product_id = ANY($1) AND po.in_stock = true
    ORDER BY po.price ASC
  `, [productIds]);

  const offersByProduct = new Map<number, DBOffer[]>();
  for (const o of offersResult.rows) {
    const list = offersByProduct.get(o.product_id) || [];
    list.push(o);
    offersByProduct.set(o.product_id, list);
  }

  return productResult.rows.map((p: any) => mapToSneaker(p, offersByProduct.get(p.id) || []));
}

export async function getTrendingFromDB(limit = 8): Promise<Sneaker[]> {
  return getProductsFromDB({ limit, orderBy: 'popular' });
}

export async function getPriceDropsFromDB(limit = 6): Promise<Sneaker[]> {
  return getProductsFromDB({ limit, orderBy: 'discount' });
}

export async function getProductBySlug(slug: string): Promise<Sneaker | null> {
  const result = await query<any>(`
    SELECT p.id, p.name, p.slug, p.brand, p.colorway, p.gender, p.image_url, p.original_price,
           MIN(po.price) as lowest_price, MAX(po.original_price) as original_price_offer, COUNT(po.id) as offer_count
    FROM products p
    LEFT JOIN product_offers po ON po.product_id = p.id AND po.in_stock = true
    WHERE p.slug = $1
    GROUP BY p.id
  `, [slug]);

  if (result.rows.length === 0) return null;

  const p = result.rows[0];
  const offersResult = await query<any>(`
    SELECT po.product_id, po.price, po.original_price, po.product_url, po.in_stock,
           f.name as feed_name, f.merchant_name, po.sizes
    FROM product_offers po
    JOIN feeds f ON f.id = po.feed_id
    WHERE po.product_id = $1
    ORDER BY po.price ASC
  `, [p.id]);

  const sneaker = mapToSneaker(p, offersResult.rows);

  // Fetch price history (lowest price per day across all offers)
  const historyResult = await query<any>(`
    SELECT recorded_at::date as date, MIN(price) as price
    FROM price_history
    WHERE product_id = $1
    GROUP BY recorded_at::date
    ORDER BY date ASC
  `, [p.id]);

  sneaker.priceHistory = historyResult.rows.map((r: any) => ({
    date: r.date instanceof Date ? r.date.toISOString().split('T')[0] : String(r.date),
    price: Number(r.price),
  }));

  return sneaker;
}

export async function getProductCount(): Promise<number> {
  const result = await query<any>(`
    SELECT COUNT(DISTINCT p.id) as count
    FROM products p
    JOIN product_offers po ON po.product_id = p.id AND po.in_stock = true
    WHERE p.is_sneaker = true AND p.image_url IS NOT NULL AND p.image_url != ''
  `);
  return Number(result.rows[0]?.count || 0);
}

export async function getShopCount(): Promise<number> {
  const result = await query<any>(`
    SELECT COUNT(DISTINCT COALESCE(f.merchant_name, f.name)) as count
    FROM feeds f
    JOIN product_offers po ON po.feed_id = f.id AND po.in_stock = true
  `);
  return Number(result.rows[0]?.count || 0);
}

export async function getBrandsFromDB(): Promise<{ name: string; slug: string; count: number; logo_url: string | null }[]> {
  const result = await query<any>(`
    SELECT p.brand as name,
           LOWER(REGEXP_REPLACE(REGEXP_REPLACE(p.brand, '[^a-zA-Z0-9]+', '-', 'g'), '(^-|-$)', '', 'g')) as slug,
           COUNT(DISTINCT p.id) as count,
           b.logo_url
    FROM products p
    JOIN product_offers po ON po.product_id = p.id AND po.in_stock = true
    LEFT JOIN brands b ON LOWER(b.name) = LOWER(p.brand)
    WHERE p.is_sneaker = true AND p.image_url IS NOT NULL AND p.image_url != '' AND p.brand IS NOT NULL AND p.brand != ''
    GROUP BY p.brand, b.logo_url
    ORDER BY count DESC
  `);
  return result.rows.map((r: any) => ({ name: r.name, slug: r.slug, count: Number(r.count), logo_url: r.logo_url || null }));
}

export async function getBrandBySlugFromDB(slug: string): Promise<{ name: string; slug: string; logo_url: string | null } | null> {
  const result = await query<any>(`
    SELECT b.name, b.slug, b.logo_url
    FROM brands b
    WHERE b.slug = $1
    LIMIT 1
  `, [slug]);
  if (result.rows.length > 0) return result.rows[0];

  // Fallback: find brand name from products where slug matches
  const fallback = await query<any>(`
    SELECT DISTINCT p.brand as name
    FROM products p
    WHERE LOWER(REGEXP_REPLACE(REGEXP_REPLACE(p.brand, '[^a-zA-Z0-9]+', '-', 'g'), '(^-|-$)', '', 'g')) = $1
    LIMIT 1
  `, [slug]);
  if (fallback.rows.length > 0) return { name: fallback.rows[0].name, slug, logo_url: null };
  return null;
}

const GENERIC_MODELS = [
  'Sneakers laag', 'Sneakers hoog', 'Hardloopschoenen voor op de weg',
  'Trainingsschoenen', 'Sportieve veterschoenen', 'Lage schoenen',
  'Skateschoenen', 'Basketbalschoenen', 'Tennisschoenen', 'Wandelschoenen',
  'Hardloopschoenen', 'Trainingsschoen', 'Laarzen', 'Korte laarzen',
  'Enkellaarsjes', 'Regenlaarzen'
];

export async function getModelsByBrand(brandSlug: string): Promise<{ name: string; slug: string; count: number }[]> {
  const result = await query<any>(`
    SELECT p.model as name,
           LOWER(REGEXP_REPLACE(REGEXP_REPLACE(p.model, '[^a-zA-Z0-9]+', '-', 'g'), '(^-|-$)', '', 'g')) as slug,
           COUNT(DISTINCT p.id) as count
    FROM products p
    JOIN product_offers po ON po.product_id = p.id AND po.in_stock = true
    WHERE p.is_sneaker = true
      AND p.model IS NOT NULL AND p.model != ''
      AND p.image_url IS NOT NULL AND p.image_url != ''
      AND (LOWER(p.brand) = LOWER($1) OR LOWER(REGEXP_REPLACE(REGEXP_REPLACE(p.brand, '[^a-zA-Z0-9]+', '-', 'g'), '(^-|-$)', '', 'g')) = LOWER($1))
    GROUP BY p.model
    HAVING COUNT(DISTINCT p.id) >= 5
    ORDER BY count DESC
  `, [brandSlug]);

  // Filter out generic model names
  const genericLower = GENERIC_MODELS.map(m => m.toLowerCase());
  return result.rows
    .filter((r: any) => !genericLower.includes(r.name.toLowerCase()))
    .map((r: any) => ({ name: r.name, slug: r.slug, count: Number(r.count) }));
}

export async function getPopularModels(limit = 20): Promise<{ name: string; slug: string; brandSlug: string; brandName: string; count: number }[]> {
  const result = await query<any>(`
    SELECT p.model as name,
           LOWER(REGEXP_REPLACE(REGEXP_REPLACE(p.model, '[^a-zA-Z0-9]+', '-', 'g'), '(^-|-$)', '', 'g')) as slug,
           p.brand as brand_name,
           LOWER(REGEXP_REPLACE(REGEXP_REPLACE(p.brand, '[^a-zA-Z0-9]+', '-', 'g'), '(^-|-$)', '', 'g')) as brand_slug,
           COUNT(DISTINCT p.id) as count
    FROM products p
    JOIN product_offers po ON po.product_id = p.id AND po.in_stock = true
    WHERE p.is_sneaker = true
      AND p.model IS NOT NULL AND p.model != ''
      AND p.image_url IS NOT NULL AND p.image_url != ''
      AND LOWER(p.model) NOT IN ('sneakers laag', 'sneakers hoog', 'hardloopschoenen voor op de weg',
        'trainingsschoenen', 'sportieve veterschoenen', 'lage schoenen', 'skateschoenen',
        'basketbalschoenen', 'tennisschoenen', 'wandelschoenen', 'hardloopschoenen',
        'trainingsschoen', 'laarzen', 'korte laarzen', 'enkellaarsjes', 'regenlaarzen')
    GROUP BY p.model, p.brand
    HAVING COUNT(DISTINCT p.id) >= 5
    ORDER BY count DESC
    LIMIT $1
  `, [limit]);

  return result.rows.map((r: any) => ({
    name: r.name,
    slug: r.slug,
    brandSlug: r.brand_slug,
    brandName: r.brand_name,
    count: Number(r.count),
  }));
}
