const { lit, raw, transaction } = require('objection');
const _ = require('lodash');
const moment = require('moment');
const Product = require('../../models/product');
const ProductSchedule = require('../../models/productSchedule');
const ProductAllotmentUsage = require('../../models/productAllotmentUsage');
const Allotment = require('../../models/allotment');
const ProductPricePeriod = require('../../models/productPricePeriod');
const ProductUnavailability = require('../../models/productUnavailability');
const {
  OK,
  CREATED,
  NO_CONTENT,
} = require('../../common/constants');
const {
  addedSuccessfully,
  deletedSuccessfully,
  errorInDeleting,
  foundSuccessfully,
  updatedSuccessfully,
} = require('../../common/messages');
const {
  sendJsonResponse,
  sendNoContentFound,
  sendNotFoundError,
  generateDaysFromRange,
  isDayOfYearInRange,
} = require('../../common/helpers');

const productWord = 'product';
const productOptionsWord = 'productOptions';
const productScheduleWord = 'productSchedule';
const productsWord = 'products';
module.exports.readProducts = async (req, res) => {
  const currentDate = moment().format('YYYY-MM-DD');
  const dateRange = `[${currentDate},)`;
  const allotmentUsageInfo = requestedDateRange => ProductAllotmentUsage
    .query()
    .select(
      'allotment_usage_per_day.product_id',
      raw(`json_agg(
      json_build_object(
        'consumption_date',consumption_date,
        'quantity',quantity
      )
    ) as allotment_usage_info`),
    )
    .from(raw(`(select
    product_id,consumption_date,sum(coalesce(quantity, 0)) as quantity
    from product_allotment_usage
    where ?::daterange @> consumption_date
    group by product_id , consumption_date
    ) as allotment_usage_per_day`, [requestedDateRange]))
    .groupBy('allotment_usage_per_day.product_id');
  const isNotInBlackout = requestedDateRange => ProductUnavailability
    .query()
    .select(
      'product_unavailability.product_id',
      raw(`json_agg(
      json_build_object(
        'unavailability_period',product_unavailability.unavailability_period * ?
      )
    ) as unavailability_info`, [requestedDateRange]),
    )
    .where(raw('unavailability_period && ? = true', [requestedDateRange]))
    .groupBy('product_unavailability.product_id');

  const allotmentInfo = requestedDateRange => Allotment
    .query()
    .select(
      'allotment.product_id',
      raw(`json_agg(
        json_build_object(
          'allotment_period',allotment.allotment_period * ?,
          'initial_stock',allotment.initial_stock,
          'reassort',allotment.reassort
        )
      ) as allotment_info`, [requestedDateRange]),
    )
    .where(raw('allotment_period && ? = true', [requestedDateRange]))
    .whereNotNull('allotment.initial_stock')
    .groupBy('allotment.product_id');

  const priceInfo = requestedDateRange => ProductPricePeriod
    .query()
    .with(
      'sub_price_info', (qb) => {
        qb
          .select(
            'product_price_period.id', 'product_price_period.price_period', 'product_price_period.product_id',
            raw(`json_build_object(
              'sub_period', product_price_sub_period.price_sub_period) as sub_periods`),
            raw(`(SELECT
                CASE
                WHEN product_price_period.price_mode_id = 1 THEN
                (select price from price_per_occupation where product_price_period_id = product_price_period.id and price notnull limit 1)
                WHEN product_price_period.price_mode_id = 2 THEN
                (select fit_price from fixed_price where product_price_period_id = product_price_period.id and fit_price notnull limit 1)
                WHEN product_price_period.price_mode_id = 3 THEN
                (select fit_price from price_per_pax where product_price_period_id = product_price_period.id and fit_price notnull limit 1)
                WHEN product_price_period.price_mode_id = 4 THEN
                (select fit_price from price_per_age where product_price_period_id = product_price_period.id and fit_price notnull limit 1)
                END) as price`),
          )
          .from('product_price_period')
          .innerJoin('product_price_sub_period', 'product_price_sub_period.product_price_period_id', 'product_price_period.id')
          .where(raw('product_price_period.price_period && ? = true', [requestedDateRange]))
          // .andWhere('product_price_period.product_id', 'sub_price_info.product_id')
          .groupBy('product_price_period.id', 'product_price_sub_period.price_sub_period');
      },
    )
    .select(
      'product_id', raw(`json_agg(
 sub_periods
) FILTER (WHERE price IS NOT NULL )as price_info`),
    )
    .from('sub_price_info')
    // .where('sub_price_info.product_id', 'product_price_period.product_id')
    .groupBy('sub_price_info.product_id');

  let products = await Product
    .query()
    .skipUndefined()
    .where('establishment_id', req.query.establishment_id)
    .andWhere('product_type_id', req.query.product_type_id)
    .modify((qb) => {
      if (req.query.is_balance === true) {
        qb.select('product.*', 'allotment_info',
          'price_info',
          'allotment_usage_info',
          'unavailability_info')
          .from('product')
          .innerJoin(priceInfo(dateRange).as('product_price_info'),
            'product_price_info.product_id', 'product.id')
          .leftJoin(allotmentInfo(dateRange).as('product_allotment_info'),
            'product_allotment_info.product_id', 'product.id')
          .leftJoin(allotmentUsageInfo(dateRange).as('product_allotment_usage_info'),
            'product_allotment_usage_info.product_id', 'product.id')
          .leftJoin(isNotInBlackout(dateRange).as('product_unavailability_info'),
            'product_unavailability_info.product_id', 'product.id')
          .innerJoin('establishment', 'establishment.id', 'product.establishment_id')
          .innerJoin('provider', 'provider.id', 'establishment.provider_id')
          .where('product.status', true)
          .andWhere('establishment.status', true)
          .andWhere('provider.status', true)
          .whereNotNull('product_price_info')
          .whereNotNull('product_allotment_info');
      }
    })
    .limit(req.query.limit);
  if (products.length === 0) {
    return sendNoContentFound(req, res, productsWord);
  }
  if (req.query.is_balance === true) {
    products = products.map((product) => {
      const balance = _.flatten(product.allotment_info.map((allotement) => {
        const days = generateDaysFromRange(allotement.allotment_period);

        const balanceArray = days && days.map((day) => {
          const quantity = product.allotment_usage_info && product.allotment_usage_info
            .filter(usage => usage.consumption_date === day).length > 0
            ? product.allotment_usage_info.filter(usage => usage.consumption_date
             === day)[0].quantity : 0;
          const nBalance = allotement.initial_stock + allotement.reassort - quantity;
          if (nBalance <= 0) {
            const blackOutDays = product.unavailability_info
            && _.flatten(product.unavailability_info.map(unavailability =>
              isDayOfYearInRange(unavailability.unavailability_period, moment(day))));
            if (blackOutDays && blackOutDays.some(black => black === true)) return null;
            const subPeriods = product.price_info.map(sub =>
              isDayOfYearInRange(sub.sub_period, moment(day)));
            if (subPeriods.some(period => period === true)) {
              return { day, balance: nBalance };
            }
            return null;
          }
          return null;
        });

        return balanceArray;
      })).filter(Boolean);
      const nProduct = product;
      delete nProduct.allotment_usage_info;
      delete nProduct.unavailability_info;
      delete nProduct.price_info;
      nProduct.balance = balance;
      if (balance.length > 0) return nProduct;
      return null;
    });
  }
  return sendJsonResponse(req, res, OK, products, foundSuccessfully(productsWord));
};

module.exports.readProductSchedules = async (req, res) => {
  const requestedDateRange = `[${req.query.start_date},${req.query.end_date})`;
  const products = await ProductSchedule
    .query()
    .select()
    .distinct('activity_date', 'start_hour', 'duration_in_minute')
    .joinRaw(`INNER JOIN lateral (
      select activity_date::date
      from generate_series(lower(period * ?::daterange),
      upper(period * ?::daterange) - interval '1day', '1 day') as activity_date
      where EXTRACT(DOW FROM activity_date) = ANY(checked_days::INT[])
    ) as days  on true`, [requestedDateRange, requestedDateRange])
    .skipUndefined()
    .where('product_id', req.params.products_id)
    .orderBy('activity_date', 'asc');
  if (products.length === 0) {
    return sendNoContentFound(req, res, productsWord);
  }
  return sendJsonResponse(req, res, OK, products, foundSuccessfully(productsWord));
};


module.exports.readProduct = async (req, res) => {
  const product = await Product
    .query()
    .with(
      'schedules', (qb) => {
        qb
          .select(
            'product.*',
            raw(`COALESCE (
              json_agg(
              json_build_object(
              'id',product_schedule.id,
              'start_date',lower(product_schedule.period)::text,
              'end_date',(upper(product_schedule.period) - integer '1')::text,
              'checked_days',product_schedule.checked_days,
              'start_hour',to_char(product_schedule.start_hour, 'HH24:MI'),
              'hours', (product_schedule.duration_in_minute / 60),
              'minutes', (product_schedule.duration_in_minute % 60))
              ) FILTER (WHERE product_schedule.id IS NOT NULL), '[]')
              as product_schedule`),
          )
          .from('product')
          .leftJoin('product_schedule', 'product.id', 'product_schedule.product_id')
          .where('product.id', req.params.products_id)
          .groupBy('product.id');
      },
    )
    .with(
      'options', (qb) => {
        qb
          .select(
            'product.id',
            raw(`COALESCE (
              json_agg(
              json_build_object(
              'establishment_option_id', product_option.establishment_option_id,
              'is_required', product_option.is_required)
              ) FILTER (WHERE product_option.id IS NOT NULL), '[]')
              as product_option`),
          )
          .from('product')
          .leftJoin('product_option', 'product.id', 'product_option.product_id')
          .where('product.id', req.params.products_id)
          .groupBy('product.id');
      },
    )
    .select()
    .from(raw('schedules JOIN options USING (id)'));


  if (product.length === 0) {
    return sendNoContentFound(req, res, productWord);
  }
  return sendJsonResponse(req, res, OK, product[0], foundSuccessfully(productWord));
};

module.exports.createProduct = async (req, res) => {
  const product = await Product
    .query()
    .insert({
      name: req.body.name,
      product_type_id: req.body.product_type_id,
      establishment_id: req.body.establishment_id,
      tax_profile_id: req.body.tax_profile_id,
    });
  return sendJsonResponse(req, res, CREATED, { id: product.id }, addedSuccessfully(productWord));
};

module.exports.updateProduct = async (req, res) => {
  const updatedValues = Object.assign({}, req.body, { id: parseInt(req.params.products_id, 10) });
  const productOptionsTodelete = [];
  if (updatedValues.product_option && updatedValues.product_option.length > 0) {
    const productOptionsToUpsert = [];
    updatedValues.product_option.forEach((opt) => {
      if (Object.keys(opt).length === 1 && Object.keys(opt)[0] === 'establishment_option_id') {
        productOptionsTodelete.push(opt.establishment_option_id);
      } else {
        productOptionsToUpsert.push({
          id: opt.establishment_option_id,
          is_required: opt.is_required,
        });
      }
    });
    updatedValues.product_option = productOptionsToUpsert;
  }
  const productScheduleTodelete = [];
  if (updatedValues.product_schedule && updatedValues.product_schedule.length > 0) {
    const productScheduleToUpsert = [];
    updatedValues.product_schedule.forEach((ps) => {
      if (Object.keys(ps).length === 1 && Object.keys(ps)[0] === 'id') {
        productScheduleTodelete.push(ps.id);
      } else {
        const productSchedule = Object.assign({}, ps);
        if (ps.checked_days) {
          productSchedule.checked_days = lit(ps.checked_days).asArray().castTo('int[]');
        }
        if (ps.start_date && ps.end_date) {
          productSchedule.period = `[${ps.start_date}, ${ps.end_date}]`;
          delete productSchedule.start_date;
          delete productSchedule.end_date;
        }

        if (ps.hours !== undefined && ps.minutes !== undefined) {
          const hourPerMinutes = 60;
          productSchedule.duration_in_minute = ps.hours * hourPerMinutes + ps.minutes;
          delete productSchedule.hours;
          delete productSchedule.minutes;
        }
        productScheduleToUpsert.push(productSchedule);
      }
    });
    updatedValues.product_schedule = productScheduleToUpsert;
  }

  return transaction(Product.knex(), async (trx) => {
    const productUpdate = await Product
      .query(trx)
      .upsertGraph(
        updatedValues,
        {
          relate: true,
          unrelate: false,
          noDelete: true,
        },
      );
    if (productOptionsTodelete.length > 0) {
      const product = await Product
        .query()
        .findById(req.params.products_id);

      const productOptionDelete = await product
        .$relatedQuery('product_option', trx)
        .unrelate()
        .whereIn('establishment_option.id', productOptionsTodelete);

      if (productOptionDelete === 0) {
        throw new Error(errorInDeleting(productOptionsWord));
      }
      return productOptionDelete;
    }
    if (productScheduleTodelete.length > 0) {
      const productScheduleDelete = await ProductSchedule
        .query()
        .delete()
        .whereIn('id', productScheduleTodelete);
      if (productScheduleDelete === 0) {
        throw new Error(errorInDeleting(productScheduleWord));
      }
      return productScheduleDelete;
    }
    return productUpdate;
  })
    .then(() => {
      sendJsonResponse(
        req, res, CREATED,
        updatedSuccessfully(productWord), updatedSuccessfully(productWord),
      );
    });
};
module.exports.deleteProduct = async (req, res) => {
  const product = await Product
    .query()
    .delete()
    .where('id', req.params.products_id);

  if (product === 0) {
    return sendNotFoundError(req, res, productWord);
  }
  return sendJsonResponse(
    req, res, NO_CONTENT, deletedSuccessfully(productWord),
    deletedSuccessfully(productWord),
  );
};