import random
import datetime as dt

import pandas as pd

import matplotlib.pyplot as plt
import seaborn as sns

from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import declarative_base
from sqlalchemy import create_engine, VARCHAR, Integer, Column, DateTime, ForeignKey

db_uri = """sqlite:///ipython_sql.db"""
engine = create_engine(db_uri)


# Удобный интерфейс для работы с БД, аля Flask\Django
class Db:
    def __init__(self, engine=None):
        self.engine = engine
        self._session = None

    @property
    def session(self):
        if self._session is None:
            self._session = sessionmaker(bind=self.engine)()
            return self._session
        else:
            return self._session


# ORM Модельки 
Base = declarative_base()


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, nullable=False)
    name = Column(VARCHAR(250), nullable=False)


class Order(Base):
    __tablename__ = "orders"

    id = Column(Integer, primary_key=True, index=True)
    order_id = Column(Integer, nullable=False)
    user_id = Column(Integer, ForeignKey("users.user_id"), nullable=False)
    price = Column(Integer, nullable=False)
    created_at = Column(DateTime, nullable=False)
    status_id = Column(Integer, ForeignKey("statuses.status_id"), nullable=False)


class Status(Base):
    __tablename__ = "statuses"

    id = Column(Integer, primary_key=True, index=True)
    status_id = Column(Integer, nullable=False)
    name = Column(VARCHAR(100), nullable=False)


# Создаем таблички в БД
Base.metadata.create_all(bind=engine)

"""## Генерим данные и заполняем БД"""

db = Db(engine=engine)


def init_load_db(db=None):
    user_ids = [random.randint(1000, 9999) for _ in range(10)]
    user_names = ("Jon", "Max", "Kate", "Mike", "Nick", "Olivia", "Charlotte")

    users = [{"user_id": id, "name": random.choice(user_names)} for id in user_ids]

    for user in users:
        db.session.add(User(**user))
    db.session.commit()

    statuses = [{"status_id": 1, "name": "closed"}, {"status_id": 2, "name": "processed"}]

    for status in statuses:
        db.session.add(Status(**status))
    db.session.commit()

    dates = pd.date_range(start='2022-01-01', end='2022-09-01', periods=5000)
    orders = []
    for order_id in range(5000):
        orders += [{
            "order_id": order_id,
            "user_id": random.choice(user_ids),
            "price": random.randint(100, 10000),
            "created_at": dates[order_id],
            "status_id": random.choice((1, 2))
        }]

    for order in orders:
        db.session.add(Order(**order))
    db.session.commit()


init_load_db(db=db)

# Проверяем, что данные загрузили
with engine.connect() as conn:
    res = conn.execute("""select * from orders limit 10;""").fetchall()
res