from sqlalchemy import Column, ForeignKey, Integer, String, Text, DateTime, Boolean, BigInteger, SmallInteger, Float
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.engine.url import URL
from sqlalchemy.orm import relationship, backref
from sqlalchemy import create_engine

import config

SQLALCHEMY_URL = URL(**config.database[config.database_default])

Base = declarative_base()

class Account(Base):
    __tablename__ = 'account'
    sqlite_autoincrement=True

    id = Column(Integer, primary_key=True)
    username = Column(String(100), nullable=False, unique=True)
    display_name = Column(String(250), nullable=False, index=True)
    email = Column(String(100), nullable=False, unique=True)
    password = Column(String(40), nullable=False)
    salt = Column(String(13), nullable=False)
    account_group_id = Column(Integer, ForeignKey('account_group.id', onupdate='CASCADE', ondelete='SET NULL'))
    is_active = Column(Boolean, nullable=False, server_default='1')
    is_login = Column(Boolean, nullable=False, server_default='0')

class Account_Meta(Base):
    __tablename__ = 'account_meta'

    account_id = Column(Integer, ForeignKey('account.id', ondelete='CASCADE'), primary_key=True)
    meta_name = Column(String(30), nullable=False, index=True)
    meta_value = Column(Text)

    account = relationship('Account', backref=backref('meta', uselist=False))

class Account_Group(Base):
    __tablename__ = 'account_group'
    sqlite_autoincrement=True

    id = Column(Integer, primary_key=True)
    name = Column(String(20), nullable=False)
    
    accounts = relationship('Account', backref='account_group')

class Account_Image(Base):
    __tablename__ = 'account_image'

    account_id = Column(Integer, ForeignKey('account.id', ondelete='CASCADE'), primary_key=True)
    image_data = Column(Text)

    account = relationship('Account', backref=backref('image', uselist=False))

class Session(Base):
    __tablename__ = 'session'

    account_id = Column(Integer, ForeignKey('account.id', ondelete='CASCADE'), primary_key=True)
    id = Column(String(32), nullable=False, unique=True)
    expired = Column(DateTime)

    account = relationship('Account', backref=backref('session', uselist=False))

class Token(Base):
    __tablename__ = 'token'
    
    account_id = Column(Integer, ForeignKey('account.id', ondelete='CASCADE'), primary_key=True)
    token = Column(String(4), nullable=False)

    account = relationship('Account', backref=backref('token', uselist=False))

class Device(Base):
    __tablename__ = 'device'

    account_id = Column(Integer, ForeignKey('account.id', ondelete='CASCADE'), primary_key=True)
    device_id = Column(Text, nullable=False)

    account = relationship('Account', backref=backref('device', uselist=False))

class Schedule(Base):
    __tablename__ = 'schedule'

    uid = Column(String(40), primary_key=True)
    summary = Column(Text, nullable=False)
    start = Column(DateTime, nullable=False, index=True)

class Schedule_Meta(Base):
    __tablename__ = 'schedule_meta'

    uid = Column(String(40), primary_key=True)
    is_active = Column(Boolean, nullable=False, server_default='1')
    solution = Column(String(20), nullable=False, server_default='Type E/V')
    region = Column(String(20))
    bd = Column(String(50))
    attendee = Column(String(100))
    pic = Column(String(100))
    location = Column(Text)
    description = Column(Text)


if __name__ == '__main__':
    from sys import argv

    print('Prepare engine...')
    engine = create_engine(SQLALCHEMY_URL)

    # Create database if "-c" parameter is provided (only mysql, postgresql, mssql server)
    if len(argv) > 1 and argv[1] == '-c':
        if config.database_default in ['mysql', 'postgresql', 'mssql']:
            from sqlalchemy_utils import database_exists, create_database

            if database_exists(engine.url):
                print('Database "%s" already exists, abort creating database' % SQLALCHEMY_URL.database)
            else:
                print('Create database "%s"...' % SQLALCHEMY_URL.database)
                create_database(engine.url)
    
    print('Drop tables...')
    Base.metadata.drop_all(engine)
    print('Create tables...')
    Base.metadata.create_all(engine)
    print('Finished!')