"""
Creates ddl corresponding to defined model
:author Daniil Ossinkin

"""
import csv

from sqlalchemy import MetaData

from connection import get_sync_engine
from sqlalchemy.orm import Session
from src.data.model.db.base_db import Base
from src.data.model.db.partner_agent import (
    SalesOrderType, Agent
)


def init_sales_order_types(session: Session):
    sales_order_types = [
        SalesOrderType(
            code=201,
            designation="B2B"
        ),
        SalesOrderType(
            code=100,
            designation="Vororder"
        ),
        SalesOrderType(
            code=110,
            designation="Vorprogramm"
        ),
        SalesOrderType(
            code=120,
            designation="Vororder/Season Stock"
        ),
        SalesOrderType(
            code=140,
            designation="Best Performer"
        )
    ]
    for sales_order_type in sales_order_types:
        session.add(sales_order_type)
        # session.refresh(instance=sales_order_type)
    session.commit()


def init_agents(session: Session):
    # Specify the path to your CSV file
    csv_file = 'initial_data/vertreter_user.csv'

    # Open and read the CSV file
    with open(csv_file, 'r') as file:
        csv_reader = csv.reader(file, delimiter=";")

        # Iterate through the rows in the CSV file
        for row in csv_reader:
            agent = Agent(
                agent_num=row[1],
                name=row[2] + " " + row[3],
                password_hash=row[8],
                username=row[6],
                street="",
                city="",
                land="",
                iso_code="",
                email=row[5],
                telefon=""
            )
            session.merge(instance=agent)
    session.commit()


def create_tables():
    engine = get_sync_engine()
    m = MetaData()
    m.reflect(engine)
    m.drop_all(engine)
    Base.metadata.create_all(engine)


# run ddl script to create the database
create_tables()

session = Session(bind=get_sync_engine())
init_sales_order_types(session)
init_agents(session)