/* © SRSoftware 2025 */
package de.srsoftware.umbrella.stock;

import static de.srsoftware.tools.jdbc.Query.SelectQuery.ALL;
import static de.srsoftware.tools.jdbc.Query.select;
import static de.srsoftware.umbrella.core.ConnectionProvider.connect;
import static de.srsoftware.umbrella.core.ModuleRegistry.companyService;
import static de.srsoftware.umbrella.core.ModuleRegistry.translator;
import static de.srsoftware.umbrella.core.constants.Field.*;
import static de.srsoftware.umbrella.core.constants.Text.UNIT;
import static de.srsoftware.umbrella.core.constants.Text.UNIT_PRICE;
import static de.srsoftware.umbrella.core.exceptions.UmbrellaException.databaseException;
import static de.srsoftware.umbrella.stock.Constants.TABLE_ITEMS;
import static java.lang.System.Logger.Level.DEBUG;

import de.srsoftware.tools.Tuple;
import de.srsoftware.umbrella.core.constants.Field;
import de.srsoftware.umbrella.core.constants.Text;
import de.srsoftware.umbrella.core.model.*;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;

public class ItemDb {
	private final System.Logger LOG = System.getLogger(getClass().getSimpleName());
	private final Connection db;

	public ItemDb(String dbFilePath){
		db = connect(dbFilePath);
	}

	public void migrateTo(StockDb stockDb) {
		try {
			var companyLocations = new HashMap<Long,Location>();
			var companyInfo = new HashMap<Long, Tuple<Company,String>>(); // map from companyId → (company, language)
			LOG.log(DEBUG,"Reading items:\nid: code / name / unit / price / tax rate");
			var rs = select(ALL).from(TABLE_ITEMS).exec(db);
			while (rs.next()){
				var id          = rs.getLong(ID);
				var companyId   = rs.getLong(COMPANY_ID);
				var code        = rs.getString(Field.CODE);
				var name        = rs.getString(NAME);
				var description = rs.getString(DESCRIPTION);
				var unit        = rs.getString(UNIT);
				var unitPrice   = rs.getLong(UNIT_PRICE);
				var tax         = rs.getLong(TAX);
				var tuple       = companyInfo.get(companyId);
				LOG.log(DEBUG," - read item {0}: {1} / {2} / {3} / {4} / {5} %",id,companyId,code,name,unit,unitPrice,tax);
				String lang     = null;
				Company company;
				if (tuple == null){
					LOG.log(DEBUG, "   loading company {0}:",companyId);
					company = companyService().get(companyId);
					LOG.log(DEBUG, "     → {0}",company.name());
					for (var member : companyService().getMembers(companyId)){
						lang = member.language();
						if (lang != null){
							LOG.log(DEBUG, "     → language = {0}",lang);
							tuple = Tuple.of(company,lang);
							companyInfo.put(companyId,tuple);
							break;
						}
					}
				} else {
					company = tuple.a;
					lang    = tuple.b;
					LOG.log(DEBUG, "   using company: {0} ({1})",company.name(),lang);

				}
				var location = companyLocations.get(companyId);
				if (location == null) {
					location = stockDb.save(new DbLocation(0,company,null,"virtual items",null));
					companyLocations.put(companyId,location);
				}
				LOG.log(DEBUG, "   using location: {0}",location.resolve().name());
				var stockItem = new Item(0,company,0,location,code,name,description);
				var props = stockItem.properties();
				var keyUnitPrice = translator().translate(lang, Text.UNIT_PRICE);
				var keyUnit      = translator().translate(lang, Text.UNIT);
				var keyTax       = translator().translate(lang,TAX_RATE);
				var keyLegacyId  = translator().translate(lang,"legacy_id");
				props.add(new Property(0,keyUnitPrice,unitPrice/100d,company.currency()));
				props.add(new Property(0,keyUnit,unit,null));
				props.add(new Property(0,keyTax,tax,"%"));
				props.add(new Property(0,keyLegacyId,id,null));
				LOG.log(DEBUG,"   saving item {0}:",stockItem);
				stockDb.save(stockItem);
			}
			rs.close();
		} catch (SQLException e) {
			throw databaseException("Failed to migrate items from ItemDB to stockDB!");
		}
	}
}
