def main():
    print "So it begins..."
    target_db_uri = Environment.instance().config.SQLALCHEMY_DATABASE_URI
    target_connection = psycopg2.connect(target_db_uri)
    target_cursor = target_connection.cursor()

    db_uri = Environment.instance().config.SQLALCHEMY_BINDS['stats']
    connection = psycopg2.connect(db_uri)
    cursor = connection.cursor()

    try:
        for table_name, columns in TABLES:
            print "Copying ", table_name

            # first, we need to lock the source table (ideally)
            # the problem with this is that we would need to modify permissions for production to allow
            # the lock for the user configured in SQLALCHEMY_DATABASE_URI
            # cursor.execute("LOCK TABLE %s" % table_name)

            # we need to assert the table is empty
            print "Counting ", table_name
            target_cursor.execute("select count(*) from %s" % table_name)
            count, = target_cursor.fetchone()
            assert count == 0, "Table %s "

            col_string = ','.join(columns)
            str_string = ','.join(["%s"] * len(columns))
            target_query = StringIO()
            target_query.write('insert into %s(%s) values ' % (table_name, col_string))
            print "Reading ", table_name
            cursor.execute('select %s from %s' % (col_string, table_name))
            for rec in cursor:
                target_query.write("(%s)," % target_cursor.mogrify(str_string, tuple(rec)))
            print "Writing ", table_name
            target_cursor.execute(target_query.getvalue()[:-1])

            # now we need to reset the sequence associated with the id for this table
            target_cursor.execute("select max(id) + 1 from %s" % table_name)
            nextone, = target_cursor.fetchone()
            print "Updating sequence for ", table_name
            target_cursor.execute("SELECT setval('%s_id_seq', %s, false)" % (table_name, nextone))
            print "Done ", table_name

    except Exception as e:
        print "Error ", e
        target_connection.rollback()
        connection.rollback()
    else:
        print "Good, well done, excellent."
        target_connection.commit()
        connection.commit()
    finally:
        connection.close()
        target_connection.close()

def get_campaigns(account_id=None, past=True, in_flight=True, scheduled=True, utctoday=None):
    from splice.environment import Environment

    env = Environment.instance()

    query = env.db.session.query(Campaign)

    if account_id is not None:
        query = query.filter(Campaign.account_id == account_id)

    if utctoday is None:
        utctoday = datetime.utcnow().date()

    rows = query.order_by(Campaign.id.desc()).all()

    campaigns = []
    for row in rows:
        ret = row_to_dict(row)
        countries = []
        for country in row.countries:
            countries.append(country.country_code)
        ret['countries'] = countries

        # filter based on start and end dates unless an account ID is specified
        if ((past and row.end_date.date() <= utctoday) or
                (in_flight and row.end_date.date() >= utctoday >= row.start_date.date()) or
                (scheduled and row.start_date.date() >= utctoday)):
            campaigns.append(ret)

    return campaigns

def get_scheduled_distributions(minutes, dt_query=None):
    """
    Returns distributions scheduled from a point in time, and a leniency period
    within which a tasks could've been scheduled closed to that point.
    As a regular task, it is intended to run at least once hourly.
    :minutes: amount of time in the past from the query time which is still viable
    :dt_query: optionally set the date time to find schedules for
    """
    from splice.environment import Environment

    env = Environment.instance()

    if not minutes or not (0 < minutes < 60):
        raise ValueError("minutes needs to be a number between 1..59 inclusive")

    if dt_query is None:
        dt_query = datetime.utcnow()

    # getting around PEP8 E712 warning. This is necessary for SQLAlchemy
    false_value = False

    min_query_dt = dt_query - timedelta(minutes=minutes)

    stmt = (
        env.db.session
        .query(Distribution)
        .filter(Distribution.deployed == false_value)
        .filter(Distribution.scheduled_start_date.between(min_query_dt, dt_query))
    )

    dists = stmt.all()

    return dists

def insert_distribution(url, channel_id, deployed, scheduled_dt, *args, **kwargs):
    from splice.environment import Environment

    # ensure that on insert, a distribution is either deployed or scheduled, not both
    if scheduled_dt is not None:
        deployed = False

    env = Environment.instance()
    conn = env.db.engine.connect()
    trans = conn.begin()
    try:
        conn.execute(
            text(
                "INSERT INTO distributions ("
                " url, channel_id, deployed, scheduled_start_date, created_at"
                ") "
                "VALUES ("
                " :url, :channel_id, :deployed, :scheduled_start_date, :created_at"
                ")"
            ),
            url=url,
            channel_id=channel_id,
            deployed=deployed,
            scheduled_start_date=scheduled_dt,
            created_at=datetime.utcnow()
        )
        trans.commit()
    except:
        trans.rollback()
        raise

def tile_exists(target_url, bg_color, title, type, image_uri, enhanced_image_uri, locale, conn=None, *args, **kwargs):
    """
    Return the id of a tile having the data provided
    """
    from splice.environment import Environment
    env = Environment.instance()

    if conn is not None:
        sm = sessionmaker(bind=conn)
        session = sm()
    else:
        session = env.db.session

    # we add order_by in the query although it shouldn't be necessary
    # this is because of a previous bug where duplicate tiles could be created
    results = (
        session
        .query(Tile.id)
        .filter(Tile.target_url == target_url)
        .filter(Tile.bg_color == bg_color)
        .filter(Tile.title == title)
        .filter(Tile.image_uri == image_uri)
        .filter(Tile.enhanced_image_uri == enhanced_image_uri)
        .filter(Tile.locale == locale)
        .order_by(asc(Tile.id))
        .first()
    )

    if results:
        return results[0]

    return results

def get_upcoming_distributions(limit=100, leniency_minutes=15, include_past=False):
    """
    Obtain distributions, partitioned by channels with up to ``limit`` results
    for each channel
    :leniency_minutes: have a leniency in minutes up to the present when looking for distributions
    :include_past: always return all past distributions
    """
    from splice.environment import Environment

    env = Environment.instance()

    # getting around PEP8 E712 warning. This is necessary for SQLAlchemy
    false_value = False

    dist_cte = (
        env.db.session
        .query(
            Distribution.id,
            Distribution.channel_id,
            Distribution.url,
            Distribution.created_at,
            Distribution.scheduled_start_date,
            func.row_number().over(
                partition_by=Distribution.channel_id,
                order_by=Distribution.scheduled_start_date.asc())
            .label('row_num')
        )
        .filter(Distribution.deployed == false_value))

    if not include_past:
        min_dt = datetime.utcnow() - timedelta(minutes=leniency_minutes)
        dist_cte = (
            dist_cte
            .filter(Distribution.scheduled_start_date >= min_dt))

    dist_cte = dist_cte.cte()

    stmt = (
        env.db.session
        .query(
            dist_cte.c.id,
            dist_cte.c.channel_id,
            dist_cte.c.url,
            dist_cte.c.created_at,
            dist_cte.c.scheduled_start_date)
        .filter(dist_cte.c.row_num <= limit)
        .order_by(dist_cte.c.scheduled_start_date.asc())
    )

    rows = stmt.all()

    channels = {}

    for row in rows:
        c_dists = channels.setdefault(row.channel_id, [])
        c_dists.append({'id': row.id, 'url': row.url, 'created_at': row.created_at, 'scheduled_at': row.scheduled_start_date})

    return channels

def _update_image(bucket, image_url, tile_id, column='image_uri'):
    env = Environment.instance()
    if image_url and not image_url.startswith('http'):
        imgs = list(bucket.list(prefix="images/%s" % image_url))
        if len(imgs):
            uri = os.path.join('https://%s.s3.amazonaws.com' % env.config.S3['bucket'], imgs[0])
            print "updating %s for tile=%s" % (column, tile_id)
            return "update tiles set %s = '%s' where id = %s" % (column, uri, tile_id)
    return None

    def test_get_all_categories(self):
        """ Test for getting all categories"""
        url = url_for('api.init.init', target="categories")
        response = self.client.get(url)
        assert_equal(response.status_code, 200)
        categories = json.loads(response.data)['results']

        categories_fixture = Environment.instance()._load_categories()
        assert_equal(categories, categories_fixture)

def get_account(id):
    from splice.environment import Environment

    env = Environment.instance()

    row = (
        env.db.session
        .query(Account).get(id)
    )
    return row_to_dict(row) if row else None

    def test_get_all_locale(self):
        """ Test for getting all locales"""
        url = url_for('api.init.init', target="locales")
        response = self.client.get(url)
        assert_equal(response.status_code, 200)
        locales = json.loads(response.data)['results']

        locales_fixture = Environment.instance()._load_locales()[:-1]
        locales_fixture.sort()
        assert_equal(locales, locales_fixture)

    def test_get_all_countries(self):
        """ Test for getting all countries"""
        url = url_for('api.init.init', target="countries")
        response = self.client.get(url)
        assert_equal(response.status_code, 200)
        countries = json.loads(response.data)['results']

        countries_fixture = Environment.instance()._load_countries()[:-1]
        items = [{"country_code": code, "country_name": name} for code, name in countries_fixture]
        assert_equal(countries, items)

def get_content(name):
    from splice.environment import Environment

    env = Environment.instance()

    row = env.db.session.query(Content).filter(Content.name == name).first()
    c = row_to_dict(row) if row else None
    if c is not None:
        versions = []
        for version in row.versions:
            versions.append(row_to_dict(version))
        c['versions'] = versions

    return c

def get_accounts():
    from splice.environment import Environment

    env = Environment.instance()

    rows = (
        env.db.session
        .query(Account)
        .order_by(Account.id.desc())
        .all()
    )
    output = [row_to_dict(d) for d in rows]

    return output

    def test_single_creative_upload_endpoint(self):
        """Test the API endpoint for the single creative upload"""
        from splice.environment import Environment

        env = Environment.instance()
        url = url_for('api.tile.handler_creative_upload')
        with zipfile.ZipFile(self.zip_file, "r") as zf:
            f = zf.getinfo("samples/firefox_mdn_a.png")
            data = {'creative': (StringIO.StringIO(zf.read(f)), 'creative.png')}
            response = self.client.post(url, data=data)
            assert_equal(response.status_code, 200)
            creative_url = json.loads(response.data)['result']
            bucket = env.s3.get_bucket(env.config.S3["bucket"])
            s3_key = os.path.basename(creative_url)
            key = bucket.get_key(s3_key)
            self.assertIsNotNone(key)

def get_adgroups():
    from splice.environment import Environment

    env = Environment.instance()

    rows = (
        env.db.session
        .query(Adgroup.id, Adgroup.locale)
        .order_by(Adgroup.id)
        .all()
    )

    # ensure items are a list of dicts
    # KeyedTuples may serialize differently on other systems
    output = [d._asdict() for d in rows]

    return output

def get_tiles():
    from splice.environment import Environment

    env = Environment.instance()

    rows = (
        env.db.session
        .query(Tile.id, Tile.adgroup_id, Tile.title, Tile.type, Tile.bg_color, Tile.target_url)
        .order_by(Tile.id.asc())
        .all()
    )

    # ensure items are a list of dicts
    # KeyedTuples may serialize differently on other systems
    output = [d._asdict() for d in rows]

    return output

def get_campaign(campaign_id):
    from splice.environment import Environment

    env = Environment.instance()

    row = (
        env.db.session
        .query(Campaign).get(campaign_id)
    )
    if row:
        ret = row_to_dict(row)
        countries = []
        for country in row.countries:
            countries.append(country.country_code)
        ret['countries'] = countries
        return ret
    else:
        return None

def get_distributions(limit=100, *args, **kwargs):
    from splice.environment import Environment

    env = Environment.instance()

    rows = (
        env.db.session
        .query(Distribution.url, Distribution.created_at)
        .order_by(Distribution.id.desc())
        .limit(limit)
        .all()
    )

    # ensure items are lists of lists rather than KeyedTuples
    # KeyedTuples may serialize differently on other systems
    output = [list(d) for d in rows]

    return output

def get_channels(limit=100):
    from splice.environment import Environment

    env = Environment.instance()

    rows = (
        env.db.session
        .query(Channel.id, Channel.name, Channel.created_at)
        .order_by(Channel.id.asc())
        .limit(limit)
        .all()
    )

    # ensure items are a list of dicts
    # KeyedTuples may serialize differently on other systems
    output = [d._asdict() for d in rows]

    return output

def get_adgroup(id):
    from splice.environment import Environment

    env = Environment.instance()

    row = (
        env.db.session
        .query(Adgroup).get(id)
    )
    if row is None:
        return None

    new = row_to_dict(row)
    categories = []
    for category in row.categories:
        categories.append(category.category)
    new['categories'] = categories

    return new