from dostadmin.db_model import CustomizedExperience, Experience, ChurnedUsers
from utils.helpers.helpers import get_current_isttime
from dostadmin import db_model, app, db


class ExperienceService:
    def get_active_experiences(self, cursor):
        SQL = (
            "SELECT ex.id, "
            "ex.user_id, "
            "ex.program_id, "
            "ex.start_date, "
            "tc.time, "
            "ex.timecategory_id, "
            "ex.provider_number, "
            "ex.type, "
            "ex.status, "
            "ex.phone, "
            "ex.experiment_id, "
            "ex.experiment_group_name, "
            "ex.created_on "
            "FROM experience ex "
            "INNER JOIN timecategory tc ON ex.timecategory_id = tc.id "
            "WHERE ex.status in ('active', 'pending') AND "
            "ex.type in ('pc', 'selfpacedpc')"
        )
        cursor.execute(SQL)
        active_experiences = []

        for exp in cursor.fetchall():
            exp_obj = CustomizedExperience(
                {
                    "exp_id": exp[0],
                    "user_id": exp[1],
                    "program_id": exp[2],
                    "start_date": exp[3],
                    "time": exp[4],
                    "prev_content_id": None,
                    "prev_content_version_id": None,
                    "prev_deploy_datetime": None,
                    "timecategory_id": exp[5],
                    "provider_number": exp[6],
                    "experience_type": exp[7],
                    "status": exp[8],
                    "phone": exp[9],
                    "experiment_id": exp[10],
                    "experiment_group_name": exp[11],
                    "experience_created_on": exp[12],
                }
            )
            active_experiences.append(exp_obj)

        return active_experiences

    def check_for_existing_experience(self, user_number):
        user_phone = user_number[-10:]

        experience = Experience.query.find_experience_with_phone_status(
            user_phone,
            [
                Experience.Status.ACTIVE,
                Experience.Status.PENDING,
            ],
        )

        if experience:
            return experience

        experience = Experience.query.find_experience_with_phone_status(
            user_phone,
            [
                Experience.Status.DND_WAIT,
            ],
        )

        if experience:
            return experience

        experience = Experience.query.find_experience_with_phone_status(
            user_phone, [Experience.Status.CHURNED], None, "desc"
        )

        if experience:
            churned_user = ChurnedUsers.query.get_active_churned_user_by_experience_id(
                experience.id
            )
            if churned_user:
                return experience

        experience = Experience.query.find_experience_with_phone(
            user_phone,
            [
                Experience.Type.PHONECAST,
                Experience.Type.WHATSAPP,
                Experience.Type.SELFPACEDPC,
            ],
        )

        return experience

    def create_new_experience_from_previous_experience(
        self, previous_experience, new_program_id
    ):
        experience = Experience(
            user_id=previous_experience.user_id,
            phone=previous_experience.phone,
            language_id=previous_experience.language_id,
            timecategory_id=previous_experience.timecategory_id,
            program_id=new_program_id,
            start_date=get_current_isttime().date(),
            status=Experience.Status.ACTIVE,
            provider_number=previous_experience.provider_number,
            type=previous_experience.type,
        )
        db.session.add(experience)
        db.session.commit()

        return experience

    def create_new_experience_for_DND_users(self, experience):
        default_missedcall_program_id = (
            db_model.Program.query.find_program_id_with_name(
                app.config["DEFAULT_MISSED_CALL_PROGRAM_NAME"]
            )
        )
        new_experience = self.create_new_experience_from_previous_experience(
            experience, default_missedcall_program_id
        )
        return new_experience