-- phpMyAdmin SQL Dump
-- version 5.2.2
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Jul 30, 2026 at 10:51 PM
-- Server version: 10.11.18-MariaDB-cll-lve
-- PHP Version: 8.4.22

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: `ntsazwmq_sheldang`
--

DELIMITER $$
--
-- Procedures
--
$$

DELIMITER ;

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

--
-- Table structure for table `balance_additions`
--

CREATE TABLE `balance_additions` (
  `id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `source` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `added_by` int(11) DEFAULT NULL,
  `added_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `cancellation_requests`
--

CREATE TABLE `cancellation_requests` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `reason` text DEFAULT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `requested_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `donations`
--

CREATE TABLE `donations` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `payment_proof` varchar(255) DEFAULT NULL,
  `status` enum('pending','verified','rejected') DEFAULT 'pending',
  `uploaded_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `donations`
--

INSERT INTO `donations` (`id`, `user_id`, `amount`, `payment_proof`, `status`, `uploaded_at`) VALUES
(11, 12, 250.00, '../assets/uploads/payment_proof/1779901877_22769e18-5598-4490-a4fe-7c7d42a98109.jfif', 'verified', '2026-05-27 17:11:17'),
(14, 22, 200.00, '../assets/uploads/payment_proof/1779906815_Screenshot_2026-05-28-00-02-25-04_4336b74596784d9a2aa81f87c2016f50.jpg', 'verified', '2026-05-27 18:33:35'),
(19, 23, 10.00, '../assets/uploads/payment_proof/1779907083_UPI_QR (5).png', 'verified', '2026-05-27 18:38:03'),
(21, 16, 100.00, '../assets/uploads/payment_proof/1779948205_Screenshot_2026-05-28-11-32-36-637_com.naviapp-edit.jpg', 'verified', '2026-05-28 06:03:25'),
(22, 12, 200.00, '../assets/uploads/payment_proof/1779980918_22769e18-5598-4490-a4fe-7c7d42a98109.jfif', 'verified', '2026-05-28 15:08:38'),
(23, 33, 300.00, '../assets/uploads/payment_proof/1780030430_IMG-20260529-WA0005.jpg', 'verified', '2026-05-29 04:53:50'),
(25, 45, 200.00, '../assets/uploads/payment_proof/1780064130_IMG-20260529-WA0007.jpg', 'verified', '2026-05-29 14:15:30'),
(27, 47, 250.00, '../assets/uploads/payment_proof/1780161143_IMG-20260530-WA0062.jpg', 'verified', '2026-05-30 17:12:23'),
(28, 46, 11.00, '../assets/uploads/payment_proof/1780196257_Screenshot_2026_0531_082346.jpg', 'verified', '2026-05-31 02:57:37'),
(31, 43, 1.00, '../assets/uploads/payment_proof/1780406181_Screenshot_20260602_182611.jpg', 'verified', '2026-06-02 13:16:21'),
(33, 52, 100.00, '../assets/uploads/payment_proof/1780408189_Screenshot_2026-06-02-19-17-56-83_6012fa4d4ddec268fc5c7112cbb265e7.jpg', 'verified', '2026-06-02 13:49:49'),
(34, 56, 90.00, '../assets/uploads/payment_proof/1781248915_inbound7470601569452131026.jpg', 'rejected', '2026-06-12 07:21:55'),
(35, 56, 90.00, '../assets/uploads/payment_proof/1781248917_inbound7470601569452131026.jpg', 'rejected', '2026-06-12 07:21:57'),
(38, 16, 100.00, '../assets/uploads/payment_proof/1781629896_Screenshot_2026-06-16-22-40-35-679_com.naviapp.jpg', 'verified', '2026-06-16 17:11:37');

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

--
-- Table structure for table `fund_distributions`
--

CREATE TABLE `fund_distributions` (
  `id` int(11) NOT NULL,
  `description` text DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `distributed_to` varchar(255) DEFAULT NULL,
  `date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `kyc_rejection_logs`
--

CREATE TABLE `kyc_rejection_logs` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `rejection_reason` text DEFAULT NULL,
  `rejected_by` int(11) DEFAULT NULL,
  `rejected_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `kyc_rejection_logs`
--

INSERT INTO `kyc_rejection_logs` (`id`, `user_id`, `rejection_reason`, `rejected_by`, `rejected_at`) VALUES
(2, 22, 'retry', 12, '2026-05-27 18:01:21'),
(3, 21, 'retry', 12, '2026-05-27 18:06:47'),
(6, 30, 'â€¢ Document is incomplete Father name requir', 12, '2026-05-28 17:36:35'),
(9, 42, 'â€¢ Document does not match user name', 12, '2026-05-29 13:43:14'),
(10, 24, 'â€¢ Document is incomplete', 12, '2026-06-02 06:29:11'),
(11, 28, 'â€¢ Document is incomplete', 12, '2026-06-02 06:29:27'),
(12, 29, 'â€¢ Document is incomplete', 12, '2026-06-02 06:29:38'),
(13, 31, 'â€¢ Document is incomplete\r\nâ€¢ Document is incomplete', 12, '2026-06-02 06:29:51'),
(14, 32, 'â€¢ Document is incomplete', 12, '2026-06-02 06:30:01'),
(15, 37, 'â€¢ Document is incomplete', 12, '2026-06-02 06:30:19'),
(16, 44, 'â€¢ Document is incomplete', 12, '2026-06-02 06:30:26');

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

--
-- Table structure for table `org_signatures`
--

CREATE TABLE `org_signatures` (
  `id` int(11) NOT NULL,
  `position` varchar(50) NOT NULL,
  `signature_image` varchar(255) DEFAULT NULL,
  `signatory_name` varchar(100) DEFAULT NULL,
  `designation` varchar(100) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `org_signatures`
--

INSERT INTO `org_signatures` (`id`, `position`, `signature_image`, `signatory_name`, `designation`, `updated_at`) VALUES
(1, 'president', 'assets/uploads/signatures/signature_president_1779810333.jpg', 'Shri Anup Sinha', 'Central In Charge', '2026-05-26 15:45:33'),
(2, 'secretary', 'assets/uploads/signatures/signature_secretary_1779810380.jpg', 'Shri Arpon Sinha', 'President', '2026-05-27 12:02:57'),
(3, 'treasurer', 'assets/uploads/signatures/signature_treasurer_1779810387.jpg', 'Smt. Sukla Singha', 'Secretary', '2026-05-27 12:03:12');

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

--
-- Table structure for table `password_resets`
--

CREATE TABLE `password_resets` (
  `id` int(11) NOT NULL,
  `email` varchar(100) NOT NULL,
  `token` varchar(255) NOT NULL,
  `expires_at` datetime NOT NULL,
  `used` tinyint(4) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `password_resets`
--

INSERT INTO `password_resets` (`id`, `email`, `token`, `expires_at`, `used`, `created_at`) VALUES
(1, 'anupsinha.amugo@gmail.com', '777470a750af0a5355b79facf9e7d1cbc37555234319aa798ece137c2d696363', '2026-05-27 20:01:34', 1, '2026-05-27 13:31:34'),
(2, 'bimansinha2@gmail.com', '8deb6531bba0a175907c85e64ca3251a883fae7d80804f48ba94ddbe95bc3496', '2026-06-11 15:38:04', 0, '2026-06-11 09:08:04');

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

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `mobile` varchar(15) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `role` enum('user','admin') DEFAULT 'user',
  `kyc_status` enum('pending','approved','rejected') DEFAULT 'pending',
  `kyc_doc` varchar(255) DEFAULT NULL,
  `membership_active` tinyint(1) DEFAULT 1,
  `total_donation` decimal(10,2) DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `name`, `email`, `password`, `mobile`, `address`, `role`, `kyc_status`, `kyc_doc`, `membership_active`, `total_donation`, `created_at`) VALUES
(12, 'Anup Sinha', 'anupsinha.amugo@gmail.com', '$2y$10$NKIoEXQAXAI0tZ4SFcaMK.q/Vjntj21PCBpI7NcFd/nU1XSYJp5MG', '7086299453', 'Dullabcherra', 'admin', 'approved', NULL, 1, 450.00, '2026-05-26 15:41:09'),
(16, 'Bikranta Sinha', 'bikrantasinha123@gmail.com', '$2y$10$IOvLfWICKfS0IQ2ke34u6.QW2Z2HfkTwrWYSyI/6DpoDdbShXAQsy', '6009651482', 'Kanchancherra Manipuri Para \r\n82 milles Dhalai Tripura', 'user', 'approved', 'assets/uploads/kyc/kyc_16_1779955708_3192.jpg', 1, 200.00, '2026-05-26 17:41:53'),
(19, 'Admin User', 'admin@example.com', '$2y$10$46z87wxvhfTVmMOWzama0uEAfCX6tE4yBJZq/byxca2Nf.QM3A1Na', NULL, NULL, 'admin', 'approved', NULL, 1, 0.00, '2026-05-27 09:43:14'),
(21, 'KRIPESH KANTA SINHA', 'kripeshsinha55810@gmail.com', '$2y$10$jQtzvayQkrKwB0ry4r4W3O4f0GIYGQ71GU1mZ/mUryAmOfRXvIXSu', '9325074068', 'Gorerbond', 'user', 'approved', 'assets/uploads/kyc/1780053507_kyc_21_1779905500_9781.jpg', 1, 0.00, '2026-05-27 17:52:45'),
(22, 'BIMAL SINHA', 'sinhabimal2018@gmail.com', '$2y$10$DMB5kZvAEzNAz2eeG0BDzeMOb7n5SANL1Q96NvwSO5hCDSzKxwxIG', '9862213600', 'Vill+Po.Betcherra Kumarghat unakoti Tripura', 'user', 'approved', 'assets/uploads/kyc/kyc_22_1779904927_7870.jpg', 1, 200.00, '2026-05-27 17:55:47'),
(23, 'Arpon Sinha', 'sinhaarpon90@gmail.com', '$2y$10$7hBKrVlXgNdf/.X.HjE2/OtQhaCHoMQkwADvQA606qy4MIRl0GCba', '+917002460060', 'Arpon Sinha \r\nVillage -Bhutucherra\r\nP\'o- Cheragi \r\nDist- Shreebumi (Assam)\r\nPin-788737', 'user', 'approved', 'assets/uploads/kyc/1780053786_kyc_23_1779905194_2287.png', 1, 10.00, '2026-05-27 17:55:53'),
(24, 'Sumit Kumar Sinha', 'sumitkumar82kxj@gmail.com', '$2y$10$tOP6K7AsFEVAAeJBDQwa1OzE..v4OnpAYwl7s7K3ykp18xIyZZVZO', '6000396695', 'Goang- Bethubari \r\nLoya- Paschim Singla \r\nDist. - Sribhumi \r\nPin - 788735', 'user', 'rejected', NULL, 1, 0.00, '2026-05-28 05:33:49'),
(28, 'Kapil Dev Singha', 'kapildevsingha93@gmail.com', '$2y$10$iMAr/4O9lx7OmHBRuqz91u1Ve9FpAIX/njd9JdJ5vPls4.kvzgluO', '9395426447', 'Nityanandapur Pt I\r\nHailakandi, Assam', 'user', 'rejected', NULL, 1, 0.00, '2026-05-28 17:10:05'),
(29, 'Shibu Sinha', 'shibusinha87@gmail.com', '$2y$10$TMrc03emybv.sTOWhHpeQeC.5QDHIDf.2hapXyWmJQi9BuHTNgpt.', '8638662117', 'Rabindra sarani Road\r\nWard no 2', 'user', 'rejected', NULL, 1, 0.00, '2026-05-28 17:17:20'),
(30, 'Sudip Sinha', 'Sudipsinha853@gmail.com', '$2y$10$5ks6GGToCdwNTpiBy4V/H.vKM42yK5rihCjEIueHdh.buzvVFJdpi', '7637094526', 'Vill-Tinokhal. Po-Tinokhal. District-Sribhumi. Assam Pin-788724', 'user', 'approved', NULL, 1, 0.00, '2026-05-28 17:21:12'),
(31, 'Sukhajit singha', 'sukhjitsingha84@gmail.com', '$2y$10$CwuI99RVxh7q2bVoFM06lutZcioRDNtu7MzWYil9j1m/lQFme.L2u', '7020566720', 'Paltan Bazaar', 'user', 'rejected', NULL, 1, 0.00, '2026-05-28 17:21:42'),
(32, 'Biki Sinha', 'bikisinha802@gmail.com', '$2y$10$W5n7CPpyksi1vo7kQUDZuOpxXz93LWNfCfFckm0Qxvv1EF30jnotO', '+917002772124', '799288', 'user', 'rejected', NULL, 1, 0.00, '2026-05-29 00:53:01'),
(33, 'RANADHIR. SINHA', 'rds071149@gmail.com', '$2y$10$oN0XfMpWUAqrQX8dAsTideBpZHobqo5VHMYLHElU5A6.cdmeOkazS', '9366185421', 'Vill+Po=Betchera Kumarghat Unakoti Tripura', 'user', 'approved', NULL, 1, 300.00, '2026-05-29 04:41:05'),
(34, 'Biswa Kumar Sinha', 'biswasinha1990@gmail.com', '$2y$10$jybVL5odBLXwZRgu2/Lv/.nUsNAS3rmnBJGGti4K.oTzvutDRTJsO', '8474096647', 'B. P. Caliha nagar \r\nRailway water tank Road. Bamunimaidam ,Ghy - 21', 'user', 'approved', 'assets/uploads/kyc/kyc_34_1780032751_6107.jpg', 1, 0.00, '2026-05-29 05:24:51'),
(36, 'Bijit Sinha', 'sinhabijit933@gmail.com', '$2y$10$AZB57rVDLPHzEX0F1lC.W.tgyHNlHw72dMdgheKeSxrx9H.kihZMC', '8876482489', 'Baromuni silchar', 'user', 'approved', 'assets/uploads/kyc/1780206878_Screenshot2026-05-31112311.png', 1, 0.00, '2026-05-29 08:51:53'),
(37, 'Barnali Sinha', 'barnalisinha501@gmail.com', '$2y$10$YClKvZvgq8VLbuK9d63U9eGWd3aDjs3aYhzTHVE38FkPqovWINeDq', '+916909775154', 'Halhali kamolpur Tripura', 'user', 'rejected', NULL, 1, 0.00, '2026-05-29 09:06:20'),
(42, 'Ajit Sinha', 'ajit@mail.com', '$2y$10$woWMT62HQNvYEdPO1imrauucQ284NwY8I3S3CVtzO.x33nVKKO.02', '123', 'abc', 'user', 'rejected', NULL, 1, 0.00, '2026-05-29 10:56:51'),
(43, 'Sumit Kumar Sinha', 'sumitkrsinha82kxj@gmail.com', '$2y$10$wIjX4KOkRLZWHL9i87ld2.V7HD8/G8iQNhbzXMw8jHr7zv3kv6m.2', '+1916000396695', 'Vill. - Bethubari.   P O. - Ratabari Dist. - Sribhumi \r\nPIN - 788735.  Loya - Paschim Singla', 'user', 'approved', 'assets/uploads/kyc/kyc_43_1780052704_6536.jpg', 1, 1.00, '2026-05-29 11:04:03'),
(44, 'Kanika sinha', 'kanikasinha276@gmail.com', '$2y$10$uF2o2YSTUwcYsApfJ3TbueXbP6YREYzCCjRa4J2zXUjC4VT1C1NWC', '7086663842', 'PWD 3rd Colony, Hagjer Nagar, Haflong, Assam 788819, India Dima Hasao', 'user', 'rejected', NULL, 1, 0.00, '2026-05-29 13:19:51'),
(45, 'NIRMAL KANTA.SINHA', 'nirmalsingha9862@gmail.com', '$2y$10$oVE7oQv09F50nNVolr9.Oef8DJ2MauqMI9yGDeOfC./zp7JZjQuDi', '7005060880', 'Vill+Po-Betcherra Kumarghat Unakoti Tripurari', 'user', 'approved', 'assets/uploads/kyc/1780063896_b7abd089-03d2-41f7-8345-c49013443350.jpg', 1, 200.00, '2026-05-29 14:08:59'),
(46, 'Biman Sinha', 'bimansinha2@gmail.com', '$2y$10$6TGpBVsnHnFEzUH6LOtUIe4riGcARkKSuG6itr9hDdCYroLcLO1Bq', '9508182644', 'Bhubaneswar Nagar (Baropua), Sewti Part III', 'user', 'approved', 'assets/uploads/kyc/kyc_46_1780070716_7093.jpg', 1, 11.00, '2026-05-29 15:55:07'),
(47, 'Sani Sinha', 'sanisinha34877@gmail.com', '$2y$10$C0oHNX3Y76W3y.o6ZLgmN.fDrnAJP7R9FtGOV4IM0/YIGr8ckT9WO', '8134975741', '9044 5780 0645', 'user', 'approved', 'assets/uploads/kyc/kyc_47_1780161010_2220.jpg', 1, 250.00, '2026-05-29 17:04:49'),
(48, 'Dipu kr sinha', 'dipusinha707@gmail.com', '$2y$10$8stI6u.zWholPWLLlEW7G.lYL/dvnso7ehrgiwMXzpS28Acj7CPC6', '8908079282', 'Anipu village Madanpur', 'user', 'approved', 'assets/uploads/kyc/1780076984_Screenshot2026-05-29231921.png', 1, 0.00, '2026-05-29 17:09:40'),
(49, 'Goutam sinha', 'goutamsinha759@gmail.com', '$2y$10$l1AwgivqIuwypXbfcJ7bO.l4UafJNqeSoF0xbkFVRWcm3NH6yfHS2', '8554938370', 'Tripura Dharmanaghor', 'user', 'approved', 'assets/uploads/kyc/1780230682_Screenshot_20260531_180057_WhatsApp.jpg', 1, 0.00, '2026-05-29 17:12:41'),
(50, 'Rishikesh sinha', 'sinharishikesh28@gmail.com', '$2y$10$CCirXZXDIgtynaWvn7ksQewWoA/XWTWYCyWbi2BxYHErZdfdlgwAO', '8861792167', 'Village:Algapur po:Rajnagar\r\nDistc:cachar assam pin 788026', 'user', 'approved', 'assets/uploads/kyc/1780079109_kyc_50_1780078845_6262.jpg', 1, 0.00, '2026-05-29 18:02:41'),
(51, 'Subhash Sinha', 'subhashsinha00007@gmail.com', '$2y$10$7xP9U.owAJoaLRhZSc7T3.n6XZTCaM1x17ODlMZJ5TGgfM2m4gfcm', '07022738629', 'Pachdali roads, Beratuk Bisnupriya Manipuri Mandir\r\nBeratuk mandir', 'user', 'approved', 'assets/uploads/kyc/kyc_51_1780161249_9292.jpg', 1, 0.00, '2026-05-30 17:08:15'),
(52, 'Brojendra Sinha', 'brojensinha620@gmail.com', '$2y$10$P03rzaSVkNlFNnpR6dwYfOS8yk/zGODhcNvjLzs6DBv/cWaJ/Q8Xq', '8638868134', 'Vill Amarkhal \r\nPo Anipur \r\nDist Sribhumi \r\nPin 788734', 'user', 'approved', 'assets/uploads/kyc/kyc_52_1780320488_7996.jpg', 1, 100.00, '2026-06-01 13:15:59'),
(53, 'Siumit Kumar Sinha', 'sumitkrsinha271@gmail.com', '$2y$10$s5w9eowKXF0o.r2nsLzCdeizT.VjKeacBdfKkIMgHV62rXWH9Lfwi', '9863052779', 'Vill.  Bethubari  PO.  Ratabari \r\nDist. Sribhumi (Paschim Singla)  PIN  788735', 'user', 'approved', NULL, 1, 0.00, '2026-06-02 15:04:02'),
(54, 'Biduth sinha', 'biduthsinha97@gmail.com', '$2y$10$inpfV4fVEYyyHmX0G25VO.KG4WvhjFrxvLCzaeLSiJ7EDUveN5Ojq', '8011979691', 'Vill-singari, patharkandi, karimganj, assam', 'user', 'approved', NULL, 1, 0.00, '2026-06-02 15:06:38'),
(55, 'Mrinal Singha', 'mrinalsingha03333@gmail.com', '$2y$10$sl7eUEG/FSjBoH9jrWcH8.aOjNS098j2THfR/BJCAvtgav1pSaS3i', '8761047463', 'Guwahati', 'user', 'approved', NULL, 1, 0.00, '2026-06-11 18:33:25'),
(56, 'Vinod sinha', 'sinhamolina8@gmail.com', '$2y$10$dHNt9UvDxu/Ngm20Ba2mm.bEUUSb.vbNrm01g65a6l.gOyu1fcQEi', '7083089123', 'Pune,maharashtra', 'user', 'approved', NULL, 1, 0.00, '2026-06-11 18:44:26'),
(57, 'Gopi Mohan Sinha', 'gopimohansinha1962@gmail.com', '$2y$10$E/YPnyajz3n9bGx8yG7geuOth9GKCEwZyrAW32iV4kWU.SW4cmeS2', '9974211462', 'Vill-Shibergool,PO-Bazari Cherra, Dist-Sribhumi(Assam),Pin-788727', 'user', 'approved', NULL, 1, 0.00, '2026-06-11 18:54:48'),
(58, 'xyz', 'xyz@mail.com', '$2y$10$VWvfFiC/Kyxc41YyhCr/tePN17EMKVwZgcXn9BFPKo9xoyDFo7nlO', '', 'Dullabcherra', 'user', 'approved', NULL, 1, 0.00, '2026-06-12 05:31:03');

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

--
-- Stand-in structure for view `vw_donation_summary`
-- (See below for the actual view)
--
CREATE TABLE `vw_donation_summary` (
`user_id` int(11)
,`name` varchar(100)
,`email` varchar(100)
,`mobile` varchar(15)
,`kyc_status` enum('pending','approved','rejected')
,`membership_active` tinyint(1)
,`total_donations_count` bigint(21)
,`total_donation_amount` decimal(32,2)
,`last_donation_date` timestamp
,`member_since` timestamp
);

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

--
-- Stand-in structure for view `vw_fund_summary`
-- (See below for the actual view)
--
CREATE TABLE `vw_fund_summary` (
`month` varchar(7)
,`distribution_count` bigint(21)
,`total_distributed` decimal(32,2)
);

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

--
-- Stand-in structure for view `vw_member_stats`
-- (See below for the actual view)
--
CREATE TABLE `vw_member_stats` (
`total_members` bigint(21)
,`approved_kyc` decimal(23,0)
,`pending_kyc` decimal(23,0)
,`rejected_kyc` decimal(23,0)
,`active_memberships` decimal(23,0)
,`inactive_memberships` decimal(23,0)
);

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

--
-- Stand-in structure for view `vw_total_funds`
-- (See below for the actual view)
--
CREATE TABLE `vw_total_funds` (
`total_donations` decimal(32,2)
,`total_additions` decimal(32,2)
,`total_funds_received` decimal(33,2)
,`total_distributed` decimal(32,2)
,`available_balance` decimal(34,2)
);

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

--
-- Table structure for table `withdrawal_requests`
--

CREATE TABLE `withdrawal_requests` (
  `id` int(11) NOT NULL,
  `requested_by` int(11) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `reason` text DEFAULT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `requested_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `balance_additions`
--
ALTER TABLE `balance_additions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_added_at` (`added_at`),
  ADD KEY `idx_balance_added_by` (`added_by`);

--
-- Indexes for table `cancellation_requests`
--
ALTER TABLE `cancellation_requests`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `donations`
--
ALTER TABLE `donations`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user_id` (`user_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_donations_user_status` (`user_id`,`status`),
  ADD KEY `idx_donations_uploaded` (`uploaded_at`);

--
-- Indexes for table `fund_distributions`
--
ALTER TABLE `fund_distributions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_date` (`date`);

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

--
-- Indexes for table `org_signatures`
--
ALTER TABLE `org_signatures`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_position` (`position`);

--
-- Indexes for table `password_resets`
--
ALTER TABLE `password_resets`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `idx_users_role` (`role`),
  ADD KEY `idx_users_kyc` (`kyc_status`);

--
-- Indexes for table `withdrawal_requests`
--
ALTER TABLE `withdrawal_requests`
  ADD PRIMARY KEY (`id`),
  ADD KEY `requested_by` (`requested_by`),
  ADD KEY `idx_status` (`status`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `balance_additions`
--
ALTER TABLE `balance_additions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `cancellation_requests`
--
ALTER TABLE `cancellation_requests`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

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

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

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

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

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

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

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

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

--
-- Structure for view `vw_donation_summary`
--
DROP TABLE IF EXISTS `vw_donation_summary`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_donation_summary`  AS SELECT `u`.`id` AS `user_id`, `u`.`name` AS `name`, `u`.`email` AS `email`, `u`.`mobile` AS `mobile`, `u`.`kyc_status` AS `kyc_status`, `u`.`membership_active` AS `membership_active`, count(`d`.`id`) AS `total_donations_count`, coalesce(sum(`d`.`amount`),0) AS `total_donation_amount`, max(`d`.`uploaded_at`) AS `last_donation_date`, `u`.`created_at` AS `member_since` FROM (`users` `u` left join `donations` `d` on(`u`.`id` = `d`.`user_id` and `d`.`status` = 'verified')) WHERE `u`.`role` = 'user' GROUP BY `u`.`id`, `u`.`name`, `u`.`email`, `u`.`mobile`, `u`.`kyc_status`, `u`.`membership_active`, `u`.`created_at` ;

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

--
-- Structure for view `vw_fund_summary`
--
DROP TABLE IF EXISTS `vw_fund_summary`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_fund_summary`  AS SELECT date_format(`fund_distributions`.`date`,'%Y-%m') AS `month`, count(0) AS `distribution_count`, sum(`fund_distributions`.`amount`) AS `total_distributed` FROM `fund_distributions` GROUP BY date_format(`fund_distributions`.`date`,'%Y-%m') ORDER BY date_format(`fund_distributions`.`date`,'%Y-%m') DESC ;

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

--
-- Structure for view `vw_member_stats`
--
DROP TABLE IF EXISTS `vw_member_stats`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_member_stats`  AS SELECT count(0) AS `total_members`, sum(case when `users`.`kyc_status` = 'approved' then 1 else 0 end) AS `approved_kyc`, sum(case when `users`.`kyc_status` = 'pending' then 1 else 0 end) AS `pending_kyc`, sum(case when `users`.`kyc_status` = 'rejected' then 1 else 0 end) AS `rejected_kyc`, sum(case when `users`.`membership_active` = 1 then 1 else 0 end) AS `active_memberships`, sum(case when `users`.`membership_active` = 0 then 1 else 0 end) AS `inactive_memberships` FROM `users` WHERE `users`.`role` = 'user' ;

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

--
-- Structure for view `vw_total_funds`
--
DROP TABLE IF EXISTS `vw_total_funds`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_total_funds`  AS SELECT (select coalesce(sum(`donations`.`amount`),0) from `donations` where `donations`.`status` = 'verified') AS `total_donations`, (select coalesce(sum(`balance_additions`.`amount`),0) from `balance_additions`) AS `total_additions`, (select coalesce(sum(`donations`.`amount`),0) from `donations` where `donations`.`status` = 'verified') + (select coalesce(sum(`balance_additions`.`amount`),0) from `balance_additions`) AS `total_funds_received`, (select coalesce(sum(`fund_distributions`.`amount`),0) from `fund_distributions`) AS `total_distributed`, (select coalesce(sum(`donations`.`amount`),0) from `donations` where `donations`.`status` = 'verified') + (select coalesce(sum(`balance_additions`.`amount`),0) from `balance_additions`) - (select coalesce(sum(`fund_distributions`.`amount`),0) from `fund_distributions`) AS `available_balance` ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `balance_additions`
--
ALTER TABLE `balance_additions`
  ADD CONSTRAINT `balance_additions_ibfk_1` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

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

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

--
-- Constraints for table `kyc_rejection_logs`
--
ALTER TABLE `kyc_rejection_logs`
  ADD CONSTRAINT `kyc_rejection_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `kyc_rejection_logs_ibfk_2` FOREIGN KEY (`rejected_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `withdrawal_requests`
--
ALTER TABLE `withdrawal_requests`
  ADD CONSTRAINT `withdrawal_requests_ibfk_1` FOREIGN KEY (`requested_by`) REFERENCES `users` (`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 */;
