import { BadRequestException, Injectable, NotFoundException, ServiceUnavailableException } from '@nestjs/common';
import { DatabaseService } from '../database/database.service';

@Injectable()
export class ProjectsService {
  constructor(private readonly db: DatabaseService) {}

  private requireDb() {
    if (!this.db.isConfigured()) throw new ServiceUnavailableException('DATABASE_NOT_CONFIGURED');
  }

  async listPublic() {
    if (!this.db.isConfigured()) return { configured: false, items: [] };
    const result = await this.db.query(
      `SELECT id, name, slug, category, status, description, approximate_location, verification_status, verified_at, updated_at
       FROM projects
       WHERE status IN ('PUBLISHED','ACTIVE','COMPLETED')
       ORDER BY updated_at DESC`
    );
    return { configured: true, items: result.rows };
  }

  async getPublicBySlug(slug: string) {
    if (!this.db.isConfigured()) return { configured: false, project: null, milestones: [], metrics: [] };
    const project = await this.db.query(
      `SELECT id, name, slug, category, status, description, approximate_location, verification_status, verified_at, updated_at
       FROM projects WHERE slug=$1 AND status IN ('PUBLISHED','ACTIVE','COMPLETED') LIMIT 1`,
      [slug]
    );
    if (!project.rows[0]) throw new NotFoundException('PROJECT_NOT_FOUND');
    const projectId = project.rows[0].id;
    const [milestones, metrics] = await Promise.all([
      this.db.query(
        `SELECT title, description, status, progress_percent, planned_start, planned_end, actual_start, actual_end
         FROM project_milestones WHERE project_id=$1 ORDER BY COALESCE(planned_start, created_at::date), created_at`,
        [projectId]
      ),
      this.db.query(
        `SELECT metric_key, value, unit, measured_on, source, updated_at
         FROM project_metrics WHERE project_id=$1 ORDER BY measured_on DESC LIMIT 50`,
        [projectId]
      )
    ]);
    return { configured: true, project: project.rows[0], milestones: milestones.rows, metrics: metrics.rows };
  }

  async createProject(input: Record<string, unknown>) {
    this.requireDb();
    const name = String(input.name || '').trim();
    const slug = String(input.slug || '').trim().toLowerCase();
    const category = String(input.category || '').trim();
    if (!name || !category || !/^[a-z0-9-]{3,80}$/.test(slug)) throw new BadRequestException('NAME_CATEGORY_VALID_SLUG_REQUIRED');
    const result = await this.db.query(
      `INSERT INTO projects(name,slug,category,status,description,approximate_location,is_demo,created_by)
       VALUES($1,$2,$3,'DRAFT',$4,$5,$6,$7)
       RETURNING id,name,slug,category,status,created_at`,
      [name, slug, category, input.description || null, input.approximateLocation || null, Boolean(input.isDemo), input.createdBy || null]
    );
    return { ok: true, project: result.rows[0] };
  }

  async updateProject(projectId: string, input: Record<string, unknown>) {
    this.requireDb();
    const allowedStatuses = ['DRAFT','UNDER_REVIEW','PENDING_VERIFICATION','VERIFIED','PUBLISHED','ACTIVE','PAUSED','COMPLETED','ARCHIVED'];
    const status = input.status ? String(input.status) : null;
    if (status && !allowedStatuses.includes(status)) throw new BadRequestException('INVALID_PROJECT_STATUS');
    const result = await this.db.query(
      `UPDATE projects SET
         name=COALESCE($2,name), category=COALESCE($3,category), description=COALESCE($4,description),
         approximate_location=COALESCE($5,approximate_location), status=COALESCE($6::project_status,status), updated_at=now()
       WHERE id=$1 RETURNING id,name,slug,category,status,updated_at`,
      [projectId, input.name || null, input.category || null, input.description || null, input.approximateLocation || null, status]
    );
    if (!result.rows[0]) throw new NotFoundException('PROJECT_NOT_FOUND');
    return { ok: true, project: result.rows[0] };
  }

  async createMilestone(projectId: string, input: Record<string, unknown>) {
    this.requireDb();
    const title = String(input.title || '').trim();
    if (!title) throw new BadRequestException('MILESTONE_TITLE_REQUIRED');
    const progress = Number(input.progressPercent || 0);
    if (!Number.isInteger(progress) || progress < 0 || progress > 100) throw new BadRequestException('INVALID_PROGRESS_PERCENT');
    const result = await this.db.query(
      `INSERT INTO project_milestones(project_id,title,description,status,progress_percent,planned_start,planned_end,responsible_user_id,evidence_document_id)
       VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9)
       RETURNING id,title,status,progress_percent,created_at`,
      [projectId, title, input.description || null, input.status || 'PLANNED', progress, input.plannedStart || null, input.plannedEnd || null, input.responsibleUserId || null, input.evidenceDocumentId || null]
    );
    return { ok: true, milestone: result.rows[0] };
  }
}