import sqlalchemy as sa

from .database import database

engine = sa.create_engine(database)
metadata = sa.MetaData()
metadata.create_all(engine)

for tbl in reversed(metadata.sorted_tables):
    engine.execute(tbl.delete())

## Using snowflake connector 
import snowflake.connector
from configparser import ConfigParser
import pandas as pd

config = ConfigParser()
config.read("config.ini")

user=config["SNOWFLAKE"]["USER"]
password=config["SNOWFLAKE"]["PASSWORD"]
database=config["SNOWFLAKE"]["DATABASE"]
schema=config["SNOWFLAKE"]["SCHEMA"]
warehouse=config["SNOWFLAKE"]["WAREHOUSE"]
role=config["SNOWFLAKE"]["ROLE"]



## snowflake connector method
connection = snowflake.connector.connect(
    user=user,
    password=password,
    account='hostelworld.eu-west-1',
    database = database,
    warehouse = warehouse,
    role = role)
print("snowflake connector connected...")

query = '''select * from production.ppc.campaign_performance_daily limit 1000 ;'''
df = pd.read_sql_query(sql=query, con=connection)
print('DF shape = ', df.shape)

connection.close()
print("connection closed")



### Using sql alchemy
from sqlalchemy import create_engine
from configparser import ConfigParser
import pandas as pd

config = ConfigParser()
config.read("config.ini")

user=config["SNOWFLAKE"]["USER"]
password=config["SNOWFLAKE"]["PASSWORD"]
database=config["SNOWFLAKE"]["DATABASE"]
schema=config["SNOWFLAKE"]["SCHEMA"]
warehouse=config["SNOWFLAKE"]["WAREHOUSE"]
role=config["SNOWFLAKE"]["ROLE"]

# sql alchemy method
engine = create_engine(
            f"""snowflake://{user}:{password}@hostelworld.eu-west-1/{database}/{schema}?warehouse={warehouse}&role={role}"""
)

connection = engine.connect()
print('sql alchemy connected...')
            
query = '''select * from production.ppc.campaign_performance_daily limit 1000 ;'''
df = pd.read_sql_query(sql=query, con=connection)
print('DF shape = ', df.shape)

connection.close()
print("connection closed")