-- --------------------------------------------------------
-- Server OS:                    Win64
-- HeidiSQL Version:             12.6.0.6765
-- --------------------------------------------------------



-- 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 COMMENT 'Primārā atslēga',
  `Username` varchar(50) NOT NULL COMMENT 'Lietotājvārds',
  `PasswordHash` varchar(255) NOT NULL COMMENT 'Hashēta parole ',
  `Name` varchar(100) NOT NULL COMMENT 'Lietotāja vārds',
  `Surname` varchar(100) NOT NULL COMMENT 'Lietotāja uzvārds',
  `CreatedAt` datetime DEFAULT current_timestamp() COMMENT 'Laika zīmogs, kad konts izveidots',
  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 COMMENT 'Bankas akaunta ID',
  `AccountID` int(11) NOT NULL COMMENT 'Akaunta ID',
  `Balance` decimal(12,2) DEFAULT 0.00 COMMENT 'Balans (nauda)',
  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 COMMENT 'Darbības id',
  `Action` varchar(100) NOT NULL COMMENT 'Darbība - norāda, kāda darbība tika veikta',
  `Description` text DEFAULT NULL COMMENT 'Apraksts -papildu informācija par darbību',
  `CreatedAt` datetime DEFAULT current_timestamp() COMMENT 'Laika zīmogs, kad darbība tika reģistrēta žurnālā',
  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(100) NOT NULL COMMENT 'Transakcijas id' COLLATE 'utf8mb4_general_ci',
	`BankAccountID` INT(11) NOT NULL COMMENT 'Bankas konts, kas sūta naudu ',
	`Amount` DECIMAL(12,2) NOT NULL COMMENT 'Naudas summa, kas tika nosūtīta',
	`Comment` VARCHAR(255) NULL COMMENT 'Komentārs - papildus informācija par transakciju' COLLATE 'utf8mb4_general_ci',
	`Timestamp` DATETIME NULL COMMENT 'Laika zīmogs, kad transakcija veikta'
) ENGINE=MyISAM;

-- Dumping structure for table bank310.transactions
CREATE TABLE IF NOT EXISTS `transactions` (
  `TransactionID` varchar(100) NOT NULL COMMENT 'Transakcijas id',
  `SenderBankAccountID` int(11) NOT NULL COMMENT 'Bankas konts, kas sūta naudu ',
  `ReceiverBankAccountID` int(11) NOT NULL COMMENT 'Bankas konts, kas saņem naudu ',
  `Amount` decimal(12,2) NOT NULL COMMENT 'Naudas summa, kas tika nosūtīta',
  `Comment` varchar(255) DEFAULT NULL COMMENT 'Komentārs - papildus informācija par transakciju',
  `Timestamp` datetime DEFAULT current_timestamp() COMMENT 'Laika zīmogs, kad transakcija veikta',
  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 ;

