create database seerbharat; use seerbharat; SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; CREATE TABLE plant_parts ( part_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, part_name VARCHAR(100) NOT NULL UNIQUE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE usage_categories ( usage_category_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, category_name VARCHAR(150) NOT NULL UNIQUE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE conditions ( condition_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, condition_name VARCHAR(200) NOT NULL UNIQUE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE ingredients ( ingredient_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, ingredient_name VARCHAR(150) NOT NULL UNIQUE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE animal_types ( animal_type_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, animal_type_name VARCHAR(100) NOT NULL UNIQUE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE locations ( location_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, village_name VARCHAR(150), district VARCHAR(150), state VARCHAR(150), UNIQUE KEY uq_location (village_name, district, state) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE plants ( plant_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, local_name VARCHAR(200) NOT NULL, scientific_name VARCHAR(200), plant_type VARCHAR(100), botanical_identification_by VARCHAR(200), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, KEY idx_plants_local_name (local_name), KEY idx_plants_scientific_name (scientific_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE plant_alternate_names ( alt_name_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, plant_id INT UNSIGNED NOT NULL, alternate_name VARCHAR(200) NOT NULL, language_or_dialect VARCHAR(100), FOREIGN KEY (plant_id) REFERENCES plants(plant_id) ON DELETE CASCADE, UNIQUE KEY uq_plant_alt_name (plant_id, alternate_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE informants ( informant_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tribe_name VARCHAR(150), age_raw VARCHAR(20), age_min SMALLINT UNSIGNED, age_max SMALLINT UNSIGNED, gender VARCHAR(30), role_in_community VARCHAR(150), knowledge_source VARCHAR(200), location_id INT UNSIGNED, FOREIGN KEY (location_id) REFERENCES locations(location_id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE survey_entries ( entry_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, original_entry_id VARCHAR(100) UNIQUE, submitted_at DATETIME, timestamp_2 DATETIME, consent_given BOOLEAN NOT NULL DEFAULT FALSE, plant_id INT UNSIGNED NOT NULL, informant_id INT UNSIGNED, location_id INT UNSIGNED, gps_latitude DECIMAL(10,7), gps_longitude DECIMAL(10,7), usage_category_id INT UNSIGNED, purpose_description TEXT, is_used_for_animals BOOLEAN DEFAULT FALSE, condition_id INT UNSIGNED, condition_as_reported TEXT, detailed_disease_description TEXT, preparation_method TEXT, mode_of_use TEXT, ingredients_mixed_raw TEXT, used_alone_or_combined VARCHAR(255), dosage_quantity DECIMAL(10,2), dosage_unit VARCHAR(50), frequency_of_administration VARCHAR(150), effectiveness_rating DECIMAL(3,1), has_cultural_ritual_use BOOLEAN DEFAULT FALSE, is_culturally_sensitive VARCHAR(100), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (plant_id) REFERENCES plants(plant_id) ON DELETE RESTRICT, FOREIGN KEY (informant_id) REFERENCES informants(informant_id) ON DELETE SET NULL, FOREIGN KEY (location_id) REFERENCES locations(location_id) ON DELETE SET NULL, FOREIGN KEY (usage_category_id) REFERENCES usage_categories(usage_category_id) ON DELETE SET NULL, FOREIGN KEY (condition_id) REFERENCES conditions(condition_id) ON DELETE SET NULL, KEY idx_entries_plant (plant_id), KEY idx_entries_informant (informant_id), KEY idx_entries_condition (condition_id), KEY idx_entries_location (location_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE entry_parts_used ( entry_id INT UNSIGNED NOT NULL, part_id INT UNSIGNED NOT NULL, PRIMARY KEY (entry_id, part_id), FOREIGN KEY (entry_id) REFERENCES survey_entries(entry_id) ON DELETE CASCADE, FOREIGN KEY (part_id) REFERENCES plant_parts(part_id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE entry_ingredients ( entry_id INT UNSIGNED NOT NULL, ingredient_id INT UNSIGNED NOT NULL, notes VARCHAR(255), PRIMARY KEY (entry_id, ingredient_id), FOREIGN KEY (entry_id) REFERENCES survey_entries(entry_id) ON DELETE CASCADE, FOREIGN KEY (ingredient_id) REFERENCES ingredients(ingredient_id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE entry_animal_types ( entry_id INT UNSIGNED NOT NULL, animal_type_id INT UNSIGNED NOT NULL, PRIMARY KEY (entry_id, animal_type_id), FOREIGN KEY (entry_id) REFERENCES survey_entries(entry_id) ON DELETE CASCADE, FOREIGN KEY (animal_type_id) REFERENCES animal_types(animal_type_id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE entry_plant_combinations ( combination_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, entry_id INT UNSIGNED NOT NULL, related_plant_name VARCHAR(200) NOT NULL, related_plant_id INT UNSIGNED, combination_type ENUM('Commonly Combined With','Never Combine With') NOT NULL, FOREIGN KEY (entry_id) REFERENCES survey_entries(entry_id) ON DELETE CASCADE, FOREIGN KEY (related_plant_id) REFERENCES plants(plant_id) ON DELETE SET NULL, KEY idx_combo_entry (entry_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE ritual_details ( entry_id INT UNSIGNED PRIMARY KEY, is_sacred_ritual VARCHAR(150), ritual_use_description TEXT, who_can_collect VARCHAR(200), occasion VARCHAR(200), symbolic_meaning TEXT, ritual_process TEXT, pre_collection_ritual TEXT, sacred_lunar_day_restrictions VARCHAR(200), FOREIGN KEY (entry_id) REFERENCES survey_entries(entry_id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE safety_info ( entry_id INT UNSIGNED PRIMARY KEY, side_effects TEXT, side_effects_observed TEXT, restrictions TEXT, foods_activities_to_avoid TEXT, FOREIGN KEY (entry_id) REFERENCES survey_entries(entry_id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE availability_status ( entry_id INT UNSIGNED PRIMARY KEY, season_available VARCHAR(150), availability VARCHAR(100), availability_vs_20_years_ago VARCHAR(100), cause_of_reduction TEXT, known_abundance_area VARCHAR(255), can_be_home_grown VARCHAR(150), replacement_plant_name VARCHAR(200), replacement_plant_id INT UNSIGNED, FOREIGN KEY (entry_id) REFERENCES survey_entries(entry_id) ON DELETE CASCADE, FOREIGN KEY (replacement_plant_id) REFERENCES plants(plant_id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE entry_media ( media_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, entry_id INT UNSIGNED NOT NULL, media_type ENUM('Photo','Audio') NOT NULL, file_url VARCHAR(500) NOT NULL, FOREIGN KEY (entry_id) REFERENCES survey_entries(entry_id) ON DELETE CASCADE, KEY idx_media_entry (entry_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; SET FOREIGN_KEY_CHECKS = 1; ALTER TABLE ritual_details MODIFY COLUMN is_sacred_ritual VARCHAR(255), MODIFY COLUMN who_can_collect VARCHAR(255), MODIFY COLUMN occasion VARCHAR(255); ALTER TABLE survey_entries MODIFY COLUMN dosage_unit VARCHAR(255), MODIFY COLUMN used_alone_or_combined VARCHAR(255), MODIFY COLUMN is_culturally_sensitive VARCHAR(255);