# Setup SQLAlchemy
engine = create_engine('mysql://user:%s@localhost:3306/ghostifi' % urlquote('password@!'), echo=False)
Session = sessionmaker(bind=engine)
Base = declarative_base()

# DB classes
class Subscription(Base):
    __tablename__ = 'wp_edd_subscriptions'

    id = Column(Integer, primary_key=True)
    customer_id = Column(Integer)
    period = Column(String)
    initial_amount = Column(String)
    recurring_amount = Column(String)
    bill_times = Column(Integer)
    transaction_id = Column(String)
    parent_payment_id = Column(Integer)
    product_id = Column(Integer)
    created = Column(Date)
    expiration = Column(Date)
    trial_period = Column(String)
    status = Column(String)
    profile_id = Column(String)
    notes = Column(String)
    server = relationship("Server", uselist=False, backref="Subscription")

class Server(Base):
    __tablename__ = 'servers'

    id = Column(Integer, primary_key=True)
    product_id = Column(Integer)
    wp_edd_sub_id = Column(Integer, ForeignKey(Subscription.id))
    server_ip = Column(String)
    server_name = Column(String)
    email = Column(String)
    root_password = Column(String)
    bandwidth_this_month = Column(Integer)
    bandwidth_limit_this_month = Column(Integer)
    current_location = Column(String)
    rebuild_schedule = Column(String)
    rebuild_schedule_location = Column(String)
    rebuild_now_status = Column(Integer)
    rebuild_now_location = Column(String)
    ovpn_file = Column(String)
    status = Column(String)

    def __init__(self, product_id, wp_edd_sub_id, server_ip, server_name, email, root_password, ovpn_file, status, bandwidth_limit_this_month):
        self.product_id = product_id
        self.wp_edd_sub_id = wp_edd_sub_id
        self.server_ip = server_ip
        self.server_name = server_name
        self.email = email
        self.root_password = root_password
        self.bandwidth_this_month = 0
        self.current_location = "New Jersey"
        self.rebuild_schedule = "None"
        self.rebuild_now_status = 0
        self.rebuild_now_location = "New Jersey"
        self.ovpn_file = ovpn_file
        self.status = status

Base.metadata.create_all(engine)
session = Session()

# Get all servers which have already been built
servers = session.query(Server).all()
for server in servers:
    print server.server_name

# Get all active subscriptions
subscriptions = session.query(Subscription).filter(Subscription.status=="Active").all()
for subscription in subscriptions:
    print subscription.status