#!/usr/bin/python3
""" This handles the storage for SQL """
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.orm import Session, sessionmaker, scoped_session
from sqlalchemy import Column, String, ForeignKey, DateTime
from os import environ
from os import getenv
from models.state import State
from models.city import City
from models.base_model import BaseModel
from models.user import User
from models.place import Place
from models.amenity import Amenity
from models.review import Review

classes = {'State': State, 'BaseModel': BaseModel, 'City': City}


class DBStorage:
    """ Database Storage

        cl_attr:
            __engine
            __session
    """
    __engine = None
    __session = None

    def __init__(self):
        """ initiates engine according
            to environ varaibles
        """
        uname = environ['HBNB_MYSQL_USER']
        pword = environ['HBNB_MYSQL_PWD']
        dbname = environ['HBNB_MYSQL_DB']
        host = environ['HBNB_MYSQL_HOST']

        self.__engine = create_engine('mysql+mysqldb://{}:{}@{}/{}'.format(
            uname, pword, host, dbname), pool_pre_ping=True)

        if getenv('HBNB_ENV') == 'test':
            # drop all tables
            Base.metadata.drop_all(bind=self.__engine)

    def all(self, cls=None):
        """ query on SQL database
            calling all objects
        """
        # Select * from cls passed into all
        # if cls=None query all objects
        # return dictionary like FileStorage
        obj = {}
        if cls is not None:
            for u in self.__session.query(classes[cls]).all():
                obj[classes[cls].__name__ + "." + classes[cls].id] = u
            return obj
        else:
            for a in classes:
                find = self.__session.query(classes[a]).all()
                for u in find:
                    obj[u.__class__.__name__ + "." + u.id] = u
            return obj

    def new(self, obj):
        """ add object to current session
        """
        self.__session.add(obj)

    def save(self):
        """ SQL save and commmit session
        """
        self.__session.commit()

    def delete(self, obj=None):
        """ Deletes an object from
            the current session
        """
        # only delete if not None
        if obj:
            self.__session().drop(obj)

    def reload(self):
        """ create all tables in the db
        """
        from models.base_model import Base
        Base.metadata.create_all(self.__engine)
        sesh = sessionmaker(bind=self.__engine, expire_on_commit=False)
        self.__session = scoped_session(sesh)

    def close(self):
        """calls close method"""
        self.__session.close()
