from datetime import datetime
from app.extensions import db


class Document(db.Model):
    __tablename__ = "document"

    id = db.Column(db.Integer, primary_key=True)
    file_name = db.Column(db.String(255), nullable=False)
    file_path = db.Column(db.String(255), nullable=False)
    file_type = db.Column(db.String(50), nullable=False)
    file_size = db.Column(db.Integer, nullable=False)
    uploaded_at = db.Column(db.DateTime, default=datetime.utcnow)


class CharityRequest(db.Model):
    __tablename__ = "charity_request"

    id = db.Column(db.Integer, primary_key=True)
    charity_id = db.Column(db.String(20), unique=True, nullable=False)
    member_id = db.Column(db.String(20), db.ForeignKey("savings_plan_member.member_id"), nullable=False)
    charity_type_id = db.Column(db.Integer, db.ForeignKey("charity_type.id"), nullable=False)
    beneficiary_name = db.Column(db.String(100), nullable=False)
    reason = db.Column(db.Text, nullable=False)
    amount = db.Column(db.Float, nullable=False)
    allotted_amount = db.Column(db.Float, nullable=True)
    cashier_id = db.Column(db.Integer, nullable=True)
    document_id = db.Column(db.Integer, db.ForeignKey("document.id"))
    status = db.Column(db.String(20), default="pending")
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    member = db.relationship("SavingsPlanMember", backref="charity_requests")
    charity_type = db.relationship("CharityType", backref="requests")
    document = db.relationship("Document", backref="charity_request")


class Credit(db.Model):
    __tablename__ = "credit"

    id = db.Column(db.Integer, primary_key=True)
    transaction_id = db.Column(db.String(20), unique=True, nullable=False)
    member_id = db.Column(db.String(20), db.ForeignKey("savings_plan_member.member_id"), nullable=False)
    transaction_type = db.Column(db.String(20), nullable=False)
    total_amount = db.Column(db.Float, nullable=False)
    savings_amount = db.Column(db.Float)
    charity_amount = db.Column(db.Float)
    loan_id = db.Column(db.String(20), db.ForeignKey("loan.loan_id"))
    cashier_id = db.Column(db.Integer, db.ForeignKey("cashier.id"), nullable=True)
    release_request_id = db.Column(db.String(20), nullable=True)
    transaction_date = db.Column(db.Date, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    member = db.relationship("SavingsPlanMember", backref="credits", foreign_keys=[member_id])
    loan = db.relationship("Loan", foreign_keys=[loan_id], backref="credits")
    cashier = db.relationship("Cashier", backref="processed_credits")


class Loan(db.Model):
    __tablename__ = "loan"

    id = db.Column(db.Integer, primary_key=True)
    loan_id = db.Column(db.String(20), unique=True, nullable=False)
    member_id = db.Column(db.String(20), db.ForeignKey("savings_plan_member.member_id"), nullable=False)
    request_id = db.Column(db.String(20), db.ForeignKey("loan_request.request_id"), nullable=False)
    amount = db.Column(db.Float, nullable=False)
    term_months = db.Column(db.Integer, nullable=False)
    status = db.Column(db.String(20), default="approved")
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    disbursement_date = db.Column(db.DateTime, nullable=True)

    loan_request = db.relationship("LoanRequest", backref=db.backref("loan", uselist=False))
    member = db.relationship("SavingsPlanMember", backref="loans", foreign_keys=[member_id])


class LoanRequest(db.Model):
    __tablename__ = "loan_request"

    id = db.Column(db.Integer, primary_key=True)
    request_id = db.Column(db.String(20), unique=True, nullable=False)
    member_id = db.Column(db.String(20), db.ForeignKey("savings_plan_member.member_id"), nullable=False)
    amount = db.Column(db.Float, nullable=False)
    reason = db.Column(db.Text, nullable=False)
    term_months = db.Column(db.Integer, nullable=False)
    status = db.Column(db.String(20), default="pending")
    document_id = db.Column(db.Integer, db.ForeignKey("document.id"))
    cashier_id = db.Column(db.Integer, nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    member = db.relationship("SavingsPlanMember", backref=db.backref("loan_requests", lazy=True))
    document = db.relationship("Document", backref=db.backref("loan_request", lazy=True))


class Debit(db.Model):
    __tablename__ = "debit"

    id = db.Column(db.Integer, primary_key=True)
    transaction_id = db.Column(db.String(20), unique=True, nullable=False)
    member_id = db.Column(db.String(20), db.ForeignKey("savings_plan_member.member_id"))
    transaction_type = db.Column(db.String(20), nullable=False)
    amount = db.Column(db.Float, nullable=False)
    cashier_id = db.Column(db.Integer, db.ForeignKey("cashier.id"), nullable=False)
    loan_id = db.Column(db.String(20), db.ForeignKey("loan.loan_id"))
    charity_id = db.Column(db.String(20), db.ForeignKey("charity_request.charity_id"))
    release_request_id = db.Column(db.String(20), nullable=True)
    transaction_date = db.Column(db.Date, nullable=False)
    notes = db.Column(db.Text)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    member = db.relationship("SavingsPlanMember", backref="debits")
    loan = db.relationship("Loan", backref="debits")
    charity_request = db.relationship("CharityRequest", backref="debits")
    cashier = db.relationship("Cashier", backref="processed_debits")


class ReleaseRequest(db.Model):
    __tablename__ = "release_request"

    id = db.Column(db.Integer, primary_key=True)
    release_request_id = db.Column(db.String(20), unique=True, nullable=False)
    member_id = db.Column(db.String(20), db.ForeignKey("savings_plan_member.member_id"), nullable=False)
    current_savings = db.Column(db.Float, nullable=False)
    outstanding_loan = db.Column(db.Float, nullable=False)
    release_amount = db.Column(db.Float, nullable=False)
    cashier_id = db.Column(db.Integer, db.ForeignKey("cashier.id"), nullable=True)
    status = db.Column(db.String(20), default="pending")
    notes = db.Column(db.Text, nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    member = db.relationship("SavingsPlanMember", backref="release_requests")
    cashier = db.relationship("Cashier", backref="handled_releases")
