from sqlalchemy import(
    MetaData,
    Table,
    Column,
    Integer,
    String,
    Boolean,
    BigInteger,
    Date,
    DateTime,
    ForeignKey
)
from sqlalchemy.sql.sqltypes import TIMESTAMP

from tars.db.utils import get_db_engine

db_engine = get_db_engine(database="twitter")
metadata = MetaData()

USERS_LOG_TABLE = Table("users_log", metadata,
    Column('id', BigInteger),
    Column('id_str', String(50)),
    Column('name', String(50)),
    Column('screen_name', String(50)),
    Column('location', String(50)),
    Column('description', String(320)),
    Column('url', String(50)),
    Column("protected", Boolean),
    Column("followers_count", Integer),
    Column("friends_count", Integer),
    Column("listed_count", Integer),
    Column("created_at", String(50)),
    Column("favourites_count", Integer),
    Column("utc_offset", String(50)),
    Column("time_zone", String(50)),
    Column("geo_enabled", String(50)),
    Column("verified", String(50)),
    Column("statuses_count", Integer),
    Column("lang", String(50)),
    Column('contributors_enabled', Boolean),
    Column('is_translator', Boolean),
    Column('is_translation_enabled', Boolean),
    Column('profile_background_color', String(25)),
    Column('profile_background_image_url', String(100)),
    Column('profile_background_image_url_https', String(100)),
    Column('profile_background_tile', Boolean),
    Column('profile_image_url', String(100)),
    Column('profile_image_url_https', String(100)),
    Column('profile_banner_url', String(100)),
    Column('profile_link_color', String(25)),
    Column('profile_sidebar_border_color', String(25)),
    Column('profile_sidebar_fill_color', String(25)),
    Column('profile_text_color', String(25)),
    Column('profile_use_background_image', Boolean),
    Column('has_extended_profile', Boolean),
    Column('default_profile', Boolean),
    Column('default_profile_image', Boolean),
    Column('following', Boolean),
    Column('follow_request_sent', Boolean), 
    Column('notifications', Boolean),
    Column('translator_type', String(25)),
    Column('ds', Date),
    Column('ts', DateTime)
)

USERS_DIM_TABLE = Table("users_dim", metadata,
    Column('id', BigInteger, primary_key=True),
    Column('id_str', String(50)),
    Column('name', String(50)),
    Column('screen_name', String(50)),
    Column('location', String(50)),
    Column('description', String(320)),
    Column('url', String(50)),
    Column("protected", Boolean),
    Column("followers_count", Integer),
    Column("friends_count", Integer),
    Column("listed_count", Integer),
    Column("created_at", String(50)),
    Column("favourites_count", Integer),
    Column("utc_offset", String(50)),
    Column("time_zone", String(50)),
    Column("geo_enabled", String(50)),
    Column("verified", String(50)),
    Column("statuses_count", Integer),
    Column("lang", String(50)),
    Column('contributors_enabled', Boolean),
    Column('is_translator', Boolean),
    Column('is_translation_enabled', Boolean),
    Column('profile_background_color', String(25)),
    Column('profile_background_image_url', String(100)),
    Column('profile_background_image_url_https', String(100)),
    Column('profile_background_tile', Boolean),
    Column('profile_image_url', String(100)),
    Column('profile_image_url_https', String(100)),
    Column('profile_banner_url', String(100)),
    Column('profile_link_color', String(25)),
    Column('profile_sidebar_border_color', String(25)),
    Column('profile_sidebar_fill_color', String(25)),
    Column('profile_text_color', String(25)),
    Column('profile_use_background_image', Boolean),
    Column('has_extended_profile', Boolean),
    Column('default_profile', Boolean),
    Column('default_profile_image', Boolean),
    Column('following', Boolean),
    Column('follow_request_sent', Boolean), 
    Column('notifications', Boolean),
    Column('translator_type', String(25)),
    Column('ds', Date),
    Column('ts', DateTime)
)

TWEETS_LOG_TABLE = Table("tweets_log", metadata,
    Column('created_at', TIMESTAMP),
    Column('user_id', BigInteger, ForeignKey('users_dim.id', onupdate="cascade")),
    Column('tweet_id', BigInteger, primary_key=True),
    Column('retweet_id', BigInteger),
    Column('quoted_tweet_id', BigInteger),
    Column('lang', String(10)),
    Column('source', String(500)),
    Column('full_text', String(500)),
    Column('retweet_count', Integer),
    Column('favorite_count', Integer)
)

RETWEETS_LOG_TABLE = Table("retweets_log", metadata,
    Column('created_at', TIMESTAMP),
    Column('user_id', BigInteger),
    Column('tweet_id', BigInteger, primary_key=True),
    Column('lang', String(10)),
    Column('source', String(500)),
    Column('full_text', String(500)),
    Column('retweet_count', Integer),
    Column('favorite_count', Integer)
)

QUOTED_TWEETS_LOG_TABLE = Table("quoted_tweets_log", metadata,
    Column('created_at', TIMESTAMP),
    Column('user_id', BigInteger),
    Column('tweet_id', BigInteger, primary_key=True),
    Column('lang', String(10)),
    Column('source', String(500)),
    Column('full_text', String(500)),
    Column('retweet_count', Integer),
    Column('favorite_count', Integer)
)

metadata.create_all(db_engine)
