mercado.clube67.com/backend/models/Ticket.js

'use strict'

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

class Ticket {
  /**
   * Reserva atômica: verifica disponibilidade e insere em uma única transação com status reservado.
   */
  static async reserve({ raffle_id, numeros, usuario_nome, usuario_telefone, usuario_cpf, order_id, preco_pago }) {
    return transaction(async (client) => {
      const raffle = await client.query(
        'SELECT id, ativo, total_numeros FROM raffles WHERE id = $1 FOR UPDATE',
        [raffle_id]
      )
      if (!raffle.rows[0]) throw new Error('Sorteio não encontrado')
      if (!raffle.rows[0].ativo) throw new Error('Sorteio encerrado')
      
      const insertIds = []
      
      // Permitir reservar múltiplos números
      const numsToReserve = Array.isArray(numeros) ? numeros : [numeros]
      
      for (const numero of numsToReserve) {
        if (numero < 1 || numero > raffle.rows[0].total_numeros) throw new Error(`Número inválido: ${numero}`)

        // Verifica se número já foi vendido ou reservado ativamente
        const existing = await client.query(
          "SELECT id FROM tickets WHERE raffle_id = $1 AND numero = $2 AND (status = 'sold' OR (status = 'reserved' AND reserved_until > NOW()))",
          [raffle_id, numero]
        )
        if (existing.rows.length > 0) throw new Error(`Número ${numero} já está reservado ou vendido`)
        
        // Remove reserva expirada se existir
        await client.query(
          "DELETE FROM tickets WHERE raffle_id = $1 AND numero = $2 AND status = 'reserved' AND reserved_until <= NOW()",
          [raffle_id, numero]
        )

        // Insere o ticket como reservado (+5 min TTL)
        const result = await client.query(
          `INSERT INTO tickets (raffle_id, numero, usuario_nome, usuario_telefone, usuario_cpf, status, reserved_until, order_id, price_paid)
           VALUES ($1, $2, $3, $4, $5, 'reserved', NOW() + INTERVAL '5 minutes', $6, $7) RETURNING id`,
          [raffle_id, numero, usuario_nome, usuario_telefone || null, usuario_cpf || null, order_id, preco_pago]
        )
        insertIds.push(result.rows[0].id)
      }

      return { ids: insertIds }
    })
  }

  /**
   * Confirma o pagamento convertendo reservados em vendidos
   */
  static async confirmOrder(order_id) {
    return execute(
      "UPDATE tickets SET status = 'sold', reserved_until = NULL WHERE order_id = $1 AND status = 'reserved'",
      [order_id]
    )
  }

  static getByOrderId(order_id) {
    return query(
      'SELECT t.*, r.titulo as raffle_nome FROM tickets t JOIN raffles r ON r.id = t.raffle_id WHERE t.order_id = $1',
      [order_id]
    )
  }
  
  /**
   * Libera tickets reservados não pagos
   */
  static async releaseExpiredReservations() {
    return execute("DELETE FROM tickets WHERE status = 'reserved' AND reserved_until <= NOW()")
  }

  static findByRaffle(raffle_id) {
    return query(
      'SELECT * FROM tickets WHERE raffle_id = $1 ORDER BY numero ASC',
      [raffle_id]
    )
  }

  static findByNumber(raffle_id, numero) {
    return queryOne(
      'SELECT * FROM tickets WHERE raffle_id = $1 AND numero = $2',
      [raffle_id, numero]
    )
  }

  static getSoldNumbers(raffle_id) {
    return query("SELECT numero FROM tickets WHERE raffle_id = $1 AND (status = 'sold' OR (status = 'reserved' AND reserved_until > NOW()))", [raffle_id])
      .then(rows => rows.map(r => r.numero))
  }

  static async countByRaffle(raffle_id) {
    const row = await queryOne(
      "SELECT COUNT(*) as total FROM tickets WHERE raffle_id = $1 AND (status = 'sold' OR (status = 'reserved' AND reserved_until > NOW()))",
      [raffle_id]
    )
    return row ? parseInt(row.total) : 0
  }

  static getAll() {
    return query(`
      SELECT t.*, r.titulo AS raffle_name
      FROM tickets t
      JOIN raffles r ON t.raffle_id = r.id
      ORDER BY t.data_compra DESC
    `)
  }

  static deleteById(id) {
    return execute('DELETE FROM tickets WHERE id = $1', [id])
  }

  static deleteByIdAndTenant(id, tenantId) {
    return execute(
      'DELETE FROM tickets t USING raffles r WHERE t.id = $1 AND t.raffle_id = r.id AND r.tenant_id = $2',
      [id, tenantId]
    )
  }

  static findByUserCpf(cpf) {
    const cleanCpf = cpf.replace(/[^\d]+/g, '')
    return query(`
      SELECT t.*, r.titulo AS raffle_name
      FROM tickets t
      JOIN raffles r ON t.raffle_id = r.id
      WHERE t.usuario_cpf = $1
      ORDER BY t.data_compra DESC
    `, [cleanCpf])
  }
}

module.exports = Ticket