<gh_stars>1-10
import sqlite3
import os

from sqlalchemy import create_engine 
from sqlalchemy.orm import sessionmaker  

from setup.models import MPCommons, Address, Office
from setup import setup_archipelago



class Archipelago(object):

    # totally run into problems if more than one db
    _db_url = None 
    _engine = None 
    _session_factory = sessionmaker()
    
    def __init__(self, database=None):
        if not self._db_url:
            self._db_url = os.getenv('ARCHIPELAGO_DB', 'sqlite:///parl.db')

        if  database != None:
            # effectively a 'reset' & change db. not ideal. this all needs serious rework.
            self._db_url = database
            self._engine = None
        
        if not self._engine:
            self._engine = create_engine(self._db_url, echo=False)
            self._session_factory.configure(bind=self._engine)

        if  not self._engine.has_table(MPCommons.__tablename__) or \
            not self._engine.has_table(Address.__tablename__) or \
            not self._engine.has_table(Office.__tablename__): 
           self._engine, self._session_factory = setup_archipelago(self._db_url)


        self.session = self._session_factory()

    def get_constituencies(self):
        """Return a python list of constituencies in the archipelago database.""" 

        return [constit[0] for constit in self.session.query(MPCommons.Constituency).all()]

    def get_twitter_users(self):
        """Return a python list of constituencies in the archipelago database.""" 
        
        results = self.session.query(MPCommons, Address.Address).\
            join(MPCommons.Addresses).\
            filter(Address.AddressType=='twitter').\
            order_by(MPCommons.Name).all()

        return [
                   {   
                    "name": MP.Name,
                    "party":MP.Party,
                    "constituency":MP.Constituency,
                    "o_id":MP.OfficialId, 
                    "twitter_url":twitter_url, 
                    "handle":twitter_url[20:] 
                    } for MP, twitter_url in results 
                ]

    def get_all_mps(self):
        return self.session.query(MPCommons).order_by(MPCommons.Name).all()

    def get_all_tweeting_mps(self):
        return self.session.query(MPCommons).\
            join(MPCommons.Addresses).\
            filter(Address.AddressType=='twitter').\
            order_by(MPCommons.Name).all()


    def get_mps_by_official_id(self, o_id_list):
        return self.session.query(MPCommons).filter(MPCommons.OfficialId.in_(o_id_list)).order_by(MPCommons.Name).all()





