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

from app.api.types.data_types import MemberFileData
import uuid

logger = logging.getLogger(__name__)


class MembersWebfiles(db.Model):
    __tablename__ = 'members_webfiles'

    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)
    member_uid = db.Column(db.String(100), nullable=False)
    file_group = db.Column(db.String(25), nullable=False)
    filename = db.Column(db.String(100), nullable=False)
    save_filename = db.Column(db.String(100), nullable=False)
    file_type = db.Column(db.String(50), nullable=False)
    status = db.Column(db.Integer, nullable=True, default=1)
    file_size = db.Column(db.Integer, nullable=True, default=0)
    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 create_file_profile(cls, file_data: MemberFileData):
        logger.info(
            f"File Data {file_data.member_id} {file_data.member_uid} {file_data.file_group} {file_data.filename} {file_data.save_filename} {file_data.file_type} {file_data.file_size} ")
        file_uid = str(uuid.uuid4())
        file_save_data = cls(
            uid=str(file_uid),
            member_id=file_data.member_id,
            member_uid=file_data.member_uid,
            file_group=file_data.file_group,
            filename=file_data.filename,
            save_filename=file_data.save_filename,
            file_type=file_data.file_type,
            file_size=file_data.file_size,
            added=datetime.now(timezone.utc),
            updated=datetime.now(timezone.utc)
        )

        try:
            logger.info(f"About to Insert Members File Data {file_data.member_id} ")
            db.session.add(file_save_data)
            db.session.commit()
            # db.session.flush()
        except IntegrityError as err:
            logger.error(f" Error inserting file_save_data data {err}  -- ")
            raise ValueError(f"Database integrity error: {err}")

        save_result = {
            'file_uid': file_uid
        }
        return save_result, file_uid

    @classmethod
    def get_member_webfiles_by_member_id(cls, member_id):
        member_webfile = cls.query.filter_by(member_id=str(member_id), file_group='WEBSITE').order_by(
            cls.added.desc()).all()

        if not member_webfile:
            return None
        return member_webfile

    @classmethod
    def get_member_webfiles_by_member_file_uid(cls, member_id, file_uid):
        member_webfile = cls.query.filter_by(member_id=str(member_id), uid=str(file_uid)).first()

        if not member_webfile:
            return None
        return member_webfile

    @classmethod
    def get_member_webfiles_by_file_uid(cls, file_uid):
        member_webfile = cls.query.filter_by(uid=str(file_uid)).first()

        if not member_webfile:
            return None
        return member_webfile

    # @classmethod
    # def get_member_webfiles_by_member_file_group(cls, member_id , file_group):
    #     member_webfile = cls.query.filter_by(member_id=str(member_id), web_files_list=str(file_group)).all()
    #
    #     if not member_webfile:
    #         return None
    #     return member_webfile

    @classmethod
    def get_all_webfiles(cls, file_group=None, member_id=None, page=1, limit=20):

        query = cls.query
        logger.info(f"Get all payments back")

        if member_id:
            query = query.filter(cls.member_id == member_id)

        if file_group:
            query = query.filter(cls.file_group == file_group)

        # Order by created_at descending (newest first)
        query = query.order_by(cls.added.desc())

        # Get total count before pagination
        total_count = query.count()

        # Apply pagination
        offset = (page - 1) * limit
        query = query.limit(limit).offset(offset)

        return query.all(), total_count

    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,
            'member_uid': self.member_uid,
            'file_group': self.file_group,
            'filename': self.filename,
            'save_filename': self.save_filename,
            'file_type': self.file_type,
            'status': self.status,
            'file_size': self.file_size,
            'added': self.added,
            'updated': self.updated
        }

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