<commit_before>from datetime import datetime
import sqlalchemy
from sqlalchemy.types import *
from sqlalchemy.ext.declarative import declarative_base
from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy.sql.functions import current_date
from sqlalchemy.dialects.postgresql import ARRAY
from splice.environment import Environment

db = Environment.instance().db

class Country(db.Model):
    __tablename__ = "countries"

    id = db.Column(db.Integer(), autoincrement=True, primary_key=True, info={"identity": [0,1]})
    code = db.Column(db.String(5), nullable=False, index=True)
    name = db.Column(db.String(100), nullable=False, unique=True)

class Locale(db.Model):
    __tablename__ = "locales"
    id = db.Column(db.Integer(), autoincrement=True, primary_key=True, info={"identity": [0,1]})
    name = db.Column(db.String(14), unique=True)

class Company(db.Model):
    __tablename__ = "companies"

    id = db.Column(db.Integer(), autoincrement=True, primary_key=True, info={"identity": [0,1]})
    name = db.Column(db.String(255), unique=True)
    created_at = db.Column(db.DateTime(), nullable=False, default=datetime.utcnow)

    campaigns = db.relationship("Campaign")

class CampaignsLocales(db.Model):
    __tablename__ = "campaigns_locales"

    locale_id = db.Column(db.Integer(), db.ForeignKey("locales.id"), primary_key=True)
    campaign_id = db.Column(db.Integer(), db.ForeignKey("campaigns.id"), primary_key=True)

class CampaignsCountries(db.Model):
    __tablename__ = "campaigns_countries"

    country_id = db.Column(db.Integer(), db.ForeignKey("countries.id"), primary_key=True)
    campaign_id = db.Column(db.Integer(), db.ForeignKey("campaigns.id"), primary_key=True)

class Campaign(db.Model):
    __tablename__ = "campaigns"

    id = db.Column(db.Integer(), autoincrement=True, primary_key=True, info={"identity": [0,1]})
    company_id = db.Column(db.Integer(), db.ForeignKey("companies.id"))

    flight_date = db.Column(db.DateTime(), nullable=False, index=True)
    end_date = db.Column(db.DateTime(), nullable=True, index=True)
    impression_limit = db.Column(db.Integer(), nullable=False, default=-1)

    countries = db.relationship("Country", secondary="campaigns_countries",
                                backref=db.backref("campaigns", lazy="dynamic"))

    locales = db.relationship("Locale", secondary="campaigns_locales",
                                backref=db.backref("campaigns", lazy="dynamic"))

    created_at = db.Column(db.DateTime(), nullable=False, default=datetime.utcnow)

class TileList(db.Model):
    __tablename__ = "tiles_lists"

    id = db.Column(db.Integer(), autoincrement=True, primary_key=True, info={"identity": [0,1]})

    tile_id = db.Column(db.Integer(), db.ForeignKey("tiles.id"))
    list_id = db.Column(db.Integer(), db.ForeignKey("lists.id"))
    order_index = db.Column(db.Integer(), nullable=False, index=True)

class List(db.Model):
    __tablename__ = "lists"

    id = db.Column(db.Integer(), autoincrement=True, primary_key=True, info={"identity": [0,1]})
    name = db.Column(db.String(255), nullable=False)

    created_at = db.Column(db.DateTime(), nullable=False, default=datetime.utcnow)

class Tile(db.Model):
    __tablename__ = "tiles"

    TYPES = ["organic", "sponsored", "affiliate"]

    id = db.Column(db.Integer(), autoincrement=True, primary_key=True, info={"identity": [0,1]})
    campaign_id = db.Column(db.Integer(), db.ForeignKey("campaigns.id"))
    target_url = db.Column(db.String(255), nullable=False)
    bg_color = db.Column(db.String(16), nullable=False)
    title = db.Column(db.String(255), nullable=False)
    type = db.Column(db.String(40), nullable=False)

    image_uri = db.Column(db.Text(), nullable=False)
    enhanced_image_uri = db.Column(db.Text(), nullable=True)

    created_at = db.Column(db.DateTime(), nullable=False, default=datetime.utcnow)
    lists = db.relationship("List", secondary="tiles_lists",
                                backref=db.backref("tiles", lazy="dynamic"))

class UniqueCountsDaily(db.Model):
    __tablename__ = "unique_counts_daily"

    id = db.Column(db.Integer(), autoincrement=True, primary_key=True, info={"identity": [0,1]})
    tile_id = db.Column(db.Integer(), db.ForeignKey("tiles.id"))

    date = db.Column(db.Date(), nullable=False, default=current_date, index=True)
    impression = db.Column(db.Boolean(), nullable=False, default=True)
    locale = db.Column(db.String(5), nullable=False, default="en-US", index=True)
    country_code = db.Column(db.String(2), nullable=False, default="US", index=True)

class UniqueHLL(db.Model):
    __tablename__ = "unique_hlls"

    id = db.Column(db.Integer(), autoincrement=True, primary_key=True, info={"identity": [0,1]})
    unique_counts_daily_id = db.Column(db.Integer(), db.ForeignKey("unique_counts_daily.id"))

    index = db.Column(db.SmallInteger(), nullable=False, index=True)
    value = db.Column(db.SmallInteger(), nullable=False, index=True)

impression_stats_daily = db.Table('impression_stats_daily',
        db.Column('tile_id', db.Integer, db.ForeignKey('tiles.id'), nullable = False),
        db.Column('date', db.Date, index = True, nullable = False),
        db.Column('impressions', db.Integer, nullable = False, server_default = "0"),
        db.Column('clicks', db.Integer, nullable = False, server_default = "0"),
        db.Column('pinned', db.Integer, nullable = False, server_default = "0"),
        db.Column('blocked', db.Integer, nullable = False, server_default = "0"),
        db.Column('sponsored_link', db.Integer, nullable = False, server_default = "0"),
        db.Column('sponsored', db.Integer, nullable = False, server_default = "0"),
        db.Column('position', db.Integer, nullable = False, server_default = "0"),
        db.Column('locale', db.String(5), index = True, nullable = False),
        db.Column('country_code', db.String(2), index = True, nullable = False),
        db.Column('os', db.String(64), index = True, nullable = False),<commit_msg>simplified models to only have Tiles and aggregate tables<commit_after>from datetime import datetime
import sqlalchemy
from sqlalchemy.types import *
from sqlalchemy.ext.declarative import declarative_base
from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy.sql.functions import current_date
from sqlalchemy.dialects.postgresql import ARRAY
from splice.environment import Environment

db = Environment.instance().db

class Tile(db.Model):
    __tablename__ = "tiles"

    TYPES = {"organic", "sponsored", "affiliate"}

    id = db.Column(db.Integer(), autoincrement=True, primary_key=True, info={"identity": [0,1]})
    target_url = db.Column(db.String(255), nullable=False)
    bg_color = db.Column(db.String(16), nullable=False)
    title = db.Column(db.String(255), nullable=False)
    type = db.Column(db.String(40), nullable=False)

    image_uri = db.Column(db.Text(), nullable=False)
    enhanced_image_uri = db.Column(db.Text(), nullable=True)

    locale = db.Column(db.String(14), nullable=False, index=True)
    country_code = db.Column(db.String(5), nullable=False, index=True)

    created_at = db.Column(db.DateTime(), nullable=False, default=datetime.utcnow)

class UniqueCountsDaily(db.Model):
    __tablename__ = "unique_counts_daily"

    id = db.Column(db.Integer(), autoincrement=True, primary_key=True, info={"identity": [0,1]})
    tile_id = db.Column(db.Integer(), db.ForeignKey("tiles.id"))

    date = db.Column(db.Date(), nullable=False, default=current_date, index=True)
    impression = db.Column(db.Boolean(), nullable=False, default=True)
    locale = db.Column(db.String(14), nullable=False, default="en-US", index=True)
    country_code = db.Column(db.String(5), nullable=False, default="US", index=True)

unique_hlls = db.Table('unique_hlls',
    db.Column('unique_counts_daily_id', db.Integer, db.ForeignKey('unique_counts_daily.id')),
    db.Column('index', db.SmallInteger, nullable = False, server_default = "0", index=True),
    db.Column('value', db.SmallInteger, nullable = False, server_default = "0", index=True),
)

impression_stats_daily = db.Table('impression_stats_daily',
        db.Column('tile_id', db.Integer, db.ForeignKey('tiles.id')),
        db.Column('date', db.Date, index = True, nullable = False),
        db.Column('impressions', db.Integer, nullable = False, server_default = "0"),
        db.Column('clicks', db.Integer, nullable = False, server_default = "0"),
        db.Column('pinned', db.Integer, nullable = False, server_default = "0"),
        db.Column('blocked', db.Integer, nullable = False, server_default = "0"),
        db.Column('sponsored_link', db.Integer, nullable = False, server_default = "0"),
        db.Column('sponsored', db.Integer, nullable = False, server_default = "0"),
        db.Column('position', db.Integer, nullable = False, server_default = "0"),
        db.Column('locale', db.String(14), index = True, nullable = False),
        db.Column('country_code', db.String(5), index = True, nullable = False),
        db.Column('os', db.String(64), index = True, nullable = False),
{'fraction_non_alphanumeric': 0.15739111306643203, 'fraction_numerical': 0.009678838539375276, 'mean_word_length': 4.644320297951583, 'pattern_counts': {'":': 11, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5748259', 'n_tokens_mistral': 3087, 'n_tokens_neox': 2971, 'n_words': 555}