# app/models/project.py

from datetime import datetime
import random
import string
import os
from app.extensions import db




# app/models/project.py

class Project(db.Model):
    """مدل پروژه‌های ساختمانی"""
    __tablename__ = 'projects'
    __table_args__ = {'extend_existing': True}
    
    id = db.Column(db.Integer, primary_key=True)
    project_code = db.Column(db.String(16), unique=True, nullable=False)
    name = db.Column(db.String(100), nullable=False)
    project_type = db.Column(db.String(50), nullable=True)
    description = db.Column(db.Text, nullable=True)
    
    # آدرس و موقعیت
    address = db.Column(db.String(500), nullable=False)
    gps_lat = db.Column(db.Float, nullable=False)
    gps_lon = db.Column(db.Float, nullable=False)
    gps_radius = db.Column(db.Float, default=100)
    gps_bounds = db.Column(db.JSON, nullable=True)
    
    # تاریخ‌ها
    start_date = db.Column(db.Date, nullable=False)
    end_date = db.Column(db.Date, nullable=True)
    
    # وضعیت
    status = db.Column(db.String(20), default='active')
    
    # کلیدهای خارجی
    manager_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    employer_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=True)
    created_by = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=True)
    
    # ======== حذف escrow_account_id از اینجا ========
    # صندوق امانی به صورت مرکزی است و مستقیماً به پروژه متصل نیست
    # escrow_account_id = db.Column(db.Integer, db.ForeignKey('escrow_accounts.id'), nullable=True)  # ← حذف کنید
    
    # زمان‌ها
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # ========== روابط ==========
    
    # رابطه با User (Manager)
    manager = db.relationship(
        'User',
        foreign_keys=[manager_id],
        backref='managed_projects'
    )
    
    # رابطه با User (Employer)
    employer = db.relationship(
        'User',
        foreign_keys=[employer_id],
        backref='employed_projects'
    )
    
    # رابطه با User (Creator)
    creator = db.relationship(
        'User',
        foreign_keys=[created_by],
        backref='creator_projects'
    )
    
    # رابطه با ProjectEmployee
    employees = db.relationship(
        'ProjectEmployee',
        backref='project_employees_ref',
        lazy=True,
        cascade='all, delete-orphan'
    )
    
    # رابطه با Attendance
    attendances = db.relationship(
        'Attendance',
        backref='project_attendances_ref',
        lazy=True
    )
    
    # رابطه با Contract
    contracts = db.relationship(
        'Contract',
        backref='project_contracts_ref',
        lazy=True
    )
    
    # رابطه با Deposit
    deposits = db.relationship(
        'Deposit',
        backref='project_deposits_ref',
        lazy=True
    )
    
    # رابطه با PhotoReport
    photo_reports = db.relationship(
        'PhotoReport',
        backref='project_photo_reports_ref',
        lazy=True
    )
    
    # ======== اصلاح رابطه با EscrowAccount ========
    # از آنجایی که صندوق امانی مرکزی است، این رابطه را حذف یا اصلاح کنید
    # escrow_account = db.relationship(
    #     'EscrowAccount',
    #     backref='project_escrow_ref',
    #     uselist=False,
    #     lazy=True
    # )  # ← این را حذف کنید یا نظر کنید
    
    # رابطه با Report
    reports = db.relationship(
        'Report',
        backref='project_reports_ref',
        lazy=True
    )
    
    # رابطه با SystemCommission
    commissions = db.relationship(
        'SystemCommission',
        backref='project_commissions_ref',
        lazy=True
    )
    
    # رابطه با SmartReport
    smart_reports = db.relationship(
        'SmartReport',
        backref='project_smart_reports_ref',
        lazy=True
    )
    
    # رابطه با ProjectDocument
    documents = db.relationship(
        'ProjectDocument',
        backref='project_documents_ref',
        lazy=True,
        cascade='all, delete-orphan'
    )
    
    # ========== Propertyها ==========
    
    @property
    def escrow_account(self):
        """دریافت صندوق امانی مرکزی"""
        from app.models import EscrowAccount
        return EscrowAccount.query.first()  # صندوق امانی مرکزی
    
    @property
    def project_check_books(self):
        """دریافت تمام دفترچه چک‌های مرتبط با پروژه"""
        from app.models import BankCheckBook
        escrow = self.escrow_account
        if escrow and escrow.system_account:
            return BankCheckBook.query.filter_by(
                system_account_id=escrow.system_account_id
            ).all()
        return []
    
    @property
    def project_check_books_count(self):
        return len(self.project_check_books)
    
    @property
    def project_available_checks(self):
        from app.models import BankCheck
        escrow = self.escrow_account
        if escrow and escrow.system_account:
            return BankCheck.query.join(BankCheckBook).filter(
                BankCheckBook.system_account_id == escrow.system_account_id,
                BankCheck.status == 'available'
            ).all()
        return []
    
    @property
    def project_used_checks(self):
        from app.models import BankCheck
        escrow = self.escrow_account
        if escrow and escrow.system_account:
            return BankCheck.query.join(BankCheckBook).filter(
                BankCheckBook.system_account_id == escrow.system_account_id,
                BankCheck.status.in_(['allocated', 'used'])
            ).all()
        return []
    
    @property
    def project_total_checks(self):
        from app.models import BankCheck
        escrow = self.escrow_account
        if escrow and escrow.system_account:
            return BankCheck.query.join(BankCheckBook).filter(
                BankCheckBook.system_account_id == escrow.system_account_id
            ).count()
        return 0
    
    @property
    def project_payment_requests(self):
        from app.models import PaymentRequest
        return PaymentRequest.query.join(Contract).filter(Contract.project_id == self.id).all()
    
    @property
    def project_payment_requests_count(self):
        from app.models import PaymentRequest
        return PaymentRequest.query.join(Contract).filter(Contract.project_id == self.id).count()
    
    @property
    def project_pending_payment_requests(self):
        from app.models import PaymentRequest
        return PaymentRequest.query.join(Contract).filter(
            Contract.project_id == self.id,
            PaymentRequest.status == 'pending'
        ).all()
    
    @property
    def project_approved_payment_requests(self):
        from app.models import PaymentRequest
        return PaymentRequest.query.join(Contract).filter(
            Contract.project_id == self.id,
            PaymentRequest.status == 'approved'
        ).all()
    
    @property
    def project_paid_payment_requests(self):
        from app.models import PaymentRequest
        return PaymentRequest.query.join(Contract).filter(
            Contract.project_id == self.id,
            PaymentRequest.status == 'paid'
        ).all()
    
    @property
    def project_total_payment_requests_amount(self):
        from app.models import PaymentRequest
        total = PaymentRequest.query.join(Contract).filter(
            Contract.project_id == self.id,
            PaymentRequest.status.in_(['approved', 'paid'])
        ).with_entities(db.func.sum(PaymentRequest.amount)).scalar()
        return total or 0
    
    # ========== متدها ==========
    
    def get_project_type_display(self):
        """دریافت نمایش فارسی نوع پروژه"""
        type_map = {
            'construction': 'ساختمانی',
            'road': 'راه‌سازی',
            'bridge': 'پل‌سازی',
            'tunnel': 'تونل‌سازی',
            'dam': 'سدسازی',
            'industrial': 'صنعتی',
            'agricultural': 'کشاورزی',
            'service': 'خدماتی',
            'other': 'سایر'
        }
        return type_map.get(self.project_type, self.project_type)
    
    def get_status_display(self):
        """دریافت نمایش فارسی وضعیت پروژه"""
        status_map = {
            'draft': 'پیش‌نویس',
            'employer_info_added': 'اطلاعات کارفرما ثبت شد',
            'employees_added': 'عوامل ثبت شدند',
            'active': 'فعال',
            'suspended': 'متوقف',
            'completed': 'تکمیل شده'
        }
        return status_map.get(self.status, self.status)
    
    def get_project_type_class(self):
        """دریافت کلاس CSS برای نوع پروژه"""
        type_class_map = {
            'construction': 'primary',
            'road': 'success',
            'bridge': 'info',
            'tunnel': 'warning',
            'dam': 'danger',
            'industrial': 'secondary',
            'agricultural': 'success',
            'service': 'info',
            'other': 'secondary'
        }
        return type_class_map.get(self.project_type, 'secondary')
    

    def generate_project_code(self):
        while True:
            code = ''.join(random.choices(string.digits, k=16))
            if not Project.query.filter_by(project_code=code).first():
                self.project_code = code
                return code
    
    def get_employee_count(self):
        return ProjectEmployee.query.filter_by(project_id=self.id, is_active=True).count()
    
    def get_today_attendance_count(self):
        from app.models import Attendance
        today = datetime.now().date()
        return Attendance.query.filter(
            Attendance.project_id == self.id,
            db.func.date(Attendance.check_in_time) == today
        ).count()
    
    def get_total_contracts_value(self):
        from app.models import Contract
        total = db.session.query(db.func.sum(Contract.total_amount)).filter(
            Contract.project_id == self.id,
            Contract.is_active == True
        ).scalar()
        return total or 0
    
    def get_total_deposits(self):
        from app.models import Deposit
        total = db.session.query(db.func.sum(Deposit.amount)).filter(
            Deposit.project_id == self.id,
            Deposit.status == 'verified'
        ).scalar()
        return total or 0
    
    def get_escrow_balance(self):
        """دریافت موجودی صندوق امانی مرکزی"""
        escrow = self.escrow_account
        if escrow:
            return escrow.balance
        return 0
    
    def get_project_escrow_balance(self):
        """دریافت موجودی اختصاصی پروژه در صندوق امانی مرکزی"""
        escrow = self.escrow_account
        if escrow:
            return escrow.get_project_balance(self.id)
        return 0
    
    def get_status_display(self):
        status_map = {
            'active': 'فعال',
            'completed': 'تکمیل شده',
            'suspended': 'متوقف',
            'cancelled': 'لغو شده'
        }
        return status_map.get(self.status, self.status)
    
    def to_dict(self):
        return {
            'id': self.id,
            'project_code': self.project_code,
            'name': self.name,
            'project_type': self.project_type,
            'description': self.description,
            'address': self.address,
            'gps_lat': self.gps_lat,
            'gps_lon': self.gps_lon,
            'gps_radius': self.gps_radius,
            'start_date': self.start_date.strftime('%Y-%m-%d'),
            'end_date': self.end_date.strftime('%Y-%m-%d') if self.end_date else None,
            'status': self.status,
            'status_display': self.get_status_display(),
            'manager_id': self.manager_id,
            'manager_name': self.manager.fullname if self.manager else None,
            'employer_id': self.employer_id,
            'employer_name': self.employer.fullname if self.employer else None,
            'employee_count': self.get_employee_count(),
            'today_attendance': self.get_today_attendance_count(),
            'total_contracts_value': self.get_total_contracts_value(),
            'total_deposits': self.get_total_deposits(),
            'escrow_balance': self.get_escrow_balance(),
            'project_escrow_balance': self.get_project_escrow_balance(),
            'payment_requests_count': self.project_payment_requests_count,
            'total_payment_requests_amount': self.project_total_payment_requests_amount,
            'check_books_count': self.project_check_books_count,
            'available_checks': len(self.project_available_checks),
            'used_checks': len(self.project_used_checks),
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S')
        }
    
    def __repr__(self):
        return f'<Project {self.name} - {self.project_code} - {self.status}>'



class ProjectEmployee(db.Model):
    """مدل ارتباط پروژه و عوامل اجرایی"""
    __tablename__ = 'project_employees'
    __table_args__ = {'extend_existing': True}
    
    id = db.Column(db.Integer, primary_key=True)
    project_id = db.Column(db.Integer, db.ForeignKey('projects.id'), nullable=False)
    employee_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    
    role_type = db.Column(db.String(50), nullable=False)
    job_title = db.Column(db.String(100), nullable=True)
    
    is_active = db.Column(db.Boolean, default=True)
    join_date = db.Column(db.Date, nullable=True)
    leave_date = db.Column(db.Date, nullable=True)
    
    phone_verified = db.Column(db.Boolean, default=False)
    face_verified = db.Column(db.Boolean, default=False)
    gps_verified = db.Column(db.Boolean, default=False)
    
    notes = db.Column(db.Text, nullable=True)
    
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    created_by = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=True)
    
    # ========== روابط با backrefهای یکتا ==========
    
    # رابطه با Project - تغییر backref
    project = db.relationship(
        'Project',
        foreign_keys=[project_id],
        backref='project_employees_list'  # تغییر نام
    )
    
    # رابطه با User (Employee)
    employee = db.relationship(
        'User',
        foreign_keys=[employee_id],
        backref='employee_projects_list'  # تغییر نام
    )
    
    # رابطه با User (Creator)
    creator = db.relationship(
        'User',
        foreign_keys=[created_by],
        backref='created_employee_records_list'  # تغییر نام
    )
    
    # رابطه با Contract
    contracts = db.relationship(
        'Contract',
        backref='employee_contracts_list',  # تغییر نام
        lazy=True
    )
    
    # ========== متدها ==========
    
    def get_role_display(self):
        role_map = {
            'مهندس مجری': 'مهندس مجری',
            'مهندس ناظر': 'مهندس ناظر',
            'تکنسین': 'تکنسین',
            'کارگر ماهر': 'کارگر ماهر',
            'کارگر ساده': 'کارگر ساده',
            'راننده': 'راننده'
        }
        return role_map.get(self.role_type, self.role_type)
    
    def is_active_employee(self):
        return self.is_active
    
    def to_dict(self):
        return {
            'id': self.id,
            'project_id': self.project_id,
            'project_name': self.project.name if self.project else None,
            'employee_id': self.employee_id,
            'employee_name': self.employee.fullname if self.employee else None,
            'employee_phone': self.employee.phone if self.employee else None,
            'role_type': self.role_type,
            'role_display': self.get_role_display(),
            'job_title': self.job_title,
            'is_active': self.is_active,
            'join_date': self.join_date.strftime('%Y-%m-%d') if self.join_date else None,
            'leave_date': self.leave_date.strftime('%Y-%m-%d') if self.leave_date else None,
            'phone_verified': self.phone_verified,
            'face_verified': self.face_verified,
            'gps_verified': self.gps_verified,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S')
        }
    
    def __repr__(self):
        return f'<ProjectEmployee Project:{self.project_id} Employee:{self.employee_id} - {self.role_type}>'


class ProjectDocument(db.Model):
    """مدل اسناد و مدارک پروژه"""
    __tablename__ = 'project_documents'
    __table_args__ = {'extend_existing': True}
    
    id = db.Column(db.Integer, primary_key=True)
    project_id = db.Column(db.Integer, db.ForeignKey('projects.id'), nullable=False)
    
    title = db.Column(db.String(200), nullable=False)
    description = db.Column(db.Text, nullable=True)
    file_path = db.Column(db.String(500), nullable=False)
    file_type = db.Column(db.String(50), nullable=True)
    file_size = db.Column(db.Integer, nullable=True)
    
    category = db.Column(db.String(50), nullable=True)
    
    is_approved = db.Column(db.Boolean, default=False)
    approved_by = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=True)
    approved_at = db.Column(db.DateTime, nullable=True)
    
    version = db.Column(db.Integer, default=1)
    parent_document_id = db.Column(db.Integer, db.ForeignKey('project_documents.id'), nullable=True)
    
    extra_metadata = db.Column(db.JSON, nullable=True)
    
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    created_by = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=True)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # ========== روابط با backrefهای یکتا ==========
    
    # رابطه با Project
    project = db.relationship(
        'Project',
        foreign_keys=[project_id],
        backref='project_documents_list'  # تغییر نام
    )
    
    # رابطه با User (Approver)
    approver = db.relationship(
        'User',
        foreign_keys=[approved_by],
        backref='approved_documents_list'  # تغییر نام
    )
    
    # رابطه با User (Creator)
    creator = db.relationship(
        'User',
        foreign_keys=[created_by],
        backref='created_documents_list'  # تغییر نام
    )
    
    # رابطه برای نسخه‌بندی (خودارجاعی)
    parent_document = db.relationship(
        'ProjectDocument',
        remote_side=[id],
        backref='document_versions_list',  # تغییر نام
        foreign_keys=[parent_document_id]
    )
    
    # ========== متدها ==========
    
    @property
    def file_name(self):
        if self.file_path:
            return os.path.basename(self.file_path)
        return None
    
    @property
    def file_extension(self):
        if self.file_path:
            return self.file_path.split('.')[-1].lower() if '.' in self.file_path else None
        return None
    
    @property
    def is_image(self):
        image_extensions = ['jpg', 'jpeg', 'png', 'gif', 'bmp', 'webp', 'svg']
        return self.file_extension in image_extensions
    
    @property
    def is_pdf(self):
        return self.file_extension == 'pdf'
    
    @property
    def is_document(self):
        doc_extensions = ['doc', 'docx', 'txt', 'rtf', 'odt']
        return self.file_extension in doc_extensions
    
    @property
    def is_spreadsheet(self):
        sheet_extensions = ['xls', 'xlsx', 'csv', 'ods']
        return self.file_extension in sheet_extensions
    
    def get_category_display(self):
        category_map = {
            'contract': 'قرارداد',
            'report': 'گزارش',
            'photo': 'تصویر',
            'invoice': 'فاکتور',
            'certificate': 'مدارک',
            'other': 'سایر'
        }
        return category_map.get(self.category, self.category)
    
    def get_file_type_display(self):
        type_map = {
            'pdf': 'PDF',
            'jpg': 'تصویر',
            'jpeg': 'تصویر',
            'png': 'تصویر',
            'gif': 'تصویر',
            'doc': 'سند ورد',
            'docx': 'سند ورد',
            'xls': 'اکسل',
            'xlsx': 'اکسل',
            'txt': 'متن ساده'
        }
        return type_map.get(self.file_type, self.file_type)
    
    def get_file_size_display(self):
        if not self.file_size:
            return 'نامشخص'
        
        size = self.file_size
        for unit in ['بایت', 'KB', 'MB', 'GB']:
            if size < 1024.0 or unit == 'GB':
                return f"{size:.1f} {unit}"
            size /= 1024.0
        return f"{size:.1f} GB"
    
    def approve(self, user_id):
        self.is_approved = True
        self.approved_by = user_id
        self.approved_at = datetime.utcnow()
    
    def reject(self):
        self.is_approved = False
        self.approved_by = None
        self.approved_at = None
    
    def create_new_version(self, new_file_path, user_id, description=None):
        new_version = ProjectDocument(
            project_id=self.project_id,
            title=self.title,
            description=description or self.description,
            file_path=new_file_path,
            file_type=self.file_type,
            file_size=self.file_size,
            category=self.category,
            is_approved=False,
            parent_document_id=self.id,
            version=self.version + 1,
            created_by=user_id
        )
        return new_version
    
    def get_version_history(self):
        versions = [self]
        current = self
        while current.parent_document:
            current = current.parent_document
            versions.append(current)
        return versions[::-1]
    
    def is_editable_by_user(self, user_id):
        if not self.is_approved:
            return self.created_by == user_id
        return False
    
    def to_dict(self):
        return {
            'id': self.id,
            'project_id': self.project_id,
            'project_name': self.project.name if self.project else None,
            'title': self.title,
            'description': self.description,
            'file_path': self.file_path,
            'file_name': self.file_name,
            'file_type': self.file_type,
            'file_type_display': self.get_file_type_display(),
            'file_size': self.file_size,
            'file_size_display': self.get_file_size_display(),
            'category': self.category,
            'category_display': self.get_category_display(),
            'is_approved': self.is_approved,
            'is_image': self.is_image,
            'is_pdf': self.is_pdf,
            'version': self.version,
            'parent_document_id': self.parent_document_id,
            'extra_metadata': self.extra_metadata,
            'approved_by': self.approved_by,
            'approver_name': self.approver.fullname if self.approver else None,
            'approved_at': self.approved_at.strftime('%Y-%m-%d %H:%M:%S') if self.approved_at else None,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S'),
            'created_by': self.created_by,
            'creator_name': self.creator.fullname if self.creator else None,
            'updated_at': self.updated_at.strftime('%Y-%m-%d %H:%M:%S') if self.updated_at else None
        }
    
    def __repr__(self):
        return f'<ProjectDocument {self.id} - {self.title}>'