import { Injectable } from '@nestjs/common';
import { DataSource } from 'typeorm';

@Injectable()
export class StatsService {
  constructor(private readonly dataSource: DataSource) {}

  async getStats() {
    const result = await this.dataSource.query(
      `
      SELECT
        (SELECT COUNT(*) FROM job_posts WHERE status = 'open' AND (expires_at IS NULL OR expires_at > CURRENT_TIMESTAMP)) AS "liveJobs",
        (SELECT COUNT(*) FROM companies WHERE is_deleted = 0 AND status = 1) AS "companies",
        (SELECT COUNT(*) FROM users WHERE role = 'seeker') AS "candidates",
        (SELECT COUNT(*) FROM users WHERE status = 'active') AS "users",
        (SELECT COUNT(*) FROM job_posts WHERE DATE(created_at) = CURDATE() AND status = 'open' AND (expires_at IS NULL OR expires_at > CURRENT_TIMESTAMP)) AS "newJobsToday"
      `,
    );

    return result[0]; // Only one row returned
  }
}
