import { Injectable } from '@nestjs/common';
import { CreatePersonDto } from './dto/create-person.dto';
import { UpdatePersonDto } from './dto/update-person.dto';
import { InjectRepository } from '@nestjs/typeorm';
import { Person } from './entities/person.entity';
import { Repository } from 'typeorm';

@Injectable()
export class PersonService {
  constructor(@InjectRepository(Person) private personRepo: Repository<Person>) {}

  create(createPersonDto: CreatePersonDto) {
    return 'This action adds a new person';
  }

  findAll() {
    return this.personRepo.query(`
      SELECT p.*, COUNT(distinct pv.id) verseCount, GROUP_CONCAT(distinct pc.cat) cats
      FROM person p
      LEFT JOIN verse_person pv ON pv.person = p.id
      LEFT JOIN person_category pc ON pc.personId = p.id
      WHERE
        p.status = 1
      GROUP BY p.id
    `);
  }

  async findOne(id: number, loadVerses: boolean = false): Promise<Person | null> {
    let relations = loadVerses ? ['verses'] : undefined;
    return await this.personRepo.findOne({where: {id}, relations});
  }

  async update(id: number, updatePersonDto: UpdatePersonDto) {
    let cats = (updatePersonDto as any).cats;
    delete (updatePersonDto as any).cats;
    if(cats instanceof Array) {
      await this.personRepo.query(`delete from person_category where personId=${id};`)
      if(cats.length) {
        let values = cats.map(cat => `(${id}, ${+cat})`).join(',');
        await this.personRepo.query(`insert into person_category (personId, cat) values ${values};`)
      }
    }
    return this.personRepo.update({id}, updatePersonDto);
  }

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