'use strict';
const express = require('express');
const { db, categoryBySlug, productBySlug, productGallery, productGalleryMap, liveProducts, countLiveProducts, relatedProducts, categoriesList } = require('../db');

const router = express.Router();

function money(n) {
  return 'R ' + Number(n || 0).toLocaleString('en-ZA', { minimumFractionDigits: 2, maximumFractionDigits: 2 });
}

function statusForProduct(p) {
  if (p.price == null || p.price <= 0) return 'Price on request';
  if (p.stock <= 0) return 'Out of stock';
  return 'In stock';
}

router.get('/', (req, res) => {
  const featured = liveProducts(8, 0, null, true);
  const newArrivals = liveProducts(12, 0, null, false);
  const allProducts = [...featured, ...newArrivals];
  const galleryMap = productGalleryMap(allProducts.map(p => p.id));
  const reviews = db
    .prepare('SELECT * FROM reviews WHERE approved = 1 ORDER BY created_at DESC LIMIT 9')
    .all();
  const testimonials = db
    .prepare('SELECT * FROM testimonials WHERE approved = 1 ORDER BY sort_order, id DESC LIMIT 12')
    .all();
  res.render('index', {
    title: 'TOUS CINO — Everyone\'s Sense of Freeness',
    featured,
    newArrivals,
    galleryMap,
    reviews,
    testimonials,
    money,
  });
});

router.get('/testimonials', (req, res) => {
  const testimonials = db
    .prepare('SELECT * FROM testimonials WHERE approved = 1 ORDER BY sort_order, id DESC')
    .all();
  res.render('testimonials', { title: 'Testimonials | TOUS CINO', testimonials });
});

router.get('/about', (req, res) => {
  res.render('about', { title: 'About Us | TOUS CINO' });
});

router.get('/contact', (req, res) => {
  res.render('contact', { title: 'Contact | TOUS CINO' });
});

router.get('/cart', (req, res) => {
  res.render('cart', { title: 'Your Cart | TOUS CINO', money });
});

router.get('/wishlist', (req, res) => {
  res.render('wishlist', { title: 'Your Wishlist | TOUS CINO', money });
});

router.get('/checkout', (req, res) => {
  res.render('checkout', {
    title: 'Checkout | TOUS CINO',
    money,
    cancelled: req.query.payfast === 'cancelled',
  });
});

router.get('/search', (req, res) => {
  const q = String(req.query.q || '').trim();
  let results = [];
  if (q) {
    const like = `%${q}%`;
    results = db
      .prepare(
        `SELECT p.*, c.name AS category_name, c.slug AS category_slug
         FROM products p LEFT JOIN categories c ON c.id = p.category_id
         WHERE p.status='live' AND (p.name LIKE ? OR p.description LIKE ? OR c.name LIKE ?)
         ORDER BY p.created_at DESC`
      )
      .all(like, like, like);
  }
  res.render('search', { title: `Search | TOUS CINO`, q, results, money });
});

router.get('/product/:slug', (req, res) => {
  const product = productBySlug(req.params.slug);
  if (!product) return res.status(404).render('404', { title: 'Page not found' });
  const gallery = productGallery(product.id);
  const related = relatedProducts(product);
  const priceRequest = product.price == null || product.price <= 0;
  const productReviews = db.prepare('SELECT * FROM reviews WHERE product_id = ? AND approved = 1 ORDER BY created_at DESC').all(product.id);
  const productSizes = String(product.sizes || '').split(/[\s,]+/).map((s) => s.trim()).filter(Boolean);
  const sizeType = String(product.size_type || (productSizes.length ? 'shoes' : 'none'));
  res.render('product', {
    title: `${product.name} | TOUS CINO`,
    product,
    gallery,
    related,
    money,
    priceRequest,
    status: statusForProduct(product),
    productReviews,
    productSizes,
    sizeType,
  });
});

router.get('/order/:order_number', (req, res) => {  const order = db.prepare('SELECT * FROM orders WHERE order_number = ?').get(req.params.order_number);
  if (!order) return res.status(404).render('404', { title: 'Order not found' });
  const items = db.prepare('SELECT * FROM order_items WHERE order_id = ?').all(order.id);
  const paid = order.payment_status === 'paid';
  res.render('order-confirmation', {
    title: `Order ${order.order_number} | TOUS CINO`,
    order,
    items,
    money,
    paid,
    payfastSuccess: req.query.payfast === 'success',
  });
});

router.get('/robots.txt', (req, res) => {
  res.type('text/plain').send('User-agent: *\nAllow: /\nSitemap: ' + (req.app.locals.siteUrl || 'http://localhost:3000') + '/sitemap.xml\n');
});

router.get('/sitemap.xml', (req, res) => {
  const base = req.app.locals.siteUrl || 'http://localhost:3000';
  const cats = db.prepare("SELECT slug FROM categories WHERE EXISTS (SELECT 1 FROM products p WHERE p.category_id = categories.id AND p.status='live')").all();
  const prods = db.prepare("SELECT slug FROM products WHERE status='live'").all();
  let xml = '<?xml version="1.0" encoding="UTF-8"?>\n<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">\n';
  const urls = ['/', '/about', '/contact', '/cart', '/wishlist'];
  cats.forEach((c) => urls.push('/' + c.slug));
  prods.forEach((p) => urls.push('/product/' + p.slug));
  urls.forEach((u) => { xml += `  <url><loc>${base}${u}</loc></url>\n`; });
  xml += '</urlset>';
  res.type('application/xml').send(xml);
});

router.get('/all', (req, res) => {
  const q = String(req.query.q || '').trim();
  const catSlug = String(req.query.cat || '').trim();
  const sort = String(req.query.sort || 'random');
  const inStock = req.query.stock === '1';
  const page = Math.max(1, parseInt(req.query.page, 10) || 1);
  const per = 12;

  const cats = categoriesList();
  const selectedCat = cats.find((c) => c.slug === catSlug) || null;

  let where = "p.status='live'";
  const params = [];
  if (selectedCat) { where += ' AND p.category_id = ?'; params.push(selectedCat.id); }
  if (inStock) {
    where += ' AND p.stock > 0 AND (p.price IS NOT NULL AND p.price > 0)';
  }
  if (q) {
    const like = `%${q}%`;
    where += ' AND (p.name LIKE ? OR p.description LIKE ? OR c.name LIKE ?)';
    params.push(like, like, like);
  }

  let orderClause = 'ORDER BY RANDOM()';
  if (sort === 'newest') orderClause = 'ORDER BY datetime(p.created_at) DESC';
  else if (sort === 'price_low') orderClause = 'ORDER BY p.price ASC';
  else if (sort === 'price_high') orderClause = 'ORDER BY p.price DESC';
  else if (sort === 'name') orderClause = 'ORDER BY p.name ASC';

  const total = db
    .prepare(`SELECT COUNT(*) c FROM products p LEFT JOIN categories c ON c.id = p.category_id WHERE ${where}`)
    .get(...params).c;
  const products = db
    .prepare(
      `SELECT p.*, c.name AS category_name, c.slug AS category_slug
       FROM products p LEFT JOIN categories c ON c.id = p.category_id
       WHERE ${where} ${orderClause} LIMIT ? OFFSET ?`
    )
    .all(...params, per, (page - 1) * per);
  const pages = Math.max(1, Math.ceil(total / per));
  const galleryMap = productGalleryMap(products.map(p => p.id));

  res.render('shop', {
    title: `Shop All | TOUS CINO`,
    q,
    catSlug,
    selectedCat,
    sort,
    inStock,
    cats,
    products,
    galleryMap,
    total,
    page,
    pages,
    money,
  });
});

router.get('/:slug', (req, res) => {
  const cat = categoryBySlug(req.params.slug);
  if (!cat) {
    return res.status(404).render('404', { title: 'Page not found' });
  }
  const page = Math.max(1, parseInt(req.query.page, 10) || 1);
  const per = 12;
  const total = countLiveProducts(cat.id);
  const products = liveProducts(per, (page - 1) * per, cat.id, false);
  const pages = Math.max(1, Math.ceil(total / per));
  const galleryMap = productGalleryMap(products.map(p => p.id));
  res.render('category', {
    title: `${cat.name} | TOUS CINO`,
    cat,
    products,
    galleryMap,
    money,
    page,
    pages,
    total,
  });
});

module.exports = router;
