# Flask API for grant and funding tracking
from flask import Flask, request, jsonify
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime, timedelta
from flask_jwt import JWT, jwt_required, current_identity

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://npo:password@localhost/grants_db'
app.config['JWT_SECRET_KEY'] = 'your-secret-key'

db = SQLAlchemy(app)
jwt = JWT(app)

# Fund model with restrictions tracking
class Fund(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200), nullable=False)
    funder = db.Column(db.String(200), nullable=False)
    total_amount = db.Column(db.Float, nullable=False)
    spent_amount = db.Column(db.Float, default=0.0)
    restricted = db.Column(db.Boolean, default=True)
    restrictions = db.Column(db.Text)
    start_date = db.Column(db.Date)
    end_date = db.Column(db.Date)
    team_id = db.Column(db.Integer, db.ForeignKey('team.id'))

    allocations = db.relationship('Allocation', backref='fund', lazy=True)

    @property
    def remaining(self):
        return self.total_amount - self.spent_amount

    @property
    def utilization_rate(self):
        if self.total_amount == 0:
            return 0
        return (self.spent_amount / self.total_amount) * 100

# Allocation model for project-level tracking
class Allocation(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    fund_id = db.Column(db.Integer, db.ForeignKey('fund.id'), nullable=False)
    project_id = db.Column(db.Integer, db.ForeignKey('project.id'))
    amount = db.Column(db.Float, nullable=False)
    description = db.Column(db.Text)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

# Expense tracking with approval workflow
class Expense(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    allocation_id = db.Column(db.Integer, db.ForeignKey('allocation.id'))
    amount = db.Column(db.Float, nullable=False)
    description = db.Column(db.Text)
    category = db.Column(db.String(50))
    submitted_by = db.Column(db.Integer, db.ForeignKey('user.id'))
    status = db.Column(db.String(20), default='pending')  # pending, approved, rejected
    submitted_at = db.Column(db.DateTime, default=datetime.utcnow)
    approved_at = db.Column(db.DateTime)

@app.route('/api/funds/<int:fund_id>/expense', methods=['POST'])
@jwt_required()
def submit_expense(fund_id):
    """Submit expense for approval against a specific fund"""
    data = request.json

    # Verify fund exists and has sufficient remaining balance
    fund = Fund.query.get(fund_id)
    if not fund:
        return jsonify({'error': 'Fund not found'}), 404

    if fund.remaining < data['amount']:
        return jsonify({
            'error': 'Insufficient funds',
            'remaining': fund.remaining,
            'requested': data['amount']
        }), 400

    # Check restrictions
    if fund.restricted and not check_restriction_compliance(fund, data):
        return jsonify({'error': 'Expense does not comply with fund restrictions'}), 400

    expense = Expense(
        allocation_id=data.get('allocation_id'),
        amount=data['amount'],
        description=data.get('description'),
        category=data.get('category'),
        submitted_by=current_identity.id,
        status='pending'
    )

    db.session.add(expense)
    db.session.commit()

    return jsonify({
        'id': expense.id,
        'status': expense.status,
        'submitted_at': expense.submitted_at.isoformat()
    }), 201

@app.route('/api/funds/<int:fund_id>/spending-alerts', methods=['GET'])
@jwt_required()
def get_spending_alerts(fund_id):
    """Generate spending alerts for a fund"""
    fund = Fund.query.get(fund_id)

    alerts = []
    utilization = fund.utilization_rate

    # Alert: 75% utilization
    if utilization >= 75 and utilization < 90:
        alerts.append({
            'level': 'warning',
            'message': f'Fund is {utilization:.1f}% spent',
            'remaining': fund.remaining
        })

    # Alert: 90% utilization
    if utilization >= 90:
        alerts.append({
            'level': 'critical',
            'message': f'Fund is {utilization:.1f}% spent - immediate attention required',
            'remaining': fund.remaining
        })

    # Alert: Approaching end date
    if fund.end_date:
        days_remaining = (fund.end_date - datetime.now().date()).days
        if 0 < days_remaining <= 30:
            alerts.append({
                'level': 'warning',
                'message': f'Fund expires in {days_remaining} days',
                'end_date': fund.end_date.isoformat()
            })

    return jsonify({'fund_id': fund_id, 'alerts': alerts})

def check_restriction_compliance(fund, expense_data):
    """Verify expense matches fund restrictions"""
    if not fund.restrictions:
        return True

    # Parse restrictions and validate
    restriction_keywords = fund.restrictions.lower().split(',')
    expense_description = expense_data.get('description', '').lower()
    expense_category = expense_data.get('category', '').lower()

    for keyword in restriction_keywords:
        keyword = keyword.strip()
        if keyword in expense_description or keyword in expense_category:
            return True

    return False

# Generate funder-compatible CSV exports
import csv
from io import StringIO
from datetime import datetime

def export_fund_report(fund_id, format='standard'):
    fund = Fund.query.get(fund_id)
    expenses = Expense.query.filter_by(
        allocation_id=fund.allocations[0].id,
        status='approved'
    ).all()

    output = StringIO()

    if format == 'standard':
        writer = csv.DictWriter(output, fieldnames=[
            'Date', 'Description', 'Category', 'Amount', 'Approved By'
        ])
        writer.writeheader()

        for expense in expenses:
            writer.writerow({
                'Date': expense.submitted_at.strftime('%Y-%m-%d'),
                'Description': expense.description,
                'Category': expense.category,
                'Amount': expense.amount,
                'Approved By': expense.approver.name if expense.approver else 'N/A'
            })

    elif format == 'foundation_portal':
        # Format specific to common foundation portals
        writer = csv.DictWriter(output, fieldnames=[
            'Expense ID', 'Transaction Date', 'Payee', 'Amount',
            'Purpose', 'Grant Period', 'Code'
        ])
        writer.writeheader()

        for expense in expenses:
            writer.writerow({
                'Expense ID': f'EXP-{expense.id:06d}',
                'Transaction Date': expense.submitted_at.strftime('%m/%d/%Y'),
                'Payee': 'See supporting documents',
                'Amount': f'{expense.amount:.2f}',
                'Purpose': expense.description[:100],
                'Grant Period': f'{fund.start_date} - {fund.end_date}',
                'Code': fund.funder[:10].upper().replace(' ', '')
            })

    output.seek(0)
    return output.getvalue()