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

START TRANSACTION;



--
-- Database: `agrisense_db`
--

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

--
-- Table structure for table `crop_advice`
--

CREATE TABLE `crop_advice` (
  `id` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `content` text NOT NULL,
  `category` varchar(50) DEFAULT NULL,
  `min_temp` float DEFAULT NULL,
  `max_temp` float DEFAULT NULL,
  `ideal_condition` varchar(50) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `crop_advice`
--

INSERT INTO `crop_advice` (`id`, `title`, `content`, `category`, `min_temp`, `max_temp`, `ideal_condition`, `created_at`) VALUES
(1, 'Ideal Planting Time', 'Early spring is the best time to plant tomatoes in your region.', 'General', NULL, NULL, NULL, '2026-04-06 11:49:07'),
(2, 'Fertilizer Tip', 'Use nitrogen-rich fertilizers during the initial growth phase.', 'Fertilizer', NULL, NULL, NULL, '2026-04-06 11:49:07');

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

--
-- Table structure for table `disease_info`
--

CREATE TABLE `disease_info` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `plant_name` varchar(50) NOT NULL,
  `description` text DEFAULT NULL,
  `cure` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `disease_info`
--

INSERT INTO `disease_info` (`id`, `name`, `plant_name`, `description`, `cure`) VALUES
(1, 'Tomato Early Blight', 'Tomato', 'Early blight is a common tomato disease...', 'Apply fungicide, remove infected leaves.'),
(2, 'Healthy Plant', 'All', 'The plant shows no signs of disease.', 'Continue regular watering and care.');

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

--
-- Table structure for table `messages`
--

CREATE TABLE `messages` (
  `id` int(11) NOT NULL,
  `sender_id` int(11) NOT NULL,
  `receiver_id` int(11) NOT NULL,
  `message` text NOT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `message` text NOT NULL,
  `type` enum('Alert','Info','Message') DEFAULT 'Info',
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `shop_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `category` varchar(50) NOT NULL,
  `price` varchar(20) NOT NULL,
  `is_available` tinyint(1) DEFAULT 1,
  `image_url` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `scans`
--

CREATE TABLE `scans` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `plant_name` varchar(50) NOT NULL,
  `disease_name` varchar(100) NOT NULL,
  `confidence` float NOT NULL,
  `description` text DEFAULT NULL,
  `cure` text DEFAULT NULL,
  `image_url` varchar(255) NOT NULL,
  `scan_date` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `scans`
--

INSERT INTO `scans` (`id`, `user_id`, `plant_name`, `disease_name`, `confidence`, `description`, `cure`, `image_url`, `scan_date`) VALUES
(1, 1, 'Apple', 'Cedar Apple Rust', 0.597503, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'content://media/picker/0/com.android.providers.media.photopicker/media/1000267299', '2026-04-07 07:34:42'),
(2, 1, 'Apple', 'Cedar Apple Rust', 0.597503, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'content://media/picker/0/com.android.providers.media.photopicker/media/1000267299', '2026-04-07 07:34:43'),
(3, 1, 'Apple', 'Cedar Apple Rust', 0.597503, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'content://media/picker/0/com.android.providers.media.photopicker/media/1000267299', '2026-04-07 07:35:23'),
(4, 1, 'Apple', 'Cedar Apple Rust', 0.597503, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'content://media/picker/0/com.android.providers.media.photopicker/media/1000267299', '2026-04-07 07:35:24'),
(5, 1, 'Apple', 'Cedar Apple Rust', 0.597503, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'content://media/picker/0/com.android.providers.media.photopicker/media/1000267299', '2026-04-07 11:04:35'),
(6, 1, 'Apple', 'Cedar Apple Rust', 0.597503, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'content://media/picker/0/com.android.providers.media.photopicker/media/1000267299', '2026-04-07 11:04:35'),
(7, 1, 'Apple', 'Cedar Apple Rust', 0.597503, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'uploads/scans/scan_69d4ea0176ea7.jpeg', '2026-04-07 11:26:57'),
(8, 1, 'Apple', 'Cedar Apple Rust', 0.597503, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'uploads/scans/scan_69d4ea0202f2f.jpeg', '2026-04-07 11:26:58'),
(9, 1, 'Apple', 'Cedar Apple Rust', 0.597503, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'uploads/scans/scan_69d54479dbcdc.jpeg', '2026-04-07 17:52:57'),
(10, 1, 'Apple', 'Cedar Apple Rust', 0.597503, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'uploads/scans/scan_69d5447abf2dd.jpeg', '2026-04-07 17:52:58'),
(11, 1, 'Apple', 'Cedar Apple Rust', 0.597503, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'uploads/scans/scan_69d5b6c49753a.jpeg', '2026-04-08 02:00:36'),
(12, 1, 'Apple', 'Cedar Apple Rust', 0.597503, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'uploads/scans/scan_69d5b6c50e473.jpeg', '2026-04-08 02:00:37'),
(13, 14, 'Apple', 'Cedar Apple Rust', 0.597503, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'uploads/scans/scan_69d5c479123c2.jpeg', '2026-04-08 02:59:05'),
(14, 14, 'Apple', 'Cedar Apple Rust', 0.597503, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'uploads/scans/scan_69d5c479ed822.jpeg', '2026-04-08 02:59:05'),
(15, 15, 'Apple', 'Cedar Apple Rust', 0.597503, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'uploads/scans/scan_69d5c557d53b7.jpeg', '2026-04-08 03:02:47'),
(16, 15, 'Apple', 'Cedar Apple Rust', 0.597503, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'uploads/scans/scan_69d5c55897481.jpeg', '2026-04-08 03:02:48'),
(17, 16, 'Apple', 'Cedar Apple Rust', 0.656355, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'uploads/scans/scan_69d5c79d3e2ea.jpeg', '2026-04-08 03:12:29'),
(18, 16, 'Apple', 'Cedar Apple Rust', 0.656355, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'uploads/scans/scan_69d5c79eec60e.jpeg', '2026-04-08 03:12:30'),
(19, 16, 'Apple', 'Cedar Apple Rust', 0.685538, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'uploads/scans/scan_69d5c7e3c3703.jpeg', '2026-04-08 03:13:39'),
(20, 16, 'Apple', 'Cedar Apple Rust', 0.685538, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'uploads/scans/scan_69d5c7e4baefe.jpeg', '2026-04-08 03:13:40'),
(21, 16, 'Corn', 'Gray Leaf Spot', 0.516351, 'Detected Gray Leaf Spot in Corn', 'Rotate crops, Till under residue, Plant resistant hybrids', 'uploads/scans/scan_69d5c82eba361.jpeg', '2026-04-08 03:14:54'),
(22, 16, 'Corn', 'Gray Leaf Spot', 0.516351, 'Detected Gray Leaf Spot in Corn', 'Rotate crops, Till under residue, Plant resistant hybrids', 'uploads/scans/scan_69d5c82f76b29.jpeg', '2026-04-08 03:14:55'),
(23, 16, 'Apple', 'Cedar Apple Rust', 0.597503, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'uploads/scans/scan_69d5cc3f31373.jpeg', '2026-04-08 03:32:15'),
(24, 16, 'Apple', 'Cedar Apple Rust', 0.597503, 'Detected Cedar Apple Rust in Apple', 'Remove nearby cedar trees if possible, Apply protective fungicides, Plant resistant varieties', 'uploads/scans/scan_69d5cc3f8f467.jpeg', '2026-04-08 03:32:15');

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

--
-- Table structure for table `shops`
--

CREATE TABLE `shops` (
  `id` int(11) NOT NULL,
  `owner_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `village` varchar(50) NOT NULL,
  `address` text NOT NULL,
  `phone` varchar(15) NOT NULL,
  `image_url` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `phone_number` varchar(10) NOT NULL,
  `password` varchar(255) NOT NULL,
  `full_name` varchar(100) NOT NULL,
  `shop_name` varchar(100) DEFAULT NULL,
  `role` enum('Farmer','Vendor') NOT NULL,
  `state` varchar(50) DEFAULT NULL,
  `district` varchar(50) DEFAULT NULL,
  `village` varchar(50) DEFAULT NULL,
  `profile_pic_url` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `users` (`id`, `phone_number`, `password`, `full_name`, `shop_name`, `role`, `state`, `district`, `village`, `profile_pic_url`, `created_at`) VALUES
(1, '6301025550', '$2y$10$MOIogKSRpBBqEbaPeuiWp.iEjv3/eE0Je0rsqhFmw1aj8Fwe17oCa', 'shaik muzamil', NULL, 'Farmer', 'Andhra Pradesh', 'NTR', 'buchi', NULL, '2026-04-07 07:11:22'),
(2, '9502605933', '$2y$10$zz1d4OT1D4EJYG5SoYhHfeNETsvYW.SZRuc8LwE.XPMol3T2mxT5m', 'vamsi', NULL, 'Vendor', 'Telangana', 'Kamareddy', 'kadapa', NULL, '2026-04-07 09:28:08'),
(4, '8008529786', '$2y$10$eP1BKD7UrUMnoOX/11e3veIoSWMfSdTXHky3AKf301I1H9k2BtVja', 'muzzu ', NULL, 'Vendor', 'Andhra Pradesh', 'Anantapur', 'buchi ', NULL, '2026-04-07 09:42:25'),
(6, '9502605931', '$2y$10$ZnWiiDBHdW3VHfCdb0k.KOim3m0m8M6P4JOj3kyjYkbaBtDYsDtOS', 'Vamsi ', NULL, 'Vendor', 'Andhra Pradesh', 'YSR', 'kadapa', NULL, '2026-04-07 09:52:34'),
(7, '8374302669', '$2y$10$fS0QIq5Sy2dEw7lG1JxqtuxEJuKzfrEQpq59IlKGfUdyItKMBApCW', 'Mohith', NULL, 'Vendor', 'Andhra Pradesh', 'Chittoor', 'Chittoor ', NULL, '2026-04-07 11:24:55'),
(9, '9391400411', '$2y$10$.m5VCAzgqyaDRcY0/2ilyuoVpU0/bY/QERtY0caW9xgeDMXatuEau', 'vamsi', NULL, 'Vendor', 'Andhra Pradesh', 'YSR', 'kadapa', NULL, '2026-04-07 12:57:31'),
(10, '9989221144', '$2y$10$Q3ayncN9Qkkb5a9bPcoJ2eYnFkQEBots8iV7fa0GJ/bxLGAR1LBN6', 'pavan', NULL, 'Vendor', 'Chhattisgarh', 'Gariaband', 'vadino', NULL, '2026-04-07 15:01:19'),
(11, '9988665544', '$2y$10$bg4Clc8gqk2zL13hrMeM1uGPj0ecR8pe7NYBanozPYRZtkpaQSNjm', 'raja', NULL, 'Vendor', 'Haryana', 'Karnal', 'vadugal', NULL, '2026-04-07 15:22:13'),
(14, '9502605996', '$2y$10$KdmzyYn4xxlRJBIOVQw0W.NKZ7CJqpmt19lKdC6r9Zh1ip0g4/yUO', 'vamsi', NULL, 'Farmer', 'Andhra Pradesh', 'Annamayya', 'kadapa', NULL, '2026-04-08 02:01:44'),
(15, '8374302660', '$2y$10$NS5Rnijpjj4W1zLyP6mT7eie2AE4RrzFcW7oqcJt3doQWYbMoNy46', 'ramu', NULL, 'Farmer', 'Andhra Pradesh', 'Anantapur', 'kadapa', NULL, '2026-04-08 03:01:53'),
(16, '5855566595', '$2y$10$z5kBm/lCd5EJdAMFGrWt1OfgNvSN2HR5BOq6plVsr0v8Ydy5y78uS', '2115455484', NULL, 'Farmer', 'Tamil Nadu', 'Vellore', 'yeege', NULL, '2026-04-08 03:09:59'),
(17, '8825489412', '$2y$10$5cH1ujbKr8fFWxN/I.Ihm.ggubmXNcOaYrEoQh4vki4WfJV/69bMK', 'lokesh', NULL, 'Farmer', 'Tamil Nadu', 'Vellore', 'vellore', NULL, '2026-04-08 03:50:00'),
(18, '9989132100', '$2y$10$JHkmJL9x9Q9f6KmXMvH35ubzoS7FDBtmYxYYWPZcfsT7wsgNpIrEK', 'ravi', NULL, 'Farmer', 'Delhi', 'Shahdara', 'kadapa', NULL, '2026-04-08 09:01:01');

--
-- Indexes for dumped tables
--

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

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

--
-- Indexes for table `messages`
--
ALTER TABLE `messages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `sender_id` (`sender_id`),
  ADD KEY `receiver_id` (`receiver_id`);

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

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`id`),
  ADD KEY `shop_id` (`shop_id`);

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

--
-- Indexes for table `shops`
--
ALTER TABLE `shops`
  ADD PRIMARY KEY (`id`),
  ADD KEY `owner_id` (`owner_id`);

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

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

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

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

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `messages`
--
ALTER TABLE `messages`
  ADD CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `messages_ibfk_2` FOREIGN KEY (`receiver_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

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

--
-- Constraints for table `products`
--
ALTER TABLE `products`
  ADD CONSTRAINT `products_ibfk_1` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE CASCADE;

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

--
-- Constraints for table `shops`
--
ALTER TABLE `shops`
  ADD CONSTRAINT `shops_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
COMMIT;

