package consulta

import (
	"context"
	"database/sql"
	"fmt"
	"strings"

	"gitlab.com/sistema-pro/xmlcolibex/internal/banco"
)

func CarregarCabecalho(ctx context.Context, db *sql.DB, idMaster int) (CabecalhoMaster, error) {
	q := `
SELECT cm.id_clientes_master,
  CONCAT(COALESCE(cm.nome,''),' ',COALESCE(cm.razao_social,'')) AS nome_concat,
  COALESCE(cm.email,''), COALESCE(cm.telefone,''), COALESCE(cm.whatsapp,''), COALESCE(cm.celular,''),
  COALESCE(d.dominio,''),
  REPLACE(xc.data_alteracao,' ','T') AS data_atualizacao,
  COALESCE(he.creci_dados,'')
FROM clientes_master cm
LEFT JOIN dominios d ON d.id_cliente = cm.id_clientes_master
LEFT JOIN xml_config xc ON xc.id_master = cm.id_clientes_master
LEFT JOIN historia_empresa he ON he.id_master = cm.id_clientes_master
WHERE cm.id_clientes_master = ?
ORDER BY d.data_cadastro DESC
LIMIT 1`
	var h CabecalhoMaster
	err := banco.Retentar(3, func() error {
		return db.QueryRowContext(ctx, q, idMaster).Scan(
			&h.IDMaster, &h.NomeConcat, &h.Email, &h.Telefone, &h.WhatsApp, &h.Celular,
			&h.Dominio, &h.DataAlteracao, &h.CRECI,
		)
	})
	return h, err
}

func CarregarTextoPadrao(ctx context.Context, db *sql.DB, idMaster int) (string, error) {
	var t sql.NullString
	err := banco.Retentar(3, func() error {
		return db.QueryRowContext(ctx,
			`SELECT texto_adicional FROM texto_padrao WHERE id_master = ? LIMIT 1`, idMaster,
		).Scan(&t)
	})
	if err == sql.ErrNoRows {
		return "", nil
	}
	if err != nil {
		return "", err
	}
	if t.Valid {
		return t.String, nil
	}
	return "", nil
}

func CarregarFotosPadraoJSON(ctx context.Context, db *sql.DB, idMaster int) (string, error) {
	var f sql.NullString
	err := banco.Retentar(3, func() error {
		return db.QueryRowContext(ctx,
			`SELECT fotos FROM fotos_padrao WHERE id_master = ? LIMIT 1`, idMaster,
		).Scan(&f)
	})
	if err == sql.ErrNoRows {
		return "", nil
	}
	if err != nil {
		return "", err
	}
	if f.Valid {
		return f.String, nil
	}
	return "", nil
}

func CarregarDescricaoCaixa(ctx context.Context, db *sql.DB, idMaster, idImovel int) (string, error) {
	var d sql.NullString
	err := db.QueryRowContext(ctx,
		`SELECT descricao_caixa FROM imoveis WHERE id_master = ? AND id_imovel = ? LIMIT 1`,
		idMaster, idImovel,
	).Scan(&d)
	if err == sql.ErrNoRows {
		return "", nil
	}
	if err != nil {
		return "", err
	}
	if d.Valid {
		return d.String, nil
	}
	return "", nil
}

func CarregarDestaques(ctx context.Context, db *sql.DB, idsConfig []int, idMaster int, idsImovel []int) (map[int]DestaqueDB, error) {
	out := make(map[int]DestaqueDB)
	if len(idsImovel) == 0 || len(idsConfig) == 0 {
		return out, nil
	}
	phConfig := strings.TrimRight(strings.Repeat("?,", len(idsConfig)), ",")
	phImovel := strings.TrimRight(strings.Repeat("?,", len(idsImovel)), ",")
	args := make([]any, 0, len(idsConfig)+len(idsImovel)+1)
	for _, id := range idsConfig {
		args = append(args, id)
	}
	args = append(args, idMaster)
	for _, id := range idsImovel {
		args = append(args, id)
	}
	q := fmt.Sprintf(`
SELECT xi.imovel, COALESCE(xd.seo,''), COALESCE(xd.nome,'')
FROM xml_imoveis xi
LEFT JOIN xml_destaques xd ON xi.destaque = xd.id_xml_destaque
INNER JOIN imoveis i ON xi.imovel = i.id_imovel AND xi.id_master = i.id_master
WHERE xi.id_xml_config IN (%s) AND xi.id_master = ? AND xi.imovel IN (%s) AND i.status = 'Ativo'`,
		phConfig, phImovel)

	err := banco.Retentar(3, func() error {
		rows, err := db.QueryContext(ctx, q, args...)
		if err != nil {
			return err
		}
		defer rows.Close()
		for rows.Next() {
			var d DestaqueDB
			if err := rows.Scan(&d.IDImovel, &d.SEO, &d.Nome); err != nil {
				return err
			}
			out[d.IDImovel] = d
		}
		return rows.Err()
	})
	return out, err
}

func CarregarFotosImoveis(ctx context.Context, db *sql.DB, idsImovel []int) (map[int][]FotoDB, error) {
	out := make(map[int][]FotoDB)
	if len(idsImovel) == 0 {
		return out, nil
	}
	ph := strings.TrimRight(strings.Repeat("?,", len(idsImovel)), ",")
	args := make([]any, len(idsImovel))
	for i, id := range idsImovel {
		args[i] = id
	}
	q := fmt.Sprintf(`SELECT id, id_master, id_imovel, foto, posicao FROM fotos_imoveis WHERE id_imovel IN (%s) ORDER BY id_imovel, posicao`, ph)
	err := banco.Retentar(3, func() error {
		rows, err := db.QueryContext(ctx, q, args...)
		if err != nil {
			return err
		}
		defer rows.Close()
		for rows.Next() {
			var f FotoDB
			if err := rows.Scan(&f.ID, &f.IDMaster, &f.IDImovel, &f.Foto, &f.Posicao); err != nil {
				return err
			}
			out[f.IDImovel] = append(out[f.IDImovel], f)
		}
		return rows.Err()
	})
	return out, err
}

func CarregarFotosCaixa(ctx context.Context, db *sql.DB, refCaixa string) ([]string, error) {
	if refCaixa == "" {
		return nil, nil
	}
	q := `SELECT id FROM z_fotos_imoveis_caixa WHERE id_imovel_caixa = ?`
	var urls []string
	err := banco.Retentar(3, func() error {
		rows, err := db.QueryContext(ctx, q, refCaixa)
		if err != nil {
			return err
		}
		defer rows.Close()
		urls = urls[:0]
		for rows.Next() {
			var id int
			if err := rows.Scan(&id); err != nil {
				return err
			}
			urls = append(urls, fmt.Sprintf("%d", id))
		}
		return rows.Err()
	})
	return urls, err
}

func CarregarFeaturesServicos(ctx context.Context, db *sql.DB, servicosJSON string) ([]string, error) {
	ids := parseServicosJSON(servicosJSON)
	if len(ids) == 0 {
		return nil, nil
	}
	ph := strings.TrimRight(strings.Repeat("?,", len(ids)), ",")
	args := make([]any, len(ids))
	for i, id := range ids {
		args[i] = id
	}
	q := fmt.Sprintf(`SELECT COALESCE(features_vivareal,'') FROM caracteristicas WHERE id IN (%s)`, ph)
	var feats []string
	err := banco.Retentar(3, func() error {
		rows, err := db.QueryContext(ctx, q, args...)
		if err != nil {
			return err
		}
		defer rows.Close()
		for rows.Next() {
			var f string
			if err := rows.Scan(&f); err != nil {
				return err
			}
			if f != "" {
				feats = append(feats, f)
			}
		}
		return rows.Err()
	})
	return feats, err
}

func CarregarFeaturesZap(ctx context.Context, db *sql.DB, servicosJSON string) ([]string, error) {
	ids := parseServicosJSON(servicosJSON)
	if len(ids) == 0 {
		return nil, nil
	}
	ph := strings.TrimRight(strings.Repeat("?,", len(ids)), ",")
	args := make([]any, len(ids))
	for i, id := range ids {
		args[i] = id
	}
	q := fmt.Sprintf(`SELECT COALESCE(features_zapimovel,'') FROM caracteristicas WHERE id IN (%s)`, ph)
	var feats []string
	err := banco.Retentar(3, func() error {
		rows, err := db.QueryContext(ctx, q, args...)
		if err != nil {
			return err
		}
		defer rows.Close()
		for rows.Next() {
			var f string
			if err := rows.Scan(&f); err != nil {
				return err
			}
			if f != "" {
				feats = append(feats, f)
			}
		}
		return rows.Err()
	})
	return feats, err
}

func parseServicosJSON(raw string) []int {
	raw = strings.TrimSpace(raw)
	if raw == "" || raw == "[]" {
		return nil
	}
	raw = strings.TrimPrefix(raw, "[")
	raw = strings.TrimSuffix(raw, "]")
	parts := strings.Split(raw, ",")
	var ids []int
	for _, p := range parts {
		p = strings.Trim(strings.TrimSpace(p), `"`)
		if p == "" {
			continue
		}
		var id int
		fmt.Sscanf(p, "%d", &id)
		if id > 0 {
			ids = append(ids, id)
		}
	}
	return ids
}