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

START TRANSACTION;



--
-- Database: `hocms`
--

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

--
-- Table structure for table `category_list`
--

CREATE TABLE `category_list` (
  `id` int(30) NOT NULL,
  `name` text NOT NULL,
  `description` text NOT NULL,
  `fee` double NOT NULL DEFAULT 0,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `delete_flag` tinyint(1) NOT NULL DEFAULT 0,
  `date_created` datetime NOT NULL DEFAULT current_timestamp(),
  `date_updated` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `category_list`
--

INSERT INTO `category_list` (`id`, `name`, `description`, `fee`, `status`, `delete_flag`, `date_created`, `date_updated`) VALUES
(1, 'Maintenance', 'Collection for maintenance', 5, 1, 1, '2022-02-08 09:19:39', '2024-02-13 17:46:02'),
(2, 'Security', 'Collection for Security', 200, 1, 1, '2022-02-08 09:20:18', '2024-02-13 17:46:09'),
(3, 'Collection 101', 'Sample Collection only', 100, 1, 1, '2022-02-08 09:21:26', '2024-02-13 17:45:56'),
(4, 'Collection 102', 'Sample Collection 102', 10, 1, 1, '2022-02-08 09:21:43', '2024-02-13 17:45:59'),
(5, 'Reserve Fund', 'This is a sample Reserve Fund', 20, 1, 1, '2022-02-08 09:22:31', '2024-02-13 17:46:05'),
(8, 'monthly used', 'monthly used', 50, 1, 0, '2024-02-13 17:46:56', '2024-02-13 17:47:23'),
(9, 'Water', 'water', 80, 1, 0, '2024-02-13 17:48:13', '2024-02-13 17:49:56');

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

--
-- Table structure for table `collection_items`
--

CREATE TABLE `collection_items` (
  `collection_id` int(30) NOT NULL,
  `category_id` int(30) NOT NULL,
  `fee` double NOT NULL DEFAULT 0,
  `date_added` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `collection_items`
--

INSERT INTO `collection_items` (`collection_id`, `category_id`, `fee`, `date_added`) VALUES
(21, 8, 50, '2024-02-16 21:33:08'),
(21, 9, 80, '2024-02-16 21:33:08');

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

--
-- Table structure for table `collection_list`
--

CREATE TABLE `collection_list` (
  `id` int(30) NOT NULL,
  `code` varchar(100) NOT NULL,
  `member_id` int(30) NOT NULL,
  `total_amount` double NOT NULL DEFAULT 0,
  `date_collected` date NOT NULL,
  `collected_by` text NOT NULL,
  `date_created` datetime NOT NULL DEFAULT current_timestamp(),
  `date_updated` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `collection_list`
--

INSERT INTO `collection_list` (`id`, `code`, `member_id`, `total_amount`, `date_collected`, `collected_by`, `date_created`, `date_updated`) VALUES
(21, '202402-00001', 3, 130, '2024-02-16', 'red', '2024-02-16 21:33:08', NULL);

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

--
-- Table structure for table `member_list`
--

CREATE TABLE `member_list` (
  `id` int(30) NOT NULL,
  `firstname` text NOT NULL,
  `middlename` text DEFAULT NULL,
  `lastname` text NOT NULL,
  `gender` text NOT NULL,
  `contact` text NOT NULL,
  `lot` text NOT NULL,
  `block` text NOT NULL,
  `notes` text DEFAULT NULL,
  `phase_id` int(30) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `delete_flag` tinyint(1) NOT NULL DEFAULT 0,
  `date_created` datetime NOT NULL DEFAULT current_timestamp(),
  `date_updated` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `member_list`
--

INSERT INTO `member_list` (`id`, `firstname`, `middlename`, `lastname`, `gender`, `contact`, `lot`, `block`, `notes`, `phase_id`, `status`, `delete_flag`, `date_created`, `date_updated`) VALUES
(1, 'Mark', 'D', 'Cooper', 'Male', '09123456789', '1', '1', NULL, 1, 0, 1, '2022-02-08 10:14:45', '2024-02-16 21:32:08'),
(2, 'Samantha Jane', 'C', 'Anthony', 'Female', '09456789123', '2', '1', NULL, 1, 0, 1, '2022-02-08 13:22:35', '2024-02-16 21:32:11'),
(3, 'test11', 'test', 'test', 'Male', '09', '6', '6', 'test', 1, 1, 0, '2024-02-16 13:34:23', '2024-02-17 20:49:39'),
(4, 'test', 'test', 'test', 'Male', '09', '1', '1', NULL, 1, 1, 1, '2024-02-16 16:28:10', '2024-02-16 21:32:15'),
(5, 'test', 'test', 'test', 'Male', '09', '2', '1', NULL, 1, 1, 1, '2024-02-16 17:37:12', '2024-02-16 21:32:18'),
(6, 'Mark', '', 'Copper', 'Male', '09', '3', '1', NULL, 1, 1, 1, '2024-02-16 18:00:34', '2024-02-16 21:32:01'),
(7, 'a', 'a', 'a', 'Male', '09', '2', '2', 'test a', 1, 1, 0, '2024-02-17 20:51:29', NULL);

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

--
-- Table structure for table `phase_list`
--

CREATE TABLE `phase_list` (
  `id` int(30) NOT NULL,
  `name` text NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `delete_flag` tinyint(1) NOT NULL DEFAULT 0,
  `date_created` datetime NOT NULL DEFAULT current_timestamp(),
  `date_updated` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `phase_list`
--

INSERT INTO `phase_list` (`id`, `name`, `status`, `delete_flag`, `date_created`, `date_updated`) VALUES
(1, 'Phase 1', 1, 0, '2022-02-08 10:02:52', NULL),
(2, 'Phase 2', 1, 0, '2022-02-08 10:02:56', NULL),
(3, 'Phase 3', 1, 0, '2022-02-08 10:03:02', NULL),
(4, 'Phase 4', 1, 0, '2022-02-08 10:03:06', NULL),
(5, 'Phase 5', 0, 1, '2022-02-08 10:03:20', '2022-02-08 10:03:25');

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

--
-- Table structure for table `system_info`
--

CREATE TABLE `system_info` (
  `id` int(30) NOT NULL,
  `meta_field` text NOT NULL,
  `meta_value` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `system_info`
--

INSERT INTO `system_info` (`id`, `meta_field`, `meta_value`) VALUES
(1, 'name', 'Home Owners Collection Management'),
(6, 'short_name', ' HOCMS'),
(11, 'logo', 'uploads/1644282780_logo.png'),
(13, 'user_avatar', 'uploads/user_avatar.jpg'),
(14, 'cover', 'uploads/1644282780_wallpaper.jpg');

--
-- Indexes for dumped tables
--

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

--
-- Indexes for table `collection_items`
--
ALTER TABLE `collection_items`
  ADD KEY `collection_id` (`collection_id`),
  ADD KEY `category_id` (`category_id`);

--
-- Indexes for table `collection_list`
--
ALTER TABLE `collection_list`
  ADD PRIMARY KEY (`id`),
  ADD KEY `member_id` (`member_id`);

--
-- Indexes for table `member_list`
--
ALTER TABLE `member_list`
  ADD PRIMARY KEY (`id`),
  ADD KEY `phase_id` (`phase_id`);

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

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

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `category_list`
--
ALTER TABLE `category_list`
  MODIFY `id` int(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT for table `collection_list`
--
ALTER TABLE `collection_list`
  MODIFY `id` int(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;

--
-- AUTO_INCREMENT for table `member_list`
--
ALTER TABLE `member_list`
  MODIFY `id` int(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT for table `phase_list`
--
ALTER TABLE `phase_list`
  MODIFY `id` int(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `system_info`
--
ALTER TABLE `system_info`
  MODIFY `id` int(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `collection_items`
--
ALTER TABLE `collection_items`
  ADD CONSTRAINT `collection_items_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `category_list` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `collection_items_ibfk_2` FOREIGN KEY (`collection_id`) REFERENCES `collection_list` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `collection_list`
--
ALTER TABLE `collection_list`
  ADD CONSTRAINT `collection_list_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `member_list` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `member_list`
--
ALTER TABLE `member_list`
  ADD CONSTRAINT `member_list_ibfk_1` FOREIGN KEY (`phase_id`) REFERENCES `phase_list` (`id`) ON DELETE CASCADE;
COMMIT;

