"""initial schema Revision ID: 0001 Revises: Create Date: 2026-04-15 """ import sqlalchemy as sa from alembic import op from sqlalchemy.dialects import postgresql revision = "0001" down_revision = None branch_labels = None depends_on = None def upgrade() -> None: # --- plants --- op.create_table( "plants", sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True), sa.Column("name", sa.String(255), nullable=False), sa.Column("species", sa.String(255), nullable=False), sa.Column( "variety_type", sa.Enum("scion", "rootstock", "both", name="varietytype"), nullable=False, ), sa.Column("drought_tolerance", sa.Integer, nullable=False), sa.Column("salinity_tolerance", sa.Integer, nullable=False), sa.Column("heat_tolerance", sa.Integer, nullable=False), sa.Column("cold_tolerance", sa.Integer, nullable=False), sa.Column("disease_resistance", sa.JSON, nullable=True), sa.Column("optimal_ph_min", sa.Float, nullable=False), sa.Column("optimal_ph_max", sa.Float, nullable=False), sa.Column("nutrient_uptake_efficiency", sa.JSON, nullable=True), sa.Column( "root_depth_category", sa.Enum("shallow", "medium", "deep", name="rootdepthcategory"), nullable=False, ), sa.Column("growth_cycle_days", sa.Integer, nullable=False), sa.Column("yield_potential_kg_per_plant", sa.Float, nullable=False), sa.Column("description", sa.Text, nullable=True), sa.Column("image_url", sa.String(500), nullable=True), sa.Column("created_at", sa.DateTime(timezone=True), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False), ) # --- soil_profiles --- op.create_table( "soil_profiles", sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True), sa.Column("name", sa.String(255), nullable=False), sa.Column( "soil_type", sa.Enum("sandy", "loam", "clay", "silt", "peat", "chalky", name="soiltype"), nullable=False, ), sa.Column("ph_level", sa.Float, nullable=False), sa.Column("organic_matter_percent", sa.Float, nullable=False), sa.Column( "drainage_rate", sa.Enum("poor", "moderate", "good", "excessive", name="drainagerate"), nullable=False, ), sa.Column( "water_retention", sa.Enum("low", "medium", "high", name="waterretention"), nullable=False, ), sa.Column("native_nitrogen", sa.Float, nullable=False), sa.Column("native_phosphorus", sa.Float, nullable=False), sa.Column("native_potassium", sa.Float, nullable=False), sa.Column("salinity_level", sa.Float, nullable=False), sa.Column("temperature_range_min", sa.Float, nullable=False), sa.Column("temperature_range_max", sa.Float, nullable=False), sa.Column("description", sa.Text, nullable=True), sa.Column("climate_zone", sa.String(100), nullable=False), sa.Column("created_at", sa.DateTime(timezone=True), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False), ) # --- graft_combinations --- op.create_table( "graft_combinations", sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True), sa.Column("rootstock_id", postgresql.UUID(as_uuid=True), sa.ForeignKey("plants.id"), nullable=False), sa.Column("scion_id", postgresql.UUID(as_uuid=True), sa.ForeignKey("plants.id"), nullable=False), sa.Column("compatibility_score", sa.Float, nullable=False), sa.Column("vigor_boost", sa.Float, nullable=False, server_default="1.0"), sa.Column("yield_modifier", sa.Float, nullable=False, server_default="1.0"), sa.Column("stress_tolerance_override", sa.JSON, nullable=True), sa.Column("notes", sa.Text, nullable=True), sa.Column("source_reference", sa.String(500), nullable=True), sa.Column("created_at", sa.DateTime(timezone=True), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False), ) # --- scenarios --- op.create_table( "scenarios", sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True), sa.Column("name", sa.String(255), nullable=False), sa.Column("graft_combination_id", postgresql.UUID(as_uuid=True), sa.ForeignKey("graft_combinations.id"), nullable=True), sa.Column("plant_id", postgresql.UUID(as_uuid=True), sa.ForeignKey("plants.id"), nullable=True), sa.Column("soil_profile_id", postgresql.UUID(as_uuid=True), sa.ForeignKey("soil_profiles.id"), nullable=False), sa.Column("applied_nitrogen", sa.Float, nullable=False), sa.Column("applied_phosphorus", sa.Float, nullable=False), sa.Column("applied_potassium", sa.Float, nullable=False), sa.Column( "irrigation_method", sa.Enum("drip", "sprinkler", "flood", "rainfed", name="irrigationmethod"), nullable=False, ), sa.Column("irrigation_frequency_days", sa.Integer, nullable=False), sa.Column( "season", sa.Enum("spring", "summer", "autumn", "winter", name="season"), nullable=False, ), sa.Column("duration_days", sa.Integer, nullable=False), sa.Column("created_at", sa.DateTime(timezone=True), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False), ) # --- scenario_results --- op.create_table( "scenario_results", sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True), sa.Column("scenario_id", postgresql.UUID(as_uuid=True), sa.ForeignKey("scenarios.id"), nullable=False), sa.Column("predicted_yield", sa.Float, nullable=True), sa.Column("survival_probability", sa.Float, nullable=True), sa.Column("risk_factors", sa.JSON, nullable=True), sa.Column("recommended_adjustments", sa.JSON, nullable=True), sa.Column("growth_timeline", sa.JSON, nullable=True), sa.Column("confidence_score", sa.Float, nullable=True), sa.Column("ai_analysis", sa.Text, nullable=True), sa.Column("created_at", sa.DateTime(timezone=True), nullable=False), ) def downgrade() -> None: op.drop_table("scenario_results") op.drop_table("scenarios") op.drop_table("graft_combinations") op.drop_table("soil_profiles") op.drop_table("plants") op.execute("DROP TYPE IF EXISTS season") op.execute("DROP TYPE IF EXISTS irrigationmethod") op.execute("DROP TYPE IF EXISTS waterretention") op.execute("DROP TYPE IF EXISTS drainagerate") op.execute("DROP TYPE IF EXISTS soiltype") op.execute("DROP TYPE IF EXISTS rootdepthcategory") op.execute("DROP TYPE IF EXISTS varietytype")