Mackey30/deepsite-project-w4btd

1-- Farmflow Optimizer Database Structure2-- Created for Smart Irrigation Management System3 4-- Create database5CREATE DATABASE IF NOT EXISTS farmflow CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;6USE farmflow;7 8-- ============================================9-- TABLE: sensors10-- Stores all farm sensors and their configuration11-- ============================================12CREATE TABLE sensors (13    id INT AUTO_INCREMENT PRIMARY KEY,14    name VARCHAR(100) NOT NULL,15    zone VARCHAR(50) NOT NULL,16    type ENUM('moisture', 'temperature', 'humidity', 'rain', 'ph', 'nutrient') NOT NULL DEFAULT 'moisture',17    status ENUM('active', 'inactive', 'warning', 'maintenance', 'offline') DEFAULT 'active',18    battery_level INT DEFAULT 100,19    location_description VARCHAR(255),20    min_threshold DECIMAL(5,2) DEFAULT NULL,21    max_threshold DECIMAL(5,2) DEFAULT NULL,22    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,23    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,24    last_reading DECIMAL(5,2) DEFAULT NULL,25    last_reading_time TIMESTAMP NULL26) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;27 28-- ============================================29-- TABLE: sensor_readings30-- Stores historical sensor data readings31-- ============================================32CREATE TABLE sensor_readings (33    id INT AUTO_INCREMENT PRIMARY KEY,34    sensor_id INT NOT NULL,35    moisture_level DECIMAL(5,2) DEFAULT NULL,36    temperature DECIMAL(5,2) DEFAULT NULL,37    humidity DECIMAL(5,2) DEFAULT NULL,38    ph_level DECIMAL(4,2) DEFAULT NULL,39    battery_level INT DEFAULT NULL,40    reading_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,41    INDEX idx_sensor_time (sensor_id, reading_time),42    FOREIGN KEY (sensor_id) REFERENCES sensors(id) ON DELETE CASCADE43) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;44 45-- ============================================46-- TABLE: irrigation_schedules47-- Stores automated irrigation schedules48-- ============================================49CREATE TABLE irrigation_schedules (50    id INT AUTO_INCREMENT PRIMARY KEY,51    name VARCHAR(100) NOT NULL,52    zone VARCHAR(50) NOT NULL,53    start_time TIME NOT NULL,54    duration_minutes INT NOT NULL DEFAULT 30,55    frequency ENUM('daily', 'weekly', 'custom') NOT NULL DEFAULT 'daily',56    days_of_week VARCHAR(50) DEFAULT NULL COMMENT 'Comma-separated days: Mon,Tue,Wed',57    water_volume_liters INT DEFAULT 500,58    status ENUM('active', 'paused', 'completed', 'draft') DEFAULT 'active',59    moisture_threshold DECIMAL(5,2) DEFAULT NULL COMMENT 'Trigger only if below this level',60    weather_dependent BOOLEAN DEFAULT FALSE,61    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,62    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,63    last_executed TIMESTAMP NULL,64    next_scheduled TIMESTAMP NULL,65    created_by VARCHAR(100) DEFAULT 'System'66) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;67 68-- ============================================69-- TABLE: irrigation_logs70-- Stores executed irrigation sessions71-- ============================================72CREATE TABLE irrigation_logs (73    id INT AUTO_INCREMENT PRIMARY KEY,74    schedule_id INT,75    zone VARCHAR(50) NOT NULL,76    start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,77    end_time TIMESTAMP NULL,78    duration_minutes INT DEFAULT NULL,79    water_usage INT DEFAULT 0 COMMENT 'Liters used',80    status ENUM('running', 'completed', 'cancelled', 'failed') DEFAULT 'running',81    trigger_type ENUM('scheduled', 'manual', 'moisture_trigger', 'weather_based') DEFAULT 'scheduled',82    moisture_before DECIMAL(5,2) DEFAULT NULL,83    moisture_after DECIMAL(5,2) DEFAULT NULL,84    temperature DECIMAL(5,2) DEFAULT NULL,85    notes TEXT,86    FOREIGN KEY (schedule_id) REFERENCES irrigation_schedules(id) ON DELETE SET NULL87) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;88 89-- ============================================90-- TABLE: tasks91-- Stores farm management tasks and to-do items92-- ============================================93CREATE TABLE tasks (94    id INT AUTO_INCREMENT PRIMARY KEY,95    title VARCHAR(200) NOT NULL,96    description TEXT,97    zone VARCHAR(50) DEFAULT NULL,98    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',99    status ENUM('pending', 'in_progress', 'completed', 'cancelled', 'overdue') DEFAULT 'pending',100    assigned_to VARCHAR(100) DEFAULT NULL,101    due_date DATE DEFAULT NULL,102    completed_at TIMESTAMP NULL,103    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,104    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,105    created_by VARCHAR(100) DEFAULT 'System',106    task_type ENUM('maintenance', 'irrigation', 'harvest', 'inspection', 'general') DEFAULT 'general',107    estimated_duration INT DEFAULT NULL COMMENT 'Minutes'108) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;109 110-- ============================================111-- TABLE: alerts112-- Stores system alerts and notifications113-- ============================================114CREATE TABLE alerts (115    id INT AUTO_INCREMENT PRIMARY KEY,116    type ENUM('info', 'warning', 'danger', 'success') DEFAULT 'info',117    title VARCHAR(200) NOT NULL,118    message TEXT NOT NULL,119    status ENUM('pending', 'acknowledged', 'resolved', 'ignored') DEFAULT 'pending',120    source ENUM('sensor', 'schedule', 'system', 'weather', 'user') DEFAULT 'system',121    related_sensor_id INT DEFAULT NULL,122    related_schedule_id INT DEFAULT NULL,123    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,124    acknowledged_at TIMESTAMP NULL,125    acknowledged_by VARCHAR(100) NULL,126    resolved_at TIMESTAMP NULL,127    FOREIGN KEY (related_sensor_id) REFERENCES sensors(id) ON DELETE SET NULL,128    FOREIGN KEY (related_schedule_id) REFERENCES irrigation_schedules(id) ON DELETE SET NULL129) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;130 131-- ============================================132-- TABLE: weather_data133-- Stores local weather information for decision making134-- ============================================135CREATE TABLE weather_data (136    id INT AUTO_INCREMENT PRIMARY KEY,137    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,138    temperature DECIMAL(5,2) DEFAULT NULL,139    humidity DECIMAL(5,2) DEFAULT NULL,140    wind_speed DECIMAL(5,2) DEFAULT NULL,141    precipitation DECIMAL(5,2) DEFAULT NULL,142    precipitation_probability INT DEFAULT NULL,143    uv_index DECIMAL(3,1) DEFAULT NULL,144    condition_code VARCHAR(50) DEFAULT NULL,145    forecast_for_date DATE DEFAULT NULL146) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;147 148-- ============================================149-- TABLE: system_settings150-- Stores system-wide configuration151-- ============================================152CREATE TABLE system_settings (153    id INT AUTO_INCREMENT PRIMARY KEY,154    setting_key VARCHAR(100) NOT NULL UNIQUE,155    setting_value TEXT,156    setting_type ENUM('string', 'integer', 'boolean', 'json') DEFAULT 'string',157    description VARCHAR(255),158    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP159) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;160 161-- ============================================162-- TABLE: users (optional - for multi-user support)163-- ============================================164CREATE TABLE users (165    id INT AUTO_INCREMENT PRIMARY KEY,166    username VARCHAR(50) NOT NULL UNIQUE,167    email VARCHAR(100) NOT NULL UNIQUE,168    password_hash VARCHAR(255) NOT NULL,169    full_name VARCHAR(100),170    role ENUM('admin', 'operator', 'viewer') DEFAULT 'operator',171    is_active BOOLEAN DEFAULT TRUE,172    last_login TIMESTAMP NULL,173    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP174) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;175 176-- ============================================177-- INSERT SAMPLE DATA178-- ============================================179 180-- Sample Sensors181INSERT INTO sensors (name, zone, type, status, battery_level, location_description, min_threshold, max_threshold, last_reading, last_reading_time) VALUES182('Soil Moisture A1', 'Zone 1', 'moisture', 'active', 85, 'North field, row 3', 60.00, 80.00, 72.50, NOW() - INTERVAL 5 MINUTE),183('Temperature T1', 'Zone 1', 'temperature', 'active', 92, 'North field weather station', NULL, NULL, 24.50, NOW() - INTERVAL 3 MINUTE),184('Soil Moisture A2', 'Zone 2', 'moisture', 'active', 78, 'Central greenhouse bed A', 65.00, 85.00, 65.00, NOW() - INTERVAL 7 MINUTE),185('Soil Moisture A3', 'Zone 3', 'moisture', 'warning', 45, 'South orchard, tree line 2', 50.00, 75.00, 45.20, NOW() - INTERVAL 15 MINUTE),186('Humidity H1', 'Zone 2', 'humidity', 'active', 88, 'Central greenhouse interior', NULL, NULL, 68.00, NOW() - INTERVAL 2 MINUTE),187('Rain Gauge R1', 'Zone 1', 'rain', 'maintenance', 12, 'Weather station roof', NULL, NULL, 0.00, NOW() - INTERVAL 2 DAY),188('pH Sensor P1', 'Zone 2', 'ph', 'active', 95, 'Hydroponics tank A', 6.00, 7.00, 6.50, NOW() - INTERVAL 10 MINUTE);189 190-- Sample Sensor Readings (last 24 hours for moisture sensors)191INSERT INTO sensor_readings (sensor_id, moisture_level, temperature, humidity, reading_time) VALUES192(1, 70.00, 23.50, 65.00, NOW() - INTERVAL 24 HOUR),193(1, 71.50, 24.00, 66.00, NOW() - INTERVAL 12 HOUR),194(1, 72.50, 24.50, 65.00, NOW() - INTERVAL 5 MINUTE),195(3, 63.00, 22.80, 70.00, NOW() - INTERVAL 24 HOUR),196(3, 64.00, 23.00, 69.00, NOW() - INTERVAL 12 HOUR),197(3, 65.00, 23.20, 68.00, NOW() - INTERVAL 7 MINUTE),198(4, 48.00, 25.00, 60.00, NOW() - INTERVAL 24 HOUR),199(4, 46.50, 25.20, 58.00, NOW() - INTERVAL 12 HOUR),200(4, 45.20, 25.50, 55.00, NOW() - INTERVAL 15 MINUTE);201 202-- Sample Irrigation Schedules203INSERT INTO irrigation_schedules (name, zone, start_time, duration_minutes, frequency, days_of_week, water_volume_liters, status, moisture_threshold, weather_dependent, last_executed, next_scheduled) VALUES204('Morning Routine', 'Zone 1', '06:00:00', 30, 'daily', NULL, 450, 'active', 65.00, TRUE, NOW() - INTERVAL 1 DAY, NOW()),205('Evening Cool Down', 'Zone 2', '18:00:00', 45, 'daily', NULL, 675, 'active', NULL, FALSE, NOW() - INTERVAL 6 HOUR, NOW() + INTERVAL 12 HOUR),206('Deep Watering', 'Zone 3', '05:30:00', 60, 'weekly', 'Mon,Wed,Fri', 1200, 'paused', 50.00, FALSE, NOW() - INTERVAL 3 DAY, NOW() + INTERVAL 1 DAY),207('Seedling Care', 'Zone 1', '10:00:00', 15, 'daily', NULL, 225, 'active', 70.00, FALSE, NOW() - INTERVAL 4 HOUR, NOW() + INTERVAL 20 HOUR),208('Emergency Drought Mode', 'Zone 3', '12:00:00', 20, 'daily', NULL, 300, 'draft', 40.00, FALSE, NULL, NULL);209 210-- Sample Irrigation Logs (last 7 days)211INSERT INTO irrigation_logs (schedule_id, zone, start_time, end_time, duration_minutes, water_usage, status, trigger_type, moisture_before, moisture_after, temperature, notes) VALUES212(1, 'Zone 1', NOW() - INTERVAL 1 DAY, NOW() - INTERVAL 1 DAY + INTERVAL 30 MINUTE, 30, 450, 'completed', 'scheduled', 68.00, 75.00, 24.00, 'Normal morning irrigation'),213(2, 'Zone 2', NOW() - INTERVAL 6 HOUR, NOW() - INTERVAL 5 HOUR + INTERVAL 15 MINUTE, 45, 675, 'completed', 'scheduled', 62.00, 72.00, 23.50, 'Evening watering completed'),214(1, 'Zone 1', NOW() - INTERVAL 2 DAY, NOW() - INTERVAL 2 DAY + INTERVAL 30 MINUTE, 30, 440, 'completed', 'scheduled', 69.00, 76.00, 24.20, 'Slightly reduced flow rate'),215(3, 'Zone 3', NOW() - INTERVAL 3 DAY, NOW() - INTERVAL 3 DAY + INTERVAL 60 MINUTE, 60, 1200, 'completed', 'scheduled', 45.00, 68.00, 26.00, 'Deep watering for orchard'),216(4, 'Zone 1', NOW() - INTERVAL 4 HOUR, NOW() - INTERVAL 4 HOUR + INTERVAL 15 MINUTE, 15, 225, 'completed', 'scheduled', 71.00, 74.00, 25.00, 'Gentle seedling watering');217 218-- Update water usage for realistic daily totals (matching dashboard)219UPDATE irrigation_logs SET water_usage = 1250 WHERE DATE(start_time) = CURDATE();220 221-- Sample Tasks222INSERT INTO tasks (title, description, zone, priority, status, assigned_to, due_date, task_type, estimated_duration) VALUES223('Check drip irrigation lines', 'Inspect all drip lines in Zone 2 for clogs or leaks. Pay special attention to bed A and B connections.', 'Zone 2', 'high', 'pending', 'John', CURDATE(), 'maintenance', 45),224('Calibrate moisture sensors', 'Recalibrate sensors A2 and A3 in Zone 3 using standard solution. Document readings before and after.', 'Zone 3', 'medium', 'in_progress', 'Sarah', DATE_ADD(CURDATE(), INTERVAL 1 DAY), 'maintenance', 60),225('Review weekly water usage', 'Analyze water consumption reports and compare with weather data. Identify any anomalies.', NULL, 'low', 'pending', 'John', DATE_ADD(CURDATE(), INTERVAL 2 DAY), 'general', 30),226('Fertilizer application - Zone 2', 'Apply nitrogen fertilizer to vegetable crops in greenhouse. Use spreader setting 4.', 'Zone 2', 'high', 'pending', 'Mike', DATE_ADD(CURDATE(), INTERVAL 3 DAY), 'irrigation', 90),227('Clean water filters', 'Replace and clean main irrigation filters. Check pressure gauges before and after.', NULL, 'medium', 'completed', 'Sarah', DATE_SUB(CURDATE(), INTERVAL 1 DAY), 'maintenance', 30),228('Repair rain gauge', 'Rain gauge R1 showing low battery and inconsistent readings. Replace battery and clean funnel.', 'Zone 1', 'urgent', 'pending', 'Mike', CURDATE(), 'maintenance', 20),229('Harvest tomatoes - Zone 1', 'Pick ripe tomatoes from row 3-5. Expected yield: 50-60 crates.', 'Zone 1', 'medium', 'pending', 'Sarah', DATE_ADD(CURDATE(), INTERVAL 1 DAY), 'harvest', 180);230 231-- Mark one task as completed with timestamp232UPDATE tasks SET completed_at = NOW() - INTERVAL 2 HOUR WHERE id = 5;233 234-- Sample Alerts235INSERT INTO alerts (type, title, message, status, source, related_sensor_id, created_at) VALUES236('warning', 'Low Soil Moisture Alert', 'Soil moisture in Zone 3 has dropped below 50%. Current reading: 45.2%. Irrigation recommended.', 'pending', 'sensor', 4, NOW() - INTERVAL 2 HOUR),237('info', 'Schedule Completed', 'Irrigation schedule "Evening Cool Down" for Zone 2 completed successfully. Water used: 675L', 'acknowledged', 'schedule', NULL, NOW() - INTERVAL 6 HOUR),238('warning', 'Low Battery Warning', 'Sensor "Rain Gauge R1" battery level at 12%. Replacement needed within 48 hours.', 'pending', 'sensor', 6, NOW() - INTERVAL 12 HOUR),239('info', 'Daily Backup Complete', 'System database backup completed successfully. Size: 2.4MB', 'resolved', 'system', NULL, NOW() - INTERVAL 2 HOUR),240('success', 'Sensor Calibration Complete', 'Moisture sensor A2 calibration completed. Accuracy within 0.5% tolerance.', 'resolved', 'sensor', 3, NOW() - INTERVAL 1 DAY);241 242-- Sample Weather Data (last 7 days)243INSERT INTO weather_data (recorded_at, temperature, humidity, wind_speed, precipitation, precipitation_probability, condition_code, forecast_for_date) VALUES244(NOW() - INTERVAL 6 DAY, 26.50, 65.00, 12.00, 0.00, 10, 'sunny', DATE_SUB(CURDATE(), INTERVAL 6 DAY)),245(NOW() - INTERVAL 5 DAY, 27.00, 62.00, 15.00, 0.00, 5, 'sunny', DATE_SUB(CURDATE(), INTERVAL 5 DAY)),246(NOW() - INTERVAL 4 DAY, 25.80, 70.00, 10.00, 2.50, 80, 'rainy', DATE_SUB(CURDATE(), INTERVAL 4 DAY)),247(NOW() - INTERVAL 3 DAY, 24.50, 75.00, 8.00, 5.20, 90, 'rainy', DATE_SUB(CURDATE(), INTERVAL 3 DAY)),248(NOW() - INTERVAL 2 DAY, 26.00, 68.00, 11.00, 0.00, 20, 'cloudy', DATE_SUB(CURDATE(), INTERVAL 2 DAY)),249(NOW() - INTERVAL 1 DAY, 27.50, 60.00, 14.00, 0.00, 0, 'sunny', DATE_SUB(CURDATE(), INTERVAL 1 DAY)),250(NOW(), 28.00, 55.00, 12.00, 0.00, 10, 'partly_cloudy', CURDATE());251 252-- System Settings253INSERT INTO system_settings (setting_key, setting_value, setting_type, description) VALUES254('farm_name', 'Green Valley Farm', 'string', 'Name of the farm operation'),255('location', 'Agricultural Zone A', 'string', 'Physical location identifier'),256('timezone', 'UTC', 'string', 'System timezone'),257('auto_irrigation_enabled', 'true', 'boolean', 'Enable automatic irrigation triggering'),258('moisture_critical_threshold', '50', 'integer', 'Moisture level that triggers critical alerts'),259('water_price_per_liter', '0.002', 'string', 'Cost calculation for water usage reports'),260('maintenance_mode', 'false', 'boolean', 'System maintenance mode flag'),261('notification_email', 'admin@farmflow.local', 'string', 'Email for system notifications'),262('backup_frequency_hours', '24', 'integer', 'Automatic backup interval');263 264-- Create indexes for better performance265CREATE INDEX idx_sensors_zone ON sensors(zone);266CREATE INDEX idx_sensors_status ON sensors(status);267CREATE INDEX idx_schedules_status ON irrigation_schedules(status);268CREATE INDEX idx_tasks_status ON tasks(status);269CREATE INDEX idx_tasks_due_date ON tasks(due_date);270CREATE INDEX idx_alerts_status ON alerts(status);271CREATE INDEX idx_alerts_created ON alerts(created_at);272CREATE INDEX idx_logs_start_time ON irrigation_logs(start_time);273CREATE INDEX idx_readings_time ON sensor_readings(reading_time);274 275-- Create a view for dashboard statistics276CREATE VIEW dashboard_stats AS277SELECT 278    (SELECT COUNT(*) FROM sensors WHERE status = 'active') as active_sensors,279    (SELECT COUNT(*) FROM irrigation_schedules WHERE status = 'active') as active_schedules,280    (SELECT COUNT(*) FROM alerts WHERE status = 'pending') as pending_alerts,281    (SELECT COUNT(*) FROM tasks WHERE status = 'completed' AND DATE(completed_at) = CURDATE()) as completed_tasks_today,282    (SELECT AVG(moisture_level) FROM sensor_readings WHERE DATE(reading_time) = CURDATE()) as avg_moisture_today,283    (SELECT SUM(water_usage) FROM irrigation_logs WHERE DATE(start_time) = CURDATE()) as water_usage_today;284 285-- Stored procedure for generating daily reports (optional)286DELIMITER //287CREATE PROCEDURE GenerateDailyReport(IN report_date DATE)288BEGIN289    SELECT 290        report_date as date,291        COUNT(DISTINCT s.id) as total_sensors,292        AVG(sr.moisture_level) as avg_moisture,293        SUM(il.water_usage) as total_water_used,294        COUNT(il.id) as irrigation_cycles,295        AVG(il.duration_minutes) as avg_cycle_duration296    FROM sensors s297    LEFT JOIN sensor_readings sr ON s.id = sr.sensor_id AND DATE(sr.reading_time) = report_date298    LEFT JOIN irrigation_logs il ON DATE(il.start_time) = report_date299    GROUP BY report_date;300END //301DELIMITER ;302 303-- Add table comments for documentation304ALTER TABLE sensors COMMENT = 'IoT sensors deployed across farm zones';305ALTER TABLE sensor_readings COMMENT = 'Time-series data from all sensors';306ALTER TABLE irrigation_schedules COMMENT = 'Automated watering schedules';307ALTER TABLE irrigation_logs COMMENT = 'Historical record of irrigation events';308ALTER TABLE tasks COMMENT = 'Farm management and maintenance tasks';309ALTER TABLE alerts COMMENT = 'System notifications and warnings';310ALTER TABLE weather_data COMMENT = 'Local weather conditions for irrigation decisions';311ALTER TABLE system_settings COMMENT = 'Configuration parameters for the system';