import { Injectable } from '@nestjs/common';
import { Prisma } from '@prisma/client';
import { PrismaService } from 'src/prisma/prisma.service';

@Injectable()
export class ReportsService {
  constructor(
    private prisma: PrismaService
  ) {}

  async findYears(): Promise<{
    id: number,
    name: number
  }[]> {
    return await this.prisma.$queryRaw`
      SELECT DISTINCT assessment_year AS id, assessment_year AS name
      FROM eval_form
      ORDER BY assessment_year DESC
    `;
  }

  async findGroupNames(userId: number): Promise<{
    user_group_id: number,
    group_name: string,
    bis_id: number
  }[]> {
    return await this.prisma.$queryRaw`
      SELECT distinct g.user_group_id, g.group_name, g.bis_id
      FROM users_has_group u
      INNER JOIN user_group g
      ON g.user_group_id = u.user_group_id
      WHERE organisation_type_id = 0
      AND u.user_id = ${userId}
      ORDER BY bis_id asc
    `;
  }

  async findControlProducts(userId: number): Promise<{
    control_product: number,
    product_group: string,
    trade_name: string,
    active_ingredient: string,
    formulation: string
  }[]> {
    return await this.prisma.$queryRaw`
      SELECT DISTINCT cp.control_product_id,
      CASE
        WHEN cp.product_group_id = 1 THEN 'Herbicides'
        WHEN cp.product_group_id = 2 THEN 'Pesticides'
        WHEN cp.product_group_id = 3 THEN 'Fungicides'
        WHEN cp.product_group_id = 4 THEN 'Nematicides'
      END AS product_group, cp.trade_name, cp.active_ingredient, cp.formulation
      FROM group_eval_form gef
      INNER JOIN users_has_group uhg ON uhg.user_group_id = gef.user_group_id
      INNER JOIN user_group ug ON ug.user_group_id = uhg.user_group_id AND uhg.user_id = ${userId}
      INNER JOIN eval_form ef ON ef.eval_form_id = gef.eval_form_id
      INNER JOIN group_eval_form_appendix app ON app.group_eval_form_id = gef.group_eval_form_id
      INNER JOIN control_product cp ON cp.control_product_id = app.control_product_id
      WHERE gef.status IN (50,55)
      ORDER BY product_group,trade_name
    `;
  }

//   async getTableReport(
//     user_id: number,
//     year: number,
//     user_group_id: number,
//     control_product_id: number,
//   ): Promise<{
//     product_group: number,
//     trade_name: string,
//     active_ingredient: string,
//     formulation: string
//   }[]> {
//     let query = 'SELECT DISTINCT ';
//     if (user_group_id === -1) query += ' ug.group_name, ';
//     query += ` CASE
//                  WHEN cp.product_group_id = 1 THEN 'Herbicides'
//                  WHEN cp.product_group_id = 2 THEN 'Pesticides'
//                  WHEN cp.product_group_id = 3 THEN 'Fungicides'
//                  WHEN cp.product_group_id = 4 THEN 'Nematicides'
//                END AS product_group,
//                cp.trade_name, cp.active_ingredient, cp.formulation
//                FROM group_eval_form gef `;
//     if (user_group_id === -1) {
//       query += ` INNER JOIN users_has_group uhg on uhg.user_group_id = gef.user_group_id
//                  INNER JOIN user_group ug on ug.user_group_id = uhg.user_group_id
//                  AND uhg.user_id = ${user_id}`;
//     }
//     query += ` INNER JOIN eval_form ef ON ef.eval_form_id = gef.eval_form_id
//                INNER JOIN group_eval_form_appendix app ON app.group_eval_form_id = gef.group_eval_form_id
//                INNER JOIN control_product cp ON cp.control_product_id = app.control_product_id
//                WHERE gef.status IN (50,55) `;
//     if (year !== -1)  query += ` AND ef.assessment_year = ${year}`;
//     if (user_group_id !== -1)  query += ` AND gef.user_group_id = ${user_group_id}`;
//     if (control_product_id !== -1) query += ` AND cp.control_product_id = ${control_product_id}`;
//     query += ' order by product_group_id, trade_name';
//     return await this.prisma.$queryRaw`${query}`;
//   }
// }

  async getTableReport(
    user_id: number,
    year: number,
    user_group_id: number,
    control_product_id: number,
  ): Promise<{
    product_group: number,
    trade_name: string,
    active_ingredient: string,
    formulation: string
  }[]> {
    const groupNameClause = user_group_id === -1 ? Prisma.sql`ug.group_name,` : Prisma.empty;
    const userGroupJoinClause = user_group_id === -1 ? Prisma.sql`
      INNER JOIN users_has_group uhg on uhg.user_group_id = gef.user_group_id
      INNER JOIN user_group ug on ug.user_group_id = uhg.user_group_id
      AND uhg.user_id = ${user_id}
    ` : Prisma.empty;
    const yearClause = year !== -1 ? Prisma.sql`AND ef.assessment_year = ${year}` : Prisma.empty;
    const groupNameClause2 = user_group_id !== -1 ? Prisma.sql`AND gef.user_group_id = ${user_group_id}` : Prisma.empty;
    const controlProductClause = control_product_id !== -1 ? Prisma.sql`AND cp.control_product_id = ${control_product_id}` : Prisma.empty;
    return await this.prisma.$queryRaw`
      SELECT DISTINCT ${groupNameClause}
      CASE
        WHEN cp.product_group_id = 1 THEN 'Herbicides'
        WHEN cp.product_group_id = 2 THEN 'Pesticides'
        WHEN cp.product_group_id = 3 THEN 'Fungicides'
        WHEN cp.product_group_id = 4 THEN 'Nematicides'
      END AS product_group,
      cp.trade_name, cp.active_ingredient, cp.formulation
      FROM group_eval_form gef
      ${userGroupJoinClause}
      INNER JOIN eval_form ef ON ef.eval_form_id = gef.eval_form_id
      INNER JOIN group_eval_form_appendix app ON app.group_eval_form_id = gef.group_eval_form_id
      INNER JOIN control_product cp ON cp.control_product_id = app.control_product_id
      WHERE gef.status IN (50,55)
      ${yearClause}
      ${groupNameClause2}
      ${controlProductClause}
      ORDER BY product_group_id, trade_name
    `;
  }
}