Schema.sql

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