from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, DateTime, BOOLEAN
from sqlalchemy.orm import sessionmaker
from sqlalchemy import exc

Base = declarative_base()

sql_script = '''
CREATE DATABASE IF NOT EXISTS university DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE {};
CREATE TABLE people(id INTEGER NOT NULL, surname CHAR(32), name CHAR(32), patronymic CHAR(32), burthday DATETIME, phone CHAR(32));
CREATE TABLE student(id INTEGER NOT NULL, group CHAR(10), boss BOOLEAN, speciality CHAR(100), people_id INTEGER NOT NULL);
ALTER TABLE people ADD PRIMARY KEY (id);
ALTER TABLE student ADD PRIMARY KEY (id);
ALTER TABLE student ADD FOREIGN KEY (people_id) REFERENCES people (id) ON DELETE CASCADE;
'''


class University:
    def __init__(self, host='localhost', port=3306, user='root', password='python', base_name='university'):
        self.engine = create_engine('mysql://{}:{}@{}:{}'.format(user, password, host, port), echo=False)
        # создаем новую базу если ее не существует
        with self.engine.begin() as conn:
            if base_name not in [i[0] for i in conn.execute('show databases;').fetchall()]:
                conn.execute(sql_script.format(base_name))
                print('base university created successfully')
            else:
                conn.execute('USE {};'.format(base_name))