from app import db
from flask_login import UserMixin
from datetime import datetime
from sqlalchemy import func


class Currency(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True, nullable=False)
    code = db.Column(db.String(3), unique=True, nullable=False)
    symbol = db.Column(db.String(10), nullable=False)
    is_default = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # Relationships
    contributions = db.relationship('Contribution', backref='currency_ref', lazy=True)
    big_collections = db.relationship('BigCollectionEvent', backref='currency_ref', lazy=True)


class Group(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128), nullable=False)
    description = db.Column(db.Text)
    group_code = db.Column(db.String(20), unique=True, nullable=False)  # Unique group identifier
    currency_id = db.Column(db.Integer, db.ForeignKey('currency.id'), nullable=False)
    is_active = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    created_by = db.Column(db.Integer, db.ForeignKey('member.id'), nullable=False)
    
    # Relationships
    members = db.relationship('Member', backref='group', lazy=True, foreign_keys='Member.group_id')
    contributions = db.relationship('Contribution', backref='group', lazy=True)
    events = db.relationship('BigCollectionEvent', backref='group', lazy=True)
    creator = db.relationship('Member', foreign_keys=[created_by])


class Member(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    custom_id = db.Column(db.String(20), nullable=True)  # Custom member ID (not unique globally)
    username = db.Column(db.String(64), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    # ensure password hash field has length of at least 256
    password_hash = db.Column(db.String(256))
    full_name = db.Column(db.String(128), nullable=False)
    phone_number = db.Column(db.String(20))
    group_id = db.Column(db.Integer, db.ForeignKey('group.id'), nullable=True)  # Group membership
    is_admin = db.Column(db.Boolean, default=False)
    is_superadmin = db.Column(db.Boolean, default=False)
    is_group_admin = db.Column(db.Boolean, default=False)  # Admin within their group
    is_active = db.Column(db.Boolean, default=True)
    join_date = db.Column(db.DateTime, default=datetime.utcnow)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # Relationships
    contributions = db.relationship('Contribution', foreign_keys='Contribution.member_id', backref='member', lazy=True)
    big_collection_participations = db.relationship('BigCollectionParticipation', backref='member', lazy=True)
    recorded_contributions = db.relationship('Contribution', foreign_keys='Contribution.recorded_by', backref='recorder', lazy=True)
    created_events = db.relationship('BigCollectionEvent', foreign_keys='BigCollectionEvent.created_by', backref='creator', lazy=True)


class Contribution(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    member_id = db.Column(db.Integer, db.ForeignKey('member.id'), nullable=False)
    group_id = db.Column(db.Integer, db.ForeignKey('group.id'), nullable=False)
    currency_id = db.Column(db.Integer, db.ForeignKey('currency.id'), nullable=False)
    amount = db.Column(db.Numeric(10, 2), nullable=False)
    contribution_type = db.Column(db.String(20), nullable=False)  # 'monthly', 'penalty', 'bonus'
    contribution_month = db.Column(db.Date, nullable=True)  # For monthly contributions
    description = db.Column(db.Text)
    status = db.Column(db.String(20), default='confirmed')  # 'pending', 'confirmed', 'cancelled'
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    recorded_by = db.Column(db.Integer, db.ForeignKey('member.id'))
    



class BigCollectionEvent(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128), nullable=False)
    description = db.Column(db.Text)
    group_id = db.Column(db.Integer, db.ForeignKey('group.id'), nullable=False)
    target_amount = db.Column(db.Numeric(12, 2))
    currency_id = db.Column(db.Integer, db.ForeignKey('currency.id'), nullable=False)
    start_date = db.Column(db.Date, nullable=False)
    end_date = db.Column(db.Date, nullable=False)
    is_active = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    created_by = db.Column(db.Integer, db.ForeignKey('member.id'), nullable=False)
    
    # Relationships
    participations = db.relationship('BigCollectionParticipation', backref='event', lazy=True)


class BigCollectionParticipation(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    event_id = db.Column(db.Integer, db.ForeignKey('big_collection_event.id'), nullable=False)
    member_id = db.Column(db.Integer, db.ForeignKey('member.id'), nullable=False)
    amount_contributed = db.Column(db.Numeric(10, 2), default=0)
    last_contribution_date = db.Column(db.DateTime)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # Unique constraint to prevent duplicate participations
    __table_args__ = (db.UniqueConstraint('event_id', 'member_id', name='unique_event_member'),)


class LoanRequest(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    member_id = db.Column(db.Integer, db.ForeignKey('member.id'), nullable=False)
    group_id = db.Column(db.Integer, db.ForeignKey('group.id'), nullable=False)
    currency_id = db.Column(db.Integer, db.ForeignKey('currency.id'), nullable=False)
    amount_requested = db.Column(db.Numeric(10, 2), nullable=False)
    purpose = db.Column(db.Text, nullable=False)
    repayment_months = db.Column(db.Integer, nullable=False)  # Number of months for repayment
    monthly_payment = db.Column(db.Numeric(10, 2), nullable=False)  # Calculated monthly payment
    status = db.Column(db.String(20), default='pending')  # 'pending', 'approved', 'rejected', 'active', 'completed'
    admin_notes = db.Column(db.Text)  # Admin comments on the request
    approved_by = db.Column(db.Integer, db.ForeignKey('member.id'), nullable=True)
    approved_at = db.Column(db.DateTime, nullable=True)
    disbursed_at = db.Column(db.DateTime, nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # Relationships
    member = db.relationship('Member', foreign_keys=[member_id], backref='loan_requests')
    group = db.relationship('Group', backref='loan_requests')
    currency = db.relationship('Currency', backref='loan_requests')
    approver = db.relationship('Member', foreign_keys=[approved_by])


class ContributionRequest(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    member_id = db.Column(db.Integer, db.ForeignKey('member.id'), nullable=False)
    group_id = db.Column(db.Integer, db.ForeignKey('group.id'), nullable=False)
    currency_id = db.Column(db.Integer, db.ForeignKey('currency.id'), nullable=False)
    amount = db.Column(db.Numeric(10, 2), nullable=False)
    contribution_type = db.Column(db.String(20), nullable=False)  # 'monthly', 'special', 'penalty', 'bonus'
    contribution_month = db.Column(db.Date, nullable=True)  # For monthly contributions
    description = db.Column(db.Text)
    payment_method = db.Column(db.String(50))  # 'cash', 'bank_transfer', 'mobile_banking', 'check'
    reference_number = db.Column(db.String(100))  # Transaction reference or check number
    status = db.Column(db.String(20), default='pending')  # 'pending', 'approved', 'rejected'
    admin_notes = db.Column(db.Text)  # Admin comments on the request
    approved_by = db.Column(db.Integer, db.ForeignKey('member.id'), nullable=True)
    approved_at = db.Column(db.DateTime, nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # Relationships
    member = db.relationship('Member', foreign_keys=[member_id], backref='contribution_requests')
    group = db.relationship('Group', backref='contribution_requests')
    currency = db.relationship('Currency', backref='contribution_requests')
    approver = db.relationship('Member', foreign_keys=[approved_by])