MariaDB 10.11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DROP TABLE IF EXISTS User;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE User (
  UserId int(10) unsigned NOT NULL AUTO_INCREMENT,
  UserName varchar(100) NOT NULL,
  UserEmail varchar(100) NOT NULL,
  Coins int(10) unsigned NOT NULL DEFAULT 100000,
  JoinDate datetime NOT NULL DEFAULT current_timestamp(),
  PasswordHash varchar(100) NOT NULL,
  EmailConfirmed tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (UserId),
  UNIQUE KEY User_UNIQUE_1 (UserEmail),
  UNIQUE KEY User_UNIQUE (UserName) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table User
--

LOCK TABLES User WRITE;
/*!40000 ALTER TABLE User DISABLE KEYS */;
INSERT INTO User VALUES
(1,'123','123@web.de',100000,'2025-02-01 20:39:48','$2y$10$PcArOIkthf5HbYuDjcfdbeylc135lnoiPgLZUmy.T55igrbof4Yem',0),
(2,'qwe','qwe@web.de',100000,'2025-02-01 20:40:21','$2y$10$ybmJwoh3NdkFVnogWShP7ORHlWkPaGJ2FEaV8heuodQwCIwv78ysu',0);
/*!40000 ALTER TABLE User ENABLE KEYS */;
UNLOCK TABLES;

Records: 2  Duplicates: 0  Warnings: 0


DROP TABLE IF EXISTS Meme;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE Meme (
  MemeId int(10) unsigned NOT NULL AUTO_INCREMENT,
  MemeCreator int(10) unsigned NOT NULL,
  ShareCount int(10) unsigned NOT NULL DEFAULT 1024,
  TotalValue double NOT NULL DEFAULT 0,
  CreationDate datetime NOT NULL DEFAULT current_timestamp(),
  MemeName varchar(100) NOT NULL,
  ImageType varchar(100) NOT NULL,
  PRIMARY KEY (MemeId),
  KEY Meme_User_FK (MemeCreator),
  CONSTRAINT Meme_User_FK FOREIGN KEY (MemeCreator) REFERENCES User (UserId)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table Meme
--

LOCK TABLES Meme WRITE;
/*!40000 ALTER TABLE Meme DISABLE KEYS */;
INSERT INTO Meme VALUES
(1,1,1024,0,'2025-02-01 20:40:04','lol','jpeg');
/*!40000 ALTER TABLE Meme ENABLE KEYS */;
UNLOCK TABLES;
DROP TABLE IF EXISTS BuyOrder;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE BuyOrder (
  BuyOrderId int(10) unsigned NOT NULL AUTO_INCREMENT,
  MemeId int(10) unsigned NOT NULL,
  TotalShareAmount int(10) unsigned NOT NULL,
  SharesLeft int(10) unsigned NOT NULL,
  TotalCoinAmount int(10) unsigned NOT NULL,
  CoinsLeft int(10) unsigned NOT NULL,
  CostThreshold double NOT NULL,
  UserId int(10) unsigned NOT NULL,
  OrderTime datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (BuyOrderId),
  KEY BuyOrder_User_FK (UserId),
  KEY BuyOrder_Meme_FK (MemeId),
  CONSTRAINT BuyOrder_Meme_FK FOREIGN KEY (MemeId) REFERENCES Meme (MemeId),
  CONSTRAINT BuyOrder_User_FK FOREIGN KEY (UserId) REFERENCES User (UserId)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table BuyOrder
--

LOCK TABLES BuyOrder WRITE;
/*!40000 ALTER TABLE BuyOrder DISABLE KEYS */;
INSERT INTO BuyOrder VALUES
(1,1,100,100,100,100,1,2,'2025-02-01 20:40:30');
/*!40000 ALTER TABLE BuyOrder ENABLE KEYS */;
UNLOCK TABLES;
DROP TABLE IF EXISTS SellOrder;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE SellOrder (
  SellOrderId int(10) unsigned NOT NULL AUTO_INCREMENT,
  MemeId int(10) unsigned NOT NULL,
  TotalShareAmount int(10) unsigned NOT NULL,
  SharesLeft int(10) unsigned NOT NULL,
  TotalCoinAmount int(10) unsigned NOT NULL,
  CoinsLeft int(10) unsigned NOT NULL,
  CostThreshold double NOT NULL,
  UserId int(10) unsigned NOT NULL,
  OrderTime datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (SellOrderId),
  KEY SellOrder_User_FK (UserId),
  KEY SellOrder_Meme_FK (MemeId),
  CONSTRAINT SellOrder_Meme_FK FOREIGN KEY (MemeId) REFERENCES Meme (MemeId),
  CONSTRAINT SellOrder_User_FK FOREIGN KEY (UserId) REFERENCES User (UserId)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table SellOrder
--

LOCK TABLES SellOrder WRITE;
/*!40000 ALTER TABLE SellOrder DISABLE KEYS */;
INSERT INTO SellOrder VALUES
(1,1,100,100,100,100,1,1,'2025-02-01 20:40:10');
/*!40000 ALTER TABLE SellOrder ENABLE KEYS */;
UNLOCK TABLES;
DROP TABLE IF EXISTS Shares;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE Shares (
  UserId int(10) unsigned NOT NULL,
  MemeId int(10) unsigned NOT NULL,
  Amount int(10) unsigned NOT NULL DEFAULT 1024,
  PRIMARY KEY (UserId,MemeId),
  KEY Shares_Meme_FK (MemeId),
  CONSTRAINT Shares_Meme_FK FOREIGN KEY (MemeId) REFERENCES Meme (MemeId),
  CONSTRAINT Shares_User_FK FOREIGN KEY (UserId) REFERENCES User (UserId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table Shares
--

LOCK TABLES Shares WRITE;
/*!40000 ALTER TABLE Shares DISABLE KEYS */;
INSERT INTO Shares VALUES
(1,1,1024);
/*!40000 ALTER TABLE Shares ENABLE KEYS */;
UNLOCK TABLES;
DROP TABLE IF EXISTS Transaction;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE Transaction (
  TransactionId int(10) unsigned NOT NULL AUTO_INCREMENT,
  TransactionTime datetime NOT NULL DEFAULT current_timestamp(),
  SharesBuyer int(10) unsigned NOT NULL,
  BoughtShare int(10) unsigned NOT NULL,
  ShareAmount int(10) unsigned NOT NULL,
  SharesSeller int(10) unsigned NOT NULL,
  CoinCost int(10) unsigned NOT NULL,
  PRIMARY KEY (TransactionId),
  KEY Transaction_Meme_FK (BoughtShare),
  KEY Transaction_User_FK (SharesBuyer),
  KEY Transaction_User_FK_1 (SharesSeller),
  CONSTRAINT Transaction_Meme_FK FOREIGN KEY (BoughtShare) REFERENCES Meme (MemeId),
  CONSTRAINT Transaction_User_FK FOREIGN KEY (SharesBuyer) REFERENCES User (UserId),
  CONSTRAINT Transaction_User_FK_1 FOREIGN KEY (SharesSeller) REFERENCES User (UserId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table Transaction
--

LOCK TABLES Transaction WRITE;
/*!40000 ALTER TABLE Transaction DISABLE KEYS */;
/*!40000 ALTER TABLE Transaction ENABLE KEYS */;
UNLOCK TABLES;
CREATE PROCEDURE MatchOrders(IN meme INT UNSIGNED, OUT could_sell BOOL)
sp: BEGIN
        DECLARE buy_order_id INT UNSIGNED;
        DECLARE buy_shares_left INT UNSIGNED;
        DECLARE buy_cost_threshold DOUBLE;
        DECLARE buy_user_id INT UNSIGNED;
        DECLARE buy_coins_left INT UNSIGNED;

        DECLARE sell_order_id INT UNSIGNED;
        DECLARE sell_shares_left INT UNSIGNED;
        DECLARE sell_cost_threshold DOUBLE;
        DECLARE sell_user_id INT UNSIGNED;
        DECLARE sell_coins_left INT UNSIGNED;

        DECLARE transaction_share_amount INT UNSIGNED;
        DECLARE transaction_coin_amount INT UNSIGNED;
        


        SELECT BuyOrderId, SharesLeft, CostThreshold, UserId, CoinsLeft INTO buy_order_id, buy_shares_left, buy_cost_threshold, buy_user_id, buy_coins_left FROM BuyOrder WHERE MemeId = meme AND SharesLeft != 0 ORDER BY CostThreshold DESC, BuyOrderId ASC LIMIT 1;
        SELECT SellOrderId, SharesLeft, CostThreshold, UserId, CoinsLeft INTO sell_order_id, sell_shares_left, sell_cost_threshold, sell_user_id, sell_coins_left FROM SellOrder WHERE MemeId = meme AND SharesLeft != 0 ORDER BY CostThreshold ASC, SellOrderId ASC LIMIT 1;

        IF (buy_order_id IS NULL OR sell_order_id IS NULL) THEN
                SET could_sell = FALSE;
                LEAVE sp;
        END IF;


        IF buy_cost_threshold < sell_cost_threshold THEN
                SET could_sell = FALSE;
                LEAVE sp;
        END IF;

        IF buy_shares_left < sell_shares_left THEN
                SET transaction_share_amount = buy_shares_left;
        ELSE
                SET transaction_share_amount = sell_shares_left;
        END IF;
        SET transaction_coin_amount = CEIL((sell_coins_left / sell_shares_left) * transaction_share_amount);

        UPDATE BuyOrder SET SharesLeft = SharesLeft - transaction_share_amount, CoinsLeft = CoinsLeft - transaction_coin_amount, CostThreshold = (CoinsLeft - transaction_coin_amount) / IF(SharesLeft - transaction_share_amount = 0, 1, SharesLeft - transaction_share_amount) WHERE BuyOrderId = buy_order_id;
        UPDATE SellOrder SET SharesLeft = SharesLeft - transaction_share_amount, CoinsLeft = CoinsLeft - transaction_coin_amount, CostThreshold = (CoinsLeft - transaction_coin_amount) / IF(SharesLeft - transaction_share_amount = 0, 1, SharesLeft - transaction_share_amount) WHERE SellOrderId = sell_order_id;


        UPDATE User SET Coins = Coins - transaction_coin_amount WHERE UserId = buy_user_id;
        UPDATE User SET Coins = Coins + transaction_coin_amount WHERE UserId = sell_user_id;

        UPDATE Shares SET Amount = Amount - transaction_share_amount WHERE UserId = sell_user_id AND MemeId = meme;
        DELETE FROM Shares WHERE UserId = sell_user_id AND MemeId = meme AND Amount = 0;

        IF EXISTS (SELECT * FROM Shares WHERE UserId = buy_user_id AND MemeId = meme) THEN
        UPDATE Shares SET Amount = Amount + transaction_share_amount WHERE UserId = buy_user_id AND MemeId = meme;
        ELSE
                INSERT INTO Shares (UserId, MemeId, Amount) VALUES (buy_user_id, meme, transaction_share_amount);
        END IF;

        INSERT INTO Transaction (SharesBuyer, BoughtShare, ShareAmount, SharesSeller, CoinCost) VALUES (buy_user_id, meme, transaction_share_amount, sell_user_id, transaction_coin_amount);

        UPDATE Meme SET TotalValue = (transaction_coin_amount / transaction_share_amount) * ShareCount WHERE MemeId = meme; 

        SET could_sell = TRUE;
END