# coding: utf-8
from sqlalchemy import Boolean, Column, DateTime, ForeignKey, Index, Integer, Table, Text, text
from sqlalchemy.orm import relationship
from sqlalchemy.dialects.postgresql.base import CIDR, INTERVAL
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()
metadata = Base.metadata


class DirectMessage(Base):
    __tablename__ = 'direct_messages'

    dm_id = Column(Integer, primary_key=True, server_default=text("nextval('direct_messages_dm_id_seq'::regclass)"))
    message_id = Column(ForeignKey(u'messages.message_id'), nullable=False)
    author_id = Column(ForeignKey(u'users.user_id'), nullable=False)
    recip_id = Column(ForeignKey(u'users.user_id'), nullable=False)

    author = relationship(u'User', primaryjoin='DirectMessage.author_id == User.user_id')
    message = relationship(u'Message')
    recip = relationship(u'User', primaryjoin='DirectMessage.recip_id == User.user_id')


class FeedImage(Base):
    __tablename__ = 'feed_images'

    feed_url = Column(Text, nullable=False)
    image_url = Column(Text, primary_key=True, nullable=False, index=True)
    message_id = Column(ForeignKey(u'messages.message_id'))
    queued_on = Column(DateTime, nullable=False, server_default=text("now()"))
    room = Column(Text, primary_key=True, nullable=False)
    link = Column(Text)
    title = Column(Text)

    message = relationship(u'Message')


class ImageAltar(Base):
    __tablename__ = 'image_altars'

    message_id = Column(Integer, primary_key=True)
    user_id = Column(Integer, nullable=False)


class ImageUrl(Base):
    __tablename__ = 'image_urls'

    url = Column(Text, primary_key=True)
    last_posted = Column(DateTime, nullable=False, server_default=text("now()"))


t_invalid_feed_images = Table(
    'invalid_feed_images', metadata,
    Column('image_url', Text, nullable=False, unique=True),
    Column('reason', Text, nullable=False),
    Column('added_on', DateTime, nullable=False, server_default=text("now()"))
)


class Message(Base):
    __tablename__ = 'messages'
    __table_args__ = (
        Index('messages_room_id_created_on_idx', 'room_id', 'created_on'),
        Index('messages_user_created_on_image_only_idx', 'user_id', 'created_on')
    )

    message_id = Column(Integer, primary_key=True, server_default=text("nextval('messages_message_id_seq'::regclass)"))
    user_id = Column(ForeignKey(u'users.user_id'), nullable=False)
    room_id = Column(ForeignKey(u'rooms.room_id'), nullable=False)
    content = Column(Text, nullable=False)
    created_on = Column(DateTime, nullable=False, server_default=text("now()"))
    is_image = Column(Boolean)
    is_text = Column(Boolean, nullable=False, server_default=text("false"))

    room = relationship(u'Room')
    user = relationship(u'User')


class Mute(Base):
    __tablename__ = 'mutes'

    user_id = Column(ForeignKey(u'users.user_id'), nullable=False)
    admin_id = Column(ForeignKey(u'users.user_id'), nullable=False)
    set_on = Column(DateTime, nullable=False, server_default=text("now()"))
    duration = Column(INTERVAL, nullable=False)
    reason = Column(Text, nullable=False)
    cancel_admin_id = Column(ForeignKey(u'users.user_id'))
    cancelled = Column(Boolean, nullable=False, server_default=text("false"))
    cancel_reason = Column(Text)
    mute_id = Column(Integer, primary_key=True, server_default=text("nextval('mutes_mute_id_seq'::regclass)"))

    admin = relationship(u'User', primaryjoin='Mute.admin_id == User.user_id')
    cancel_admin = relationship(u'User', primaryjoin='Mute.cancel_admin_id == User.user_id')
    user = relationship(u'User', primaryjoin='Mute.user_id == User.user_id')


class Room(Base):
    __tablename__ = 'rooms'

    room_id = Column(Integer, primary_key=True, server_default=text("nextval('rooms_room_id_seq'::regclass)"))
    key = Column(Text, nullable=False, unique=True)
    created_on = Column(DateTime, nullable=False, server_default=text("now()"))
    admin_only = Column(Boolean, nullable=False, server_default=text("false"))
    name = Column(Text, nullable=False)
    description = Column(Text)
    active = Column(Boolean, nullable=False, server_default=text("true"))
    history_size = Column(Integer, nullable=False, server_default=text("25"))
    max_file_size = Column(Integer, nullable=False, server_default=text("1048576"))
    max_image_width = Column(Integer, nullable=False, server_default=text("2000"))
    max_image_height = Column(Integer, nullable=False, server_default=text("2000"))


class Tag(Base):
    __tablename__ = 'tags'
    __table_args__ = (
        Index('tags_user_id_created_on_idx', 'user_id', 'created_on'),
    )

    user_id = Column(ForeignKey(u'users.user_id'), primary_key=True, nullable=False, index=True)
    message_id = Column(ForeignKey(u'messages.message_id'), primary_key=True, nullable=False, index=True)
    created_on = Column(DateTime, nullable=False, server_default=text("now()"))
    tag = Column(Text, primary_key=True, nullable=False)

    message = relationship(u'Message')
    user = relationship(u'User')


class User(Base):
    __tablename__ = 'users'

    user_id = Column(Integer, primary_key=True, server_default=text("nextval('users_user_id_seq'::regclass)"))
    nick = Column(Text, nullable=False, unique=True)
    hash = Column(Text, nullable=False)
    email = Column(Text, nullable=False)
    created_on = Column(DateTime, nullable=False, server_default=text("now()"))
    avatar = Column(Text)
    contact = Column(Text)
    bio = Column(Text)
    is_admin = Column(Boolean)
    last_login = Column(DateTime)
    created_ip = Column(CIDR, server_default=text("'0.0.0.0/8'::cidr"))
    last_ip = Column(CIDR, server_default=text("'0.0.0.0/8'::cidr"))

from sqlalchemy import Column, Integer, LargeBinary, String, create_engine, sql
from sqlalchemy.orm import sessionmaker
DB_USER="postgres"
DB_HOST="localhost"
DB_NAME="dumpfm"
engine = create_engine('postgresql://{}@{}/{}'.format(
    DB_USER,
#    DB_PASSWORD,
    DB_HOST,
    DB_NAME
))
Session = sessionmaker(bind=engine)

session = Session()
message_list = session.query(Message).filter_by(is_image=True)
count = 0
import sys
import re
f = open("IDLIST2", "w")
exp = re.compile(r'.*asdf.us/im/.*')
exp2 = re.compile(r'.*favor.*', re.IGNORECASE)
for message in message_list:
    if exp.match(message.content):
        session2 = Session()
        favorites_count = 0
        tags = session2.query(Tag).filter_by(message_id=message.message_id)
        for tag in tags:
            if exp2.match(tag.tag):
                favorites_count += 1
        session2.close()
        f.write("%s\t%s\t%s\n" % ( message.message_id, message.content.encode('utf8'), favorites_count))