'use strict'

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

class ClubMember {
  static create({ tenant_id, club_id, plan_id, user_cpf, user_nome, user_telefone, user_email,
                  asaas_customer_id, asaas_subscription_id, next_billing_at }) {
    return execute(
      `INSERT INTO club_members
         (tenant_id, club_id, plan_id, user_cpf, user_nome, user_telefone, user_email,
          status, asaas_customer_id, asaas_subscription_id, next_billing_at, started_at)
       VALUES ($1,$2,$3,$4,$5,$6,$7,'pending_payment',$8,$9,$10, NOW()) RETURNING id`,
      [tenant_id, club_id, plan_id,
       user_cpf.replace(/\D/g, ''), user_nome,
       user_telefone || null, user_email || null,
       asaas_customer_id || null, asaas_subscription_id || null,
       next_billing_at || null]
    )
  }

  static findByCpfAndClub(cpf, clubId) {
    return queryOne(
      `SELECT * FROM club_members
       WHERE user_cpf=$1 AND club_id=$2 AND status NOT IN ('canceled')
       ORDER BY id DESC LIMIT 1`,
      [cpf.replace(/\D/g, ''), clubId]
    )
  }

  static findAllByCpf(cpf) {
    return query(
      `SELECT cm.*, bc.name AS club_name, bc.slug AS club_slug,
              cp.name AS plan_name, cp.price_monthly, cp.features
       FROM club_members cm
       JOIN benefit_clubs bc ON bc.id = cm.club_id
       JOIN club_plans    cp ON cp.id = cm.plan_id
       WHERE cm.user_cpf = $1 AND cm.status NOT IN ('canceled')
       ORDER BY cm.id`,
      [cpf.replace(/\D/g, '')]
    )
  }

  static findById(id) {
    return queryOne(
      `SELECT cm.*, bc.name AS club_name, bc.slug AS club_slug,
              cp.name AS plan_name, cp.price_monthly
       FROM club_members cm
       JOIN benefit_clubs bc ON bc.id = cm.club_id
       JOIN club_plans    cp ON cp.id = cm.plan_id
       WHERE cm.id = $1`,
      [id]
    )
  }

  static listByClub(clubId, { status, limit = 100, offset = 0 } = {}) {
    if (status) {
      return query(
        `SELECT cm.*, cp.name AS plan_name, cp.price_monthly
         FROM club_members cm
         JOIN club_plans cp ON cp.id = cm.plan_id
         WHERE cm.club_id=$1 AND cm.status=$2
         ORDER BY cm.created_at DESC LIMIT $3 OFFSET $4`,
        [clubId, status, limit, offset]
      )
    }
    return query(
      `SELECT cm.*, cp.name AS plan_name, cp.price_monthly
       FROM club_members cm
       JOIN club_plans cp ON cp.id = cm.plan_id
       WHERE cm.club_id=$1
       ORDER BY cm.created_at DESC LIMIT $2 OFFSET $3`,
      [clubId, limit, offset]
    )
  }

  static updateStatus(id, status) {
    const extra = status === 'canceled' ? `, canceled_at=NOW()` : ''
    return execute(
      `UPDATE club_members SET status=$1${extra} WHERE id=$2`,
      [status, id]
    )
  }

  static updateSubscription(id, { asaas_subscription_id, asaas_customer_id, next_billing_at }) {
    return execute(
      `UPDATE club_members
       SET asaas_subscription_id=$1, asaas_customer_id=$2, next_billing_at=$3
       WHERE id=$4`,
      [asaas_subscription_id, asaas_customer_id, next_billing_at || null, id]
    )
  }

  static findBySubscriptionId(subscriptionId) {
    return queryOne(
      'SELECT * FROM club_members WHERE asaas_subscription_id=$1',
      [subscriptionId]
    )
  }

  // Membros com pagamento em atraso há mais de N dias
  static findOverdue(daysOverdue = 5) {
    return query(
      `SELECT cm.*, cp.price_monthly
       FROM club_members cm
       JOIN club_plans cp ON cp.id = cm.plan_id
       WHERE cm.status = 'active'
         AND cm.next_billing_at < NOW() - ($1::int * INTERVAL '1 day')`,
      [parseInt(daysOverdue, 10)]
    )
  }
}

module.exports = ClubMember