from sqlalchemy import (
    MetaData, Table, Column, CheckConstraint, ForeignKeyConstraint, UniqueConstraint, Index,
    Boolean, DateTime, Integer, String, Text, func, text)
from sqlalchemy.dialects.postgresql import ARRAY, BYTEA, ENUM, JSONB, TIMESTAMP
from sqlalchemy.schema import ForeignKey

from libweasyl.models.helpers import (
    ArrowColumn, CharSettingsColumn, JSONValuesColumn, RatingColumn, WeasylTimestampColumn)
from libweasyl import constants


metadata = MetaData()


def default_fkey(*args, **kwargs):
    return ForeignKeyConstraint(*args, onupdate='CASCADE', ondelete='CASCADE', **kwargs)


api_tokens = Table(
    'api_tokens', metadata,
    Column('userid', Integer(), primary_key=True, nullable=False),
    Column('token', String(length=64), primary_key=True, nullable=False),
    Column('description', String()),
    default_fkey(['userid'], ['login.userid'], name='api_tokens_userid_fkey'),
)


authbcrypt = Table(
    'authbcrypt', metadata,
    Column('userid', Integer(), primary_key=True, nullable=False),
    Column('hashsum', String(length=100), nullable=False),
    default_fkey(['userid'], ['login.userid'], name='authbcrypt_userid_fkey'),
)


blocktag = Table(
    'blocktag', metadata,
    Column('userid', Integer(), primary_key=True, nullable=False),
    Column('tagid', Integer(), primary_key=True, nullable=False),
    Column('rating', RatingColumn, nullable=False),
    default_fkey(['tagid'], ['searchtag.tagid'], name='blocktag_tagid_fkey'),
    default_fkey(['userid'], ['login.userid'], name='blocktag_userid_fkey'),
)

Index('ind_blocktag_userid', blocktag.c.userid)


charcomment = Table(
    'charcomment', metadata,
    Column('commentid', Integer(), primary_key=True, nullable=False),
    Column('userid', Integer(), nullable=False),
    Column('targetid', Integer(), nullable=False),
    Column('parentid', Integer(), nullable=False, server_default='0'),
    Column('content', String(length=10000), nullable=False),
    Column('unixtime', WeasylTimestampColumn(), nullable=False),
    Column('settings', String(length=20), nullable=False, server_default=''),
    Column('hidden_by', Integer(), nullable=True),
    default_fkey(['targetid'], ['character.charid'], name='charcomment_targetid_fkey'),
    default_fkey(['userid'], ['login.userid'], name='charcomment_userid_fkey'),
    default_fkey(['hidden_by'], ['login.userid'], name='charcomment_hidden_by_fkey'),
)

Index('ind_charcomment_targetid_commentid', charcomment.c.targetid, charcomment.c.commentid)


collection = Table(
    'collection', metadata,
    Column('userid', Integer(), primary_key=True, nullable=False),
    Column('submitid', Integer(), primary_key=True, nullable=False),
    Column('unixtime', WeasylTimestampColumn(), nullable=False),
    Column('settings', String(length=20), nullable=False, server_default='p'),
    default_fkey(['userid'], ['login.userid'], name='collection_userid_fkey'),
    default_fkey(['submitid'], ['submission.submitid'], name='collection_submitid_fkey'),
)

Index('ind_collection_userid', collection.c.userid)


comments = Table(
    'comments', metadata,
    Column('commentid', Integer(), primary_key=True),
    Column('userid', Integer(), nullable=False),
    Column('target_user', Integer(), nullable=True),
    Column('target_sub', Integer(), nullable=True),
    Column('parentid', Integer(), nullable=True),
    Column('content', Text(), nullable=False),
    Column('unixtime', WeasylTimestampColumn(), nullable=False),
    Column('settings', CharSettingsColumn({
        'h': 'hidden',
        's': 'staff-note',
    }, length=20), nullable=False, server_default=''),
    Column('hidden_by', Integer(), nullable=True),
    default_fkey(['userid'], ['login.userid'], name='comments_userid_fkey'),
    default_fkey(['target_user'], ['login.userid'], name='comments_target_user_fkey'),
    default_fkey(['target_sub'], ['submission.submitid'], name='comments_target_sub_fkey'),
    default_fkey(['parentid'], ['comments.commentid'], name='comments_parentid_fkey'),
    default_fkey(['hidden_by'], ['login.userid'], name='comments_hidden_by_fkey'),
    CheckConstraint('(target_user IS NOT NULL) != (target_sub IS NOT NULL)', name='comments_target_check'),
)

Index('ind_comments_target_user_commentid', comments.c.target_user, comments.c.commentid, postgresql_where=comments.c.target_user != None)
Index('ind_comments_target_sub_commentid', comments.c.target_sub, comments.c.commentid, postgresql_where=comments.c.target_sub != None)


commishclass = Table(
    'commishclass', metadata,
    Column('classid', Integer(), primary_key=True, nullable=False),
    Column('userid', Integer(), primary_key=True, nullable=False),
    Column('title', String(length=100), nullable=False),
    default_fkey(['userid'], ['login.userid'], name='commishclass_userid_fkey'),
)

Index('ind_userid_title', commishclass.c.userid, commishclass.c.title, unique=True)
Index('ind_commishclass_userid', commishclass.c.userid)


commishdesc = Table(
    'commishdesc', metadata,
    Column('userid', Integer(), primary_key=True, nullable=False),
    Column('content', String(length=20000), nullable=False),
    default_fkey(['userid'], ['login.userid'], name='commishdesc_userid_fkey'),
)


commishprice = Table(
    'commishprice', metadata,
    Column('priceid', Integer(), primary_key=True, nullable=False),
    Column('classid', Integer(), primary_key=True, nullable=False),
    Column('userid', Integer(), primary_key=True, nullable=False),
    Column('title', String(length=500), nullable=False),
    Column('amount_min', Integer(), nullable=False),
    Column('amount_max', Integer(), nullable=False),
    Column('settings', String(length=20), nullable=False, server_default=''),
    default_fkey(['userid'], ['login.userid'], name='commishprice_userid_fkey'),
)

Index('ind_classid_userid_title', commishprice.c.classid, commishprice.c.userid, commishprice.c.title, unique=True)


emailblacklist = Table(
    'emailblacklist', metadata,
    Column('id', Integer(), primary_key=True, nullable=False),
    Column('added_by', Integer(), nullable=False),
    Column('domain_name', String(length=252), nullable=False, unique=True),
    Column('reason', Text(), nullable=False),
    default_fkey(['added_by'], ['login.userid'], name='emailblacklist_userid_fkey'),
)


emailverify = Table(
    'emailverify', metadata,
    Column('userid', Integer(), primary_key=True, nullable=False),
    Column('email', String(length=100), nullable=False, unique=True),
    Column('createtimestamp', DateTime(timezone=True), nullable=False, server_default=func.now()),
    Column('token', String(length=100), nullable=False),
    default_fkey(['userid'], ['login.userid'], name='emailverify_userid_fkey'),
)

Index('ind_emailverify_token', emailverify.c.token)


favorite = Table(
    'favorite', metadata,
    Column('userid', Integer(), primary_key=True, nullable=False),
    Column('targetid', Integer(), primary_key=True, nullable=False, autoincrement=False),
    Column('type', String(length=5), primary_key=True, nullable=False, server_default=''),
    Column('unixtime', WeasylTimestampColumn(), nullable=False),
    default_fkey(['userid'], ['login.userid'], name='favorite_userid_fkey'),
)

Index('ind_favorite_userid', favorite.c.userid)
Index('ind_favorite_type_targetid', favorite.c.type, favorite.c.targetid)
Index('ind_favorite_userid_type_unixtime', favorite.c.userid, favorite.c.type, favorite.c.unixtime)


folder = Table(
    'folder', metadata,
    Column('folderid', Integer(), primary_key=True, nullable=False),
    Column('parentid', Integer(), nullable=False),
    Column('userid', Integer(), nullable=False),
    Column('title', String(length=100), nullable=False),
    Column('settings', CharSettingsColumn({
        'h': 'hidden',
        'n': 'no-notifications',
        'u': 'profile-filter',
        'm': 'index-filter',
        'f': 'featured-filter',
    }, length=20), nullable=False, server_default=''),
    default_fkey(['userid'], ['login.userid'], name='folder_userid_fkey'),
)

Index('ind_folder_userid', folder.c.userid)


forgotpassword = Table(
    'forgotpassword', metadata,
    Column('token_sha256', BYTEA(), primary_key=True, nullable=False),
    Column('email', String(length=254), nullable=False),
    Column('created_at', TIMESTAMP(timezone=True), nullable=False, server_default=func.now()),
)

Index('ind_forgotpassword_created_at', forgotpassword.c.created_at)


frienduser = Table(
    'frienduser', metadata,
    Column('userid', Integer(), primary_key=True, nullable=False),
    Column('otherid', Integer(), primary_key=True, nullable=False),
    Column('settings', CharSettingsColumn({
        'p': 'pending',
    }, length=20), nullable=False, server_default='p'),
    Column('created_at', TIMESTAMP(timezone=True), nullable=False, server_default=func.now()),
    default_fkey(['otherid'], ['login.userid'], name='frienduser_otherid_fkey'),
    default_fkey(['userid'], ['login.userid'], name='frienduser_userid_fkey'),
)

Index('ind_frienduser_otherid', frienduser.c.otherid)
Index('ind_frienduser_userid', frienduser.c.userid)


character = Table(
    'character', metadata,
    Column('charid', Integer(), primary_key=True, nullable=False),
    Column('userid', Integer(), nullable=False),
    Column('unixtime', WeasylTimestampColumn(), nullable=False),
    Column('char_name', String(length=100), nullable=False, server_default=''),
    Column('age', String(length=100), nullable=False, server_default=''),
    Column('gender', String(length=100), nullable=False, server_default=''),
    Column('height', String(length=100), nullable=False, server_default=''),
    Column('weight', String(length=100), nullable=False, server_default=''),
    Column('species', String(length=100), nullable=False, server_default=''),
    Column('content', String(length=100000), nullable=False, server_default=u""),
    Column('rating', RatingColumn, nullable=False),
    Column('settings', CharSettingsColumn({
        'h': 'hidden',
        'f': 'friends-only',
    }, length=20), nullable=False, server_default=''),
    Column('hidden', Boolean(), nullable=False, server_default='f'),
    Column('friends_only', Boolean(), nullable=False, server_default='f'),
    Column('page_views', Integer(), nullable=False, server_default='0'),
    default_fkey(['userid'], ['login.userid'], name='character_userid_fkey'),
)

Index('ind_character_userid', character.c.userid)


google_doc_embeds = Table(
    'google_doc_embeds', metadata,
    Column('submitid', Integer(), primary_key=True, nullable=False),
    Column('embed_url', String(length=255), nullable=False),
    default_fkey(['submitid'], ['submission.submitid'], name='google_doc_embeds_submitid_fkey'),
)


ignoreuser = Table(
    'ignoreuser', metadata,
    Column('userid', Integer(), primary_key=True, nullable=False),
    Column('otherid', Integer(), primary_key=True, nullable=False),
    default_fkey(['userid'], ['login.userid'], name='ignoreuser_userid_fkey'),
    default_fkey(['otherid'], ['login.userid'], name='ignoreuser_otherid_fkey'),
)

Index('ind_ignoreuser_userid', ignoreuser.c.userid)


journal = Table(
    'journal', metadata,
    Column('journalid', Integer(), primary_key=True, nullable=False),
    Column('userid', Integer(), nullable=False),
    Column('title', String(length=200), nullable=False),
    Column('content', String(length=100000), nullable=False),
    Column('rating', RatingColumn, nullable=False),
    Column('unixtime', WeasylTimestampColumn(), nullable=False),
    Column('settings', CharSettingsColumn({
        'h': 'hidden',
        'f': 'friends-only',
    }, length=20), nullable=False, server_default=''),
    Column('hidden', Boolean(), nullable=False, server_default='f'),