import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import {
  Repository,
  MoreThanOrEqual,
  LessThan,
  DataSource,
  IsNull,
} from 'typeorm';
import { JobPosts } from '../job_posts/entities/job_posts.entity';
import { Companies } from '../companies/entities/companies.entity';

@Injectable()
export class SitemapService {
  constructor(
    @InjectRepository(JobPosts)
    private readonly jobPostsRepository: Repository<JobPosts>,
    @InjectRepository(Companies)
    private readonly companiesRepository: Repository<Companies>,
    private readonly dataSource: DataSource,
  ) {}

  async getSitemapJobs() {
    return this.jobPostsRepository
      .createQueryBuilder('job')
      .select([
        'job.id',
        'job.title',
        'job.updated_at',
        'job.expires_at',
        'job.status',
      ])
      .where('job.status = :status', { status: 'open' })
      .andWhere('(job.expires_at IS NULL OR job.expires_at >= NOW())')
      .orderBy('job.updated_at', 'DESC')
      .getMany();
  }

  async getActiveJobsCount() {
    return this.jobPostsRepository.count({
      where: [
        { status: 'open', expires_at: MoreThanOrEqual(new Date()) },
        { status: 'open', expires_at: IsNull() },
      ],
    });
  }

  async getExpiredJobsCount() {
    return this.jobPostsRepository.count({
      where: [
        { status: 'closed' },
        { status: 'open', expires_at: LessThan(new Date()) },
      ],
    });
  }

  async getPaginatedActiveJobs(page: number, limit: number) {
    const offset = Math.max(0, Number(page) * Number(limit));
    const take = Math.max(1, Number(limit));
    return this.dataSource.query(
      `SELECT id, title, updated_at, expires_at, status 
       FROM job_posts 
       WHERE status = 'open' AND (expires_at IS NULL OR expires_at >= NOW()) 
       ORDER BY id DESC 
       LIMIT ${take} OFFSET ${offset}`,
    );
  }

  async getPaginatedExpiredJobs(page: number, limit: number) {
    const offset = Math.max(0, Number(page) * Number(limit));
    const take = Math.max(1, Number(limit));
    return this.dataSource.query(
      `SELECT id, title, updated_at, expires_at, status 
       FROM job_posts 
       WHERE status = 'closed' OR (status = 'open' AND expires_at < NOW()) 
       ORDER BY id DESC 
       LIMIT ${take} OFFSET ${offset}`,
    );
  }

  /* Wait, I should refine the expired query to be more precise */
  async getSitemapCompanies() {
    return this.companiesRepository.find({
      select: ['id', 'name', 'updated_at'],
      where: { is_approved: 1, status: 1, is_deleted: 0 },
      order: { updated_at: 'DESC' },
    });
  }
}
