import datetime
import logging
import warnings

from werkzeug.exceptions import BadRequest

from rdr_service.dao.base_dao import BaseDao
from rdr_service.dao.hpo_dao import HPODao
from rdr_service.dao.metrics_cache_dao import (
    MetricsAgeCacheDao,
    MetricsCacheJobStatusDao,
    MetricsEnrollmentStatusCacheDao,
    MetricsGenderCacheDao,
    MetricsLanguageCacheDao,
    MetricsLifecycleCacheDao,
    MetricsRaceCacheDao,
    MetricsRegionCacheDao,
    MetricsParticipantOriginCacheDao
)
from rdr_service.model.metrics_cache import MetricsCacheJobStatus
from rdr_service.model.participant_summary import ParticipantSummary
from rdr_service.participant_enums import (
    EnrollmentStatus,
    EnrollmentStatusV2,
    MetricsAPIVersion,
    MetricsCacheType,
    Stratifications,
    TEST_EMAIL_PATTERN,
    TEST_HPO_NAME,
    WithdrawalStatus,
    MetricsCronJobStage
)
from rdr_service.dao.metrics_cache_dao import TEMP_TABLE_PREFIX


class ParticipantCountsOverTimeService(BaseDao):
    def __init__(self):
        super(ParticipantCountsOverTimeService, self).__init__(ParticipantSummary, alembic=True)
        self.test_hpo_id = HPODao().get_by_name(TEST_HPO_NAME).hpoId
        self.test_email_pattern = TEST_EMAIL_PATTERN
        self.start_date = datetime.datetime.strptime("2017-05-30", "%Y-%m-%d").date()
        self.end_date = datetime.datetime.now().date() + datetime.timedelta(days=10)
        self.stage_number = MetricsCronJobStage.STAGE_ONE
        self.cronjob_time = datetime.datetime.now().replace(microsecond=0)

    def init_tmp_table(self):
        with self.session() as session:
            hpo_dao = HPODao()
            hpo_list = hpo_dao.get_all()
            for hpo in hpo_list:
                if hpo.hpoId == self.test_hpo_id:
                    continue
                temp_table_name = TEMP_TABLE_PREFIX + str(hpo.hpoId)
                with warnings.catch_warnings():
                    warnings.simplefilter('ignore')
                    session.execute('DROP TABLE IF EXISTS {};'.format(temp_table_name))
                # generated columns can not be inserted any value, need to drop them
                exclude_columns = ['retention_eligible_time', 'retention_eligible_status', 'was_ehr_data_available']
                session.execute('CREATE TABLE {} LIKE participant_summary'.format(temp_table_name))

                indexes_cursor = session.execute('SHOW INDEX FROM {}'.format(temp_table_name))
                for exclude_column_name in exclude_columns:
                    session.execute('ALTER TABLE {} DROP COLUMN  {}'.format(temp_table_name, exclude_column_name))

                index_name_list = []
                for index in indexes_cursor:
                    index_name_list.append(index[2])
                index_name_list = list(set(index_name_list))

                for index_name in index_name_list:
                    if index_name != 'PRIMARY':
                        session.execute('ALTER TABLE {} DROP INDEX  {}'.format(temp_table_name, index_name))

                # The ParticipantSummary table requires these, but there may not be a participant_summary for
                # all participants that we insert
                session.execute('ALTER TABLE {} MODIFY first_name VARCHAR(255)'.format(temp_table_name))
                session.execute('ALTER TABLE {} MODIFY last_name VARCHAR(255)'.format(temp_table_name))
                session.execute('ALTER TABLE {} MODIFY suspension_status SMALLINT'.format(temp_table_name))
                session.execute('ALTER TABLE {} MODIFY participant_origin VARCHAR(80)'.format(temp_table_name))
                session.execute('ALTER TABLE {} MODIFY deceased_status SMALLINT'.format(temp_table_name))
                session.execute('ALTER TABLE {} MODIFY is_ehr_data_available TINYINT(1)'.format(temp_table_name))

                columns_cursor = session.execute('SELECT * FROM {} LIMIT 0'.format(temp_table_name))

                participant_fields = ['participant_id', 'biobank_id', 'sign_up_time', 'withdrawal_status',
                                      'hpo_id', 'organization_id', 'site_id', 'participant_origin']

                def get_field_name(name):
                    if name in participant_fields:
                        return 'p.' + name
                    else:
                        return 'ps.' + name

                columns = map(get_field_name, columns_cursor.keys())
                columns_str = ','.join(columns)

                participant_sql = """
                  INSERT INTO
                  """ + temp_table_name + """
                  SELECT
                  """ + columns_str + """
                  FROM participant p
                  left join participant_summary ps on p.participant_id = ps.participant_id
                  WHERE p.hpo_id <> :test_hpo_id
                  AND p.is_ghost_id IS NOT TRUE
                  AND p.is_test_participant IS NOT TRUE
                  AND (ps.email IS NULL OR NOT ps.email LIKE :test_email_pattern)
                  AND p.withdrawal_status = :not_withdraw
                  AND p.hpo_id = :hpo_id
                """
                params = {'test_hpo_id': self.test_hpo_id, 'test_email_pattern': self.test_email_pattern,
                          'not_withdraw': int(WithdrawalStatus.NOT_WITHDRAWN), 'hpo_id': hpo.hpoId}

                session.execute('CREATE INDEX idx_sign_up_time ON {} (sign_up_time)'.format(temp_table_name))
                session.execute('CREATE INDEX idx_date_of_birth ON {} (date_of_birth)'.format(temp_table_name))
                session.execute('CREATE INDEX idx_consent_time ON {} (consent_for_study_enrollment_time)'
                                .format(temp_table_name))
                session.execute('CREATE INDEX idx_member_time ON {} (enrollment_status_member_time)'
                                .format(temp_table_name))
                session.execute('CREATE INDEX idx_sample_time ON {} (enrollment_status_core_stored_sample_time)'
                                .format(temp_table_name))
                session.execute('CREATE INDEX idx_participant_origin ON {} (participant_origin)'
                                .format(temp_table_name))

                session.execute(participant_sql, params)
                logging.info('crete temp table for hpo_id: ' + str(hpo.hpoId))

            session.execute('DROP TABLE IF EXISTS metrics_tmp_participant_origin;')
            session.execute('CREATE TABLE metrics_tmp_participant_origin (participant_origin VARCHAR(50))')
            participant_origin_sql = """
                INSERT INTO metrics_tmp_participant_origin
                SELECT DISTINCT participant_origin FROM participant
            """
            session.execute(participant_origin_sql)

            logging.info('Init temp table for metrics cron job.')

    def clean_tmp_tables(self):
        with self.session() as session:
            hpo_dao = HPODao()
            hpo_list = hpo_dao.get_all()
            for hpo in hpo_list:
                if hpo.hpoId == self.test_hpo_id:
                    continue
                temp_table_name = TEMP_TABLE_PREFIX + str(hpo.hpoId)
                with warnings.catch_warnings():
                    warnings.simplefilter('ignore')
                    session.execute('DROP TABLE IF EXISTS {};'.format(temp_table_name))

    def refresh_metrics_cache_data(self, start_date, end_date, stage_number):
        self.start_date = start_date
        self.end_date = end_date
        self.stage_number = stage_number

        # For public metrics job, calculate new result for stage one, and copy history result for stage two
        if stage_number == MetricsCronJobStage.STAGE_ONE:
            self.refresh_data_for_metrics_cache(MetricsLifecycleCacheDao(MetricsCacheType.PUBLIC_METRICS_EXPORT_API))
            logging.info("Refresh MetricsLifecycleCache for Public Metrics API done.")
            self.refresh_data_for_metrics_cache(MetricsGenderCacheDao(MetricsCacheType.PUBLIC_METRICS_EXPORT_API))
            logging.info("Refresh MetricsGenderCache for Public Metrics API done.")
            self.refresh_data_for_metrics_cache(MetricsAgeCacheDao(MetricsCacheType.PUBLIC_METRICS_EXPORT_API))
            logging.info("Refresh MetricsAgeCache for Public Metrics API done.")
            self.refresh_data_for_metrics_cache(MetricsRaceCacheDao(MetricsCacheType.PUBLIC_METRICS_EXPORT_API))
            logging.info("Refresh MetricsRaceCache for Public Metrics API done.")
        elif stage_number == MetricsCronJobStage.STAGE_TWO:
            self.refresh_data_for_public_metrics_cache_stage_two(
                MetricsLifecycleCacheDao(MetricsCacheType.PUBLIC_METRICS_EXPORT_API))
            self.refresh_data_for_public_metrics_cache_stage_two(
                MetricsGenderCacheDao(MetricsCacheType.PUBLIC_METRICS_EXPORT_API))
            self.refresh_data_for_public_metrics_cache_stage_two(
                MetricsAgeCacheDao(MetricsCacheType.PUBLIC_METRICS_EXPORT_API))
            self.refresh_data_for_public_metrics_cache_stage_two(
                MetricsRaceCacheDao(MetricsCacheType.PUBLIC_METRICS_EXPORT_API))

        self.refresh_data_for_metrics_cache(MetricsEnrollmentStatusCacheDao())
        logging.info("Refresh MetricsEnrollmentStatusCache done.")
        self.refresh_data_for_metrics_cache(MetricsRegionCacheDao())
        logging.info("Refresh MetricsRegionCache done.")
        self.refresh_data_for_metrics_cache(MetricsLanguageCacheDao())
        logging.info("Refresh MetricsLanguageCache done.")
        self.refresh_data_for_metrics_cache(MetricsGenderCacheDao(MetricsCacheType.METRICS_V2_API))
        logging.info("Refresh MetricsGenderCache for Metrics2API done.")
        self.refresh_data_for_metrics_cache(MetricsRaceCacheDao(MetricsCacheType.METRICS_V2_API))
        logging.info("Refresh MetricsRaceCache for Metrics2API done.")

    def refresh_data_for_metrics_cache(self, dao):
        status_dao = MetricsCacheJobStatusDao()
        if self.stage_number == MetricsCronJobStage.STAGE_ONE:
            kwargs = dict(
                cacheTableName=dao.table_name,
                type=str(dao.cache_type),
                inProgress=True,
                stage_one_complete=False,
                stage_two_complete=False,
                dateInserted=self.cronjob_time,
            )
            job_status_obj = MetricsCacheJobStatus(**kwargs)
            status_dao.insert(job_status_obj)

        hpo_dao = HPODao()
        hpo_list = hpo_dao.get_all()
        for hpo in hpo_list:
            if hpo.hpoId == self.test_hpo_id:
                continue
            self.insert_cache_by_hpo(dao, hpo.hpoId)

        status_dao.set_to_complete(dao.cache_type, dao.table_name, self.cronjob_time, self.stage_number)
        if self.stage_number == MetricsCronJobStage.STAGE_TWO:
            dao.delete_old_records()

    def refresh_data_for_public_metrics_cache_stage_two(self, dao):
        if self.stage_number != MetricsCronJobStage.STAGE_TWO:
            return
        status_dao = MetricsCacheJobStatusDao()
        last_success_stage_two = status_dao.get_last_complete_stage_two_data_inserted_time(dao.table_name,
                                                                                           dao.cache_type)
        if not last_success_stage_two:
            logging.info(f'No last success stage two found for {dao.table_name}, calculate new data for stage two')
            self.refresh_data_for_metrics_cache(dao)
        else:
            dao.update_historical_cache_data(self.cronjob_time, last_success_stage_two.dateInserted,
                                             self.start_date, self.end_date)
            status_dao.set_to_complete(dao.cache_type, dao.table_name, self.cronjob_time, self.stage_number)
            dao.delete_old_records(n_days_ago=30)

    def insert_cache_by_hpo(self, dao, hpo_id):
        sql_arr = dao.get_metrics_cache_sql(hpo_id)

        params = {'hpo_id': hpo_id, 'start_date': self.start_date, 'end_date': self.end_date,
                  'date_inserted': self.cronjob_time}
        with dao.session() as session:
            for sql in sql_arr:
                session.execute(sql, params)

    def get_filtered_results(
        self, stratification, start_date, end_date, history, awardee_ids, enrollment_statuses, sample_time_def,
        participant_origins, version
    ):
        """Queries DB, returns results in format consumed by front-end

    :param start_date: Start date object
    :param end_date: End date object
    :param awardee_ids: indicate awardee ids
    :param enrollment_statuses: indicate the enrollment status
    :param sample_time_def: indicate how to filter the core participant
    :param history: query for history data from metrics cache table
    :param stratification: How to stratify (layer) results, as in a stacked bar chart
    :param version: indicate the version of the result filter
    :param participant_origins: indicate the participant origins