import "server-only"
import { Pool } from "pg"

type PortalContato = {
  id: string
  email: string
  senha: string
  nome: string | null
}

export type PortalCliente = {
  id: string
  razao_social: string | null
  nome_fantasia: string | null
  cnpj: string | null
}

const pool = new Pool({
  connectionString: process.env.PORTAL_DB_URL,
  host: process.env.PORTAL_DB_HOST || undefined,
  port: process.env.PORTAL_DB_PORT ? Number(process.env.PORTAL_DB_PORT) : undefined,
  user: process.env.PORTAL_DB_USER || undefined,
  password: process.env.PORTAL_DB_PASSWORD || undefined,
  database: process.env.PORTAL_DB_NAME || undefined,
})

function assertPortalDbConfigured() {
  if (!process.env.PORTAL_DB_URL && !process.env.PORTAL_DB_HOST) {
    throw new Error("PORTAL_DB_URL or PORTAL_DB_HOST is required")
  }
}

export async function getContatoByEmail(email: string): Promise<PortalContato | null> {
  assertPortalDbConfigured()
  const res = await pool.query(
    `select id::text as id, email, senha, nome
     from contatos
     where lower(email) = lower($1)
     limit 1`,
    [email]
  )
  return (res.rows?.[0] as PortalContato) ?? null
}

export async function getClientesForContato(contatoId: string): Promise<PortalCliente[]> {
  assertPortalDbConfigured()
  const res = await pool.query(
    `select c.id::text as id, c.razao_social, c.nome_fantasia, c.cnpj
     from permissaocontatos pc
     join clientes c on c.id = pc.cliente_id
     where pc.contato_id = $1
       and pc.ativo = true
       and pc.acesso_portal = true
     order by c.razao_social asc`,
    [contatoId]
  )
  return (res.rows as PortalCliente[]) ?? []
}

export type SmtpPlanRow = {
  cliente_id: string
  razao_social: string | null
  plano: string
  dominio: string | null
}

export async function getActiveSmtpPlans(): Promise<SmtpPlanRow[]> {
  assertPortalDbConfigured()
  const res = await pool.query(
    `select c.id::text as cliente_id,
            c.razao_social,
            p.nome as plano,
            d.dominio
     from contratos ct
     join contratosdepacotes cdp on cdp.contrato_id = ct.id
     join pacotes p on p.id = cdp.pacote_id
     join clientes c on c.id = ct.cliente_id
     left join dominios d on d.id = cdp.dominio_id
     where ct.estado = 20
       and p.nome ilike '%SMTP%'
     order by c.razao_social asc, p.nome asc`,
  )
  return (res.rows as SmtpPlanRow[]) ?? []
}