import datetime
import random
import sqlalchemy
from sqlalchemy import text

from config import config


def random_date(start, end):
    return start + datetime.timedelta(
        seconds=random.randint(0, int((end - start).total_seconds())),
    )


def clear_tables(connect):
    sql = [
        "DELETE FROM employment",
        "DELETE FROM operation",
        "DELETE FROM position",
        "DELETE FROM employee",
        "DELETE FROM project"
    ]
    for query in sql:
        connect.execute(query)


def fill_position(connect, count):
    sql = text("INSERT INTO position (name, salary) VALUES (:name, :salary)")
    values = []
    for i in range(count):
        values.append(dict(
            name=str(i),
            salary=random.randint(100, 10000)
        ))
    connect.execute(sql, values)


def fill_project(connect, count):
    sql = text("INSERT INTO project (name, budget) VALUES (:name, :budget)")
    values = []
    for i in range(count):
        values.append(dict(
            name=str(i),
            budget=random.randint(10 ** 3, 10 ** 6)
        ))
    connect.execute(sql, values)


def fill_employee(connect, count):
    sql = text("INSERT INTO employee (fio, sex, bday, start_date) VALUES (:fio, :sex, :bday, :start_date)")
    values = []
    for i in range(count):
        values.append(dict(
            fio=str(i),
            sex=random.choice(['m', 'f']),
            bday=random_date(datetime.datetime(1985, 1, 1), datetime.datetime(1995, 1, 1)),
            start_date=random_date(datetime.datetime(2010, 1, 1), datetime.datetime(2018, 1, 1))
        ))
    connect.execute(sql, values)


def get_table_ids(connect, table):
    res = connect.execute("Select id from {}".format(table)).fetchall()
    return tuple(pid[0] for pid in res)


def fill_operation(connect, count):
    pids = get_table_ids(connect, "project")
    sql = text("INSERT INTO operation (project_id, name, description, sum) VALUES (:pid, :name, :description, :sum)")
    values = []
    for i in range(count):
        values.append(dict(
            pid=random.choice(pids),
            name=str(i),
            description=str(i ** 2),
            sum=random.randint(1, 100)
        ))
    connect.execute(sql, values)


def fill_employment(connect, count):
    pids = get_table_ids(connect, "project")
    eids = get_table_ids(connect, "employee")
    posids = get_table_ids(connect, "position")
    sql = text("INSERT INTO employment (project_id, employee_id, position_id, start_date, rate) "
               "VALUES (:pid, :eid, :posid, :start_date, :rate)")
    values = []
    for i in range(count):
        values.append(dict(
            pid=random.choice(pids),
            eid=random.choice(eids),
            posid=random.choice(posids),
            start_date=random_date(datetime.datetime(2018, 1, 1), datetime.datetime(2020, 1, 1)),
            rate=(random.random() + 0.1) * 2.5
        ))
    connect.execute(sql, values)


def create_connect():
    engine = sqlalchemy.create_engine('postgresql://{user}:{password}@{host}:{port}/{db}'.format(**config))
    connect = engine.connect()
    return connect


def _main():
    connect = create_connect()
    fill_employment(connect, 100)
    # clear_tables(connect)


if __name__ == '__main__':
    _main()