-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               10.4.32-MariaDB - mariadb.org binary distribution
-- Server OS:                    Win64
-- HeidiSQL Version:             12.8.0.6908
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


-- Dumping database structure for bank310
CREATE DATABASE IF NOT EXISTS `bank310` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;
USE `bank310`;

-- Dumping structure for table bank310.account
CREATE TABLE IF NOT EXISTS `account` (
  `AccountID` int(11) NOT NULL AUTO_INCREMENT,
  `Username` varchar(50) NOT NULL,
  `PasswordHash` varchar(255) NOT NULL,
  `Name` varchar(100) NOT NULL,
  `Surname` varchar(100) NOT NULL,
  `CreatedAt` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`AccountID`),
  UNIQUE KEY `Username` (`Username`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table bank310.account: ~3 rows (approximately)
INSERT INTO `account` (`AccountID`, `Username`, `PasswordHash`, `Name`, `Surname`, `CreatedAt`) VALUES
	(4, 'hogz', '$2b$10$j39CxD0DM3q8ViFS/SKIou3iE0HD1jQm.4yVgvtU82hRybbYVBSN.', 'Vladislavs', 'Balajs', '2025-10-21 15:10:57'),
	(6, 'Test', '$2b$10$ZW2NOZ6vXq5QnaiLLoEIHuLDBJlWqbXU6kjhSOWWNJK.FtG1IQyS.', 'Andris', 'Potapenko', '2025-10-21 15:15:26'),
	(7, 'Gamanists', '$2b$10$2tgddDdIriis6We.U1Gk3uEMIQ8LJYfOhd/flk60dnBw8Z9prOO9O', 'Nazars', 'Gamanists', '2025-10-21 20:55:30');

-- Dumping structure for table bank310.balance
CREATE TABLE IF NOT EXISTS `balance` (
  `BankAccountID` int(11) NOT NULL,
  `AccountID` int(11) NOT NULL,
  `Balance` decimal(12,2) DEFAULT 0.00,
  PRIMARY KEY (`BankAccountID`),
  KEY `AccountID` (`AccountID`),
  CONSTRAINT `balance_ibfk_1` FOREIGN KEY (`AccountID`) REFERENCES `account` (`AccountID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table bank310.balance: ~3 rows (approximately)
INSERT INTO `balance` (`BankAccountID`, `AccountID`, `Balance`) VALUES
	(104, 4, 0.00),
	(106, 6, 0.00),
	(107, 7, 0.00);

-- Dumping structure for table bank310.logs
CREATE TABLE IF NOT EXISTS `logs` (
  `LogID` int(11) NOT NULL AUTO_INCREMENT,
  `Action` varchar(100) NOT NULL,
  `Description` text DEFAULT NULL,
  `CreatedAt` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`LogID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table bank310.logs: ~0 rows (approximately)

-- Dumping structure for view bank310.transactionhistory
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `transactionhistory` (
	`TransactionID` VARCHAR(1) NOT NULL COLLATE 'utf8mb4_general_ci',
	`BankAccountID` INT(11) NOT NULL,
	`Amount` DECIMAL(12,2) NOT NULL,
	`Comment` VARCHAR(1) NULL COLLATE 'utf8mb4_general_ci',
	`Timestamp` DATETIME NULL
) ENGINE=MyISAM;

-- Dumping structure for table bank310.transactions
CREATE TABLE IF NOT EXISTS `transactions` (
  `TransactionID` varchar(100) NOT NULL,
  `SenderBankAccountID` int(11) NOT NULL,
  `ReceiverBankAccountID` int(11) NOT NULL,
  `Amount` decimal(12,2) NOT NULL CHECK (`Amount` > 0),
  `Comment` varchar(255) DEFAULT NULL,
  `Timestamp` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`TransactionID`),
  KEY `SenderBankAccountID` (`SenderBankAccountID`),
  KEY `ReceiverBankAccountID` (`ReceiverBankAccountID`),
  CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`SenderBankAccountID`) REFERENCES `balance` (`BankAccountID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `transactions_ibfk_2` FOREIGN KEY (`ReceiverBankAccountID`) REFERENCES `balance` (`BankAccountID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table bank310.transactions: ~0 rows (approximately)

-- Dumping structure for trigger bank310.generate_transaction_id
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER generate_transaction_id
BEFORE INSERT ON Transactions
FOR EACH ROW
BEGIN
    SET NEW.TransactionID = CONCAT(
        NEW.SenderBankAccountID, '_',
        NEW.ReceiverBankAccountID, '_',
        UNIX_TIMESTAMP()
    );
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;

-- Dumping structure for trigger bank310.set_bank_account_id
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER set_bank_account_id
BEFORE INSERT ON Balance
FOR EACH ROW
BEGIN
    SET NEW.BankAccountID = NEW.AccountID + 100;
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;

-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `transactionhistory`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `transactionhistory` AS SELECT 
    TransactionID,
    SenderBankAccountID AS BankAccountID,
    Amount,
    Comment,
    Timestamp
FROM Transactions ;

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;