database.py

from flask_sqlalchemy import SQLAlchemy
from flask_login import UserMixin
from datetime import datetime
import uuid

db = SQLAlchemy()

class User(UserMixin, db.Model):
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    phone = db.Column(db.String(15), unique=True, nullable=False)
    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(100))
    address = db.Column(db.String(255))
    password = db.Column(db.String(255), nullable=False)
    role = db.Column(db.String(20), default='user') # 'user', 'admin', 'admin-user'
    wallet_balance = db.Column(db.Float, default=0.0)
    is_active = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

class Transaction(db.Model):
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    user_id = db.Column(db.String(36), db.ForeignKey('user.id'), nullable=False)
    amount = db.Column(db.Float, nullable=False)
    utr_number = db.Column(db.String(50), unique=True, nullable=False)
    screenshot_url = db.Column(db.String(255))
    status = db.Column(db.String(20), default='pending') # 'pending', 'approved', 'rejected'
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    verified_at = db.Column(db.DateTime)

class WalletLedger(db.Model):
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    user_id = db.Column(db.String(36), db.ForeignKey('user.id'), nullable=False)
    type = db.Column(db.String(20), nullable=False) # 'credit' or 'debit'
    amount = db.Column(db.Float, nullable=False)
    description = db.Column(db.String(255))
    balance_after = db.Column(db.Float, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

class AadhaarHistory(db.Model):
    id = db.Column(db.String(36), primary_key=True)
    user_id = db.Column(db.String(36), db.ForeignKey('user.id'), nullable=False)
    timestamp = db.Column(db.String(50))
    data = db.Column(db.Text) # JSON string

class AadhaarDownloadHistory(db.Model):
    """Stores Aadhaar download history for users"""
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    user_id = db.Column(db.String(36), db.ForeignKey('user.id'), nullable=False)
    aadhaar_number = db.Column(db.String(14), nullable=False)  # Masked format: XXXX XXXX XXXX
    eid = db.Column(db.String(50))  # Enrollment ID if available
    is_masked = db.Column(db.Boolean, default=False)
    pdf_content = db.Column(db.Text)  # Base64 encoded PDF content
    downloaded_at = db.Column(db.DateTime, default=datetime.utcnow)
    ip_address = db.Column(db.String(45))  # IPv6 compatible
    user_agent = db.Column(db.String(255))

# ─────────────────────────────────────────────────────
#  SUPPORT TICKET SYSTEM (Improved)
# ─────────────────────────────────────────────────────

class SupportTicket(db.Model):
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    ticket_number = db.Column(db.String(20), unique=True)          # e.g. TKT-0001
    user_id = db.Column(db.String(36), db.ForeignKey('user.id'), nullable=False)
    subject = db.Column(db.String(150))
    message = db.Column(db.Text, nullable=False)                   # First / original message
    category = db.Column(db.String(30), default='other')           # 'payment','wallet','refund','other'
    priority = db.Column(db.String(10), default='medium')          # 'high','medium','low'
    status = db.Column(db.String(20), default='open')              # 'open','pending','resolved','closed'
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationship helper
    messages_rel = db.relationship('TicketMessage', backref='ticket', lazy=True,
                                   order_by='TicketMessage.created_at')


class TicketMessage(db.Model):
    """Stores every message in a ticket thread (user + admin replies)."""
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    ticket_id = db.Column(db.String(36), db.ForeignKey('support_ticket.id'), nullable=False)
    sender = db.Column(db.String(10), nullable=False)   # 'user' or 'admin'
    message = db.Column(db.Text, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)