from __future__ import annotations

from datetime import datetime
from functools import wraps
from typing import Callable, Iterable, Optional, Tuple

from flask import (
    Flask,
    abort,
    flash,
    redirect,
    render_template,
    make_response,
    request,
    url_for,
)
from flask_login import (
    LoginManager,
    UserMixin,
    current_user,
    login_required,
    login_user,
    logout_user,
)
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text
from flask_wtf import FlaskForm
from werkzeug.security import check_password_hash, generate_password_hash
from werkzeug.utils import secure_filename
import os
from wtforms import DateField, DecimalField, PasswordField, RadioField, SelectField, StringField, SubmitField, FileField, TextAreaField, BooleanField, DateTimeField
from wtforms.validators import DataRequired, Email, Length, NumberRange, Optional
from sqlalchemy import extract


# ----------------------------------------------------------------------------
# App configuration
# ----------------------------------------------------------------------------

app = Flask(__name__)
app.config["SECRET_KEY"] = "change-this-secret-in-production"
# Database configuration optimized for Linux
import os
from pathlib import Path

# Use instance folder for database (Linux best practice)
INSTANCE_PATH = Path(__file__).parent / "instance"
INSTANCE_PATH.mkdir(exist_ok=True, mode=0o755)

DATABASE_PATH = INSTANCE_PATH / "poultry.db"
app.config["SQLALCHEMY_DATABASE_URI"] = f"sqlite:///{DATABASE_PATH}"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

# Enhanced database configuration for Linux production
app.config["SQLALCHEMY_ENGINE_OPTIONS"] = {
    "pool_pre_ping": True,
    "pool_recycle": 300,
    "pool_timeout": 30,
    "pool_size": 10,  # Increased pool size
    "max_overflow": 20,  # Allow overflow connections
    "echo": False,  # Set to True for debugging
    "connect_args": {
        "timeout": 30,  # Increased timeout
        "check_same_thread": False,  # For SQLite threading
    }
}

# Database backup configuration
app.config["DATABASE_BACKUP_ENABLED"] = True
app.config["DATABASE_BACKUP_RETENTION_DAYS"] = 7

# Upload folder for receipts and other files
UPLOAD_FOLDER = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'uploads')
if not os.path.exists(UPLOAD_FOLDER):
    os.makedirs(UPLOAD_FOLDER, mode=0o755)  # Set proper permissions for Linux
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER

# Linux-specific configurations
app.config['MAX_CONTENT_LENGTH'] = 16 * 1024 * 1024  # 16MB max file size
app.config['UPLOAD_EXTENSIONS'] = ['.jpg', '.jpeg', '.png', '.gif', '.pdf', '.doc', '.docx']

db = SQLAlchemy(app)
login_manager = LoginManager(app)
login_manager.login_view = "login"


# ----------------------------------------------------------------------------
# Models
# ----------------------------------------------------------------------------

class User(UserMixin, db.Model):
    __tablename__ = "users"

    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True, nullable=False, index=True)
    password_hash = db.Column(db.String(255), nullable=False)
    name = db.Column(db.String(255), nullable=False)
    role = db.Column(db.String(32), nullable=False, index=True)  # admin | customer
    company_name = db.Column(db.String(255))
    parent_id = db.Column(db.Integer, db.ForeignKey("users.id"))
    # Contact & address
    phone = db.Column(db.String(50))
    address_line1 = db.Column(db.String(255))
    address_line2 = db.Column(db.String(255))
    city = db.Column(db.String(120))
    state = db.Column(db.String(120))
    postal_code = db.Column(db.String(20))
    country = db.Column(db.String(120))
    # Accounting/banking
    gst_vat = db.Column(db.String(64))
    pan_tax_id = db.Column(db.String(64))
    bank_account_name = db.Column(db.String(255))
    bank_account_number = db.Column(db.String(64))
    bank_ifsc_swift = db.Column(db.String(64))
    opening_balance = db.Column(db.Numeric(10, 2), default=0)  # Opening balance amount
    opening_balance_type = db.Column(db.String(10), default="debit")  # debit (customer owes) or credit (we owe customer)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    def set_password(self, password: str) -> None:
        self.password_hash = generate_password_hash(password)

    def check_password(self, password: str) -> bool:
        return check_password_hash(self.password_hash, password)


class Farm(db.Model):
    __tablename__ = "farms"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255), nullable=False)
    contact = db.Column(db.String(255))
    address = db.Column(db.String(255))
    opening_balance = db.Column(db.Numeric(10, 2), default=0)  # Opening balance amount
    opening_balance_type = db.Column(db.String(10), default="credit")  # credit (we owe) or debit (they owe us)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)


class FarmLedgerEntry(db.Model):
    __tablename__ = "farm_ledger_entries"

    id = db.Column(db.Integer, primary_key=True)
    farm_id = db.Column(db.Integer, db.ForeignKey("farms.id"), nullable=False)
    purchase_id = db.Column(db.Integer, db.ForeignKey("purchases.id"))
    account_txn_id = db.Column(db.Integer, db.ForeignKey("account_txns.id"))  # Link to account transaction for payments
    date = db.Column(db.Date, nullable=False, default=datetime.utcnow)
    description = db.Column(db.String(255))
    credit = db.Column(db.Numeric(10, 2), default=0)  # amount we owe to farm (purchase)
    debit = db.Column(db.Numeric(10, 2), default=0)   # payment to farm

    # Relationships
    farm = db.relationship("Farm")
    purchase = db.relationship("Purchase")
    account_txn = db.relationship("AccountTxn", foreign_keys=[account_txn_id])


class Purchase(db.Model):
    __tablename__ = "purchases"

    id = db.Column(db.Integer, primary_key=True)
    # Stored as the user who recorded/bought (admin account)
    supplier_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    farm_id = db.Column(db.Integer, db.ForeignKey("farms.id"))
    date = db.Column(db.Date, nullable=False, default=datetime.utcnow)
    weight_kg = db.Column(db.Numeric(10, 2), nullable=False)
    price_per_kg = db.Column(db.Numeric(10, 2), nullable=False)

    supplier = db.relationship("User", foreign_keys=[supplier_id])
    farm = db.relationship("Farm", foreign_keys=[farm_id])

    @property
    def total_amount(self):
        return (self.weight_kg or 0) * (self.price_per_kg or 0)


class Sale(db.Model):
    __tablename__ = "sales"

    id = db.Column(db.Integer, primary_key=True)
    customer_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    date = db.Column(db.Date, nullable=False, default=datetime.utcnow)
    weight_kg = db.Column(db.Numeric(10, 2), nullable=False)
    price_per_kg = db.Column(db.Numeric(10, 2), nullable=False)

    # Workflow fields
    status = db.Column(db.String(20), default="pending", index=True)  # pending | delivered
    delivered_at = db.Column(db.DateTime)
    payment_status = db.Column(
        db.String(32),
        default="unpaid",
        index=True,
    )  # unpaid | paid_to_admin
    payment_amount = db.Column(db.Numeric(10, 2), default=0)
    # Link to demand order if this sale was created from a demand order
    demand_order_id = db.Column(db.Integer, db.ForeignKey("demand_orders.id"))

    customer = db.relationship("User", foreign_keys=[customer_id])
    demand_order = db.relationship("DemandOrder", foreign_keys=[demand_order_id])

    @property
    def total_amount(self):
        return (self.weight_kg or 0) * (self.price_per_kg or 0)

    @property
    def is_pending(self) -> bool:
        return (self.status or "").lower() == "pending"

    @property
    def is_delivered(self) -> bool:
        return (self.status or "").lower() == "delivered"


class LedgerEntry(db.Model):
    __tablename__ = "ledger_entries"

    id = db.Column(db.Integer, primary_key=True)
    customer_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    sale_id = db.Column(db.Integer, db.ForeignKey("sales.id"))
    date = db.Column(db.Date, nullable=False, default=datetime.utcnow)
    description = db.Column(db.String(255))
    debit = db.Column(db.Numeric(10, 2), default=0)   # amount customer owes (sale)
    credit = db.Column(db.Numeric(10, 2), default=0)  # payment received from customer

    customer = db.relationship("User", foreign_keys=[customer_id])


class Account(db.Model):
    __tablename__ = "accounts"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(120), nullable=False)  # e.g., Cash in Hand, Meezan Bank, JazzCash
    type = db.Column(db.String(20), nullable=False, index=True)  # cash | bank | wallet
    opening_balance = db.Column(db.Numeric(12, 2), default=0)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    # Optional details
    holder_name = db.Column(db.String(255))
    account_number = db.Column(db.String(64))
    bank_name = db.Column(db.String(255))
    branch = db.Column(db.String(255))
    ifsc_swift = db.Column(db.String(64))
    phone = db.Column(db.String(50))
    note = db.Column(db.String(255))
    # Online payment settings
    is_online_payment_enabled = db.Column(db.Boolean, default=False)  # Show to customers for online payment
    payment_instructions = db.Column(db.Text)  # Instructions for customers


class AccountTxn(db.Model):
    __tablename__ = "account_txns"

    id = db.Column(db.Integer, primary_key=True)
    account_id = db.Column(db.Integer, db.ForeignKey("accounts.id"), nullable=False)
    date = db.Column(db.Date, nullable=False, default=datetime.utcnow)
    description = db.Column(db.String(255))
    amount = db.Column(db.Numeric(12, 2), nullable=False)  # positive for deposit, negative for withdrawal
    ref_customer_id = db.Column(db.Integer, db.ForeignKey("users.id"))

    account = db.relationship("Account", foreign_keys=[account_id])
    ref_customer = db.relationship("User", foreign_keys=[ref_customer_id])


class Employee(db.Model):
    __tablename__ = "employees"

    id = db.Column(db.Integer, primary_key=True)
    employee_id = db.Column(db.String(20), unique=True, nullable=False)  # EMP001, EMP002, etc.
    name = db.Column(db.String(100), nullable=False)  # Full name of employee
    employee_type = db.Column(db.String(50), nullable=False, index=True)  # meat_shop, delivery, office, etc.
    designation = db.Column(db.String(50), nullable=False)  # Supplier, Driver, Manager, etc.
    department = db.Column(db.String(50), nullable=False)  # Meat Shop, Delivery, Office, etc.
    joining_date = db.Column(db.Date, nullable=False)
    salary = db.Column(db.Numeric(10, 2), nullable=False)
    salary_type = db.Column(db.String(20), default="monthly")  # monthly, daily, commission
    status = db.Column(db.String(20), default="active", index=True)  # active, inactive, terminated
    phone = db.Column(db.String(20))
    address = db.Column(db.Text)
    emergency_contact = db.Column(db.String(20))
    emergency_contact_name = db.Column(db.String(100))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    attendances = db.relationship("EmployeeAttendance", backref="employee", lazy="dynamic")
    expenses = db.relationship("EmployeeExpense", backref="employee", lazy="dynamic")
    payments = db.relationship("EmployeePayment", backref="employee", lazy="dynamic")

    @property
    def is_active(self) -> bool:
        return (self.status or "").lower() == "active"

    @property
    def full_name(self) -> str:
        return self.name


class EmployeeAttendance(db.Model):
    __tablename__ = "employee_attendances"

    id = db.Column(db.Integer, primary_key=True)
    employee_id = db.Column(db.Integer, db.ForeignKey("employees.id"), nullable=False)
    date = db.Column(db.Date, nullable=False, index=True)
    check_in = db.Column(db.DateTime)
    check_out = db.Column(db.DateTime)
    status = db.Column(db.String(20), default="present", index=True)  # present, absent, late, half_day
    note = db.Column(db.Text)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    @property
    def working_hours(self) -> float:
        if self.check_in and self.check_out:
            diff = self.check_out - self.check_in
            return diff.total_seconds() / 3600  # hours
        return 0.0


class EmployeeExpense(db.Model):
    __tablename__ = "employee_expenses"

    id = db.Column(db.Integer, primary_key=True)
    employee_id = db.Column(db.Integer, db.ForeignKey("employees.id"), nullable=True)  # Made nullable for general expenses
    date = db.Column(db.Date, nullable=False, index=True)
    category = db.Column(db.String(50), nullable=False)  # Travel, Food, Supplies, etc.
    amount = db.Column(db.Numeric(10, 2), nullable=False)
    description = db.Column(db.Text, nullable=False)
    receipt_path = db.Column(db.String(255))  # Path to uploaded receipt
    status = db.Column(db.String(20), default="pending", index=True)  # pending, approved, rejected
    admin_notes = db.Column(db.Text)
    approved_by = db.Column(db.Integer, db.ForeignKey("users.id"))
    approved_at = db.Column(db.DateTime)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    approver = db.relationship("User", foreign_keys=[approved_by])

    @property
    def is_pending(self) -> bool:
        return (self.status or "").lower() == "pending"

    @property
    def is_approved(self) -> bool:
        return (self.status or "").lower() == "approved"

    @property
    def is_rejected(self) -> bool:
        return (self.status or "").lower() == "rejected"


class EmployeePayment(db.Model):
    __tablename__ = "employee_payments"

    id = db.Column(db.Integer, primary_key=True)
    employee_id = db.Column(db.Integer, db.ForeignKey("employees.id"), nullable=False)
    date = db.Column(db.Date, nullable=False, index=True)
    payment_type = db.Column(db.String(20), nullable=False)  # salary, bonus, advance, expense_reimbursement
    amount = db.Column(db.Numeric(10, 2), nullable=False)
    account_id = db.Column(db.Integer, db.ForeignKey("accounts.id"), nullable=False)
    description = db.Column(db.Text)
    status = db.Column(db.String(20), default="pending", index=True)  # pending, paid, cancelled
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    account = db.relationship("Account", foreign_keys=[account_id])


class DemandOrder(db.Model):
    __tablename__ = "demand_orders"

    id = db.Column(db.Integer, primary_key=True)
    customer_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    date = db.Column(db.Date, nullable=False, default=datetime.utcnow)
    weight_kg = db.Column(db.Numeric(10, 2), nullable=False)
    price_per_kg = db.Column(db.Numeric(10, 2), nullable=False)
    description = db.Column(db.Text)  # Customer's specific requirements
    status = db.Column(db.String(20), default="pending", index=True)  # pending | approved | rejected | delivered | paid
    admin_notes = db.Column(db.Text)  # Admin's notes on approval/modification
    delivered_at = db.Column(db.DateTime)  # When order was delivered
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    customer = db.relationship("User", foreign_keys=[customer_id])

    @property
    def total_amount(self):
        return (self.weight_kg or 0) * (self.price_per_kg or 0)


class OnlinePayment(db.Model):
    __tablename__ = "online_payments"

    id = db.Column(db.Integer, primary_key=True)
    customer_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    account_id = db.Column(db.Integer, db.ForeignKey("accounts.id"), nullable=False)
    sale_id = db.Column(db.Integer, db.ForeignKey("sales.id"))
    date = db.Column(db.Date, nullable=False, default=datetime.utcnow)
    amount = db.Column(db.Numeric(12, 2), nullable=False)
    reference_id = db.Column(db.String(255))  # Bank reference/transaction ID
    screenshot_path = db.Column(db.String(500))  # Path to uploaded screenshot
    status = db.Column(db.String(20), default="pending", index=True)  # pending | approved | rejected
    admin_notes = db.Column(db.Text)  # Admin's notes on approval/rejection
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    customer = db.relationship("User", foreign_keys=[customer_id])
    account = db.relationship("Account", foreign_keys=[account_id])
    sale = db.relationship("Sale", foreign_keys=[sale_id])


class Waste(db.Model):
    __tablename__ = "wastes"

    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.Date, nullable=False, default=datetime.utcnow, index=True)
    weight_kg = db.Column(db.Numeric(10, 2), nullable=False)
    category = db.Column(db.String(50), nullable=False)  # spoilage, shrinkage, damage, expired, other
    description = db.Column(db.Text)  # Detailed description of waste
    cost_per_kg = db.Column(db.Numeric(10, 2))  # Average cost per kg at time of waste
    created_by = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    creator = db.relationship("User", foreign_keys=[created_by])

    @property
    def total_cost(self):
        if self.cost_per_kg and self.weight_kg:
            return float(self.cost_per_kg or 0) * float(self.weight_kg or 0)
        return 0


# ----------------------------------------------------------------------------
# Forms
# ----------------------------------------------------------------------------


class LoginForm(FlaskForm):
    email = StringField("Email", validators=[DataRequired(), Email()])
    password = PasswordField("Password", validators=[DataRequired()])
    submit = SubmitField("Log In")


class FarmForm(FlaskForm):
    name = StringField("Farm Name", validators=[DataRequired(), Length(min=2, max=255)])
    contact = StringField("Contact")
    address = StringField("Address")
    opening_balance = DecimalField("Opening Balance Amount", validators=[Optional(), NumberRange(min=0)], places=2, default=0)
    opening_balance_type = RadioField(
        "Balance Type",
        choices=[
            ("credit", "Credit (We Owe to Farm)"),
            ("debit", "Debit (Farm Owes Us)"),
        ],
        default="credit",
        validators=[DataRequired()],
    )
    submit = SubmitField("Save Farm")


class PurchaseForm(FlaskForm):
    farm_id = SelectField("Farm", coerce=int)
    date = DateField("Date", validators=[DataRequired()])
    weight_kg = DecimalField("Weight (kg)", validators=[DataRequired(), NumberRange(min=0)], places=2)
    price_per_kg = DecimalField(
        "Price per kg",
        validators=[DataRequired(), NumberRange(min=0)],
        places=2,
    )
    submit = SubmitField("Record Purchase")


class SaleForm(FlaskForm):
    customer_id = SelectField("Customer", coerce=int)
    date = DateField("Date", validators=[DataRequired()])
    weight_kg = DecimalField("Weight (kg)", validators=[DataRequired(), NumberRange(min=0)], places=2)
    price_per_kg = DecimalField(
        "Price per kg",
        validators=[DataRequired(), NumberRange(min=0)],
        places=2,
    )
    submit = SubmitField("Record Sale")


class DeliveryForm(FlaskForm):
    payment_status = RadioField(
        "Payment from Customer",
        choices=[
            ("unpaid", "Not received (customer will pay admin)"),
            ("paid_to_admin", "Received by admin"),
        ],
        default="unpaid",
        validators=[DataRequired()],
    )
    submit = SubmitField("Mark Delivered")


class PaymentForm(FlaskForm):
    date = DateField("Date", validators=[DataRequired()])
    amount = DecimalField("Amount", validators=[DataRequired(), NumberRange(min=0)], places=2)
    account_id = SelectField("Account", coerce=int)
    payment_type = RadioField(
        "Payment Type",
        choices=[
            ("full", "Full Payment"),
            ("partial", "Partial Payment"),
        ],
        default="full",
        validators=[DataRequired()],
    )
    note = StringField("Note")
    submit = SubmitField("Record Payment")


class UserForm(FlaskForm):
    name = StringField("Name", validators=[DataRequired(), Length(min=2, max=255)])
    email = StringField("Email", validators=[DataRequired(), Email()])
    role = SelectField(
        "Role",
        choices=[("customer", "Customer"), ("admin", "Admin")],
        validators=[DataRequired()],
    )
    company_name = StringField("Company")
    manager_id = SelectField("Reports To (Admin)", coerce=int)
    phone = StringField("Phone")
    address_line1 = StringField("Address Line 1")
    address_line2 = StringField("Address Line 2")
    city = StringField("City")
    state = StringField("State")
    postal_code = StringField("Postal Code")
    country = StringField("Country")
    gst_vat = StringField("GST/VAT")
    pan_tax_id = StringField("Tax ID")
    bank_account_name = StringField("Bank Account Name")
    bank_account_number = StringField("Bank Account Number")
    bank_ifsc_swift = StringField("IFSC/SWIFT")
    opening_balance = DecimalField("Opening Balance Amount (for customers)", validators=[Optional(), NumberRange(min=0)], places=2, default=0)
    opening_balance_type = RadioField(
        "Balance Type (for customers)",
        choices=[
            ("debit", "Debit (Customer Owes Us)"),
            ("credit", "Credit (We Owe Customer)"),
        ],
        default="debit",
        validators=[Optional()],
    )
    password = PasswordField("Password", validators=[DataRequired(), Length(min=6)])
    submit = SubmitField("Create User")


class UserEditForm(FlaskForm):
    name = StringField("Name", validators=[DataRequired(), Length(min=2, max=255)])
    email = StringField("Email", validators=[DataRequired(), Email()])
    role = SelectField(
        "Role",
        choices=[("customer", "Customer"), ("admin", "Admin")],
        validators=[DataRequired()],
    )
    company_name = StringField("Company")
    manager_id = SelectField("Reports To (Admin)", coerce=int)
    phone = StringField("Phone")
    address_line1 = StringField("Address Line 1")
    address_line2 = StringField("Address Line 2")
    city = StringField("City")
    state = StringField("State")
    postal_code = StringField("Postal Code")
    country = StringField("Country")
    gst_vat = StringField("GST/VAT")
    pan_tax_id = StringField("Tax ID")
    bank_account_name = StringField("Bank Account Name")
    bank_account_number = StringField("Bank Account Number")
    bank_ifsc_swift = StringField("IFSC/SWIFT")
    opening_balance = DecimalField("Opening Balance Amount (for customers)", validators=[Optional(), NumberRange(min=0)], places=2, default=0)
    opening_balance_type = RadioField(
        "Balance Type (for customers)",
        choices=[
            ("debit", "Debit (Customer Owes Us)"),
            ("credit", "Credit (We Owe Customer)"),
        ],
        default="debit",
        validators=[Optional()],
    )
    password = PasswordField("New Password (leave blank to keep current)", validators=[Optional(), Length(min=6, max=128)])
    submit = SubmitField("Update User")


# ----------------------------------------------------------------------------
# Auth helpers
# ----------------------------------------------------------------------------


@login_manager.user_loader
def load_user(user_id: str) -> Optional[User]:
    try:
        return db.session.get(User, int(user_id))
    except Exception:
        return None


def role_required(*roles: Iterable[str]) -> Callable:
    def decorator(view_func: Callable) -> Callable:
        @wraps(view_func)
        def wrapped(*args, **kwargs):
            if not current_user.is_authenticated:
                return login_manager.unauthorized()
            if current_user.role not in roles:
                abort(403)
            return view_func(*args, **kwargs)

        return wrapped

    return decorator


def calculate_inventory():
    """Calculate available inventory accounting for purchases, sales, and waste."""
    total_purchased_kg = db.session.scalar(
        db.select(db.func.coalesce(db.func.sum(Purchase.weight_kg), 0))
    ) or 0
    total_sold_kg = db.session.scalar(
        db.select(db.func.coalesce(db.func.sum(Sale.weight_kg), 0))
    ) or 0
    total_waste_kg = db.session.scalar(
        db.select(db.func.coalesce(db.func.sum(Waste.weight_kg), 0))
    ) or 0
    available_inventory = (total_purchased_kg or 0) - (total_sold_kg or 0) - (total_waste_kg or 0)
    return {
        'purchased': float(total_purchased_kg or 0),
        'sold': float(total_sold_kg or 0),
        'waste': float(total_waste_kg or 0),
        'available': float(available_inventory)
    }


# ----------------------------------------------------------------------------
# Startup: create DB and seed admin
# ----------------------------------------------------------------------------


def _ensure_column(table: str, column: str, ddl: str) -> None:
    try:
        cols = db.session.execute(text(f"PRAGMA table_info({table})")).mappings().all()
        names = {c["name"] for c in cols}
        if column not in names:
            db.session.execute(text(f"ALTER TABLE {table} ADD COLUMN {column} {ddl}"))
            db.session.commit()
    except Exception:
        db.session.rollback()


# Database initialization flag
_db_initialized = False

def ensure_database_and_admin():
    global _db_initialized
    if _db_initialized:
        return
        
    try:
        db.create_all()
        # Ensure new columns exist for evolving schema
        _ensure_column("users", "parent_id", "INTEGER")
        _ensure_column("users", "phone", "TEXT")
        _ensure_column("users", "address_line1", "TEXT")
        _ensure_column("users", "address_line2", "TEXT")
        _ensure_column("users", "city", "TEXT")
        _ensure_column("users", "state", "TEXT")
        _ensure_column("users", "postal_code", "TEXT")
        _ensure_column("users", "country", "TEXT")
        _ensure_column("users", "gst_vat", "TEXT")
        _ensure_column("users", "pan_tax_id", "TEXT")
        _ensure_column("users", "bank_account_name", "TEXT")
        _ensure_column("users", "bank_account_number", "TEXT")
        _ensure_column("users", "bank_ifsc_swift", "TEXT")
        _ensure_column("sales", "delivered_by_id", "INTEGER")
        _ensure_column("sales", "status", "TEXT")
        _ensure_column("sales", "delivered_at", "DATETIME")
        _ensure_column("sales", "payment_status", "TEXT")
        _ensure_column("sales", "payment_amount", "NUMERIC")
        _ensure_column("sales", "demand_order_id", "INTEGER")
        _ensure_column("ledger_entries", "sale_id", "INTEGER")
        _ensure_column("farms", "opening_balance", "NUMERIC(10, 2)")
        _ensure_column("farms", "opening_balance_type", "TEXT")
        _ensure_column("users", "opening_balance", "NUMERIC(10, 2)")
        _ensure_column("users", "opening_balance_type", "TEXT")
        _ensure_column("farm_ledger_entries", "account_txn_id", "INTEGER")
        _db_initialized = True
    except Exception as e:
        print(f"Database initialization error: {e}")
        # Don't mark as initialized if there was an error
        return
    
    # Create supplier_remittances table if it doesn't exist
    try:
        db.session.execute(db.text("""
            CREATE TABLE IF NOT EXISTS supplier_remittances (
                id INTEGER PRIMARY KEY,
                supplier_id INTEGER NOT NULL,
                account_id INTEGER NOT NULL,
                date DATE NOT NULL,
                amount NUMERIC(12,2) NOT NULL,
                note TEXT,
                status TEXT DEFAULT 'pending',
                admin_notes TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """))
        db.session.commit()
    except Exception as e:
        print(f"Note: supplier_remittances table creation: {e}")
    
    # Create employees table if it doesn't exist
    try:
        db.session.execute(db.text("""
            CREATE TABLE IF NOT EXISTS employees (
                id INTEGER PRIMARY KEY,
                employee_id TEXT NOT NULL UNIQUE,
                name TEXT NOT NULL,
                employee_type TEXT NOT NULL,
                designation TEXT NOT NULL,
                department TEXT NOT NULL,
                joining_date DATE NOT NULL,
                salary NUMERIC(10,2) NOT NULL,
                salary_type TEXT DEFAULT 'monthly',
                status TEXT DEFAULT 'active',
                phone TEXT,
                address TEXT,
                emergency_contact TEXT,
                emergency_contact_name TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """))
        db.session.commit()
    except Exception as e:
        print(f"Note: employees table creation: {e}")
    
    # Add new columns if they don't exist (for existing databases)
    _ensure_column("employees", "name", "TEXT")
    _ensure_column("employees", "employee_type", "TEXT")
    
    # Remove user_id column if it exists (for existing databases)
    # SQLite doesn't support DROP COLUMN, so we need to recreate the table
    try:
        # Check if user_id column exists
        result = db.session.execute(db.text("PRAGMA table_info(employees)"))
        columns = [row[1] for row in result.fetchall()]
        
        if 'user_id' in columns:
            print("Recreating employees table without user_id column...")
            
            # Create new table without user_id
            db.session.execute(db.text("""
                CREATE TABLE employees_new (
                    id INTEGER PRIMARY KEY,
                    employee_id TEXT NOT NULL UNIQUE,
                    name TEXT NOT NULL,
                    employee_type TEXT NOT NULL,
                    designation TEXT NOT NULL,
                    department TEXT NOT NULL,
                    joining_date DATE NOT NULL,
                    salary NUMERIC(10,2) NOT NULL,
                    salary_type TEXT DEFAULT 'monthly',
                    status TEXT DEFAULT 'active',
                    phone TEXT,
                    address TEXT,
                    emergency_contact TEXT,
                    emergency_contact_name TEXT,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
                )
            """))
            
            # Copy data from old table to new table (excluding user_id)
            db.session.execute(db.text("""
                INSERT INTO employees_new (id, employee_id, name, employee_type, designation, 
                                         department, joining_date, salary, salary_type, status, 
                                         phone, address, emergency_contact, emergency_contact_name, 
                                         created_at, updated_at)
                SELECT id, employee_id, name, employee_type, designation, 
                       department, joining_date, salary, salary_type, status, 
                       phone, address, emergency_contact, emergency_contact_name, 
                       created_at, updated_at
                FROM employees
            """))
            
            # Drop old table and rename new table
            db.session.execute(db.text("DROP TABLE employees"))
            db.session.execute(db.text("ALTER TABLE employees_new RENAME TO employees"))
            
            db.session.commit()
            print("Successfully recreated employees table without user_id column")
    except Exception as e:
        print(f"Note: user_id column removal: {e}")
    
    # Create employee_attendances table if it doesn't exist
    try:
        db.session.execute(db.text("""
            CREATE TABLE IF NOT EXISTS employee_attendances (
                id INTEGER PRIMARY KEY,
                employee_id INTEGER NOT NULL,
                date DATE NOT NULL,
                check_in DATETIME,
                check_out DATETIME,
                status TEXT DEFAULT 'present',
                note TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """))
        db.session.commit()
    except Exception as e:
        print(f"Note: employee_attendances table creation: {e}")
    
    # Create employee_expenses table if it doesn't exist
    try:
        db.session.execute(db.text("""
            CREATE TABLE IF NOT EXISTS employee_expenses (
                id INTEGER PRIMARY KEY,
                employee_id INTEGER,
                date DATE NOT NULL,
                category TEXT NOT NULL,
                amount NUMERIC(10,2) NOT NULL,
                description TEXT NOT NULL,
                receipt_path TEXT,
                status TEXT DEFAULT 'pending',
                admin_notes TEXT,
                approved_by INTEGER,
                approved_at DATETIME,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """))
        db.session.commit()
    except Exception as e:
        print(f"Note: employee_expenses table creation: {e}")
    
    # Add migration for existing databases to make employee_id nullable
    _ensure_column("employee_expenses", "employee_id", "INTEGER")
    
    # Create employee_payments table if it doesn't exist
    try:
        db.session.execute(db.text("""
            CREATE TABLE IF NOT EXISTS employee_payments (
                id INTEGER PRIMARY KEY,
                employee_id INTEGER NOT NULL,
                date DATE NOT NULL,
                payment_type TEXT NOT NULL,
                amount NUMERIC(10,2) NOT NULL,
                account_id INTEGER NOT NULL,
                description TEXT,
                status TEXT DEFAULT 'pending',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """))
        db.session.commit()
    except Exception as e:
        print(f"Note: employee_payments table creation: {e}")
    _ensure_column("accounts", "holder_name", "TEXT")
    _ensure_column("accounts", "account_number", "TEXT")
    _ensure_column("accounts", "bank_name", "TEXT")
    _ensure_column("accounts", "branch", "TEXT")
    _ensure_column("accounts", "ifsc_swift", "TEXT")
    _ensure_column("accounts", "phone", "TEXT")
    _ensure_column("accounts", "note", "TEXT")
    _ensure_column("accounts", "is_online_payment_enabled", "BOOLEAN DEFAULT 0")
    _ensure_column("accounts", "payment_instructions", "TEXT")
    _ensure_column("demand_orders", "id", "INTEGER PRIMARY KEY")
    db.create_all()

    # Seed default admin if none exists
    admin = User.query.filter_by(role="admin").first()
    if not admin:
        admin = User(email="admin@example.com", name="Admin", role="admin")
        admin.set_password("admin123")
        db.session.add(admin)
        db.session.commit()


    # Backfill ledger debits for sales missing entries
    try:
        sales_without_ledger = (
            Sale.query.all()
        )
        for s in sales_without_ledger:
            exists = db.session.scalar(
                db.select(db.func.count(LedgerEntry.id)).where(LedgerEntry.sale_id == s.id)
            )
            if not exists:
                amount = (s.weight_kg or 0) * (s.price_per_kg or 0)
                db.session.add(
                    LedgerEntry(
                        customer_id=s.customer_id,
                        sale_id=s.id,
                        date=s.date,
                        description=f"Sale #{s.id}",
                        debit=amount,
                        credit=0,
                    )
                )
        db.session.commit()
    except Exception:
        db.session.rollback()

    # Backfill farm ledger credits for purchases missing entries
    try:
        purchases_without_ledger = (
            Purchase.query.filter(Purchase.farm_id.isnot(None)).all()
        )
        for p in purchases_without_ledger:
            exists = db.session.scalar(
                db.select(db.func.count(FarmLedgerEntry.id)).where(FarmLedgerEntry.purchase_id == p.id)
            )
            if not exists:
                amount = (p.weight_kg or 0) * (p.price_per_kg or 0)
                db.session.add(
                    FarmLedgerEntry(
                        farm_id=p.farm_id,
                        purchase_id=p.id,
                        date=p.date,
                        description=f"Purchase #{p.id}",
                        credit=amount,
                        debit=0,
                    )
                )
        db.session.commit()
    except Exception:
        db.session.rollback()


# ----------------------------------------------------------------------------
# Routes - Public
# ----------------------------------------------------------------------------


@app.route("/")
def index():
    if current_user.is_authenticated:
        return redirect(url_for("dashboard"))
    return render_template("landing.html", title="PoultryPro - Professional Poultry Management")

@app.route("/manifest.json")
def manifest():
    """Serve PWA manifest file"""
    return app.send_static_file('manifest.json')


@app.route("/login", methods=["GET", "POST"])
def login():
    if current_user.is_authenticated:
        return redirect(url_for("dashboard"))
    form = LoginForm()
    if form.validate_on_submit():
        user = User.query.filter_by(email=form.email.data.lower()).first()
        if user and user.check_password(form.password.data):
            login_user(user)
            flash("Welcome back, {}".format(user.name), "success")
            return redirect(url_for("dashboard"))
        flash("Invalid email or password", "danger")
    return render_template("login.html", form=form, title="Log In")


@app.route("/logout")
@login_required
def logout():
    logout_user()
    flash("You have been logged out.", "info")
    return redirect(url_for("index"))


# ----------------------------------------------------------------------------
# Routes - Dashboards
# ----------------------------------------------------------------------------


@app.route("/dashboard", methods=["GET", "POST"])
def dashboard():
    # If user is not authenticated, show login form
    if not current_user.is_authenticated:
        # Handle form submission from landing page popup
        if request.method == "POST":
            email = request.form.get('email', '').lower()
            password = request.form.get('password', '')
            
            if email and password:
                user = User.query.filter_by(email=email).first()
                if user and user.check_password(password):
                    login_user(user)
                    flash("Welcome back, {}".format(user.name), "success")
                    # Redirect to appropriate dashboard based on role
                    if user.role == "admin":
                        return redirect(url_for("dashboard_admin"))
                    elif user.role == "customer":
                        return redirect(url_for("dashboard_customer"))
                flash("Invalid email or password", "danger")
            else:
                flash("Please fill in all fields", "danger")
        
        # Show login form
        form = LoginForm()
        return render_template("login.html", form=form, title="Login - PoultryPro")
    
    # If user is authenticated, redirect to appropriate dashboard
    if current_user.role == "admin":
        return redirect(url_for("dashboard_admin"))
    if current_user.role == "customer":
        return redirect(url_for("dashboard_customer"))
    abort(403)


@app.route("/dashboard/admin")
@login_required
@role_required("admin")
def dashboard_admin():
    from datetime import datetime, timedelta
    
    # Basic totals (using helper function that accounts for waste)
    inventory = calculate_inventory()
    total_purchased_kg = inventory['purchased']
    total_sold_kg = inventory['sold']
    total_waste_kg = inventory['waste']
    inventory_kg = inventory['available']
    
    # Financial totals
    total_purchase_amount = db.session.scalar(db.select(db.func.coalesce(db.func.sum(Purchase.weight_kg * Purchase.price_per_kg), 0)))
    total_sales_amount = db.session.scalar(db.select(db.func.coalesce(db.func.sum(Sale.weight_kg * Sale.price_per_kg), 0)))
    total_revenue = (total_sales_amount or 0) - (total_purchase_amount or 0)

    # Demand order statistics
    pending_orders = db.session.scalar(db.select(db.func.coalesce(db.func.count(DemandOrder.id), 0)).where(DemandOrder.status == "pending"))
    approved_orders = db.session.scalar(db.select(db.func.coalesce(db.func.count(DemandOrder.id), 0)).where(DemandOrder.status == "approved"))
    assigned_orders = db.session.scalar(db.select(db.func.coalesce(db.func.count(DemandOrder.id), 0)).where(DemandOrder.status == "assigned"))
    delivered_orders = db.session.scalar(db.select(db.func.coalesce(db.func.count(DemandOrder.id), 0)).where(DemandOrder.status == "delivered"))

    # User statistics
    total_customers = db.session.scalar(db.select(db.func.coalesce(db.func.count(User.id), 0)).where(User.role == "customer"))
    total_employees = db.session.scalar(db.select(db.func.coalesce(db.func.count(Employee.id), 0)).where(Employee.status == "active"))

    # Recent data
    recent_purchases = Purchase.query.order_by(Purchase.date.desc()).limit(5).all()
    recent_sales = Sale.query.order_by(Sale.date.desc()).limit(5).all()
    recent_orders = DemandOrder.query.order_by(DemandOrder.created_at.desc()).limit(5).all()

    # Chart data - Last 7 days
    end_date = datetime.utcnow().date()
    start_date = end_date - timedelta(days=6)
    
    # Sales chart data
    sales_data = []
    purchase_data = []
    labels = []
    
    for i in range(7):
        date = start_date + timedelta(days=i)
        labels.append(date.strftime('%m/%d'))
        
        # Sales for this date
        daily_sales = db.session.scalar(
            db.select(db.func.coalesce(db.func.sum(Sale.weight_kg * Sale.price_per_kg), 0))
            .where(db.func.date(Sale.date) == date)
        ) or 0
        sales_data.append(float(daily_sales))
        
        # Purchases for this date
        daily_purchases = db.session.scalar(
            db.select(db.func.coalesce(db.func.sum(Purchase.weight_kg * Purchase.price_per_kg), 0))
            .where(db.func.date(Purchase.date) == date)
        ) or 0
        purchase_data.append(float(daily_purchases))

    # Inventory chart data (last 30 days)
    inventory_labels = []
    inventory_data = []
    
    for i in range(30, 0, -1):
        date = end_date - timedelta(days=i)
        inventory_labels.append(date.strftime('%m/%d'))
        
        # Calculate inventory on this date (including waste)
        purchased_by_date = db.session.scalar(
            db.select(db.func.coalesce(db.func.sum(Purchase.weight_kg), 0))
            .where(db.func.date(Purchase.date) <= date)
        ) or 0
        
        sold_by_date = db.session.scalar(
            db.select(db.func.coalesce(db.func.sum(Sale.weight_kg), 0))
            .where(db.func.date(Sale.date) <= date)
        ) or 0
        
        waste_by_date = db.session.scalar(
            db.select(db.func.coalesce(db.func.sum(Waste.weight_kg), 0))
            .where(db.func.date(Waste.date) <= date)
        ) or 0
        
        inventory_on_date = purchased_by_date - sold_by_date - waste_by_date
        inventory_data.append(float(inventory_on_date))

    # Account balances
    accounts = Account.query.all()
    account_balances = []
    account_names = []
    total_balance = 0
    
    for account in accounts:
        balance = db.session.scalar(
            db.select(db.func.coalesce(db.func.sum(AccountTxn.amount), 0))
            .where(AccountTxn.account_id == account.id)
        ) or 0
        account_balances.append(float(balance))
        account_names.append(account.name)
        total_balance += balance

    return render_template(
        "dashboard_admin.html",
        title="Admin Dashboard",
        # Basic stats
        total_purchased_kg=total_purchased_kg or 0,
        total_sold_kg=total_sold_kg or 0,
        total_waste_kg=total_waste_kg or 0,
        inventory_kg=inventory_kg or 0,
        total_purchase_amount=total_purchase_amount or 0,
        total_sales_amount=total_sales_amount or 0,
        total_revenue=total_revenue,
        total_balance=total_balance,
        # Order stats
        pending_orders=pending_orders or 0,
        approved_orders=approved_orders or 0,
        assigned_orders=assigned_orders or 0,
        delivered_orders=delivered_orders or 0,
        # User stats
        total_customers=total_customers or 0,
        total_employees=total_employees or 0,
        # Recent data
        recent_purchases=recent_purchases,
        recent_sales=recent_sales,
        recent_orders=recent_orders,
        # Chart data
        sales_chart_labels=labels,
        sales_chart_data=sales_data,
        purchase_chart_data=purchase_data,
        inventory_chart_labels=inventory_labels,
        inventory_chart_data=inventory_data,
        account_names=account_names,
        account_balances=account_balances,
    )




@app.route("/dashboard/customer")
@login_required
@role_required("customer")
def dashboard_customer():
    total_bought_kg = db.session.scalar(
        db.select(db.func.coalesce(db.func.sum(Sale.weight_kg), 0)).where(Sale.customer_id == current_user.id)
    )
    recent_sales = Sale.query.filter_by(customer_id=current_user.id).order_by(Sale.date.desc()).limit(10).all()
    return render_template(
        "dashboard_customer.html",
        title="Customer Dashboard",
        total_bought_kg=total_bought_kg or 0,
        recent_sales=recent_sales,
    )


# ----------------------------------------------------------------------------
# Routes - Purchases (Admin)
# ----------------------------------------------------------------------------


@app.route("/purchases")
@login_required
def purchases_list():
    if current_user.role != "admin":
        abort(403)
    purchases = Purchase.query.order_by(Purchase.date.desc()).limit(200).all()
    return render_template("purchases.html", title="Purchases", purchases=purchases)


@app.route("/purchases/new", methods=["GET", "POST"])
@login_required
def purchase_new():
    # Only admin can create purchases from farms
    if current_user.role != "admin":
        abort(403)

    form = PurchaseForm()
    farms = Farm.query.order_by(Farm.name.asc()).all()
    form.farm_id.choices = [(0, "-- None --")] + [(f.id, f.name) for f in farms]

    if request.method == "GET":
        form.date.data = datetime.utcnow().date()

    if form.validate_on_submit():
        farm_id_value = form.farm_id.data or None
        if farm_id_value == 0:
            farm_id_value = None

        purchase = Purchase(
            supplier_id=current_user.id,
            farm_id=farm_id_value,
            date=form.date.data,
            weight_kg=form.weight_kg.data,
            price_per_kg=form.price_per_kg.data,
        )
        db.session.add(purchase)
        db.session.flush()
        # Farm ledger credit (we owe the farm) - only if farm is selected
        if farm_id_value:
            db.session.add(
                FarmLedgerEntry(
                    farm_id=farm_id_value,
                    purchase_id=purchase.id,
                    date=form.date.data,
                    description="Purchase created",
                    credit=(form.weight_kg.data or 0) * (form.price_per_kg.data or 0),
                    debit=0,
                )
            )
        db.session.commit()
        flash("Purchase recorded.", "success")
        return redirect(url_for("purchases_list"))

    return render_template("purchase_new.html", title="New Purchase", form=form)


# ----------------------------------------------------------------------------
# Routes - Sales (Admin). Customers can only view their own
# ----------------------------------------------------------------------------


@app.route("/sales")
@login_required
def sales_list():
    q = Sale.query.order_by(Sale.date.desc())
    if current_user.role == "customer":
        q = q.filter_by(customer_id=current_user.id)
    
    # Get search parameters
    search_name = request.args.get('search_name', '').strip()
    search_phone = request.args.get('search_phone', '').strip()
    
    # Apply customer filters if provided
    if search_name or search_phone:
        customer_filters = []
        if search_name:
            customer_filters.append(User.name.ilike(f'%{search_name}%'))
        if search_phone:
            customer_filters.append(User.phone.ilike(f'%{search_phone}%'))
        
        if customer_filters:
            from sqlalchemy import or_
            q = q.join(User, Sale.customer_id == User.id).filter(or_(*customer_filters))
    
    sales = q.limit(200).all()
    
    # Get all customers for the filter dropdown
    all_customers = User.query.filter_by(role="customer").order_by(User.name.asc()).all()
    
    # Filter customers based on search criteria
    filtered_customers = all_customers
    if search_name or search_phone:
        customer_filters = []
        if search_name:
            customer_filters.append(User.name.ilike(f'%{search_name}%'))
        if search_phone:
            customer_filters.append(User.phone.ilike(f'%{search_phone}%'))
        
        if customer_filters:
            from sqlalchemy import or_
            filtered_customers = User.query.filter_by(role="customer").filter(or_(*customer_filters)).order_by(User.name.asc()).all()
    
    # Calculate customer balances for filtered customers
    customer_balances = {}
    for customer in filtered_customers:
        # Get all sales for this customer
        customer_sales = Sale.query.filter_by(customer_id=customer.id).all()
        total_sales = sum([float(s.total_amount or 0) for s in customer_sales])
        
        # Get all payments made by this customer
        total_payments = db.session.scalar(
            db.select(db.func.coalesce(db.func.sum(LedgerEntry.credit), 0))
            .where(LedgerEntry.customer_id == customer.id)
        )
        
        customer_balances[customer.id] = {
            'total_sales': total_sales,
            'total_payments': float(total_payments or 0),
            'balance': total_sales - float(total_payments or 0),
            'sales_count': len(customer_sales),
            'last_sale': max([s.date for s in customer_sales]) if customer_sales else None
        }
    
    return render_template("sales.html", 
                         title="Sales", 
                         sales=sales, 
                         all_customers=all_customers,
                         filtered_customers=filtered_customers,
                         customer_balances=customer_balances,
                         search_name=search_name,
                         search_phone=search_phone)




@app.route("/sales/new", methods=["GET", "POST"])
@login_required
@role_required("admin")
def sale_new():
    form = SaleForm()
    customers = User.query.filter_by(role="customer").order_by(User.name.asc()).all()
    form.customer_id.choices = [(c.id, f"{c.name}") for c in customers]

    if request.method == "GET":
        form.date.data = datetime.utcnow().date()

    if form.validate_on_submit():
        # Check available inventory before allowing sale (including waste)
        inventory = calculate_inventory()
        available_inventory = inventory['available']
        
        if float(form.weight_kg.data) > available_inventory:
            flash(f"Insufficient inventory! Available: {available_inventory:.2f} kg, Requested: {float(form.weight_kg.data):.2f} kg", "danger")
            return render_template("sale_new.html", title="New Sale", form=form, available_inventory=available_inventory)
        
        sale = Sale(
            customer_id=form.customer_id.data,
            date=form.date.data,
            weight_kg=form.weight_kg.data,
            price_per_kg=form.price_per_kg.data,
            status="pending",
        )
        db.session.add(sale)
        db.session.flush()  # get sale.id
        # Ledger debit for customer
        sale_amount = (form.weight_kg.data or 0) * (form.price_per_kg.data or 0)
        db.session.add(
            LedgerEntry(
                customer_id=form.customer_id.data,
                sale_id=sale.id,
                date=form.date.data,
                description="Sale created",
                debit=sale_amount,
                credit=0,
            )
        )
        db.session.commit()
        flash("Sale recorded.", "success")
        return redirect(url_for("sales_list"))

    # Calculate available inventory for display (including waste)
    inventory = calculate_inventory()
    available_inventory = inventory['available']

    return render_template("sale_new.html", title="New Sale", form=form, available_inventory=available_inventory)


# ----------------------------------------------------------------------------
# Demand Order System
# ----------------------------------------------------------------------------

@app.route("/demand-orders")
@login_required
def demand_orders_list():
    """List demand orders based on user role"""
    if current_user.role == "admin":
        # Admin sees all orders
        orders = DemandOrder.query.order_by(DemandOrder.created_at.desc()).all()
    elif current_user.role == "customer":
        # Customer sees only their own orders
        orders = DemandOrder.query.filter_by(customer_id=current_user.id).order_by(DemandOrder.created_at.desc()).all()
    else:
        abort(403)
    
    return render_template("demand_orders.html", title="Demand Orders", orders=orders)


@app.route("/demand-orders/new", methods=["GET", "POST"])
@login_required
@role_required("customer")
def demand_order_new():
    """Customer creates a new demand order"""
    form = DemandOrderForm()
    
    if request.method == "GET":
        form.date.data = datetime.utcnow().date()
    
    if form.validate_on_submit():
        order = DemandOrder(
            customer_id=current_user.id,
            date=form.date.data,
            weight_kg=form.weight_kg.data,
            price_per_kg=form.price_per_kg.data,
            description=form.description.data,
            status="pending"
        )
        db.session.add(order)
        db.session.commit()
        
        flash("Demand order created successfully! Admin will review and approve.", "success")
        return redirect(url_for("demand_orders_list"))
    
    return render_template("demand_order_new.html", title="New Demand Order", form=form)


@app.route("/demand-orders/<int:order_id>")
@login_required
def demand_order_view(order_id):
    """View demand order details"""
    order = db.session.get(DemandOrder, order_id)
    if not order:
        abort(404)
    
    # Check access permissions
    if current_user.role == "customer" and order.customer_id != current_user.id:
        abort(403)
    
    return render_template("demand_order_view.html", title=f"Order #{order.id}", order=order)


@app.route("/demand-orders/<int:order_id>/approve", methods=["GET", "POST"])
@login_required
@role_required("admin")
def demand_order_approve(order_id):
    """Admin approves and modifies demand order"""
    order = db.session.get(DemandOrder, order_id)
    if not order:
        abort(404)
    
    if order.status != "pending":
        flash("Only pending orders can be approved.", "warning")
        return redirect(url_for("demand_order_view", order_id=order_id))
    
    form = AdminDemandOrderForm()
    
    if request.method == "GET":
        form.weight_kg.data = order.weight_kg
        form.price_per_kg.data = order.price_per_kg
        form.description.data = order.description
    
    if form.validate_on_submit():
        # Check available inventory before allowing approval (including waste)
        inventory = calculate_inventory()
        available_inventory = inventory['available']
        
        if float(form.weight_kg.data) > available_inventory:
            flash(f"Insufficient inventory! Available: {available_inventory:.2f} kg, Requested: {float(form.weight_kg.data):.2f} kg", "danger")
            return render_template("demand_order_approve.html", title=f"Approve Order #{order.id}", form=form, order=order, available_inventory=available_inventory)
        
        # Update order with admin modifications
        order.weight_kg = form.weight_kg.data
        order.price_per_kg = form.price_per_kg.data
        order.description = form.description.data
        order.admin_notes = form.admin_notes.data
        order.status = "approved"
        order.updated_at = datetime.utcnow()
        
        db.session.commit()
        flash("Order approved successfully!", "success")
        return redirect(url_for("demand_order_view", order_id=order_id))
    
    # Calculate available inventory for display (including waste)
    inventory = calculate_inventory()
    available_inventory = inventory['available']

    return render_template("demand_order_approve.html", title=f"Approve Order #{order.id}", form=form, order=order, available_inventory=available_inventory)


@app.route("/demand-orders/<int:order_id>/reject", methods=["POST"])
@login_required
@role_required("admin")
def demand_order_reject(order_id):
    """Admin rejects demand order"""
    order = db.session.get(DemandOrder, order_id)
    if not order:
        abort(404)
    
    if order.status != "pending":
        flash("Only pending orders can be rejected.", "warning")
        return redirect(url_for("demand_order_view", order_id=order_id))
    
    admin_notes = request.form.get("admin_notes", "")
    order.status = "rejected"
    order.admin_notes = admin_notes
    order.updated_at = datetime.utcnow()
    
    db.session.commit()
    flash("Order rejected.", "info")
    return redirect(url_for("demand_order_view", order_id=order_id))




@app.route("/demand-orders/<int:order_id>/mark-delivered", methods=["POST"])
@login_required
@role_required("admin")
def demand_order_mark_delivered(order_id):
    """Admin marks order as delivered"""
    order = db.session.get(DemandOrder, order_id)
    if not order:
        abort(404)
    
    if order.status != "approved":
        flash("Only approved orders can be marked as delivered.", "warning")
        return redirect(url_for("demand_order_view", order_id=order_id))
    
    # Mark as delivered
    order.status = "delivered"
    order.delivered_at = datetime.utcnow()
    order.updated_at = datetime.utcnow()
    
    # Create a sale record
    sale = Sale(
        customer_id=order.customer_id,
        date=order.date,
        weight_kg=order.weight_kg,
        price_per_kg=order.price_per_kg,
        status="delivered",
        delivered_at=datetime.utcnow(),
        payment_status="unpaid",
        demand_order_id=order.id
    )
    db.session.add(sale)
    db.session.flush()
    
    # Create ledger entry
    db.session.add(
        LedgerEntry(
            customer_id=order.customer_id,
            sale_id=sale.id,
            date=order.date,
            description=f"Demand Order #{order.id} - Delivered",
            debit=order.total_amount,
            credit=0,
        )
    )
    
    db.session.commit()
    flash("Order marked as delivered and sale recorded!", "success")
    return redirect(url_for("demand_order_view", order_id=order_id))


@app.route("/demand-orders/<int:order_id>/collect-payment", methods=["GET", "POST"])
@login_required
@role_required("admin")
def demand_order_collect_payment(order_id):
    """Admin collects payment from customer for delivered demand order"""
    order = db.session.get(DemandOrder, order_id)
    if not order:
        abort(404)
    
    if order.status != "delivered":
        flash("Only delivered orders can have payments collected.", "warning")
        return redirect(url_for("demand_order_view", order_id=order_id))
    
    # Find the corresponding sale record
    sale = Sale.query.filter_by(demand_order_id=order.id).first()
    
    if not sale:
        flash("Sale record not found for this order.", "error")
        return redirect(url_for("demand_order_view", order_id=order_id))
    
    if sale.payment_status == "paid_to_admin":
        flash("Payment already collected for this order.", "info")
        return redirect(url_for("demand_order_view", order_id=order_id))
    
    if request.method == "POST":
        payment_amount = request.form.get("payment_amount")
        payment_method = request.form.get("payment_method", "cash")
        
        try:
            payment_amount = float(payment_amount)
            if payment_amount <= 0:
                flash("Payment amount must be greater than zero.", "danger")
            elif payment_amount > float(sale.total_amount):
                flash(f"Payment amount cannot exceed order total: ₹{sale.total_amount:,.2f}", "danger")
            else:
                # Update sale payment status
                sale.payment_status = "paid_to_admin"
                sale.payment_amount = payment_amount
                
                # Create ledger entry for payment received
                db.session.add(
                    LedgerEntry(
                        customer_id=order.customer_id,
                        sale_id=sale.id,
                        date=datetime.utcnow().date(),
                        description=f"Payment collected for Demand Order #{order.id}",
                        debit=0,
                        credit=payment_amount,
                    )
                )
                
                # Update demand order status
                order.status = "paid"
                order.updated_at = datetime.utcnow()
                
                db.session.commit()
                flash(f"Payment of ₹{payment_amount:,.2f} collected successfully from customer!", "success")
                return redirect(url_for("demand_order_view", order_id=order_id))
                
        except ValueError:
            flash("Invalid payment amount.", "danger")
    
    return render_template(
        "demand_order_collect_payment.html",
        title=f"Collect Payment - Order #{order.id}",
        order=order,
        sale=sale
    )


# ----------------------------------------------------------------------------
# Online Payment System
# ----------------------------------------------------------------------------

@app.route("/online-payment")
@login_required
@role_required("customer")
def online_payment_home():
    """Customer can view available payment methods and submit payments"""
    # Get accounts enabled for online payment
    payment_accounts = Account.query.filter_by(is_online_payment_enabled=True).order_by(Account.name.asc()).all()
    
    # Get customer's complete payment history (all statuses)
    all_payments = OnlinePayment.query.filter_by(
        customer_id=current_user.id
    ).order_by(OnlinePayment.created_at.desc()).all()
    
    # Get customer's balance
    entries = LedgerEntry.query.filter_by(customer_id=current_user.id).all()
    balance = sum([float(e.debit or 0) - float(e.credit or 0) for e in entries])
    
    return render_template(
        "online_payment_home.html",
        title="Online Payment",
        payment_accounts=payment_accounts,
        all_payments=all_payments,
        balance=balance
    )


@app.route("/online-payment/submit", methods=["GET", "POST"])
@login_required
@role_required("customer")
def online_payment_submit():
    """Customer submits online payment proof"""
    form = OnlinePaymentForm()
    
    # Get accounts enabled for online payment (for both GET and POST)
    payment_accounts = Account.query.filter_by(is_online_payment_enabled=True).order_by(Account.name.asc()).all()
    
    if not payment_accounts:
        flash("No online payment methods are currently available. Please contact admin.", "warning")
        return redirect(url_for("online_payment_home"))
    
    form.account_id.choices = [(a.id, f"{a.name} - {a.bank_name or 'N/A'}") for a in payment_accounts]
    
    if request.method == "GET":
        # Get customer's balance
        entries = LedgerEntry.query.filter_by(customer_id=current_user.id).all()
        balance = sum([float(e.debit or 0) - float(e.credit or 0) for e in entries])
        form.amount.data = balance if balance > 0 else 0
    
    if form.validate_on_submit():
        # Check if payment method is valid
        account = db.session.get(Account, form.account_id.data)
        if not account or not account.is_online_payment_enabled:
            flash("Invalid payment method selected.", "danger")
            return redirect(url_for("online_payment_submit"))
        
        # Handle screenshot (file or link)
        screenshot_path = ""
        if form.screenshot_file.data:
            # Handle file upload - save the file
            file = form.screenshot_file.data
            if file and file.filename:
                # Create uploads directory if it doesn't exist
                import os
                upload_dir = os.path.join(os.path.dirname(__file__), 'uploads')
                if not os.path.exists(upload_dir):
                    os.makedirs(upload_dir)
                
                # Generate unique filename
                import uuid
                filename = f"uploaded_{uuid.uuid4().hex}_{file.filename}"
                file_path = os.path.join(upload_dir, filename)
                
                # Save the file
                file.save(file_path)
                screenshot_path = filename
        elif form.screenshot_link.data:
            screenshot_path = form.screenshot_link.data
        
        # Create online payment record
        payment = OnlinePayment(
            customer_id=current_user.id,
            account_id=form.account_id.data,
            amount=form.amount.data,
            reference_id=form.reference_id.data,
            screenshot_path=screenshot_path,
            status="pending"
        )
        
        db.session.add(payment)
        db.session.commit()
        
        flash("Payment proof submitted successfully! Admin will verify and approve.", "success")
        return redirect(url_for("online_payment_home"))
    
    return render_template("online_payment_submit.html", title="Submit Payment Proof", form=form)


@app.route("/api/account/<int:account_id>/details")
@login_required
@role_required("customer")
def get_account_details(account_id):
    """Get account details for selected payment method"""
    account = db.session.get(Account, account_id)
    if not account or not account.is_online_payment_enabled:
        return {"error": "Account not found or not enabled for online payment"}, 404
    
    return {
        "id": account.id,
        "name": account.name,
        "bank_name": account.bank_name or "N/A",
        "holder_name": account.holder_name or "N/A",
        "account_number": account.account_number or "N/A",
        "ifsc_swift": account.ifsc_swift or "N/A",
        "payment_instructions": account.payment_instructions or "No specific instructions"
    }

@app.route("/uploads/<filename>")
@login_required
def view_upload(filename):
    """View uploaded files (screenshots)"""
    # Only allow viewing files that start with 'uploaded_'
    if not filename.startswith('uploaded_'):
        abort(404)
    
    import os
    from flask import send_from_directory, jsonify
    
    upload_dir = os.path.join(os.path.dirname(__file__), 'uploads')
    file_path = os.path.join(upload_dir, filename)
    
    if os.path.exists(file_path):
        return send_from_directory(upload_dir, filename)
    else:
        # File doesn't exist - return a placeholder image or error info
        return jsonify({
            "error": "File not found",
            "filename": filename,
            "message": "This file was uploaded before proper file storage was implemented."
        }), 404


@app.route("/admin/online-payments")
@login_required
@role_required("admin")
def admin_online_payments():
    """Admin can view and manage online payment submissions"""
    payments = OnlinePayment.query.order_by(OnlinePayment.created_at.desc()).all()
    return render_template("admin_online_payments.html", title="Online Payments", payments=payments)


@app.route("/admin/online-payment/<int:payment_id>/approve", methods=["POST"])
@login_required
@role_required("admin")
def admin_approve_online_payment(payment_id):
    """Admin approves online payment"""
    payment = db.session.get(OnlinePayment, payment_id)
    if not payment:
        abort(404)
    
    # Mark payment as approved
    payment.status = "approved"
    payment.admin_notes = request.form.get("notes", "")
    
    # Create ledger entry (credit for customer)
    db.session.add(
        LedgerEntry(
            customer_id=payment.customer_id,
            date=payment.date,
            description=f"Online payment approved - Ref: {payment.reference_id}",
            debit=0,
            credit=payment.amount,
        )
    )
    
    # Create account transaction (deposit)
    db.session.add(
        AccountTxn(
            account_id=payment.account_id,
            date=payment.date,
            description=f"Online payment from {payment.customer.name} - Ref: {payment.reference_id}",
            amount=payment.amount,
            ref_customer_id=payment.customer_id,
        )
    )
    
    db.session.commit()
    flash("Payment approved successfully!", "success")
    return redirect(url_for("admin_online_payments"))


@app.route("/admin/online-payment/<int:payment_id>/reject", methods=["POST"])
@login_required
@role_required("admin")
def admin_reject_online_payment(payment_id):
    """Admin rejects online payment"""
    payment = db.session.get(OnlinePayment, payment_id)
    if not payment:
        abort(404)
    
    # Mark payment as rejected
    payment.status = "rejected"
    payment.admin_notes = request.form.get("notes", "")
    
    db.session.commit()
    flash("Payment rejected.", "info")
    return redirect(url_for("admin_online_payments"))


# ----------------------------------------------------------------------------
# Customer Ledger (Customers can view their own)
# ----------------------------------------------------------------------------

@app.route("/my-ledger")
@login_required
@role_required("customer")
def customer_my_ledger():
    """Customer can view their own ledger"""
    entries = LedgerEntry.query.filter_by(customer_id=current_user.id).order_by(LedgerEntry.date.desc(), LedgerEntry.id.desc()).all()
    # Calculate balance including opening balance (with debit/credit type)
    opening_balance_amount = float(current_user.opening_balance or 0)
    opening_balance_type = current_user.opening_balance_type or "debit"
    if opening_balance_type == "debit":
        opening_balance = opening_balance_amount  # Customer owes us
    else:
        opening_balance = -opening_balance_amount  # We owe customer (credit)
    ledger_balance = sum([float(e.debit or 0) - float(e.credit or 0) for e in entries])
    balance = opening_balance + ledger_balance
    return render_template("customer_ledger.html", title="My Ledger", entries=entries, balance=balance, opening_balance=opening_balance, opening_balance_amount=opening_balance_amount, opening_balance_type=opening_balance_type)


# ----------------------------------------------------------------------------
# Supplier Payment Remittance (Suppliers can remit payments to admin)
# ----------------------------------------------------------------------------

class EmployeeForm(FlaskForm):
    employee_id = StringField("Employee ID", render_kw={'readonly': True})
    name = StringField("Full Name", validators=[DataRequired()])
    employee_type = SelectField("Employee Type", choices=[
        ("meat_shop", "Meat Shop Staff"),
        ("delivery", "Delivery Staff"),
        ("office", "Office Staff"),
        ("production", "Production Staff"),
        ("management", "Management"),
        ("other", "Other")
    ], validators=[DataRequired()])
    designation = StringField("Designation", validators=[DataRequired()])
    department = SelectField("Department", choices=[
        ("Meat Shop", "Meat Shop"),
        ("Delivery", "Delivery"),
        ("Office", "Office"),
        ("Production", "Production"),
        ("Management", "Management"),
        ("Other", "Other")
    ], validators=[DataRequired()])
    joining_date = DateField("Joining Date", validators=[DataRequired()])
    salary = DecimalField("Salary", validators=[DataRequired(), NumberRange(min=0)], places=2)
    salary_type = SelectField("Salary Type", choices=[
        ("monthly", "Monthly"),
        ("daily", "Daily"),
        ("commission", "Commission"),
        ("hourly", "Hourly")
    ], validators=[DataRequired()])
    phone = StringField("Phone")
    address = TextAreaField("Address")
    emergency_contact = StringField("Emergency Contact")
    emergency_contact_name = StringField("Emergency Contact Name")
    submit = SubmitField("Save Employee")


class EmployeeAttendanceForm(FlaskForm):
    employee_id = SelectField("Employee", coerce=int, validators=[DataRequired()])
    date = DateField("Date", validators=[DataRequired()])
    check_in = DateTimeField("Check In", format="%Y-%m-%dT%H:%M")
    check_out = DateTimeField("Check Out", format="%Y-%m-%dT%H:%M")
    status = SelectField("Status", choices=[
        ("present", "Present"),
        ("absent", "Absent"),
        ("late", "Late"),
        ("half_day", "Half Day"),
        ("leave", "Leave")
    ], validators=[DataRequired()])
    note = TextAreaField("Note")
    submit = SubmitField("Save Attendance")


class EmployeeExpenseForm(FlaskForm):
    date = DateField("Date", validators=[DataRequired()])
    category = SelectField("Category", choices=[
        ("General", "General"),
        ("Travel", "Travel"),
        ("Food", "Food"),
        ("Supplies", "Supplies"),
        ("Equipment", "Equipment"),
        ("Marketing", "Marketing"),
        ("Other", "Other")
    ], validators=[DataRequired()])
    amount = DecimalField("Amount", validators=[DataRequired(), NumberRange(min=0)], places=2)
    description = TextAreaField("Description", validators=[DataRequired()])
    receipt = FileField("Receipt (Optional)")
    submit = SubmitField("Submit Expense")


class EmployeePaymentForm(FlaskForm):
    employee_id = SelectField("Employee", coerce=int, validators=[DataRequired()])
    date = DateField("Date", validators=[DataRequired()])
    payment_type = SelectField("Payment Type", choices=[
        ("salary", "Salary"),
        ("bonus", "Bonus"),
        ("advance", "Advance"),
        ("expense_reimbursement", "Expense Reimbursement"),
        ("other", "Other")
    ], validators=[DataRequired()])
    amount = DecimalField("Amount", validators=[DataRequired(), NumberRange(min=0)], places=2)
    account_id = SelectField("Payment Account", coerce=int, validators=[DataRequired()])
    description = TextAreaField("Description")
    submit = SubmitField("Process Payment")


# ----------------------------------------------------------------------------
# Reports (Admin)
# ----------------------------------------------------------------------------


# ----------------------------------------------------------------------------
# Employee Management (Admin)
# ----------------------------------------------------------------------------

@app.route("/admin/employees")
@login_required
@role_required("admin")
def admin_employees():
    """Admin can view all employees"""
    employees = Employee.query.order_by(Employee.created_at.desc()).all()
    
    # Group employees by type for better organization
    employee_types = {
        "meat_shop": "Meat Shop Staff",
        "delivery": "Delivery Staff", 
        "office": "Office Staff",
        "production": "Production Staff",
        "management": "Management",
        "other": "Other"
    }
    
    return render_template("admin_employees.html", title="Employee Management", 
                         employees=employees, employee_types=employee_types)


@app.route("/admin/employees/new", methods=["GET", "POST"])
@login_required
@role_required("admin")
def admin_employee_new():
    """Admin can add new employee"""
    form = EmployeeForm()
    
    if request.method == "GET":
        form.joining_date.data = datetime.utcnow().date()
        # Auto-generate employee ID
        last_employee = Employee.query.order_by(Employee.id.desc()).first()
        if last_employee:
            last_num = int(last_employee.employee_id[3:])
            new_num = last_num + 1
        else:
            new_num = 1
        form.employee_id.data = f"EMP{new_num:03d}"
    
    if form.validate_on_submit():
        # Always use the auto-generated employee ID
        employee_id = form.employee_id.data
        
        # Check if employee ID already exists (shouldn't happen with auto-generation)
        existing_employee = Employee.query.filter_by(employee_id=employee_id).first()
        if existing_employee:
            flash("Employee ID already exists. Please refresh the page.", "error")
            return render_template("admin_employee_new.html", title="Add New Employee", form=form)
        
        employee = Employee(
            employee_id=employee_id,
            name=form.name.data,
            employee_type=form.employee_type.data,
            designation=form.designation.data,
            department=form.department.data,
            joining_date=form.joining_date.data,
            salary=form.salary.data,
            salary_type=form.salary_type.data,
            phone=form.phone.data,
            address=form.address.data,
            emergency_contact=form.emergency_contact.data,
            emergency_contact_name=form.emergency_contact_name.data
        )
        
        db.session.add(employee)
        db.session.commit()
        flash(f"Employee {employee.full_name} added successfully!", "success")
        return redirect(url_for("admin_employees"))
    
    return render_template("admin_employee_new.html", title="Add New Employee", form=form)


@app.route("/admin/employees/<int:employee_id>")
@login_required
@role_required("admin")
def admin_employee_view(employee_id):
    """Admin can view employee details"""
    employee = db.session.get(Employee, employee_id)
    if not employee:
        abort(404)
    
    return render_template("admin_employee_view.html", title=f"Employee: {employee.full_name}", employee=employee)


@app.route("/admin/employees/<int:employee_id>/edit", methods=["GET", "POST"])
@login_required
@role_required("admin")
def admin_employee_edit(employee_id):
    """Admin can edit employee details"""
    employee = db.session.get(Employee, employee_id)
    if not employee:
        abort(404)
    
    form = EmployeeForm()
    
    if request.method == "GET":
        form.employee_id.data = employee.employee_id
        form.name.data = employee.name
        form.employee_type.data = employee.employee_type
        form.designation.data = employee.designation
        form.department.data = employee.department
        form.joining_date.data = employee.joining_date
        form.salary.data = employee.salary
        form.salary_type.data = employee.salary_type
        form.phone.data = employee.phone
        form.address.data = employee.address
        form.emergency_contact.data = employee.emergency_contact
        form.emergency_contact_name.data = employee.emergency_contact_name
    
    if form.validate_on_submit():
        employee.employee_id = form.employee_id.data
        employee.name = form.name.data
        employee.employee_type = form.employee_type.data
        employee.designation = form.designation.data
        employee.department = form.department.data
        employee.joining_date = form.joining_date.data
        employee.salary = form.salary.data
        employee.salary_type = form.salary_type.data
        employee.phone = form.phone.data
        employee.address = form.address.data
        employee.emergency_contact = form.emergency_contact.data
        employee.emergency_contact_name = form.emergency_contact_name.data
        employee.updated_at = datetime.utcnow()
        
        db.session.commit()
        flash(f"Employee {employee.full_name} updated successfully!", "success")
        return redirect(url_for("admin_employee_view", employee_id=employee.id))
    
    return render_template("admin_employee_edit.html", title=f"Edit Employee: {employee.full_name}", form=form, employee=employee)


@app.route("/admin/attendance")
@login_required
@role_required("admin")
def admin_attendance():
    """Admin can manage employee attendance"""
    form = EmployeeAttendanceForm()
    
    if request.method == "GET":
        form.date.data = datetime.utcnow().date()
        employees = Employee.query.filter_by(status="active").all()
        if employees:
            form.employee_id.choices = [(e.id, f"{e.employee_id} - {e.full_name}") for e in employees]
        else:
            form.employee_id.choices = []
    
    if form.validate_on_submit():
        # Check if attendance already exists for this employee and date
        existing = EmployeeAttendance.query.filter_by(
            employee_id=form.employee_id.data,
            date=form.date.data
        ).first()
        
        if existing:
            flash("Attendance already exists for this employee on this date.", "warning")
        else:
            attendance = EmployeeAttendance(
                employee_id=form.employee_id.data,
                date=form.date.data,
                check_in=form.check_in.data,
                check_out=form.check_out.data,
                status=form.status.data,
                note=form.note.data
            )
            db.session.add(attendance)
            db.session.commit()
            flash("Attendance recorded successfully!", "success")
            return redirect(url_for("admin_attendance"))
    
    # Get attendance for current month
    current_month = datetime.utcnow().month
    current_year = datetime.utcnow().year
    attendances = EmployeeAttendance.query.filter(
        extract('month', EmployeeAttendance.date) == current_month,
        extract('year', EmployeeAttendance.date) == current_year
    ).order_by(EmployeeAttendance.date.desc()).all()
    
    return render_template("admin_attendance.html", title="Employee Attendance", form=form, attendances=attendances, employees=employees if 'employees' in locals() else [])


@app.route("/admin/expenses")
@login_required
@role_required("admin")
def admin_expenses():
    """Admin can view and manage employee expenses"""
    expenses = EmployeeExpense.query.order_by(EmployeeExpense.created_at.desc()).all()
    
    # Get summary statistics
    total_expenses = sum([float(e.amount) for e in expenses])
    pending_expenses = sum([float(e.amount) for e in expenses if e.status == "pending"])
    approved_expenses = sum([float(e.amount) for e in expenses if e.status == "approved"])
    rejected_expenses = sum([float(e.amount) for e in expenses if e.status == "rejected"])
    
    return render_template("admin_expenses.html", title="Employee Expenses", 
                         expenses=expenses, total_expenses=total_expenses,
                         pending_expenses=pending_expenses, approved_expenses=approved_expenses,
                         rejected_expenses=rejected_expenses)


@app.route("/admin/expenses/new", methods=["GET", "POST"])
@login_required
@role_required("admin")
def admin_expense_new():
    """Admin can add new expense"""
    form = EmployeeExpenseForm()
    
    if request.method == "GET":
        form.date.data = datetime.utcnow().date()
    
    if form.validate_on_submit():
        expense = EmployeeExpense(
            employee_id=None,  # All expenses are general expenses
            date=form.date.data,
            category=form.category.data,
            amount=form.amount.data,
            description=form.description.data,
            status="approved"  # Admin expenses are auto-approved
        )
        
        # Handle receipt upload
        if form.receipt.data:
            file = form.receipt.data
            if file and file.filename:
                filename = secure_filename(f"expense_{datetime.utcnow().strftime('%Y%m%d_%H%M%S')}_{file.filename}")
                file_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
                file.save(file_path)
                expense.receipt_path = filename
        
        expense.approved_by = current_user.id
        expense.approved_at = datetime.utcnow()
        
        db.session.add(expense)
        db.session.commit()
        flash("Expense added successfully!", "success")
        return redirect(url_for("admin_expenses"))
    
    return render_template("admin_expense_new.html", title="Add New Expense", form=form)


@app.route("/admin/expenses/<int:expense_id>/approve", methods=["POST"])
@login_required
@role_required("admin")
def admin_expense_approve(expense_id):
    """Admin can approve employee expense"""
    expense = db.session.get(EmployeeExpense, expense_id)
    if not expense:
        abort(404)
    
    if expense.status != "pending":
        flash("This expense has already been processed.", "warning")
        return redirect(url_for("admin_expenses"))
    
    expense.status = "approved"
    expense.approved_by = current_user.id
    expense.approved_at = datetime.utcnow()
    expense.updated_at = datetime.utcnow()
    
    db.session.commit()
    flash("Expense approved successfully!", "success")
    return redirect(url_for("admin_expenses"))


@app.route("/admin/expenses/<int:expense_id>/reject", methods=["POST"])
@login_required
@role_required("admin")
def admin_expense_reject(expense_id):
    """Admin can reject employee expense"""
    expense = db.session.get(EmployeeExpense, expense_id)
    if not expense:
        abort(404)
    
    if expense.status != "pending":
        flash("This expense has already been processed.", "warning")
        return redirect(url_for("admin_expenses"))
    
    admin_notes = request.form.get("admin_notes", "")
    expense.status = "rejected"
    expense.admin_notes = admin_notes
    expense.updated_at = datetime.utcnow()
    
    db.session.commit()
    flash("Expense rejected successfully!", "success")
    return redirect(url_for("admin_expenses"))


@app.route("/admin/payments")
@login_required
@role_required("admin")
def admin_payments():
    """Admin can manage employee payments"""
    form = EmployeePaymentForm()
    
    if request.method == "GET":
        form.date.data = datetime.utcnow().date()
        employees = Employee.query.filter_by(status="active").all()
        if employees:
            form.employee_id.choices = [(e.id, f"{e.employee_id} - {e.full_name}") for e in employees]
        else:
            form.employee_id.choices = []
        accounts = Account.query.order_by(Account.name.asc()).all()
        form.account_id.choices = [(a.id, f"{a.name}") for a in accounts]
    
    if form.validate_on_submit():
        payment = EmployeePayment(
            employee_id=form.employee_id.data,
            date=form.date.data,
            payment_type=form.payment_type.data,
            amount=form.amount.data,
            account_id=form.account_id.data,
            description=form.description.data,
            status="paid"
        )
        
        # Add account transaction (withdrawal from admin account)
        db.session.add(
            AccountTxn(
                account_id=form.account_id.data,
                date=form.date.data,
                description=f"Employee payment: {form.payment_type.data} to {payment.employee.full_name}",
                amount=-float(form.amount.data),  # Negative amount for withdrawal
            )
        )
        
        db.session.add(payment)
        db.session.commit()
        flash(f"Payment of ₹{form.amount.data:,.2f} processed successfully!", "success")
        return redirect(url_for("admin_payments"))
    
    payments = EmployeePayment.query.order_by(EmployeePayment.created_at.desc()).all()
    return render_template("admin_payments.html", title="Employee Payments", form=form, payments=payments, employees=employees if 'employees' in locals() else [])


@app.route("/reports")
@login_required
@role_required("admin")
def reports_home():
    return render_template("reports.html", title="Reports")


def _parse_dates():
    start = request.args.get("start")
    end = request.args.get("end")
    try:
        start_d = datetime.fromisoformat(start).date() if start else None
    except Exception:
        start_d = None
    try:
        end_d = datetime.fromisoformat(end).date() if end else None
    except Exception:
        end_d = None
    return start_d, end_d


@app.route("/reports/sales")
@login_required
@role_required("admin")
def report_sales():
    start_d, end_d = _parse_dates()
    q = Sale.query
    if start_d:
        q = q.filter(Sale.date >= start_d)
    if end_d:
        q = q.filter(Sale.date <= end_d)
    rows = q.order_by(Sale.date.desc()).all()
    # Compute customer balances
    balances = {}
    for u in User.query.filter_by(role="customer").all():
        deb = db.session.scalar(db.select(db.func.coalesce(db.func.sum(LedgerEntry.debit), 0)).where(LedgerEntry.customer_id == u.id))
        cred = db.session.scalar(db.select(db.func.coalesce(db.func.sum(LedgerEntry.credit), 0)).where(LedgerEntry.customer_id == u.id))
        balances[u.id] = (deb or 0) - (cred or 0)
    total = sum([float((r.weight_kg or 0) * (r.price_per_kg or 0)) for r in rows])
    return render_template(
        "report_sales.html",
        title="Sales Report",
        rows=rows,
        total=total,
        start=start_d,
        end=end_d,
        balances=balances,
    )


@app.route("/reports/purchases")
@login_required
@role_required("admin")
def report_purchases():
    start_d, end_d = _parse_dates()
    q = Purchase.query
    if start_d:
        q = q.filter(Purchase.date >= start_d)
    if end_d:
        q = q.filter(Purchase.date <= end_d)
    rows = q.order_by(Purchase.date.desc()).all()
    total = sum([float((r.weight_kg or 0) * (r.price_per_kg or 0)) for r in rows])
    return render_template(
        "report_purchases.html", title="Purchase Report", rows=rows, total=total, start=start_d, end=end_d
    )


@app.route("/reports/inventory")
@login_required
@role_required("admin")
def report_inventory():
    inventory = calculate_inventory()
    return render_template(
        "report_inventory.html",
        title="Inventory Report",
        total_purchased_kg=inventory['purchased'],
        total_sold_kg=inventory['sold'],
        total_waste_kg=inventory['waste'],
        inventory_kg=inventory['available'],
    )


# ----------------------------------------------------------------------------
# Waste Management (Admin)
# ----------------------------------------------------------------------------

@app.route("/waste")
@login_required
@role_required("admin")
def waste_list():
    """List all waste entries"""
    wastes = Waste.query.order_by(Waste.date.desc()).limit(200).all()
    return render_template("waste_list.html", title="Waste Management", wastes=wastes)


@app.route("/waste/new", methods=["GET", "POST"])
@login_required
@role_required("admin")
def waste_new():
    """Record new waste entry"""
    form = WasteForm()
    
    if request.method == "GET":
        form.date.data = datetime.utcnow().date()
        # Calculate average cost per kg from recent purchases
        recent_purchases = Purchase.query.order_by(Purchase.date.desc()).limit(10).all()
        if recent_purchases:
            total_weight = sum([float(p.weight_kg or 0) for p in recent_purchases])
            total_cost = sum([float(p.total_amount or 0) for p in recent_purchases])
            if total_weight > 0:
                avg_cost = total_cost / total_weight
                form.cost_per_kg.data = avg_cost
    
    if form.validate_on_submit():
        waste = Waste(
            date=form.date.data,
            weight_kg=form.weight_kg.data,
            category=form.category.data,
            description=form.description.data,
            cost_per_kg=form.cost_per_kg.data if form.cost_per_kg.data else None,
            created_by=current_user.id
        )
        db.session.add(waste)
        db.session.commit()
        flash(f"Waste of {form.weight_kg.data} kg recorded successfully.", "success")
        return redirect(url_for("waste_list"))
    
    return render_template("waste_new.html", title="Record Waste", form=form)


@app.route("/waste/<int:waste_id>/delete", methods=["POST"])
@login_required
@role_required("admin")
def waste_delete(waste_id):
    """Delete a waste entry"""
    waste = db.session.get(Waste, waste_id)
    if not waste:
        abort(404)
    
    weight = waste.weight_kg
    db.session.delete(waste)
    db.session.commit()
    flash(f"Waste entry of {weight} kg deleted successfully.", "success")
    return redirect(url_for("waste_list"))


@app.route("/reports/waste")
@login_required
@role_required("admin")
def report_waste():
    """Waste report with date range"""
    start_d, end_d = _parse_dates()
    
    wastes = Waste.query.filter(
        Waste.date >= start_d,
        Waste.date <= end_d
    ).order_by(Waste.date.desc()).all()
    
    total_waste_kg = sum([float(w.weight_kg or 0) for w in wastes])
    total_cost = sum([float(w.total_cost or 0) for w in wastes])
    
    # Group by category
    category_stats = {}
    for waste in wastes:
        cat = waste.category
        if cat not in category_stats:
            category_stats[cat] = {'weight': 0, 'cost': 0, 'count': 0}
        category_stats[cat]['weight'] += float(waste.weight_kg or 0)
        category_stats[cat]['cost'] += float(waste.total_cost or 0)
        category_stats[cat]['count'] += 1
    
    return render_template(
        "report_waste.html",
        title="Waste Report",
        wastes=wastes,
        total_waste_kg=total_waste_kg,
        total_cost=total_cost,
        category_stats=category_stats,
        start=start_d,
        end=end_d,
    )


@app.route("/reports/export/<string:kind>.csv")
@login_required
@role_required("admin")
def report_export_csv(kind: str):
    start_d, end_d = _parse_dates()
    output_lines: list[str] = []
    if kind == "sales":
        header = "date,customer,weight_kg,price_per_kg,total,delivered_by,status,payment_status"
        output_lines.append(header)
        q = Sale.query
        if start_d:
            q = q.filter(Sale.date >= start_d)
        if end_d:
            q = q.filter(Sale.date <= end_d)
        for s in q.order_by(Sale.date.desc()).all():
            output_lines.append(
                ",".join(
                    [
                        str(s.date),
                        s.customer.name if s.customer else "",
                        f"{float(s.weight_kg):.2f}",
                        f"{float(s.price_per_kg):.2f}",
                        f"{float(s.total_amount):.2f}",
                        (s.delivered_by.name if s.delivered_by else ""),
                        s.status or "",
                        s.payment_status or "",
                    ]
                )
            )
    elif kind == "purchases":
        header = "date,farm,weight_kg,price_per_kg,total"
        output_lines.append(header)
        q = Purchase.query
        if start_d:
            q = q.filter(Purchase.date >= start_d)
        if end_d:
            q = q.filter(Purchase.date <= end_d)
        for p in q.order_by(Purchase.date.desc()).all():
            output_lines.append(
                ",".join(
                    [
                        str(p.date),
                        (p.farm.name if p.farm else ""),
                        f"{float(p.weight_kg):.2f}",
                        f"{float(p.price_per_kg):.2f}",
                        f"{float(p.total_amount):.2f}",
                    ]
                )
            )
    else:
        abort(404)

    response = make_response("\n".join(output_lines))
    response.headers["Content-Type"] = "text/csv"
    response.headers["Content-Disposition"] = f"attachment; filename={kind}_report.csv"
    return response


# ----------------------------------------------------------------------------
# Accounts: customer ledger and payments (Admin)
# ----------------------------------------------------------------------------


@app.route("/accounts/customers")
@login_required
@role_required("admin")
def accounts_customers():
    customers = User.query.filter_by(role="customer").order_by(User.name.asc()).all()
    balances = {}
    for c in customers:
        deb = db.session.scalar(db.select(db.func.coalesce(db.func.sum(LedgerEntry.debit), 0)).where(LedgerEntry.customer_id == c.id))
        cred = db.session.scalar(db.select(db.func.coalesce(db.func.sum(LedgerEntry.credit), 0)).where(LedgerEntry.customer_id == c.id))
        balances[c.id] = (deb or 0) - (cred or 0)
    return render_template("accounts_customers.html", title="Customer Accounts", customers=customers, balances=balances)


@app.route("/accounts/<int:customer_id>/ledger")
@login_required
@role_required("admin")
def account_ledger(customer_id: int):
    customer = db.session.get(User, customer_id)
    if not customer or customer.role != "customer":
        abort(404)
    entries = LedgerEntry.query.filter_by(customer_id=customer_id).order_by(LedgerEntry.date.desc(), LedgerEntry.id.desc()).all()
    # Calculate balance including opening balance (with debit/credit type)
    opening_balance_amount = float(customer.opening_balance or 0)
    opening_balance_type = customer.opening_balance_type or "debit"
    if opening_balance_type == "debit":
        opening_balance = opening_balance_amount  # Customer owes us
    else:
        opening_balance = -opening_balance_amount  # We owe customer (credit)
    ledger_balance = sum([float(e.debit or 0) - float(e.credit or 0) for e in entries])
    balance = opening_balance + ledger_balance
    return render_template("account_ledger.html", title=f"Ledger - {customer.name}", customer=customer, entries=entries, balance=balance, opening_balance=opening_balance, opening_balance_amount=opening_balance_amount, opening_balance_type=opening_balance_type)


@app.route("/accounts/<int:customer_id>/payment", methods=["GET", "POST"])
@login_required
@role_required("admin")
def account_payment(customer_id: int):
    customer = db.session.get(User, customer_id)
    if not customer or customer.role != "customer":
        abort(404)
    form = PaymentForm()
    
    # Calculate current customer balance (including opening balance with type)
    opening_balance_amount = float(customer.opening_balance or 0)
    opening_balance_type = customer.opening_balance_type or "debit"
    if opening_balance_type == "debit":
        opening_balance = opening_balance_amount  # Customer owes us
    else:
        opening_balance = -opening_balance_amount  # We owe customer (credit)
    entries = LedgerEntry.query.filter_by(customer_id=customer_id).all()
    ledger_balance = sum([float(e.debit or 0) - float(e.credit or 0) for e in entries])
    balance = opening_balance + ledger_balance
    
    if request.method == "GET":
        form.date.data = datetime.utcnow().date()
        accounts = Account.query.order_by(Account.name.asc()).all()
        form.account_id.choices = [(a.id, f"{a.name}") for a in accounts]
        # Set amount based on payment type and balance
        if balance > 0:
            # Customer owes money - set to full balance for full payment
            form.amount.data = balance
        else:
            # If customer has credit, set to zero
            form.amount.data = 0
    else:
        # Ensure choices are set for POST requests too
        accounts = Account.query.order_by(Account.name.asc()).all()
        form.account_id.choices = [(a.id, f"{a.name}") for a in accounts]
        
        # Handle full payment logic - only for customers who owe money
        if form.payment_type.data == "full" and balance > 0:
            # Force the amount to be the full balance for full payment
            form.amount.data = balance
        elif form.payment_type.data == "full" and balance <= 0:
            # Customer has credit, don't allow full payment
            flash("Customer has credit balance. Cannot process full payment.", "error")
            return render_template("account_payment.html", title=f"Record Payment - {customer.name}", form=form, customer=customer, balance=balance)
        
        if form.validate_on_submit():
            # record ledger entry (credit)
            db.session.add(
                LedgerEntry(
                    customer_id=customer_id,
                    date=form.date.data,
                    description=f"Payment - {form.note.data or ''}",
                    debit=0,
                    credit=form.amount.data,
                )
            )
            # record account transaction (deposit)
            db.session.add(
                AccountTxn(
                    account_id=form.account_id.data,
                    date=form.date.data,
                    description=f"Customer payment - {customer.name}",
                    amount=form.amount.data,
                    ref_customer_id=customer_id,
                )
            )
            db.session.commit()
            flash("Payment recorded.", "success")
            return redirect(url_for("account_ledger", customer_id=customer_id))
    
    return render_template("account_payment.html", title=f"Record Payment - {customer.name}", form=form, customer=customer, balance=balance)


# ----------------------------------------------------------------------------
# Ledger Entry Management (Edit/Delete)
# ----------------------------------------------------------------------------

class LedgerEntryForm(FlaskForm):
    date = DateField("Date", validators=[DataRequired()])
    description = StringField("Description", validators=[DataRequired()])
    debit = DecimalField("Debit Amount", places=2, validators=[NumberRange(min=0)])
    credit = DecimalField("Credit Amount", places=2, validators=[NumberRange(min=0)])


@app.route("/ledger/<int:entry_id>/edit", methods=["GET", "POST"])
@login_required
@role_required("admin")
def edit_ledger_entry(entry_id: int):
    """Edit a ledger entry"""
    entry = db.session.get(LedgerEntry, entry_id)
    if not entry:
        abort(404)
    
    customer = db.session.get(User, entry.customer_id)
    form = LedgerEntryForm()
    
    # Calculate current customer balance
    entries = LedgerEntry.query.filter_by(customer_id=entry.customer_id).all()
    current_balance = sum([float(e.debit or 0) - float(e.credit or 0) for e in entries])
    
    if request.method == "GET":
        form.date.data = entry.date
        form.description.data = entry.description
        form.debit.data = entry.debit
        form.credit.data = entry.credit
    else:
        if form.validate_on_submit():
            # Validate that either debit or credit is provided, but not both
            if not form.debit.data and not form.credit.data:
                flash("Either debit or credit amount must be provided.", "error")
                return render_template("ledger_entry_edit.html", title="Edit Transaction", form=form, entry=entry, customer=customer, current_balance=current_balance)
            
            if form.debit.data and form.credit.data:
                flash("Cannot have both debit and credit amounts.", "error")
                return render_template("ledger_entry_edit.html", title="Edit Transaction", form=form, entry=entry, customer=customer, current_balance=current_balance)
            
            # Update the entry
            entry.date = form.date.data
            entry.description = form.description.data
            entry.debit = form.debit.data or 0
            entry.credit = form.credit.data or 0
            
            db.session.commit()
            flash("Transaction updated successfully.", "success")
            return redirect(url_for("account_ledger", customer_id=entry.customer_id))
    
    return render_template("ledger_entry_edit.html", title="Edit Transaction", form=form, entry=entry, customer=customer, current_balance=current_balance)


@app.route("/ledger/<int:entry_id>/delete", methods=["POST"])
@login_required
@role_required("admin")
def delete_ledger_entry(entry_id: int):
    """Delete a ledger entry"""
    entry = db.session.get(LedgerEntry, entry_id)
    if not entry:
        abort(404)
    
    customer_id = entry.customer_id
    db.session.delete(entry)
    db.session.commit()
    flash("Transaction deleted successfully.", "success")
    return redirect(url_for("account_ledger", customer_id=customer_id))


# ----------------------------------------------------------------------------
# Farm Accounts: ledger and payments to farms (Admin)
# ----------------------------------------------------------------------------


class FarmPaymentForm(FlaskForm):
    date = DateField("Date", validators=[DataRequired()])
    amount = DecimalField("Amount", validators=[DataRequired(), NumberRange(min=0)], places=2)
    account_id = SelectField("Account", coerce=int)
    payment_type = RadioField(
        "Payment Type",
        choices=[
            ("full", "Full Payment"),
            ("partial", "Partial Payment"),
        ],
        default="full",
        validators=[DataRequired()],
    )
    note = StringField("Note")
    submit = SubmitField("Record Payment")


class FarmLedgerEntryForm(FlaskForm):
    date = DateField("Date", validators=[DataRequired()])
    description = StringField("Description", validators=[DataRequired()])
    debit = DecimalField("Debit (Payment Amount)", places=2, validators=[Optional(), NumberRange(min=0)])
    credit = DecimalField("Credit (Purchase Amount)", places=2, validators=[Optional(), NumberRange(min=0)])
    account_id = SelectField("Account (for payments)", coerce=int, validators=[Optional()])




@app.route("/accounts/farms")
@login_required
@role_required("admin")
def farm_accounts():
    farms = Farm.query.order_by(Farm.name.asc()).all()
    balances = {}
    for f in farms:
        opening_balance_amount = float(f.opening_balance or 0)
        opening_balance_type = f.opening_balance_type or "credit"
        if opening_balance_type == "credit":
            opening_balance = opening_balance_amount  # We owe farm
        else:
            opening_balance = -opening_balance_amount  # Farm owes us (debit)
        cred = db.session.scalar(db.select(db.func.coalesce(db.func.sum(FarmLedgerEntry.credit), 0)).where(FarmLedgerEntry.farm_id == f.id))
        deb = db.session.scalar(db.select(db.func.coalesce(db.func.sum(FarmLedgerEntry.debit), 0)).where(FarmLedgerEntry.farm_id == f.id))
        ledger_balance = float(cred or 0) - float(deb or 0)
        balances[f.id] = opening_balance + ledger_balance  # amount payable to farm (including opening balance)
    return render_template("farm_accounts.html", title="Farm Accounts", farms=farms, balances=balances)




@app.route("/accounts/farm/<int:farm_id>/ledger")
@login_required
@role_required("admin")
def farm_ledger(farm_id: int):
    farm = db.session.get(Farm, farm_id)
    if not farm:
        abort(404)
    entries = FarmLedgerEntry.query.filter_by(farm_id=farm_id).order_by(FarmLedgerEntry.date.desc(), FarmLedgerEntry.id.desc()).all()
    # Calculate balance including opening balance (with debit/credit type)
    opening_balance_amount = float(farm.opening_balance or 0)
    opening_balance_type = farm.opening_balance_type or "credit"
    if opening_balance_type == "credit":
        opening_balance = opening_balance_amount  # We owe farm
    else:
        opening_balance = -opening_balance_amount  # Farm owes us (debit)
    ledger_balance = sum([float(e.credit or 0) - float(e.debit or 0) for e in entries])
    balance = opening_balance + ledger_balance
    return render_template("farm_ledger.html", title=f"Ledger - {farm.name}", farm=farm, entries=entries, balance=balance, opening_balance=opening_balance, opening_balance_amount=opening_balance_amount, opening_balance_type=opening_balance_type)


@app.route("/accounts/farm/<int:farm_id>/payment", methods=["GET", "POST"])
@login_required
@role_required("admin")
def farm_payment(farm_id: int):
    farm = db.session.get(Farm, farm_id)
    if not farm:
        abort(404)
    form = FarmPaymentForm()
    if request.method == "GET":
        form.date.data = datetime.utcnow().date()
        accounts = Account.query.order_by(Account.name.asc()).all()
        form.account_id.choices = [(a.id, a.name) for a in accounts]
    else:
        # Ensure choices are set for POST requests too
        accounts = Account.query.order_by(Account.name.asc()).all()
        form.account_id.choices = [(a.id, a.name) for a in accounts]
    if form.validate_on_submit():
        # Decrease selected account balance (withdrawal)
        account_txn = AccountTxn(
            account_id=form.account_id.data,
            date=form.date.data,
            description=f"Payment to farm - {farm.name}",
            amount=-form.amount.data,
        )
        db.session.add(account_txn)
        db.session.flush()  # Get the account_txn.id
        
        # Add farm ledger debit (payment made) - link to account transaction
        db.session.add(
            FarmLedgerEntry(
                farm_id=farm_id,
                date=form.date.data,
                description=f"Payment - {form.note.data or ''}",
                credit=0,
                debit=form.amount.data,
                account_txn_id=account_txn.id,
            )
        )
        db.session.commit()
        flash("Payment recorded.", "success")
        return redirect(url_for("farm_ledger", farm_id=farm_id))
    # Calculate current farm balance
    entries = FarmLedgerEntry.query.filter_by(farm_id=farm_id).all()
    balance = sum([float(e.credit or 0) - float(e.debit or 0) for e in entries])
    
    return render_template("farm_payment.html", title=f"Record Payment - {farm.name}", form=form, farm=farm, balance=balance)


@app.route("/farm-ledger/<int:entry_id>/edit", methods=["GET", "POST"])
@login_required
@role_required("admin")
def edit_farm_ledger_entry(entry_id: int):
    """Edit a farm ledger entry (payment or purchase)"""
    entry = db.session.get(FarmLedgerEntry, entry_id)
    if not entry:
        abort(404)
    
    farm = entry.farm
    if not farm:
        abort(404)
    
    form = FarmLedgerEntryForm()
    
    # Get accounts for payment entries
    accounts = Account.query.order_by(Account.name.asc()).all()
    form.account_id.choices = [(0, "Select Account")] + [(a.id, a.name) for a in accounts]
    
    # Calculate current farm balance
    entries = FarmLedgerEntry.query.filter_by(farm_id=entry.farm_id).all()
    opening_balance_amount = float(farm.opening_balance or 0)
    opening_balance_type = farm.opening_balance_type or "credit"
    if opening_balance_type == "credit":
        opening_balance = opening_balance_amount
    else:
        opening_balance = -opening_balance_amount
    ledger_balance = sum([float(e.credit or 0) - float(e.debit or 0) for e in entries])
    current_balance = opening_balance + ledger_balance
    
    # Get linked account transaction if it's a payment
    account_txn = None
    if entry.account_txn_id:
        account_txn = db.session.get(AccountTxn, entry.account_txn_id)
    
    if request.method == "GET":
        form.date.data = entry.date
        form.description.data = entry.description
        form.debit.data = entry.debit
        form.credit.data = entry.credit
        if account_txn:
            form.account_id.data = account_txn.account_id
    else:
        if form.validate_on_submit():
            # Validate that either debit or credit is provided, but not both
            if not form.debit.data and not form.credit.data:
                flash("Either debit (payment) or credit (purchase) amount must be provided.", "error")
                return render_template("farm_ledger_entry_edit.html", title="Edit Entry", form=form, entry=entry, farm=farm, current_balance=current_balance, account_txn=account_txn)
            
            if form.debit.data and form.credit.data:
                flash("Cannot have both debit and credit amounts.", "error")
                return render_template("farm_ledger_entry_edit.html", title="Edit Entry", form=form, entry=entry, farm=farm, current_balance=current_balance, account_txn=account_txn)
            
            old_debit = float(entry.debit or 0)
            old_credit = float(entry.credit or 0)
            new_debit = float(form.debit.data or 0)
            new_credit = float(form.credit.data or 0)
            
            # Update the farm ledger entry
            entry.date = form.date.data
            entry.description = form.description.data
            entry.debit = new_debit
            entry.credit = new_credit
            
            # If it's a payment entry (has debit), update the account transaction
            if new_debit > 0:
                if not form.account_id.data or form.account_id.data == 0:
                    flash("Account is required for payment entries.", "error")
                    return render_template("farm_ledger_entry_edit.html", title="Edit Entry", form=form, entry=entry, farm=farm, current_balance=current_balance, account_txn=account_txn)
                
                # Update or create account transaction
                if account_txn:
                    # Update existing account transaction
                    account_txn.account_id = form.account_id.data
                    account_txn.date = form.date.data
                    account_txn.description = f"Payment to farm - {farm.name}"
                    account_txn.amount = -new_debit  # Negative for withdrawal
                else:
                    # Create new account transaction
                    account_txn = AccountTxn(
                        account_id=form.account_id.data,
                        date=form.date.data,
                        description=f"Payment to farm - {farm.name}",
                        amount=-new_debit,
                    )
                    db.session.add(account_txn)
                    db.session.flush()  # Get the account_txn.id
                    entry.account_txn_id = account_txn.id
            elif account_txn:
                # If changing from payment to purchase, delete the account transaction
                db.session.delete(account_txn)
                entry.account_txn_id = None
            
            db.session.commit()
            flash("Entry updated successfully.", "success")
            return redirect(url_for("farm_ledger", farm_id=entry.farm_id))
    
    return render_template("farm_ledger_entry_edit.html", title="Edit Entry", form=form, entry=entry, farm=farm, current_balance=current_balance, account_txn=account_txn)


@app.route("/farm-ledger/<int:entry_id>/delete", methods=["POST"])
@login_required
@role_required("admin")
def delete_farm_ledger_entry(entry_id: int):
    """Delete a farm ledger entry (payment) and reverse the account transaction"""
    entry = db.session.get(FarmLedgerEntry, entry_id)
    if not entry:
        abort(404)
    
    # Only allow deletion of payment entries (debit > 0 and no purchase_id)
    if not entry.debit or entry.debit <= 0 or entry.purchase_id:
        flash("Only payment entries can be deleted. Purchase entries cannot be deleted.", "error")
        return redirect(url_for("farm_ledger", farm_id=entry.farm_id))
    
    farm_id = entry.farm_id
    account_txn = None
    
    # If there's a linked account transaction, get it
    if entry.account_txn_id:
        account_txn = db.session.get(AccountTxn, entry.account_txn_id)
    else:
        # For backward compatibility: try to find matching AccountTxn by description and amount
        # Payment description format: "Payment - {note}" or "Payment -"
        # AccountTxn description format: "Payment to farm - {farm.name}"
        farm = entry.farm
        if farm:
            matching_txns = AccountTxn.query.filter(
                AccountTxn.description.like(f"Payment to farm - {farm.name}%"),
                AccountTxn.amount == -entry.debit,
                AccountTxn.date == entry.date
            ).all()
            if matching_txns:
                # Use the first matching transaction
                account_txn = matching_txns[0]
    
    # Delete the account transaction to reverse the balance
    if account_txn:
        db.session.delete(account_txn)
    
    # Delete the farm ledger entry
    db.session.delete(entry)
    db.session.commit()
    
    if account_txn:
        flash("Payment deleted successfully. Account balance has been reversed.", "success")
    else:
        flash("Payment deleted successfully. Note: No matching account transaction found to reverse.", "warning")
    
    return redirect(url_for("farm_ledger", farm_id=farm_id))


@app.route("/accounts")
@login_required
@role_required("admin")
def list_accounts():
    accounts = Account.query.order_by(Account.name.asc()).all()
    # Calculate balances: opening + sum(txns)
    balances = {}
    for a in accounts:
        total = float(a.opening_balance or 0)
        delta = db.session.scalar(
            db.select(db.func.coalesce(db.func.sum(AccountTxn.amount), 0)).where(AccountTxn.account_id == a.id)
        )
        balances[a.id] = total + float(delta or 0)
    return render_template("accounts.html", title="Cash & Bank", accounts=accounts, balances=balances)


class AccountForm(FlaskForm):
    name = StringField("Name", validators=[DataRequired(), Length(min=2, max=120)])
    type = SelectField("Type", choices=[("cash", "Cash"), ("bank", "Bank"), ("wallet", "Wallet")], validators=[DataRequired()])
    opening_balance = DecimalField("Opening Balance", validators=[Optional(), NumberRange(min=0)], places=2)
    holder_name = StringField("Account Holder")
    account_number = StringField("Account Number")
    bank_name = StringField("Bank/Provider")
    branch = StringField("Branch")
    ifsc_swift = StringField("IFSC/SWIFT")
    phone = StringField("Phone")
    note = StringField("Note")
    is_online_payment_enabled = RadioField("Enable Online Payment", choices=[("1", "Yes"), ("0", "No")], default="0")
    payment_instructions = TextAreaField("Payment Instructions for Customers")
    submit = SubmitField("Save")


class DemandOrderForm(FlaskForm):
    date = DateField("Date", validators=[DataRequired()])
    weight_kg = DecimalField("Weight (kg)", validators=[DataRequired(), NumberRange(min=0.01)], places=2)
    price_per_kg = DecimalField("Expected Price per kg", validators=[DataRequired(), NumberRange(min=0)], places=2)
    description = StringField("Special Requirements/Description", validators=[Length(max=500)])
    submit = SubmitField("Create Demand Order")


class AdminDemandOrderForm(FlaskForm):
    weight_kg = DecimalField("Weight (kg)", validators=[DataRequired(), NumberRange(min=0.01)], places=2)
    price_per_kg = DecimalField("Final Price per kg", validators=[DataRequired(), NumberRange(min=0)], places=2)
    description = StringField("Modified Requirements/Description", validators=[Length(max=500)])
    admin_notes = StringField("Admin Notes", validators=[Length(max=500)])
    submit = SubmitField("Update Order")




class OnlinePaymentForm(FlaskForm):
    account_id = SelectField("Payment Method", coerce=int, validators=[DataRequired()])
    amount = DecimalField("Amount", validators=[DataRequired(), NumberRange(min=0)], places=2)
    reference_id = StringField("Reference/Transaction ID", validators=[DataRequired(), Length(min=3, max=255)])
    screenshot_file = FileField("Screenshot/Proof Image")
    screenshot_link = StringField("Or paste screenshot link")
    note = StringField("Additional Notes")
    submit = SubmitField("Submit Payment Proof")


class WasteForm(FlaskForm):
    date = DateField("Date", validators=[DataRequired()])
    weight_kg = DecimalField("Weight (kg)", validators=[DataRequired(), NumberRange(min=0.01)], places=2)
    category = SelectField(
        "Waste Category",
        choices=[
            ("spoilage", "Spoilage (Expired/Rotten)"),
            ("shrinkage", "Shrinkage (Weight Loss)"),
            ("damage", "Damage (Physical Damage)"),
            ("expired", "Expired (Past Sell Date)"),
            ("other", "Other (Specify in description)"),
        ],
        validators=[DataRequired()],
    )
    description = TextAreaField("Description", validators=[Length(max=500)])
    cost_per_kg = DecimalField("Cost per kg (Optional)", validators=[Optional(), NumberRange(min=0)], places=2)
    submit = SubmitField("Record Waste")


@app.route("/accounts/new", methods=["GET", "POST"])
@login_required
@role_required("admin")
def new_account():
    form = AccountForm()
    if form.validate_on_submit():
        acc = Account(
            name=form.name.data,
            type=form.type.data,
            opening_balance=form.opening_balance.data or 0,
            holder_name=form.holder_name.data,
            account_number=form.account_number.data,
            bank_name=form.bank_name.data,
            branch=form.branch.data,
            ifsc_swift=form.ifsc_swift.data,
            phone=form.phone.data,
            note=form.note.data,
            is_online_payment_enabled=form.is_online_payment_enabled.data == "1",
            payment_instructions=form.payment_instructions.data,
        )
        db.session.add(acc)
        db.session.commit()
        flash("Account created.", "success")
        return redirect(url_for("list_accounts"))
    return render_template("account_new.html", title="New Account", form=form)


@app.route("/accounts/<int:account_id>")
@login_required
@role_required("admin")
def account_view(account_id: int):
    account = db.session.get(Account, account_id)
    if not account:
        abort(404)
    txns = AccountTxn.query.filter_by(account_id=account_id).order_by(AccountTxn.date.desc(), AccountTxn.id.desc()).all()
    balance = float(account.opening_balance or 0) + sum([float(t.amount or 0) for t in txns])
    return render_template("account_view.html", title=account.name, account=account, txns=txns, balance=balance)


@app.route("/accounts/<int:account_id>/deposit", methods=["GET", "POST"])
@login_required
@role_required("admin")
def deposit_account(account_id: int):
    """Deposit money into an account"""
    account = db.session.get(Account, account_id)
    if not account:
        abort(404)
    
    form = DepositForm()
    
    # Get customers for optional reference
    customers = User.query.filter_by(role="customer").order_by(User.name.asc()).all()
    form.ref_customer_id.choices = [(0, "None")] + [(c.id, c.name) for c in customers]
    
    if request.method == "GET":
        form.date.data = datetime.utcnow().date()
        form.description.data = "Manual Deposit"
    else:
        if form.validate_on_submit():
            # Create deposit transaction (positive amount)
            txn = AccountTxn(
                account_id=account_id,
                date=form.date.data,
                description=form.description.data,
                amount=form.amount.data,  # Positive for deposit
                ref_customer_id=form.ref_customer_id.data if form.ref_customer_id.data and form.ref_customer_id.data != 0 else None,
            )
            db.session.add(txn)
            db.session.commit()
            flash(f"Deposit of PKR {float(form.amount.data):,.2f} recorded successfully.", "success")
            return redirect(url_for("account_view", account_id=account_id))
    
    # Calculate current balance
    txns = AccountTxn.query.filter_by(account_id=account_id).all()
    balance = float(account.opening_balance or 0) + sum([float(t.amount or 0) for t in txns])
    
    return render_template("account_deposit.html", title=f"Deposit - {account.name}", form=form, account=account, balance=balance)


@app.route("/accounts/<int:account_id>/withdraw", methods=["GET", "POST"])
@login_required
@role_required("admin")
def withdraw_account(account_id: int):
    """Withdraw money from an account"""
    account = db.session.get(Account, account_id)
    if not account:
        abort(404)
    
    form = WithdrawForm()
    
    # Get customers for optional reference
    customers = User.query.filter_by(role="customer").order_by(User.name.asc()).all()
    form.ref_customer_id.choices = [(0, "None")] + [(c.id, c.name) for c in customers]
    
    if request.method == "GET":
        form.date.data = datetime.utcnow().date()
        form.description.data = "Manual Withdrawal"
    else:
        if form.validate_on_submit():
            # Calculate current balance
            txns = AccountTxn.query.filter_by(account_id=account_id).all()
            current_balance = float(account.opening_balance or 0) + sum([float(t.amount or 0) for t in txns])
            
            # Check if sufficient balance (allow overdraft but warn)
            if current_balance < form.amount.data:
                flash(f"Warning: Account balance (PKR {current_balance:,.2f}) is less than withdrawal amount (PKR {float(form.amount.data):,.2f}). This will result in an overdraft.", "warning")
            
            # Create withdrawal transaction (negative amount)
            txn = AccountTxn(
                account_id=account_id,
                date=form.date.data,
                description=form.description.data,
                amount=-form.amount.data,  # Negative for withdrawal
                ref_customer_id=form.ref_customer_id.data if form.ref_customer_id.data and form.ref_customer_id.data != 0 else None,
            )
            db.session.add(txn)
            db.session.commit()
            flash(f"Withdrawal of PKR {float(form.amount.data):,.2f} recorded successfully.", "success")
            return redirect(url_for("account_view", account_id=account_id))
    
    # Calculate current balance
    txns = AccountTxn.query.filter_by(account_id=account_id).all()
    balance = float(account.opening_balance or 0) + sum([float(t.amount or 0) for t in txns])
    
    return render_template("account_withdraw.html", title=f"Withdraw - {account.name}", form=form, account=account, balance=balance)


@app.route("/accounts/<int:account_id>/edit", methods=["GET", "POST"])
@login_required
@role_required("admin")
def edit_account(account_id: int):
    """Edit an account"""
    account = db.session.get(Account, account_id)
    if not account:
        abort(404)
    
    form = AccountForm()
    
    if request.method == "GET":
        form.name.data = account.name
        form.type.data = account.type
        form.opening_balance.data = account.opening_balance
        form.holder_name.data = account.holder_name
        form.account_number.data = account.account_number
        form.bank_name.data = account.bank_name
        form.branch.data = account.branch
        form.ifsc_swift.data = account.ifsc_swift
        form.phone.data = account.phone
        form.note.data = account.note
        form.is_online_payment_enabled.data = "1" if account.is_online_payment_enabled else "0"
        form.payment_instructions.data = account.payment_instructions
    else:
        if form.validate_on_submit():
            account.name = form.name.data
            account.type = form.type.data
            account.opening_balance = form.opening_balance.data or 0
            account.holder_name = form.holder_name.data
            account.account_number = form.account_number.data
            account.bank_name = form.bank_name.data
            account.branch = form.branch.data
            account.ifsc_swift = form.ifsc_swift.data
            account.phone = form.phone.data
            account.note = form.note.data
            account.is_online_payment_enabled = form.is_online_payment_enabled.data == "1"
            account.payment_instructions = form.payment_instructions.data
            
            db.session.commit()
            flash("Account updated successfully.", "success")
            return redirect(url_for("list_accounts"))
    
    return render_template("account_edit.html", title=f"Edit Account - {account.name}", form=form, account=account)


@app.route("/accounts/<int:account_id>/delete", methods=["POST"])
@login_required
@role_required("admin")
def delete_account(account_id: int):
    """Delete an account (only if it has no transactions)"""
    account = db.session.get(Account, account_id)
    if not account:
        abort(404)
    
    # Check if account has any transactions
    txns_count = db.session.scalar(
        db.select(db.func.count(AccountTxn.id)).where(AccountTxn.account_id == account_id)
    ) or 0
    
    if txns_count > 0:
        flash(f"Cannot delete account '{account.name}' because it has {txns_count} transaction(s). Please delete or reassign transactions first.", "error")
        return redirect(url_for("list_accounts"))
    
    # Check if account is used in farm payments (via account_txn_id in FarmLedgerEntry)
    farm_ledger_count = db.session.scalar(
        db.select(db.func.count(FarmLedgerEntry.id)).where(FarmLedgerEntry.account_txn_id.isnot(None))
    ) or 0
    
    # Check if account is used in online payments
    online_payment_count = db.session.scalar(
        db.select(db.func.count(OnlinePayment.id)).where(OnlinePayment.account_id == account_id)
    ) or 0
    
    if online_payment_count > 0:
        flash(f"Cannot delete account '{account.name}' because it is linked to {online_payment_count} online payment(s).", "error")
        return redirect(url_for("list_accounts"))
    
    account_name = account.name
    db.session.delete(account)
    db.session.commit()
    
    flash(f"Account '{account_name}' deleted successfully.", "success")
    return redirect(url_for("list_accounts"))


# ----------------------------------------------------------------------------
# Account Transaction Management (Edit/Delete)
# ----------------------------------------------------------------------------

class AccountTxnForm(FlaskForm):
    date = DateField("Date", validators=[DataRequired()])
    description = StringField("Description", validators=[DataRequired()])
    amount = DecimalField("Amount", places=2, validators=[DataRequired()])
    ref_customer_id = SelectField("Customer", coerce=int, validators=[Optional()])


class DepositForm(FlaskForm):
    date = DateField("Date", validators=[DataRequired()])
    amount = DecimalField("Amount", validators=[DataRequired(), NumberRange(min=0.01)], places=2)
    description = StringField("Description", validators=[DataRequired()])
    ref_customer_id = SelectField("Customer (Optional)", coerce=int, validators=[Optional()])
    submit = SubmitField("Deposit")


class WithdrawForm(FlaskForm):
    date = DateField("Date", validators=[DataRequired()])
    amount = DecimalField("Amount", validators=[DataRequired(), NumberRange(min=0.01)], places=2)
    description = StringField("Description", validators=[DataRequired()])
    ref_customer_id = SelectField("Customer (Optional)", coerce=int, validators=[Optional()])
    submit = SubmitField("Withdraw")


@app.route("/account-txn/<int:txn_id>/edit", methods=["GET", "POST"])
@login_required
@role_required("admin")
def edit_account_txn(txn_id: int):
    """Edit an account transaction"""
    txn = db.session.get(AccountTxn, txn_id)
    if not txn:
        abort(404)
    
    account = db.session.get(Account, txn.account_id)
    form = AccountTxnForm()
    
    # Set up customer choices
    customers = User.query.filter_by(role="customer").order_by(User.name.asc()).all()
    form.ref_customer_id.choices = [(0, "No Customer")] + [(c.id, c.name) for c in customers]
    
    # Calculate current account balance
    all_txns = AccountTxn.query.filter_by(account_id=txn.account_id).all()
    current_balance = float(account.opening_balance or 0) + sum([float(t.amount or 0) for t in all_txns])
    
    if request.method == "GET":
        form.date.data = txn.date
        form.description.data = txn.description
        form.amount.data = txn.amount
        form.ref_customer_id.data = txn.ref_customer_id or 0
    else:
        if form.validate_on_submit():
            # Update the transaction
            txn.date = form.date.data
            txn.description = form.description.data
            txn.amount = form.amount.data
            txn.ref_customer_id = form.ref_customer_id.data if form.ref_customer_id.data != 0 else None
            
            db.session.commit()
            flash("Transaction updated successfully.", "success")
            return redirect(url_for("account_view", account_id=txn.account_id))
    
    return render_template("account_txn_edit.html", title="Edit Transaction", form=form, txn=txn, account=account, current_balance=current_balance)


@app.route("/account-txn/<int:txn_id>/delete", methods=["POST"])
@login_required
@role_required("admin")
def delete_account_txn(txn_id: int):
    """Delete an account transaction"""
    txn = db.session.get(AccountTxn, txn_id)
    if not txn:
        abort(404)
    
    account_id = txn.account_id
    db.session.delete(txn)
    db.session.commit()
    flash("Transaction deleted successfully.", "success")
    return redirect(url_for("account_view", account_id=account_id))
# ----------------------------------------------------------------------------
# Routes - Farms (Admin)
# ----------------------------------------------------------------------------


@app.route("/farms")
@login_required
def farms_list():
    if current_user.role != "admin":
        abort(403)
    farms = Farm.query.order_by(Farm.name.asc()).all()
    return render_template("farms.html", title="Farms", farms=farms)


@app.route("/farms/new", methods=["GET", "POST"])
@login_required
@role_required("admin")
def farm_new():
    form = FarmForm()
    if form.validate_on_submit():
        farm = Farm(
            name=form.name.data, 
            contact=form.contact.data, 
            address=form.address.data,
            opening_balance=form.opening_balance.data or 0,
            opening_balance_type=form.opening_balance_type.data or "credit"
        )
        db.session.add(farm)
        db.session.commit()
        flash("Farm added.", "success")
        return redirect(url_for("farms_list"))
    return render_template("farm_new.html", title="New Farm", form=form)


# ----------------------------------------------------------------------------
# Routes - Users (Admin)
# ----------------------------------------------------------------------------


@app.route("/users")
@login_required
@role_required("admin")
def users_list():
    users = User.query.order_by(User.role.asc(), User.name.asc()).all()
    return render_template("users.html", title="Users", users=users)


@app.route("/users/new", methods=["GET", "POST"])
@login_required
@role_required("admin")
def user_new():
    form = UserForm()
    # Fill manager/admin choices
    admins = User.query.filter_by(role="admin").order_by(User.name.asc()).all()
    form.manager_id.choices = [(0, "-- None --")] + [(a.id, a.name) for a in admins]
    if form.validate_on_submit():
        if User.query.filter_by(email=form.email.data.lower()).first():
            flash("Email already exists.", "warning")
        else:
            user = User(
                name=form.name.data,
                email=form.email.data.lower(),
                role=form.role.data,
                company_name=form.company_name.data,
                phone=form.phone.data,
                address_line1=form.address_line1.data,
                address_line2=form.address_line2.data,
                city=form.city.data,
                state=form.state.data,
                postal_code=form.postal_code.data,
                country=form.country.data,
                gst_vat=form.gst_vat.data,
                pan_tax_id=form.pan_tax_id.data,
                bank_account_name=form.bank_account_name.data,
                bank_account_number=form.bank_account_number.data,
                bank_ifsc_swift=form.bank_ifsc_swift.data,
                opening_balance=form.opening_balance.data or 0 if form.role.data == "customer" else 0,
                opening_balance_type=form.opening_balance_type.data or "debit" if form.role.data == "customer" else "debit",
            )
            user.set_password(form.password.data)
            db.session.add(user)
            db.session.commit()
            flash("User created.", "success")
            return redirect(url_for("users_list"))
    return render_template("user_new.html", title="New User", form=form)


@app.route("/users/<int:user_id>/edit", methods=["GET", "POST"])
@login_required
@role_required("admin")
def user_edit(user_id):
    user = User.query.get_or_404(user_id)
    form = UserEditForm()
    
    # Fill manager/admin choices
    admins = User.query.filter_by(role="admin").order_by(User.name.asc()).all()
    form.manager_id.choices = [(0, "-- None --")] + [(a.id, a.name) for a in admins]
    
    if request.method == "GET":
        # Pre-fill opening balance if editing existing customer
        if user.role == "customer":
            form.opening_balance.data = user.opening_balance or 0
    
    if request.method == "POST":
        print(f"POST request received for user {user_id}")
        print(f"Form validation errors: {form.errors}")
        print(f"Form data: {form.data}")
        print(f"Form validate_on_submit result: {form.validate_on_submit()}")
    
    if form.validate_on_submit():
        print(f"Form validated successfully for user {user_id}")
        # Check if email is being changed and if it already exists
        if form.email.data.lower() != user.email:
            existing_user = User.query.filter_by(email=form.email.data.lower()).first()
            if existing_user:
                flash("Email already exists.", "warning")
                return render_template("user_edit.html", title="Edit User", form=form, user=user)
        
        # Update user fields
        user.name = form.name.data
        user.email = form.email.data.lower()
        user.role = form.role.data
        user.company_name = form.company_name.data
        user.phone = form.phone.data
        user.address_line1 = form.address_line1.data
        user.address_line2 = form.address_line2.data
        user.city = form.city.data
        user.state = form.state.data
        user.postal_code = form.postal_code.data
        user.country = form.country.data
        user.gst_vat = form.gst_vat.data
        user.pan_tax_id = form.pan_tax_id.data
        user.bank_account_name = form.bank_account_name.data
        user.bank_account_number = form.bank_account_number.data
        user.bank_ifsc_swift = form.bank_ifsc_swift.data
        if form.role.data == "customer":
            user.opening_balance = form.opening_balance.data or 0
            user.opening_balance_type = form.opening_balance_type.data or "debit"
        else:
            user.opening_balance = 0
            user.opening_balance_type = "debit"
        
        user.parent_id = None
            
        # Update password only if provided
        if form.password.data:
            user.set_password(form.password.data)
            
        try:
            db.session.commit()
            print(f"User {user_id} updated successfully")
            flash("User updated successfully.", "success")
            return redirect(url_for("users_list"))
        except Exception as e:
            print(f"Database commit error: {e}")
            db.session.rollback()
            flash("Error updating user. Please try again.", "error")
            return render_template("user_edit.html", title="Edit User", form=form, user=user)
    
    # Pre-populate form with existing user data
    if request.method == "GET":
        print(f"GET request - populating form for user {user_id}")
        print(f"User data: name={user.name}, email={user.email}, role={user.role}")
    
    form.name.data = user.name
    form.email.data = user.email
    form.role.data = user.role
    form.company_name.data = user.company_name
    form.phone.data = user.phone
    form.address_line1.data = user.address_line1
    form.address_line2.data = user.address_line2
    form.city.data = user.city
    form.state.data = user.state
    form.postal_code.data = user.postal_code
    form.country.data = user.country
    form.gst_vat.data = user.gst_vat
    form.pan_tax_id.data = user.pan_tax_id
    form.bank_account_name.data = user.bank_account_name
    form.bank_account_number.data = user.bank_account_number
    form.bank_ifsc_swift.data = user.bank_ifsc_swift
    form.manager_id.data = user.parent_id or 0
    
    return render_template("user_edit.html", title="Edit User", form=form, user=user)


@app.route("/test-form")
def test_form():
    """Test route to check form validation"""
    form = UserEditForm()
    print(f"Form fields: {[field.name for field in form]}")
    print(f"Form validation: {form.validate()}")
    print(f"Form errors: {form.errors}")
    return f"Form test - Fields: {[field.name for field in form]}, Valid: {form.validate()}, Errors: {form.errors}"


@app.route("/test-user-update/<int:user_id>", methods=["GET", "POST"])
@login_required
@role_required("admin")
def test_user_update(user_id):
    """Simple test route to update user without form validation"""
    user = User.query.get_or_404(user_id)
    
    if request.method == "POST":
        print(f"Test update for user {user_id}")
        print(f"Request form data: {request.form}")
        
        # Simple update without form validation
        user.name = request.form.get('name', user.name)
        user.email = request.form.get('email', user.email)
        user.phone = request.form.get('phone', user.phone)
        
        try:
            db.session.commit()
            print(f"User {user_id} updated successfully via test route")
            flash("User updated successfully via test route.", "success")
            return redirect(url_for("users_list"))
        except Exception as e:
            print(f"Database commit error in test route: {e}")
            db.session.rollback()
            flash("Error updating user in test route.", "error")
    
    return f"""
    <h2>Test Update for User: {user.name}</h2>
    <form method="post">
        <p>Name: <input type="text" name="name" value="{user.name}"></p>
        <p>Email: <input type="email" name="email" value="{user.email}"></p>
        <p>Phone: <input type="text" name="phone" value="{user.phone or ''}"></p>
        <p><input type="submit" value="Update User"></p>
    </form>
    """


# ----------------------------------------------------------------------------
# Template filters & utilities
# ----------------------------------------------------------------------------


@app.template_filter("kg")
def kg_format(value) -> str:
    try:
        return f"{float(value):,.2f} kg"
    except Exception:
        return "0.00 kg"


@app.template_filter("currency")
def currency_format(value) -> str:
    try:
        return f"PKR {float(value):,.2f}"
    except Exception:
        return "PKR 0.00"


@app.template_filter("date_format")
def date_format(value) -> str:
    try:
        if hasattr(value, 'strftime'):
            return value.strftime('%d %b %Y')
        return str(value)
    except Exception:
        return str(value)


@app.context_processor
def inject_now():
    return {"now": datetime.utcnow()}


if __name__ == "__main__":
    # Initialize database on startup
    with app.app_context():
        ensure_database_and_admin()
    
    # Production configuration
    app.run(debug=True, host='0.0.0.0', port=5000)


