-- =====================================================================
-- AgriFound Digital Twin — MySQL Schema
-- Covers: orchard growth & health, cold-storage supply chain,
--         fruit quality / ripeness sensing
-- Grounded in: Revised_digital_twin_proposal.docx (AgriFormer / MS-CDT)
--              Other_Technical_Details_for_digital_twin.docx
-- =====================================================================

DROP DATABASE IF EXISTS agrifound;
CREATE DATABASE agrifound CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE agrifound;

-- ---------------------------------------------------------------------
-- 1. GEOGRAPHY / ORCHARD MASTER DATA
-- ---------------------------------------------------------------------

CREATE TABLE regions (
    region_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    state VARCHAR(100) NOT NULL,
    latitude DECIMAL(8,5) NOT NULL,
    longitude DECIMAL(8,5) NOT NULL,
    elevation_m INT,
    avg_growing_season_temp_c DECIMAL(4,1) COMMENT 'Real agro-climatic normal',
    avg_annual_rainfall_mm INT COMMENT 'Real agro-climatic normal',
    soil_type VARCHAR(100),
    data_source VARCHAR(255) DEFAULT 'NHB India / SKUAST agro-climatic references'
) ENGINE=InnoDB;

CREATE TABLE orchards (
    orchard_id INT AUTO_INCREMENT PRIMARY KEY,
    region_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    variety VARCHAR(100) NOT NULL COMMENT 'e.g. Red Delicious, Royal Delicious, Golden Delicious',
    area_hectares DECIMAL(6,2) NOT NULL,
    planting_year YEAR,
    pollinator_variety VARCHAR(100),
    target_yield_t_ha DECIMAL(5,2) COMMENT 'Regional benchmark yield',
    FOREIGN KEY (region_id) REFERENCES regions(region_id)
) ENGINE=InnoDB;

CREATE TABLE sensor_nodes (
    node_id INT AUTO_INCREMENT PRIMARY KEY,
    orchard_id INT NOT NULL,
    node_type ENUM('soil','weather','tree_physiology') NOT NULL,
    label VARCHAR(100) NOT NULL,
    install_date DATE,
    depth_cm INT NULL COMMENT 'For soil nodes: 0-10 / 10-30 / 30-60',
    FOREIGN KEY (orchard_id) REFERENCES orchards(orchard_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 2. MODULE I/II — SENSOR FUSION (soil as first-class agent)
-- ---------------------------------------------------------------------

CREATE TABLE soil_readings (
    reading_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    node_id INT NOT NULL,
    ts DATETIME NOT NULL,
    depth_cm INT NOT NULL,
    vwc_pct DECIMAL(5,2) COMMENT 'Volumetric water content',
    soil_temp_c DECIMAL(4,1),
    ec_ds_m DECIMAL(4,2) COMMENT 'Salinity; tipping point > 2.5 dS/m',
    no3_ppm DECIMAL(6,2) COMMENT 'Tipping point < 50 ppm',
    p_ppm DECIMAL(6,2),
    k_ppm DECIMAL(6,2),
    microbial_biomass_c_mgkg DECIMAL(7,2),
    FOREIGN KEY (node_id) REFERENCES sensor_nodes(node_id),
    INDEX idx_soil_ts (node_id, ts)
) ENGINE=InnoDB;

CREATE TABLE weather_readings (
    reading_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    node_id INT NOT NULL,
    ts DATETIME NOT NULL,
    air_temp_c DECIMAL(4,1),
    humidity_pct DECIMAL(5,2),
    leaf_wetness_hrs DECIMAL(4,1),
    solar_radiation_wm2 DECIMAL(6,1),
    rainfall_mm DECIMAL(6,2),
    wind_speed_kmh DECIMAL(5,1),
    FOREIGN KEY (node_id) REFERENCES sensor_nodes(node_id),
    INDEX idx_weather_ts (node_id, ts)
) ENGINE=InnoDB;

CREATE TABLE tree_physiology_readings (
    reading_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    node_id INT NOT NULL,
    ts DATETIME NOT NULL,
    sap_flow_g_hr DECIMAL(7,2),
    fruit_diameter_mm DECIMAL(5,2),
    trunk_tilt_deg DECIMAL(4,2),
    phenological_stage VARCHAR(40) COMMENT 'dormant/bloom/fruit-set/growth/maturity/harvest',
    FOREIGN KEY (node_id) REFERENCES sensor_nodes(node_id),
    INDEX idx_tree_ts (node_id, ts)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 3. PEST / DISEASE + CAUSAL / TIPPING POINT ENGINE
-- ---------------------------------------------------------------------

CREATE TABLE pest_disease_catalog (
    catalog_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    type ENUM('pest','disease') NOT NULL,
    typical_pathway TEXT COMMENT 'Narrative of the causal chain from the proposal'
) ENGINE=InnoDB;

CREATE TABLE pest_disease_observations (
    obs_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    orchard_id INT NOT NULL,
    catalog_id INT NOT NULL,
    ts DATETIME NOT NULL,
    severity_pct DECIMAL(5,2),
    source ENUM('sensor_biomarker','scouting','model_inferred') NOT NULL,
    FOREIGN KEY (orchard_id) REFERENCES orchards(orchard_id),
    FOREIGN KEY (catalog_id) REFERENCES pest_disease_catalog(catalog_id)
) ENGINE=InnoDB;

CREATE TABLE tipping_point_alerts (
    alert_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    orchard_id INT NOT NULL,
    ts DATETIME NOT NULL,
    risk_score DECIMAL(5,2) NOT NULL COMMENT '0-100 cascading-failure probability',
    risk_category ENUM('low','moderate','high','critical') NOT NULL,
    predicted_horizon_days INT COMMENT 'Lead time of the prediction, per MS-CFP charter (>=14 days target)',
    description VARCHAR(255),
    model_version VARCHAR(40) DEFAULT 'MS-CFP-lite-v1',
    FOREIGN KEY (orchard_id) REFERENCES orchards(orchard_id),
    INDEX idx_alert_ts (orchard_id, ts)
) ENGINE=InnoDB;

CREATE TABLE causal_pathway_log (
    log_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    alert_id BIGINT NOT NULL,
    pathway_name VARCHAR(150) NOT NULL,
    contributing_factors TEXT COMMENT 'JSON-ish explanation of the driving variables',
    effect_size DECIMAL(5,2) COMMENT 'Estimated contribution to yield-loss risk',
    FOREIGN KEY (alert_id) REFERENCES tipping_point_alerts(alert_id)
) ENGINE=InnoDB;

CREATE TABLE prescriptive_recommendations (
    rec_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    alert_id BIGINT NOT NULL,
    action VARCHAR(255) NOT NULL,
    expected_water_savings_pct DECIMAL(5,2),
    expected_pesticide_savings_pct DECIMAL(5,2),
    FOREIGN KEY (alert_id) REFERENCES tipping_point_alerts(alert_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 4. COLD STORAGE / SUPPLY CHAIN
-- ---------------------------------------------------------------------

CREATE TABLE storage_facilities (
    facility_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    location VARCHAR(150),
    storage_type ENUM('cold_storage','controlled_atmosphere') NOT NULL,
    capacity_tonnes DECIMAL(8,2),
    target_temp_c DECIMAL(4,1) DEFAULT 1.0 COMMENT 'Standard apple CA/cold store target ~0-4C',
    target_humidity_pct DECIMAL(5,2) DEFAULT 92.0
) ENGINE=InnoDB;

CREATE TABLE storage_batches (
    batch_id INT AUTO_INCREMENT PRIMARY KEY,
    facility_id INT NOT NULL,
    orchard_id INT NOT NULL,
    variety VARCHAR(100),
    intake_date DATE NOT NULL,
    quantity_kg DECIMAL(9,2),
    intake_quality_grade CHAR(1) COMMENT 'A/B/C at intake',
    FOREIGN KEY (facility_id) REFERENCES storage_facilities(facility_id),
    FOREIGN KEY (orchard_id) REFERENCES orchards(orchard_id)
) ENGINE=InnoDB;

CREATE TABLE storage_conditions_log (
    log_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    facility_id INT NOT NULL,
    ts DATETIME NOT NULL,
    temp_c DECIMAL(4,1),
    humidity_pct DECIMAL(5,2),
    co2_pct DECIMAL(5,2),
    o2_pct DECIMAL(5,2),
    ethylene_ppm DECIMAL(6,3),
    FOREIGN KEY (facility_id) REFERENCES storage_facilities(facility_id),
    INDEX idx_storage_ts (facility_id, ts)
) ENGINE=InnoDB;

CREATE TABLE storage_risk_log (
    risk_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    batch_id INT NOT NULL,
    ts DATETIME NOT NULL,
    spoilage_risk_score DECIMAL(5,2) NOT NULL COMMENT '0-100',
    risk_category ENUM('low','moderate','high','critical') NOT NULL,
    notes VARCHAR(255),
    FOREIGN KEY (batch_id) REFERENCES storage_batches(batch_id),
    INDEX idx_risk_ts (batch_id, ts)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 5. QUALITY / RIPENESS SENSING
-- ---------------------------------------------------------------------

CREATE TABLE quality_inspections (
    inspection_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    batch_id INT NULL,
    orchard_id INT NULL,
    ts DATETIME NOT NULL,
    firmness_kgf DECIMAL(4,2) COMMENT 'Typical harvest-ready apple: 7-9 kgf',
    brix_pct DECIMAL(4,2) COMMENT 'Soluble solids; typical 10-14%',
    starch_index DECIMAL(3,1) COMMENT '1 (immature) - 10 (fully ripe), Cornell/CTIFL-style index',
    background_color VARCHAR(30) COMMENT 'green / green-yellow / yellow / red-blush',
    defect_pct DECIMAL(5,2),
    inspector ENUM('sensor_nir','human_grader','model_inferred') NOT NULL,
    FOREIGN KEY (batch_id) REFERENCES storage_batches(batch_id),
    FOREIGN KEY (orchard_id) REFERENCES orchards(orchard_id)
) ENGINE=InnoDB;

CREATE TABLE ripeness_classifications (
    class_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    inspection_id BIGINT NOT NULL,
    predicted_stage ENUM('immature','pre_climacteric','optimal_harvest','ripe','overripe') NOT NULL,
    confidence DECIMAL(5,2) NOT NULL,
    recommended_action VARCHAR(255),
    FOREIGN KEY (inspection_id) REFERENCES quality_inspections(inspection_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 6. APP USERS (simple auth for farmer/agronomist dashboard)
-- ---------------------------------------------------------------------

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('farmer','agronomist','admin') NOT NULL DEFAULT 'agronomist',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;