from __future__ import absolute_import
from flask_sqlalchemy.query import Query as BaseQuery

from dostadmin import db
from sqlalchemy import and_, desc
from utils.helpers.helpers import replace_chars
from dostadmin.mixins import TimestampMixin


class ChurnedUsersQuery(BaseQuery):
    def get_details_by_id(self, churned_user_id):
        return self.filter(ChurnedUsers.id == churned_user_id).first()

    def get_active_churned_user_by_experience_id(self, exp_id):
        return self.filter(
            ChurnedUsers.experience_id == exp_id,
            ChurnedUsers.end_date.is_(None),
        ).first()

    def get_all_active_churned_users_by_experience_type(self, experience_type=None):
        if experience_type is None:
            return self.filter(ChurnedUsers.end_date.is_(None)).all()

        return self.filter(
            ChurnedUsers.end_date.is_(None),
            ChurnedUsers.experience_type == experience_type,
        ).all()


class ChurnedUsers(TimestampMixin, db.Model):
    __tablename__ = "churned_users"
    query_class = ChurnedUsersQuery

    class PreviousStatus:
        ACTIVE = "active"
        PENDING = "pending"
        CHURNED = "churned"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"))
    experience_id = db.Column(db.Integer, db.ForeignKey("experience.id"))
    user_phone = db.Column(db.String(20), nullable=False)
    experience_status = db.Column(db.String(50), unique=False)
    start_date = db.Column(db.Date)
    end_date = db.Column(db.Date, nullable=True)
    experience_type = db.Column(db.String(20))
    program_id = db.Column(db.Integer, db.ForeignKey("program.id"))
    provider_number = db.Column(db.String(20))

    user = db.relationship(
        "User",
        backref="ChurnedUsers",
        primaryjoin="ChurnedUsers.user_id == User.id",
    )
    experience = db.relationship(
        "Experience",
        backref="ChurnedUsers",
        primaryjoin="ChurnedUsers.experience_id == Experience.id",
    )
    program = db.relationship(
        "Program",
        backref="ChurnedUsers",
        primaryjoin="ChurnedUsers.program_id == Program.id",
    )

    def find_churned_user_with_phone(self, phone):
        churned_user = (
            self.filter(
                and_(
                    ChurnedUsers.phone == replace_chars(phone),
                    ChurnedUsers.end_date.is_(None),
                )
            )
            .order_by(desc(ChurnedUsers.start_date))
            .first()
        )
        return churned_user

    @classmethod
    def update_churned_user_end_date(cls, experience, date):
        experience.end_date = date
        db.session.commit()