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



user = db.safely_getenv('usernameReportingDB')
table_name_test_1 = f'sandbox.{user}_djagitit_test_db_1'
table_name_test_2 = f'sandbox.{user}_djagitit_test_db_2'
table_name_test_3 = f'sandbox.{user}_djagitit_test_db_3'
table_name_test_4 = f'sandbox.{user}_djagitit_test_db_4'


class TestBaseClass:

    @staticmethod
    def test_init():
        '''Make sure that base class Dbase is non-instantiable'''

        try:
            baseclassinstance = db.Dbase()
            init_status = True
        except:
            init_status = False

        assert not init_status


class TestDelphi:

    @staticmethod
    def test_init():
        dlp = db.DelphiDB(schema='CHARTMETRIC')
        with pytest.raises(ValueError):
            dlp = db.DelphiDB(schema='INFAMOUS')

    @staticmethod
    def test_query():

        q = f'''select count(*) as n from spotify'''
        dlp = db.DelphiDB(schema='CHARTMETRIC')
        data = dlp.query(q)

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

class TestRDB:

    @staticmethod
    def test_init():
        rdb = db.ReportingDB()

    @staticmethod
    def test_execute():
        q = f'''
            drop table if exists {table_name_test_1};
            drop table if exists {table_name_test_2};
            drop table if exists {table_name_test_3};
            create table if not exists {table_name_test_1} (
                fixed_value varchar(20) encode zstd,
                latest_test_run date encode zstd
                );
            insert into {table_name_test_1}
            values ('djagitit', '1995-04-25');
            create table {table_name_test_2}
            as select * from {table_name_test_1};
            create table {table_name_test_3}
            as select * from {table_name_test_1};
            delete from {table_name_test_2};
            delete from {table_name_test_3};
            '''

        rdb = db.ReportingDB()
        status = rdb.execute(q)

        assert status


    @staticmethod
    def test_query():

        q = f'''
        select min(fixed_value) as fixed_value
        from {table_name_test_1}
        '''
        rdb = db.ReportingDB()
        data = rdb.query(q)

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


    @staticmethod
    def test_write():

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

        rdb = db.ReportingDB()
        status = rdb.write(pd.DataFrame(data), table_name=table_name_test_2)

        assert status

    @staticmethod
    def test_refresh():

        rdb = db.ReportingDB()
        rdb.refresh()


class TestRDBS:

    @staticmethod
    def test_init():
        rdb = db.ReportingDBSession()

    @staticmethod
    def test_execute_valid():

        q1 = f'''
            drop table if exists {table_name_test_4};
            create table if not exists {table_name_test_4} (
                fixed_value varchar(20) encode zstd,
                music_changed_on date encode zstd
            );
        '''

        q2 = f'''
            insert into {table_name_test_4}
            values ('djagitit', '1995-04-25');
        '''

        rdbs = db.ReportingDBSession()
        rdbs.execute(q1)
        rdbs.execute(q2)
        rdbs.commit()
        rdbs.close()

        rdb = db.ReportingDB()
        data = rdb.query(f"select * from {table_name_test_4}")
        assert isinstance(data, pd.DataFrame)
        assert len(data) != 0

    @staticmethod
    def test_execute_invalid():

        q1 = f'''
            drop table if exists {table_name_test_4};
        '''

        q2 = f'''
            insert into sandbox.nonexistent_table
            values ('djagitit', '1995-04-25');
        '''

        rdbs = db.ReportingDBSession()
        with pytest.raises(db.RDBSError):
            rdbs.execute(q1)
            rdbs.execute(q2)
            rdbs.commit()
        # session.rollback is automatically invoked when RDBSError is raised
        rdbs.close()

        rdb = db.ReportingDB()
        data = rdb.query(f"select * from {table_name_test_4}")
        assert isinstance(data, pd.DataFrame)
        assert len(data) != 0


class TestS3:

    @staticmethod
    def test_init():
        s3 = db.S3()

    @staticmethod
    def test_write():

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

        s3 = db.S3()

        status = s3.write_df_to_reportingDB(df=pd.DataFrame(data), tablename=table_name_test_2)
        assert status, 'Error writing to reportingDB from DataFrame'

        status = s3.write_df_to_s3(df=pd.DataFrame(data), s3_path='test_djagitit/test2', format='csv', csv_delimiter='|')
        assert status, 'Error writing to s3 from DataFrame'

        status = s3.write_reportingDB_to_s3(tablename=table_name_test_2, s3_path='test_djagitit/test3')
        assert status, 'Error writing to s3 from reportingDB'

        status = s3.write_reportingDB_to_s3(tablename=table_name_test_2, s3_path='test_djagitit/test3', overwrite=True)
        assert status, 'Error overwriting to s3 from reportingDB'

        status = s3.write_s3_to_reportingDB(s3_path='test_djagitit/test3', tablename=table_name_test_3)
        assert status, 'Error writing to reportingDB from S3'

    @staticmethod
    def test_filelist():

        s3 = db.S3()

        result = s3.file_list('test_djagitit')

        assert len(result) > 0, 'Error retrieving file list from s3'

    @staticmethod
    def test_file_upload():

        localfile = os.path.join(os.path.dirname(__file__), '..', '..', 'sample.env')
        s3 = db.S3()

        s3file = 'test_djagitit/test4.env'
        s3.upload_file(local_path=localfile, s3_path=s3file)
        result = s3.file_list(s3file)

        assert len(result)==1, 'Error uploading file to s3'


    @staticmethod
    def test_download():

        s3 = db.S3()

        s3file = 'test_djagitit/test4.env'
        result = s3.download_files(s3file, '.')
        os.remove(os.path.join('.', 'test_djagitit_test4.env'))
        assert len(result)==1, 'Error downloading file from s3'


    @staticmethod
    def test_delete():

        s3 = db.S3()

        result = s3.delete_files('test_djagitit')
        assert len(result) > 0, 'Error deleting file from s3'

        result = s3.file_list('test_djagitit')
        assert len(result)==0, 'Not all files where deleted from s3'