1 files changed, 163 insertions, 0 deletions
diff --git a/app/source/Cplt/Model/Database.cpp b/app/source/Cplt/Model/Database.cpp
new file mode 100644
index 0000000..07c6e36
--- /dev/null
+++ b/app/source/Cplt/Model/Database.cpp
@@ -0,0 +1,163 @@
+#include "Database.hpp"
+
+#include <Cplt/Model/Project.hpp>
+
+#include <filesystem>
+#include <stdexcept>
+
+namespace fs = std::filesystem;
+
+SalesTable::SalesTable(MainDatabase& db)
+	// language=SQLite
+	: GetRowCount(db.GetSQLite(), "SELECT Count(*) FROM Sales")
+	// language=SQLite
+	, GetRows(db.GetSQLite(), "SELECT * FROM Sales LIMIT ? OFFSET ?")
+	// language=SQLite
+	, GetItems(db.GetSQLite(), "SELECT * FROM SalesItems WHERE SaleId == ?")
+{
+}
+
+PurchasesTable::PurchasesTable(MainDatabase& db)
+	// language=SQLite
+	: GetRowCount(db.GetSQLite(), "SELECT Count(*) FROM Purchases")
+	// language=SQLite
+	, GetRows(db.GetSQLite(), "SELECT * FROM Purchases LIMIT ? OFFSET ?")
+	// language=SQLite
+	, GetItems(db.GetSQLite(), "SELECT * FROM PurchasesItems WHERE PurchaseId == ?")
+{
+}
+
+DeliveryTable::DeliveryTable(MainDatabase& db)
+	// language=SQLite
+	: FilterByTypeAndId(db.GetSQLite(), "SELECT * FROM Deliveries WHERE AssociatedOrder == ? AND Outgoing = ?")
+	// language=SQLite
+	, GetItems(db.GetSQLite(), "SELECT * FROM DeliveriesItems WHERE DeliveryId == ?")
+{
+}
+
+static std::string GetDatabaseFilePath(const Project& project)
+{
+	auto dbsDir = project.GetPath() / "databases";
+	fs::create_directories(dbsDir);
+
+	auto dbFile = dbsDir / "transactions.sqlite3";
+	return dbFile.string();
+}
+
+/// Wrapper for SQLite::Database that creates the default tables
+MainDatabase::DatabaseWrapper::DatabaseWrapper(MainDatabase& self)
+	: mSqlite(GetDatabaseFilePath(*self.mProject), SQLite::OPEN_READWRITE | SQLite::OPEN_CREATE)
+{
+	// If this table doesn't exist, the database probably just got initialized
+	if (mSqlite.tableExists("Sales")) {
+		return;
+	}
+
+	// 'Sales' schema
+	// - Customer: the customer item ID
+	// - Deadline: unix epoch time of order deadline
+	// - DeliveryTime: the time this order was completed (through a set of deliveries)
+
+	// 'Purchases' schema
+	// - Factory: the factory id,
+	// - OrderTime: the time this order was made
+	// - DeliveryTime: the time this order was completed (through a set of deliveries)
+
+	// 'Deliveries' schema
+	// - ShipmentTime: unix epoch time stamp of sending to delivery
+	// - ArrivalTime: unix epoch time stamp of delivery arrived at warehouse; 0 if not arrived yet
+	// - AssociatedOrder: Id of the order that this delivery is completing (which table: Outgoing=true -> Sales, Outgoing=false -> Purchases)
+	// - Outgoing: true if the delivery is from warehouse to customer; false if the delivery is from factory to warehouse
+
+	// Note: the 'Id' key would be unique (not recycled after row deletion) because it's explicit
+	// https://www.sqlite.org/rowidtable.html
+
+	// language=SQLite
+	mSqlite.exec(R"""(
+CREATE TABLE IF NOT EXISTS Sales(
+	Id INT PRIMARY KEY,
+	Customer INT,
+	Deadline DATETIME,
+	DeliveryTime DATETIME
+);
+CREATE TABLE IF NOT EXISTS SalesItems(
+	SaleId INT,
+	ItemId INT,
+	Count INT
+);
+
+CREATE TABLE IF NOT EXISTS Purchases(
+	Id INT PRIMARY KEY,
+	Factory INT,
+	OrderTime DATETIME,
+	DeliveryTime DATETIME
+);
+CREATE TABLE IF NOT EXISTS PurchasesItems(
+	PurchaseId INT,
+	ItemId INT,
+	Count INT
+);
+
+CREATE TABLE IF NOT EXISTS Deliveries(
+	Id INT PRIMARY KEY,
+	ShipmentTime DATETIME,
+	ArrivalTime DATETIME,
+	AssociatedOrder INT,
+	Outgoing BOOLEAN
+);
+CREATE TABLE IF NOT EXISTS DeliveriesItems(
+	DeliveryId INT,
+	ItemId INT,
+	Count INT
+);
+)""");
+}
+
+MainDatabase::MainDatabase(Project& project)
+	: mProject{ &project }
+	, mDbWrapper(*this)
+	, mSales(*this)
+	, mPurchases(*this)
+	, mDeliveries(*this)
+{
+}
+
+const SQLite::Database& MainDatabase::GetSQLite() const
+{
+	return mDbWrapper.mSqlite;
+}
+
+SQLite::Database& MainDatabase::GetSQLite()
+{
+	return mDbWrapper.mSqlite;
+}
+
+const SalesTable& MainDatabase::GetSales() const
+{
+	return mSales;
+}
+
+SalesTable& MainDatabase::GetSales()
+{
+	return mSales;
+}
+
+const PurchasesTable& MainDatabase::GetPurchases() const
+{
+	return mPurchases;
+}
+
+PurchasesTable& MainDatabase::GetPurchases()
+{
+	return mPurchases;
+}
+
+const DeliveryTable& MainDatabase::GetDeliveries() const
+{
+	return mDeliveries;
+}
+
+DeliveryTable& MainDatabase::GetDeliveries()
+{
+	return mDeliveries;
+}