-- Potato Farm Manager database backup
-- Generated: 2026-07-22 03:40:05
SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `admin_users`;
CREATE TABLE `admin_users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `role` enum('admin','staff') DEFAULT 'staff',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `admin_users` (`id`, `username`, `password`, `email`, `role`, `created_at`) VALUES
('1', 'admin', '$2y$10$lh8hx94y.QIfZ/dGZ8B4HO78sSIUflc8FlSKAG3rlp4.7LQakdnji', 'admin@rentsystem.com', 'admin', '2026-05-15 08:38:55'),
('2', 'bim', '$2y$10$4e9HqcsJZyfZ6YdXG25Xge9wjtmyttSXWarX9cdOGvxOrNDgQgFMC', 'newadmin@example.com', 'admin', '2026-05-24 06:43:50');

DROP TABLE IF EXISTS `balances`;
CREATE TABLE `balances` (
  `id` int NOT NULL AUTO_INCREMENT,
  `house_id` int NOT NULL,
  `tenant_id` int NOT NULL,
  `rent_balance` decimal(10,2) DEFAULT '0.00',
  `water_balance` decimal(10,2) DEFAULT '0.00',
  `last_updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `house_id` (`house_id`),
  KEY `tenant_id` (`tenant_id`),
  CONSTRAINT `balances_ibfk_1` FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE,
  CONSTRAINT `balances_ibfk_2` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `balances` (`id`, `house_id`, `tenant_id`, `rent_balance`, `water_balance`, `last_updated`) VALUES
('10', '1', '11', '-5640.00', '0.00', '2026-06-27 14:13:10'),
('12', '4', '13', '-91199.00', '0.00', '2026-05-30 03:39:59'),
('13', '9', '14', '-8566.00', '0.00', '2026-05-19 21:45:45'),
('14', '10', '15', '-56000.00', '0.00', '2026-05-24 05:43:48'),
('15', '14', '16', '1700.00', '0.00', '2026-06-11 12:47:37'),
('16', '5', '17', '11980.00', '0.00', '2026-06-27 14:16:45');

DROP TABLE IF EXISTS `crop_photos`;
CREATE TABLE `crop_photos` (
  `id` int NOT NULL AUTO_INCREMENT,
  `planting_id` int NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `taken_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_planting_date` (`planting_id`,`taken_at` DESC),
  CONSTRAINT `crop_photos_ibfk_1` FOREIGN KEY (`planting_id`) REFERENCES `plantings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `dating_blocks`;
CREATE TABLE `dating_blocks` (
  `block_id` int NOT NULL AUTO_INCREMENT,
  `blocker_id` int NOT NULL,
  `blocked_id` int NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`block_id`),
  UNIQUE KEY `uniq_block` (`blocker_id`,`blocked_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `dating_friend_requests`;
CREATE TABLE `dating_friend_requests` (
  `request_id` int NOT NULL AUTO_INCREMENT,
  `sender_id` int NOT NULL,
  `receiver_id` int NOT NULL,
  `status` enum('pending','accepted','declined') DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`request_id`),
  UNIQUE KEY `unique_request` (`sender_id`,`receiver_id`),
  KEY `receiver_id` (`receiver_id`),
  CONSTRAINT `dating_friend_requests_ibfk_1` FOREIGN KEY (`sender_id`) REFERENCES `dating_users` (`user_id`),
  CONSTRAINT `dating_friend_requests_ibfk_2` FOREIGN KEY (`receiver_id`) REFERENCES `dating_users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `dating_friend_requests` (`request_id`, `sender_id`, `receiver_id`, `status`, `created_at`) VALUES
('1', '3', '2', 'accepted', '2026-07-04 10:44:17'),
('2', '2', '1', 'accepted', '2026-07-04 15:03:19');

DROP TABLE IF EXISTS `dating_likes`;
CREATE TABLE `dating_likes` (
  `like_id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `liked_user_id` int NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`like_id`),
  UNIQUE KEY `unique_like` (`user_id`,`liked_user_id`),
  KEY `liked_user_id` (`liked_user_id`),
  CONSTRAINT `dating_likes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `dating_users` (`user_id`),
  CONSTRAINT `dating_likes_ibfk_2` FOREIGN KEY (`liked_user_id`) REFERENCES `dating_users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `dating_likes` (`like_id`, `user_id`, `liked_user_id`, `created_at`) VALUES
('1', '2', '1', '2026-07-04 09:39:32'),
('4', '3', '2', '2026-07-04 13:47:44'),
('5', '2', '3', '2026-07-04 15:00:57'),
('6', '1', '2', '2026-07-04 15:04:13'),
('8', '3', '1', '2026-07-05 01:02:34'),
('10', '2', '4', '2026-07-09 16:01:28'),
('11', '4', '2', '2026-07-09 16:02:53'),
('12', '3', '21', '2026-07-15 04:17:06'),
('13', '21', '3', '2026-07-15 04:17:06');

DROP TABLE IF EXISTS `dating_matches`;
CREATE TABLE `dating_matches` (
  `match_id` int NOT NULL AUTO_INCREMENT,
  `user1_id` int NOT NULL,
  `user2_id` int NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`match_id`),
  UNIQUE KEY `unique_match` (`user1_id`,`user2_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `dating_matches` (`match_id`, `user1_id`, `user2_id`, `created_at`) VALUES
('1', '2', '3', '2026-07-04 15:00:57'),
('2', '1', '2', '2026-07-04 15:04:13'),
('4', '2', '4', '2026-07-09 16:02:53'),
('5', '3', '21', '2026-07-15 04:17:06');

DROP TABLE IF EXISTS `dating_messages`;
CREATE TABLE `dating_messages` (
  `message_id` int NOT NULL AUTO_INCREMENT,
  `sender_id` int NOT NULL,
  `receiver_id` int NOT NULL,
  `message` text,
  `sent_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `photo` varchar(255) DEFAULT NULL,
  `is_read` tinyint(1) DEFAULT '0',
  `edited_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`message_id`),
  KEY `sender_id` (`sender_id`),
  KEY `receiver_id` (`receiver_id`),
  CONSTRAINT `dating_messages_ibfk_1` FOREIGN KEY (`sender_id`) REFERENCES `dating_users` (`user_id`),
  CONSTRAINT `dating_messages_ibfk_2` FOREIGN KEY (`receiver_id`) REFERENCES `dating_users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `dating_messages` (`message_id`, `sender_id`, `receiver_id`, `message`, `sent_at`, `photo`, `is_read`, `edited_at`, `deleted_at`) VALUES
('1', '3', '2', 'Hi', '2026-07-04 14:26:55', NULL, '1', NULL, NULL),
('2', '3', '2', 'Hibnnnnn', '2026-07-04 14:27:09', NULL, '1', NULL, NULL),
('3', '3', '2', 'HibnnnnnDdddddddd', '2026-07-04 14:27:13', NULL, '1', NULL, NULL),
('4', '3', '2', 'HibnnnnnDddddddddFff', '2026-07-04 14:27:15', NULL, '1', NULL, NULL),
('5', '3', '2', 'HibnnnnnDddddddddFff', '2026-07-04 14:27:15', NULL, '1', NULL, NULL),
('6', '3', '2', 'HibnnnnnDddddddddFff', '2026-07-04 14:27:16', NULL, '1', NULL, NULL),
('7', '3', '2', NULL, '2026-07-04 14:32:38', 'chat_6a4951c63da05.png', '1', NULL, NULL),
('8', '3', '2', NULL, '2026-07-04 14:32:47', 'chat_6a4951cf88ad7.png', '1', NULL, NULL),
('9', '2', '3', 'Hi', '2026-07-04 14:52:31', NULL, '1', NULL, NULL),
('10', '2', '3', 'Hi mum', '2026-07-04 14:58:41', NULL, '1', NULL, NULL),
('11', '2', '3', 'Hi too', '2026-07-04 14:59:23', NULL, '1', NULL, NULL),
('12', '2', '3', 'Hi too', '2026-07-04 14:59:32', NULL, '1', NULL, NULL),
('13', '2', '3', 'Okey', '2026-07-04 15:01:58', NULL, '1', NULL, NULL),
('14', '1', '3', 'Sasa', '2026-07-04 15:04:56', NULL, '0', NULL, NULL),
('15', '1', '2', 'Hi', '2026-07-05 00:44:23', NULL, '1', NULL, NULL),
('16', '1', '2', 'Hello', '2026-07-05 00:52:04', NULL, '1', NULL, NULL),
('17', '1', '2', 'Poa', '2026-07-05 01:02:01', NULL, '1', NULL, NULL),
('18', '3', '2', 'Okay', '2026-07-05 01:03:39', NULL, '1', NULL, NULL),
('19', '3', '2', 'Pole sana', '2026-07-05 01:04:23', NULL, '1', NULL, NULL),
('20', '3', '2', 'Hi', '2026-07-05 01:25:55', NULL, '1', NULL, NULL),
('21', '3', '2', 'Hi', '2026-07-05 01:29:28', NULL, '1', NULL, NULL),
('22', '3', '2', 'Hi', '2026-07-05 01:46:12', NULL, '1', NULL, NULL),
('23', '3', '2', 'Pole', '2026-07-05 01:47:58', NULL, '1', NULL, NULL),
('24', '3', '2', 'Pole', '2026-07-05 01:48:11', NULL, '1', NULL, NULL),
('25', '3', '2', 'Wow', '2026-07-05 01:58:14', NULL, '1', NULL, NULL),
('26', '3', '2', 'It\'s working', '2026-07-05 01:58:28', NULL, '1', NULL, NULL),
('27', '3', '2', NULL, '2026-07-05 01:59:06', 'chat_6a49f2a9ac3a0.png', '1', NULL, NULL),
('28', '1', '2', 'Hi mary', '2026-07-05 02:18:53', NULL, '1', NULL, NULL),
('29', '2', '3', 'Seen', '2026-07-05 02:20:31', NULL, '1', NULL, NULL),
('30', '2', '3', 'Pooe samnjjdjjfjfjj\nJhhsjdjdc\nDxujdid\nDusuud\nHdgdhd\nHskdjd\nYuuidjdjdjdd\nYuidididiidiid\nYuudusuusud\nDsuuduudd\nUuufff jjdhdhdhyduduududuudufuufuufuufuuufu', '2026-07-05 02:26:07', NULL, '1', '2026-07-05 02:53:19', NULL),
('31', '2', '3', NULL, '2026-07-05 02:28:38', NULL, '1', NULL, '2026-07-05 02:44:49'),
('32', '2', '3', 'Okey', '2026-07-05 02:53:39', NULL, '1', NULL, NULL),
('33', '2', '3', NULL, '2026-07-05 02:58:25', 'chat_6a4a009127bec.png', '1', NULL, NULL),
('34', '2', '1', 'Hi', '2026-07-08 13:08:37', NULL, '1', NULL, NULL),
('35', '3', '2', 'Hi', '2026-07-08 14:24:46', NULL, '1', NULL, NULL),
('36', '4', '2', 'Hi', '2026-07-09 16:03:30', NULL, '1', NULL, NULL),
('37', '2', '4', 'Poa', '2026-07-09 16:03:48', NULL, '1', NULL, NULL),
('38', '4', '2', 'Marry me', '2026-07-09 16:04:05', NULL, '1', NULL, NULL),
('39', '2', '4', 'Ghhgh', '2026-07-09 16:04:34', NULL, '1', NULL, NULL),
('40', '2', '4', 'Ghhgh', '2026-07-09 16:04:34', NULL, '1', NULL, NULL),
('41', '2', '4', NULL, '2026-07-09 16:05:25', 'chat_6a4fff052f8f1.png', '1', NULL, NULL),
('42', '2', '4', NULL, '2026-07-09 16:06:15', 'chat_6a4fff366f84f.jpg', '1', NULL, NULL),
('43', '2', '4', NULL, '2026-07-09 16:06:17', 'chat_6a4fff3811356.jpg', '1', NULL, NULL),
('44', '2', '4', NULL, '2026-07-09 16:06:18', 'chat_6a4fff3931e11.jpg', '1', NULL, NULL),
('45', '4', '2', 'Ukoje', '2026-07-09 16:06:58', NULL, '1', NULL, NULL),
('46', '2', '4', NULL, '2026-07-09 16:07:45', NULL, '1', NULL, '2026-07-09 16:08:24'),
('47', '4', '2', NULL, '2026-07-09 16:08:33', 'chat_6a4fffb9858f4.jpg', '1', NULL, NULL),
('48', '4', '2', NULL, '2026-07-09 16:08:43', 'chat_6a4fffc406edd.jpg', '1', NULL, NULL),
('49', '2', '3', NULL, '2026-07-09 16:22:02', 'chat_6a5002ea92ecd.jpg', '1', NULL, NULL),
('50', '2', '3', NULL, '2026-07-09 16:22:04', NULL, '1', NULL, '2026-07-09 16:24:08'),
('51', '2', '3', NULL, '2026-07-09 16:23:02', 'chat_6a500325f3a5b.jpg', '1', NULL, NULL),
('52', '2', '3', NULL, '2026-07-09 16:23:40', 'chat_6a50034ae5915.jpg', '1', NULL, NULL),
('53', '2', '3', NULL, '2026-07-09 16:23:42', NULL, '1', NULL, '2026-07-09 16:23:59'),
('54', '3', '2', 'Hi', '2026-07-15 04:17:24', NULL, '0', NULL, NULL);

DROP TABLE IF EXISTS `dating_notifications`;
CREATE TABLE `dating_notifications` (
  `notification_id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `type` varchar(30) NOT NULL,
  `message` varchar(255) NOT NULL,
  `link` varchar(255) DEFAULT NULL,
  `is_read` tinyint(1) DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`notification_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `dating_notifications` (`notification_id`, `user_id`, `type`, `message`, `link`, `is_read`, `created_at`) VALUES
('1', '2', 'match', 'You matched with Mary!', 'chat.php?with=3', '1', '2026-07-04 15:00:57'),
('2', '3', 'match', 'You matched with Joyce!', 'chat.php?with=2', '1', '2026-07-04 15:00:57'),
('3', '1', 'friend_request', 'Joyce sent you a friend request.', 'friends.php', '1', '2026-07-04 15:03:19'),
('4', '1', 'match', 'You matched with Joyce!', 'chat.php?with=2', '1', '2026-07-04 15:04:13'),
('5', '2', 'match', 'You matched with Bimwa!', 'chat.php?with=1', '1', '2026-07-04 15:04:13'),
('6', '1', 'match', 'You matched with Mary!', 'chat.php?with=3', '1', '2026-07-04 15:04:36'),
('7', '3', 'match', 'You matched with Bimwa!', 'chat.php?with=1', '1', '2026-07-04 15:04:36'),
('8', '2', 'friend_accept', 'Bimwa accepted your friend request.', 'friends.php', '1', '2026-07-04 15:05:13'),
('9', '4', 'match', 'You matched with Joyce!', 'chat.php?with=2', '0', '2026-07-09 16:02:53'),
('10', '2', 'match', 'You matched with Maryw!', 'chat.php?with=4', '1', '2026-07-09 16:02:53'),
('11', '3', 'match', 'You matched with esther_naliaka!', 'chat.php?with=21', '1', '2026-07-15 04:17:06'),
('12', '21', 'match', 'You matched with Mary!', 'chat.php?with=3', '0', '2026-07-15 04:17:06');

DROP TABLE IF EXISTS `dating_passes`;
CREATE TABLE `dating_passes` (
  `pass_id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `passed_user_id` int NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`pass_id`),
  UNIQUE KEY `uniq_pass` (`user_id`,`passed_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `dating_reports`;
CREATE TABLE `dating_reports` (
  `report_id` int NOT NULL AUTO_INCREMENT,
  `reporter_id` int NOT NULL,
  `reported_id` int NOT NULL,
  `reason` varchar(255) NOT NULL,
  `details` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`report_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `dating_search_log`;
CREATE TABLE `dating_search_log` (
  `log_id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `query` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `dating_search_log` (`log_id`, `user_id`, `query`, `created_at`) VALUES
('1', '3', 'yraM', '2026-07-05 02:17:58'),
('2', '3', 'y rayraMM', '2026-07-05 02:18:07'),
('3', '3', 'e cycoyJ rayraMM', '2026-07-05 02:18:18');

DROP TABLE IF EXISTS `dating_typing`;
CREATE TABLE `dating_typing` (
  `user_id` int NOT NULL,
  `with_user_id` int NOT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`with_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `dating_users`;
CREATE TABLE `dating_users` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `bio` text,
  `photo` varchar(255) DEFAULT 'default.jpg',
  `county` varchar(50) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `preferred_gender` varchar(10) DEFAULT 'any',
  `last_active` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `skin_colour` varchar(30) DEFAULT NULL,
  `preferred_colour` varchar(30) DEFAULT 'any',
  `relationship_status` varchar(30) DEFAULT NULL,
  `preferred_age_min` int DEFAULT '18',
  `preferred_age_max` int DEFAULT '99',
  `is_demo` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `dating_users` (`user_id`, `username`, `first_name`, `last_name`, `phone`, `password`, `email`, `age`, `gender`, `bio`, `photo`, `county`, `created_at`, `preferred_gender`, `last_active`, `skin_colour`, `preferred_colour`, `relationship_status`, `preferred_age_min`, `preferred_age_max`, `is_demo`) VALUES
('1', 'Bimwa', NULL, NULL, NULL, '$2y$10$JNOkUCzESaziXgsO3a5pvOJLOfOvFY2MMTR1644.j/A3hLwrF5ub2', 'bidan4554@gmail.com', '26', 'male', 'Single and searching', 'default.jpg', 'Nakuru', '2026-07-04 09:17:41', 'any', '2026-07-08 14:25:31', NULL, 'any', NULL, '18', '99', '0'),
('2', 'Joyce', NULL, NULL, NULL, '$2y$10$IugOJgviKI3suHxlBbyTauIiO.zDgNJlQV1cQh5rwIbUE6wE8cN3.', 'bimwa1022@gmail.com', '30', 'male', 'I like them', 'user_6a490cf54c8cc.png', 'Molo', '2026-07-04 09:39:05', 'any', '2026-07-09 16:12:46', NULL, 'any', NULL, '18', '99', '0'),
('3', 'Mary', 'Mary', 'Wairimu', '0719119493', '$2y$10$ot6aLK4zXvNwQYYMRz2tPed4Q3XXCVyIOU.4hM1M3ARrwiQUP0ZsS', 'wairimu@gmail.com', '31', 'female', 'I like ?', 'user_6a491bf74e7d3.jpg', 'NAKURU', '2026-07-04 10:43:56', 'female', '2026-07-15 04:17:06', 'Chocolate', 'Chocolate', 'Single', '18', '99', '0'),
('4', 'Maryw', 'Mary', 'Wairimu', '0719119493', '$2y$10$QS3qpSn4FcrxJNFFVJ3LlOBPggpI3Cs0H2Np5pfOJ6/TQgNQ2E6ra', 'wairimumary279@gmail.com', '28', 'female', NULL, 'user_6a4ffdadd543f.jpg', NULL, '2026-07-09 16:00:59', 'any', '2026-07-09 16:02:53', 'Chocolate', 'Light Brown / Caramel', 'Single', '18', '99', '0'),
('5', 'james_otieno', 'James', 'Otieno', '0798684327', '$2y$10$9FgPQn3KQ8MTOOXxOSqLueIurEmQJVA3cbDLqw1Pa7lLtphlehFQy', 'james_otieno@example.com', '28', 'male', 'Coffee first, everything else after. Love hiking and trying new restaurants.', 'demo_james_otieno.jpg', 'Nairobi', '2026-07-15 04:10:57', 'female', '2026-07-15 04:10:57', 'Fair / Light Skin', 'any', 'Single', '18', '50', '1'),
('6', 'brian_kimani', 'Brian', 'Kimani', '0761242640', '$2y$10$WS2HScxHVDdKk2iuR/a6nOhhUUzocU2IwLMNJjymfYosiiLvvZuKe', 'brian_kimani@example.com', '24', 'male', 'Here for genuine conversations, not games. Big fan of good music and road trips.', 'demo_brian_kimani.jpg', 'Mombasa', '2026-07-15 04:10:58', 'female', '2026-07-15 04:10:58', 'Brown', 'any', 'Widow', '18', '50', '1'),
('7', 'kevin_mwangi', 'Kevin', 'Mwangi', '0704177690', '$2y$10$2Q9Ailbppo6jwXj09e8qd.DQ35dSXCn3dku7QbTMfJe/iZAbuE4Ya', 'kevin_mwangi@example.com', '33', 'male', 'Fitness enthusiast, dog lover, and terrible cook (working on it).', 'demo_kevin_mwangi.jpg', 'Kisumu', '2026-07-15 04:10:58', 'female', '2026-07-15 04:10:58', 'Light Brown / Caramel', 'any', 'Single with kid(s)', '18', '50', '1'),
('8', 'david_kiptoo', 'David', 'Kiptoo', '0719759874', '$2y$10$ro6cxZIdSzPYcZdpNeDs8eHjJVuVgVz3yj8BUZZfmcHb5SFeuV686', 'david_kiptoo@example.com', '45', 'male', 'Bookworm by night, gym rat by morning. Let\'s talk about your favourite show.', 'demo_david_kiptoo.jpg', 'Nakuru', '2026-07-15 04:10:58', 'female', '2026-07-15 04:10:58', 'Dark Brown', 'any', 'Widow', '18', '50', '1'),
('9', 'peter_odhiambo', 'Peter', 'Odhiambo', '0769475137', '$2y$10$q8HSiFALU.qG7gClQIeK2e4P5YP762JzAsQYukgh2T3BW6aB0Hedu', 'peter_odhiambo@example.com', '21', 'male', 'Simple person looking for something real. I laugh a lot, fair warning.', 'demo_peter_odhiambo.jpg', 'Eldoret', '2026-07-15 04:10:59', 'female', '2026-07-15 04:10:59', 'Light Brown / Caramel', 'any', 'Divorced', '18', '50', '1'),
('10', 'samuel_wafula', 'Samuel', 'Wafula', '0765490860', '$2y$10$Z/cDSlklWIkeKiRQ1.7EC.J9rm57L615jlzGGWCs/cIh7iOvOjVTS', 'samuel_wafula@example.com', '38', 'male', 'Foodie exploring Nairobi one restaurant at a time. Ask me for recommendations.', 'demo_samuel_wafula.jpg', 'Kiambu', '2026-07-15 04:10:59', 'female', '2026-07-15 04:10:59', 'Brown', 'any', 'Single', '18', '50', '1'),
('11', 'eric_njoroge', 'Eric', 'Njoroge', '0737953185', '$2y$10$vGG8MD23AM/efq6Uwr0zzuUope.mP6vY4KFn1ji4iciITpB94CKee', 'eric_njoroge@example.com', '19', 'male', 'Weekend hiker, weekday hustler. Looking for someone to share adventures with.', 'demo_eric_njoroge.jpg', 'Machakos', '2026-07-15 04:10:59', 'female', '2026-07-15 04:10:59', 'Fair / Light Skin', 'any', 'Single with kid(s)', '18', '50', '1'),
('12', 'dennis_omondi', 'Dennis', 'Omondi', '0781983450', '$2y$10$Xz5kUxZsJrkr7OfLpSxA.uowo.1fT3cxHFaVP7rEGnNvu2F296vAm', 'dennis_omondi@example.com', '50', 'male', 'Music, movies, and good vibes only. Let\'s see where this goes.', 'demo_dennis_omondi.jpg', 'Kajiado', '2026-07-15 04:11:00', 'female', '2026-07-15 04:11:00', 'Dark Brown', 'any', 'Divorced', '18', '50', '1'),
('13', 'collins_mutua', 'Collins', 'Mutua', '0775825439', '$2y$10$thM6iacOjdNOB4OreT7btuCNGZBLzhUiQfhrP8yCgW/4UkQi5.Teq', 'collins_mutua@example.com', '26', 'male', 'New in town and figuring life out. Love deep talks and even deeper naps.', 'demo_collins_mutua.jpg', 'Nairobi', '2026-07-15 04:11:00', 'female', '2026-07-15 04:11:00', 'Brown', 'any', 'Single with kid(s)', '18', '50', '1'),
('14', 'victor_cheruiyot', 'Victor', 'Cheruiyot', '0773244321', '$2y$10$fjGe9lAg1hWJdoSJUdV39uZAJH8ni7kTM8.GxPMLIuRRfGjP7PbWq', 'victor_cheruiyot@example.com', '41', 'male', 'Family-oriented, hardworking, and always up for a good laugh.', 'demo_victor_cheruiyot.jpg', 'Mombasa', '2026-07-15 04:11:01', 'female', '2026-07-15 04:11:01', 'Dark Brown', 'any', 'Single with kid(s)', '18', '50', '1'),
('15', 'mary_wanjiru', 'Mary', 'Wanjiru', '0748610462', '$2y$10$fgSzZaLWqyZzI7VmmJJd9.YxlKq9zMjInafEY3C73Z1Mzpg8h1AAK', 'mary_wanjiru@example.com', '25', 'female', 'Coffee first, everything else after. Love hiking and trying new restaurants.', 'demo_mary_wanjiru.jpg', 'Nairobi', '2026-07-15 04:11:01', 'male', '2026-07-15 04:11:01', 'Black', 'any', 'Widow', '18', '50', '1'),
('16', 'faith_achieng', 'Faith', 'Achieng', '0730946381', '$2y$10$b59z32M7v54ubw2l4y4XhuiMcbqBvEvyXfiI/hSNuqKU/Y45pl1au', 'faith_achieng@example.com', '22', 'female', 'Here for genuine conversations, not games. Big fan of good music and road trips.', 'demo_faith_achieng.jpg', 'Mombasa', '2026-07-15 04:11:01', 'male', '2026-07-15 04:11:01', 'Black', 'any', 'Single', '18', '50', '1'),
('17', 'grace_nyambura', 'Grace', 'Nyambura', '0779991854', '$2y$10$nkOJ/Z2N3XI4V5cwDsvKaeN26T3EWGGDGq2UFE1h62DMN95iBQLMq', 'grace_nyambura@example.com', '30', 'female', 'Fitness enthusiast, dog lover, and terrible cook (working on it).', 'demo_grace_nyambura.jpg', 'Kisumu', '2026-07-15 04:11:02', 'male', '2026-07-15 04:11:02', 'Dark Brown', 'any', 'Divorced', '18', '50', '1'),
('18', 'joyce_chebet', 'Joyce', 'Chebet', '0761552328', '$2y$10$hetQWncIrgxb/RUvPtGSou36RXiINhJKMOsLsYWTt..6RruZGJmPe', 'joyce_chebet@example.com', '47', 'female', 'Bookworm by night, gym rat by morning. Let\'s talk about your favourite show.', 'demo_joyce_chebet.jpg', 'Nakuru', '2026-07-15 04:11:02', 'male', '2026-07-15 04:11:02', 'Fair / Light Skin', 'any', 'Single', '18', '50', '1'),
('19', 'winnie_adhiambo', 'Winnie', 'Adhiambo', '0717374080', '$2y$10$ZzBKUrAIwgw1/4zI6NBKR.TAnmBgYgkNmiptq.cbG8ftsUYAtYv6S', 'winnie_adhiambo@example.com', '18', 'female', 'Simple person looking for something real. I laugh a lot, fair warning.', 'demo_winnie_adhiambo.jpg', 'Eldoret', '2026-07-15 04:11:02', 'male', '2026-07-15 04:11:02', 'Fair / Light Skin', 'any', 'Single', '18', '50', '1'),
('20', 'susan_wambui', 'Susan', 'Wambui', '0782685315', '$2y$10$S8tg.hQA.4VWmg2n4kVraeKFo9J0yniptE1IZbz036ApDVpgd6OeO', 'susan_wambui@example.com', '36', 'female', 'Foodie exploring Nairobi one restaurant at a time. Ask me for recommendations.', 'demo_susan_wambui.jpg', 'Kiambu', '2026-07-15 04:11:03', 'male', '2026-07-15 04:11:03', 'Fair / Light Skin', 'any', 'Widow', '18', '50', '1'),
('21', 'esther_naliaka', 'Esther', 'Naliaka', '0745757364', '$2y$10$FJLTXPmXgUU76k4eqpP5IuPTGenZjtzoqOJR0leRQVpwGDCj8FAHG', 'esther_naliaka@example.com', '29', 'female', 'Weekend hiker, weekday hustler. Looking for someone to share adventures with.', 'demo_esther_naliaka.jpg', 'Machakos', '2026-07-15 04:11:04', 'male', '2026-07-15 04:11:04', 'Fair / Light Skin', 'any', 'Single', '18', '50', '1'),
('22', 'purity_moraa', 'Purity', 'Moraa', '0704589500', '$2y$10$ywh8JAU.FXCnzbZNzoVhLeFrxRjd72LJCBugp8XNjTY39VPwz06L.', 'purity_moraa@example.com', '43', 'female', 'Music, movies, and good vibes only. Let\'s see where this goes.', 'demo_purity_moraa.jpg', 'Kajiado', '2026-07-15 04:11:04', 'male', '2026-07-15 04:11:04', 'Brown', 'any', 'Divorced', '18', '50', '1'),
('23', 'sharon_auma', 'Sharon', 'Auma', '0722864878', '$2y$10$UFKuczumFDXuWgPqYWgMkOWz6V.pwO8S/Yw283.Z.5A4jCCIUSLqC', 'sharon_auma@example.com', '24', 'female', 'New in town and figuring life out. Love deep talks and even deeper naps.', 'demo_sharon_auma.jpg', 'Nairobi', '2026-07-15 04:11:05', 'male', '2026-07-15 04:11:05', 'Chocolate', 'any', 'Single with kid(s)', '18', '50', '1'),
('24', 'diana_jepkosgei', 'Diana', 'Jepkosgei', '0777252239', '$2y$10$Cq84G8bnaaixCSnxadncn.e0m.Hj.JcWOj.Nxo9QWg1cdTwGb7SRi', 'diana_jepkosgei@example.com', '39', 'female', 'Family-oriented, hardworking, and always up for a good laugh.', 'demo_diana_jepkosgei.jpg', 'Mombasa', '2026-07-15 04:11:06', 'male', '2026-07-15 04:11:06', 'Fair / Light Skin', 'any', 'Single with kid(s)', '18', '50', '1');

DROP TABLE IF EXISTS `extra_labour`;
CREATE TABLE `extra_labour` (
  `id` int NOT NULL AUTO_INCREMENT,
  `planting_id` int NOT NULL,
  `labour_date` date NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `planting_id` (`planting_id`),
  CONSTRAINT `extra_labour_ibfk_1` FOREIGN KEY (`planting_id`) REFERENCES `plantings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `extra_labour` (`id`, `planting_id`, `labour_date`, `description`, `amount`, `created_at`) VALUES
('18', '70', '2026-06-15', 'Weeding', '600.00', '2026-06-15 05:14:57'),
('19', '70', '2026-07-01', 'Molding', '600.00', '2026-07-01 13:07:49'),
('20', '72', '2026-07-03', 'Weeding', '400.00', '2026-07-03 07:56:01'),
('21', '76', '2026-07-20', 'We', '10.00', '2026-07-20 14:38:01');

DROP TABLE IF EXISTS `farmers`;
CREATE TABLE `farmers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `farmers` (`id`, `name`, `phone`, `email`, `created_at`) VALUES
('1', 'Default Farmer', '+254712345678', 'farmer@example.com', '2026-05-25 18:48:33');

DROP TABLE IF EXISTS `foliar_applications`;
CREATE TABLE `foliar_applications` (
  `id` int NOT NULL AUTO_INCREMENT,
  `planting_id` int NOT NULL,
  `application_date` date NOT NULL,
  `product_name` varchar(100) DEFAULT NULL,
  `quantity_liters` decimal(10,2) DEFAULT NULL,
  `total_cost` decimal(12,2) NOT NULL,
  `notes` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `planting_id` (`planting_id`),
  CONSTRAINT `foliar_applications_ibfk_1` FOREIGN KEY (`planting_id`) REFERENCES `plantings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `foliar_applications` (`id`, `planting_id`, `application_date`, `product_name`, `quantity_liters`, `total_cost`, `notes`, `created_at`) VALUES
('2', '70', '2026-06-26', 'Crop wonder max p', '0.20', '150.00', '', '2026-06-27 06:13:28');

DROP TABLE IF EXISTS `fungicide_applications`;
CREATE TABLE `fungicide_applications` (
  `id` int NOT NULL AUTO_INCREMENT,
  `planting_id` int NOT NULL,
  `application_date` date NOT NULL,
  `product_name` varchar(100) DEFAULT NULL,
  `quantity_liters` decimal(10,2) DEFAULT NULL,
  `total_cost` decimal(12,2) NOT NULL,
  `notes` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `planting_id` (`planting_id`),
  CONSTRAINT `fungicide_applications_ibfk_1` FOREIGN KEY (`planting_id`) REFERENCES `plantings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `fungicide_applications` (`id`, `planting_id`, `application_date`, `product_name`, `quantity_liters`, `total_cost`, `notes`, `created_at`) VALUES
('11', '70', '2026-06-05', 'Zetagold', '1.00', '300.00', '', '2026-06-15 05:16:01'),
('12', '70', '2026-06-12', 'Zetagold', '1.00', '300.00', '', '2026-06-15 05:16:28'),
('14', '70', '2026-06-19', 'Zetagold', '0.20', '400.00', '', '2026-06-23 08:01:19'),
('15', '70', '2026-06-26', 'Zetagold', '0.50', '300.00', '', '2026-06-27 06:11:57'),
('16', '72', '2026-06-27', 'Zetagold', '0.20', '150.00', '', '2026-06-27 06:12:25'),
('17', '70', '2026-07-04', 'Zetagold', '0.20', '300.00', '', '2026-07-04 05:39:29'),
('18', '72', '2026-07-08', 'Zetagold', '0.20', '200.00', '', '2026-07-08 02:27:55'),
('19', '70', '2026-07-13', 'Zetagold', '0.20', '400.00', '', '2026-07-13 05:35:24'),
('20', '72', '2026-07-16', 'Zetagold', '0.20', '200.00', '', '2026-07-20 01:21:41'),
('21', '70', '2026-07-20', 'Zetagold', '0.20', '300.00', '', '2026-07-20 09:59:51');

DROP TABLE IF EXISTS `harvest_sales`;
CREATE TABLE `harvest_sales` (
  `id` int NOT NULL AUTO_INCREMENT,
  `planting_id` int NOT NULL,
  `harvest_date` date NOT NULL,
  `bags_harvested` decimal(10,2) NOT NULL,
  `price_per_bag` decimal(12,2) NOT NULL,
  `total_sales` decimal(12,2) NOT NULL,
  `notes` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `planting_id` (`planting_id`),
  CONSTRAINT `harvest_sales_ibfk_1` FOREIGN KEY (`planting_id`) REFERENCES `plantings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `harvested_lands`;
CREATE TABLE `harvested_lands` (
  `id` int NOT NULL AUTO_INCREMENT,
  `original_land_id` int NOT NULL,
  `land_name` varchar(100) NOT NULL,
  `size_acres` decimal(10,2) NOT NULL,
  `lease_price` decimal(12,2) NOT NULL,
  `lease_year` year NOT NULL,
  `planting_date` date DEFAULT NULL,
  `harvest_date` date NOT NULL,
  `total_cost` decimal(12,2) DEFAULT NULL,
  `total_sales` decimal(12,2) DEFAULT NULL,
  `profit_loss` decimal(12,2) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `harvested_lands` (`id`, `original_land_id`, `land_name`, `size_acres`, `lease_price`, `lease_year`, `planting_date`, `harvest_date`, `total_cost`, `total_sales`, `profit_loss`, `created_at`) VALUES
('1', '1', 'Sirikwa mark', '0.50', '12000.00', '2026', NULL, '2026-06-30', '32408.00', '0.00', '-32408.00', '2026-06-13 05:51:14'),
('2', '12', 'Mary', '1.00', '25000.00', '2026', NULL, '2026-06-10', '75700.00', '105000.00', '29300.00', '2026-06-13 14:31:19'),
('3', '16', 'Y', '6.00', '6.00', '2006', NULL, '2026-06-14', '110.00', '0.00', '-110.00', '2026-06-14 02:11:57'),
('4', '17', 'Kq', '5.00', '5.00', '2006', NULL, '2026-06-14', '14002.50', '52000.00', '37997.50', '2026-06-14 02:18:58'),
('5', '18', 'John', '0.50', '6000.00', '2026', NULL, '2026-06-14', '11800.00', '0.00', '-11800.00', '2026-06-14 02:24:22'),
('6', '20', 'John1', '2.00', '40000.00', '2002', NULL, '2026-06-14', '23342.00', '0.00', '-23342.00', '2026-06-14 02:30:48'),
('7', '21', 'M', '2.00', '9.00', '2006', NULL, '2026-06-14', '82.50', '0.00', '-82.50', '2026-06-14 02:41:11'),
('8', '19', 'John', '2.00', '6.00', '2023', NULL, '2026-06-14', '9203.00', '18.00', '-9185.00', '2026-06-14 02:43:28'),
('9', '22', 'Kinyanjui', '0.50', '1500.00', '2027', NULL, '2026-06-14', NULL, NULL, NULL, '2026-06-14 03:53:05'),
('10', '23', 'J', '0.25', '6000.00', '2026', NULL, '2026-06-14', '3019.00', '3300.00', '40281.00', '2026-06-14 09:03:31'),
('11', '24', 'Kim', '1.00', '25000.00', '0000', NULL, '2026-06-09', '42600.00', '159600.00', '129000.00', '2026-06-14 09:14:00'),
('12', '11', 'Dem (kwa cucu wa muthoni)', '0.20', '5000.00', '2026', NULL, '2026-06-09', '7381.00', '213440.00', '212059.00', '2026-06-14 09:27:23'),
('13', '25', 'Jj', '2.00', '8.00', '2008', NULL, '2026-06-09', '97.00', '30.00', '1557.00', '2026-06-14 09:36:19'),
('14', '26', 'Sample', '9999.00', '9999.00', '0000', NULL, '2026-06-09', '4742188.50', '100000.00', '-4622188.50', '2026-06-14 10:01:10'),
('15', '7', 'Kanyingi farm', '0.50', '1200.00', '2026', NULL, '2026-06-16', '18200.00', '150000.00', '191800.00', '2026-06-14 10:01:46'),
('16', '27', 'Kam', '562.00', '666.00', '0000', NULL, '2026-06-08', '1742037.00', '0.00', '-1682037.00', '2026-06-14 10:12:36'),
('17', '29', 'Njoeroge', '200.00', '200.00', '2000', NULL, '2026-06-01', '196115695.00', '0.00', '-196064295.00', '2026-06-14 10:30:24'),
('18', '28', 'Jambo', '200.00', '266.00', '0000', NULL, '2026-06-10', '22680781.00', '0.00', '-22014781.00', '2026-06-14 10:45:42'),
('19', '30', 'Mum', '6656.00', '666.00', '2066', NULL, '2026-06-08', '8886111.00', '663336.00', '-7622775.00', '2026-06-14 11:20:02'),
('20', '31', 'Jk', '20.00', '35.00', '2036', '2026-06-01', '0000-00-00', '121769.50', '60000.00', '-61769.50', '2026-06-14 11:38:03'),
('21', '32', 'Peter', '2.00', '30000.00', '2026', '2026-05-03', '2026-06-01', '127334.00', '180000.00', '52666.00', '2026-06-14 12:13:48'),
('22', '33', 'Kk', '2.00', '300.00', '2020', '2026-06-17', '2026-05-05', '242778.00', '136120.00', '-106658.00', '2026-06-14 12:22:22'),
('23', '34', 'Mk', '68.00', '66.00', '2066', '2026-06-02', '2026-06-14', '246017.00', '43956.00', '-202061.00', '2026-06-14 13:00:31'),
('24', '35', 'Jkl', '25.00', '555.00', '2022', '2026-06-01', '2026-06-09', '450782.50', '110544.00', '-340238.50', '2026-06-14 13:13:09'),
('25', '36', 'Kjn', '666.00', '66.00', '2066', '2026-06-01', '2026-06-08', '8085.00', '4356.00', '-3729.00', '2026-06-14 13:17:09'),
('26', '37', 'He', '66.00', '9.00', '2060', '2026-06-08', '2026-06-01', '64.50', '43758.00', '43693.50', '2026-06-14 13:33:40'),
('27', '36', 'Kjn', '666.00', '66.00', '2066', '2026-06-02', '2026-06-09', '49500.00', '2310.00', '-47190.00', '2026-06-14 13:35:31'),
('28', '38', 'Xyz', '258.00', '558.00', '2055', '2026-06-08', '2026-06-09', '11638.00', '172000.00', '160362.00', '2026-06-14 14:17:08'),
('29', '39', 'Joy', '0.50', '12500.00', '2028', '2026-06-01', '2026-06-08', '30472.00', '4356.00', '-26116.00', '2026-06-14 14:23:40'),
('30', '38', 'Xyz', '258.00', '558.00', '2055', '2026-06-08', '2026-06-09', '4704.00', '4356.00', '-348.00', '2026-06-14 14:34:59'),
('31', '39', 'Joy', '0.50', '12500.00', '2028', '2026-06-14', '2026-06-09', '454828.00', '443556.00', '-11272.00', '2026-06-14 14:40:35'),
('32', '40', 'Jacob', '65.00', '66.00', '2006', '2026-06-14', '2026-06-10', '10296.00', '4356.00', '-5940.00', '2026-06-14 14:44:21'),
('33', '41', 'Ruto', '1.00', '6000.00', '2023', '2026-05-10', '2026-06-09', '16200.00', '28900.00', '12700.00', '2026-06-14 14:48:39'),
('34', '41', 'Ruto', '1.00', '6000.00', '2023', '2026-06-10', '2026-06-10', '140622.00', '369630.00', '229008.00', '2026-06-14 14:53:51'),
('35', '42', 'TV', '666.00', '66.00', '0000', '2026-06-10', '2026-06-09', '6609.00', '65934.00', '59325.00', '2026-06-14 15:07:57'),
('36', '39', 'Joy', '0.50', '12500.00', '2028', '2026-06-09', '2026-06-10', '10630.00', '6534.00', '-4096.00', '2026-06-14 15:09:53'),
('37', '43', 'Timo', '66.00', '66.00', '2006', '2026-06-14', '2026-06-10', '831.00', '43956.00', '43125.00', '2026-06-14 15:19:05'),
('38', '44', 'Ken', '60.00', '200.00', '2006', '2026-06-09', '2026-06-01', '18898.00', '441936.00', '423038.00', '2026-06-14 15:29:07'),
('39', '45', 'Maiba', '55.00', '55.00', '2055', '2026-06-09', '2026-06-08', '596652.50', '51330.00', '-545322.50', '2026-06-14 15:35:47'),
('40', '46', 'Kaka', '555.00', '555.00', '0000', '2026-06-16', '2026-06-10', '3207.50', '308025.00', '304817.50', '2026-06-14 15:38:52'),
('41', '47', 'Musa', '25.00', '25.00', '2025', '2026-06-16', '2026-06-16', '1287.50', '1450.00', '162.50', '2026-06-14 15:43:47'),
('42', '48', 'Juli', '25.00', '25.00', '2022', '2026-06-16', '2026-06-16', '328156.50', '308025.00', '-20131.50', '2026-06-14 15:50:28'),
('43', '40', 'Jacob', '65.00', '66.00', '2006', '2026-06-14', '2026-06-16', '88.00', '2860.00', '2772.00', '2026-06-14 16:13:31'),
('44', '49', 'Paste', '22.00', '55.00', '2022', '2026-06-17', '2026-06-18', '171877.50', '73926.00', '-97951.50', '2026-06-14 16:21:03'),
('45', '50', 'Gachagua', '25.00', '55.00', '2056', '2026-06-10', '2026-06-17', '1765.50', '37290.00', '35524.50', '2026-06-14 16:54:54'),
('46', '49', 'Paste', '22.00', '55.00', '2022', '2026-06-09', '2026-06-09', '171429.50', '78000.00', '-93429.50', '2026-06-14 17:01:38'),
('47', '51', 'Jacob', '6.00', '66.00', '0000', '2026-06-15', '2026-06-10', '4422.00', '6666000.00', '6661578.00', '2026-06-15 03:27:43'),
('48', '8', 'Karima', '0.25', '5000.00', '2026', '2026-05-07', '2026-06-27', '14000.00', '4165140.00', '4151140.00', '2026-06-15 03:32:40'),
('49', '52', 'Bidan', '2.00', '25056.00', '0000', '2026-06-15', '2026-06-15', '1054551.00', '2178.00', '-1052373.00', '2026-06-15 03:41:01'),
('50', '53', 'Bimwa', '980.00', '996.00', '0000', '2026-06-01', '2026-06-15', '445476.00', '4356.00', '-441120.00', '2026-06-15 04:09:10'),
('51', '54', 'Maina', '1.00', '25000.00', '2026', '2026-03-11', '2026-06-10', '48400.00', '142500.00', '94100.00', '2026-06-15 05:10:51');

DROP TABLE IF EXISTS `herbicide_applications`;
CREATE TABLE `herbicide_applications` (
  `id` int NOT NULL AUTO_INCREMENT,
  `planting_id` int NOT NULL,
  `application_date` date NOT NULL,
  `product_name` varchar(100) DEFAULT NULL,
  `quantity_liters` decimal(10,2) DEFAULT NULL,
  `total_cost` decimal(12,2) NOT NULL,
  `notes` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `planting_id` (`planting_id`),
  CONSTRAINT `herbicide_applications_ibfk_1` FOREIGN KEY (`planting_id`) REFERENCES `plantings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `herbicide_applications` (`id`, `planting_id`, `application_date`, `product_name`, `quantity_liters`, `total_cost`, `notes`, `created_at`) VALUES
('5', '71', '2026-06-27', 'Kausha and moto plus', '0.20', '600.00', '', '2026-06-27 06:08:05'),
('6', '8', '2026-06-26', 'Kausha', '0.50', '600.00', '', '2026-06-27 06:08:47'),
('7', '72', '2026-06-27', 'Moto plus', '0.50', '250.00', '', '2026-06-27 06:09:24'),
('9', '73', '2026-07-16', 'Kausha', '0.20', '350.00', '', '2026-07-20 02:43:04'),
('10', '74', '2026-07-20', 'Kausha', '0.30', '300.00', '', '2026-07-20 10:01:37');

DROP TABLE IF EXISTS `houses`;
CREATE TABLE `houses` (
  `id` int NOT NULL AUTO_INCREMENT,
  `plot_id` int NOT NULL,
  `house_number` varchar(20) NOT NULL,
  `monthly_rent` decimal(10,2) NOT NULL,
  `water_rate_per_unit` decimal(10,2) DEFAULT '60.00',
  `status` enum('occupied','vacant','maintenance') DEFAULT 'vacant',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `plot_id` (`plot_id`),
  CONSTRAINT `houses_ibfk_1` FOREIGN KEY (`plot_id`) REFERENCES `plots` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `houses` (`id`, `plot_id`, `house_number`, `monthly_rent`, `water_rate_per_unit`, `status`, `created_at`) VALUES
('1', '1', 'House 1', '17000.00', '60.00', 'occupied', '2026-05-15 08:38:55'),
('3', '1', 'House 3', '18000.00', '60.00', 'occupied', '2026-05-15 08:38:55'),
('4', '2', 'House 6', '12000.00', '60.00', 'occupied', '2026-05-15 08:38:55'),
('5', '2', 'House 7', '12000.00', '60.00', 'vacant', '2026-05-15 08:38:55'),
('7', '2', 'House 1', '25000.00', '60.00', 'maintenance', '2026-05-15 11:44:48'),
('9', '4', 'A', '6000.00', '60.00', 'occupied', '2026-05-16 04:20:49'),
('10', '4', 'B', '6000.00', '60.00', 'occupied', '2026-05-16 04:21:14'),
('13', '5', 'A1', '7000.00', '60.00', 'maintenance', '2026-05-17 08:52:09'),
('14', '5', '22', '35000.00', '60.00', 'occupied', '2026-05-17 09:36:17');

DROP TABLE IF EXISTS `lands`;
CREATE TABLE `lands` (
  `id` int NOT NULL AUTO_INCREMENT,
  `land_name` varchar(100) NOT NULL,
  `size_acres` decimal(10,2) NOT NULL,
  `lease_price` decimal(12,2) NOT NULL,
  `lease_year` year NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('active','harvested') DEFAULT 'active',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `lands` (`id`, `land_name`, `size_acres`, `lease_price`, `lease_year`, `created_at`, `status`) VALUES
('8', 'Karima', '0.25', '5000.00', '2026', '2026-06-13 10:02:31', 'active'),
('10', 'Katunda', '0.50', '12000.00', '2026', '2026-06-13 10:45:53', 'active'),
('55', 'Kanyingi', '0.50', '12000.00', '2026', '2026-06-15 05:21:03', 'active'),
('56', 'Dem', '0.20', '5000.00', '2026', '2026-06-15 05:42:52', 'active'),
('57', 'Ivone sirikwa', '0.50', '12500.00', '2026', '2026-06-17 03:18:01', 'active'),
('58', 'Charles kones', '0.80', '6000.00', '2026', '2026-06-23 09:28:16', 'active'),
('59', 'Mark sirikwa', '0.50', '12000.00', '2026', '2026-06-27 06:14:36', 'active'),
('60', 'Tabitha', '0.25', '6000.00', '2026', '2026-07-03 07:58:29', 'active');

DROP TABLE IF EXISTS `notification_log`;
CREATE TABLE `notification_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `planting_id` int NOT NULL,
  `notification_type` enum('weedicide','fungicide') NOT NULL,
  `target_date` date NOT NULL,
  `sent_at` timestamp NULL DEFAULT NULL,
  `status` enum('pending','sent','failed') DEFAULT 'pending',
  `sms_response` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_notification` (`planting_id`,`notification_type`),
  CONSTRAINT `notification_log_ibfk_1` FOREIGN KEY (`planting_id`) REFERENCES `plantings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_type` enum('admin','tenant') DEFAULT 'admin',
  `user_id` int NOT NULL,
  `message` text NOT NULL,
  `is_read` tinyint(1) DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `notifications` (`id`, `user_type`, `user_id`, `message`, `is_read`, `created_at`) VALUES
('6', 'admin', '0', 'Partial deposit of KSh 2000 received from tenant 9. Still needs KSh 10000', '1', '2026-05-17 13:08:51'),
('7', 'admin', '0', 'Tenant Peter kimani made payment of KSh 2000. balance is KSh 12,000.00 (overdue).', '1', '2026-05-17 13:08:51'),
('9', 'admin', '0', 'Partial deposit of KSh 6000 received from tenant 8. Still needs KSh 1000', '1', '2026-05-17 13:10:01'),
('10', 'admin', '0', 'Tenant James mwangi made payment of KSh 6000. balance is KSh 5,000.00 (overdue).', '1', '2026-05-17 13:10:01'),
('15', 'admin', '0', 'Partial deposit of KSh 5000 received from tenant 9. Still needs KSh 5000', '1', '2026-05-17 13:19:04'),
('16', 'admin', '0', 'Tenant Peter kimani made payment of KSh 5000. balance is KSh 12,000.00 (overdue).', '1', '2026-05-17 13:19:05'),
('20', 'admin', '0', 'Deposit of KSh 12000.00 fully paid by tenant 9 for house 12', '1', '2026-05-17 13:24:47'),
('21', 'admin', '0', 'Tenant Peter kimani made payment of KSh 5000. balance is KSh 12,000.00 (overdue).', '1', '2026-05-17 13:24:47'),
('22', 'admin', '0', 'Tenant Peter kimani made payment of KSh 6000. balance is KSh 6,000.00 (overdue).', '1', '2026-05-17 13:31:34'),
('23', 'admin', '0', 'Tenant Peter kimani made payment of KSh 5000. balance is KSh 1,000.00 (overdue).', '1', '2026-05-17 13:40:01'),
('24', 'admin', '0', 'Tenant Peter maina paid KSh 100 for Plot B - House 6. New balance: KSh -90,199.00', '1', '2026-05-20 01:21:07'),
('25', 'admin', '0', 'Tenant Mary wairimuB paid KSh 1000 for Tayari molo plot  - B. New balance: KSh -55,000.00', '1', '2026-05-24 05:41:37'),
('26', 'admin', '0', 'Tenant Mary wairimuB paid KSh 1000 for Tayari molo plot  - B. New balance: KSh -56,000.00', '1', '2026-05-24 05:43:48'),
('27', 'admin', '0', 'Tenant Peter maina paid KSh 1000 for Plot B - House 6. New balance: KSh -91,199.00', '1', '2026-05-30 03:39:59'),
('28', 'admin', '0', 'Tenant Bidan Mwangi  paid KSh 1000 for Plot A - House 1. New balance: KSh -5,630.00', '1', '2026-06-11 12:49:21'),
('29', 'admin', '0', 'Tenant Bidan Mwangi  paid KSh 10 for Plot A - House 1. New balance: KSh -5,640.00', '1', '2026-06-27 14:13:10'),
('30', 'admin', '0', 'Tenant Peter kamau paid KSh 20 for Plot B - House 7. New balance: KSh 11,980.00', '1', '2026-06-27 14:16:45');

DROP TABLE IF EXISTS `payments`;
CREATE TABLE `payments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL,
  `house_id` int NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `payment_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `transaction_id` varchar(100) DEFAULT NULL,
  `phone_number` varchar(15) DEFAULT NULL,
  `payment_method` enum('mpesa','cash','bank') DEFAULT 'mpesa',
  `status` enum('pending','completed','unmatched') DEFAULT 'completed',
  `notes` text,
  `recorded_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transaction_id` (`transaction_id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `house_id` (`house_id`),
  CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `payments_ibfk_2` FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=500 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `payments` (`id`, `tenant_id`, `house_id`, `amount`, `payment_date`, `transaction_id`, `phone_number`, `payment_method`, `status`, `notes`, `recorded_by`) VALUES
('50', '14', '9', '2000.00', '2026-05-18 02:25:17', 'Tgh', NULL, 'mpesa', 'completed', NULL, NULL),
('51', '15', '10', '60000.00', '2026-05-18 02:25:47', 'Gh', NULL, 'mpesa', 'completed', NULL, NULL),
('52', '14', '9', '1000.00', '2026-05-18 12:06:13', 'Hj ', NULL, 'mpesa', 'completed', NULL, NULL),
('53', '11', '1', '65.00', '2026-05-18 12:37:38', 'Y', NULL, 'mpesa', 'completed', NULL, NULL),
('54', '11', '1', '50.00', '2026-05-18 12:44:31', 'Ty', NULL, 'mpesa', 'completed', NULL, NULL),
('55', '11', '1', '2000.00', '2026-05-18 13:13:38', 'Hhh', NULL, 'mpesa', 'completed', NULL, NULL),
('56', '11', '1', '2580.00', '2026-05-18 13:25:31', 'Ghg', NULL, 'mpesa', 'completed', NULL, NULL),
('57', '11', '1', '600.00', '2026-05-18 13:29:15', 'Tt', NULL, 'mpesa', 'completed', NULL, NULL),
('58', '13', '4', '20000.00', '2026-05-18 13:48:20', 'St', NULL, 'mpesa', 'completed', NULL, NULL),
('59', '13', '4', '23000.00', '2026-05-18 13:49:52', 'Xty', NULL, 'mpesa', 'completed', NULL, NULL),
('60', '14', '9', '2855.00', '2026-05-18 13:54:53', 'Ggy', NULL, 'mpesa', 'completed', NULL, NULL),
('61', '11', '1', '5000.00', '2026-05-18 14:05:11', 'Rty', NULL, 'mpesa', 'completed', NULL, NULL),
('62', '13', '4', '2300.00', '2026-05-18 14:05:57', 'Gyu', NULL, 'mpesa', 'completed', NULL, NULL),
('63', '13', '4', '25000.00', '2026-05-18 14:08:21', 'Fgggf Jenn', NULL, 'mpesa', 'completed', NULL, NULL),
('64', '13', '4', '2500.00', '2026-05-18 14:09:24', 'Ryd', NULL, 'mpesa', 'completed', NULL, NULL),
('65', '14', '9', '2500.00', '2026-05-18 14:11:50', 'Gj', NULL, 'mpesa', 'completed', NULL, NULL),
('67', '13', '4', '255.00', '2026-05-18 14:35:13', 'Yts', NULL, 'mpesa', 'completed', NULL, NULL),
('68', '14', '9', '200.00', '2026-05-18 14:36:24', 'Hhhh', NULL, 'mpesa', 'completed', NULL, NULL),
('69', '11', '1', '2300.00', '2026-05-18 14:52:53', 'Fggg', NULL, 'mpesa', 'completed', NULL, NULL),
('70', '14', '9', '2588.00', '2026-05-18 14:53:17', 'Rgg', NULL, 'mpesa', 'completed', NULL, NULL),
('73', '13', '4', '25562.00', '2026-05-18 14:53:55', 'Fgggf', NULL, 'mpesa', 'completed', NULL, NULL),
('74', '14', '9', '230.00', '2026-05-18 14:59:29', 'Dfd', NULL, 'mpesa', 'completed', NULL, NULL),
('75', '11', '1', '250.00', '2026-05-18 14:59:57', 'Dff', NULL, 'mpesa', 'completed', NULL, NULL),
('76', '13', '4', '63.00', '2026-05-18 15:00:27', 'Xvb', NULL, 'mpesa', 'completed', NULL, NULL),
('77', '11', '1', '5681.00', '2026-05-18 15:18:55', 'Rtd', NULL, 'mpesa', 'completed', NULL, NULL),
('79', '13', '4', '2300.00', '2026-05-18 15:20:00', 'Dfy', NULL, 'mpesa', 'completed', NULL, NULL),
('81', '14', '9', '356.00', '2026-05-18 15:20:43', 'Ttt', NULL, 'mpesa', 'completed', NULL, NULL),
('82', '14', '9', '1000.00', '2026-05-18 23:24:52', 'Thj', NULL, 'mpesa', 'completed', NULL, NULL),
('83', '14', '9', '25.00', '2026-05-18 23:36:49', 'Vbh', NULL, 'mpesa', 'completed', NULL, NULL),
('84', '13', '4', '200.00', '2026-05-18 23:37:17', 'Hgh', NULL, 'mpesa', 'completed', NULL, NULL),
('85', '11', '1', '100.00', '2026-05-19 00:17:20', 'Ggggg', NULL, 'mpesa', 'completed', NULL, NULL),
('86', '13', '4', '100.00', '2026-05-19 00:17:46', 'Yxh', NULL, 'mpesa', 'completed', NULL, NULL),
('87', '14', '9', '250.00', '2026-05-19 00:18:11', 'Ehxh', NULL, 'mpesa', 'completed', NULL, NULL),
('88', '11', '1', '1000.00', '2026-05-19 01:39:53', 'Hsdh', NULL, 'mpesa', 'completed', NULL, NULL),
('89', '14', '9', '1000.00', '2026-05-19 01:41:11', 'Rd', NULL, 'mpesa', 'completed', NULL, NULL),
('90', '11', '1', '100.00', '2026-05-19 01:42:04', 'Ghb', NULL, 'mpesa', 'completed', NULL, NULL),
('91', '11', '1', '23.00', '2026-05-19 02:18:38', 'Bzj', NULL, 'mpesa', 'completed', NULL, NULL),
('92', '13', '4', '50.00', '2026-05-19 02:19:29', 'Ggh', NULL, 'mpesa', 'completed', NULL, NULL),
('93', '13', '4', '53.00', '2026-05-19 02:23:31', 'Ghhf', NULL, 'mpesa', 'completed', NULL, NULL),
('94', '13', '4', '235.00', '2026-05-19 02:25:58', 'Ghh', NULL, 'mpesa', 'completed', NULL, NULL),
('95', '11', '1', '300.00', '2026-05-19 02:26:18', 'Hjj', NULL, 'mpesa', 'completed', NULL, NULL),
('96', '11', '1', '123.00', '2026-05-19 02:35:50', 'Tcd', NULL, 'mpesa', 'completed', NULL, NULL),
('99', '13', '4', '236.00', '2026-05-19 02:36:42', 'Ghhfe', NULL, 'mpesa', 'completed', NULL, NULL),
('100', '11', '1', '358.00', '2026-05-19 02:57:40', 'Ghhahh', NULL, 'mpesa', 'completed', NULL, NULL),
('101', '13', '4', '10.00', '2026-05-19 21:31:21', 'Kge', NULL, 'mpesa', 'completed', NULL, NULL),
('102', '11', '1', '100.00', '2026-05-19 21:32:12', 'Kgf', NULL, 'mpesa', 'completed', NULL, NULL),
('103', '14', '9', '562.00', '2026-05-19 21:45:45', 'Thdf', NULL, 'mpesa', 'completed', NULL, NULL),
('104', '13', '4', '100.00', '2026-05-20 01:21:07', 'Hfxv', NULL, 'mpesa', 'completed', NULL, NULL),
('109', '11', '14', '1000.00', '2026-05-18 02:23:32', '', NULL, 'mpesa', 'completed', NULL, NULL),
('491', '13', '4', '235.00', '2026-05-18 02:24:50', 'Zi', NULL, 'mpesa', 'completed', NULL, NULL),
('493', '15', '10', '1000.00', '2026-05-24 05:41:37', 'Jsnsh', NULL, 'mpesa', 'completed', NULL, NULL),
('494', '15', '10', '1000.00', '2026-05-24 05:43:48', 'Jssh', NULL, 'mpesa', 'completed', NULL, NULL),
('495', '13', '4', '1000.00', '2026-05-30 03:39:59', 'Ndbxbx', NULL, 'mpesa', 'completed', NULL, NULL),
('496', '11', '1', '1000.00', '2026-06-11 12:49:21', 'Hhdhd', NULL, 'mpesa', 'completed', NULL, NULL),
('497', '11', '1', '10.00', '2026-06-27 14:13:10', 'Xff', NULL, 'mpesa', 'completed', NULL, NULL),
('498', '17', '5', '20.00', '2026-06-27 14:16:45', 'Gchh', NULL, 'mpesa', 'completed', NULL, NULL),
('499', '17', '5', '12000.00', '2026-06-28 08:07:19', 'DEPOSIT_REFUND', NULL, 'mpesa', 'completed', 'Full deposit refund after vacating', NULL);

DROP TABLE IF EXISTS `plantings`;
CREATE TABLE `plantings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `land_id` int NOT NULL,
  `planting_date` date NOT NULL,
  `seed_amount_kg` decimal(10,2) NOT NULL,
  `seed_price_per_kg` decimal(10,2) NOT NULL,
  `fertilizer_used_kg` decimal(10,2) NOT NULL,
  `fertilizer_price_per_50kg` decimal(10,2) NOT NULL,
  `labor_cost` decimal(12,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `germination_status` enum('pending','on_time','delayed') NOT NULL DEFAULT 'pending',
  `germination_delay_marked_date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `land_id` (`land_id`),
  CONSTRAINT `plantings_ibfk_1` FOREIGN KEY (`land_id`) REFERENCES `lands` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=77 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `plantings` (`id`, `land_id`, `planting_date`, `seed_amount_kg`, `seed_price_per_kg`, `fertilizer_used_kg`, `fertilizer_price_per_50kg`, `labor_cost`, `created_at`, `germination_status`, `germination_delay_marked_date`) VALUES
('8', '10', '2026-06-01', '3.50', '3000.00', '2.00', '3000.00', '2100.00', '2026-06-13 10:48:40', 'delayed', '2026-07-20'),
('70', '8', '2026-05-07', '2.00', '3500.00', '1.00', '3000.00', '1200.00', '2026-06-15 05:12:44', 'on_time', NULL),
('71', '55', '2026-06-10', '4.00', '3000.00', '2.00', '3000.00', '1800.00', '2026-06-15 05:22:57', 'delayed', '2026-07-20'),
('72', '56', '2026-05-23', '1.50', '3500.00', '1.00', '3000.00', '900.00', '2026-06-15 05:43:55', 'on_time', NULL),
('73', '60', '2026-07-01', '1.50', '2500.00', '1.00', '3000.00', '1400.00', '2026-07-03 07:59:51', 'pending', NULL),
('74', '58', '2026-07-06', '3.00', '2500.00', '1.00', '3000.00', '2600.00', '2026-07-07 06:33:27', 'pending', NULL),
('75', '59', '2026-07-07', '4.00', '2500.00', '2.00', '3000.00', '2100.00', '2026-07-07 06:35:24', 'pending', NULL),
('76', '57', '2026-07-14', '3.30', '3000.00', '2.00', '3250.00', '1800.00', '2026-07-14 10:49:30', 'pending', NULL);

DROP TABLE IF EXISTS `plots`;
CREATE TABLE `plots` (
  `id` int NOT NULL AUTO_INCREMENT,
  `plot_name` varchar(50) NOT NULL,
  `description` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `plot_name` (`plot_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `plots` (`id`, `plot_name`, `description`, `created_at`) VALUES
('1', 'Plot A', 'Kaheho', '2026-05-15 08:38:55'),
('2', 'Plot B', 'Back side', '2026-05-15 08:38:55'),
('4', 'Tayari molo plot ', '', '2026-05-16 04:20:05'),
('5', 'NJONJO Tents', 'Bedsitter', '2026-05-17 07:58:07');

DROP TABLE IF EXISTS `potato_photos`;
CREATE TABLE `potato_photos` (
  `id` int NOT NULL AUTO_INCREMENT,
  `planting_id` int NOT NULL,
  `land_id` int NOT NULL,
  `photo_path` varchar(255) NOT NULL,
  `date_taken` date NOT NULL,
  `uploaded_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_planting` (`planting_id`),
  KEY `idx_land` (`land_id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `potato_photos` (`id`, `planting_id`, `land_id`, `photo_path`, `date_taken`, `uploaded_at`) VALUES
('13', '71', '55', 'uploads/potato_photos/potato_6a4ce97acbcdb4.03736243.jpg', '2026-07-07', '2026-07-07 07:56:44'),
('14', '8', '10', 'uploads/potato_photos/potato_6a4cfc18e95b11.26325211.jpg', '2026-07-07', '2026-07-07 09:16:10'),
('29', '70', '8', '/potato_uploads/potato_photos/potato_6a5dfb7fca6d60.43134754.jpg', '2026-07-20', '2026-07-20 06:42:09'),
('30', '70', '8', '/potato_uploads/potato_photos/potato_6a5dfbaf00c341.51507339.jpg', '2026-07-20', '2026-07-20 06:42:57'),
('31', '70', '8', '/potato_uploads/potato_photos/potato_6a5dfbdb2b93f6.09675817.jpg', '2026-07-20', '2026-07-20 06:43:40'),
('32', '70', '8', '/potato_uploads/potato_photos/potato_6a5dfc12ae6ac3.92049768.jpg', '2026-07-20', '2026-07-20 06:44:36'),
('33', '70', '8', '/potato_uploads/potato_photos/potato_6a5dfe26e208a9.19541404.jpg', '2026-07-20', '2026-07-20 06:53:28'),
('34', '71', '55', '/potato_uploads/potato_photos/potato_6a5e41e4ed1515.87408636.jpg', '2026-07-20', '2026-07-20 11:42:30'),
('35', '71', '55', '/potato_uploads/potato_photos/potato_6a5e421e3e4942.13648085.jpg', '2026-07-20', '2026-07-20 11:43:27'),
('36', '8', '10', '/potato_uploads/potato_photos/potato_6a5e6b9b3b3b12.13806120.jpg', '2026-07-20', '2026-07-20 14:40:28');

DROP TABLE IF EXISTS `seed_recovery`;
CREATE TABLE `seed_recovery` (
  `id` int NOT NULL AUTO_INCREMENT,
  `planting_id` int NOT NULL,
  `recovery_date` date NOT NULL,
  `bags_remaining` decimal(10,2) NOT NULL,
  `current_seed_price` decimal(12,2) NOT NULL,
  `total_value` decimal(12,2) NOT NULL,
  `notes` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `planting_id` (`planting_id`),
  CONSTRAINT `seed_recovery_ibfk_1` FOREIGN KEY (`planting_id`) REFERENCES `plantings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=77 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `sms_archive`;
CREATE TABLE `sms_archive` (
  `id` int NOT NULL AUTO_INCREMENT,
  `recipient_phone` varchar(20) NOT NULL,
  `message` text NOT NULL,
  `status` enum('sent','failed') NOT NULL,
  `attempts` int DEFAULT '0',
  `sent_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `sms_archive` (`id`, `recipient_phone`, `message`, `status`, `attempts`, `sent_at`) VALUES
('1', '254713132531', 'Dear Bidan Mwangi , we received your rent payment of KSh 123 for Plot A - House 1. Your new balance is KSh -4,172.00', 'failed', '5', '2026-05-19 02:39:04'),
('2', '254713132531', 'Tenant Bidan Mwangi  paid KSh 123 for Plot A - House 1. New balance: KSh -4,172.00', 'failed', '5', '2026-05-19 02:39:04'),
('3', '254746508450', 'Dear Peter maina, we received your rent payment of KSh 236 for Plot B - House 6. Your new balance is KSh -90,089.00', 'failed', '5', '2026-05-19 02:40:04'),
('4', '254713132531', 'Tenant Peter maina paid KSh 236 for Plot B - House 6. New balance: KSh -90,089.00', 'failed', '5', '2026-05-19 02:40:04'),
('5', '254713132531', 'Dear Bidan Mwangi , we received your rent payment of KSh 358 for Plot A - House 1. Your new balance is KSh -4,530.00', 'failed', '5', '2026-05-19 03:01:06'),
('6', '254713132531', 'Tenant Bidan Mwangi  paid KSh 358 for Plot A - House 1. New balance: KSh -4,530.00', 'failed', '5', '2026-05-19 03:01:08'),
('7', '254746508450', 'Dear Peter maina, we received your rent payment of KSh 10 for Plot B - House 6. Your new balance is KSh -90,099.00', 'sent', '1', '2026-05-19 21:32:04'),
('8', '254713132531', 'Tenant Peter maina paid KSh 10 for Plot B - House 6. New balance: KSh -90,099.00', 'sent', '1', '2026-05-19 21:32:05'),
('9', '254713132531', 'Dear Bidan Mwangi , we received your rent payment of KSh 100 for Plot A - House 1. Your new balance is KSh -4,630.00', 'sent', '1', '2026-05-19 21:33:04'),
('10', '254713132531', 'Tenant Bidan Mwangi  paid KSh 100 for Plot A - House 1. New balance: KSh -4,630.00', 'sent', '1', '2026-05-19 21:33:04'),
('11', '254719119493', 'Dear Mary wairimu, we received your rent payment of KSh 562 for Tayari molo plot  - A. Your new balance is KSh -8,566.00', 'sent', '1', '2026-05-19 21:46:04'),
('12', '254713132531', 'Tenant Mary wairimu paid KSh 562 for Tayari molo plot  - A. New balance: KSh -8,566.00', 'sent', '1', '2026-05-19 21:46:04'),
('13', '254746508450', 'Dear Peter maina, we received your rent payment of KSh 100 for Plot B - House 6. Your new balance is KSh -90,199.00', 'sent', '1', '2026-05-20 01:22:04'),
('14', '254731670424', 'Dear Mary wairimuB, we received your rent payment of KSh 1000 for Tayari molo plot  - B. Your new balance is KSh -55,000.00', 'sent', '1', '2026-05-24 05:42:04'),
('15', '254731670424', 'Dear Mary wairimuB, we received your rent payment of KSh 1000 for Tayari molo plot  - B. Your new balance is KSh -56,000.00', 'sent', '1', '2026-05-24 05:44:04'),
('16', '254746508450', 'Dear Peter maina, we received your rent payment of KSh 1000 for Plot B - House 6. Your new balance is KSh -91,199.00', 'sent', '1', '2026-05-30 03:40:05'),
('17', '254713132531', 'Dear Bidan Mwangi , we received your rent payment of KSh 1000 for Plot A - House 1. Your new balance is KSh -5,630.00', 'failed', '5', '2026-06-11 12:54:04'),
('18', '254713132531', 'Dear Bidan Mwangi , we received your rent payment of KSh 10 for Plot A - House 1. Your new balance is KSh -5,640.00', 'failed', '5', '2026-06-27 14:18:04'),
('19', '254719119493', 'Dear Peter kamau, we received your rent payment of KSh 20 for Plot B - House 7. Your new balance is KSh 11,980.00', 'failed', '5', '2026-06-27 14:21:04');

DROP TABLE IF EXISTS `sms_log`;
CREATE TABLE `sms_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `phone` varchar(15) DEFAULT NULL,
  `message` text,
  `status` varchar(20) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `sms_queue`;
CREATE TABLE `sms_queue` (
  `id` int NOT NULL AUTO_INCREMENT,
  `recipient_phone` varchar(20) NOT NULL,
  `message` text NOT NULL,
  `attempts` int DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `tenant_houses`;
CREATE TABLE `tenant_houses` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL,
  `house_id` int NOT NULL,
  `move_in_date` date DEFAULT (curdate()),
  `move_out_date` date DEFAULT NULL,
  `is_current` tinyint(1) DEFAULT '1',
  `deposit_paid` tinyint(1) DEFAULT '0',
  `deposit_amount` decimal(10,2) DEFAULT '0.00',
  `expected_vacate_date` date DEFAULT NULL,
  `vacate_notification_sent` tinyint(1) DEFAULT '0',
  `last_rent_billed_date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `house_id` (`house_id`),
  CONSTRAINT `tenant_houses_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tenant_houses_ibfk_2` FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `tenant_houses` (`id`, `tenant_id`, `house_id`, `move_in_date`, `move_out_date`, `is_current`, `deposit_paid`, `deposit_amount`, `expected_vacate_date`, `vacate_notification_sent`, `last_rent_billed_date`) VALUES
('10', '11', '1', '2026-05-18', NULL, '1', '0', '0.00', NULL, '0', NULL),
('12', '13', '4', '2026-05-18', NULL, '1', '0', '0.00', NULL, '0', NULL),
('13', '14', '9', '2026-05-18', NULL, '1', '0', '0.00', NULL, '0', NULL),
('14', '15', '10', '2026-05-18', NULL, '1', '0', '0.00', NULL, '0', NULL),
('15', '16', '14', '2026-06-11', NULL, '1', '0', '0.00', NULL, '0', NULL),
('16', '17', '5', '2026-06-27', '2026-06-28', '0', '0', '0.00', NULL, '0', NULL);

DROP TABLE IF EXISTS `tenant_phones`;
CREATE TABLE `tenant_phones` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL,
  `phone_number` varchar(15) NOT NULL,
  `is_primary` tinyint(1) DEFAULT '0',
  `description` varchar(100) DEFAULT NULL,
  `added_by` varchar(50) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  CONSTRAINT `tenant_phones_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=74 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `tenant_phones` (`id`, `tenant_id`, `phone_number`, `is_primary`, `description`, `added_by`, `created_at`) VALUES
('31', '14', '254719119493', '1', '', NULL, '2026-05-18 23:23:36'),
('32', '11', '254713132531', '0', '', NULL, '2026-05-19 00:16:32'),
('37', '15', '254713132531', '0', 'E', NULL, '2026-05-24 05:43:04'),
('38', '13', '254746508450', '1', '', NULL, '2026-05-24 08:42:14'),
('49', '16', '0713132531', '1', NULL, NULL, '2026-06-11 12:47:37'),
('57', '17', '0719119493', '1', NULL, NULL, '2026-06-27 14:15:20');

DROP TABLE IF EXISTS `tenants`;
CREATE TABLE `tenants` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `id_number` varchar(20) NOT NULL,
  `phone` varchar(15) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `gender` enum('Male','Female','Other') DEFAULT 'Male',
  `tenant_photo` varchar(255) DEFAULT NULL,
  `id_photo` varchar(255) DEFAULT NULL,
  `current_water_reading` decimal(10,2) DEFAULT '0.00',
  `last_water_reading_date` date DEFAULT NULL,
  `status` enum('active','inactive','evicted') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_number` (`id_number`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `tenants` (`id`, `name`, `id_number`, `phone`, `email`, `gender`, `tenant_photo`, `id_photo`, `current_water_reading`, `last_water_reading_date`, `status`, `created_at`) VALUES
('11', 'Bidan Mwangi ', '30476271', '0713132531', NULL, 'Male', NULL, NULL, '0.00', NULL, 'active', '2026-05-18 02:20:42'),
('13', 'Peter maina', '438', '0746508450', NULL, 'Male', NULL, NULL, '0.00', NULL, 'active', '2026-05-18 02:21:50'),
('14', 'Mary wairimu', '20734522', '0719119493', NULL, 'Male', NULL, NULL, '0.00', NULL, 'active', '2026-05-18 02:22:27'),
('15', 'Mary wairimuB', '30452728', '0731670424', NULL, 'Male', NULL, NULL, '0.00', NULL, 'active', '2026-05-18 02:23:10'),
('16', 'James Macharia', '30304683', '0713132531', NULL, 'Male', NULL, NULL, '0.00', NULL, 'active', '2026-06-11 12:47:37'),
('17', 'Peter kamau', '46&', '0719119493', NULL, 'Male', NULL, NULL, '0.00', NULL, 'inactive', '2026-06-27 14:15:20');

DROP TABLE IF EXISTS `topdressing`;
CREATE TABLE `topdressing` (
  `id` int NOT NULL AUTO_INCREMENT,
  `planting_id` int NOT NULL,
  `application_date` date NOT NULL,
  `bags_used` decimal(10,2) NOT NULL,
  `price_per_bag` decimal(12,2) NOT NULL,
  `total_cost` decimal(12,2) NOT NULL,
  `notes` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `planting_id` (`planting_id`),
  CONSTRAINT `topdressing_ibfk_1` FOREIGN KEY (`planting_id`) REFERENCES `plantings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `topdressing` (`id`, `planting_id`, `application_date`, `bags_used`, `price_per_bag`, `total_cost`, `notes`, `created_at`) VALUES
('7', '70', '2026-07-01', '1.00', '3000.00', '3000.00', 'Mavuno planting', '2026-07-01 13:08:47'),
('8', '72', '2026-07-03', '1.00', '2500.00', '2500.00', 'Mavuno planting', '2026-07-03 07:55:37');

DROP TABLE IF EXISTS `unmatched_payments`;
CREATE TABLE `unmatched_payments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `transaction_id` varchar(100) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `phone_number` varchar(15) DEFAULT NULL,
  `payment_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('pending','resolved','ignored') DEFAULT 'pending',
  `resolved_by` int DEFAULT NULL,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `notes` text,
  `payer_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transaction_id` (`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `users` (`id`, `username`, `password_hash`, `created_at`) VALUES
('1', 'admin', '$2y$10$OF4GQ2iprSG4Eo8Wd6Ksxewm6DrjEa4lmrY7iEH/SeIJrITdKq.ri', '2026-07-04 05:11:41');

DROP TABLE IF EXISTS `water_bills`;
CREATE TABLE `water_bills` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL,
  `house_id` int NOT NULL,
  `billing_month` date NOT NULL,
  `previous_reading` decimal(10,2) DEFAULT '0.00',
  `current_reading` decimal(10,2) DEFAULT '0.00',
  `units_consumed` decimal(10,2) DEFAULT '0.00',
  `rate_per_unit` decimal(10,2) DEFAULT '60.00',
  `amount` decimal(10,2) DEFAULT '0.00',
  `status` enum('pending','paid','partial') DEFAULT 'pending',
  `payment_date` timestamp NULL DEFAULT NULL,
  `payment_amount` decimal(10,2) DEFAULT '0.00',
  `notes` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `house_id` (`house_id`),
  CONSTRAINT `water_bills_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `water_bills_ibfk_2` FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

SET FOREIGN_KEY_CHECKS=1;