"""Utility file to seed ratings database from seed files"""


from sqlalchemy import func
from model import Drug, SpeciesGroup, SpeciesIndividual, Route, Condition, User, PersonalDose, Form, Formulation, Vet
import datetime
from model import connect_to_db, db
from server import app
from werkzeug.security import generate_password_hash

def load_users():
    """Seed user data from seed_users.psv

    File format:
    username|fname|lname|email|password|user_type
    """

   

    User.query.delete()

    with open("seed_data/seed_users.psv") as users:
        for row in users:
            username, fname, lname, email, password, user_role = row.strip().split("|")

            user = User(username=username,
                        fname=fname,
                        lname=lname,
                        email=email,
                        password=generate_password_hash(password),
                        user_role=user_role)

            db.session.add(user)

        db.session.commit()

def load_vets():
    """Seed data fromr vet_seed.psv into the vets table

    File format:
    user_id | grad_year | specialty
    """

    print("Vets")

    Vet.query.delete()

    with open("seed_data/vet_seed.psv") as vets:
        for row in vets:
            user_id, grad_year, specialty = row.strip().split("|")

            vet = Vet(
                user_id = user_id,
                grad_year = datetime.datetime.strptime(grad_year, "%d-%b-%Y"),
                specialty = specialty
            )

            db.session.add(vet)

        db.session.commit()


def load_drugs():
    """Seed data from drug_seed.psv into the drugs table of the database

    File format:
    Drug name

    """

    print("Drugs")

    Drug.query.delete()

    with open("seed_data/drug_seed.psv") as drugs:
        for row in drugs:
            name = row.strip()

            drug = Drug(generic_name=name)

            db.session.add(drug)

        db.session.commit()

def load_drug_forms():
    """Seed data from forms_seed.psv into forms table of database

    File format:
    Form name
    """

    print("Forms")

    Form.query.delete()

    with open("seed_data/forms_seed.psv") as forms:
        for row in forms:
            name = row.strip()

            form = Form(form_name=name)

            db.session.add(form)

        db.session.commit()

def load_drug_formulations():
    """Seed data from strengths_seed.psv

    File format:
    drug_id | form_id | strength | units
    """

    print("Formulations")

    Formulation.query.delete()

    with open("seed_data/formulations_seed.psv") as formulations:
        for row in formulations:
            drug_id, form_id, strength, units = row.strip().split("|")

            formulation = Formulation(
                drug_id=drug_id,
                drug_form_id=form_id,
                strength=strength,
                units=units
            )

            db.session.add(formulation)

        db.session.commit()

def load_species_groups():
    """Seed data from species_group_seed, each species group has an id associated with it.

    File format:

    group_id | group name

    """

    print("Species groups")

    SpeciesGroup.query.delete()

    with open("seed_data/species_group_seed.psv") as species:
        for row in species:
            species_group_id, species_group_name = row.strip().split("|")

            group = SpeciesGroup(species_group_id = species_group_id,
                         species_group = species_group_name)

            db.session.add(group)

        db.session.commit()


def load_individual_species():
    """Seed data from species_seed, each species is associated with a species group.

    File format:
    species | group_id
    """

    print ("individual species")

    SpeciesIndividual.query.delete()

    with open("seed_data/species_seed.psv") as species:
        for row in species:
            species_name, group_id = row.strip().split("|")

            species = SpeciesIndividual(species_name=species_name,
                                        species_group_id=group_id)

            db.session.add(species)

        db.session.commit()


def load_routes():
    """Seed data from routes_seed.psv into the seed table

    File format:
    route|route_acronym
    """

    print ("routes")

    Route.query.delete()

    with open("seed_data/routes_seed.psv") as routes:
        for row in routes:
            route, route_acronym = row.strip().split("|")

            # Checks if seed is empty, if so, inserts a Null cell into the db
            acronym = None if route_acronym == 'None' else route_acronym

            route = Route(route=route,
                          route_acronym=acronym)


            db.session.add(route)

        db.session.commit()

def load_conditions():
    """Seed data from routes_seed.psv into the seed table

    File format:
    disease
    """

    print ("conditions")

    Condition.query.delete()

    with open("seed_data/disease_seed.psv") as diseases:
        for row in diseases:
            condition = row.strip()

            condition = Condition(condition=condition)

            db.session.add(condition)

        db.session.commit()

def load_personal_doses():
    """Seed data from routes_seed.psv into the seed table

    File format:

    drugname | drug_id | dose_lower | dose_upper | recommended_dose | species_group_id | species_individual_id|
    .... condition_id | creator_id | duration_days | frequency_hrs
    """

    print ("Personal doses")

    PersonalDose.query.delete()

    with open("seed_data/personal_doses_seed.csv") as doses:
        for row in doses:

            dirty_info = row.strip().split(',')

            info = []

            for val in dirty_info:
                if val == 'None' or val == 'none':
                    info.append(None)
                else:
                    info.append(val)


            dose = PersonalDose(
                drug_id = info[1],
                dose_lower = info[2],
                dose_upper = info[3],
                recommended_dose = info[4],
                species_group_id = info[5],
                individual_species_id = info[6],
                condition_id = info[7],
                creator_id = info[8],
                duration_days = info[9],
                frequency_hrs = info[10]
            )

            db.session.add(dose)

        db.session.commit()


def set_val_species_group_id():
    """Set value for the next species_group_id after seeding database"""

    # Get the Max species_group_id in the database
    result = db.session.query(func.max(SpeciesGroup.species_group_id)).one()
    max_id = int(result[0])

    # Set the value for the next user_id to be max_id + 1
    query = "SELECT setval('species_groups_species_group_id_seq', :new_id)"
    db.session.execute(query, {'new_id': max_id + 1})
    db.session.commit()


if __name__ == "__main__":
    connect_to_db(app)

    # In case tables haven't been created, create them
    db.create_all()

    # Run functions in the file to seed all the different databases.
    load_drugs()
    load_drug_forms()
    load_drug_formulations()
    load_species_groups()
    load_individual_species()
    load_users()
    load_routes()
    load_conditions()
    load_personal_doses()
    load_vets()

    set_val_species_group_id()
3a58b2536fe451c2f87e26dd3a5f5350935ed430
<mask token>


def load_users():
    """Seed user data from seed_users.psv

    File format:
    username|fname|lname|email|password|user_type
    """
    User.query.delete()
    with open('seed_data/seed_users.psv') as users:
        for row in users:
            username, fname, lname, email, password, user_role = row.strip(
                ).split('|')
            user = User(username=username, fname=fname, lname=lname, email=
                email, password=generate_password_hash(password), user_role
                =user_role)
            db.session.add(user)
        db.session.commit()


def load_vets():
    """Seed data fromr vet_seed.psv into the vets table

    File format:
    user_id | grad_year | specialty
    """
    print('Vets')
    Vet.query.delete()
    with open('seed_data/vet_seed.psv') as vets:
        for row in vets:
            user_id, grad_year, specialty = row.strip().split('|')
            vet = Vet(user_id=user_id, grad_year=datetime.datetime.strptime
                (grad_year, '%d-%b-%Y'), specialty=specialty)
            db.session.add(vet)
        db.session.commit()


<mask token>


def load_drug_forms():
    """Seed data from forms_seed.psv into forms table of database

    File format:
    Form name
    """
    print('Forms')
    Form.query.delete()
    with open('seed_data/forms_seed.psv') as forms:
        for row in forms:
            name = row.strip()
            form = Form(form_name=name)
            db.session.add(form)
        db.session.commit()


<mask token>


def load_species_groups():
    """Seed data from species_group_seed, each species group has an id associated with it.

    File format:

    group_id | group name

    """
    print('Species groups')
    SpeciesGroup.query.delete()
    with open('seed_data/species_group_seed.psv') as species:
        for row in species:
            species_group_id, species_group_name = row.strip().split('|')
            group = SpeciesGroup(species_group_id=species_group_id,
                species_group=species_group_name)
            db.session.add(group)
        db.session.commit()


def load_individual_species():
    """Seed data from species_seed, each species is associated with a species group.

    File format:
    species | group_id
    """
    print('individual species')
    SpeciesIndividual.query.delete()
    with open('seed_data/species_seed.psv') as species:
        for row in species:
            species_name, group_id = row.strip().split('|')
            species = SpeciesIndividual(species_name=species_name,
                species_group_id=group_id)
            db.session.add(species)
        db.session.commit()


def load_routes():
    """Seed data from routes_seed.psv into the seed table

    File format:
    route|route_acronym
    """
    print('routes')
    Route.query.delete()
    with open('seed_data/routes_seed.psv') as routes:
        for row in routes:
            route, route_acronym = row.strip().split('|')
            acronym = None if route_acronym == 'None' else route_acronym
            route = Route(route=route, route_acronym=acronym)
            db.session.add(route)
        db.session.commit()


<mask token>

<mask token>


def load_users():
    """Seed user data from seed_users.psv

    File format:
    username|fname|lname|email|password|user_type
    """
    User.query.delete()
    with open('seed_data/seed_users.psv') as users:
        for row in users:
            username, fname, lname, email, password, user_role = row.strip(
                ).split('|')
            user = User(username=username, fname=fname, lname=lname, email=
                email, password=generate_password_hash(password), user_role
                =user_role)
            db.session.add(user)
        db.session.commit()


def load_vets():
    """Seed data fromr vet_seed.psv into the vets table

    File format:
    user_id | grad_year | specialty
    """
    print('Vets')
    Vet.query.delete()
    with open('seed_data/vet_seed.psv') as vets:
        for row in vets:
            user_id, grad_year, specialty = row.strip().split('|')
            vet = Vet(user_id=user_id, grad_year=datetime.datetime.strptime
                (grad_year, '%d-%b-%Y'), specialty=specialty)
            db.session.add(vet)
        db.session.commit()


def load_drugs():
    """Seed data from drug_seed.psv into the drugs table of the database

    File format:
    Drug name

    """
    print('Drugs')
    Drug.query.delete()
    with open('seed_data/drug_seed.psv') as drugs:
        for row in drugs:
            name = row.strip()
            drug = Drug(generic_name=name)
            db.session.add(drug)
        db.session.commit()


def load_drug_forms():
    """Seed data from forms_seed.psv into forms table of database

    File format:
    Form name
    """
    print('Forms')
    Form.query.delete()
    with open('seed_data/forms_seed.psv') as forms:
        for row in forms:
            name = row.strip()
            form = Form(form_name=name)
            db.session.add(form)
        db.session.commit()


<mask token>


def load_species_groups():
    """Seed data from species_group_seed, each species group has an id associated with it.

    File format:

    group_id | group name

    """
    print('Species groups')
    SpeciesGroup.query.delete()
    with open('seed_data/species_group_seed.psv') as species:
        for row in species:
            species_group_id, species_group_name = row.strip().split('|')
            group = SpeciesGroup(species_group_id=species_group_id,
                species_group=species_group_name)
            db.session.add(group)
        db.session.commit()


def load_individual_species():
    """Seed data from species_seed, each species is associated with a species group.

    File format:
    species | group_id
    """
    print('individual species')
    SpeciesIndividual.query.delete()
    with open('seed_data/species_seed.psv') as species:
        for row in species:
            species_name, group_id = row.strip().split('|')
            species = SpeciesIndividual(species_name=species_name,
                species_group_id=group_id)
            db.session.add(species)
        db.session.commit()


def load_routes():
    """Seed data from routes_seed.psv into the seed table

    File format:
    route|route_acronym
    """
    print('routes')
    Route.query.delete()
    with open('seed_data/routes_seed.psv') as routes:
        for row in routes:
            route, route_acronym = row.strip().split('|')
            acronym = None if route_acronym == 'None' else route_acronym
            route = Route(route=route, route_acronym=acronym)
            db.session.add(route)
        db.session.commit()


<mask token>


def load_personal_doses():
    """Seed data from routes_seed.psv into the seed table

    File format:

    drugname | drug_id | dose_lower | dose_upper | recommended_dose | species_group_id | species_individual_id|
    .... condition_id | creator_id | duration_days | frequency_hrs
    """
    print('Personal doses')
    PersonalDose.query.delete()
    with open('seed_data/personal_doses_seed.csv') as doses:
        for row in doses:
            dirty_info = row.strip().split(',')
            info = []
            for val in dirty_info:
                if val == 'None' or val == 'none':
                    info.append(None)
                else:
                    info.append(val)
            dose = PersonalDose(drug_id=info[1], dose_lower=info[2],
                dose_upper=info[3], recommended_dose=info[4],
                species_group_id=info[5], individual_species_id=info[6],
                condition_id=info[7], creator_id=info[8], duration_days=
                info[9], frequency_hrs=info[10])
            db.session.add(dose)
        db.session.commit()


<mask token>

<mask token>


def load_users():
    """Seed user data from seed_users.psv

    File format:
    username|fname|lname|email|password|user_type
    """
    User.query.delete()
    with open('seed_data/seed_users.psv') as users:
        for row in users:
            username, fname, lname, email, password, user_role = row.strip(
                ).split('|')
            user = User(username=username, fname=fname, lname=lname, email=
                email, password=generate_password_hash(password), user_role
                =user_role)
            db.session.add(user)
        db.session.commit()


def load_vets():
    """Seed data fromr vet_seed.psv into the vets table

    File format:
    user_id | grad_year | specialty
    """
    print('Vets')
    Vet.query.delete()
    with open('seed_data/vet_seed.psv') as vets:
        for row in vets:
            user_id, grad_year, specialty = row.strip().split('|')
            vet = Vet(user_id=user_id, grad_year=datetime.datetime.strptime
                (grad_year, '%d-%b-%Y'), specialty=specialty)
            db.session.add(vet)
        db.session.commit()


def load_drugs():
    """Seed data from drug_seed.psv into the drugs table of the database

    File format:
    Drug name

    """
    print('Drugs')
    Drug.query.delete()
    with open('seed_data/drug_seed.psv') as drugs:
        for row in drugs:
            name = row.strip()
            drug = Drug(generic_name=name)
            db.session.add(drug)
        db.session.commit()


def load_drug_forms():
    """Seed data from forms_seed.psv into forms table of database

    File format:
    Form name
    """
    print('Forms')
    Form.query.delete()
    with open('seed_data/forms_seed.psv') as forms:
        for row in forms:
            name = row.strip()
            form = Form(form_name=name)
            db.session.add(form)
        db.session.commit()


<mask token>


def load_species_groups():
    """Seed data from species_group_seed, each species group has an id associated with it.

    File format:

    group_id | group name

    """
    print('Species groups')
    SpeciesGroup.query.delete()
    with open('seed_data/species_group_seed.psv') as species:
        for row in species:
            species_group_id, species_group_name = row.strip().split('|')
            group = SpeciesGroup(species_group_id=species_group_id,
                species_group=species_group_name)
            db.session.add(group)
        db.session.commit()


def load_individual_species():
    """Seed data from species_seed, each species is associated with a species group.

    File format:
    species | group_id
    """
    print('individual species')
    SpeciesIndividual.query.delete()
    with open('seed_data/species_seed.psv') as species:
        for row in species:
            species_name, group_id = row.strip().split('|')
            species = SpeciesIndividual(species_name=species_name,
                species_group_id=group_id)
            db.session.add(species)
        db.session.commit()


def load_routes():
    """Seed data from routes_seed.psv into the seed table

    File format:
    route|route_acronym
    """
    print('routes')
    Route.query.delete()
    with open('seed_data/routes_seed.psv') as routes:
        for row in routes:
            route, route_acronym = row.strip().split('|')
            acronym = None if route_acronym == 'None' else route_acronym
            route = Route(route=route, route_acronym=acronym)
            db.session.add(route)
        db.session.commit()


def load_conditions():
    """Seed data from routes_seed.psv into the seed table

    File format:
    disease
    """
    print('conditions')
    Condition.query.delete()
    with open('seed_data/disease_seed.psv') as diseases:
        for row in diseases:
            condition = row.strip()
            condition = Condition(condition=condition)
            db.session.add(condition)
        db.session.commit()


def load_personal_doses():
    """Seed data from routes_seed.psv into the seed table

    File format:

    drugname | drug_id | dose_lower | dose_upper | recommended_dose | species_group_id | species_individual_id|
    .... condition_id | creator_id | duration_days | frequency_hrs
    """
    print('Personal doses')
    PersonalDose.query.delete()
    with open('seed_data/personal_doses_seed.csv') as doses:
        for row in doses:
            dirty_info = row.strip().split(',')
            info = []
            for val in dirty_info:
                if val == 'None' or val == 'none':
                    info.append(None)
                else:
                    info.append(val)
            dose = PersonalDose(drug_id=info[1], dose_lower=info[2],
                dose_upper=info[3], recommended_dose=info[4],
                species_group_id=info[5], individual_species_id=info[6],
                condition_id=info[7], creator_id=info[8], duration_days=
                info[9], frequency_hrs=info[10])
            db.session.add(dose)
        db.session.commit()


def set_val_species_group_id():
    """Set value for the next species_group_id after seeding database"""
    result = db.session.query(func.max(SpeciesGroup.species_group_id)).one()
    max_id = int(result[0])
    query = "SELECT setval('species_groups_species_group_id_seq', :new_id)"
    db.session.execute(query, {'new_id': max_id + 1})
    db.session.commit()


<mask token>

<mask token>


def load_users():
    """Seed user data from seed_users.psv

    File format:
    username|fname|lname|email|password|user_type
    """
    User.query.delete()
    with open('seed_data/seed_users.psv') as users:
        for row in users:
            username, fname, lname, email, password, user_role = row.strip(
                ).split('|')
            user = User(username=username, fname=fname, lname=lname, email=
                email, password=generate_password_hash(password), user_role
                =user_role)
            db.session.add(user)
        db.session.commit()


def load_vets():
    """Seed data fromr vet_seed.psv into the vets table

    File format:
    user_id | grad_year | specialty
    """
    print('Vets')
    Vet.query.delete()
    with open('seed_data/vet_seed.psv') as vets:
        for row in vets:
            user_id, grad_year, specialty = row.strip().split('|')
            vet = Vet(user_id=user_id, grad_year=datetime.datetime.strptime
                (grad_year, '%d-%b-%Y'), specialty=specialty)
            db.session.add(vet)
        db.session.commit()


def load_drugs():
    """Seed data from drug_seed.psv into the drugs table of the database

    File format:
    Drug name

    """
    print('Drugs')
    Drug.query.delete()
    with open('seed_data/drug_seed.psv') as drugs:
        for row in drugs:
            name = row.strip()
            drug = Drug(generic_name=name)
            db.session.add(drug)
        db.session.commit()


def load_drug_forms():
    """Seed data from forms_seed.psv into forms table of database

    File format:
    Form name
    """
    print('Forms')
    Form.query.delete()
    with open('seed_data/forms_seed.psv') as forms:
        for row in forms:
            name = row.strip()
            form = Form(form_name=name)
            db.session.add(form)
        db.session.commit()


def load_drug_formulations():
    """Seed data from strengths_seed.psv

    File format:
    drug_id | form_id | strength | units
    """
    print('Formulations')
    Formulation.query.delete()
    with open('seed_data/formulations_seed.psv') as formulations:
        for row in formulations:
            drug_id, form_id, strength, units = row.strip().split('|')
            formulation = Formulation(drug_id=drug_id, drug_form_id=form_id,
                strength=strength, units=units)
            db.session.add(formulation)
        db.session.commit()


def load_species_groups():
    """Seed data from species_group_seed, each species group has an id associated with it.

    File format:

    group_id | group name

    """
    print('Species groups')
    SpeciesGroup.query.delete()
    with open('seed_data/species_group_seed.psv') as species:
        for row in species:
            species_group_id, species_group_name = row.strip().split('|')
            group = SpeciesGroup(species_group_id=species_group_id,
                species_group=species_group_name)
            db.session.add(group)
        db.session.commit()


def load_individual_species():
    """Seed data from species_seed, each species is associated with a species group.

    File format:
    species | group_id
    """
    print('individual species')
    SpeciesIndividual.query.delete()
    with open('seed_data/species_seed.psv') as species:
        for row in species:
            species_name, group_id = row.strip().split('|')
            species = SpeciesIndividual(species_name=species_name,
                species_group_id=group_id)
            db.session.add(species)
        db.session.commit()


def load_routes():
    """Seed data from routes_seed.psv into the seed table

    File format:
    route|route_acronym
    """
    print('routes')
    Route.query.delete()
    with open('seed_data/routes_seed.psv') as routes:
        for row in routes:
            route, route_acronym = row.strip().split('|')
            acronym = None if route_acronym == 'None' else route_acronym
            route = Route(route=route, route_acronym=acronym)
            db.session.add(route)
        db.session.commit()


def load_conditions():
    """Seed data from routes_seed.psv into the seed table

    File format:
    disease
    """
    print('conditions')
    Condition.query.delete()
    with open('seed_data/disease_seed.psv') as diseases:
        for row in diseases:
            condition = row.strip()
            condition = Condition(condition=condition)
            db.session.add(condition)
        db.session.commit()


def load_personal_doses():
    """Seed data from routes_seed.psv into the seed table

    File format:

    drugname | drug_id | dose_lower | dose_upper | recommended_dose | species_group_id | species_individual_id|
    .... condition_id | creator_id | duration_days | frequency_hrs
    """
    print('Personal doses')
    PersonalDose.query.delete()
    with open('seed_data/personal_doses_seed.csv') as doses:
        for row in doses:
            dirty_info = row.strip().split(',')
            info = []
            for val in dirty_info:
                if val == 'None' or val == 'none':
                    info.append(None)
                else:
                    info.append(val)
            dose = PersonalDose(drug_id=info[1], dose_lower=info[2],
                dose_upper=info[3], recommended_dose=info[4],
                species_group_id=info[5], individual_species_id=info[6],
                condition_id=info[7], creator_id=info[8], duration_days=
                info[9], frequency_hrs=info[10])
            db.session.add(dose)
        db.session.commit()


def set_val_species_group_id():
    """Set value for the next species_group_id after seeding database"""
    result = db.session.query(func.max(SpeciesGroup.species_group_id)).one()
    max_id = int(result[0])
    query = "SELECT setval('species_groups_species_group_id_seq', :new_id)"
    db.session.execute(query, {'new_id': max_id + 1})
    db.session.commit()


if __name__ == '__main__':
    connect_to_db(app)
    db.create_all()
    load_drugs()
    load_drug_forms()
    load_drug_formulations()
    load_species_groups()
    load_individual_species()
    load_users()
    load_routes()
    load_conditions()
    load_personal_doses()
    load_vets()
    set_val_species_group_id()

"""Utility file to seed ratings database from seed files"""


from sqlalchemy import func
from model import Drug, SpeciesGroup, SpeciesIndividual, Route, Condition, User, PersonalDose, Form, Formulation, Vet
import datetime
from model import connect_to_db, db
from server import app
from werkzeug.security import generate_password_hash

def load_users():
    """Seed user data from seed_users.psv

    File format:
    username|fname|lname|email|password|user_type
    """

   

    User.query.delete()

    with open("seed_data/seed_users.psv") as users:
        for row in users:
            username, fname, lname, email, password, user_role = row.strip().split("|")

            user = User(username=username,
                        fname=fname,
                        lname=lname,
                        email=email,
                        password=generate_password_hash(password),
                        user_role=user_role)

            db.session.add(user)

        db.session.commit()

def load_vets():
    """Seed data fromr vet_seed.psv into the vets table

    File format:
    user_id | grad_year | specialty
    """

    print("Vets")

    Vet.query.delete()

    with open("seed_data/vet_seed.psv") as vets:
        for row in vets:
            user_id, grad_year, specialty = row.strip().split("|")

            vet = Vet(
                user_id = user_id,
                grad_year = datetime.datetime.strptime(grad_year, "%d-%b-%Y"),
                specialty = specialty
            )

            db.session.add(vet)

        db.session.commit()


def load_drugs():
    """Seed data from drug_seed.psv into the drugs table of the database

    File format:
    Drug name

    """

    print("Drugs")

    Drug.query.delete()

    with open("seed_data/drug_seed.psv") as drugs:
        for row in drugs:
            name = row.strip()

            drug = Drug(generic_name=name)

            db.session.add(drug)

        db.session.commit()

def load_drug_forms():
    """Seed data from forms_seed.psv into forms table of database

    File format:
    Form name
    """

    print("Forms")

    Form.query.delete()

    with open("seed_data/forms_seed.psv") as forms:
        for row in forms:
            name = row.strip()

            form = Form(form_name=name)

            db.session.add(form)

        db.session.commit()

def load_drug_formulations():
    """Seed data from strengths_seed.psv

    File format:
    drug_id | form_id | strength | units
    """

    print("Formulations")

    Formulation.query.delete()

    with open("seed_data/formulations_seed.psv") as formulations:
        for row in formulations:
            drug_id, form_id, strength, units = row.strip().split("|")

            formulation = Formulation(
                drug_id=drug_id,
                drug_form_id=form_id,
                strength=strength,
                units=units
            )

            db.session.add(formulation)

        db.session.commit()

def load_species_groups():
    """Seed data from species_group_seed, each species group has an id associated with it.

    File format:

    group_id | group name

    """

    print("Species groups")

    SpeciesGroup.query.delete()

    with open("seed_data/species_group_seed.psv") as species:
        for row in species:
            species_group_id, species_group_name = row.strip().split("|")

            group = SpeciesGroup(species_group_id = species_group_id,
                         species_group = species_group_name)

            db.session.add(group)

        db.session.commit()


def load_individual_species():
    """Seed data from species_seed, each species is associated with a species group.

    File format:
    species | group_id
    """

    print ("individual species")

    SpeciesIndividual.query.delete()

    with open("seed_data/species_seed.psv") as species:
        for row in species:
            species_name, group_id = row.strip().split("|")

            species = SpeciesIndividual(species_name=species_name,
                                        species_group_id=group_id)

            db.session.add(species)

        db.session.commit()


def load_routes():
    """Seed data from routes_seed.psv into the seed table

    File format:
    route|route_acronym
    """

    print ("routes")

    Route.query.delete()

    with open("seed_data/routes_seed.psv") as routes:
        for row in routes:
            route, route_acronym = row.strip().split("|")

            # Checks if seed is empty, if so, inserts a Null cell into the db
            acronym = None if route_acronym == 'None' else route_acronym

            route = Route(route=route,
                          route_acronym=acronym)


            db.session.add(route)

        db.session.commit()

def load_conditions():
    """Seed data from routes_seed.psv into the seed table

    File format:
    disease
    """

    print ("conditions")

    Condition.query.delete()

    with open("seed_data/disease_seed.psv") as diseases:
        for row in diseases:
            condition = row.strip()

            condition = Condition(condition=condition)

            db.session.add(condition)

        db.session.commit()

def load_personal_doses():
    """Seed data from routes_seed.psv into the seed table

    File format:

    drugname | drug_id | dose_lower | dose_upper | recommended_dose | species_group_id | species_individual_id|
    .... condition_id | creator_id | duration_days | frequency_hrs
    """

    print ("Personal doses")

    PersonalDose.query.delete()

    with open("seed_data/personal_doses_seed.csv") as doses:
        for row in doses:

            dirty_info = row.strip().split(',')

            info = []

            for val in dirty_info:
                if val == 'None' or val == 'none':
                    info.append(None)
                else:
                    info.append(val)


            dose = PersonalDose(
                drug_id = info[1],
                dose_lower = info[2],
                dose_upper = info[3],
                recommended_dose = info[4],
                species_group_id = info[5],
                individual_species_id = info[6],
                condition_id = info[7],
                creator_id = info[8],
                duration_days = info[9],
                frequency_hrs = info[10]
            )

            db.session.add(dose)

        db.session.commit()


def set_val_species_group_id():
    """Set value for the next species_group_id after seeding database"""

    # Get the Max species_group_id in the database
    result = db.session.query(func.max(SpeciesGroup.species_group_id)).one()
    max_id = int(result[0])

    # Set the value for the next user_id to be max_id + 1
    query = "SELECT setval('species_groups_species_group_id_seq', :new_id)"
    db.session.execute(query, {'new_id': max_id + 1})
    db.session.commit()


if __name__ == "__main__":
    connect_to_db(app)

    # In case tables haven't been created, create them
    db.create_all()

    # Run functions in the file to seed all the different databases.
    load_drugs()
    load_drug_forms()
    load_drug_formulations()
    load_species_groups()
    load_individual_species()
    load_users()
    load_routes()
    load_conditions()
    load_personal_doses()
    load_vets()

    set_val_species_group_id()