# -*- encoding: utf-8 -*-

from sqlalchemy import MetaData, Table, Column, Integer, Unicode, Boolean, String, Date, DateTime, ForeignKey, UniqueConstraint, CheckConstraint, create_engine

import cfg

metadata = MetaData()


part_table = Table('part', metadata,
                   Column('id', Integer, primary_key=True),
                   Column('url', Unicode, unique=True, index=True),  # akphil
                   Column('name', Unicode),                          # AKPhil
                   Column('title', Unicode),                         # Arbeitskreis Philosophie
                   Column('order', String, unique=True))


session_table = Table('session', metadata,
                      Column('id', Integer, primary_key=True),
                      Column('url', Unicode, index=True),            # 1
                      Column('name', Unicode),                       # AKPhil 1
                      Column('title', Unicode),                      # Information und Bedeutung
                      Column('location', Unicode),
                      Column('begin', DateTime),
                      Column('end', DateTime),
                      Column('part_order', String),
                      Column('part_id', Integer, ForeignKey('part.id'), nullable=False),
                      UniqueConstraint('part_id', 'part_order'),
                      UniqueConstraint('part_id', 'url'))


paper_table = Table('paper', metadata,
                    Column('id', Integer, primary_key=True),
                    Column('url', Unicode, index=True),              # 1
                    Column('name', Unicode),                         # AKPhil 1.1
                    Column('html_link', Unicode),                    # html for the link text (i.e. title)
                    Column('html_extra', Unicode),                   # html for the text after the link (i.e. authors)
                    Column('html', Unicode),                         # html version
                    Column('latex', Unicode),                        # latex version
                    Column('search', Unicode),
                    Column('search_title', Unicode),
                    Column('search_authors', Unicode),
                    Column('search_presenter', Unicode),
                    Column('search_session', Unicode),
                    Column('search_location', Unicode),
                    Column('type', Unicode),
                    Column('part', Unicode),                         # shortcut for paper.session.part.name (search speed)
                    Column('joint', Boolean),                        # exclude from search when no part selected (avoid multiple hits)
                    Column('begin', DateTime),
                    Column('end', DateTime),
                    Column('session_order', String),
                    Column('session_id', Integer, ForeignKey('session.id'), nullable=False),
                    UniqueConstraint('session_id', 'session_order'),
                    UniqueConstraint('session_id', 'url'))


state_table = Table('state', metadata,
                    Column('id', Integer, primary_key=True),
                    Column('lang', Unicode),                         # de
                    Column('layout', Unicode),                       # screen
                    Column('format', Unicode),                       # regular
                    CheckConstraint("lang in ('%s')" % "', '".join(cfg.general.languages)),
                    CheckConstraint("layout in ('%s')" % "', '".join(cfg.general.layouts)),
                    CheckConstraint("format in ('%s')" % "', '".join(cfg.format.choices)))


state_session_table = Table('state_session', metadata,
                            Column('id', Integer, primary_key=True),
                            Column('choice', Unicode),               # minimal
                            Column('state_id', Integer, ForeignKey('state.id'), nullable=False),
                            Column('session_id', Integer, ForeignKey('session.id'), nullable=False),
                            UniqueConstraint('state_id', 'session_id'),
                            CheckConstraint("choice in ('%s')" % "', '".join(cfg.session.choices[1:])))


state_paper_table = Table('state_paper', metadata,
                          Column('id', Integer, primary_key=True),
                          Column('choice', Unicode),                 # title
                          Column('state_id', Integer, ForeignKey('state.id'), nullable=False),
                          Column('paper_id', Integer, ForeignKey('paper.id'), nullable=False),
                          UniqueConstraint('state_id', 'paper_id'),
                          CheckConstraint("choice in ('%s')" % "', '".join(cfg.paper.choices[1:])))


save_table = Table('save', metadata,
                   Column('id', Integer, primary_key=True),
                   Column('login', Unicode, nullable=False),
                   Column('passwd_hash', Unicode, nullable=False),
                   UniqueConstraint('login', 'passwd_hash'))


save_session_table = Table('save_session', metadata,
                           Column('id', Integer, primary_key=True),
                           Column('choice', Unicode),               # minimal
                           Column('save_id', Integer, ForeignKey('save.id'), nullable=False),
                           Column('session_id', Integer, ForeignKey('session.id'), nullable=False),
                           UniqueConstraint('save_id', 'session_id'),
                           CheckConstraint("choice in ('%s')" % "', '".join(cfg.session.choices[1:])))


save_paper_table = Table('save_paper', metadata,
                         Column('id', Integer, primary_key=True),
                         Column('choice', Unicode),                 # title
                         Column('save_id', Integer, ForeignKey('save.id'), nullable=False),
                         Column('paper_id', Integer, ForeignKey('paper.id'), nullable=False),
                         UniqueConstraint('save_id', 'paper_id'),
                         CheckConstraint("choice in ('%s')" % "', '".join(cfg.paper.choices[1:])))


update_table = Table('update', metadata,
                     Column('id', Integer, primary_key=True),
                     Column('number', Integer, unique=True, index=True),
                     Column('released', DateTime))


update_item_table = Table('update_item', metadata,
                          Column('id', Integer, primary_key=True),
                          Column('description', Unicode),
                          Column('update_id', Integer, ForeignKey('update.id'), nullable=False),
                          Column('paper_id', Integer, ForeignKey('paper.id'), nullable=False))


engine = create_engine(cfg.db.connect_url, connect_args=cfg.db.connect_args)
