-- ============================================================ -- Genotype–Environment–Management (G×E×M) Framework -- Database Schema -- ============================================================ CREATE DATABASE IF NOT EXISTS gem_framework CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE gem_framework; -- ------------------------------------------------------------ -- 1. GENOTYPES (crop variety / breeding line data) -- ------------------------------------------------------------ CREATE TABLE genotypes ( genotype_id INT AUTO_INCREMENT PRIMARY KEY, genotype_code VARCHAR(50) NOT NULL UNIQUE, -- e.g. "IR-2451" genotype_name VARCHAR(150), crop_species VARCHAR(100), pedigree VARCHAR(255), breeding_program VARCHAR(150), market_class VARCHAR(100), genomic_data_ref VARCHAR(255), -- pointer/path to SNP or marker file used by ML service notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- ------------------------------------------------------------ -- 2. ENVIRONMENTS (site / season / agro-climatic conditions) -- ------------------------------------------------------------ CREATE TABLE environments ( environment_id INT AUTO_INCREMENT PRIMARY KEY, environment_code VARCHAR(50) NOT NULL UNIQUE, -- e.g. "MP-SEONI-2025R" site_name VARCHAR(150), country VARCHAR(100), region VARCHAR(100), latitude DECIMAL(9,6), longitude DECIMAL(9,6), season VARCHAR(50), -- e.g. "Kharif 2025" soil_type VARCHAR(100), avg_rainfall_mm DECIMAL(8,2), avg_temp_c DECIMAL(5,2), weather_data_ref VARCHAR(255), -- pointer to IoT/MET time-series used by ML service notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- ------------------------------------------------------------ -- 3. MANAGEMENT PRACTICES (agronomic treatment/plan) -- ------------------------------------------------------------ CREATE TABLE management_practices ( management_id INT AUTO_INCREMENT PRIMARY KEY, management_code VARCHAR(50) NOT NULL UNIQUE, -- e.g. "MGT-HIGHN-01" plan_name VARCHAR(150), sowing_date DATE, sowing_density DECIMAL(8,2), -- plants/m2 irrigation_type VARCHAR(100), fertilizer_n_kg_ha DECIMAL(8,2), fertilizer_p_kg_ha DECIMAL(8,2), fertilizer_k_kg_ha DECIMAL(8,2), pest_mgmt_strategy VARCHAR(150), notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- ------------------------------------------------------------ -- 4. TRIALS (links one Genotype + one Environment + one Management -- plan together — this is the unit the ML pipeline trains on) -- ------------------------------------------------------------ CREATE TABLE trials ( trial_id INT AUTO_INCREMENT PRIMARY KEY, trial_code VARCHAR(50) NOT NULL UNIQUE, genotype_id INT NOT NULL, environment_id INT NOT NULL, management_id INT NOT NULL, trial_year YEAR, yield_kg_ha DECIMAL(10,2), -- observed outcome (label for training) quality_score DECIMAL(5,2), stress_index DECIMAL(5,2), data_source VARCHAR(150), -- e.g. "MET", "Commercial Breeding DB" qc_passed TINYINT(1) DEFAULT 0, -- Stage 1 quality-control flag created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (genotype_id) REFERENCES genotypes(genotype_id) ON DELETE CASCADE, FOREIGN KEY (environment_id) REFERENCES environments(environment_id) ON DELETE CASCADE, FOREIGN KEY (management_id) REFERENCES management_practices(management_id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ------------------------------------------------------------ -- 5. ML MODEL RUNS (metadata for each call to the Python ML API — -- Stages 3-4 of the framework) -- ------------------------------------------------------------ CREATE TABLE model_runs ( run_id INT AUTO_INCREMENT PRIMARY KEY, run_label VARCHAR(150), model_type ENUM('RandomForest','XGBoost','DNN','LSTM','Ensemble_Stack') NOT NULL, status ENUM('queued','running','completed','failed') DEFAULT 'queued', requested_by VARCHAR(100), api_request_payload JSON, api_response_payload JSON, metrics_json JSON, -- RMSE, R2, feature importances, SHAP summary, etc. started_at TIMESTAMP NULL, completed_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- ------------------------------------------------------------ -- 6. RECOMMENDATIONS (Stage 5 output — ranked genotype list for -- a target environment + management plan) -- ------------------------------------------------------------ CREATE TABLE recommendations ( recommendation_id INT AUTO_INCREMENT PRIMARY KEY, run_id INT NOT NULL, target_environment_id INT NOT NULL, target_management_id INT NOT NULL, genotype_id INT NOT NULL, predicted_yield DECIMAL(10,2), predicted_gg_score DECIMAL(6,4), -- the GG projected-performance score in the diagram rank_position INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (run_id) REFERENCES model_runs(run_id) ON DELETE CASCADE, FOREIGN KEY (target_environment_id) REFERENCES environments(environment_id) ON DELETE CASCADE, FOREIGN KEY (target_management_id) REFERENCES management_practices(management_id) ON DELETE CASCADE, FOREIGN KEY (genotype_id) REFERENCES genotypes(genotype_id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ------------------------------------------------------------ -- 7. USERS (session-based auth) -- No seed row here on purpose: the app's login.php detects an empty -- users table and shows a "create the first admin account" form, -- so passwords are always hashed by PHP's password_hash() rather -- than pasted into SQL. -- ------------------------------------------------------------ CREATE TABLE users ( user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, full_name VARCHAR(150), role ENUM('admin','breeder','viewer') DEFAULT 'breeder', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- ============================================================ -- SEED DATA (small sample so the dashboard isn't empty on first run) -- ============================================================ INSERT INTO genotypes (genotype_code, genotype_name, crop_species, pedigree, breeding_program, market_class) VALUES ('IR-2451','Indica Rice 2451','Rice','IR64 x IR72','National Rice Program','Long Grain'), ('WH-1102','Winter Wheat 1102','Wheat','HD2967 x PBW550','Wheat Improvement Program','Bread Wheat'), ('MZ-0087','Maize Hybrid 0087','Maize','CML312 x CML445','Maize Hybrid Program','Yellow Dent'); INSERT INTO environments (environment_code, site_name, country, region, latitude, longitude, season, soil_type, avg_rainfall_mm, avg_temp_c) VALUES ('MP-SEONI-25K','Seoni Research Station','India','Madhya Pradesh',22.0850,79.5470,'Kharif 2025','Clay Loam',1150.5,27.3), ('MP-JBP-25R','Jabalpur Trial Site','India','Madhya Pradesh',23.1815,79.9864,'Rabi 2025','Sandy Loam',420.2,21.8); INSERT INTO management_practices (management_code, plan_name, sowing_date, sowing_density, irrigation_type, fertilizer_n_kg_ha, fertilizer_p_kg_ha, fertilizer_k_kg_ha, pest_mgmt_strategy) VALUES ('MGT-HIGHN-01','High Nitrogen Intensive','2025-06-15',33.0,'Drip',150.0,60.0,40.0,'IPM'), ('MGT-LOWIN-02','Low Input Conservation','2025-11-01',25.0,'Rainfed',60.0,30.0,20.0,'Biocontrol'); INSERT INTO trials (trial_code, genotype_id, environment_id, management_id, trial_year, yield_kg_ha, quality_score, stress_index, data_source, qc_passed) VALUES ('TRIAL-0001',1,1,1,2025,5820.40,8.2,0.15,'MET',1), ('TRIAL-0002',2,2,2,2025,4310.75,7.6,0.32,'Commercial Breeding DB',1), ('TRIAL-0003',3,1,1,2025,9120.10,8.9,0.10,'MET',1);