-- phpMyAdmin SQL Dump
-- version 5.2.2
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Mar 23, 2026 at 01:27 PM
-- Server version: 10.6.25-MariaDB
-- PHP Version: 8.4.18

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `cidapayc_solution`
--

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

--
-- Table structure for table `cida_savings_plans`
--

CREATE TABLE `cida_savings_plans` (
  `id` int(11) NOT NULL,
  `code` varchar(20) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `min_amount` decimal(15,2) DEFAULT 0.00,
  `interest_rate` decimal(5,2) DEFAULT 0.00,
  `lock_period_months` int(11) DEFAULT NULL,
  `withdrawal_charge` decimal(5,2) DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `status` varchar(20) DEFAULT 'ACTIVE',
  `auto_save_percentage` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `cida_savings_plans`
--

INSERT INTO `cida_savings_plans` (`id`, `code`, `name`, `description`, `min_amount`, `interest_rate`, `lock_period_months`, `withdrawal_charge`, `created_at`, `status`, `auto_save_percentage`) VALUES
(1, 'CIDA_PREMIUM', 'CIDA Premium', '12 months fixed savings with high interests', 4999.98, 15.00, 12, 3.00, '2025-06-02 12:03:09', 'ACTIVE', ''),
(2, 'CIDA_GOLD', 'CIDA Gold', '6 months fixed savings', 3000.00, 3.00, 6, 2.00, '2025-06-02 12:03:09', 'ACTIVE', NULL),
(3, 'CIDA_BRONZE', 'CIDA Bronze', 'Flexible savings with interest', 1000.00, 1.50, NULL, 1.50, '2025-06-02 12:03:09', 'ACTIVE', NULL),
(4, 'CIDA_FREEDOM', 'CIDA Freedom', 'Daily savings with no restrictions', 100.00, 0.00, NULL, 0.00, '2025-06-02 12:03:09', 'ACTIVE', NULL),
(5, 'CIDA_SMARTSAVE', 'CIDA SmartSave', 'Automatic savings from spending', 500.00, 0.00, NULL, 0.00, '2025-06-02 12:03:09', 'ACTIVE', NULL),
(6, 'CIDA_TARGET', 'CIDA Target', 'Goal-oriented savings', 2000.00, 1.00, NULL, 1.00, '2025-06-02 12:03:09', 'ACTIVE', NULL);

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

--
-- Table structure for table `cida_savings_transactions`
--

CREATE TABLE `cida_savings_transactions` (
  `id` int(11) NOT NULL,
  `savings_account_id` int(11) NOT NULL,
  `reference` varchar(50) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `previous_balance` decimal(15,2) NOT NULL,
  `new_balance` decimal(15,2) NOT NULL,
  `transaction_type` varchar(20) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `cida_savings_transactions`
--

INSERT INTO `cida_savings_transactions` (`id`, `savings_account_id`, `reference`, `amount`, `previous_balance`, `new_balance`, `transaction_type`, `description`, `created_at`) VALUES
(21, 10, 'CIDA1753526625627', 2000.00, 0.00, 2000.00, 'DEPOSIT', 'Initial deposit', '2025-07-26 10:43:45'),
(22, 10, 'CIDA1753526733132', 200.00, 2000.00, 2200.00, 'DEPOSIT', 'Additional deposit', '2025-07-26 10:45:33'),
(24, 12, 'CIDA1754679189376', 10000.00, 0.00, 10000.00, 'DEPOSIT', 'Initial deposit', '2025-08-08 18:53:09'),
(42, 29, 'CIDA1756820239222', 6000.00, 0.00, 6000.00, 'DEPOSIT', 'Initial deposit', '2025-09-02 13:37:19'),
(47, 29, 'CIDA1756820856247', 1000.00, 6000.00, 7000.00, 'DEPOSIT', 'Additional deposit', '2025-09-02 13:47:36'),
(48, 29, 'CIDA1756820895962', 1000.00, 7000.00, 6000.00, 'WITHDRAWAL', 'Withdrawal (Early) - Charge: ₦30.00', '2025-09-02 13:48:15'),
(52, 32, 'CIDA1757039144200', 300000.00, 0.00, 300000.00, 'DEPOSIT', 'Initial deposit', '2025-09-05 02:25:44'),
(60, 35, 'CIDA1774101873180', 4000.00, 0.00, 4000.00, 'DEPOSIT', 'Initial deposit', '2026-03-21 14:04:33'),
(61, 36, 'CIDA1774101873874', 4000.00, 0.00, 4000.00, 'DEPOSIT', 'Initial deposit', '2026-03-21 14:04:33'),
(62, 38, 'CIDA1774101873949', 4000.00, 0.00, 4000.00, 'DEPOSIT', 'Initial deposit', '2026-03-21 14:04:33'),
(64, 39, 'CIDA1774101873460', 4000.00, 0.00, 4000.00, 'DEPOSIT', 'Initial deposit', '2026-03-21 14:04:33'),
(65, 40, 'CIDA1774101873168', 4000.00, 0.00, 4000.00, 'DEPOSIT', 'Initial deposit', '2026-03-21 14:04:33'),
(66, 41, 'CIDA1774101873123', 4000.00, 0.00, 4000.00, 'DEPOSIT', 'Initial deposit', '2026-03-21 14:04:33');

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

--
-- Table structure for table `cida_transaction_types`
--

CREATE TABLE `cida_transaction_types` (
  `code` varchar(20) NOT NULL,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `cida_transaction_types`
--

INSERT INTO `cida_transaction_types` (`code`, `name`, `description`) VALUES
('DEPOSIT', 'Deposit', 'Funds added to savings account'),
('INTEREST', 'Interest', 'Interest earned on savings'),
('WITHDRAWAL', 'Withdrawal', 'Funds removed from savings account');

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

--
-- Table structure for table `cida_user_savings`
--

CREATE TABLE `cida_user_savings` (
  `id` int(11) NOT NULL,
  `user_id` varchar(50) NOT NULL,
  `plan_id` int(11) NOT NULL,
  `account_name` varchar(100) NOT NULL,
  `current_balance` decimal(15,2) NOT NULL DEFAULT 0.00,
  `target_amount` decimal(15,2) DEFAULT NULL,
  `start_date` date NOT NULL,
  `maturity_date` date DEFAULT NULL,
  `plan_type` enum('FIXED','FLEXIBLE') NOT NULL DEFAULT 'FLEXIBLE',
  `status` enum('ACTIVE','MATURED','CLOSED') NOT NULL DEFAULT 'ACTIVE',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `auto_save_percentage` decimal(5,2) DEFAULT NULL,
  `ratified_at` datetime DEFAULT NULL,
  `ratified_by` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `cida_user_savings`
--

INSERT INTO `cida_user_savings` (`id`, `user_id`, `plan_id`, `account_name`, `current_balance`, `target_amount`, `start_date`, `maturity_date`, `plan_type`, `status`, `created_at`, `auto_save_percentage`, `ratified_at`, `ratified_by`) VALUES
(10, 'B4GSQ2YRMN', 6, 'tttesting', 2200.00, 3900.00, '2025-07-26', NULL, 'FLEXIBLE', 'ACTIVE', '2025-07-26 10:43:45', 10.00, NULL, NULL),
(12, 'ZYHLH6GP49', 1, '07049879846', 10000.00, 0.00, '2025-08-08', '2026-08-08', 'FIXED', 'ACTIVE', '2025-08-08 18:53:09', 10.00, NULL, NULL),
(29, '2NJ58HG1OA', 1, 'lllll', 6000.00, 0.00, '2025-09-02', '2026-09-02', 'FIXED', 'ACTIVE', '2025-09-02 13:37:19', 10.00, NULL, NULL),
(32, '2NJ58HG1OA', 6, 'Macbook', 300000.00, 0.00, '2025-09-05', NULL, 'FLEXIBLE', 'ACTIVE', '2025-09-05 02:25:44', 10.00, NULL, NULL),
(35, 'MNGM514ER8', 2, 'marriage', 4000.00, 0.00, '2026-03-21', '2026-09-21', 'FIXED', 'ACTIVE', '2026-03-21 14:04:33', 10.00, NULL, NULL),
(36, 'MNGM514ER8', 2, 'marriage', 4000.00, 0.00, '2026-03-21', '2026-09-21', 'FIXED', 'ACTIVE', '2026-03-21 14:04:33', 10.00, NULL, NULL),
(38, 'MNGM514ER8', 2, 'marriage', 4000.00, 0.00, '2026-03-21', '2026-09-21', 'FIXED', 'ACTIVE', '2026-03-21 14:04:33', 10.00, NULL, NULL),
(39, 'MNGM514ER8', 2, 'marriage', 4000.00, 0.00, '2026-03-21', '2026-09-21', 'FIXED', 'ACTIVE', '2026-03-21 14:04:33', 10.00, NULL, NULL),
(40, 'MNGM514ER8', 2, 'marriage', 4000.00, 0.00, '2026-03-21', '2026-09-21', 'FIXED', 'ACTIVE', '2026-03-21 14:04:33', 10.00, NULL, NULL),
(41, 'MNGM514ER8', 2, 'marriage', 4000.00, 0.00, '2026-03-21', '2026-09-21', 'FIXED', 'ACTIVE', '2026-03-21 14:04:33', 10.00, NULL, NULL);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `cida_savings_plans`
--
ALTER TABLE `cida_savings_plans`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `code` (`code`);

--
-- Indexes for table `cida_savings_transactions`
--
ALTER TABLE `cida_savings_transactions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `savings_account_id` (`savings_account_id`),
  ADD KEY `transaction_type` (`transaction_type`);

--
-- Indexes for table `cida_transaction_types`
--
ALTER TABLE `cida_transaction_types`
  ADD PRIMARY KEY (`code`);

--
-- Indexes for table `cida_user_savings`
--
ALTER TABLE `cida_user_savings`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `plan_id` (`plan_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `cida_savings_plans`
--
ALTER TABLE `cida_savings_plans`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `cida_savings_transactions`
--
ALTER TABLE `cida_savings_transactions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=68;

--
-- AUTO_INCREMENT for table `cida_user_savings`
--
ALTER TABLE `cida_user_savings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=42;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `cida_savings_transactions`
--
ALTER TABLE `cida_savings_transactions`
  ADD CONSTRAINT `cida_savings_transactions_ibfk_1` FOREIGN KEY (`savings_account_id`) REFERENCES `cida_user_savings` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_transaction_type` FOREIGN KEY (`transaction_type`) REFERENCES `cida_transaction_types` (`code`);

--
-- Constraints for table `cida_user_savings`
--
ALTER TABLE `cida_user_savings`
  ADD CONSTRAINT `cida_user_savings_ibfk_1` FOREIGN KEY (`plan_id`) REFERENCES `cida_savings_plans` (`id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
