-- phpMyAdmin SQL Dump -- version 5.2.1 -- https://www.phpmyadmin.net/ -- -- Host: 127.0.0.1 -- Generation Time: Jul 13, 2026 at 11:41 AM -- Server version: 10.4.32-MariaDB -- PHP Version: 8.2.12 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: `society_db` -- -- -------------------------------------------------------- -- -- Table structure for table `activity_log` -- DROP TABLE IF EXISTS `activity_log`; CREATE TABLE `activity_log` ( `id` int(10) UNSIGNED NOT NULL, `society_id` int(10) UNSIGNED NOT NULL, `activity_type` enum('maintenance_payment','expense_added','announcement_posted','member_registered') NOT NULL, `reference_id` int(10) UNSIGNED DEFAULT NULL COMMENT 'ID of related record', `title` varchar(200) NOT NULL, `subtitle` varchar(300) DEFAULT NULL, `amount` decimal(12,2) DEFAULT NULL COMMENT '+ve for income, -ve for expense', `done_by` varchar(150) DEFAULT NULL COMMENT 'Display name of actor', `activity_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `activity_log` -- INSERT INTO `activity_log` (`id`, `society_id`, `activity_type`, `reference_id`, `title`, `subtitle`, `amount`, `done_by`, `activity_at`) VALUES (1, 1, 'member_registered', 4, 'New Member Registered', 'Pending admin approval', NULL, 'Rohit Kumar', '2026-07-08 11:12:08'), (2, 1, 'announcement_posted', 1, 'Announcement Posted', 'Water Supply Notice', NULL, 'Super Admin', '2026-07-09 04:54:49'), (3, 1, 'announcement_posted', 2, 'Announcement Posted', 'Water Supply Notice', NULL, 'Super Admin', '2026-07-09 05:26:21'), (4, 1, 'expense_added', 1, 'Expense Added', 'Electricity bill for July — Jaipur Vidyut Nigam', -12000.00, 'Neeshu Verma', '2026-07-09 05:43:10'), (5, 1, 'expense_added', 2, 'Expense Added', 'Electricity bill for July — Jaipur Vidyut Nigam', -12000.00, 'Neeshu Verma', '2026-07-09 05:54:53'), (6, 1, 'member_registered', 1, 'New Member Registered', 'Pending admin approval', NULL, 'Rohit Sharma', '2026-07-09 06:16:45'), (7, 1, 'maintenance_payment', 1, 'Maintenance Payment Submitted', 'Pending approval', 2500.00, NULL, '2026-07-09 06:54:29'), (8, 1, 'member_registered', 7, 'New Member Registered', 'Pending admin approval', NULL, 'Pooja', '2026-07-09 09:55:16'), (9, 6, 'expense_added', 3, 'Expense Added', 'Electricity bill for July — Jaipur Vidyut Nigam', -20000.00, NULL, '2026-07-10 09:11:46'), (10, 6, 'expense_added', 4, 'Expense Added', 'Electricity bill for July — Jaipur Vidyut Nigam', -20000.00, NULL, '2026-07-10 09:12:22'), (11, 5, 'expense_added', 5, 'Expense Added', 'Water bill for July — Jaipur Water House', -10000.00, NULL, '2026-07-10 09:17:29'), (12, 6, 'expense_added', 6, 'Expense Added', 'Water bill for July — Jaipur Water House', -10000.00, NULL, '2026-07-10 09:28:29'), (13, 6, 'expense_added', 7, 'Expense Added', 'Gardening bill for Augst — Garden mainten team', -15000.00, NULL, '2026-07-10 09:30:59'), (14, 5, 'announcement_posted', 3, 'Announcement Posted', 'Rakshabandhan', NULL, NULL, '2026-07-10 10:05:13'); -- -------------------------------------------------------- -- -- Table structure for table `announcements` -- DROP TABLE IF EXISTS `announcements`; CREATE TABLE `announcements` ( `id` int(10) UNSIGNED NOT NULL, `society_id` int(10) UNSIGNED NOT NULL, `posted_by` int(10) UNSIGNED NOT NULL COMMENT 'FK → society_members.id', `title` varchar(255) NOT NULL, `body` text NOT NULL, `category` enum('general','maintenance','events') NOT NULL DEFAULT 'general', `priority` enum('normal','urgent') NOT NULL DEFAULT 'normal', `has_attachment` tinyint(1) NOT NULL DEFAULT 0, `attachment_url` varchar(255) DEFAULT NULL, `status` tinyint(1) NOT NULL DEFAULT 1, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `announcements` -- INSERT INTO `announcements` (`id`, `society_id`, `posted_by`, `title`, `body`, `category`, `priority`, `has_attachment`, `attachment_url`, `status`, `created_at`, `updated_at`) VALUES (1, 1, 1, 'Updated Water Supply Notice', 'Water supply will remain off tomorrow from 10 AM to 2 PM.', 'maintenance', 'normal', 0, NULL, 0, '2026-07-09 04:54:49', '2026-07-09 05:15:21'), (2, 1, 1, 'Water Supply Notice', 'Water supply will remain off tomorrow from 10 AM to 2 PM.', 'maintenance', 'urgent', 0, NULL, 1, '2026-07-09 05:26:21', '2026-07-09 05:26:21'), (3, 5, 0, 'Rakshabandhan', 'happy rakshbandhan ', '', 'normal', 1, '/uploads/announcement/1783677913491-303193111.jpg', 1, '2026-07-10 10:05:13', '2026-07-10 10:05:13'); -- -------------------------------------------------------- -- -- Table structure for table `expenses` -- DROP TABLE IF EXISTS `expenses`; CREATE TABLE `expenses` ( `id` int(10) UNSIGNED NOT NULL, `society_id` int(10) UNSIGNED NOT NULL, `added_by` int(10) UNSIGNED NOT NULL COMMENT 'FK → society_members.id', `category` enum('maintenance','electricity','security','water','gardening','other') NOT NULL, `description` varchar(500) NOT NULL, `vendor` varchar(200) DEFAULT NULL COMMENT 'Paid To', `amount` decimal(12,2) NOT NULL, `expense_date` date NOT NULL, `payment_mode` enum('upi','cash','bank_transfer','cheque','other') NOT NULL DEFAULT 'upi', `receipt_url` varchar(255) DEFAULT NULL, `status` tinyint(1) NOT NULL DEFAULT 1, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `expenses` -- INSERT INTO `expenses` (`id`, `society_id`, `added_by`, `category`, `description`, `vendor`, `amount`, `expense_date`, `payment_mode`, `receipt_url`, `status`, `created_at`, `updated_at`) VALUES (1, 1, 1, 'electricity', 'Updated electricity bill', 'Jaipur Vidyut Nigam', 15000.00, '2026-07-09', 'upi', '', 0, '2026-07-09 05:43:10', '2026-07-09 05:53:54'), (2, 1, 1, 'electricity', 'Electricity bill for July', 'Jaipur Vidyut Nigam', 12000.00, '2026-07-09', 'upi', '', 1, '2026-07-09 05:54:53', '2026-07-09 05:54:53'), (3, 6, 0, 'electricity', 'Electricity bill for July', 'Jaipur Vidyut Nigam', 20000.00, '2026-07-10', 'upi', '/uploads/expense/1783674706090-51765767.jpg', 1, '2026-07-10 09:11:46', '2026-07-10 09:11:46'), (4, 6, 0, 'electricity', 'Electricity bill for July', 'Jaipur Vidyut Nigam', 20000.00, '2026-07-10', 'upi', '/uploads/expense/1783674742576-428006540.jpg', 1, '2026-07-10 09:12:22', '2026-07-10 09:12:22'), (5, 5, 0, 'water', 'Water bill for July', 'Jaipur Water House', 10000.00, '2026-07-20', 'cash', '/uploads/expense/1783675049215-974098635.jpg', 1, '2026-07-10 09:17:29', '2026-07-10 09:19:55'), (6, 6, 0, 'gardening', 'Water bill for July', 'Jaipur Water House', 10000.00, '2026-07-20', 'upi', '/uploads/expense/1783675709291-407476769.jpg', 1, '2026-07-10 09:28:29', '2026-07-10 09:28:29'), (7, 6, 0, 'gardening', 'Gardening bill for Augst', 'Garden mainten team', 15000.00, '2026-07-20', 'upi', '/uploads/expense/1783675859416-348913581.jpg', 1, '2026-07-10 09:30:59', '2026-07-10 09:30:59'); -- -------------------------------------------------------- -- -- Table structure for table `maintenance_payments` -- DROP TABLE IF EXISTS `maintenance_payments`; CREATE TABLE `maintenance_payments` ( `id` int(10) UNSIGNED NOT NULL, `society_id` int(10) UNSIGNED NOT NULL, `member_id` int(10) UNSIGNED NOT NULL COMMENT 'FK → society_members.id', `amount` decimal(12,2) NOT NULL, `for_month` date NOT NULL COMMENT 'Store first day of month, e.g. 2023-10-01', `payment_mode` enum('upi','cash','bank_transfer','cheque','other') NOT NULL DEFAULT 'upi', `transaction_number` varchar(100) DEFAULT NULL COMMENT 'UTR / reference number', `remark` varchar(300) DEFAULT NULL COMMENT 'Note for treasurer', `screenshot_url` varchar(255) DEFAULT NULL COMMENT 'Payment screenshot upload', `approval_status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending', `approved_by` int(10) UNSIGNED DEFAULT NULL COMMENT 'FK → users.id who approved', `approved_at` timestamp NULL DEFAULT NULL, `reference_no` varchar(100) DEFAULT NULL, `notes` varchar(300) DEFAULT NULL, `status` tinyint(1) NOT NULL DEFAULT 1, `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `maintenance_payments` -- INSERT INTO `maintenance_payments` (`id`, `society_id`, `member_id`, `amount`, `for_month`, `payment_mode`, `transaction_number`, `remark`, `screenshot_url`, `approval_status`, `approved_by`, `approved_at`, `reference_no`, `notes`, `status`, `created_at`) VALUES (1, 1, 2, 2500.00, '2026-07-01', 'upi', 'TXN123456789', 'July Maintenance', 'https://example.com/payment.jpg', 'approved', 5, '2026-07-09 01:28:16', NULL, NULL, 1, '2026-07-09 06:54:29'); -- -------------------------------------------------------- -- -- Table structure for table `society` -- DROP TABLE IF EXISTS `society`; CREATE TABLE `society` ( `id` int(10) UNSIGNED NOT NULL, `name` varchar(200) NOT NULL COMMENT 'e.g. Orchid Towers', `registration_no` varchar(100) DEFAULT NULL COMMENT 'Society registration certificate number', `type` enum('residential_apartment','gated_community','cooperative_housing','villa_township','commercial','other') NOT NULL DEFAULT 'residential_apartment', `address` text DEFAULT NULL, `city` varchar(100) NOT NULL, `state` varchar(100) NOT NULL, `pincode` varchar(10) DEFAULT NULL, `country` varchar(100) NOT NULL DEFAULT 'India', `email` varchar(150) DEFAULT NULL, `phone` varchar(15) DEFAULT NULL, `website` varchar(255) DEFAULT NULL, `total_units` smallint(5) UNSIGNED DEFAULT NULL COMMENT 'Total flats / units', `wings` varchar(100) DEFAULT NULL COMMENT 'Comma-separated wing labels, e.g. A,B,C', `logo_url` varchar(255) DEFAULT NULL, `description` text DEFAULT NULL, `admin_member_id` int(10) UNSIGNED DEFAULT NULL COMMENT 'FK → society_members.id (primary admin)', `plan` enum('free','basic','pro','enterprise') NOT NULL DEFAULT 'free', `plan_expires_at` date DEFAULT NULL, `registration_request_id` int(10) UNSIGNED DEFAULT NULL COMMENT 'FK → society_registration_requests.id', `status` enum('active','suspended','inactive') NOT NULL DEFAULT 'active', `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `society` -- INSERT INTO `society` (`id`, `name`, `registration_no`, `type`, `address`, `city`, `state`, `pincode`, `country`, `email`, `phone`, `website`, `total_units`, `wings`, `logo_url`, `description`, `admin_member_id`, `plan`, `plan_expires_at`, `registration_request_id`, `status`, `created_at`, `updated_at`) VALUES (2, 'Green Valley Society', 'REG001', 'residential_apartment', 'Vaishali Nagar', 'Jaipur', 'Rajasthan', '302021', 'India', 'society@test.com', '3456789876', 'https://greenvalley.in', 100, 'A,B,C', NULL, NULL, NULL, 'pro', NULL, NULL, 'active', '2026-07-08 09:33:43', '2026-07-10 04:48:11'), (3, 'Green Valley', NULL, 'residential_apartment', NULL, 'Jaipur', 'Rajasthan', NULL, 'India', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 6, 'basic', NULL, 1, 'active', '2026-07-09 04:24:12', '2026-07-09 04:24:13'), (4, 'Green Valley', NULL, 'residential_apartment', NULL, 'Jaipur', 'Rajasthan', NULL, 'India', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9, 'basic', NULL, 2, 'active', '2026-07-09 10:33:55', '2026-07-09 10:33:55'), (5, 'Gandhi Nagar', NULL, 'residential_apartment', NULL, 'Jaipur', 'Rajasthan', NULL, 'India', NULL, '3456789876', 'https://gandhinagar.in', NULL, NULL, '1783661117969-700798987.jpg', NULL, 10, 'basic', NULL, 3, 'active', '2026-07-09 10:36:54', '2026-07-10 05:25:17'), (6, 'Orchid Towers CHS Ltd.', 'MUM/CHS/2019/1024', 'residential_apartment', '12 MG Road, Orchid Block, Banglore 560001', 'Banglore', 'Karnataka', NULL, 'India', NULL, '9999995443', 'https://orchid.in', 102, NULL, '1783668103911-297649554.jpg', NULL, 13, 'pro', NULL, 4, 'active', '2026-07-10 07:18:57', '2026-07-10 07:21:43'); -- -------------------------------------------------------- -- -- Table structure for table `society_join_requests` -- DROP TABLE IF EXISTS `society_join_requests`; CREATE TABLE `society_join_requests` ( `id` int(10) UNSIGNED NOT NULL, `society_id` int(10) UNSIGNED NOT NULL, `full_name` varchar(150) NOT NULL, `mobile_number` varchar(15) NOT NULL, `email` varchar(150) DEFAULT NULL, `id_proof_url` varchar(255) DEFAULT NULL COMMENT 'Aadhaar / PAN upload', `id_proof_type` enum('aadhaar','pan','passport','other') DEFAULT NULL, `wing` varchar(10) DEFAULT NULL, `flat_number` varchar(20) DEFAULT NULL, `ownership_type` enum('owner','tenant') DEFAULT 'owner', `additional_docs` varchar(255) DEFAULT NULL COMMENT 'Any extra document URL', `remarks` varchar(300) DEFAULT NULL, `step_completed` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1, 2 or 3', `status` enum('draft','submitted','approved','rejected') NOT NULL DEFAULT 'draft', `reviewed_by` int(10) UNSIGNED DEFAULT NULL COMMENT 'FK → users.id', `reviewed_at` timestamp NULL DEFAULT NULL, `rejection_note` varchar(300) DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `society_join_requests` -- INSERT INTO `society_join_requests` (`id`, `society_id`, `full_name`, `mobile_number`, `email`, `id_proof_url`, `id_proof_type`, `wing`, `flat_number`, `ownership_type`, `additional_docs`, `remarks`, `step_completed`, `status`, `reviewed_by`, `reviewed_at`, `rejection_note`, `created_at`, `updated_at`) VALUES (1, 1, 'Rohit Sharma', '9876543211', 'rohit@gmail.com', 'https://abc.com/aadhar.jpg', '', 'A', '101', 'owner', 'https://abc.com/doc.pdf', 'Please approve', 3, 'rejected', 5, '2026-07-09 01:10:05', 'Documents not valid', '2026-07-09 06:08:44', '2026-07-09 06:40:05'), (2, 1, 'Sandeep', '9876789876', 'sandeep@gmail.com', 'https://abc.com/aadhar.jpg', '', NULL, NULL, 'owner', NULL, NULL, 1, 'draft', NULL, NULL, NULL, '2026-07-09 09:50:37', '2026-07-09 09:50:37'); -- -------------------------------------------------------- -- -- Table structure for table `society_registration_requests` -- DROP TABLE IF EXISTS `society_registration_requests`; CREATE TABLE `society_registration_requests` ( `id` int(10) UNSIGNED NOT NULL, `contact_name` varchar(150) NOT NULL COMMENT 'Name of the person applying', `contact_mobile` varchar(15) NOT NULL, `contact_email` varchar(150) NOT NULL, `designation` varchar(100) DEFAULT NULL COMMENT 'e.g. Secretary, Chairman', `society_name` varchar(200) NOT NULL, `society_type` enum('residential_apartment','gated_community','cooperative_housing','villa_township','commercial','other') NOT NULL DEFAULT 'residential_apartment', `registration_no` varchar(100) DEFAULT NULL, `total_units` smallint(5) UNSIGNED DEFAULT NULL, `wings` varchar(100) DEFAULT NULL COMMENT 'e.g. A,B,C', `address` text DEFAULT NULL, `city` varchar(100) NOT NULL, `state` varchar(100) NOT NULL, `pincode` varchar(10) DEFAULT NULL, `country` varchar(100) NOT NULL DEFAULT 'India', `society_email` varchar(150) DEFAULT NULL, `society_phone` varchar(15) DEFAULT NULL, `website` varchar(255) DEFAULT NULL, `reg_certificate_url` varchar(255) DEFAULT NULL COMMENT 'Society registration certificate upload', `other_docs_url` varchar(255) DEFAULT NULL, `description` text DEFAULT NULL, `status` enum('pending','under_review','approved','rejected') NOT NULL DEFAULT 'pending', `reviewed_by` int(10) UNSIGNED DEFAULT NULL COMMENT 'FK → society_members.id (superadmin)', `reviewed_at` timestamp NULL DEFAULT NULL, `review_notes` text DEFAULT NULL COMMENT 'Superadmin feedback / rejection reason', `society_id` int(10) UNSIGNED DEFAULT NULL COMMENT 'FK → society.id — set after approval', `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `logo_url` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `society_registration_requests` -- INSERT INTO `society_registration_requests` (`id`, `contact_name`, `contact_mobile`, `contact_email`, `designation`, `society_name`, `society_type`, `registration_no`, `total_units`, `wings`, `address`, `city`, `state`, `pincode`, `country`, `society_email`, `society_phone`, `website`, `reg_certificate_url`, `other_docs_url`, `description`, `status`, `reviewed_by`, `reviewed_at`, `review_notes`, `society_id`, `created_at`, `updated_at`, `logo_url`) VALUES (1, 'Rahul Sharma', '9876543210', 'rahul@gmail.com', NULL, 'Green Valley', 'residential_apartment', NULL, NULL, NULL, NULL, 'Jaipur', 'Rajasthan', NULL, 'India', NULL, NULL, NULL, NULL, NULL, NULL, 'approved', 5, '2026-07-08 22:54:12', NULL, 3, '2026-07-08 12:04:08', '2026-07-09 04:24:13', NULL), (2, 'Rahul Sharma', '9876543210', 'rahul@gmail.com', NULL, 'Green Valley', 'residential_apartment', NULL, NULL, NULL, NULL, 'Jaipur', 'Rajasthan', NULL, 'India', NULL, NULL, NULL, NULL, NULL, NULL, 'approved', 5, '2026-07-09 05:03:55', NULL, 4, '2026-07-09 10:31:09', '2026-07-09 10:33:55', NULL), (3, 'Manisha', '9999995443', 'manisha@gmail.com', NULL, 'Gandhi Nagar', 'residential_apartment', NULL, NULL, NULL, NULL, 'Jaipur', 'Rajasthan', NULL, 'India', NULL, NULL, NULL, NULL, NULL, NULL, 'approved', 5, '2026-07-09 05:06:54', NULL, 5, '2026-07-09 10:36:20', '2026-07-09 10:36:54', NULL), (4, 'Manisha', '9999995443', 'manisha@gmail.com', NULL, 'Orchid Towers CHS Ltd.', 'residential_apartment', 'MUM/CHS/2019/1024', 102, NULL, '12 MG Road, Orchid Block, Banglore 560001', 'Banglore', 'Karnataka', NULL, 'India', NULL, NULL, 'https://orchid.in', NULL, NULL, NULL, 'approved', 5, '2026-07-10 01:48:57', NULL, 6, '2026-07-10 07:04:27', '2026-07-10 07:18:58', NULL); -- -------------------------------------------------------- -- -- Table structure for table `users` -- DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `id` int(10) UNSIGNED NOT NULL, `society_id` int(10) UNSIGNED NOT NULL, `full_name` varchar(150) NOT NULL, `email` varchar(150) NOT NULL, `mobile_number` varchar(15) NOT NULL, `wing` varchar(10) NOT NULL COMMENT 'e.g. A, B, C', `flat_number` varchar(20) NOT NULL COMMENT 'e.g. 101, 205', `role` enum('superadmin','admin','member') NOT NULL DEFAULT 'member', `profile_photo` varchar(255) DEFAULT NULL, `password_hash` varchar(255) DEFAULT NULL COMMENT 'bcrypt hash; NULL = OTP-only login', `fcm_token` varchar(255) DEFAULT NULL COMMENT 'Firebase push-notification token', `last_login_at` timestamp NULL DEFAULT NULL, `member_since` date DEFAULT NULL, `is_approved` tinyint(1) NOT NULL DEFAULT 0, `is_active` tinyint(1) NOT NULL DEFAULT 1 COMMENT '0 = deactivated, 1 = active', `status` tinyint(1) NOT NULL DEFAULT 1, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `users` -- INSERT INTO `users` (`id`, `society_id`, `full_name`, `email`, `mobile_number`, `wing`, `flat_number`, `role`, `profile_photo`, `password_hash`, `fcm_token`, `last_login_at`, `member_since`, `is_approved`, `is_active`, `status`, `created_at`, `updated_at`) VALUES (2, 1, 'Test Admin', 'admin@test.com', '9876543210', 'A', '101', 'admin', NULL, '$2a$10$b5.UhZ7UIGKydzAY3EebjetLYqHuEoz2efl5cyh/Y97cGnAT4AIU6', NULL, '2026-07-08 10:53:06', '2026-07-08', 1, 1, 1, '2026-07-08 09:35:10', '2026-07-12 12:40:47'), (3, 1, 'Rahul Sharma', 'rahul@test.com', '9999999999', 'A', '102', 'member', NULL, '$2a$10$ckJaxpicpwuNgAk7QOAWGOR0gKFHamCg4iN4gqT2qTBV04OxoBOcO', NULL, NULL, NULL, 1, 1, 1, '2026-07-08 10:11:52', '2026-07-08 10:11:52'), (4, 1, 'Rohit', 'rohit@test.com', '9876543222', 'C', '305', 'member', NULL, NULL, NULL, NULL, '2026-07-08', 0, 0, 1, '2026-07-08 11:12:08', '2026-07-08 12:21:42'), (5, 1, 'Neeshu Verma', 'superadmin@gmail.com', '9999999998', 'A', '000', 'superadmin', NULL, '$2a$10$gOIxvlpeY/7O5YN74OCPKumuPgxZ8Ym1e/zmOGJJJHlxFctEzSc0a', NULL, '2026-07-09 10:40:04', '2026-07-08', 1, 1, 1, '2026-07-08 12:22:47', '2026-07-09 10:40:04'), (6, 3, 'Rahul Sharma', 'rahul@gmail.com', '9876543210', '', '', 'admin', NULL, '$2a$10$4/ja06fPyE.DR07eSYhw3eM/K/TpKdXnn8hB2fNEuDiXd2KQ6encS', NULL, NULL, '2026-07-09', 1, 1, 1, '2026-07-09 04:24:13', '2026-07-09 04:24:13'), (7, 1, 'Pooja', 'pooja@gmail.com.com', '1234567890', 'B', '205', 'member', NULL, NULL, NULL, NULL, '2026-07-08', 0, 1, 1, '2026-07-09 09:55:16', '2026-07-09 09:55:16'), (8, 1, 'Manisha', 'manisha@gmail.com', '9999995443', 'A', '104', 'member', NULL, '$2a$10$RG/jWBerqKPuu1C5tl1V3uMzjGG1XpRS/WouRgC0CclfK4zeaK5fC', NULL, NULL, NULL, 1, 1, 1, '2026-07-09 10:03:41', '2026-07-09 10:03:41'), (9, 4, 'Rahul Sharma', 'rahul@gmail.com', '9876543210', '', '', 'admin', NULL, '$2a$10$P42IvNiDofnHPymH5DLrtOw1cZztEaArBkB7ZgWDMDSEv8ImA9FzC', NULL, NULL, '2026-07-09', 1, 1, 1, '2026-07-09 10:33:55', '2026-07-09 10:33:55'), (10, 5, 'Manisha', 'manisha@gmail.com', '9999995443', '', '', 'admin', NULL, '$2a$10$vdmp3sknk0sg0arN6ZELzeNqwrySeNt3./rljn/6DKcSHl5I0iIMu', NULL, NULL, '2026-07-09', 1, 1, 1, '2026-07-09 10:36:54', '2026-07-09 10:36:54'), (11, 1, 'Rekha', 'rekha@gmail.com', '1232134543', 'A', '105', 'member', NULL, '$2a$10$pSLhbqM.5pa4NVAz9KBixuLTudvMXky1fvdEONMH.ujyMSSw12P/q', NULL, NULL, NULL, 1, 1, 1, '2026-07-09 11:50:31', '2026-07-09 11:50:31'), (12, 2, 'Rekha', 'rekha01@gmail.com', '1232130003', 'B', '302', 'member', '/uploads/profile/1783599588390-649764953.jpg', NULL, NULL, NULL, NULL, 1, 1, 1, '2026-07-09 12:19:48', '2026-07-09 12:19:48'), (13, 6, 'Manisha', 'manisha@gmail.com', '9999995443', '', '', 'admin', NULL, '$2a$10$30HRd3qxQoiuVNDM92OSmePKfbNJUh5jjlTOUdgZwm7BLYnBOj6B.', NULL, NULL, '2026-07-10', 1, 1, 1, '2026-07-10 07:18:58', '2026-07-12 12:17:53'); -- -------------------------------------------------------- -- -- Table structure for table `user_otps` -- DROP TABLE IF EXISTS `user_otps`; CREATE TABLE `user_otps` ( `id` int(10) UNSIGNED NOT NULL, `mobile_number` varchar(15) NOT NULL, `society_id` int(10) UNSIGNED NOT NULL, `member_id` int(10) UNSIGNED DEFAULT NULL COMMENT 'FK → society_members.id', `otp` varchar(10) NOT NULL, `purpose` enum('login','forgot_password') NOT NULL DEFAULT 'login', `device_id` varchar(255) DEFAULT NULL, `is_verified` tinyint(1) NOT NULL DEFAULT 0, `expire_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `resend_count` int(11) NOT NULL DEFAULT 0, `locked_until` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `user_otps` -- INSERT INTO `user_otps` (`id`, `mobile_number`, `society_id`, `member_id`, `otp`, `purpose`, `device_id`, `is_verified`, `expire_at`, `created_at`, `resend_count`, `locked_until`) VALUES (1, '9876543210', 1, 2, '248098', 'login', NULL, 1, '2026-07-08 10:31:02', '2026-07-08 10:27:39', 0, NULL), (2, '9876543210', 1, 2, '525698', 'login', NULL, 1, '2026-07-08 10:53:06', '2026-07-08 10:52:51', 0, NULL), (8, '9876543210', 1, 2, '131043', 'forgot_password', NULL, 1, '2026-07-13 04:36:45', '2026-07-13 04:34:52', 0, NULL), (9, '9876543210', 1, 2, '372649', 'forgot_password', NULL, 1, '2026-07-13 05:06:05', '2026-07-13 04:36:45', 0, NULL), (10, '9876543210', 1, 2, '934966', 'forgot_password', NULL, 1, '2026-07-13 05:06:38', '2026-07-13 05:06:05', 1, NULL), (11, '9876543210', 1, 2, '340530', 'forgot_password', NULL, 1, '2026-07-13 05:06:56', '2026-07-13 05:06:38', 2, NULL), (12, '9876543210', 1, 2, '682670', 'forgot_password', NULL, 1, '2026-07-13 06:16:18', '2026-07-13 05:06:56', 3, '2026-07-13 11:06:56'), (13, '1232130003', 2, 12, '750379', 'forgot_password', NULL, 0, '2026-07-13 05:28:58', '2026-07-13 05:23:58', 0, NULL), (14, '9876543210', 1, 2, '093824', 'forgot_password', NULL, 1, '2026-07-13 06:22:59', '2026-07-13 06:16:18', 1, NULL), (15, '9876543210', 1, 2, '104859', 'forgot_password', NULL, 1, '2026-07-13 06:28:07', '2026-07-13 06:22:59', 2, NULL), (16, '9876543210', 1, 2, '942595', 'forgot_password', NULL, 1, '2026-07-13 07:21:17', '2026-07-13 06:28:07', 3, '2026-07-13 12:28:07'), (17, '9876543210', 1, 2, '778073', 'forgot_password', NULL, 1, '2026-07-13 08:39:07', '2026-07-13 07:21:17', 4, '2026-07-13 13:51:17'), (18, '9876543210', 1, 2, '228740', 'forgot_password', NULL, 0, '2026-07-13 08:41:07', '2026-07-13 08:39:07', 5, '2026-07-14 14:09:07'); -- -- Indexes for dumped tables -- -- -- Indexes for table `activity_log` -- ALTER TABLE `activity_log` ADD PRIMARY KEY (`id`), ADD KEY `idx_society_id` (`society_id`), ADD KEY `idx_activity_type` (`activity_type`), ADD KEY `idx_activity_at` (`activity_at`); -- -- Indexes for table `announcements` -- ALTER TABLE `announcements` ADD PRIMARY KEY (`id`), ADD KEY `idx_society_id` (`society_id`), ADD KEY `idx_category` (`category`), ADD KEY `idx_priority` (`priority`), ADD KEY `idx_created_at` (`created_at`); -- -- Indexes for table `expenses` -- ALTER TABLE `expenses` ADD PRIMARY KEY (`id`), ADD KEY `idx_society_id` (`society_id`), ADD KEY `idx_category` (`category`), ADD KEY `idx_expense_date` (`expense_date`); -- -- Indexes for table `maintenance_payments` -- ALTER TABLE `maintenance_payments` ADD PRIMARY KEY (`id`), ADD KEY `idx_society_id` (`society_id`), ADD KEY `idx_member_id` (`member_id`), ADD KEY `idx_for_month` (`for_month`); -- -- Indexes for table `society` -- ALTER TABLE `society` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uq_registration_no` (`registration_no`), ADD KEY `idx_city` (`city`), ADD KEY `idx_status` (`status`); -- -- Indexes for table `society_join_requests` -- ALTER TABLE `society_join_requests` ADD PRIMARY KEY (`id`), ADD KEY `idx_society_id` (`society_id`), ADD KEY `idx_mobile_number` (`mobile_number`), ADD KEY `idx_status` (`status`); -- -- Indexes for table `society_registration_requests` -- ALTER TABLE `society_registration_requests` ADD PRIMARY KEY (`id`), ADD KEY `idx_status` (`status`), ADD KEY `idx_contact_email` (`contact_email`), ADD KEY `idx_society_name` (`society_name`); -- -- Indexes for table `users` -- ALTER TABLE `users` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uq_society_email` (`society_id`,`email`), ADD UNIQUE KEY `uq_society_mobile` (`society_id`,`mobile_number`), ADD KEY `idx_society_id` (`society_id`), ADD KEY `idx_mobile` (`mobile_number`), ADD KEY `idx_email` (`email`); -- -- Indexes for table `user_otps` -- ALTER TABLE `user_otps` ADD PRIMARY KEY (`id`), ADD KEY `idx_mobile_otp` (`mobile_number`,`otp`), ADD KEY `idx_member_otp` (`member_id`,`otp`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `activity_log` -- ALTER TABLE `activity_log` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15; -- -- AUTO_INCREMENT for table `announcements` -- ALTER TABLE `announcements` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; -- -- AUTO_INCREMENT for table `expenses` -- ALTER TABLE `expenses` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8; -- -- AUTO_INCREMENT for table `maintenance_payments` -- ALTER TABLE `maintenance_payments` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2; -- -- AUTO_INCREMENT for table `society` -- ALTER TABLE `society` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7; -- -- AUTO_INCREMENT for table `society_join_requests` -- ALTER TABLE `society_join_requests` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; -- -- AUTO_INCREMENT for table `society_registration_requests` -- ALTER TABLE `society_registration_requests` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5; -- -- AUTO_INCREMENT for table `users` -- ALTER TABLE `users` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14; -- -- AUTO_INCREMENT for table `user_otps` -- ALTER TABLE `user_otps` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=19; 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 */;