from dostadmin import db
from sqlalchemy import and_, func
from dostadmin.mixins import TimestampMixin
from flask_sqlalchemy.query import Query as BaseQuery


class DistrictBlockMappingQuery(BaseQuery):
    def get_by_district_and_keypress(self, district_name, keypress):
        return self.filter(
            and_(
                func.lower(DistrictBlockMapping.district) == func.lower(district_name),
                DistrictBlockMapping.keypress == keypress,
            )
        ).first()

    def get_by_state_and_keypress(self, state_name, keypress):
        return self.filter(
            and_(
                func.lower(DistrictBlockMapping.state) == func.lower(state_name),
                DistrictBlockMapping.keypress == keypress,
            )
        ).first()

    def get_all_hindi_block_names(self):
        result = (
            self.with_entities(DistrictBlockMapping.hindi_block)
            .order_by(DistrictBlockMapping.hindi_block)
            .distinct()
            .all()
        )
        return [block[0] for block in result]

    def get_by_block_name(self, block_name):
        return self.filter(DistrictBlockMapping.block == block_name).first()

    def get_block_by_hindi_name(self, hindi_block_name):
        return self.filter(
            func.lower(DistrictBlockMapping.hindi_block) == func.lower(hindi_block_name)
        ).first()


class DistrictBlockMapping(TimestampMixin, db.Model):
    __tablename__ = "district_block_mapping"
    query_class = DistrictBlockMappingQuery

    id = db.Column(db.Integer, primary_key=True)
    state = db.Column(db.String(50))
    district = db.Column(db.String(50), unique=False, nullable=False)
    block = db.Column(db.String(50), unique=True, nullable=False)
    hindi_block = db.Column(db.String(100), unique=True, nullable=True)
    keypress = db.Column(db.Integer)