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