import MySQLdb
import os
import sys
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, String, Integer, Date, Table, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from urllib import quote_plus as urlquote

# Set PID file, this prevents the script from running if already running
pid = str(os.getpid())
pidfile = "/tmp/server-py.pid"
if os.path.isfile(pidfile):
    print "%s already exists, exiting" % pidfile
    sys.exit()
file(pidfile, 'w').write(pid)

try:
    # 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

    # Setup SQLAlchemy stuff
    Base.metadata.create_all(engine)
    session = Session()

    # Create lists which will be populated by SQL queries
    servers_to_create = []
    servers_to_destroy = []

    # Get all active subscriptions, joined to servers
    active_subscriptions = session.query(Subscription, Server).outerjoin(Server, Subscription.id == Server.wp_edd_sub_id).filter(Subscription.status=="Active").all()

    # Find active subscriptions which do not have existing servers (create these)
    for subscription in active_subscriptions:
        # If subscription exists for this server, skip, else append to the server create list
        if subscription[1]:
            pass
        else:
            servers_to_create.append(subscription[0])

    # Get all existing servers, joined to subscriptions
    active_servers = session.query(Server, Subscription).outerjoin(Subscription, Subscription.id == Server.wp_edd_sub_id).all()

    # Find existing servers which do not have active subscriptions (destroy these)
    for server in active_servers:
        # If subscription exists for this server, skip, else append to the server destroy list
        if server[1]:
            pass
        else:
            servers_to_destroy.append(server[0])

    # Get all servers which need to be rebuilt now (rebuild these)
    servers_to_rebuild_now = session.query(Server).filter(Server.rebuild_now_status==1).all()

    # Create servers
    for server in servers_to_create:
        pass

    # Destroy servers
    for server in servers_to_destroy:
        pass

    # Rebuild servers
    for server in servers_to_rebuild_now:
        pass

finally:
    os.unlink(pidfile)