'use strict'

const { execute, queryOne, query } = require('../database/postgres')

// ── Helpers ───────────────────────────────────────────────────────────────────

function tenantId(req) { return req.tenant?.id || 1 }

const VALID_STATUSES = ['novo', 'em_analise', 'aprovado', 'reprovado', 'contratado']

// ── Público — Vagas ───────────────────────────────────────────────────────────

exports.listJobs = async (req, res) => {
  try {
    const tid = tenantId(req)
    const { category, work_mode, job_type, search, limit = 20, offset = 0 } = req.query

    const conditions = ['j.tenant_id = $1', 'j.active = true']
    const params = [tid]
    let pi = 2

    if (category)  { conditions.push(`j.category = $${pi++}`);  params.push(category) }
    if (work_mode) { conditions.push(`j.work_mode = $${pi++}`); params.push(work_mode) }
    if (job_type)  { conditions.push(`j.job_type = $${pi++}`);  params.push(job_type) }
    if (search)    { conditions.push(`j.title ILIKE $${pi++}`); params.push(`%${search}%`) }
    conditions.push(`(j.expires_at IS NULL OR j.expires_at > NOW())`)

    const where = conditions.join(' AND ')

    const countRow = await queryOne(
      `SELECT COUNT(*) AS total FROM jobs j WHERE ${where}`,
      params
    )

    const jobs = await query(
      `SELECT j.*,
              COUNT(a.id) AS applicants_count
       FROM jobs j
       LEFT JOIN job_applications a ON a.job_id = j.id
       WHERE ${where}
       GROUP BY j.id
       ORDER BY j.published_at DESC
       LIMIT $${pi} OFFSET $${pi + 1}`,
      [...params, +limit, +offset]
    )

    res.json({ jobs: jobs || [], total: parseInt(countRow?.total || '0') })
  } catch (err) {
    console.error('[jobController] listJobs:', err.message)
    res.status(500).json({ error: 'Erro interno' })
  }
}

exports.getJob = async (req, res) => {
  try {
    const tid = tenantId(req)
    const job = await queryOne(
      `SELECT j.*, COUNT(a.id) AS applicants_count
       FROM jobs j
       LEFT JOIN job_applications a ON a.job_id = j.id
       WHERE j.id = $1 AND j.tenant_id = $2 AND j.active = true
       GROUP BY j.id`,
      [req.params.id, tid]
    )
    if (!job) return res.status(404).json({ error: 'Vaga não encontrada' })
    res.json(job)
  } catch (err) {
    console.error('[jobController] getJob:', err.message)
    res.status(500).json({ error: 'Erro interno' })
  }
}

exports.applyToJob = async (req, res) => {
  try {
    const tid = tenantId(req)
    const { id: job_id } = req.params
    const { applicant_cpf, applicant_nome, applicant_phone, applicant_email, cover_letter, resume_id } = req.body

    if (!applicant_cpf || !applicant_nome)
      return res.status(400).json({ error: 'CPF e nome são obrigatórios' })

    const job = await queryOne(
      `SELECT id FROM jobs WHERE id=$1 AND tenant_id=$2 AND active=true`,
      [job_id, tid]
    )
    if (!job) return res.status(404).json({ error: 'Vaga não encontrada' })

    // Verifica duplicata
    const existing = await queryOne(
      `SELECT id FROM job_applications WHERE job_id=$1 AND applicant_cpf=$2`,
      [job_id, applicant_cpf.replace(/\D/g, '')]
    )
    if (existing) return res.status(409).json({ error: 'Você já se candidatou a esta vaga' })

    const result = await execute(
      `INSERT INTO job_applications
         (tenant_id, job_id, resume_id, applicant_cpf, applicant_nome, applicant_phone, applicant_email, cover_letter)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8) RETURNING id`,
      [tid, job_id, resume_id || null,
       applicant_cpf.replace(/\D/g, ''), applicant_nome,
       applicant_phone || null, applicant_email || null, cover_letter || null]
    )
    res.status(201).json({ ok: true, id: result.rows?.[0]?.id })
  } catch (err) {
    console.error('[jobController] applyToJob:', err.message)
    res.status(500).json({ error: 'Erro interno' })
  }
}

// ── Usuário — Currículo ───────────────────────────────────────────────────────

exports.getMyResume = async (req, res) => {
  try {
    const tid = tenantId(req)
    const cpf = req.user?.cpf?.replace(/\D/g, '')
    if (!cpf) return res.status(401).json({ error: 'Não autenticado' })
    const resume = await queryOne(
      `SELECT * FROM resumes WHERE tenant_id=$1 AND user_cpf=$2`,
      [tid, cpf]
    )
    res.json(resume || null)
  } catch (err) {
    console.error('[jobController] getMyResume:', err.message)
    res.status(500).json({ error: 'Erro interno' })
  }
}

exports.saveMyResume = async (req, res) => {
  try {
    const tid = tenantId(req)
    const cpf = req.user?.cpf?.replace(/\D/g, '')
    if (!cpf) return res.status(401).json({ error: 'Não autenticado' })

    const { user_nome, email, phone, linkedin, portfolio, about, experience, education, skills } = req.body
    if (!user_nome) return res.status(400).json({ error: 'Nome é obrigatório' })

    const existing = await queryOne(
      `SELECT id FROM resumes WHERE tenant_id=$1 AND user_cpf=$2`, [tid, cpf]
    )

    if (existing) {
      await execute(
        `UPDATE resumes SET user_nome=$1, email=$2, phone=$3, linkedin=$4, portfolio=$5,
           about=$6, experience=$7, education=$8, skills=$9, updated_at=NOW()
         WHERE tenant_id=$10 AND user_cpf=$11`,
        [user_nome, email||null, phone||null, linkedin||null, portfolio||null,
         about||null, JSON.stringify(experience||[]), JSON.stringify(education||[]),
         JSON.stringify(skills||[]), tid, cpf]
      )
      res.json({ ok: true, id: existing.id })
    } else {
      const result = await execute(
        `INSERT INTO resumes (tenant_id, user_cpf, user_nome, email, phone, linkedin, portfolio, about, experience, education, skills)
         VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11) RETURNING id`,
        [tid, cpf, user_nome, email||null, phone||null, linkedin||null, portfolio||null,
         about||null, JSON.stringify(experience||[]), JSON.stringify(education||[]),
         JSON.stringify(skills||[])]
      )
      res.status(201).json({ ok: true, id: result.rows?.[0]?.id })
    }
  } catch (err) {
    console.error('[jobController] saveMyResume:', err.message)
    res.status(500).json({ error: 'Erro interno' })
  }
}

exports.getMyApplications = async (req, res) => {
  try {
    const tid = tenantId(req)
    const cpf = req.user?.cpf?.replace(/\D/g, '')
    if (!cpf) return res.status(401).json({ error: 'Não autenticado' })

    const apps = await query(
      `SELECT a.id, a.job_id, a.status, a.applied_at, a.cover_letter,
              j.title, j.work_mode, j.job_type, j.location
       FROM job_applications a
       JOIN jobs j ON j.id = a.job_id
       WHERE a.applicant_cpf = $1 AND a.tenant_id = $2
       ORDER BY a.applied_at DESC`,
      [cpf, tid]
    )
    res.json(apps || [])
  } catch (err) {
    console.error('[jobController] getMyApplications:', err.message)
    res.status(500).json({ error: 'Erro interno' })
  }
}

exports.cancelMyApplication = async (req, res) => {
  try {
    const cpf = req.user?.cpf?.replace(/\D/g, '')
    const tid = tenantId(req)
    const app = await queryOne(
      `SELECT id, applicant_cpf, status FROM job_applications WHERE id=$1 AND tenant_id=$2`,
      [req.params.id, tid]
    )
    if (!app) return res.status(404).json({ error: 'Candidatura não encontrada' })
    if (app.applicant_cpf !== cpf) return res.status(403).json({ error: 'Acesso negado' })
    if (app.status !== 'novo') return res.status(409).json({ error: 'Candidatura já em análise, não pode ser cancelada' })

    await execute(`DELETE FROM job_applications WHERE id=$1`, [req.params.id])
    res.json({ ok: true })
  } catch (err) {
    console.error('[jobController] cancelMyApplication:', err.message)
    res.status(500).json({ error: 'Erro interno' })
  }
}

// ── Admin — Vagas ─────────────────────────────────────────────────────────────

exports.adminListJobs = async (req, res) => {
  try {
    const tid = tenantId(req)
    const { active, category, work_mode, search, limit = 50, offset = 0 } = req.query

    const conditions = ['j.tenant_id = $1']
    const params = [tid]
    let pi = 2

    if (active !== undefined && active !== '') {
      conditions.push(`j.active = $${pi++}`)
      params.push(active === 'true')
    }
    if (category)  { conditions.push(`j.category = $${pi++}`);     params.push(category) }
    if (work_mode) { conditions.push(`j.work_mode = $${pi++}`);    params.push(work_mode) }
    if (search)    { conditions.push(`j.title ILIKE $${pi++}`);    params.push(`%${search}%`) }

    const where = conditions.join(' AND ')
    const countRow = await queryOne(`SELECT COUNT(*) AS total FROM jobs j WHERE ${where}`, params)

    const jobs = await query(
      `SELECT j.*, COUNT(a.id) AS applicants_count
       FROM jobs j
       LEFT JOIN job_applications a ON a.job_id = j.id
       WHERE ${where}
       GROUP BY j.id
       ORDER BY j.created_at DESC
       LIMIT $${pi} OFFSET $${pi + 1}`,
      [...params, +limit, +offset]
    )

    res.json({ jobs: jobs || [], total: parseInt(countRow?.total || '0') })
  } catch (err) {
    console.error('[jobController] adminListJobs:', err.message)
    res.status(500).json({ error: 'Erro interno' })
  }
}

exports.adminCreateJob = async (req, res) => {
  try {
    const tid = tenantId(req)
    const {
      title, description, requirements, benefits, location,
      work_mode = 'presencial', job_type = 'clt', category,
      salary_min, salary_max, salary_hide = false, slots = 1,
      active = true, expires_at
    } = req.body

    if (!title || !description) return res.status(400).json({ error: 'Título e descrição são obrigatórios' })

    const result = await execute(
      `INSERT INTO jobs
         (tenant_id, title, description, requirements, benefits, location,
          work_mode, job_type, category, salary_min, salary_max, salary_hide,
          slots, active, expires_at)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15) RETURNING id`,
      [tid, title, description, requirements||null, benefits||null, location||null,
       work_mode, job_type, category||null,
       salary_min||null, salary_max||null, salary_hide, slots, active,
       expires_at||null]
    )
    res.status(201).json({ ok: true, id: result.rows?.[0]?.id })
  } catch (err) {
    console.error('[jobController] adminCreateJob:', err.message)
    res.status(500).json({ error: 'Erro interno' })
  }
}

exports.adminUpdateJob = async (req, res) => {
  try {
    const tid = tenantId(req)
    const { id } = req.params
    const {
      title, description, requirements, benefits, location,
      work_mode, job_type, category, salary_min, salary_max,
      salary_hide, slots, active, expires_at
    } = req.body
    const result = await execute(
      `UPDATE jobs SET
         title=$1, description=$2, requirements=$3, benefits=$4, location=$5,
         work_mode=$6, job_type=$7, category=$8, salary_min=$9, salary_max=$10,
         salary_hide=$11, slots=$12, active=$13, expires_at=$14, updated_at=NOW()
       WHERE id=$15 AND tenant_id=$16`,
      [title, description, requirements||null, benefits||null, location||null,
       work_mode, job_type, category||null, salary_min||null, salary_max||null,
       salary_hide||false, slots||1, active??true, expires_at||null, id, tid]
    )
    if (result.rowCount === 0) return res.status(404).json({ error: 'Vaga não encontrada' })
    res.json({ ok: true })
  } catch (err) {
    console.error('[jobController] adminUpdateJob:', err.message)
    res.status(500).json({ error: 'Erro interno' })
  }
}

exports.adminToggleJob = async (req, res) => {
  try {
    const tid = tenantId(req)
    const job = await queryOne(`SELECT id, active FROM jobs WHERE id=$1 AND tenant_id=$2`, [req.params.id, tid])
    if (!job) return res.status(404).json({ error: 'Vaga não encontrada' })
    await execute(`UPDATE jobs SET active=$1, updated_at=NOW() WHERE id=$2`, [!job.active, job.id])
    res.json({ ok: true, active: !job.active })
  } catch (err) {
    console.error('[jobController] adminToggleJob:', err.message)
    res.status(500).json({ error: 'Erro interno' })
  }
}

exports.adminDeleteJob = async (req, res) => {
  try {
    const tid = tenantId(req)
    const job = await queryOne(`SELECT id FROM jobs WHERE id=$1 AND tenant_id=$2`, [req.params.id, tid])
    if (!job) return res.status(404).json({ error: 'Vaga não encontrada' })
    const hasApps = await queryOne(
      `SELECT id FROM job_applications WHERE job_id=$1 LIMIT 1`, [req.params.id]
    )
    if (hasApps) {
      await execute(`UPDATE jobs SET active=false, updated_at=NOW() WHERE id=$1`, [req.params.id])
      return res.json({ ok: true, deactivated: true, message: 'Vaga desativada pois possui candidaturas' })
    }
    await execute(`DELETE FROM jobs WHERE id=$1`, [req.params.id])
    res.json({ ok: true })
  } catch (err) {
    console.error('[jobController] adminDeleteJob:', err.message)
    res.status(500).json({ error: 'Erro interno' })
  }
}

// ── Admin — Candidaturas ──────────────────────────────────────────────────────

exports.adminGetApplications = async (req, res) => {
  try {
    const { id: job_id } = req.params
    const { status, limit = 100, offset = 0 } = req.query
    const conditions = ['a.job_id = $1']
    const params = [job_id]
    let pi = 2
    if (status) { conditions.push(`a.status = $${pi++}`); params.push(status) }
    const where = conditions.join(' AND ')

    const apps = await query(
      `SELECT a.*,
              r.about, r.experience, r.education, r.skills, r.file_url,
              r.linkedin, r.portfolio
       FROM job_applications a
       LEFT JOIN resumes r ON r.user_cpf = a.applicant_cpf AND r.tenant_id = a.tenant_id
       WHERE ${where}
       ORDER BY a.applied_at DESC
       LIMIT $${pi} OFFSET $${pi+1}`,
      [...params, +limit, +offset]
    )
    res.json(apps || [])
  } catch (err) {
    console.error('[jobController] adminGetApplications:', err.message)
    res.status(500).json({ error: 'Erro interno' })
  }
}

exports.adminGetApplication = async (req, res) => {
  try {
    const tid = tenantId(req)
    const app = await queryOne(
      `SELECT a.*,
              r.about, r.experience, r.education, r.skills, r.file_url,
              r.linkedin, r.portfolio, r.email AS resume_email, r.phone AS resume_phone,
              j.title AS job_title
       FROM job_applications a
       LEFT JOIN resumes r ON r.user_cpf = a.applicant_cpf AND r.tenant_id = a.tenant_id
       LEFT JOIN jobs j ON j.id = a.job_id
       WHERE a.id = $1 AND a.tenant_id = $2`,
      [req.params.id, tid]
    )
    if (!app) return res.status(404).json({ error: 'Candidatura não encontrada' })
    res.json(app)
  } catch (err) {
    console.error('[jobController] adminGetApplication:', err.message)
    res.status(500).json({ error: 'Erro interno' })
  }
}

exports.adminUpdateApplication = async (req, res) => {
  try {
    const tid = tenantId(req)
    const { status, admin_notes } = req.body
    if (status && !VALID_STATUSES.includes(status))
      return res.status(400).json({ error: 'Status inválido' })
    const result = await execute(
      `UPDATE job_applications SET status=COALESCE($1, status), admin_notes=$2, updated_at=NOW() WHERE id=$3 AND tenant_id=$4`,
      [status || null, admin_notes ?? null, req.params.id, tid]
    )
    if (result.rowCount === 0) return res.status(404).json({ error: 'Candidatura não encontrada' })
    res.json({ ok: true })
  } catch (err) {
    console.error('[jobController] adminUpdateApplication:', err.message)
    res.status(500).json({ error: 'Erro interno' })
  }
}

// ── Admin — Currículos ────────────────────────────────────────────────────────

exports.adminSearchResumes = async (req, res) => {
  try {
    const tid = tenantId(req)
    const { search, limit = 50, offset = 0 } = req.query
    const conditions = ['r.tenant_id = $1']
    const params = [tid]
    let pi = 2
    if (search) {
      conditions.push(`(r.user_nome ILIKE $${pi} OR r.user_cpf ILIKE $${pi})`)
      params.push(`%${search}%`)
      pi++
    }
    const where = conditions.join(' AND ')
    const resumes = await query(
      `SELECT r.id, r.user_cpf, r.user_nome, r.email, r.phone, r.updated_at,
              COUNT(a.id) AS applications_count
       FROM resumes r
       LEFT JOIN job_applications a ON a.applicant_cpf = r.user_cpf AND a.tenant_id = r.tenant_id
       WHERE ${where}
       GROUP BY r.id
       ORDER BY r.updated_at DESC
       LIMIT $${pi} OFFSET $${pi+1}`,
      [...params, +limit, +offset]
    )
    res.json(resumes || [])
  } catch (err) {
    console.error('[jobController] adminSearchResumes:', err.message)
    res.status(500).json({ error: 'Erro interno' })
  }
}

exports.adminGetResume = async (req, res) => {
  try {
    const tid = tenantId(req)
    const cpf = req.params.cpf.replace(/\D/g, '')
    const resume = await queryOne(
      `SELECT * FROM resumes WHERE tenant_id=$1 AND user_cpf=$2`, [tid, cpf]
    )
    if (!resume) return res.status(404).json({ error: 'Currículo não encontrado' })
    res.json(resume)
  } catch (err) {
    console.error('[jobController] adminGetResume:', err.message)
    res.status(500).json({ error: 'Erro interno' })
  }
}

// ── Admin — KPIs ──────────────────────────────────────────────────────────────

exports.adminJobsKpi = async (req, res) => {
  try {
    const tid = tenantId(req)
    const [active, inactive, total_apps, hired] = await Promise.all([
      queryOne(`SELECT COUNT(*) AS c FROM jobs WHERE tenant_id=$1 AND active=true`, [tid]),
      queryOne(`SELECT COUNT(*) AS c FROM jobs WHERE tenant_id=$1 AND active=false`, [tid]),
      queryOne(`SELECT COUNT(*) AS c FROM job_applications a JOIN jobs j ON j.id=a.job_id WHERE j.tenant_id=$1`, [tid]),
      queryOne(`SELECT COUNT(*) AS c FROM job_applications a JOIN jobs j ON j.id=a.job_id WHERE j.tenant_id=$1 AND a.status='contratado'`, [tid]),
    ])
    res.json({
      active: parseInt(active?.c || '0'),
      inactive: parseInt(inactive?.c || '0'),
      total_applications: parseInt(total_apps?.c || '0'),
      hired: parseInt(hired?.c || '0'),
    })
  } catch (err) {
    console.error('[jobController] adminJobsKpi:', err.message)
    res.status(500).json({ error: 'Erro interno' })
  }
}
