DROP TABLE IF EXISTS plants3.characteristics;

SELECT * INTO plants3.characteristics
FROM (
  SELECT DISTINCT * FROM (
    SELECT
      p.plant_symbol,
      plant_master_id,
      coalesce(m.cultivar_name, g.cultivar_name, r.cultivar_name, s.cultivar_name) AS cultivar,
      full_scientific_name_without_author,
      primary_vernacular,
      plant_duration_name,
      plant_nativity_type,
      plant_nativity_region_name,
      plant_growth_habit_name,
      cover_crop,
      active_growth_period.season_name AS active_growth_period,
      after_harvest_regrowth_rate.rate_name AS after_harvest_regrowth_rate,
      bloat_potential.extent_name AS bloat_potential,
      c_n_ratio.extent_name AS c_n_ratio,
      coppice_potential_ind,
      fall_conspicuous_ind,
      fire_resistant_ind,
      color_name,
      flower_conspicuous_ind,
      summer.foliage_porosity_name AS summer,
      winter.foliage_porosity_name AS winter,
      foliage_texture_name,
      fruit_seed_conspicuous_ind,
      growth_form_name,
      growth_rate.rate_name AS growth_rate,
      height_max_at_base_age,
      height_at_maturity,
      known_allelopath_ind,
      leaf_retention_ind,
      lifespan_name,
      low_growing_grass_ind,
      nitrogen_fixation_potential.extent_name AS nitrogen_fixation_potential,
      resprout_ability_ind,
      shape_orientation_name,
      toxicity_name,
      
      pd_max_range,
      ff_min_range,
      ph_min_range,
      ph_max_range,
      density_min_range,
      precip_min_range,
      precip_max_range,
      root_min_range,
      temp_min_range,
      commercial_availability_id,
      fruit_seed_abundance_id,
      propagated_by_bare_root_ind,
      propagated_by_bulb_ind,
  
      coarse_texture_soil_adaptable_ind,
      medium_texture_soil_adaptable_ind,
      fine_texture_soil_adaptable_ind,
      anaerobic_tolerance.extent_name AS anaerobic_tolerance,
      caco3_tolerance.extent_name AS caco3_tolerance,
      cold_stratification_required_ind,
      drought_tolerance.extent_name AS drought_tolerance,
      fire_tolerance.extent_name AS fire_tolerance,
      hedge_tolerance.extent_name AS hedge_tolerance,
      moisture_usage.extent_name AS moisture_usage,
      soil_ph_tolerance_min,
      soil_ph_tolerance_max,
      precipitation_tolerance_min, 
      precipitation_tolerance_max,
      salinity_tolerance.extent_name AS salinity_tolerance,
      shade_tolerance_name,
      temperature_tolerance_min,
  
      bloom_period.season_name AS bloom_period,
      fruit_seed_period_start.season_name AS fruit_seed_period_start,
      fruit_seed_period_end.season_name AS fruit_seed_period_end,
      fruit_seed_persistence_ind,
      seed_per_pound,
      seed_spread_rate.rate_name AS seed_spread_rate,
      seedling_vigor.extent_name AS seedling_vigor,
      vegetative_spread_rate.rate_name AS vegetative_spread_rate,
  
      berry_nut_seed_product_ind,
      fodder_product_ind,
      palatability_browse.extent_name AS palatability_browse,
      palatability_graze.extent_name AS palatability_graze,
      palatability_human_ind,
      protein_potential.extent_name AS protein_potential,

      frost_free_days_min,
      planting_density_min,
      root_depth_min

    FROM plants3.plant_master_tbl p
    
    LEFT JOIN plants3.plant_classifications_tbl USING (plant_master_id)
    LEFT JOIN (
      SELECT
        STRING_AGG(plant_duration_name, ', ' ORDER BY plant_duration_name) AS plant_duration_name,
        plant_master_id
      FROM plants3.plant_duration
      LEFT JOIN plants3.d_plant_duration USING (plant_duration_id)
      GROUP BY plant_master_id
    ) pd USING (plant_master_id)
    LEFT JOIN (
      SELECT
        STRING_AGG(plant_growth_habit_name, ', ' ORDER BY plant_growth_habit_name) AS plant_growth_habit_name,
        plant_master_id
      FROM plants3.plant_growth_habit 
      LEFT JOIN plants3.d_plant_growth_habit USING (plant_growth_habit_id)
      GROUP BY plant_master_id
    ) pgh USING (plant_master_id)
    LEFT JOIN plants3.plant_morphology_physiology m USING (plant_master_id)
    LEFT JOIN plants3.plant_growth_requirements g USING (plant_master_id, cultivar_name)
    LEFT JOIN plants3.plant_reproduction r USING (plant_master_id, cultivar_name)
    LEFT JOIN plants3.plant_suitability_use s USING (plant_master_id, cultivar_name)

    LEFT JOIN (
      SELECT
        plant_nativity_type,
        STRING_AGG(nativity.plant_nativity_region_name, ', ' ORDER BY nativity.plant_nativity_region_name) AS plant_nativity_region_name,
        plant_master_id
      FROM plants3.nativity
      LEFT JOIN plants3.d_plant_nativity_region USING (plant_nativity_region_id)
      GROUP BY plant_master_id, plant_nativity_type
    ) nat USING (plant_master_id)

    LEFT JOIN plants3.d_season active_growth_period ON m.active_growth_period_id=active_growth_period.season_id
    LEFT JOIN plants3.d_rate after_harvest_regrowth_rate ON m.after_harvest_regrowth_rate_id=after_harvest_regrowth_rate.rate_id
    LEFT JOIN plants3.d_extent bloat_potential ON m.bloat_potential_id=bloat_potential.extent_id
    LEFT JOIN plants3.d_extent c_n_ratio ON m.c_n_ratio_id=c_n_ratio.extent_id
    LEFT JOIN plants3.d_color c ON m.flower_color_id = c.color_id
    LEFT JOIN plants3.d_foliage_porosity summer ON m.summer_foliage_porosity_id=summer.foliage_porosity_id
    LEFT JOIN plants3.d_foliage_porosity winter ON m.winter_foliage_porosity_id=winter.foliage_porosity_id
    LEFT JOIN plants3.d_foliage_texture foliage_texture_name ON m.foliage_texture_id=foliage_texture_name.foliage_texture_id
    LEFT JOIN plants3.d_growth_form gf ON m.growth_form_id=gf.growth_form_id
    LEFT JOIN plants3.d_rate growth_rate ON m.growth_rate_id=growth_rate.rate_id
    LEFT JOIN plants3.d_lifespan USING (lifespan_id)
    LEFT JOIN plants3.d_extent nitrogen_fixation_potential ON m.nitrogen_fixation_potential_id=nitrogen_fixation_potential.extent_id
    LEFT JOIN plants3.d_shape_orientation q ON m.shape_orientation_id=q.shape_orientation_id
    LEFT JOIN plants3.d_toxicity USING (toxicity_id)
    LEFT JOIN plants3.d_extent anaerobic_tolerance ON g.anaerobic_tolerance_id=anaerobic_tolerance.extent_id
    LEFT JOIN plants3.d_extent caco3_tolerance ON g.caco3_tolerance_id=caco3_tolerance.extent_id
    LEFT JOIN plants3.d_extent drought_tolerance ON g.drought_tolerance_id=drought_tolerance.extent_id
    LEFT JOIN plants3.d_extent fire_tolerance ON g.fire_tolerance_id=fire_tolerance.extent_id
    LEFT JOIN plants3.d_extent hedge_tolerance ON g.hedge_tolerance_id=hedge_tolerance.extent_id
    LEFT JOIN plants3.d_extent moisture_usage ON g.moisture_usage_id = moisture_usage.extent_id
    LEFT JOIN plants3.d_extent salinity_tolerance ON g.salinity_tolerance_id = salinity_tolerance.extent_id
    LEFT JOIN plants3.d_shade_tolerance USING (shade_tolerance_id)
  
    LEFT JOIN plants3.d_season bloom_period ON r.bloom_period_id=bloom_period.season_id
    LEFT JOIN plants3.d_season fruit_seed_period_start ON r.fruit_seed_period_start_id=fruit_seed_period_start.season_id
    LEFT JOIN plants3.d_season fruit_seed_period_end ON r.fruit_seed_period_end_id=fruit_seed_period_end.season_id
    LEFT JOIN plants3.d_rate seed_spread_rate ON r.seed_spread_rate_id=seed_spread_rate.rate_id
    LEFT JOIN plants3.d_extent seedling_vigor ON r.seedling_vigor_id = seedling_vigor.extent_id
    LEFT JOIN plants3.d_rate vegetative_spread_rate ON r.vegetative_spread_rate_id=vegetative_spread_rate.rate_id
  
    LEFT JOIN plants3.d_extent palatability_browse ON s.palatability_browse_id = palatability_browse.extent_id
    LEFT JOIN plants3.d_extent palatability_graze ON s.palatability_graze_id = palatability_graze.extent_id
    LEFT JOIN plants3.d_extent protein_potential ON s.protein_potential_id = protein_potential.extent_id
    WHERE coalesce(
      active_growth_period::text, after_harvest_regrowth_rate::text, bloat_potential::text, c_n_ratio::text, coppice_potential_ind::text,
      fall_conspicuous_ind::text, fire_resistant_ind::text, color_name::text, flower_conspicuous_ind::text, summer::text, winter::text,
      fruit_seed_conspicuous_ind::text, growth_form_name::text, growth_rate::text, height_max_at_base_age::text, height_at_maturity::text,
      known_allelopath_ind::text, leaf_retention_ind::text, lifespan_name::text, low_growing_grass_ind::text, nitrogen_fixation_potential::text,
      resprout_ability_ind::text, shape_orientation_name::text, toxicity_name::text, coarse_texture_soil_adaptable_ind::text,
      medium_texture_soil_adaptable_ind::text, fine_texture_soil_adaptable_ind::text, anaerobic_tolerance::text, caco3_tolerance::text,
      cold_stratification_required_ind::text, drought_tolerance::text, fire_tolerance::text, hedge_tolerance::text, moisture_usage::text,
      soil_ph_tolerance_min::text, soil_ph_tolerance_max::text, precipitation_tolerance_min::text, precipitation_tolerance_max::text,
      salinity_tolerance::text, shade_tolerance_name::text, temperature_tolerance_min::text, bloom_period::text, fruit_seed_period_start::text,
      fruit_seed_period_end::text, fruit_seed_persistence_ind::text, seed_per_pound::text, seed_spread_rate::text, seedling_vigor::text,
      vegetative_spread_rate::text, berry_nut_seed_product_ind::text, fodder_product_ind::text,
      palatability_browse::text, palatability_graze::text,
      palatability_human_ind::text, protein_potential::text,
      plant_nativity_region_name
    ) > ''
  ) alias
  ORDER BY 1, 2, 3
) alias;

CREATE INDEX ON plants3.characteristics (plant_symbol);
CREATE INDEX ON plants3.characteristics (plant_master_id);