import MySQLdb
import os
import sys
from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import scoped_session, 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
from multiprocessing.dummy import Pool as ThreadPool

# 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)
    metadata = MetaData(bind=engine)
    Session = scoped_session(sessionmaker(engine, autoflush=True))

    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)
        customer_id = Column(Integer)
        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, customer_id, product_id, wp_edd_sub_id, server_ip, server_name, email, root_password, ovpn_file, status, bandwidth_limit_this_month):
            self.customer_id = customer_id
            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.bandwidth_limit_this_month = 0
            self.current_location = "New Jersey"
            self.rebuild_schedule = "None"
            self.rebuild_schedule_location = "New Jersey"
            self.rebuild_now_status = 0
            self.rebuild_now_location = "New Jersey"
            self.ovpn_file = ovpn_file
            self.status = status

        def create(self):
            print "Created!"

        def destroy(self):
            print "Destroyed!"

        def rebuild_now(self):
            print "Rebuilt!"

    # 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()

    # Make the Pool of workers
    pool = ThreadPool(4)

    def thread_servers_to_create(self):
        # Setup SQLAlchemy
        session = Session()
        new_server = Server(self.customer_id, self.product_id, self.id, '0.0.0.0', '', '', '', '', 'Installing', 0)
        # Save to database
        session.add(new_server)
        session.commit()
        # Close session
        session.close()
        new_server.create()

    def thread_servers_to_destroy(self):
        self.destroy()

    def thread_servers_to_rebuild_now(self):
        self.rebuild_now()

    # Start a thread for each servers_to_create
    results = pool.map(thread_servers_to_create, servers_to_create)

    # Start a thread for each servers_to_destroy
    results = pool.map(thread_servers_to_destroy, servers_to_destroy)

    # Start a thread for each servers_to_rebuild_now
    results = pool.map(thread_servers_to_rebuild_now, servers_to_rebuild_now)

    # close the pool and wait for the work to finish
    pool.close()
    pool.join()

finally:
    os.unlink(pidfile)