import { HttpException, HttpStatus, Inject, Injectable, NotFoundException } from '@nestjs/common';
import { TypeOrmQueryService } from '@nestjs-query/query-typeorm';
import { Project, SubProject } from './project.entity';
import { InjectConnection, InjectRepository } from '@nestjs/typeorm';
import { Connection, Not, Repository } from 'typeorm';
import { REQUEST } from '@nestjs/core';
import { Request } from 'express';
import { TokenPayload } from 'src/types/token-payload.type';
import { ConfigService } from 'nestjs-config';
import { JwtService } from '@nestjs/jwt';
import { CreateProjectDto, UpdateProjectDto, CreateSubProjectDto, UpdateSubProjectDto, CreateProjectHistoryDto, UpdateProjectHistoryDto, CreateSubSubProjectDto, UpdateSubSubProjectDto } from './dto/project.dto';

@Injectable()
export class ProjectService extends TypeOrmQueryService<Project> {
  constructor(
    @InjectRepository(Project)
    private readonly projectRepository: Repository<Project>,
    private readonly jwtService: JwtService,
    private readonly config: ConfigService,
    @Inject(REQUEST) private readonly request: Request,
    @InjectConnection() private readonly connection: Connection
  ) {
    super(projectRepository, { useSoftDelete: true });
  }

  //Project History

  async getHistory() {
    try {
      var selectQuery = 'SELECT project_history.* from project_history';
      var project_history = await this.connection.query(selectQuery);

      if (!project_history) {
        throw new NotFoundException(`Project History not found`);
      }

      return project_history;


    } catch (ex) {
      throw ex;
    }
  }



  async getProjectHistorybyId(id: number) {

    try {
      var selectQuery = 'SELECT project_history.* from project_history where project_history.id =' + id;
      var project = await this.connection.query(selectQuery);

      if (!project) {
        throw new NotFoundException(`Project History #${id} not found`);
      }

      return project;


    } catch (ex) {
      throw ex;
    }

  }

  async addProjectHistory(data: CreateProjectHistoryDto) {
    try {

      var insert_query = "insert into project_history(client_id,project_id,team_id,task_id,project_status,sub_project_id,sub_sub_project_id) Values ('" + data.client_id + "','" + data.project_id + "','" + data.team_id + "','" + data.task_id + "','" + data.project_status + "','" + data.sub_project_id + "','" + data.sub_sub_project_id + "')";
      console.log(insert_query);
      var inserted_query = await this.connection.query(insert_query);
      if (inserted_query) {
        var last_inserted_id = inserted_query.insertId;


      }
      return inserted_query;

    } catch (err) {
      throw err
    }
  }



  async updateProjectHistory(id: number, data: UpdateProjectHistoryDto) {
    try {
      var insert_attachment_query = "UPDATE project_history set client_id='" + data.client_id + "',project_id='" + data.project_id + "', team_id= '" + data.team_id + "', task_id= '" + data.task_id + "',project_status='" + data.project_status + "', sub_project_id= '" + data.sub_project_id + "', sub_sub_project_id= '" + data.sub_sub_project_id + "' where id=" + id + " ";
      console.log(insert_attachment_query)
      var attachement_execute_query = await this.connection.query(insert_attachment_query);
      return "Project History has been updated"


    } catch (ex) {
      throw ex;
    }
  }

  async add(data: CreateProjectDto) {
    try {
      const project: Project = new Project();
      project.project_name = data.project_name;
      project.project_desc = data.project_desc;
      project.client_name = data.client_name;

      var execute = await this.projectRepository.save(project);
      return "Project has been created";
    } catch (err) {
      throw err
    }
  }



  async update(id: number, data: UpdateProjectDto) {
    try {
      const project: Project = new Project();
      project.project_name = data.project_name;
      project.project_desc = data.project_desc;
      project.client_name = data.client_name;

      return await this.projectRepository.update(id, project);

    } catch (ex) {
      throw ex;
    }
  }

  async remove(id: number) {
    try {
      var delete_query = "Update project set status = 0 where id = " + id;
      var delete_query_execute = await this.connection.query(delete_query);
      if (delete_query_execute) {
        return "soft deleted";
      }
    } catch (ex) {
      throw ex;
    }
  }



  async addsub(data: CreateSubProjectDto) {
    try {
 
      var insert_subproject_query = "insert into subproject(sub_project_name,client_name,project_name,sub_project_desc,project_status,sub_sub_project_id) Values ('" + data.sub_project_name + "' ,'"  + data.client_name + "','" + data.project_name + "','" + data.sub_project_desc + "','" + data.project_status + "','" + data.sub_sub_project_id + "')";
      console.log(insert_subproject_query);

      const inserted_query = await this.connection.query(insert_subproject_query, [
        data.sub_project_name,
        data.client_name,
        data.project_name,
        data.sub_project_desc,
        data.project_status,
        data.sub_sub_project_id,
      ]);

      if (inserted_query) {
        var last_inserted_id = inserted_query.insertId;


      }
      return inserted_query;
    } catch (err) {
      throw err
    }
  }

  async updatesubproject(id: number, data: UpdateSubProjectDto) {
    try {
      console.log(data);
      const update_subproject_query = `
          UPDATE subproject
          SET sub_project_name = ?,
              client_name = ?,
              project_name = ?,
              sub_project_desc = ?,
              project_status = ?,
              sub_sub_project_id = ?
          WHERE id = ?
      `;

      const updated_query = await this.connection.query(update_subproject_query, [
        data.sub_project_name,
        data.client_name,
        data.project_name,
        data.sub_project_desc,
        data.project_status,
        data.sub_sub_project_id,
        id,
      ]);

      return updated_query;



    } catch (err) {
      throw err
    }
  }


  removesub(id: number) {
    return `This action removes a #${id} service`;
  }

  async getProjects() {
    try {

      return await this.projectRepository.find();

    } catch (ex) {
      throw ex;
    }
  }
  async getProjectByClient(client_id: number) {
    try {
      var selectQuery = 'SELECT project_history.*, project.project_name, clients.client_name'
      selectQuery += ' FROM project_history'
      selectQuery += ' LEFT JOIN project ON project_history.project_id = project.id'
      selectQuery += ' LEFT JOIN clients ON project_history.client_id = clients.id'
      selectQuery += ' WHERE client_id =' + client_id;
      var project = await this.connection.query(selectQuery);

      if (!project) {
        throw new NotFoundException(`Project #${client_id} not found`);
      }

      return project;
    } catch (ex) {
      throw ex;
    }
  }

  async getProjectbyId(id: number) {
    try {
      var selectQuery = 'SELECT project.* from project where project.id =' + id;
      var project = await this.connection.query(selectQuery);

      if (!project) {
        throw new NotFoundException(`Project #${id} not found`);
      }
      return project;

    } catch (ex) {
      throw ex;
    }

  }

  async getSubProject(project_name: string, client_name: string) {
    try {
      var selectQuery = 'SELECT * FROM pmt_tool.subproject WHERE client_name =' +client_name+ `AND project_name =` + project_name;

      var subproject = await this.connection.query(selectQuery,[client_name, project_name]);
      if (!subproject) {
        throw new NotFoundException(`Sub-Project not found`);
      }

      return subproject;
    } catch (ex) {
      throw ex;
    }
  }

  async delete_subproject(id: number) {
    try {
      // return await this.usersRepository.delete(id);
      var delete_query = "Update subproject set status = 0 where id = " + id;
      var delete_query_execute = await this.connection.query(delete_query);
      if (delete_query_execute) {
        return "deleted";
      }
    } catch (ex) {
      throw ex;
    }
  }


  //Sub Sub Project

  async getSubSubProject(project_name: string, client_name: string) {
    try {
      var selectQuery = 'SELECT * FROM pmt_tool.subsubproject WHERE client_name = ? AND project_name = ?';

      var subsubproject = await this.connection.query(selectQuery,[client_name, project_name]);

      if (!subsubproject) {
        throw new NotFoundException(`Sub-Sub Project not found`);
      }

      return subsubproject;
    } catch (ex) {
      throw ex;
    }
  }


  async addSubSubproject(data: CreateSubSubProjectDto) {
    try {
      console.log(data);

      var insert_query = "insert into subsubproject(sub_sub_project_name,client_id,project_id,sub_sub_project_desc,project_status) Values ('" + data.sub_sub_project_name + "'," + data.client_id + ",'" + data.project_id + "','" + data.sub_sub_project_desc + "','" + data.project_status + "')";
      const inserted_query = await this.connection.query(insert_query, [
        data.sub_sub_project_name,
        data.client_id,
        data.project_id,
        data.sub_sub_project_desc,
        data.project_status
      ]);

      if (inserted_query) {
        var last_inserted_id = inserted_query.insertId;


      }
      return inserted_query;
    } catch (err) {
      throw err
    }
  }

  async updateSubSubProject(id: number, data: UpdateSubSubProjectDto) {
    try {
      console.log(data);
      const update_query = `
          UPDATE subsubproject
          SET sub_sub_project_name = ?,
              client_id = ?,
              project_id = ?,
              sub_sub_project_desc = ?
              project_status = ?
          WHERE id = ?
      `;

      const updated_query = await this.connection.query(update_query, [
        data.sub_sub_project_name,
        data.client_id,
        data.project_id,
        data.sub_sub_project_desc,
        data.project_status,
        id,
      ]);

      console.log(updated_query)
      return updated_query;



    } catch (err) {
      throw err
    }
  }




}