--
-- Host: localhost    Database: moneyexchange
-- ------------------------------------------------------


--
-- Table structure for table `bank_account_transactions`
--

CREATE TABLE `bank_account_transactions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `bank_account_id` bigint unsigned DEFAULT NULL,
  `transaction_type` enum('in','out') COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` double NOT NULL,
  `credit_before_transaction` double NOT NULL,
  `credit_after_transaction` double NOT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `note` text COLLATE utf8mb4_unicode_ci,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `bank_account_transactions`
--


--
-- Table structure for table `bank_accounts`
--

CREATE TABLE `bank_accounts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `bank_id` bigint unsigned DEFAULT NULL,
  `currency_id` bigint unsigned DEFAULT NULL,
  `bank_username` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `bank_number` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `credit` double NOT NULL DEFAULT '0',
  `user_id` bigint unsigned DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `bank_accounts`
--


--
-- Table structure for table `banks`
--

CREATE TABLE `banks` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `logo` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `banks_name_unique` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `banks`
--

INSERT INTO `banks` VALUES (1,'ABA',NULL,NULL,1,'2022-07-07 09:08:39','2022-07-07 09:08:39'),(2,'វីង',NULL,NULL,1,'2022-07-07 09:08:42','2022-07-07 09:08:56'),(3,'អេស៊ីលីដា',NULL,NULL,1,'2022-07-07 09:08:51','2022-07-07 09:08:51');

--
-- Table structure for table `currencies`
--

CREATE TABLE `currencies` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `logo` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `currencies_name_unique` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `currencies`
--

INSERT INTO `currencies` VALUES (1,'ដុល្លា',NULL,1,NULL,'2022-07-07 09:08:08','2022-07-07 09:08:23'),(2,'រៀល',NULL,1,NULL,'2022-07-07 09:08:15','2022-07-07 09:08:15'),(3,'បាត',NULL,1,NULL,'2022-07-07 09:08:27','2022-07-07 09:08:27');

--
-- Table structure for table `failed_jobs`
--

CREATE TABLE `failed_jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `connection` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `queue` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `exception` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `failed_jobs`
--


--
-- Table structure for table `migrations`
--

CREATE TABLE `migrations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `migrations`
--

INSERT INTO `migrations` VALUES (1,'2014_10_11_000001_create_roles_table',1),(2,'2014_10_12_000000_create_users_table',1),(3,'2014_10_12_100000_create_password_resets_table',1),(4,'2019_08_19_000000_create_failed_jobs_table',1),(5,'2019_12_14_000001_create_personal_access_tokens_table',1),(6,'2022_06_20_151035_create_currencies_table',1),(7,'2022_06_20_154842_create_money_exchange_lists_table',1),(8,'2022_06_20_154843_create_money_exchange_rates_table',1),(9,'2022_06_20_154922_create_banks_table',1),(10,'2022_06_20_154933_create_bank_accounts_table',1),(11,'2022_06_20_154934_create_transactions_table',1),(12,'2022_06_20_154952_create_bank_account_transactions_table',1),(13,'2022_06_21_124503_create_settings_table',1),(14,'2022_07_02_141950_add_last_update_rate_date_to_money_exchange_lists_table',1),(15,'2022_07_02_183812_add_received_amount_to_transactions_table',1);

--
-- Table structure for table `money_exchange_lists`
--

CREATE TABLE `money_exchange_lists` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `exchange_from_currency_id` bigint unsigned DEFAULT NULL,
  `exchange_to_currency_id` bigint unsigned DEFAULT NULL,
  `calculation` enum('x','/') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'x',
  `current_rate` double(8,2) DEFAULT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `last_updated_rate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `exchange_from_currency_id_exchange_to_currency_id` (`exchange_from_currency_id`,`exchange_to_currency_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `money_exchange_lists`
--

INSERT INTO `money_exchange_lists` VALUES (1,1,2,'x',4093.00,1,1,NULL,'2022-07-07 09:09:32','2022-07-07 09:12:32','2022-07-07 09:12:32'),(2,1,3,'x',39.93,1,1,NULL,'2022-07-07 09:09:39','2022-07-07 09:12:20','2022-07-07 09:12:20'),(3,2,1,'/',4104.00,1,1,NULL,'2022-07-07 09:09:50','2022-07-07 09:12:07','2022-07-07 09:12:07'),(4,2,3,'/',114.00,1,1,NULL,'2022-07-07 09:09:59','2022-07-07 09:11:55','2022-07-07 09:11:55'),(5,3,1,'/',36.13,1,1,NULL,'2022-07-07 09:10:14','2022-07-07 09:11:41','2022-07-07 09:11:41'),(6,3,2,'x',113.40,1,1,NULL,'2022-07-07 09:10:23','2022-07-07 09:11:33','2022-07-07 09:11:33');

--
-- Table structure for table `money_exchange_rates`
--

CREATE TABLE `money_exchange_rates` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `money_exchange_list_id` bigint unsigned DEFAULT NULL,
  `exchange_rate` double(8,2) DEFAULT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `money_exchange_rates`
--

INSERT INTO `money_exchange_rates` VALUES (1,6,113.40,1,NULL,'2022-07-07 09:11:33','2022-07-07 09:11:33'),(2,5,36.13,1,NULL,'2022-07-07 09:11:41','2022-07-07 09:11:41'),(3,4,114.00,1,NULL,'2022-07-07 09:11:55','2022-07-07 09:11:55'),(4,3,4104.00,1,NULL,'2022-07-07 09:12:07','2022-07-07 09:12:07'),(5,2,39.93,1,NULL,'2022-07-07 09:12:20','2022-07-07 09:12:20'),(6,1,4093.00,1,NULL,'2022-07-07 09:12:32','2022-07-07 09:12:32');

--
-- Table structure for table `roles`
--

CREATE TABLE `roles` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `roles_name_unique` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `roles`
--

INSERT INTO `roles` VALUES (1,'staff',NULL,'2022-07-07 09:02:18','2022-07-07 09:02:18'),(2,'admin',NULL,'2022-07-07 09:02:18','2022-07-07 09:02:18');

--
-- Table structure for table `settings`
--

CREATE TABLE `settings` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `settings_name_unique` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `settings`
--

INSERT INTO `settings` VALUES (1,'title','Fast Money Exchange',NULL,'2022-07-07 09:02:18','2022-07-07 09:02:18'),(2,'logo','logo.png',NULL,'2022-07-07 09:02:18','2022-07-07 09:02:18');

--
-- Table structure for table `transactions`
--

CREATE TABLE `transactions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `customer_level` smallint NOT NULL DEFAULT '1',
  `transaction_type` enum('cash_exchange','bank_deposit','bank_withdraw','staff_deposit','staff_withdraw') COLLATE utf8mb4_unicode_ci NOT NULL,
  `money_exchange_list_id` bigint unsigned DEFAULT NULL,
  `bank_account_id` bigint unsigned DEFAULT NULL,
  `fee` double NOT NULL DEFAULT '0',
  `amount` double DEFAULT NULL,
  `exchange_rate` double(8,2) DEFAULT NULL,
  `remain` double NOT NULL DEFAULT '0',
  `note` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `received_amount` double NOT NULL DEFAULT '0',
  `amount_after_exchange` double NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `transactions`
--



