package db

func (d *DB) SearchCompanies(query string, limit int) ([]Company, error) {
	rows, err := d.Conn.Query(`
		SELECT c.id, COALESCE(c.ticker,''), c.name, c.cnpj, COALESCE(c.cvm_code,''), COALESCE(c.sector,''), c.status, c.created_at, c.updated_at
		FROM companies_fts f JOIN companies c ON f.rowid = c.id
		WHERE companies_fts MATCH ? LIMIT ?`, query, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Company
	for rows.Next() {
		var c Company
		rows.Scan(&c.ID, &c.Ticker, &c.Name, &c.CNPJ, &c.CVMCode, &c.Sector, &c.Status, &c.CreatedAt, &c.UpdatedAt)
		out = append(out, c)
	}
	return out, nil
}

func (d *DB) SearchFilings(query string, limit int) ([]Filing, error) {
	rows, err := d.Conn.Query(`
		SELECT fi.id, fi.external_id, fi.company_id, fi.cnpj, fi.category, COALESCE(fi.type,''), COALESCE(fi.species,''),
			COALESCE(fi.subject,''), COALESCE(fi.reference_date,''), fi.delivery_date, COALESCE(fi.protocol,''),
			COALESCE(fi.version,''), COALESCE(fi.download_url,''), fi.importance, fi.created_at
		FROM filings_fts f JOIN filings fi ON f.rowid = fi.id
		WHERE filings_fts MATCH ? LIMIT ?`, query, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Filing
	for rows.Next() {
		var f Filing
		rows.Scan(&f.ID, &f.ExternalID, &f.CompanyID, &f.CNPJ, &f.Category, &f.Type, &f.Species,
			&f.Subject, &f.ReferenceDate, &f.DeliveryDate, &f.Protocol, &f.Version, &f.DownloadURL, &f.Importance, &f.CreatedAt)
		out = append(out, f)
	}
	return out, nil
}