import pandas as pd
from datetime import date
import sys
import os
from dotenv import load_dotenv
load_dotenv()
import pytest
sys.path.append(os.path.join(os.path.dirname(__file__), '../djagitit'))
import db

user = db.safely_getenv('usernameSnowflake').split('.')[0]
table_name_test_snowflake = f'SME_ANALYTICS.SHARED.{user}_djagitit_test'


class TestSnowflake:

    @staticmethod
    def test_init_without_args():
        snw = db.SnowflakeDB(account='SME-DELPHI')

    @staticmethod
    def test_init_with_args():
        snw = db.SnowflakeDB(account='SME-DELPHI', database='SME_ANALYTICS', schema='SHARED')

    @staticmethod
    def test_execute():
        q1 = f'''drop table if exists {table_name_test_snowflake};'''
        q2 = f'''
                create table {table_name_test_snowflake} (
                fixed_value varchar(20),
                latest_test_run date
                );
            '''
        q3 = f'''
                insert into {table_name_test_snowflake}
                values ('djagitit', '1995-04-25');
            '''

        snw = db.SnowflakeDB(account='SME-DELPHI')
        status1 = snw.execute(q1)
        status2 = snw.execute(q2)
        status3 = snw.execute(q3)

        assert status1 and status2 and status3

    @staticmethod
    def test_execute_multi():
        q = f'''
            drop table if exists {table_name_test_snowflake};
            create table {table_name_test_snowflake} (
                fixed_value varchar(20),
                latest_test_run date
            );
            insert into {table_name_test_snowflake}
            values ('djagitit', '1995-04-25');
        '''

        snw = db.SnowflakeDB(account='SME-DELPHI')
        status = snw.execute(q)

        assert status

    @staticmethod
    def test_split_sql():
        q = rf'''
            create or replace table {table_name_test_snowflake} (
                fixed_value varchar(20),
                latest_test_run date
            );
            insert into {table_name_test_snowflake}
            values ('djagitit', '1995-04-25'),
            values ('djagitit;', '1995-04-25'),
            values (";djagitit", '1995-04-25'),
            values ('djagitit\;', '1995-04-25'),
            values ("\;djagitit", '1995-04-25')
            ;
            /*
            this is not valid sql, but should be discarded;
            */
            --this is not valid sql either, but should also be discarded;
            insert into {table_name_test_snowflake}
            values ('mobbdeep', '1995-04-25')
            ;
        '''

        parts = db.split_sql_statements(q)

        assert len(parts) == 3, f'Expected 3 parts, got {len(parts)}'

    @staticmethod
    def test_query_good():

        q = f'''
        select min(fixed_value) as fixed_value
        from {table_name_test_snowflake}
        '''
        snw = db.SnowflakeDB(account='SME-DELPHI')
        data = snw.query(q)

        assert isinstance(data, pd.DataFrame), f'Expected an instance of pd.DataFrame, got type {type(data)}'

    @staticmethod
    def test_query_bad_query(capsys):

        q = f'''
        select madeupfunction(fixed_value) as fixed_value
        from {table_name_test_snowflake}
        '''
        snw = db.SnowflakeDB(account='SME-DELPHI')
        data = snw.query(q)

        captured = capsys.readouterr()
        assert 'bad SQL' in captured.out and not data


    @staticmethod
    def test_write():

        data = {
            'fixed_value': 'djagitit',
            'latest_test_run': [date.today()]
        }

        snw = db.SnowflakeDB(account='SME-DELPHI')
        status = snw.write(pd.DataFrame(data), table_name=table_name_test_snowflake)

        assert status

    @staticmethod
    def test_refresh():

        snw = db.SnowflakeDB(account='SME-DELPHI')
        snw.refresh()
    

    @staticmethod
    def test_drop_table():
        #This final test exists only to clean up the test table after the tests have run.
        q = f'''drop table if exists {table_name_test_snowflake};'''
        
        snw = db.SnowflakeDB(account='SME-DELPHI')
        status = snw.execute(q)
        
        assert status