from langchain import SQLDatabase
import pandas as pd
from sqlalchemy import create_engine, Column, Integer, String, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime
from sqlalchemy import create_engine, Column, String, Integer, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

def create_demand_table(engine, table_name, excel_file):
    # Read the Excel file
    dataframes = pd.read_excel(excel_file, sheet_name=None)

    # Create a base class for the table models
    Base = declarative_base()

    # Define the table model
    class DemandPlanned(Base):
        __tablename__ = table_name

        KEY = Column(String, primary_key=True)
        DU = Column(String)
        ORIGIN = Column(String)
        DESTINATION = Column(String)
        DEMAND_PLANNED_QTY = Column(Integer)
        DEMAND_PLANNED_DATE = Column(Date)

    # Drop the existing table in the SQLite database, if it exists
    Base.metadata.drop_all(engine)

    # Create the table in the SQLite database
    Base.metadata.create_all(engine)

    # Create a session to interact with the database
    Session = sessionmaker(bind=engine)

    with Session() as session:
        # Insert data into the table (db)
        demand = dataframes.get('Demand-Planned')
        if demand is not None:
            demand['DEMAND_PLANNED_DATE'] = pd.to_datetime(demand['DEMAND_PLANNED_DATE']).dt.strftime('%Y-%m-%d')
            demand.to_sql(table_name, con=engine, if_exists='append', index=False)

    db = SQLDatabase(engine)

    # Commit the changes to the production database
    session.commit()

    # Close the session
    session.close()

    return db