-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `amerikan bank`
--

-- --------------------------------------------------------

--
-- Table structure for table `accounts`
--

CREATE TABLE `accounts` (
  `account_id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `account_type` enum('Checking','Savings') NOT NULL,
  `account_number` varchar(20) NOT NULL,
  `balance` decimal(15,2) NOT NULL DEFAULT 0.00,
  `is_activated` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `accounts`
--

INSERT INTO `accounts` (`account_id`, `user_id`, `account_type`, `account_number`, `balance`, `is_activated`, `created_at`) VALUES
(1, 1, 'Checking', '1234567890', 0.00, 1, '2025-11-04 12:09:08'),
(2, 2, 'Savings', '7304922438', 600.00, 1, '2025-11-04 12:09:36'),
(3, 3, 'Savings', '7376963333', 0.00, 1, '2025-11-04 13:14:03'),
(4, 4, 'Savings', '5622540531', 0.00, 1, '2025-11-04 13:42:01');

-- --------------------------------------------------------

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

CREATE TABLE `transactions` (
  `transaction_id` int(10) UNSIGNED NOT NULL,
  `account_id` int(10) UNSIGNED NOT NULL,
  `type` enum('Deposit','Withdrawal','Transfer','TRANSFER_OUT') NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `narration` varchar(255) DEFAULT NULL,
  `beneficiary_bank` varchar(100) DEFAULT NULL,
  `beneficiary_name` varchar(100) DEFAULT NULL,
  `beneficiary_account` varchar(20) DEFAULT NULL,
  `status` enum('PENDING','COMPLETED','FAILED') DEFAULT 'PENDING',
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `transactions` (`transaction_id`, `account_id`, `type`, `amount`, `description`, `narration`, `beneficiary_bank`, `beneficiary_name`, `beneficiary_account`, `status`, `created_at`) VALUES
(1, 2, 'Deposit', 1000.00, NULL, 'Balance', NULL, NULL, NULL, 'PENDING', '2025-11-04 12:11:09'),
(2, 2, 'TRANSFER_OUT', 100.00, 'Transfer to CHIDI', NULL, 'Bank of America', 'CHIDI', '2232323232', 'COMPLETED', '2025-11-04 12:18:00'),
(3, 2, 'TRANSFER_OUT', 100.00, 'Transfer to JOHN', NULL, 'Bank of America', 'JOHN', '2232323232', 'COMPLETED', '2025-11-04 12:20:33'),
(4, 2, 'TRANSFER_OUT', 100.00, 'Transfer to JOHN', NULL, 'Chase Bank', 'JOHN', '2232323232', 'COMPLETED', '2025-11-04 12:33:40'),
(5, 2, 'Transfer', 100.00, 'TRANSFER', NULL, 'Wells Fargo', 'JOHN', '2232323232', 'COMPLETED', '2025-11-04 13:02:19'),
(6, 2, 'Deposit', 600.00, NULL, 'Balance', NULL, NULL, NULL, 'PENDING', '2025-11-04 13:03:05'),
(7, 2, 'Deposit', 1000.00, NULL, 'Balance', NULL, NULL, NULL, 'PENDING', '2025-11-04 13:03:11'),
(8, 2, 'Transfer', 100.00, NULL, 'Transfer Pending', NULL, NULL, NULL, 'PENDING', '2025-11-04 13:03:53'),
(9, 2, 'Transfer', 100.00, 'Transfer', NULL, 'Wells Fargo', 'CHIDI', '2232323232', 'COMPLETED', '2025-11-04 13:24:33'),
(10, 2, 'Transfer', 100.00, 'Transfer', NULL, 'Chase Bank', 'CHIDI', '2232323232', 'COMPLETED', '2025-11-05 01:47:57'),
(11, 2, 'Transfer', 100.00, 'Transfer', NULL, 'Other', 'CHIDI', '2232323232', 'COMPLETED', '2025-11-05 01:51:35'),
(12, 2, 'Transfer', 100.00, 'Transfer', NULL, 'Wells Fargo', 'CHIDI', '5656565656', 'COMPLETED', '2025-11-05 01:56:22');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `accounts`
--
ALTER TABLE `accounts`
  ADD PRIMARY KEY (`account_id`),
  ADD UNIQUE KEY `account_number` (`account_number`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `transactions`
--
ALTER TABLE `transactions`
  ADD PRIMARY KEY (`transaction_id`),
  ADD KEY `account_id` (`account_id`),
  ADD KEY `type` (`type`),
  ADD KEY `created_at` (`created_at`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `accounts`
--
ALTER TABLE `accounts`
  MODIFY `account_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `transactions`
--
ALTER TABLE `transactions`
  MODIFY `transaction_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `accounts`
--
ALTER TABLE `accounts`
  ADD CONSTRAINT `accounts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE;

--
-- Constraints for table `transactions`
--
ALTER TABLE `transactions`
  ADD CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`account_id`) ON DELETE CASCADE;
COMMIT;

