database.cpp

#include "database.h"
#include <QSqlQuery>
#include <QSqlError>
#include <QVariant>
#include <QMap>
#include <QtDebug>
#include <cmath>

Database& Database::instance() {
    static Database inst;
    return inst;
}

bool Database::init(const QString& path) {
    m_db = QSqlDatabase::addDatabase("QSQLITE");
    m_db.setDatabaseName(path);
    if (!m_db.open()) {
        m_last_error = m_db.lastError().text();
        return false;
    }
    QSqlQuery q;
    q.exec("PRAGMA foreign_keys = ON");
    q.exec("PRAGMA journal_mode = WAL");
    return createTables();
}

QString Database::lastError() const { return m_last_error; }

bool Database::createTables() {
    QSqlQuery q;
    auto exec = [&](const QString& sql) {
        if (!q.exec(sql)) {
            m_last_error = q.lastError().text();
            qWarning() << "SQL error:" << m_last_error << "\nQuery:" << sql;
            return false;
        }
        return true;
    };

    return exec(R"(
        CREATE TABLE IF NOT EXISTS settings (
            key   TEXT PRIMARY KEY,
            value TEXT NOT NULL
        )
    )") && exec(R"(
        CREATE TABLE IF NOT EXISTS items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            is_enchanted INTEGER NOT NULL DEFAULT 0,
            enchantments TEXT NOT NULL DEFAULT '',
            base_item_id INTEGER DEFAULT NULL REFERENCES items(id) ON DELETE SET NULL,
            is_composite INTEGER NOT NULL DEFAULT 0,
            preferred_price_source TEXT NOT NULL DEFAULT 'market',
            notes TEXT NOT NULL DEFAULT ''
        )
    )") && exec(R"(
        CREATE TABLE IF NOT EXISTS item_recipes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            item_id INTEGER NOT NULL REFERENCES items(id) ON DELETE CASCADE,
            component_id INTEGER NOT NULL REFERENCES items(id),
            quantity REAL NOT NULL
        )
    )") && exec(R"(
        CREATE TABLE IF NOT EXISTS market_prices (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            item_id INTEGER NOT NULL UNIQUE REFERENCES items(id) ON DELETE CASCADE,
            price REAL NOT NULL,
            updated_at TEXT NOT NULL DEFAULT (datetime('now')),
            notes TEXT NOT NULL DEFAULT ''
        )
    )") && exec(R"(
        CREATE TABLE IF NOT EXISTS equipment_types (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            item_id INTEGER DEFAULT NULL REFERENCES items(id) ON DELETE SET NULL,
            total_uses INTEGER NOT NULL DEFAULT 25,
            notes TEXT NOT NULL DEFAULT ''
        )
    )") && exec(R"(
        CREATE TABLE IF NOT EXISTS techniques (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            description TEXT NOT NULL DEFAULT ''
        )
    )") && exec(R"(
        CREATE TABLE IF NOT EXISTS sessions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            technique_id INTEGER NOT NULL REFERENCES techniques(id) ON DELETE CASCADE,
            duration_seconds INTEGER NOT NULL,
            session_date TEXT NOT NULL DEFAULT (datetime('now')),
            notes TEXT NOT NULL DEFAULT ''
        )
    )") && exec(R"(
        CREATE TABLE IF NOT EXISTS session_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            session_id INTEGER NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
            item_id INTEGER NOT NULL REFERENCES items(id),
            quantity REAL NOT NULL,
            is_expense INTEGER NOT NULL DEFAULT 0
        )
    )") && exec(R"(
        CREATE TABLE IF NOT EXISTS session_equipment (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            session_id INTEGER NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
            equipment_type_id INTEGER NOT NULL REFERENCES equipment_types(id),
            uses INTEGER NOT NULL DEFAULT 1
        )
    )") && exec(R"(
        CREATE TABLE IF NOT EXISTS own_prices (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            item_id INTEGER NOT NULL UNIQUE REFERENCES items(id) ON DELETE CASCADE,
            technique_id INTEGER NOT NULL REFERENCES techniques(id) ON DELETE CASCADE,
            price REAL NOT NULL,
            calculated_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    )");
}

// ---- Items ----

static Item itemFromQuery(const QSqlQuery& q) {
    Item it;
    it.id           = q.value("id").toInt();
    it.name         = q.value("name").toString();
    it.is_enchanted = q.value("is_enchanted").toBool();
    it.enchantments = q.value("enchantments").toString();
    it.base_item_id = q.value("base_item_id").isNull() ? -1 : q.value("base_item_id").toInt();
    it.is_composite = q.value("is_composite").toBool();
    it.preferred_price_source = priceSourceFromString(q.value("preferred_price_source").toString());
    it.notes        = q.value("notes").toString();
    return it;
}

QList<Item> Database::getItems() {
    QList<Item> list;
    QSqlQuery q("SELECT * FROM items ORDER BY name");
    while (q.next()) list << itemFromQuery(q);
    return list;
}

Item Database::getItem(int id) {
    QSqlQuery q;
    q.prepare("SELECT * FROM items WHERE id = ?");
    q.addBindValue(id);
    q.exec();
    if (q.next()) return itemFromQuery(q);
    return {};
}

int Database::addItem(const Item& item) {
    QSqlQuery q;
    q.prepare("INSERT INTO items (name,is_enchanted,enchantments,base_item_id,is_composite,preferred_price_source,notes)"
              " VALUES (?,?,?,?,?,?,?)");
    q.addBindValue(item.name);
    q.addBindValue(item.is_enchanted ? 1 : 0);
    q.addBindValue(item.enchantments);
    q.addBindValue(item.base_item_id < 0 ? QVariant() : item.base_item_id);
    q.addBindValue(item.is_composite ? 1 : 0);
    q.addBindValue(priceSourceToString(item.preferred_price_source));
    q.addBindValue(item.notes);
    if (!q.exec()) { m_last_error = q.lastError().text(); return -1; }
    return q.lastInsertId().toInt();
}

bool Database::updateItem(const Item& item) {
    QSqlQuery q;
    q.prepare("UPDATE items SET name=?,is_enchanted=?,enchantments=?,base_item_id=?,is_composite=?,"
              "preferred_price_source=?,notes=? WHERE id=?");
    q.addBindValue(item.name);
    q.addBindValue(item.is_enchanted ? 1 : 0);
    q.addBindValue(item.enchantments);
    q.addBindValue(item.base_item_id < 0 ? QVariant() : item.base_item_id);
    q.addBindValue(item.is_composite ? 1 : 0);
    q.addBindValue(priceSourceToString(item.preferred_price_source));
    q.addBindValue(item.notes);
    q.addBindValue(item.id);
    if (!q.exec()) { m_last_error = q.lastError().text(); return false; }
    return true;
}

bool Database::deleteItem(int id) {
    QSqlQuery q;
    q.prepare("DELETE FROM items WHERE id=?");
    q.addBindValue(id);
    if (!q.exec()) { m_last_error = q.lastError().text(); return false; }
    return true;
}

// ---- Item recipes ----

QList<ItemRecipe> Database::getRecipes(int item_id) {
    QList<ItemRecipe> list;
    QSqlQuery q;
    q.prepare("SELECT * FROM item_recipes WHERE item_id=?");
    q.addBindValue(item_id);
    q.exec();
    while (q.next()) {
        ItemRecipe r;
        r.id           = q.value("id").toInt();
        r.item_id      = q.value("item_id").toInt();
        r.component_id = q.value("component_id").toInt();
        r.quantity     = q.value("quantity").toDouble();
        list << r;
    }
    return list;
}

bool Database::setRecipes(int item_id, const QList<ItemRecipe>& recipes) {
    QSqlQuery q;
    q.prepare("DELETE FROM item_recipes WHERE item_id=?");
    q.addBindValue(item_id);
    if (!q.exec()) { m_last_error = q.lastError().text(); return false; }
    for (const auto& r : recipes) {
        q.prepare("INSERT INTO item_recipes (item_id,component_id,quantity) VALUES (?,?,?)");
        q.addBindValue(item_id);
        q.addBindValue(r.component_id);
        q.addBindValue(r.quantity);
        if (!q.exec()) { m_last_error = q.lastError().text(); return false; }
    }
    return true;
}

// ---- Market prices ----

QList<MarketPrice> Database::getMarketPrices() {
    QList<MarketPrice> list;
    QSqlQuery q("SELECT * FROM market_prices ORDER BY updated_at DESC");
    while (q.next()) {
        MarketPrice mp;
        mp.id         = q.value("id").toInt();
        mp.item_id    = q.value("item_id").toInt();
        mp.price      = q.value("price").toDouble();
        mp.updated_at = q.value("updated_at").toString();
        mp.notes      = q.value("notes").toString();
        list << mp;
    }
    return list;
}

double Database::getMarketPrice(int item_id) {
    QSqlQuery q;
    q.prepare("SELECT price FROM market_prices WHERE item_id=?");
    q.addBindValue(item_id);
    q.exec();
    if (q.next()) return q.value(0).toDouble();
    return -1.0;
}

bool Database::setMarketPrice(int item_id, double price, const QString& notes) {
    QSqlQuery q;
    q.prepare("INSERT INTO market_prices (item_id,price,notes,updated_at)"
              " VALUES (?,?,?,datetime('now'))"
              " ON CONFLICT(item_id) DO UPDATE SET price=excluded.price,"
              " notes=excluded.notes, updated_at=excluded.updated_at");
    q.addBindValue(item_id);
    q.addBindValue(price);
    q.addBindValue(notes);
    if (!q.exec()) { m_last_error = q.lastError().text(); return false; }
    return true;
}

bool Database::deleteMarketPrice(int id) {
    QSqlQuery q;
    q.prepare("DELETE FROM market_prices WHERE id=?");
    q.addBindValue(id);
    if (!q.exec()) { m_last_error = q.lastError().text(); return false; }
    return true;
}

// ---- Equipment types ----

QList<EquipmentType> Database::getEquipmentTypes() {
    QList<EquipmentType> list;
    QSqlQuery q("SELECT * FROM equipment_types ORDER BY name");
    while (q.next()) {
        EquipmentType eq;
        eq.id         = q.value("id").toInt();
        eq.name       = q.value("name").toString();
        eq.item_id    = q.value("item_id").isNull() ? -1 : q.value("item_id").toInt();
        eq.total_uses = q.value("total_uses").toInt();
        eq.notes      = q.value("notes").toString();
        list << eq;
    }
    return list;
}

int Database::addEquipmentType(const EquipmentType& eq) {
    QSqlQuery q;
    q.prepare("INSERT INTO equipment_types (name,item_id,total_uses,notes) VALUES (?,?,?,?)");
    q.addBindValue(eq.name);
    q.addBindValue(eq.item_id < 0 ? QVariant() : eq.item_id);
    q.addBindValue(eq.total_uses);
    q.addBindValue(eq.notes);
    if (!q.exec()) { m_last_error = q.lastError().text(); return -1; }
    return q.lastInsertId().toInt();
}

bool Database::updateEquipmentType(const EquipmentType& eq) {
    QSqlQuery q;
    q.prepare("UPDATE equipment_types SET name=?,item_id=?,total_uses=?,notes=? WHERE id=?");
    q.addBindValue(eq.name);
    q.addBindValue(eq.item_id < 0 ? QVariant() : eq.item_id);
    q.addBindValue(eq.total_uses);
    q.addBindValue(eq.notes);
    q.addBindValue(eq.id);
    if (!q.exec()) { m_last_error = q.lastError().text(); return false; }
    return true;
}

bool Database::deleteEquipmentType(int id) {
    QSqlQuery q;
    q.prepare("DELETE FROM equipment_types WHERE id=?");
    q.addBindValue(id);
    if (!q.exec()) { m_last_error = q.lastError().text(); return false; }
    return true;
}

// ---- Techniques ----

QList<Technique> Database::getTechniques() {
    QList<Technique> list;
    QSqlQuery q("SELECT id,name,description FROM techniques ORDER BY name");
    while (q.next()) {
        Technique t;
        t.id          = q.value("id").toInt();
        t.name        = q.value("name").toString();
        t.description = q.value("description").toString();
        list << t;
    }
    return list;
}

Technique Database::getTechnique(int id) {
    QSqlQuery q;
    q.prepare("SELECT id,name,description FROM techniques WHERE id=?");
    q.addBindValue(id);
    q.exec();
    if (q.next()) {
        Technique t;
        t.id          = q.value("id").toInt();
        t.name        = q.value("name").toString();
        t.description = q.value("description").toString();
        return t;
    }
    return {};
}

int Database::addTechnique(const Technique& t) {
    QSqlQuery q;
    q.prepare("INSERT INTO techniques (name,description) VALUES (?,?)");
    q.addBindValue(t.name);
    q.addBindValue(t.description);
    if (!q.exec()) { m_last_error = q.lastError().text(); return -1; }
    return q.lastInsertId().toInt();
}

bool Database::updateTechnique(const Technique& t) {
    QSqlQuery q;
    q.prepare("UPDATE techniques SET name=?,description=? WHERE id=?");
    q.addBindValue(t.name);
    q.addBindValue(t.description);
    q.addBindValue(t.id);
    if (!q.exec()) { m_last_error = q.lastError().text(); return false; }
    return true;
}

bool Database::deleteTechnique(int id) {
    QSqlQuery q;
    q.prepare("DELETE FROM techniques WHERE id=?");
    q.addBindValue(id);
    if (!q.exec()) { m_last_error = q.lastError().text(); return false; }
    return true;
}

// ---- Global settings ----

double Database::getMelt() {
    QSqlQuery q("SELECT value FROM settings WHERE key='melt'");
    if (q.next()) return q.value(0).toString().toDouble();
    return 0.0;
}

bool Database::setMelt(double value) {
    QSqlQuery q;
    q.prepare("INSERT INTO settings (key,value) VALUES ('melt',?)"
              " ON CONFLICT(key) DO UPDATE SET value=excluded.value");
    q.addBindValue(QString::number(value, 'f', 10));
    if (!q.exec()) { m_last_error = q.lastError().text(); return false; }
    return true;
}

// ---- Sessions ----

static Session sessionFromQuery(const QSqlQuery& q) {
    Session s;
    s.id               = q.value("id").toInt();
    s.technique_id     = q.value("technique_id").toInt();
    s.duration_seconds = q.value("duration_seconds").toInt();
    s.session_date     = q.value("session_date").toString();
    s.notes            = q.value("notes").toString();
    return s;
}

QList<Session> Database::getSessions(int technique_id) {
    QList<Session> list;
    QSqlQuery q;
    if (technique_id < 0) {
        q.exec("SELECT * FROM sessions ORDER BY session_date DESC");
    } else {
        q.prepare("SELECT * FROM sessions WHERE technique_id=? ORDER BY session_date DESC");
        q.addBindValue(technique_id);
        q.exec();
    }
    while (q.next()) list << sessionFromQuery(q);
    return list;
}

Session Database::getSession(int id) {
    QSqlQuery q;
    q.prepare("SELECT * FROM sessions WHERE id=?");
    q.addBindValue(id);
    q.exec();
    if (q.next()) return sessionFromQuery(q);
    return {};
}

int Database::addSession(const Session& s) {
    QSqlQuery q;
    q.prepare("INSERT INTO sessions (technique_id,duration_seconds,session_date,notes) VALUES (?,?,?,?)");
    q.addBindValue(s.technique_id);
    q.addBindValue(s.duration_seconds);
    q.addBindValue(s.session_date.isEmpty() ? QVariant() : s.session_date);
    q.addBindValue(s.notes);
    if (!q.exec()) { m_last_error = q.lastError().text(); return -1; }
    return q.lastInsertId().toInt();
}

bool Database::updateSession(const Session& s) {
    QSqlQuery q;
    q.prepare("UPDATE sessions SET technique_id=?,duration_seconds=?,session_date=?,notes=? WHERE id=?");
    q.addBindValue(s.technique_id);
    q.addBindValue(s.duration_seconds);
    q.addBindValue(s.session_date);
    q.addBindValue(s.notes);
    q.addBindValue(s.id);
    if (!q.exec()) { m_last_error = q.lastError().text(); return false; }
    return true;
}

bool Database::deleteSession(int id) {
    QSqlQuery q;
    q.prepare("DELETE FROM sessions WHERE id=?");
    q.addBindValue(id);
    if (!q.exec()) { m_last_error = q.lastError().text(); return false; }
    return true;
}

// ---- Session items ----

QList<SessionItem> Database::getSessionItems(int session_id) {
    QList<SessionItem> list;
    QSqlQuery q;
    q.prepare("SELECT * FROM session_items WHERE session_id=?");
    q.addBindValue(session_id);
    q.exec();
    while (q.next()) {
        SessionItem si;
        si.id         = q.value("id").toInt();
        si.session_id = q.value("session_id").toInt();
        si.item_id    = q.value("item_id").toInt();
        si.quantity   = q.value("quantity").toDouble();
        si.is_expense = q.value("is_expense").toBool();
        list << si;
    }
    return list;
}

bool Database::setSessionItems(int session_id, const QList<SessionItem>& items) {
    QSqlQuery q;
    q.prepare("DELETE FROM session_items WHERE session_id=?");
    q.addBindValue(session_id);
    if (!q.exec()) { m_last_error = q.lastError().text(); return false; }
    for (const auto& si : items) {
        q.prepare("INSERT INTO session_items (session_id,item_id,quantity,is_expense) VALUES (?,?,?,?)");
        q.addBindValue(session_id);
        q.addBindValue(si.item_id);
        q.addBindValue(si.quantity);
        q.addBindValue(si.is_expense ? 1 : 0);
        if (!q.exec()) { m_last_error = q.lastError().text(); return false; }
    }
    return true;
}

// ---- Session equipment ----

QList<SessionEquipment> Database::getSessionEquipment(int session_id) {
    QList<SessionEquipment> list;
    QSqlQuery q;
    q.prepare("SELECT * FROM session_equipment WHERE session_id=?");
    q.addBindValue(session_id);
    q.exec();
    while (q.next()) {
        SessionEquipment se;
        se.id                = q.value("id").toInt();
        se.session_id        = q.value("session_id").toInt();
        se.equipment_type_id = q.value("equipment_type_id").toInt();
        se.uses              = q.value("uses").toInt();
        list << se;
    }
    return list;
}

bool Database::setSessionEquipment(int session_id, const QList<SessionEquipment>& equips) {
    QSqlQuery q;
    q.prepare("DELETE FROM session_equipment WHERE session_id=?");
    q.addBindValue(session_id);
    if (!q.exec()) { m_last_error = q.lastError().text(); return false; }
    for (const auto& se : equips) {
        q.prepare("INSERT INTO session_equipment (session_id,equipment_type_id,uses) VALUES (?,?,?)");
        q.addBindValue(session_id);
        q.addBindValue(se.equipment_type_id);
        q.addBindValue(se.uses);
        if (!q.exec()) { m_last_error = q.lastError().text(); return false; }
    }
    return true;
}

// ---- Own prices ----

QList<OwnPrice> Database::getOwnPrices() {
    QList<OwnPrice> list;
    QSqlQuery q("SELECT * FROM own_prices");
    while (q.next()) {
        OwnPrice op;
        op.id           = q.value("id").toInt();
        op.item_id      = q.value("item_id").toInt();
        op.technique_id = q.value("technique_id").toInt();
        op.price        = q.value("price").toDouble();
        op.calculated_at= q.value("calculated_at").toString();
        list << op;
    }
    return list;
}

double Database::getOwnPrice(int item_id, int exclude_technique_id) {
    QSqlQuery q;
    if (exclude_technique_id >= 0) {
        q.prepare("SELECT price FROM own_prices WHERE item_id=? AND technique_id != ?");
        q.addBindValue(item_id);
        q.addBindValue(exclude_technique_id);
    } else {
        q.prepare("SELECT price FROM own_prices WHERE item_id=?");
        q.addBindValue(item_id);
    }
    q.exec();
    if (q.next()) return q.value(0).toDouble();
    return -1.0;
}

bool Database::setOwnPrice(int item_id, int technique_id, double price) {
    QSqlQuery q;
    q.prepare("INSERT INTO own_prices (item_id,technique_id,price,calculated_at)"
              " VALUES (?,?,?,datetime('now'))"
              " ON CONFLICT(item_id) DO UPDATE SET price=excluded.price,"
              " technique_id=excluded.technique_id, calculated_at=excluded.calculated_at");
    q.addBindValue(item_id);
    q.addBindValue(technique_id);
    q.addBindValue(price);
    if (!q.exec()) { m_last_error = q.lastError().text(); return false; }
    return true;
}

QList<SellableItem> Database::getSellableItems() {
    QList<SellableItem> list;
    // Só retorna itens onde preço próprio <= preço de mercado (ou sem preço de mercado)
    QSqlQuery q(
        "SELECT op.item_id, i.name AS item_name, op.technique_id, t.name AS technique_name, op.price AS own_price "
        "FROM own_prices op "
        "JOIN items i ON i.id = op.item_id "
        "JOIN techniques t ON t.id = op.technique_id "
        "LEFT JOIN market_prices mp ON mp.item_id = op.item_id "
        "WHERE mp.price IS NULL OR op.price <= mp.price "
        "ORDER BY i.name"
    );
    while (q.next()) {
        SellableItem si;
        si.item_id        = q.value("item_id").toInt();
        si.item_name      = q.value("item_name").toString();
        si.technique_id   = q.value("technique_id").toInt();
        si.technique_name = q.value("technique_name").toString();
        si.own_price      = q.value("own_price").toDouble();
        si.market_price   = getMarketPrice(si.item_id);
        list << si;
    }
    return list;
}

bool Database::deleteOwnPrice(int item_id) {
    QSqlQuery q;
    q.prepare("DELETE FROM own_prices WHERE item_id=?");
    q.addBindValue(item_id);
    if (!q.exec()) { m_last_error = q.lastError().text(); return false; }
    return true;
}

bool Database::deleteOwnPricesByTechnique(int technique_id) {
    QSqlQuery q;
    q.prepare("DELETE FROM own_prices WHERE technique_id=?");
    q.addBindValue(technique_id);
    if (!q.exec()) { m_last_error = q.lastError().text(); return false; }
    return true;
}

// ---- Calculations ----

double Database::getRecipePrice(int item_id, int depth, int exclude_technique_id) {
    if (depth > 10) return -1.0;  // evitar ciclo infinito
    auto recipes = getRecipes(item_id);
    if (recipes.isEmpty()) return -1.0;
    double total = 0.0;
    for (const auto& r : recipes) {
        double p = getEffectivePrice(r.component_id, exclude_technique_id);
        if (p < 0) return -1.0;  // componente sem preço
        total += p * r.quantity;
    }
    return total;
}

double Database::getEffectivePrice(int item_id, int exclude_technique_id) {
    Item item = getItem(item_id);
    if (item.id < 0) return -1.0;

    switch (item.preferred_price_source) {
    case PriceSource::Own: {
        double p = getOwnPrice(item_id, exclude_technique_id);
        if (p >= 0) return p;
        // fallback: mercado
        p = getMarketPrice(item_id);
        if (p >= 0) return p;
        return getRecipePrice(item_id, 0, exclude_technique_id);
    }
    case PriceSource::Recipe: {
        double p = getRecipePrice(item_id, 0, exclude_technique_id);
        if (p >= 0) return p;
        return getMarketPrice(item_id);
    }
    default: // Market
        double p = getMarketPrice(item_id);
        if (p >= 0) return p;
        // Se é enchanted, tenta base
        if (item.is_enchanted && item.base_item_id >= 0)
            p = getMarketPrice(item.base_item_id);
        if (p >= 0) return p;
        // fallback: receita
        return getRecipePrice(item_id, 0, exclude_technique_id);
    }
}

AverageSession Database::calculateAverageSession(int technique_id) {
    AverageSession avg;
    avg.technique_id = technique_id;

    auto sessions = getSessions(technique_id);
    int n = sessions.size();
    if (n == 0) return avg;
    avg.session_count = n;

    // Soma dos tempos
    double total_seconds = 0;
    for (const auto& s : sessions) total_seconds += s.duration_seconds;
    avg.avg_duration_sec = total_seconds / n;

    // Acumula items por (item_id, is_expense)
    QMap<QPair<int,bool>, double> item_totals;
    for (const auto& s : sessions) {
        for (const auto& si : getSessionItems(s.id)) {
            item_totals[{si.item_id, si.is_expense}] += si.quantity;
        }
    }

    // Monta AvgSessionItem
    for (auto it = item_totals.begin(); it != item_totals.end(); ++it) {
        AvgSessionItem asi;
        asi.item_id      = it.key().first;
        asi.is_expense   = it.key().second;
        asi.avg_quantity = it.value() / n;
        asi.item_name    = getItem(asi.item_id).name;
        avg.items << asi;
    }

    // Acumula equipment por equipment_type_id
    QMap<int, double> equip_totals;
    for (const auto& s : sessions) {
        for (const auto& se : getSessionEquipment(s.id)) {
            equip_totals[se.equipment_type_id] += se.uses;
        }
    }
    auto equip_types = getEquipmentTypes();
    QMap<int, EquipmentType> equip_map;
    for (const auto& eq : equip_types) equip_map[eq.id] = eq;

    for (auto it = equip_totals.begin(); it != equip_totals.end(); ++it) {
        AvgEquipmentUse aeu;
        aeu.equipment_type_id = it.key();
        aeu.avg_uses          = it.value() / n;
        auto& eq = equip_map[it.key()];
        aeu.equipment_name    = eq.name;
        aeu.equipment_item_id = eq.item_id;
        avg.equipment_uses << aeu;
    }

    return avg;
}

PriceCalculationResult Database::calculatePrice(const AverageSession& avg) {
    PriceCalculationResult result;
    if (avg.technique_id < 0 || avg.session_count == 0) return result;

    // Valor total = (tempo em horas) * melt global
    double avg_hours = avg.avg_duration_sec / 3600.0;
    result.total_value = avg_hours * getMelt();

    const int excl = avg.technique_id;  // ignora preços próprios desta mesma técnica

    // Custo de depreciação de equipamentos
    for (const auto& aeu : avg.equipment_uses) {
        if (aeu.equipment_item_id < 0) continue;
        double eq_price = getEffectivePrice(aeu.equipment_item_id, excl);
        if (eq_price < 0) continue;
        // Custo = (preço / usos_totais) * usos_médios
        // Precisa do total_uses do equipment type
        QSqlQuery q;
        q.prepare("SELECT total_uses FROM equipment_types WHERE id=?");
        q.addBindValue(aeu.equipment_type_id);
        q.exec();
        if (!q.next()) continue;
        int total_uses = q.value(0).toInt();
        if (total_uses <= 0) continue;
        double depr = (eq_price / total_uses) * aeu.avg_uses;
        result.depreciation_cost += depr;
    }

    // Custo das despesas
    for (const auto& asi : avg.items) {
        if (!asi.is_expense) continue;
        double price = getEffectivePrice(asi.item_id, excl);
        if (price < 0) price = 0.0;
        ItemPriceResult ipr;
        ipr.item_id       = asi.item_id;
        ipr.item_name     = asi.item_name;
        ipr.quantity      = asi.avg_quantity;
        ipr.price_per_unit= price;
        ipr.total_price   = price * asi.avg_quantity;
        ipr.capped_at_market = false;
        ipr.source_used   = getItem(asi.item_id).preferred_price_source;
        result.total_expense_cost += ipr.total_price;
        result.expense_items << ipr;
    }

    result.remaining_value = result.total_value
                           - result.total_expense_cost
                           - result.depreciation_cost;

    // Rateio proporcional com saturação para os itens de ganho
    struct FreeItem {
        int    idx;
        double quantity;
        double market_price_max;
    };

    QList<FreeItem> free_items;
    QList<AvgSessionItem> income_items;
    for (const auto& asi : avg.items) {
        if (asi.is_expense) continue;
        income_items << asi;
    }

    // Inicializa ItemPriceResult para todos os ganhos
    QList<ItemPriceResult> income_results;
    for (int i = 0; i < income_items.size(); ++i) {
        const auto& asi = income_items[i];
        double mkt = getEffectivePrice(asi.item_id, excl);

        ItemPriceResult ipr;
        ipr.item_id         = asi.item_id;
        ipr.item_name       = asi.item_name;
        ipr.quantity        = asi.avg_quantity;
        ipr.price_per_unit  = 0.0;
        ipr.total_price     = 0.0;
        ipr.capped_at_market= false;
        ipr.source_used     = getItem(asi.item_id).preferred_price_source;
        income_results << ipr;

        if (mkt >= 0 && asi.avg_quantity > 0) {
            free_items << FreeItem{i, asi.avg_quantity, mkt};
        }
    }

    // Loop de rateio proporcional com saturação
    double value_remaining = result.remaining_value;
    bool changed = true;
    while (changed && !free_items.isEmpty()) {
        changed = false;
        double soma_q = 0;
        for (const auto& fi : free_items) soma_q += fi.quantity;
        if (soma_q <= 0) break;

        QList<FreeItem> still_free;
        for (const auto& fi : free_items) {
            double p_provisional = (fi.quantity / soma_q) * value_remaining;
            double p_per_unit    = (fi.quantity > 0) ? p_provisional / fi.quantity : 0;
            if (p_per_unit > fi.market_price_max) {
                // Satura no preço de mercado
                income_results[fi.idx].price_per_unit  = fi.market_price_max;
                income_results[fi.idx].total_price     = fi.market_price_max * fi.quantity;
                income_results[fi.idx].capped_at_market= true;
                value_remaining -= income_results[fi.idx].total_price;
                changed = true;
            } else {
                still_free << fi;
            }
        }
        free_items = still_free;
    }
    // Distribui o restante entre os livres não saturados
    if (!free_items.isEmpty()) {
      result.can_produce = true;
      double soma_q = 0;
      for (const auto& fi : free_items) soma_q += fi.quantity;
      for (const auto& fi : free_items) {
	double p_total   = (soma_q > 0) ? (fi.quantity / soma_q) * value_remaining : 0;
	double p_per_unit= (fi.quantity > 0) ? p_total / fi.quantity : 0;
	income_results[fi.idx].price_per_unit  = p_per_unit;
	income_results[fi.idx].total_price     = p_total;
	income_results[fi.idx].capped_at_market= false;
      }
    }

    result.income_items = income_results;
    return result;
}