-- --------------------------------------------------------
-- Server OS:                    Win64
-- HeidiSQL Version:             12.8.0.6908
-- --------------------------------------------------------



-- 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.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 ;

-- 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 ;

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

