package db

import (
	"database/sql"
	"fmt"
	"time"
)

type Company struct {
	ID        int64  `json:"id"`
	Ticker    string `json:"ticker,omitempty"`
	Name      string `json:"name"`
	CNPJ      string `json:"cnpj"`
	CVMCode   string `json:"cvm_code,omitempty"`
	Sector    string `json:"sector,omitempty"`
	Status    string `json:"status"`
	CreatedAt string `json:"created_at"`
	UpdatedAt string `json:"updated_at"`
}

func (d *DB) UpsertCompany(c *Company) error {
	_, err := d.Conn.Exec(`
		INSERT INTO companies (ticker, name, cnpj, cvm_code, sector, status, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT(cnpj) DO UPDATE SET
			ticker=excluded.ticker, name=excluded.name, cvm_code=excluded.cvm_code,
			sector=excluded.sector, status=excluded.status, updated_at=excluded.updated_at`,
		c.Ticker, c.Name, c.CNPJ, c.CVMCode, c.Sector, c.Status, time.Now().UTC().Format(time.RFC3339))
	return err
}

func (d *DB) RebuildCompaniesFTS() error {
	_, err := d.Conn.Exec(`
		INSERT INTO companies_fts(companies_fts) VALUES('rebuild')`)
	return err
}

func (d *DB) ListCompanies(limit, offset int, status, sector string) ([]Company, int, error) {
	where := "WHERE 1=1"
	args := []any{}
	if status != "" {
		where += " AND status = ?"
		args = append(args, status)
	}
	if sector != "" {
		where += " AND sector = ?"
		args = append(args, sector)
	}

	var total int
	err := d.Conn.QueryRow("SELECT COUNT(*) FROM companies "+where, args...).Scan(&total)
	if err != nil {
		return nil, 0, err
	}

	query := fmt.Sprintf("SELECT id, COALESCE(ticker,''), name, cnpj, COALESCE(cvm_code,''), COALESCE(sector,''), status, created_at, updated_at FROM companies %s ORDER BY name LIMIT ? OFFSET ?", where)
	args = append(args, limit, offset)
	rows, err := d.Conn.Query(query, args...)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	var companies []Company
	for rows.Next() {
		var c Company
		if err := rows.Scan(&c.ID, &c.Ticker, &c.Name, &c.CNPJ, &c.CVMCode, &c.Sector, &c.Status, &c.CreatedAt, &c.UpdatedAt); err != nil {
			return nil, 0, err
		}
		companies = append(companies, c)
	}
	return companies, total, nil
}

func (d *DB) GetCompany(id int64) (*Company, error) {
	c := &Company{}
	err := d.Conn.QueryRow("SELECT id, COALESCE(ticker,''), name, cnpj, COALESCE(cvm_code,''), COALESCE(sector,''), status, created_at, updated_at FROM companies WHERE id = ?", id).
		Scan(&c.ID, &c.Ticker, &c.Name, &c.CNPJ, &c.CVMCode, &c.Sector, &c.Status, &c.CreatedAt, &c.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	return c, err
}

func (d *DB) GetCompanyByCNPJ(cnpj string) (*Company, error) {
	c := &Company{}
	err := d.Conn.QueryRow("SELECT id, COALESCE(ticker,''), name, cnpj, COALESCE(cvm_code,''), COALESCE(sector,''), status, created_at, updated_at FROM companies WHERE cnpj = ?", cnpj).
		Scan(&c.ID, &c.Ticker, &c.Name, &c.CNPJ, &c.CVMCode, &c.Sector, &c.Status, &c.CreatedAt, &c.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	return c, err
}