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

/*!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 dta_bank
CREATE DATABASE IF NOT EXISTS `dta_bank` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;
USE `dta_bank`;

-- Dumping structure for table dta_bank.accounts
CREATE TABLE IF NOT EXISTS `accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_number` char(10) NOT NULL COMMENT 'Unique code given to Clients/users account',
  `account_type` enum('Main','Savings') NOT NULL COMMENT 'Whether the account is Main, savings or any other type of account',
  `balance` decimal(12,2) NOT NULL COMMENT 'Account current balance',
  `currency_id` int(11) NOT NULL COMMENT 'Shows the account current currency',
  `status` enum('Open','Closed') NOT NULL DEFAULT 'Open' COMMENT 'Shows whether the account is still open or closed',
  `created_at` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Time when the account was created at',
  `closed_at` datetime DEFAULT NULL COMMENT 'Shows when the account was closed',
  PRIMARY KEY (`id`),
  UNIQUE KEY `account_number` (`account_number`),
  KEY `FK_accounts_currencies_info` (`currency_id`),
  CONSTRAINT `FK_accounts_currencies_info` FOREIGN KEY (`currency_id`) REFERENCES `currencies_info` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Information about User/Client account info.';

-- Dumping data for table dta_bank.accounts: ~10 rows (approximately)
INSERT INTO `accounts` (`id`, `account_number`, `account_type`, `balance`, `currency_id`, `status`, `created_at`, `closed_at`) VALUES
	(1, 'Z7K3L9Q2X8', 'Main', 12523.06, 1, 'Open', '2025-10-23 16:19:25', NULL),
	(2, 'M4T8J1R6W3', 'Savings', 5736.00, 1, 'Open', '2025-10-23 16:25:56', NULL),
	(3, 'B9X2C7V5N0', 'Main', 7218.42, 3, 'Open', '2025-10-23 16:26:19', NULL),
	(4, 'H2Y6D3A8K1', 'Savings', 100.27, 3, 'Open', '2025-10-23 16:26:39', NULL),
	(5, 'Q5W7E9Z4L2', 'Main', 120.24, 2, 'Open', '2025-10-23 16:27:10', NULL),
	(6, 'R8F1G6T3M9', 'Savings', 45.12, 2, 'Open', '2025-10-23 16:27:31', NULL),
	(7, 'L3P9S2U7B6', 'Main', 174921.85, 1, 'Open', '2025-10-23 16:27:47', NULL),
	(8, 'N0J4K8V1X5', 'Savings', 4218.00, 1, 'Open', '2025-10-23 16:28:04', NULL),
	(9, 'T6M2C9R7Y3', 'Main', 19204.12, 4, 'Open', '2025-10-23 16:28:19', NULL),
	(10, 'E1Z5W8L4Q0', 'Savings', 5124.25, 4, 'Open', '2025-10-23 16:28:34', NULL);

-- Dumping structure for table dta_bank.currencies_info
CREATE TABLE IF NOT EXISTS `currencies_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` char(3) NOT NULL COMMENT 'Short name for the currency',
  `name` varchar(50) NOT NULL COMMENT 'Full Currency name',
  `symbol` char(3) DEFAULT NULL COMMENT 'Short name for the currency',
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Information about currencies. Usefull if currencies change.';

-- Dumping data for table dta_bank.currencies_info: ~4 rows (approximately)
INSERT INTO `currencies_info` (`id`, `code`, `name`, `symbol`) VALUES
	(1, 'EUR', 'Euro', '€'),
	(2, 'USD', 'United States Dollar', '$'),
	(3, 'KRW', 'South Korean Won', '₩'),
	(4, 'DKK', 'Danish Krone', 'kr');

-- Dumping structure for table dta_bank.exchange_rates
CREATE TABLE IF NOT EXISTS `exchange_rates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `currency_from` char(3) NOT NULL COMMENT 'Which currency is changed',
  `currency_to` char(3) NOT NULL COMMENT 'To which currency is changed',
  `rate` decimal(10,6) NOT NULL COMMENT 'What is the rate between the currencies',
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'When were the last changes made',
  PRIMARY KEY (`id`),
  KEY `FK_exchange_rates_currencies_info` (`currency_from`),
  KEY `FK_exchange_rates_currencies_info_2` (`currency_to`),
  CONSTRAINT `FK_exchange_rates_currencies_info` FOREIGN KEY (`currency_from`) REFERENCES `currencies_info` (`code`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_exchange_rates_currencies_info_2` FOREIGN KEY (`currency_to`) REFERENCES `currencies_info` (`code`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table dta_bank.exchange_rates: ~12 rows (approximately)
INSERT INTO `exchange_rates` (`id`, `currency_from`, `currency_to`, `rate`, `updated_at`) VALUES
	(1, 'EUR', 'USD', 1.160000, '2025-10-26 22:26:27'),
	(2, 'USD', 'EUR', 0.860000, '2025-10-26 22:27:08'),
	(3, 'KRW', 'EUR', 0.000600, '2025-10-26 22:28:04'),
	(4, 'KRW', 'USD', 0.000690, '2025-10-26 22:28:32'),
	(5, 'USD', 'KRW', 1438.980000, '2025-10-26 22:28:58'),
	(6, 'EUR', 'KRW', 1670.220000, '2025-10-26 22:29:24'),
	(7, 'DKK', 'KRW', 223.910000, '2025-10-26 22:29:49'),
	(8, 'KRW', 'DKK', 0.004500, '2025-10-26 22:30:09'),
	(9, 'EUR', 'DKK', 7.470000, '2025-10-26 22:30:30'),
	(10, 'DKK', 'EUR', 0.130000, '2025-10-26 22:30:47'),
	(11, 'DKK', 'USD', 0.160000, '2025-10-26 22:31:09'),
	(12, 'USD', 'DKK', 6.430000, '2025-10-26 22:31:27');

-- Dumping structure for table dta_bank.login_details
CREATE TABLE IF NOT EXISTS `login_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(20) NOT NULL COMMENT 'Unique code given to the user',
  `personal_code` char(12) NOT NULL COMMENT 'Unique code given by the user',
  `login_time` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Used to know when User/Client loged into the system',
  PRIMARY KEY (`id`),
  KEY `FK_login_details_user_info` (`user_id`),
  KEY `FK_login_details_user_info_2` (`personal_code`),
  CONSTRAINT `FK_login_details_user_info` FOREIGN KEY (`user_id`) REFERENCES `user_info` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_login_details_user_info_2` FOREIGN KEY (`personal_code`) REFERENCES `user_info` (`personal_code`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Used to check whether entered info is true.';

-- Dumping data for table dta_bank.login_details: ~5 rows (approximately)
INSERT INTO `login_details` (`id`, `user_id`, `personal_code`, `login_time`) VALUES
	(1, 'LVU231025_2124 ', '135295-64921', '0000-00-00 00:00:00'),
	(2, 'KRU231025_5126 ', '281201-14921', '2025-10-23 16:37:43'),
	(3, 'DEU231025_5291', '180999-46101', '2025-10-23 16:37:51'),
	(4, 'USU231025_2124 ', '140589-92125', '2025-10-23 16:38:06'),
	(5, 'DKU231025_1820', '250504-91475', '2025-10-23 16:38:11');

-- Dumping structure for table dta_bank.login_history
CREATE TABLE IF NOT EXISTS `login_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(20) NOT NULL COMMENT 'Unique code given to the user',
  `login_time` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Keeps record of each time user has logged in',
  PRIMARY KEY (`id`),
  KEY `FK_login_history_login_details` (`user_id`),
  CONSTRAINT `FK_login_history_login_details` FOREIGN KEY (`user_id`) REFERENCES `login_details` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table dta_bank.login_history: ~0 rows (approximately)

-- Dumping structure for table dta_bank.transaction_details
CREATE TABLE IF NOT EXISTS `transaction_details` (
  `id` int(11) NOT NULL,
  `user_id` varchar(20) NOT NULL COMMENT 'Identifies which user started the transaction',
  `sender_account_id` int(11) NOT NULL COMMENT 'Shows from which account the funds were sent',
  `receiver_account_id` int(11) NOT NULL COMMENT 'Shows to which account were sent to',
  `amount` decimal(12,2) NOT NULL COMMENT 'Shows the transfered funds amount',
  `currency_id` int(11) NOT NULL COMMENT 'The transfered fund currency',
  `exchange_rate_id` int(11) DEFAULT NULL COMMENT 'Show the exchange rate between sent funds',
  `converted_amount` decimal(12,2) DEFAULT NULL COMMENT 'Shows the amount sent after currency exchange',
  `transaction_type` enum('Between Accounts','To another person') NOT NULL COMMENT 'Shows whether the funds were sent to another account or another user',
  `status` enum('Completed','Pending','Failed') NOT NULL COMMENT 'Shows the transaction current progress',
  `initiated_at` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Shows when the transfer was started',
  `completed_at` datetime DEFAULT NULL COMMENT 'Shows if the transfer was completed and when',
  `reference_code` varchar(20) NOT NULL COMMENT 'Unique code to easier identify transfer.',
  `description` text DEFAULT NULL COMMENT 'Users added text/message',
  PRIMARY KEY (`id`),
  UNIQUE KEY `reference_code` (`reference_code`),
  KEY `FK_transaction_details_user_info` (`user_id`),
  KEY `FK_transaction_details_accounts` (`sender_account_id`),
  KEY `FK_transaction_details_accounts_2` (`receiver_account_id`),
  KEY `FK_transaction_details_currencies_info` (`currency_id`),
  KEY `FK_transaction_details_exchange_rates` (`exchange_rate_id`),
  CONSTRAINT `FK_transaction_details_accounts` FOREIGN KEY (`sender_account_id`) REFERENCES `accounts` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_transaction_details_accounts_2` FOREIGN KEY (`receiver_account_id`) REFERENCES `accounts` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_transaction_details_currencies_info` FOREIGN KEY (`currency_id`) REFERENCES `currencies_info` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_transaction_details_exchange_rates` FOREIGN KEY (`exchange_rate_id`) REFERENCES `exchange_rates` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_transaction_details_user_info` FOREIGN KEY (`user_id`) REFERENCES `user_info` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table dta_bank.transaction_details: ~0 rows (approximately)

-- Dumping structure for table dta_bank.user_accounts
CREATE TABLE IF NOT EXISTS `user_accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL COMMENT 'Who is linked to the account',
  `user_id` varchar(20) NOT NULL COMMENT 'Which account they''re linked to',
  `role` varchar(20) NOT NULL COMMENT 'Their importance over the account',
  `status` enum('Active','Ended') NOT NULL DEFAULT 'Active' COMMENT 'Whether the link is still active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'When was the link started',
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'When was last updated at',
  PRIMARY KEY (`id`),
  KEY `FK_user_accounts_accounts` (`account_id`),
  KEY `FK_user_accounts_user_info` (`user_id`),
  CONSTRAINT `FK_user_accounts_accounts` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_user_accounts_user_info` FOREIGN KEY (`user_id`) REFERENCES `user_info` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Information about Client/User accounts';

-- Dumping data for table dta_bank.user_accounts: ~10 rows (approximately)
INSERT INTO `user_accounts` (`id`, `account_id`, `user_id`, `role`, `status`, `created_at`, `updated_at`) VALUES
	(1, 1, 'LVU231025_2124 ', 'Owner', 'Active', '2025-10-23 16:31:31', '2025-10-23 16:31:52'),
	(2, 2, 'LVU231025_2124 ', 'Owner', 'Active', '2025-10-23 16:31:48', '2025-10-23 16:31:48'),
	(3, 3, 'KRU231025_5126 ', 'Owner', 'Active', '2025-10-23 16:32:19', '2025-10-23 16:32:58'),
	(4, 4, 'KRU231025_5126 ', 'Owner', 'Active', '2025-10-23 16:33:11', '2025-10-23 16:33:35'),
	(5, 5, 'USU231025_2124 ', 'Owner', 'Active', '2025-10-23 16:33:31', '2025-10-23 16:33:39'),
	(6, 6, 'USU231025_2124 ', 'Owner', 'Active', '2025-10-23 16:33:50', '2025-10-23 16:33:50'),
	(7, 7, 'DEU231025_5291', 'Owner', 'Active', '2025-10-23 16:34:04', '2025-10-23 16:34:04'),
	(8, 8, 'DEU231025_5291', 'Owner', 'Active', '2025-10-23 16:34:13', '2025-10-23 16:34:13'),
	(9, 9, 'DKU231025_1820', 'Owner', 'Active', '2025-10-23 16:34:33', '2025-10-23 16:34:33'),
	(10, 10, 'DKU231025_1820', 'Owner', 'Active', '2025-10-23 16:34:42', '2025-10-23 16:34:42');

-- Dumping structure for table dta_bank.user_info
CREATE TABLE IF NOT EXISTS `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(20) NOT NULL COMMENT 'Unique code given to the user',
  `personal_code` char(12) NOT NULL COMMENT 'Unique code given by the user',
  `name` varchar(50) NOT NULL COMMENT 'Clients/users name',
  `surname` varchar(50) NOT NULL COMMENT 'Clients/users username',
  `email` varchar(50) NOT NULL COMMENT 'Clients/users email',
  `created_at` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'When the account was created',
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'When were the last changes made',
  `is_active` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Shows if account is still active. 1 is Yes / 0 is NO',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  UNIQUE KEY `personal_code` (`personal_code`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Information about the User/Client';

-- Dumping data for table dta_bank.user_info: ~5 rows (approximately)
INSERT INTO `user_info` (`id`, `user_id`, `personal_code`, `name`, `surname`, `email`, `created_at`, `updated_at`, `is_active`) VALUES
	(1, 'LVU231025_2124 ', '135295-64921', 'Ansis', 'Lapa', 'ansis@gmail.com', '2025-10-23 16:01:45', '2025-10-23 16:10:31', 1),
	(2, 'KRU231025_5126 ', '281201-14921', 'Yung', 'Swee-hun', 'YuSwee@yahoo.com', '2025-10-23 16:05:20', '2025-10-23 16:10:37', 1),
	(3, 'USU231025_2124 ', '140589-92125', 'Sofia', 'Hull', 'SofHu@gmail.com', '2025-10-23 16:05:35', '2025-10-23 16:09:43', 1),
	(4, 'DEU231025_5291', '180999-46101', 'Mia', 'Octier', 'MiaOc@gmail.com', '2025-10-23 16:09:35', '2025-10-23 16:11:42', 1),
	(5, 'DKU231025_1820', '250504-91475', 'Anabell', 'Schnell', 'AnabSchnell@gmail.com', '2025-10-23 16:13:11', '2025-10-23 16:13:11', 1);

/*!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) */;