# server/models.py

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import Numeric
from flask_login import UserMixin
from werkzeug.security import generate_password_hash, check_password_hash
from datetime import datetime, timezone
import uuid
from decimal import Decimal


db = SQLAlchemy()

# Association tables
user_english_roles = db.Table('user_english_roles',
    db.Column('user_id', db.String(36), db.ForeignKey('users.id')),
    db.Column('role_id', db.String(36), db.ForeignKey('english_roles.id'))
)

user_private_roles = db.Table('user_private_roles',
    db.Column('user_id', db.String(36), db.ForeignKey('users.id')),
    db.Column('role_id', db.String(36), db.ForeignKey('private_roles.id'))
)


class User(UserMixin, db.Model):
    __tablename__ = 'users'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(100), unique=True, nullable=False)
    password_hash = db.Column(db.String(200), nullable=False)
    must_change_password = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))
    
    # Separate role relationships for English and Private School
    english_roles = db.relationship('EnglishRole', secondary=user_english_roles, backref=db.backref('users', lazy='dynamic'))
    private_roles = db.relationship('PrivateRole', secondary=user_private_roles, backref=db.backref('users', lazy='dynamic'))
    
    book_receipts = db.relationship('BookPurchaseReceipt', backref='cashier', lazy='dynamic')
    registration_receipts = db.relationship('EnglishRegistrationReceipt', backref='cashier', lazy='dynamic')

    private_enrollment_receipts = db.relationship('PrivateEnrollmentReceipt', backref='cashier', lazy='dynamic')
    private_book_receipts = db.relationship('PrivateBookReceipt', backref='cashier', lazy='dynamic')
    uniform_receipts = db.relationship('PrivateUniformReceipt', backref='cashier', lazy='dynamic')

    def set_password(self, password):
        self.password_hash = generate_password_hash(password)
    
    def check_password(self, password):
        return check_password_hash(self.password_hash, password)
    
class Link_Reset(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(250), nullable=False)
    link = db.Column(db.String(250), unique=True, nullable=False)
    created_at = db.Column(db.DateTime, nullable=False, default=lambda: datetime.now(timezone.utc))

class EnglishRole(db.Model):
    __tablename__ = 'english_roles'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    name = db.Column(db.String(50), unique=True, nullable=False)

class PrivateRole(db.Model):
    __tablename__ = 'private_roles'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    name = db.Column(db.String(50), unique=True, nullable=False)


# --------------------- START: For English Classes ---------------------
class EnglishClass(db.Model):
    __tablename__ = 'english_classes'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    name = db.Column(db.String(100), nullable=False)
    code = db.Column(db.String(20), unique=True, nullable=False)
    levels = db.relationship('EnglishLevel', backref='english_class', lazy='dynamic')

class EnglishLevel(db.Model):
    __tablename__ = 'english_levels'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    name = db.Column(db.String(100), nullable=False)
    class_code = db.Column(db.String(20), db.ForeignKey('english_classes.code'), nullable=False)
    level_order = db.Column(db.Integer, nullable=False)


class EnglishStudent(db.Model):
    __tablename__ = 'english_students'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    name = db.Column(db.String(100), nullable=False)
    phone = db.Column(db.String(20), nullable=False)
    email = db.Column(db.String(100))

    class_code = db.Column(db.String(20))
    level_name = db.Column(db.String(100))
    level_order = db.Column(db.Integer)

    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))
    updated_at = db.Column(db.DateTime,
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc)
    )
    

    is_enrolled = db.Column(db.Boolean)
    english_class_id = db.Column(db.String(36), db.ForeignKey('english_classes.id'))
    
    english_class = db.relationship('EnglishClass', backref='students')
    book_receipts = db.relationship('BookPurchaseReceipt', backref='student', lazy='dynamic')
    registration_receipts = db.relationship('EnglishRegistrationReceipt', backref='student', lazy='dynamic')

    def to_dict(self):
        has_receipts = self.registration_receipts.count() > 0 if self.registration_receipts else False

        # Get receipts as dicts
        receipts = [r.to_dict() for r in self.registration_receipts] if self.registration_receipts else []

        return {
            'id': self.id,
            'name': self.name,
            'phone': self.phone,
            'email': self.email,
            'class_code': self.class_code,
            'level_name': self.level_name,
            'level_order': self.level_order,
            'is_enrolled': self.is_enrolled,
            'has_paid': has_receipts,
            'registration_receipts': receipts,
            'created_at': self.created_at.isoformat(),
            'updated_at': self.updated_at.isoformat()
        }



class EnglishBook(db.Model):
    __tablename__ = 'english_books'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    title = db.Column(db.String(200), nullable=False)
    author = db.Column(db.String(100), nullable=False)

    # REAL DB COLUMN (hidden internal field)
    _price = db.Column("price", Numeric(10, 2), nullable=False)

    quantity = db.Column(db.Integer, default=0)
    class_type = db.Column(db.String(20), default='english')  # 'english' or 'general'
    class_id = db.Column(db.String(36))
    class_name = db.Column(db.String(100))
    
    # Fields for English classes
    level = db.Column(db.String(50))  # e.g., '1', '2', '3' (level_order)
    student_status = db.Column(db.String(20))  # 'fresh' or 'returning'
    
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))
    updated_at = db.Column(db.DateTime,
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc)
    )    

    @property
    def price(self):
        return float(self._price) if self._price is not None else 0.0

    @price.setter
    def price(self, value):
        self._price = Decimal(str(value)) if value is not None else None

class RegistrationFee(db.Model):
    __tablename__ = 'registration_fees' # for english class
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    class_code = db.Column(db.String(20), unique=True, nullable=False)

    _amount = db.Column("amount", Numeric(10, 2), nullable=False)

    @property
    def amount(self):
        return float(self._amount) if self._amount is not None else 0.0

    @amount.setter
    def amount(self, value):
        self._amount = Decimal(str(value)) if value is not None else None

class BookPurchaseReceipt(db.Model):
    __tablename__ = 'book_purchase_receipts' # for english class
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    receipt_number = db.Column(db.String(50), unique=True, nullable=False)
    student_id = db.Column(db.String(36), db.ForeignKey('english_students.id'), nullable=False)

    _total_amount = db.Column("total_amount", db.Numeric(10, 2), nullable=False)

    _discount_amount = db.Column("discount_amount", db.Numeric(10, 2), default=0)
    discount_reason = db.Column(db.String(200))

    session_id = db.Column(db.String(36), db.ForeignKey('english_sessions.id'), nullable=True)

    payment_method = db.Column(db.String(20), nullable=False)  # POS, Cash, Transfer
    cashier_id = db.Column(db.String(36), db.ForeignKey('users.id'), nullable=False)
    items = db.Column(db.JSON, nullable=False)  # Store items as JSON
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))

    @property
    def total_amount(self):
        return float(self._total_amount) if self._total_amount is not None else 0.0

    @total_amount.setter
    def total_amount(self, value):
        self._total_amount = Decimal(str(value)) if value is not None else None
    
    @property
    def discount_amount(self):
        return float(self._discount_amount) if self._discount_amount is not None else 0.0

    @discount_amount.setter
    def discount_amount(self, value):
        self._discount_amount = Decimal(str(value)) if value is not None else None
    
    def to_dict(self):
        # Reconstruct subtotal from stored item totals
        subtotal = sum(float(item.get('total', 0)) for item in (self.items or []))

        # Safe discount percentage calculation
        discount_percentage = 0.0
        if subtotal > 0:
            discount_percentage = (self.discount_amount / subtotal) * 100

        return {
            'id': self.id,
            'receipt_number': self.receipt_number,
            'items': self.items,
            'discount_amount': self.discount_amount,
            'discount_percentage': round(discount_percentage, 2),
            'discount_reason': self.discount_reason,
            'total_amount': self.total_amount,
            'payment_method': self.payment_method,
            'student_name': self.student.name if self.student else 'Unknown',
            'cashier_name': self.cashier.name if self.cashier else 'Unknown',
            'created_at': self.created_at.isoformat()
        }

class EnglishRegistrationReceipt(db.Model):
    __tablename__ = 'english_registration_receipts'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    receipt_number = db.Column(db.String(50), unique=True, nullable=False)
    student_id = db.Column(db.String(36), db.ForeignKey('english_students.id'), nullable=False)
    class_code = db.Column(db.String(20), nullable=True) # CHANGED: Now nullable for payment-only receipts
    level_name = db.Column(db.String(100))
    level_order = db.Column(db.Integer)
    session_id = db.Column(db.String(36), db.ForeignKey('english_sessions.id'), nullable=True)

    _form_fee_amount = db.Column("form_fee_amount", db.Numeric(10, 2), default=0)
    _registration_fee_amount = db.Column("registration_fee_amount", db.Numeric(10, 2), default=0)
    _discount_amount = db.Column("discount_amount", db.Numeric(10, 2), default=0)

    discount_reason = db.Column(db.String(200))
    _total_amount = db.Column("total_amount", db.Numeric(10, 2), nullable=False)

    payment_method = db.Column(db.String(20), nullable=False)
    cashier_id = db.Column(db.String(36), db.ForeignKey('users.id'), nullable=False)
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))

    receipt_type = db.Column(db.String(50), nullable=False)  # 'enrollment', 'class_change', 'payment_only'

    charge_form_fee = db.Column(db.Boolean, default=True)  # Whether to charge form fee
    charge_registration_fee = db.Column(db.Boolean, default=True)  # Whether to charge registration

    # NEW: Track which fees were actually paid
    form_fee_paid = db.Column(db.Boolean, default=False)
    registration_fee_paid = db.Column(db.Boolean, default=False)

    pass_number = db.Column(db.Integer, nullable=True)

    # ----------- FORM FEE ----------------
    @property
    def form_fee_amount(self):
        return float(self._form_fee_amount) if self._form_fee_amount is not None else 0.0

    @form_fee_amount.setter
    def form_fee_amount(self, value):
        self._form_fee_amount = Decimal(str(value)) if value is not None else None

    # ------------ REGISTRATION FEE ---------------
    @property
    def registration_fee_amount(self):
        return float(self._registration_fee_amount) if self._registration_fee_amount is not None else 0.0

    @registration_fee_amount.setter
    def registration_fee_amount(self, value):
        self._registration_fee_amount = Decimal(str(value)) if value is not None else None

    # ------------- DISCOUNT AMOUNT --------------
    @property
    def discount_amount(self):
        return float(self._discount_amount) if self._discount_amount is not None else 0.0

    @discount_amount.setter
    def discount_amount(self, value):
        self._discount_amount = Decimal(str(value)) if value is not None else None

    # ------------- TOTAL AMOUNT --------------
    @property
    def total_amount(self):
        return float(self._total_amount) if self._total_amount is not None else 0.0

    @total_amount.setter
    def total_amount(self, value):
        self._total_amount = Decimal(str(value)) if value is not None else None

    def to_dict(self):
        subtotal = self.form_fee_amount + self.registration_fee_amount
        
        # Avoid division by zero
        discount_percentage = 0.0
        if subtotal > 0:
            discount_percentage = (self.discount_amount / subtotal) * 100

        # Use the explicit receipt_type from database, NOT class_code check
        is_payment_only = self.receipt_type == 'payment_only'
        
        # Override receipt_type if payment-only
        display_type = self.receipt_type
        if is_payment_only:
            display_type = 'payment_only'

        return {
            'id': self.id,
            'receipt_number': self.receipt_number,
            'class_code': self.class_code if self.class_code else '',
            'level_name': self.level_name,
            'session_id': self.session_id,
            'session_name': self.session.name,
            'form_fee_amount': self.form_fee_amount,
            'registration_fee_amount': self.registration_fee_amount,
            'discount_amount': self.discount_amount,
            'discount_percentage': round(discount_percentage, 2),
            'discount_reason': self.discount_reason,
            'total_amount': self.total_amount,
            'payment_method': self.payment_method,
            'student_name': self.student.name,
            'cashier_name': self.cashier.name,
            'created_at': self.created_at.isoformat(),
            'charge_form_fee': self.charge_form_fee,
            'charge_registration_fee': self.charge_registration_fee,
            'receipt_type': display_type,
            'is_payment_only': is_payment_only,
            'form_fee_paid': self.form_fee_paid,
            'registration_fee_paid': self.registration_fee_paid,
            'pass_number': self.pass_number
        }    

class FormStock(db.Model):
    __tablename__ = 'form_stock'
    
    id = db.Column(db.Integer, primary_key=True)
    class_code = db.Column(db.String(50), unique=True, nullable=False)
    class_name = db.Column(db.String(200), nullable=False)
    price = db.Column(db.Numeric(10, 2), nullable=False)
    stock_quantity = db.Column(db.Integer, nullable=False, default=0)
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))
    updated_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc))

class EnglishSession(db.Model):
    __tablename__ = 'english_sessions'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    name = db.Column(db.String(100), unique=False, nullable=False)
    deleted = db.Column(db.Boolean, default=False)

    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))
    updated_at = db.Column(db.DateTime,
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc)
    )  

    registration_receipts = db.relationship('EnglishRegistrationReceipt', backref='session', lazy='dynamic')
    book_purchase_receipts = db.relationship('BookPurchaseReceipt', backref='session', lazy='dynamic')
    
    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'created_at': self.created_at.isoformat(),
            'deleted': self.deleted,
            'updated_at': self.updated_at.isoformat(),
            'time': self.time_info.to_dict() if self.time_info else None,
            'period': self.period.to_dict() if self.period else None
        }
class EnglishSessionTime(db.Model):
    __tablename__ = 'english_session_times'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    session_id = db.Column(db.String(36), db.ForeignKey('english_sessions.id'), unique=True, nullable=False)
    time_text = db.Column(db.String(100), nullable=False)   # free text, e.g. "9:00 AM - 12:00 PM"

    session = db.relationship('EnglishSession', backref=db.backref('time_info', uselist=False, cascade='all, delete-orphan'))

    def to_dict(self):
        return {
            'id': self.id,
            'time_text': self.time_text
        }
        
class EnglishSessionPeriod(db.Model):
    __tablename__ = 'english_session_periods'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    session_id = db.Column(db.String(36), db.ForeignKey('english_sessions.id'), unique=True, nullable=False)
    starting = db.Column(db.DateTime, nullable=False)
    ending = db.Column(db.DateTime, nullable=False)

    session = db.relationship('EnglishSession', backref=db.backref('period', uselist=False, cascade='all, delete-orphan'))

    def to_dict(self):
        return {
            'id': self.id,
            'starting': self.starting.strftime('%Y-%m-%d') if self.starting else None,
            'ending': self.ending.strftime('%Y-%m-%d') if self.ending else None
        }
    
# --------------------- END: For English Classes ---------------------




# --------------------- START: For Private School ---------------------
class PrivateSchoolClass(db.Model):
    __tablename__ = 'private_school_classes'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    name = db.Column(db.String(100), nullable=False)
    code = db.Column(db.String(50), unique=True, nullable=False)


class PrivateFeeItem(db.Model):
    __tablename__ = 'private_fee_items'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    name = db.Column(db.String(100), nullable=False)
    description = db.Column(db.String(255))
    amount = db.Column(Numeric(10, 2), nullable=False)
    
    termly = db.Column(db.Boolean, nullable=False) 
    
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))
    updated_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc))

class PrivateFeeAssignment(db.Model):
    __tablename__ = 'private_fee_assignments'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    fee_item_id = db.Column(db.String(36), db.ForeignKey('private_fee_items.id'), nullable=False)
    class_code = db.Column(db.String(50), db.ForeignKey('private_school_classes.code'), nullable=False)
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))

    # Relationships
    fee_item = db.relationship('PrivateFeeItem', backref='assignments')
    class_obj = db.relationship('PrivateSchoolClass', backref='fee_assignments')


class PrivateSession(db.Model):
    __tablename__ = 'private_sessions'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    name = db.Column(db.String(100), nullable=False)  # e.g., "2024/2025"
    term = db.Column(db.Integer, nullable=False)  # 1, 2, 3
    deleted = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))
    updated_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc))

    pass_color = db.Column(db.String(1))

    enrollment_receipts = db.relationship('PrivateEnrollmentReceipt', backref='session', lazy='dynamic')
    book_receipts = db.relationship('PrivateBookReceipt', backref='session', lazy='dynamic')

    __table_args__ = (db.UniqueConstraint('name', 'term', name='unique_session_term'),)

    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'term': self.term,
            'deleted': self.deleted,
            'created_at': self.created_at.isoformat(),
            'updated_at': self.updated_at.isoformat()
        }


class PrivateStudent(db.Model):
    __tablename__ = 'private_students'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    name = db.Column(db.String(100), nullable=False)
    phone = db.Column(db.String(20))
    email = db.Column(db.String(100))
    class_code = db.Column(db.String(50), db.ForeignKey('private_school_classes.code'), nullable=True)
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))
    updated_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc))

    class_obj = db.relationship('PrivateSchoolClass', backref='students')
    receipts = db.relationship('PrivateEnrollmentReceipt', backref='student', lazy='dynamic')

    book_receipts = db.relationship('PrivateBookReceipt', backref='student', lazy='dynamic')
    uniform_receipts = db.relationship('PrivateUniformReceipt', backref='student', lazy='dynamic')
    
    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'phone': self.phone,
            'email': self.email or '',
            'class_code': self.class_code if self.class_code else None,
            'class_name': self.class_obj.name if self.class_obj else '',
            'created_at': self.created_at.isoformat(),
            'updated_at': self.updated_at.isoformat()
        }


class PrivateEnrollmentReceipt(db.Model):
    __tablename__ = 'private_enrollment_receipts'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    receipt_number = db.Column(db.String(50), unique=True, nullable=False)
    student_id = db.Column(db.String(36), db.ForeignKey('private_students.id'), nullable=False)
    session_id = db.Column(db.String(36), db.ForeignKey('private_sessions.id'), nullable=False)
    term = db.Column(db.Integer, nullable=False)
    class_code = db.Column(db.String(50), db.ForeignKey('private_school_classes.code'), nullable=False)
    
    # Store selected fee items as JSON: [{id, name, amount}]
    items = db.Column(db.JSON, nullable=False)
    
    discount_amount = db.Column(Numeric(10, 2), default=0)
    discount_reason = db.Column(db.String(200))
    total_amount = db.Column(Numeric(10, 2), nullable=False)
    
    payment_method = db.Column(db.String(20), nullable=False)  # Cash, POS, Transfer
    cashier_id = db.Column(db.String(36), db.ForeignKey('users.id'), nullable=False)
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))

    pass_number = db.Column(db.Integer, nullable=True)   # sequential gate pass number per session

    _additional_amount = db.Column("additional_amount", Numeric(10, 2), default=0)
    additional_reason = db.Column(db.String(200))

    @property
    def discount_amount_float(self):
        return float(self.discount_amount) if self.discount_amount else 0.0

    @discount_amount_float.setter
    def discount_amount_float(self, value):
        self.discount_amount = Decimal(str(value)) if value is not None else None

    @property
    def total_amount_float(self):
        return float(self.total_amount) if self.total_amount else 0.0

    @total_amount_float.setter
    def total_amount_float(self, value):
        self.total_amount = Decimal(str(value)) if value is not None else None

    @property
    def additional_amount(self):
        return float(self._additional_amount) if self._additional_amount is not None else 0.0

    @additional_amount.setter
    def additional_amount(self, value):
        self._additional_amount = Decimal(str(value)) if value is not None else Decimal('0')
        
    def to_dict(self):
        subtotal = sum(item['amount'] for item in self.items)
        discount_percentage = (self.discount_amount_float / subtotal * 100) if subtotal > 0 else 0
        
        return {
            'id': self.id,
            'receipt_number': self.receipt_number,
            'student_name': self.student.name,
            'student_phone': self.student.phone,
            'student_email': self.student.email or '',
            'class_code': self.class_code,
            'class_name': self.student.class_obj.name if self.student.class_obj else '',
            'session_name': self.session.name,
            'term': self.term,
            'items': self.items,
            'subtotal': subtotal,
            'discount_amount': self.discount_amount_float,
            'discount_percentage': round(discount_percentage, 2),
            'discount_reason': self.discount_reason,
            'total_amount': self.total_amount_float,
            'payment_method': self.payment_method,
            'cashier_name': self.cashier.name,
            'created_at': self.created_at.isoformat(),
            'pass_number': self.pass_number,
            'additional_amount': self.additional_amount,
            'additional_reason': self.additional_reason,
        }

class PrivateBook(db.Model):
    __tablename__ = 'private_books'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    title = db.Column(db.String(200), nullable=False)
    author = db.Column(db.String(100), nullable=False)
    _price = db.Column("price", Numeric(10, 2), nullable=False)
    quantity = db.Column(db.Integer, default=0)
    class_code = db.Column(db.String(50), db.ForeignKey('private_school_classes.code'), nullable=False)
    class_name = db.Column(db.String(100))  # denormalized for display
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))
    updated_at = db.Column(db.DateTime,
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc)
    )

    @property
    def price(self):
        return float(self._price) if self._price is not None else 0.0

    @price.setter
    def price(self, value):
        self._price = Decimal(str(value)) if value is not None else None

    def to_dict(self):
        return {
            'id': self.id,
            'title': self.title,
            'author': self.author,
            'price': self.price,
            'quantity': self.quantity,
            'class_code': self.class_code,
            'class_name': self.class_name,
            'created_at': self.created_at.isoformat(),
            'updated_at': self.updated_at.isoformat()
        }
class PrivateBookReceipt(db.Model):
    __tablename__ = 'private_book_receipts'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    receipt_number = db.Column(db.String(50), unique=True, nullable=False)
    student_id = db.Column(db.String(36), db.ForeignKey('private_students.id'), nullable=False)
    session_id = db.Column(db.String(36), db.ForeignKey('private_sessions.id'), nullable=False)
    term = db.Column(db.Integer, nullable=False)
    _total_amount = db.Column("total_amount", db.Numeric(10, 2), nullable=False)
    _discount_amount = db.Column("discount_amount", db.Numeric(10, 2), default=0)
    discount_reason = db.Column(db.String(200))
    payment_method = db.Column(db.String(20), nullable=False)
    cashier_id = db.Column(db.String(36), db.ForeignKey('users.id'), nullable=False)
    items = db.Column(db.JSON, nullable=False)  # list of purchased items
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))

    @property
    def total_amount(self):
        return float(self._total_amount) if self._total_amount is not None else 0.0

    @total_amount.setter
    def total_amount(self, value):
        self._total_amount = Decimal(str(value)) if value is not None else None

    @property
    def discount_amount(self):
        return float(self._discount_amount) if self._discount_amount is not None else 0.0

    @discount_amount.setter
    def discount_amount(self, value):
        self._discount_amount = Decimal(str(value)) if value is not None else None

    def to_dict(self):
        subtotal = sum(item.get('total', 0) for item in (self.items or []))
        discount_percentage = (self.discount_amount / subtotal * 100) if subtotal > 0 else 0
        return {
            'id': self.id,
            'receipt_number': self.receipt_number,
            'items': self.items,
            'total_amount': self.total_amount,
            'discount_amount': self.discount_amount,
            'discount_percentage': round(discount_percentage, 2),
            'discount_reason': self.discount_reason,
            'payment_method': self.payment_method,
            'student_name': self.student.name,
            'cashier_name': self.cashier.name,
            'created_at': self.created_at.isoformat()
        }
    

class PrivateUniform(db.Model):
    __tablename__ = 'private_uniforms'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    title = db.Column(db.String(200), nullable=False)
    _price = db.Column("price", Numeric(10, 2), nullable=False)
    quantity = db.Column(db.Integer, default=0)
    class_group = db.Column(db.String(50), nullable=False)  # 'Nursery', 'Primary', 'JSS', 'SSS'
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))
    updated_at = db.Column(db.DateTime,
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc)
    )
    
    # NEW: link to fee item
    fee_item_id = db.Column(db.String(36), db.ForeignKey('private_fee_items.id'), nullable=True)
    fee_item = db.relationship('PrivateFeeItem', backref='linked_uniform', uselist=False)

    @property
    def price(self):
        return float(self._price) if self._price is not None else 0.0

    @price.setter
    def price(self, value):
        self._price = Decimal(str(value)) if value is not None else None

    def to_dict(self):
        return {
            'id': self.id,
            'title': self.title,
            'price': self.price,
            'quantity': self.quantity,
            'class_group': self.class_group,
            'fee_item_id': self.fee_item_id,
            'created_at': self.created_at.isoformat(),
            'updated_at': self.updated_at.isoformat()
        }


class PrivateUniformReceipt(db.Model):
    __tablename__ = 'private_uniform_receipts'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    receipt_number = db.Column(db.String(50), unique=True, nullable=False)
    student_id = db.Column(db.String(36), db.ForeignKey('private_students.id'), nullable=False)
    session_id = db.Column(db.String(36), db.ForeignKey('private_sessions.id'), nullable=False)
    term = db.Column(db.Integer, nullable=False)
    _total_amount = db.Column("total_amount", db.Numeric(10, 2), nullable=False)
    _discount_amount = db.Column("discount_amount", db.Numeric(10, 2), default=0)
    discount_reason = db.Column(db.String(200))
    payment_method = db.Column(db.String(20), nullable=False)
    cashier_id = db.Column(db.String(36), db.ForeignKey('users.id'), nullable=False)
    items = db.Column(db.JSON, nullable=False)  # list of {uniform_id, title, price, quantity, total, class_group}
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))

    @property
    def total_amount(self):
        return float(self._total_amount) if self._total_amount is not None else 0.0

    @total_amount.setter
    def total_amount(self, value):
        self._total_amount = Decimal(str(value)) if value is not None else None

    @property
    def discount_amount(self):
        return float(self._discount_amount) if self._discount_amount is not None else 0.0

    @discount_amount.setter
    def discount_amount(self, value):
        self._discount_amount = Decimal(str(value)) if value is not None else None

    session = db.relationship('PrivateSession', backref='uniform_receipts')
    def to_dict(self):
        subtotal = sum(item.get('total', 0) for item in (self.items or []))
        discount_percentage = (self.discount_amount / subtotal * 100) if subtotal > 0 else 0
        return {
            'id': self.id,
            'receipt_number': self.receipt_number,
            'items': self.items,
            'total_amount': self.total_amount,
            'discount_amount': self.discount_amount,
            'discount_percentage': round(discount_percentage, 2),
            'discount_reason': self.discount_reason,
            'payment_method': self.payment_method,
            'student_name': self.student.name,
            'cashier_name': self.cashier.name,
            'created_at': self.created_at.isoformat()
        }

class PrivateFormStock(db.Model):
    __tablename__ = 'private_form_stock'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    class_group = db.Column(db.String(50), nullable=False)  # Nursery, Primary, JSS, SSS
    _price = db.Column("price", Numeric(10, 2), nullable=False)
    quantity = db.Column(db.Integer, default=0)
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))
    updated_at = db.Column(db.DateTime,
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc)
    )
    
    fee_item_id = db.Column(db.String(36), db.ForeignKey('private_fee_items.id'))
    fee_item = db.relationship('PrivateFeeItem', backref='linked_form', uselist=False)

    @property
    def price(self):
        return float(self._price) if self._price is not None else 0.0

    @price.setter
    def price(self, value):
        self._price = Decimal(str(value)) if value is not None else None

    def to_dict(self):
        return {
            'id': self.id,
            'class_group': self.class_group,
            'price': self.price,
            'quantity': self.quantity,
            'fee_item_id': self.fee_item_id,
            'created_at': self.created_at.isoformat(),
            'updated_at': self.updated_at.isoformat()
        }


class PrivateFormReceipt(db.Model):
    __tablename__ = 'private_form_receipts'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    receipt_number = db.Column(db.String(50), unique=True, nullable=False)
    student_id = db.Column(db.String(36), db.ForeignKey('private_students.id'), nullable=False)
    session_id = db.Column(db.String(36), db.ForeignKey('private_sessions.id'), nullable=False)
    term = db.Column(db.Integer, nullable=False)
    form_id = db.Column(db.String(36), db.ForeignKey('private_form_stock.id'), nullable=False)
    form_price = db.Column(Numeric(10, 2), nullable=False)
    form_class_group = db.Column(db.String(50), nullable=False)
    _discount_amount = db.Column("discount_amount", db.Numeric(10, 2), default=0)
    discount_reason = db.Column(db.String(200))
    total_amount = db.Column(Numeric(10, 2), nullable=False)
    payment_method = db.Column(db.String(20), nullable=False)
    cashier_id = db.Column(db.String(36), db.ForeignKey('users.id'), nullable=False)
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))

    @property
    def discount_amount(self):
        return float(self._discount_amount) if self._discount_amount is not None else 0.0

    @discount_amount.setter
    def discount_amount(self, value):
        self._discount_amount = Decimal(str(value)) if value is not None else None

    @property
    def total_amount_float(self):
        return float(self.total_amount) if self.total_amount else 0.0

    @total_amount_float.setter
    def total_amount_float(self, value):
        self.total_amount = Decimal(str(value)) if value is not None else None

    def to_dict(self):
        discount = self._discount_amount or Decimal('0')
        price = self.form_price or Decimal('0')

        discount_percentage = (discount / price * Decimal('100')) if price > 0 else Decimal('0')

        return {
            'id': self.id,
            'receipt_number': self.receipt_number,
            'student_name': self.student.name,
            'student_phone': self.student.phone,
            'student_email': self.student.email or '',
            'session_name': self.session.name,
            'term': self.term,
            'form_class_group': self.form_class_group,
            'form_price': float(self.form_price),
            'discount_amount': self.discount_amount,
            'discount_percentage': round(float(discount_percentage), 2),
            'discount_reason': self.discount_reason,
            'total_amount': float(self.total_amount),
            'payment_method': self.payment_method,
            'cashier_name': self.cashier.name,
            'created_at': self.created_at.isoformat()
        }

    form = db.relationship('PrivateFormStock', backref='receipts')
    student = db.relationship('PrivateStudent', backref='form_receipts')
    session = db.relationship('PrivateSession', backref='form_receipts')
    cashier = db.relationship('User', backref='private_form_receipts')
# --------------------- END: For Private School ---------------------


# ==================== ISLAMIYYA FEES ====================

class IslamiyyaFeeSetting(db.Model):
    """Singleton table to store current form fee and term fee."""
    __tablename__ = 'islamiyya_fee_settings'
    id = db.Column(db.Integer, primary_key=True)
    form_fee = db.Column(Numeric(10, 2), nullable=False, default=0)
    term_fee = db.Column(Numeric(10, 2), nullable=False, default=0)
    updated_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc))

    @property
    def form_fee_float(self):
        return float(self.form_fee) if self.form_fee else 0.0

    @form_fee_float.setter
    def form_fee_float(self, value):
        self.form_fee = Decimal(str(value)) if value is not None else None

    @property
    def term_fee_float(self):
        return float(self.term_fee) if self.term_fee else 0.0

    @term_fee_float.setter
    def term_fee_float(self, value):
        self.term_fee = Decimal(str(value)) if value is not None else None


class IslamiyyaPaymentReceipt(db.Model):
    __tablename__ = 'islamiyya_payment_receipts'
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    receipt_number = db.Column(db.String(50), unique=True, nullable=False)
    student_id = db.Column(db.String(36), db.ForeignKey('private_students.id'), nullable=False)
    session_id = db.Column(db.String(36), db.ForeignKey('private_sessions.id'), nullable=False)
    term = db.Column(db.Integer, nullable=False)          # from session.term
    student_type = db.Column(db.String(20), nullable=False)  # 'fresh' or 'returning'
    
    # Fee breakdown
    form_fee_charged = db.Column(Numeric(10, 2), default=0)
    term_fee_charged = db.Column(Numeric(10, 2), default=0)
    discount_amount = db.Column(Numeric(10, 2), default=0)
    discount_reason = db.Column(db.String(200))
    total_amount = db.Column(Numeric(10, 2), nullable=False)
    
    payment_method = db.Column(db.String(20), nullable=False)  # Cash, POS, Transfer
    cashier_id = db.Column(db.String(36), db.ForeignKey('users.id'), nullable=False)
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))

    # Relationships
    student = db.relationship('PrivateStudent', backref='islamiyya_receipts')
    session = db.relationship('PrivateSession', backref='islamiyya_receipts')
    cashier = db.relationship('User', backref='islamiyya_receipts')

    @property
    def form_fee_charged_float(self):
        return float(self.form_fee_charged) if self.form_fee_charged else 0.0

    @property
    def term_fee_charged_float(self):
        return float(self.term_fee_charged) if self.term_fee_charged else 0.0

    @property
    def discount_amount_float(self):
        return float(self.discount_amount) if self.discount_amount else 0.0

    @property
    def total_amount_float(self):
        return float(self.total_amount) if self.total_amount else 0.0

    def to_dict(self):
        subtotal = self.form_fee_charged_float + self.term_fee_charged_float
        discount_percentage = (self.discount_amount_float / subtotal * 100) if subtotal > 0 else 0
        return {
            'id': self.id,
            'receipt_number': self.receipt_number,
            'student': {
                'id': self.student.id,
                'name': self.student.name,
                'phone': self.student.phone,
                'email': self.student.email or ''
            },
            'session': {
                'id': self.session.id,
                'name': self.session.name,
                'term': self.term
            },
            'student_type': self.student_type,
            'form_fee': self.form_fee_charged_float,
            'term_fee': self.term_fee_charged_float,
            'subtotal': subtotal,
            'discount_amount': self.discount_amount_float,
            'discount_percentage': round(discount_percentage, 2),
            'discount_reason': self.discount_reason,
            'total_amount': self.total_amount_float,
            'payment_method': self.payment_method,
            'cashier_name': self.cashier.name,
            'created_at': self.created_at.isoformat()
        }