func companies(db *sql.DB) ([]CompanyInfo, error) {
	selectCompanies := `
		SELECT ID, NAME
		FROM companies
		ORDER BY NAME;`
	rows, err := db.Query(selectCompanies)
	if err != nil {
		err = errors.Wrap(err, "falha ao ler banco de dados")
		return nil, err
	}
	defer rows.Close()
	var info CompanyInfo
	var list []CompanyInfo
	for rows.Next() {
		err := rows.Scan(&info.id, &info.name)
		if err == nil {
			list = append(list, info)
		}
	}
	return list, nil
}
companies returns available companies in the DB
package reports
import (
	"database/sql"
	"fmt"
	"strconv"
	"strings"
	"github.com/dude333/rapina"
	"github.com/dude333/rapina/parsers"
	"github.com/pkg/errors"
)
type accItems struct {
	code    uint32
	cdConta string
	dsConta string
}
func (r Report) accountsItems(cid int) (items []accItems, err error) {
	selectItems := fmt.Sprintf(`
	SELECT DISTINCT
		CODE, CD_CONTA, DS_CONTA
	FROM
		dfp a
	WHERE
		ID_CIA = "%d"
		AND VERSAO = (SELECT MAX(VERSAO) FROM dfp WHERE ID_CIA = a.ID_CIA AND YEAR = a.YEAR)
	ORDER BY
		CD_CONTA, DS_CONTA
	;`, cid)
	rows, err := r.db.Query(selectItems)
	if err != nil {
		panic(err)
	}
	defer rows.Close()
	var item accItems
	for rows.Next() {
		err = rows.Scan(&item.code, &item.cdConta, &item.dsConta)
		if err != nil {
			return
		}
		items = append(items, item)
	}
	return
}
func (r Report) accountsValues(year int) (map[uint32]float32, error) {
	values := make(map[uint32]float32)
	lastYear, isITR, err := r.lastYear(r.cid)
	if err != nil {
		return nil, err
	}
	if year == lastYear && isITR {
		err = r.ttm(r.cid, values)
	} else {
		err = r.dfp(r.cid, year, values)
	}
	if err != nil {
		return values, err
	}
	if sum(values) == 0 {
		return values, nil
	}
	table := "dfp"
	if isITR {
		table = "itr"
	}
	values[parsers.Escala] = r.scale(r.cid, year, table)
	_ = r.shares(r.cid, year, values)
	var v float32
	v, err = r.value(r.cid, year-1, parsers.Estoque)
	if err == nil {
		values[parsers.EstoqueMedio] = avg(values[parsers.Estoque], v)
	}
	v, err = r.value(r.cid, year-1, parsers.Equity)
	if err == nil {
		values[parsers.EquityAvg] = avg(values[parsers.Equity], v)
	}
	if r.code != "" {
		date := rapina.LastBusinessDayOfYear(year)
		q, err := r.fetchStock.Quote(r.code, date)
		if err == nil {
			values[parsers.Quote] = float32(q)
		}
	}
	return values, nil
}
func avg(nums ...float32) float32 {
	var total float32 = 0
	var n float32 = 0
	for _, num := range nums {
		if num > 0 {
			total += num
			n++
		}
	}
	if n <= 0 {
		return 0
	}
	return total / n
}
func (r Report) lastYear(cid int) (int, bool, error) {
	if cid == 0 {
		return 0, false, fmt.Errorf("customer ID not set")
	}
	numErr := 0
	selectDfpLastYear := `SELECT MAX(CAST(YEAR AS INTEGER)) YEAR FROM dfp WHERE ID_CIA = ?;`
	dfp := 0
	err := r.db.QueryRow(selectDfpLastYear, cid).Scan(&dfp)
	if err != nil {
		numErr++
	}
	selectItrLastYear := `SELECT MAX(CAST(YEAR AS INTEGER)) YEAR FROM itr WHERE ID_CIA = ?;`
	itr := 0
	err = r.db.QueryRow(selectItrLastYear, cid).Scan(&itr)
	if err != nil {
		numErr++
	}
	if numErr == 2 {
		return 0, false, sql.ErrNoRows
	}
	if itr > dfp {
		return itr, true, nil  
	}
	return dfp, false, nil  
}
func (r Report) LastYearRange(cid int) (int, int, error) {
	if cid == 0 {
		return 0, 0, fmt.Errorf("customer ID not set")
	}
	s := `
		SELECT DISTINCT DT_FIM_EXERC
		FROM dfp
		WHERE ID_CIA = ?
		ORDER BY DT_FIM_EXERC DESC
		LIMIT 2;
  `
	rows, err := r.db.Query(s, cid)
	if err != nil {
		return 0, 0, err
	}
	defer rows.Close()
	var dateRange [2]int  
	i := 0
	for rows.Next() {
		_ = rows.Scan(&dateRange[i])
		i++
		if i >= len(dateRange) {
			break
		}
	}
	for _, v := range dateRange {
		if v == 0 {
			return 0, 0, fmt.Errorf("range not found")
		}
	}
	return dateRange[1], dateRange[0], nil
}
func (r Report) dfp(cid, year int, _values map[uint32]float32) error {
	selectReport := `
	SELECT
		CODE, VL_CONTA
	FROM
		dfp a
	WHERE
		ID_CIA = $1 
		AND YEAR = $2
		AND VERSAO = (SELECT MAX(VERSAO) FROM dfp WHERE ID_CIA = a.ID_CIA AND YEAR = a.YEAR)
	;`
	rows, err := r.db.Query(selectReport, cid, year)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var code uint32
		var vlConta float32
		err := rows.Scan(&code, &vlConta)
		if err == nil {
			_values[code] = vlConta
		}
	}
	return nil
}
func (r Report) lastDate(cid int) (int, string, error) {
	rowDfp := r.db.QueryRow("SELECT MAX(DT_FIM_EXERC) FROM dfp WHERE ID_CIA = ? LIMIT 1;", cid)
	maxDfp := 0
	err := rowDfp.Scan(&maxDfp)
	if err != nil {
		return 0, "", err
	}
	rowItr := r.db.QueryRow("SELECT MAX(DT_FIM_EXERC) FROM itr WHERE ID_CIA = ? LIMIT 1;", cid)
	maxItr := 0
	err = rowItr.Scan(&maxItr)
	if err != nil {
		return 0, "", err
	}
	if maxDfp >= maxItr {
		return maxDfp, "dfp", nil
	}
	return maxItr, "itr", nil
}
func (r Report) lastBalance(cid int) (map[uint32]float32, error) {
	d, table, err := r.lastDate(cid)
	if err != nil {
		return nil, err
	}
	if table != "dfp" && table != "itr" {
		return nil, fmt.Errorf("table %s is not allowed", table)
	}
	selectBalance := fmt.Sprintf(`
		SELECT 
			date(DT_FIM_EXERC, 'unixepoch') DT, CODE, SUM(VL_CONTA) TOTAL
		FROM %s t
		WHERE 
			ID_CIA = $1
			AND DT_FIM_EXERC = $2
			AND VERSAO = (SELECT MAX(VERSAO) FROM %s WHERE ID_CIA = t.ID_CIA AND DT_FIM_EXERC = t.DT_FIM_EXERC)
			AND CAST(substr(CD_CONTA, 1, 1) as decimal) <= 2
		GROUP BY
			DT_FIM_EXERC, CODE, CD_CONTA;
	`, table, table)
	rows, err := r.db.Query(selectBalance, cid, d)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	balance := make(map[uint32]float32)
	var maxDt string
	var code uint32
	var vlConta float32
	for rows.Next() {
		err := rows.Scan(&maxDt, &code, &vlConta)
		if err == nil {
			balance[code] = vlConta
		}
	}
	return balance, nil
}
func (r Report) ttm(cid int, _values map[uint32]float32) error {
	lastYear, err := r.lastDFPYear(cid)
	if err != nil {
		return err
	}
	selectQuarters := `SELECT CODE, sum(VAL) FROM (
	-- Last quarter from last year
	SELECT CODE, sum(VAL) VAL FROM (
		SELECT CODE, VL_CONTA VAL -- Year total
		FROM dfp d 
		WHERE 
			ID_CIA = $1
			AND YEAR = $2
			AND VERSAO = (SELECT max(VERSAO) FROM dfp WHERE ID_CIA = d.ID_CIA AND YEAR = d.YEAR)	
			AND CAST(substr(CD_CONTA, 1, 1) as decimal) > 2 -- IGNORE BALANCE SHEETS	
		UNION
		SELECT CODE, -1*VL_CONTA VAL -- Minus 3 semesters
		FROM itr i
		WHERE 
			ID_CIA = $1
			AND YEAR <= $2
			AND CAST(substr(CD_CONTA, 1, 1) as decimal) > 2 -- IGNORE BALANCE SHEETS	
			AND VERSAO = (SELECT MAX(VERSAO) FROM itr WHERE ID_CIA = i.ID_CIA AND CODE = i.CODE AND DT_FIM_EXERC = i.DT_FIM_EXERC)
			AND ID IN (SELECT ID FROM itr WHERE ID_CIA = i.ID_CIA AND CODE = i.CODE AND DT_FIM_EXERC = i.DT_FIM_EXERC ORDER BY DT_FIM_EXERC desc LIMIT 3)
	)
	GROUP BY CODE
	UNION
	-- Last 3 quarters
	SELECT CODE, sum(VL_CONTA) VAL
	FROM itr i
	WHERE 
		ID_CIA = $1
		AND CAST(substr(CD_CONTA, 1, 1) as decimal) > 2 -- IGNORE BALANCE SHEETS	
		AND VERSAO = (SELECT MAX(VERSAO) FROM itr WHERE ID_CIA = i.ID_CIA AND CODE = i.CODE AND DT_FIM_EXERC = i.DT_FIM_EXERC)
		AND ID IN (SELECT ID FROM itr WHERE ID_CIA = i.ID_CIA AND CODE = i.CODE ORDER BY DT_FIM_EXERC desc LIMIT 3)
	GROUP BY CODE
)
GROUP BY CODE	
ORDER BY CODE;`
	rows, err := r.db.Query(selectQuarters, cid, lastYear)
	if err != nil {
		return err
	}
	defer rows.Close()
	var code uint32
	var vlConta float32
	for rows.Next() {
		err := rows.Scan(&code, &vlConta)
		if err == nil {
			_values[code] = vlConta
		}
	}
	bal, err := r.lastBalance(cid)
	if err != nil {
		return err
	}
	for k, v := range bal {
		_values[k] = v
	}
	return nil
}
func (r Report) lastDFPYear(cid int) (int, error) {
	if cid == 0 {
		return 0, fmt.Errorf("customer ID not set")
	}
	s := `SELECT MAX(YEAR) FROM dfp	WHERE ID_CIA = ?;`
	row := r.db.QueryRow(s, cid)
	var lastDate int
	err := row.Scan(&lastDate)
	return lastDate, err
}
func (r Report) shares(cid int, year int, values map[uint32]float32) error {
	selectFRE := `
		SELECT 
			Quantidade_Total_Acoes_Circulacao, Percentual_Total_Acoes_Circulacao
		FROM fre f
		WHERE
			ID_CIA = $1
			AND YEAR = $2
			AND Versao = (SELECT MAX(Versao) FROM fre WHERE ID_CIA = f.ID_CIA AND YEAR = f.YEAR);
	`
	row := r.db.QueryRow(selectFRE, cid, year)
	var shares float32
	var freeFloat float32
	err := row.Scan(&shares, &freeFloat)
	if err != nil && err != sql.ErrNoRows {
		return err
	}
	values[parsers.Shares] = shares
	values[parsers.FreeFloat] = freeFloat
	return nil
}
func (r Report) sharesAvg(cids []string, year int, values map[uint32]float32) error {
	selectFRE := fmt.Sprintf(`
		SELECT 
			AVG(Quantidade_Total_Acoes_Circulacao), AVG(Percentual_Total_Acoes_Circulacao)
		FROM fre f
		WHERE
			ID_CIA IN (%s)
			AND YEAR = $1
			AND Versao = (SELECT MAX(Versao) FROM fre WHERE ID_CIA = f.ID_CIA AND YEAR = f.YEAR);
	`, strings.Join(cids, ","))
	row := r.db.QueryRow(selectFRE, year)
	var sharesAvg float32
	var freeFloatAvg float32
	err := row.Scan(&sharesAvg, &freeFloatAvg)
	if err != nil && err != sql.ErrNoRows {
		return err
	}
	values[parsers.Shares] = sharesAvg
	values[parsers.FreeFloat] = freeFloatAvg
	return nil
}
func (r Report) value(cid, year int, code uint32) (float32, error) {
	selectInventory := `
	SELECT
		VL_CONTA
	FROM
		dfp a
	WHERE
		ID_CIA = $1 
		AND YEAR = $2
		AND CODE = $3
		AND VERSAO = (SELECT MAX(VERSAO) FROM dfp WHERE ID_CIA = a.ID_CIA AND YEAR = a.YEAR)
	;`
	val := float32(0)
	err := r.db.QueryRow(selectInventory, cid, year, code).Scan(&val)
	if err != nil && err != sql.ErrNoRows {
		return 0, err
	}
	return val, nil
}
func (r Report) accountsAverage(company string, year int) (map[uint32]float32, error) {
	companies, _, err := r.fromSector(company)
	if len(companies) <= 1 || err != nil {
		err = errors.Wrap(err, "erro ao ler arquivo de setores "+r.yamlFile)
		return nil, err
	}
	if len(companies) == 0 {
		err = errors.Errorf("erro ao procurar empresas")
		return nil, err
	}
	cids := make([]string, len(companies))
	for i, co := range companies {
		if id, err := r.getCid(co); err == nil {
			cids[i] = strconv.Itoa(id)
		}
	}
	selectReport := fmt.Sprintf(`
	SELECT
		CODE, AVG(VL_CONTA)
	FROM
		dfp a
	WHERE
		ID_CIA IN (%s)
		AND YEAR = "%d"
		AND VERSAO = (SELECT MAX(VERSAO) FROM dfp WHERE ID_CIA = a.ID_CIA AND YEAR = a.YEAR)
	GROUP BY
		CODE;
	`, strings.Join(cids, ","), year)
	rows, err := r.db.Query(selectReport)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	values := make(map[uint32]float32)
	for rows.Next() {
		var code uint32
		var vlConta float32
		err := rows.Scan(
			&code,
			&vlConta,
		)
		if err == nil {
			values[code] = vlConta
		}
	}
	var err1, err2 error
	values[parsers.EstoqueMedio], err1 = r.movingAvg(cids, year, parsers.Estoque)
	values[parsers.EquityAvg], err2 = r.movingAvg(cids, year, parsers.Equity)
	if err1 == nil && err2 == nil {
		_ = r.sharesAvg(cids, year, values)
	}
	return values, nil
}
func (r Report) movingAvg(cids []string, year int, code uint32) (float32, error) {
	s := fmt.Sprintf(`
		SELECT  
			(SELECT AVG(VL_CONTA) FROM dfp d2 
			WHERE d1.ID_CIA = d2.ID_CIA AND d2.CODE = d1.CODE 
			AND d2.YEAR >= (d1.YEAR - 1) AND d2.YEAR <= d1.YEAR
			AND VERSAO = (SELECT MAX(VERSAO) FROM dfp WHERE ID_CIA = d2.ID_CIA AND YEAR = d2.YEAR)
			) AS MAVG
		FROM dfp d1
		WHERE ID_CIA IN (%s) 
		AND YEAR = $1
		AND CODE = $2
		AND VERSAO = (SELECT MAX(VERSAO) FROM dfp WHERE ID_CIA = d1.ID_CIA AND YEAR = d1.YEAR)
		GROUP BY YEAR; 
	`, strings.Join(cids, ","))
	mavg := float32(0)
	err := r.db.QueryRow(s, year, code).Scan(&mavg)
	if err != nil && err != sql.ErrNoRows {
		return 0, err
	}
	return mavg, nil
}
func (r Report) fromSector(company string) (companies []string, sectorName string, err error) {
	secCo, secName, err := parsers.FromSector(company, r.yamlFile)
	if len(secCo) <= 1 || err != nil {
		err = errors.Wrap(err, "erro ao ler arquivo dos setores "+r.yamlFile)
		return
	}
	list, err := ListCompanies(r.db)
	if err != nil {
		err = errors.Wrap(err, "erro ao listar empresas")
		return
	}
	for _, s := range secCo {
		z := parsers.FuzzyFind(s, list, 3)
		if len(z) > 0 {
			companies = append(companies, z)
		}
	}
	return removeDuplicates(companies), secName, nil
}
type CompanyInfo struct {
	id   int
	name string
}