import pool from '../db-config';

export const getAllPrice = async () => {
  const res = await pool.query(`
    SELECT * FROM PRICE;
  `);
  return res.rows;
};

export const getPriceByProduct = async (id) => {
  const farmFromProduct = await pool.query(`
    SELECT DISTINCT PRICE.farm_id, name FROM PRICE
    JOIN FARM
    ON FARM.id = PRICE.farm_id
    WHERE product_id = ${id}
    ORDER BY farm_id ASC;
  `);
  const dateFromProduct = await pool.query(`
    SELECT DISTINCT PRICE.date FROM PRICE
    WHERE product_id = ${id}
    ORDER BY PRICE.date ASC
  `);
  const priceFromProduct = await pool.query(`
    SELECT DISTINCT T1.farm_id, T2.date, T3.price FROM PRICE AS T1
    CROSS JOIN PRICE AS T2
    LEFT JOIN PRICE AS T3
    ON T3.farm_id = T1.farm_id AND T2.date = T3.date
    WHERE T1.product_id = ${id} AND T2.product_id = ${id}
    ORDER BY T1.farm_id ASC, T2.date ASC;
  `);
  const res = {
    duration: dateFromProduct.rows.map(d => (d.date)),
    farms: farmFromProduct.rows.map(farm => ({
      farmName: farm.name,
      price: priceFromProduct.rows.filter(p => (p.farm_id === farm.farm_id)).map(p => (p.price))
    }))
  };
  return res;
};

const getAvgByProductOnTime = async (id, type) => {
  const farmIdFromProduct = await pool.query(`
    SELECT DISTINCT farm_id, name
    FROM price
    JOIN pricestamp
      ON price.price_id = pricestamp.id
    JOIN farmproduct
      ON farmproduct.id = farmproductid
    JOIN farm
      ON farm_id = farm.id
    WHERE product_id = ${id}
    ORDER BY farm_id ASC
  `);
  const farmIdToName = farmIdFromProduct.rows.reduce((sum, val) => {
    sum[val.farm_id] = val.name.replace(/\s+$/, '');
    return sum;
  }, {});
  const timeFromProduct = await pool.query(`
    SELECT DISTINCT date_part('year' ,pricestamp.date) AS year, date_part('${type}', pricestamp.date) AS ${type}
    FROM price
    JOIN pricestamp
      ON price.price_id = pricestamp.id
    JOIN farmproduct
      ON farmproduct.id = farmproductid
    WHERE product_id = ${id}
    ORDER BY year ASC, ${type} ASC
  `);
  const avgFromProduct = await pool.query(`
    SELECT farm_id, AVG(price) AS avg , date_part('year' ,pricestamp.date) AS year, date_part('${type}', pricestamp.date) AS ${type}
    FROM price
    JOIN pricestamp
      ON price.price_id = pricestamp.id
    JOIN farmproduct
      ON farmproduct.id = farmproductid
    WHERE product_id = ${id}
    GROUP BY farm_id,product_id, year, ${type}
    ORDER BY farm_id ASC, year ASC, ${type} ASC
  `);
  const formattedData = timeFromProduct.rows.reduce((sum, row) => {
    const data = {
      time: `${row.year} ${row[type]}`,
      type
    };
    const price = avgFromProduct.rows.filter(val => (val.year === row.year && val[type] === row[type]));
    price.forEach((val) => {
      data[farmIdToName[val.farm_id]] = val.avg;
    });
    sum.push(data);
    return sum;
  }, []);
  return formattedData;
};

export const getAvgByProduct = async (id) => {
  const byWeek = await getAvgByProductOnTime(id, 'week');
  const byMonth = await getAvgByProductOnTime(id, 'month');
  return [...byWeek, ...byMonth];
};

@@ -1202,24 +1202,546 @@
  type) =%3E %7B%0A
+  const typeFormatConvert = (valuetype, value) =%3E %7B%0A    if (valuetype === 'week') %7B%0A      return %60week $%7Bvalue%7D,%60;%0A    %7D%0A    switch (value) %7B%0A      case 1: return 'JAN';%0A      case 2: return 'FEB';%0A      case 3: return 'MAR';%0A      case 4: return 'APR';%0A      case 5: return 'MAY';%0A      case 6: return 'JUN';%0A      case 7: return 'JUL';%0A      case 8: return 'AUG';%0A      case 9: return 'SEP';%0A      case 10: return 'OCT';%0A      case 11: return 'NOV';%0A      case 12: return 'DEC';%0A      default: return 'YEAR';%0A    %7D%0A  %7D;%0A
   const farm
@@ -3107,29 +3107,54 @@
  %60$%7B
-row.year
+typeFormatConvert(type, row%5Btype%5D)
 %7D $%7Brow
-%5Btype%5D
+.year
 %7D%60,%0A

update price