from datetime import datetime, timezone, timedelta
from itertools import product
from app.extensions import db
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import relationship
from dateutil.relativedelta import relativedelta
from datetime import timedelta
import logging
from sqlalchemy import and_, or_, not_
from sqlalchemy.sql import func
import uuid

logger = logging.getLogger(__name__)

class MembersProfile(db.Model):
    __tablename__ = 'members_profile'

    id = db.Column(
        db.Integer,
        primary_key=True,
        autoincrement=True,
    )

    uid = db.Column(db.String(150), nullable=False)
    member_id = db.Column(db.Integer, nullable=False)
    practice = db.Column(db.String(100), nullable=False)
    specialization = db.Column(db.String(100), nullable=False)
    introduction = db.Column(db.String(3500), nullable=True)
    url_name = db.Column(db.String(25), nullable=True)
    added = db.Column(db.DateTime(timezone=True), server_default=func.now())
    updated = db.Column(db.DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    @classmethod
    def get_member_profile_by_member_id(cls, member_id):
        member_profile = cls.query.filter_by(member_id=str(member_id)).first()
        if not member_profile:
            return None
        return member_profile

    @classmethod
    def get_member_profile_by_profile_uid(cls, profile_uid):
        member_profile = cls.query.filter_by(uid=str(profile_uid)).first()
        if not member_profile:
            return None
        return member_profile

    @classmethod
    def get_any_member_profile_by_url_name(cls, url_name):
        member_profile = cls.query.filter_by(url_name=str(url_name)).first()
        if not member_profile:
            return None
        return member_profile


    @classmethod
    def get_member_product_by_product_member_id(cls, member_id, product_id):
        member_product = cls.query.filter_by(member_id=str(member_id), product_id=str(product_id)).first()
        if not member_product:
            return None
        return member_product


    @classmethod
    def create_member_profile(cls, member_id ,practice,specialization,introduction,url_name):
        # Create the subscription
        '''
        merms_panel=# select * from members_profile;
         id | uid | member_id | practice | specialization | introduction | added | updated
        ----+-----+-----------+----------+----------------+--------------+-------+---------
        (0 rows)
        '''

        logger.info(f" Data for  Profile Data {member_id} ,{practice} ,{specialization}, {introduction} {url_name} ")
        profile_data = cls(
            uid=str(uuid.uuid4()),
            member_id=member_id,
            practice=practice,
            specialization=specialization,
            introduction=introduction,
            url_name = url_name,
            added=datetime.now(timezone.utc),
            updated=datetime.now(timezone.utc)
        )
        try:
            logger.info(f" About to Insert Profile Data {profile_data.member_id} ")
            db.session.add(profile_data)
            db.session.flush()
        except IntegrityError as err:
            logger.error(f" Error inserting profile data {err}  -- ")
            raise ValueError(f"Database integrity error: {err}")
        return profile_data

    def to_dict(self):
        """
        Convert the Loan object to a dictionary format for JSON serialization.
        """
        return {
            'id': self.id,
            'uid': self.uid,
            'member_id': self.member_id,
            'practice': self.practice,
            'specialization': self.specialization,
            'introduction': self.dns_group,
            'url_name': self.url_name,
            'added': self.added,
            'updated': self.updated
        }

    def __repr__(self):
        return f'<MembersProfile {self.id}>'