-- =====================================================================
-- Reference / master data seed
-- Values grounded in open sources:
--  - NHB India apple crop profile (growing-season temp 21-24C,
--    rainfall need 100-125cm/yr, soil pH 5.5-6.5, NPK ratio 70:35:70)
--  - J&K / HP regional yield benchmark 11-13 t/ha (NHB)
--  - Real apple-belt districts: Shopian, Anantnag, Pulwama, Kulgam,
--    Baramulla, Ganderbal, Bandipora, Kupwara (Kashmir); Shimla, Kullu
--    (Himachal) — from SKUAST / news reporting on Kashmir apple belt
-- =====================================================================
USE agrifound;

INSERT INTO regions (name, state, latitude, longitude, elevation_m, avg_growing_season_temp_c, avg_annual_rainfall_mm, soil_type) VALUES
('Shopian',   'Jammu & Kashmir', 33.7157, 74.8320, 1980, 22.5, 1150, 'Loamy, pH 5.5-6.5'),
('Anantnag',  'Jammu & Kashmir', 33.7311, 75.1487, 1620, 23.0, 1100, 'Silty loam'),
('Pulwama',   'Jammu & Kashmir', 33.8710, 74.8930, 1630, 23.2, 1080, 'Loamy'),
('Baramulla', 'Jammu & Kashmir', 34.2090, 74.3436, 1580, 22.0, 1200, 'Loamy, alluvial'),
('Shimla',    'Himachal Pradesh',31.1048, 77.1734, 2200, 21.0, 1250, 'Loamy, well-drained'),
('Kullu',     'Himachal Pradesh',31.9576, 77.1095, 1280, 21.5, 1150, 'Sandy loam');

INSERT INTO orchards (region_id, name, variety, area_hectares, planting_year, pollinator_variety, target_yield_t_ha) VALUES
(1, 'Shopian Block-A (SKUAST Pilot)', 'Red Delicious', 4.20, 2011, 'Golden Delicious', 12.5),
(1, 'Shopian Block-B', 'Royal Delicious', 3.10, 2009, 'Red Delicious', 12.0),
(2, 'Anantnag Riverside Orchard', 'Golden Delicious', 2.80, 2013, 'Red Delicious', 11.5),
(3, 'Pulwama Terrace Orchard', 'Royal Delicious', 3.50, 2010, 'Golden Delicious', 12.0),
(5, 'Shimla Highland Orchard', 'Royal Delicious', 5.00, 2008, 'Granny Smith', 13.0),
(6, 'Kullu Valley Orchard', 'Red Delicious', 2.60, 2012, 'Golden Delicious', 11.8);

-- Sensor mesh: 3 soil depths + 1 weather + 1 tree-physiology node per orchard (scaled down demo of the 50+ node network)
INSERT INTO sensor_nodes (orchard_id, node_type, label, install_date, depth_cm) VALUES
(1,'soil','Soil Node A1 - 0-10cm','2023-03-01',10),
(1,'soil','Soil Node A1 - 10-30cm','2023-03-01',30),
(1,'soil','Soil Node A1 - 30-60cm','2023-03-01',60),
(1,'weather','Weather Station A1','2023-03-01',NULL),
(1,'tree_physiology','Tree Sensor Cluster A1','2023-03-01',NULL),
(2,'soil','Soil Node B1 - 0-10cm','2023-03-01',10),
(2,'soil','Soil Node B1 - 10-30cm','2023-03-01',30),
(2,'soil','Soil Node B1 - 30-60cm','2023-03-01',60),
(2,'weather','Weather Station B1','2023-03-01',NULL),
(2,'tree_physiology','Tree Sensor Cluster B1','2023-03-01',NULL),
(3,'soil','Soil Node C1 - 0-10cm','2023-03-01',10),
(3,'soil','Soil Node C1 - 10-30cm','2023-03-01',30),
(3,'soil','Soil Node C1 - 30-60cm','2023-03-01',60),
(3,'weather','Weather Station C1','2023-03-01',NULL),
(3,'tree_physiology','Tree Sensor Cluster C1','2023-03-01',NULL),
(4,'soil','Soil Node D1 - 0-10cm','2023-03-01',10),
(4,'soil','Soil Node D1 - 10-30cm','2023-03-01',30),
(4,'soil','Soil Node D1 - 30-60cm','2023-03-01',60),
(4,'weather','Weather Station D1','2023-03-01',NULL),
(4,'tree_physiology','Tree Sensor Cluster D1','2023-03-01',NULL),
(5,'soil','Soil Node E1 - 0-10cm','2023-03-01',10),
(5,'soil','Soil Node E1 - 10-30cm','2023-03-01',30),
(5,'soil','Soil Node E1 - 30-60cm','2023-03-01',60),
(5,'weather','Weather Station E1','2023-03-01',NULL),
(5,'tree_physiology','Tree Sensor Cluster E1','2023-03-01',NULL),
(6,'soil','Soil Node F1 - 0-10cm','2023-03-01',10),
(6,'soil','Soil Node F1 - 10-30cm','2023-03-01',30),
(6,'soil','Soil Node F1 - 30-60cm','2023-03-01',60),
(6,'weather','Weather Station F1','2023-03-01',NULL),
(6,'tree_physiology','Tree Sensor Cluster F1','2023-03-01',NULL);

-- Pest/disease catalog with the causal pathways named explicitly in the proposal & tech-details doc
INSERT INTO pest_disease_catalog (name, type, typical_pathway) VALUES
('Apple Scab', 'disease', 'Wet-spell during blossom -> leaf/fruit infection; soil surface humidity aids overwintering inoculum survival (per proposal Module IV).'),
('San Jose Scale', 'pest', 'Early detection target of molecular biomarker panel (>=14 days lead, per Performance Charter).'),
('Codling Moth', 'pest', 'Soil temperature & moisture govern pupation success (Soil-Pest coupling, Module II).'),
('Apple Blotch Leaf Miner', 'pest', 'Rapid multi-generational buildup under warming temperatures (recent Kashmir field reports).'),
('Root Water-Uptake Collapse', 'disease', 'Dry spell -> soil theta < critical threshold -> root death -> nutrient collapse -> fruit abortion (irreversible tipping point).'),
('Salinity-Induced Canopy Stress', 'disease', 'Saline irrigation -> EC buildup -> osmotic stress -> reduced water uptake -> canopy stress (slow accumulation, sudden collapse).'),
('Nitrogen-Mineralization Collapse', 'disease', 'Soil temp > 28C -> microbial die-off -> N mineralization stops -> N deficiency -> pest susceptibility.'),
('Soil Compaction Drought Sensitivity', 'disease', 'Compaction layer -> root restriction -> shallow rooting -> hidden drought sensitivity.');

-- Cold storage facilities (standard apple CA/cold-store parameters: ~0-4C, 90-95% RH)
INSERT INTO storage_facilities (name, location, storage_type, capacity_tonnes, target_temp_c, target_humidity_pct) VALUES
('Shopian Integrated Cold Chain', 'Shopian, J&K', 'controlled_atmosphere', 5000, 1.0, 92.0),
('Anantnag CA Store', 'Anantnag, J&K', 'controlled_atmosphere', 3000, 0.5, 93.0),
('Parwanoo Cold Storage Hub', 'Parwanoo, HP', 'cold_storage', 8000, 2.0, 90.0);

-- Demo users (password_hash placeholders generated by seed script; see python/create_users.py)