-- 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;

--
-- 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`);

--
-- 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;

--
-- 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;

