app.py

"""
Secure Medication Navigator Application
Enhanced with proper security, CSRF protection, and complete admin functionality
"""
from flask import Flask, render_template, request, redirect, url_for, flash, jsonify, session, abort, Blueprint
from flask_sqlalchemy import SQLAlchemy
from flask_login import LoginManager, UserMixin, login_user, login_required, logout_user, current_user
from flask_wtf.csrf import CSRFProtect
from flask_limiter import Limiter
from flask_limiter.util import get_remote_address
from flask_talisman import Talisman
from flask_mail import Mail, Message
from flask_caching import Cache
from werkzeug.security import generate_password_hash, check_password_hash
from werkzeug.utils import secure_filename
import secrets
from datetime import datetime, timedelta, timezone, time
import os
from functools import wraps
from sqlalchemy import Numeric
from apscheduler.schedulers.background import BackgroundScheduler
from apscheduler.triggers.cron import CronTrigger
import atexit
import string
import random
import json

# Utility function for timezone-aware datetime
def utc_now():
    return datetime.now(timezone.utc)
from config.secure_config import config
from sms_templates import SMSTemplates
from forms import (
    LoginForm, SignupForm, MedicineForm, CategoryForm, 
    ManufacturerForm, ChangePasswordForm, ProfileForm, ReminderForm,
    FamilyMemberForm, CaregiverPermissionForm, EmergencyContactForm,
    EnhancedFamilyMemberForm, EnhancedCaregiverPermissionForm,
    ForgotPasswordForm, ResetPasswordForm, ForgotPasswordOTPForm,
    VerifyOTPForm, ResetPasswordOTPForm
)

# Initialize Flask app
app = Flask(__name__)

# Load configuration
config_name = os.getenv('FLASK_ENV', 'development')
app.config.from_object(config[config_name])

# Enable template auto-reload for development
app.config['TEMPLATES_AUTO_RELOAD'] = True
app.config['SEND_FILE_MAX_AGE_DEFAULT'] = 0  # Disable caching in development

# Initialize extensions
db = SQLAlchemy(app)
csrf = CSRFProtect(app)
mail = Mail(app)

# Initialize cache with fallback mechanism
def init_cache_with_fallback():
    """Initialize cache with Redis fallback to Simple cache"""
    try:
        # Try to initialize Redis cache first
        import redis
        redis_client = redis.Redis(
            host='localhost', 
            port=6379, 
            decode_responses=True,
            socket_connect_timeout=5,
            socket_timeout=5
        )
        # Test Redis connection
        redis_client.ping()
        
        # If Redis is available, use Redis cache
        app.config.update({
            'CACHE_TYPE': 'RedisCache',
            'CACHE_REDIS_URL': 'redis://localhost:6379/1',
            'CACHE_DEFAULT_TIMEOUT': 300,
            'CACHE_KEY_PREFIX': 'mednavigator_'
        })
        
    except Exception as e:
        # Fall back to Simple cache if Redis is not available
        app.config.update({
            'CACHE_TYPE': 'SimpleCache',
            'CACHE_DEFAULT_TIMEOUT': 300,
            'CACHE_THRESHOLD': 1000
        })
    
    return Cache(app)

cache = init_cache_with_fallback()

# Initialize services
medicine_service = None
notification_service = None
celery = None

# Security middleware
talisman = Talisman(
    app,
    force_https=False,  # Set to True in production
    strict_transport_security=True,
    content_security_policy={
        'default-src': "'self'",
        'script-src': "'self' 'unsafe-inline' cdn.jsdelivr.net cdnjs.cloudflare.com",
        'style-src': "'self' 'unsafe-inline' cdn.jsdelivr.net cdnjs.cloudflare.com",
        'font-src': "'self' cdnjs.cloudflare.com",
        'img-src': "'self' data:",
        'connect-src': "'self' cdn.jsdelivr.net cdnjs.cloudflare.com",  # Allow source maps
    }
)

# Rate limiting with explicit storage configuration
# Configure storage based on environment
storage_uri = "memory://" if config_name == 'development' else os.getenv('REDIS_URL', 'memory://')
limiter = Limiter(
    key_func=get_remote_address,
    app=app,
    default_limits=["1000 per hour"],
    storage_uri=storage_uri,
    strategy="fixed-window"
)

# Login manager
login_manager = LoginManager()
login_manager.init_app(app)
login_manager.login_view = 'auth.login'


# Email Helper Function
login_manager.login_message = 'Please log in to access this page.'
login_manager.login_message_category = 'info'

# Database Models (keeping existing models but with slight enhancements)
class User(UserMixin, db.Model):
    __tablename__ = 'users'
    
    user_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(50), unique=True, nullable=False, index=True)
    email = db.Column(db.String(100), unique=True, nullable=False, index=True)
    password_hash = db.Column(db.String(255), nullable=False)
    first_name = db.Column(db.String(50))
    last_name = db.Column(db.String(50))
    phone_number = db.Column(db.String(20))
    date_of_birth = db.Column(db.Date)
    
    # Additional demographic fields
    gender = db.Column(db.String(20))
    weight = db.Column(db.Float)  # in kg
    height = db.Column(db.Float)  # in cm
    
    # Basic medical information
    allergies = db.Column(db.Text)  # JSON string or comma-separated
    medical_conditions = db.Column(db.Text)  # JSON string or comma-separated
    
    # Profile customization
    profile_picture = db.Column(db.String(255))  # filename/path
    
    # System fields
    is_admin = db.Column(db.Boolean, default=False, nullable=False)
    is_active = db.Column(db.Boolean, default=True, nullable=False)
    email_verified = db.Column(db.Boolean, default=False, nullable=False)
    last_login = db.Column(db.DateTime)
    failed_login_attempts = db.Column(db.Integer, default=0)
    account_locked_until = db.Column(db.DateTime)
    created_at = db.Column(db.DateTime, default=utc_now, nullable=False)
    updated_at = db.Column(db.DateTime, default=utc_now, onupdate=utc_now, nullable=False)
    
    # Relationships
    medicines = db.relationship('Medicine', backref='user', lazy=True, cascade='all, delete-orphan')
    reminders = db.relationship('Reminder', backref='user', lazy=True, cascade='all, delete-orphan')
    
    def get_id(self):
        return str(self.user_id)
    
    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)
    
    def is_account_locked(self):
        if self.account_locked_until:
            return datetime.now(timezone.utc) < self.account_locked_until
        return False
    
    def lock_account(self, minutes=30):
        self.account_locked_until = datetime.now(timezone.utc) + timedelta(minutes=minutes)
        self.failed_login_attempts = 0
    
    @property
    def full_name(self):
        if self.first_name and self.last_name:
            return f"{self.first_name} {self.last_name}"
        return self.username


class Category(db.Model):
    __tablename__ = 'categories'
    
    category_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(100), nullable=False, unique=True, index=True)
    description = db.Column(db.Text)
    icon = db.Column(db.String(50))
    color = db.Column(db.String(7))
    is_active = db.Column(db.Boolean, default=True, nullable=False)
    created_at = db.Column(db.DateTime, default=utc_now, nullable=False)
    updated_at = db.Column(db.DateTime, default=utc_now, onupdate=utc_now, nullable=False)
    
    medicines = db.relationship('Medicine', backref='category', lazy=True)


class Manufacturer(db.Model):
    __tablename__ = 'manufacturers'
    
    manufacturer_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(200), nullable=False, unique=True, index=True)
    contact_person = db.Column(db.String(100))
    email = db.Column(db.String(100))
    phone = db.Column(db.String(20))
    address = db.Column(db.Text)
    website = db.Column(db.String(200))
    country = db.Column(db.String(100))
    license_number = db.Column(db.String(100))
    is_active = db.Column(db.Boolean, default=True, nullable=False)
    created_at = db.Column(db.DateTime, default=utc_now, nullable=False)
    updated_at = db.Column(db.DateTime, default=utc_now, onupdate=utc_now, nullable=False)
    
    medicines = db.relationship('Medicine', backref='manufacturer', lazy=True)


class Medicine(db.Model):
    __tablename__ = 'medicines'
    
    medicine_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(200), nullable=False, index=True)
    generic_name = db.Column(db.String(200))
    brand_name = db.Column(db.String(200))
    dosage_form = db.Column(db.String(50))
    strength = db.Column(db.String(50), nullable=False)
    price = db.Column(Numeric(10, 2), nullable=False, default=0)
    currency = db.Column(db.String(3), default='USD')
    
    # User-focused medicine tracking fields
    reason_for_taking = db.Column(db.Text)
    color = db.Column(db.String(50))
    shape = db.Column(db.String(50))
    medicine_image = db.Column(db.String(255))  # Store image filename/path
    dosage = db.Column(db.String(100), nullable=False)
    frequency = db.Column(db.String(50), nullable=False)
    food_instructions = db.Column(db.String(100))
    
    # Schedule and timing
    start_date = db.Column(db.Date, nullable=False)
    end_date = db.Column(db.Date)
    is_ongoing = db.Column(db.Boolean, default=False)
    timing_1 = db.Column(db.Time)
    timing_2 = db.Column(db.Time)
    timing_3 = db.Column(db.Time)
    timing_4 = db.Column(db.Time)
    
    # Prescription and refill information
    doctor_name = db.Column(db.String(200))
    total_quantity = db.Column(db.Integer, nullable=False)
    unit_of_measurement = db.Column(db.String(20), nullable=False, default='pieces')
    refill_reminder_days = db.Column(db.Integer, default=7)
    
    # Legacy inventory fields (for backward compatibility)
    batch_number = db.Column(db.String(100), index=True)
    lot_number = db.Column(db.String(100))
    stock_quantity = db.Column(db.Integer, default=0, nullable=False)
    minimum_stock_level = db.Column(db.Integer, default=5)
    
    manufacture_date = db.Column(db.Date)
    expiry_date = db.Column(db.Date, index=True)
    purchase_date = db.Column(db.Date)
    
    description = db.Column(db.Text)
    usage_instructions = db.Column(db.Text)
    side_effects = db.Column(db.Text)
    storage_conditions = db.Column(db.String(200))
    prescription_required = db.Column(db.Boolean, default=False)
    
    is_active = db.Column(db.Boolean, default=True, nullable=False)
    is_controlled_substance = db.Column(db.Boolean, default=False)
    
    category_id = db.Column(db.Integer, db.ForeignKey('categories.category_id'), nullable=False, index=True)
    manufacturer_id = db.Column(db.Integer, db.ForeignKey('manufacturers.manufacturer_id'), nullable=False, index=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False, index=True)
    
    created_at = db.Column(db.DateTime, default=utc_now, nullable=False)
    updated_at = db.Column(db.DateTime, default=utc_now, onupdate=utc_now, nullable=False)
    
    reminders = db.relationship('Reminder', backref='medicine', lazy=True, cascade='all, delete-orphan')
    
    # Add database columns for status tracking
    is_expired = db.Column(db.Boolean, default=False, nullable=False, index=True)
    is_low_stock = db.Column(db.Boolean, default=False, nullable=False, index=True)
    
    @property
    def days_to_expiry(self):
        if self.expiry_date:
            return (self.expiry_date - datetime.now().date()).days
        return None
    
    @property
    def days_to_end(self):
        if self.end_date:
            return (self.end_date - datetime.now().date()).days
        return None
    
    @property
    def is_near_expiry(self):
        """Check if medicine is near expiry (within 30 days) but not expired"""
        if self.expiry_date:
            days = self.days_to_expiry
            return days is not None and 0 <= days <= 30
        return False
    
    @property
    def is_treatment_ended(self):
        """Check if treatment has ended"""
        if self.end_date:
            return self.end_date < datetime.now().date()
        return False
    
    @property
    def needs_refill(self):
        """Check if medicine needs refill based on remaining quantity"""
        if self.total_quantity and self.refill_reminder_days:
            return self.total_quantity <= self.refill_reminder_days
        return False
    
    # Template properties for easier access in templates
    @property
    def treatment_ended(self):
        return self.is_treatment_ended
    
    @property
    def refill_needed(self):
        return self.needs_refill
    
    @property
    def status(self):
        if self.is_treatment_ended:
            return 'treatment_ended'
        elif self.expiry_date and self.is_expired:
            return 'expired'
        elif self.is_near_expiry:
            return 'near_expiry'
        elif self.needs_refill:
            return 'refill_needed'
        elif self.is_low_stock:
            return 'low_stock'
        else:
            return 'active'
    
    def update_status(self):
        """Update medicine status based on current date and stock levels"""
        # Update expiry status
        if self.expiry_date:
            self.is_expired = self.expiry_date < datetime.now().date()
        else:
            self.is_expired = False
        
        # Update low stock status
        self.is_low_stock = self.stock_quantity <= self.minimum_stock_level
        
        return self


class Reminder(db.Model):
    __tablename__ = 'reminders'
    
    reminder_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(200), nullable=False)
    description = db.Column(db.Text)
    reminder_type = db.Column(db.String(50), nullable=False)
    reminder_date = db.Column(db.DateTime, nullable=False, index=True)
    is_recurring = db.Column(db.Boolean, default=False)
    recurrence_pattern = db.Column(db.String(50))
    is_completed = db.Column(db.Boolean, default=False)
    is_active = db.Column(db.Boolean, default=True, nullable=False)
    
    email_notification = db.Column(db.Boolean, default=True)
    sms_notification = db.Column(db.Boolean, default=False)
    push_notification = db.Column(db.Boolean, default=True)
    
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False, index=True)
    medicine_id = db.Column(db.Integer, db.ForeignKey('medicines.medicine_id'), index=True)
    
    created_at = db.Column(db.DateTime, default=utc_now, nullable=False)
    updated_at = db.Column(db.DateTime, default=utc_now, onupdate=utc_now, nullable=False)


class AuditLog(db.Model):
    __tablename__ = 'audit_logs'
    
    log_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), index=True)
    action = db.Column(db.String(100), nullable=False, index=True)
    table_name = db.Column(db.String(50), nullable=False)
    record_id = db.Column(db.Integer)
    old_values = db.Column(db.JSON)
    new_values = db.Column(db.JSON)
    ip_address = db.Column(db.String(45))
    user_agent = db.Column(db.String(500))
    timestamp = db.Column(db.DateTime, default=utc_now, nullable=False, index=True)
    
    # Relationships
    user = db.relationship('User', backref='audit_logs')


# Enhanced User Profile Models for Multi-user Support
class UserProfile(db.Model):
    """Extended user profile with demographics and medical information"""
    __tablename__ = 'user_profiles'
    
    profile_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False, unique=True)
    
    # Demographics
    gender = db.Column(db.String(20))
    weight = db.Column(db.Float)  # in kg
    height = db.Column(db.Float)  # in cm
    blood_type = db.Column(db.String(5))
    
    # Medical Information
    allergies = db.Column(db.Text)  # JSON string of allergies
    medical_conditions = db.Column(db.Text)  # JSON string of conditions
    medications_allergic_to = db.Column(db.Text)  # JSON string
    medical_notes = db.Column(db.Text)
    
    # Profile customization
    profile_picture = db.Column(db.String(255))  # filename/path
    theme_preference = db.Column(db.String(20), default='light')
    timezone = db.Column(db.String(50), default='UTC')
    language = db.Column(db.String(10), default='en')
    
    # Privacy settings
    profile_visibility = db.Column(db.String(20), default='private')  # private, family, public
    share_medical_info = db.Column(db.Boolean, default=False)
    
    created_at = db.Column(db.DateTime, default=utc_now, nullable=False)
    updated_at = db.Column(db.DateTime, default=utc_now, onupdate=utc_now, nullable=False)
    
    # Relationships
    user = db.relationship('User', backref=db.backref('profile', uselist=False, cascade='all, delete-orphan'))


class FamilyMember(db.Model):
    """Family members and dependents managed by primary user"""
    __tablename__ = 'family_members'
    
    member_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    primary_user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False, index=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=True, index=True)  # If they have own account
    
    # Basic Info
    first_name = db.Column(db.String(50), nullable=False)
    last_name = db.Column(db.String(50), nullable=False)
    email = db.Column(db.String(100))
    phone_number = db.Column(db.String(20))
    date_of_birth = db.Column(db.Date)
    
    # Relationship
    relationship_type = db.Column(db.String(30), nullable=False)  # child, spouse, parent, dependent, other
    relationship_notes = db.Column(db.Text)
    
    # Demographics (similar to UserProfile)
    gender = db.Column(db.String(20))
    weight = db.Column(db.Float)
    height = db.Column(db.Float)
    blood_type = db.Column(db.String(5))
    
    # Medical Information
    allergies = db.Column(db.Text)
    medical_conditions = db.Column(db.Text)
    medications_allergic_to = db.Column(db.Text)
    medical_notes = db.Column(db.Text)
    
    # Status
    is_active = db.Column(db.Boolean, default=True, nullable=False)
    needs_supervision = db.Column(db.Boolean, default=False)  # For children/dependents
    
    # Profile picture
    profile_picture = db.Column(db.String(255))
    
    created_at = db.Column(db.DateTime, default=utc_now, nullable=False)
    updated_at = db.Column(db.DateTime, default=utc_now, onupdate=utc_now, nullable=False)
    
    # Relationships
    primary_user = db.relationship('User', foreign_keys=[primary_user_id], backref='family_members')
    linked_user = db.relationship('User', foreign_keys=[user_id], backref='linked_family_member', uselist=False)
    
    @property
    def full_name(self):
        return f"{self.first_name} {self.last_name}"
    
    @property
    def age(self):
        if self.date_of_birth:
            today = datetime.now().date()
            return today.year - self.date_of_birth.year - ((today.month, today.day) < (self.date_of_birth.month, self.date_of_birth.day))
        return None


class EmergencyContact(db.Model):
    """Emergency contacts for users and family members"""
    __tablename__ = 'emergency_contacts'
    
    contact_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=True, index=True)
    family_member_id = db.Column(db.Integer, db.ForeignKey('family_members.member_id'), nullable=True, index=True)
    
    # Contact Information
    name = db.Column(db.String(100), nullable=False)
    relationship = db.Column(db.String(50), nullable=False)
    primary_phone = db.Column(db.String(20), nullable=False)
    secondary_phone = db.Column(db.String(20))
    email = db.Column(db.String(100))
    address = db.Column(db.Text)
    
    # Priority and availability
    priority_order = db.Column(db.Integer, default=1)  # 1 = primary, 2 = secondary, etc.
    is_medical_contact = db.Column(db.Boolean, default=False)  # Doctor, hospital, etc.
    availability_notes = db.Column(db.Text)
    
    # Status
    is_active = db.Column(db.Boolean, default=True, nullable=False)
    verified = db.Column(db.Boolean, default=False)
    
    created_at = db.Column(db.DateTime, default=utc_now, nullable=False)
    updated_at = db.Column(db.DateTime, default=utc_now, onupdate=utc_now, nullable=False)
    
    # Relationships
    user = db.relationship('User', backref='emergency_contacts')
    family_member = db.relationship('FamilyMember', backref='emergency_contacts')


class CaregiverPermission(db.Model):
    """Caregiver access permissions for family management"""
    __tablename__ = 'caregiver_permissions'
    
    permission_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    grantor_user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False, index=True)  # Who grants access
    caregiver_user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False, index=True)  # Who gets access
    family_member_id = db.Column(db.Integer, db.ForeignKey('family_members.member_id'), nullable=True, index=True)  # For specific family member
    
    # Permission levels
    can_view_medicines = db.Column(db.Boolean, default=False)
    can_manage_medicines = db.Column(db.Boolean, default=False)
    can_view_reminders = db.Column(db.Boolean, default=False)
    can_manage_reminders = db.Column(db.Boolean, default=False)
    can_view_medical_info = db.Column(db.Boolean, default=False)
    can_manage_profile = db.Column(db.Boolean, default=False)
    can_view_emergency_contacts = db.Column(db.Boolean, default=False)
    can_manage_emergency_contacts = db.Column(db.Boolean, default=False)
    
    # Time-based access
    access_start_date = db.Column(db.Date)
    access_end_date = db.Column(db.Date)
    
    # Status and notes
    is_active = db.Column(db.Boolean, default=True, nullable=False)
    notes = db.Column(db.Text)
    
    created_at = db.Column(db.DateTime, default=utc_now, nullable=False)
    updated_at = db.Column(db.DateTime, default=utc_now, onupdate=utc_now, nullable=False)
    
    # Relationships
    grantor = db.relationship('User', foreign_keys=[grantor_user_id], backref='granted_permissions')
    caregiver = db.relationship('User', foreign_keys=[caregiver_user_id], backref='caregiver_permissions')
    family_member = db.relationship('FamilyMember', backref='caregiver_permissions')
    
    # Constraints to prevent self-permissions
    __table_args__ = (
        db.CheckConstraint('grantor_user_id != caregiver_user_id', name='no_self_permission'),
    )


class PasswordResetToken(db.Model):
    """Model for storing password reset tokens and OTP codes"""
    __tablename__ = 'password_reset_tokens'
    
    token_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False, index=True)
    
    # Token details
    reset_token = db.Column(db.String(255), unique=True, index=True)  # For email reset links
    otp_code = db.Column(db.String(6), index=True)  # For OTP verification
    token_type = db.Column(db.String(20), nullable=False)  # 'email_link' or 'otp'
    
    # Security and tracking
    ip_address = db.Column(db.String(45))
    user_agent = db.Column(db.String(500))
    
    # Status and expiry
    is_used = db.Column(db.Boolean, default=False, nullable=False)
    expires_at = db.Column(db.DateTime, nullable=False, index=True)
    used_at = db.Column(db.DateTime)
    
    created_at = db.Column(db.DateTime, default=utc_now, nullable=False)
    
    # Relationships
    user = db.relationship('User', backref='reset_tokens')
    
    def is_expired(self):
        """Check if token is expired"""
        return datetime.now(timezone.utc) > self.expires_at.replace(tzinfo=timezone.utc)
    
    def is_valid(self):
        """Check if token is valid (not used and not expired)"""
        return not self.is_used and not self.is_expired()
    
    @staticmethod
    def generate_otp():
        """Generate a 6-digit OTP code"""
        return ''.join([str(random.randint(0, 9)) for _ in range(6)])
    
    @staticmethod
    def generate_reset_token():
        """Generate a secure reset token"""
        return secrets.token_urlsafe(64)


@login_manager.user_loader
def load_user(user_id):
    return db.session.get(User, int(user_id))


# Notification Services
class NotificationService:
    """Enhanced notification service with multiple channels"""
    
    @staticmethod
    def send_email_notification(user, subject, template_name, context=None, plain_template=None):
        """Send HTML email notification with template"""
        try:
            if context is None:
                context = {}
            
            # Add common context variables
            context.update({
                'user': user,
                'now': datetime.now().date(),
                'url_for': url_for
            })
            
            # Handle URL generation for email templates
            with app.test_request_context():
                # Render HTML template
                html_body = render_template(f'emails/{template_name}.html', **context)
                
                # Render plain text template if provided
                text_body = None
                if plain_template:
                    text_body = render_template(f'emails/{plain_template}.txt', **context)
            
            msg = Message(
                subject=subject,
                recipients=[user.email],
                html=html_body,
                body=text_body
            )
            
            mail.send(msg)
            app.logger.info(f"Email notification sent to {user.email}: {subject}")
            return True
            
        except Exception as e:
            app.logger.error(f"Failed to send email notification: {e}")
            return False
    
    @staticmethod
    def send_reminder_notification(user, reminder, medicine=None):
        """Send reminder notification via email and SMS"""
        try:
            subject = f"Medication Reminder: {reminder.title}"
            context = {
                'reminder': reminder,
                'medicine': medicine
            }
            
            notifications_sent = []
            
            # Send email notification if enabled
            if reminder.email_notification and user.email:
                email_sent = NotificationService.send_email_notification(
                    user, subject, 'reminder_notification', context, 'reminder_notification'
                )
                if email_sent:
                    notifications_sent.append('email')
            
            # Send SMS notification if enabled
            if reminder.sms_notification and user.phone_number:
                sms_sent = NotificationService.send_sms_notification(user, reminder, medicine)
                if sms_sent:
                    notifications_sent.append('sms')
            
            app.logger.info(f"Reminder notifications sent via {', '.join(notifications_sent)} for reminder {reminder.reminder_id}")
            return len(notifications_sent) > 0
            
        except Exception as e:
            app.logger.error(f"Failed to send reminder notifications: {e}")
            return False
    
    @staticmethod
    def send_sms_notification(user, reminder, medicine=None):
        """Send SMS notification for reminder using templates"""
        try:
            # Check if SMS is enabled
            if not os.getenv('SMS_NOTIFICATIONS_ENABLED', 'False').lower() == 'true':
                app.logger.info("SMS notifications are disabled")
                return False
            
            # Check if user has phone number
            if not user.phone_number:
                app.logger.warning(f"User {user.username} has no phone number for SMS")
                return False
            
            try:
                from twilio.rest import Client
            except ImportError:
                app.logger.error("Twilio package not installed")
                return False
            
            account_sid = os.getenv('TWILIO_ACCOUNT_SID')
            auth_token = os.getenv('TWILIO_AUTH_TOKEN')
            twilio_phone = os.getenv('TWILIO_PHONE_NUMBER')
            
            if not all([account_sid, auth_token, twilio_phone]):
                app.logger.error("Twilio credentials not configured")
                return False
            
            client = Client(account_sid, auth_token)
            
            # Use SMS template for message
            message_body = SMSTemplates.reminder_notification(user, reminder, medicine)
            
            message = client.messages.create(
                body=message_body,
                from_=twilio_phone,
                to=user.phone_number
            )
            
            app.logger.info(f"SMS notification sent to {user.phone_number} for reminder {reminder.reminder_id}")
            return True
            
        except Exception as e:
            app.logger.error(f"Failed to send SMS notification: {e}")
            return False
    
    @staticmethod
    def send_welcome_notification(user):
        """Send welcome email to new users"""
        try:
            subject = "Welcome to Medication Navigator! 🎉"
            return NotificationService.send_email_notification(
                user, subject, 'welcome', {}, 'welcome'
            )
        except Exception as e:
            app.logger.error(f"Failed to send welcome notification: {e}")
            return False
    
    @staticmethod
    def send_expiry_alert(user, medicines):
        """Send expiry alert for medicines"""
        try:
            expired_count = sum(1 for m in medicines if m.is_expired)
            expiring_count = len(medicines) - expired_count
            
            if expired_count > 0 and expiring_count > 0:
                subject = f"⚠️ {expired_count} Expired & {expiring_count} Expiring Soon"
            elif expired_count > 0:
                subject = f"🚨 {expired_count} Medicine(s) Expired"
            else:
                subject = f"📅 {expiring_count} Medicine(s) Expiring Soon"
            
            context = {'medicines': medicines}
            return NotificationService.send_email_notification(
                user, subject, 'expiry_alert', context, 'expiry_alert'
            )
        except Exception as e:
            app.logger.error(f"Failed to send expiry alert: {e}")
            return False
    
    @staticmethod
    def send_low_stock_alert(user, medicines):
        """Send low stock alert for medicines"""
        try:
            subject = f"📦 {len(medicines)} Medicine(s) Running Low"
            context = {'medicines': medicines}
            return NotificationService.send_email_notification(
                user, subject, 'low_stock_digest', context, 'low_stock_digest'
            )
        except Exception as e:
            app.logger.error(f"Failed to send low stock alert: {e}")
            return False
    
    @staticmethod
    def send_password_change_alert(user):
        """Send password change confirmation"""
        try:
            subject = "🔐 Password Changed Successfully"
            context = {'change_time': datetime.now()}
            
            # Send email notification
            email_sent = NotificationService.send_email_notification(
                user, subject, 'password_change', context
            )
            
            # Send SMS notification if user has phone number
            sms_sent = False
            if user.phone_number and os.getenv('SMS_NOTIFICATIONS_ENABLED', 'False').lower() == 'true':
                sms_sent = NotificationService.send_sms_alert(user, 'password_change')
            
            return email_sent or sms_sent
            
        except Exception as e:
            app.logger.error(f"Failed to send password change alert: {e}")
            return False
    
    @staticmethod
    def send_sms_alert(user, alert_type, data=None):
        """Send various SMS alerts using templates"""
        try:
            if not os.getenv('SMS_NOTIFICATIONS_ENABLED', 'False').lower() == 'true':
                return False
            
            if not user.phone_number:
                return False
            
            try:
                from twilio.rest import Client
            except ImportError:
                app.logger.error("Twilio package not installed")
                return False
            
            account_sid = os.getenv('TWILIO_ACCOUNT_SID')
            auth_token = os.getenv('TWILIO_AUTH_TOKEN')
            twilio_phone = os.getenv('TWILIO_PHONE_NUMBER')
            
            if not all([account_sid, auth_token, twilio_phone]):
                return False
            
            client = Client(account_sid, auth_token)
            
            # Generate message based on alert type
            if alert_type == 'welcome':
                message_body = SMSTemplates.welcome_message(user)
            elif alert_type == 'password_change':
                message_body = SMSTemplates.password_change_alert(user)
            elif alert_type == 'expiry' and data:
                message_body = SMSTemplates.expiry_alert(user, data)
            elif alert_type == 'low_stock' and data:
                message_body = SMSTemplates.low_stock_alert(user, data)
            elif alert_type == 'medicine_added' and data:
                message_body = SMSTemplates.medicine_added_confirmation(user, data)
            elif alert_type == 'critical_expiry' and data:
                message_body = SMSTemplates.critical_expiry_alert(user, data)
            else:
                return False
            
            message = client.messages.create(
                body=message_body,
                from_=twilio_phone,
                to=user.phone_number
            )
            
            app.logger.info(f"SMS alert '{alert_type}' sent to {user.phone_number}")
            return True
            
        except Exception as e:
            app.logger.error(f"Failed to send SMS alert '{alert_type}': {e}")
            return False
    
    @staticmethod
    def send_password_reset_email(user, reset_url):
        """Send password reset link via email"""
        try:
            subject = "🔐 Password Reset Request"
            context = {
                'reset_url': reset_url,
                'expiry_hours': 1
            }
            
            return NotificationService.send_email_notification(
                user, subject, 'password_reset', context, 'password_reset'
            )
            
        except Exception as e:
            app.logger.error(f"Failed to send password reset email to {user.email}: {e}")
            return False


class ReminderScheduler:
    """Enhanced reminder scheduling service"""
    
    @staticmethod
    def check_due_reminders():
        """Check for due reminders and send notifications"""
        with app.app_context():
            try:
                now = datetime.now()
                
                # Get reminders due in the next minute
                due_reminders = Reminder.query.filter(
                    Reminder.is_active == True,
                    Reminder.is_completed == False,
                    Reminder.reminder_date <= now,
                    Reminder.reminder_date >= now - timedelta(minutes=1)
                ).all()
                
                notifications_sent = 0
                
                for reminder in due_reminders:
                    user = reminder.user
                    medicine = reminder.medicine if reminder.medicine_id else None
                    
                    # Send notifications using enhanced service
                    if NotificationService.send_reminder_notification(user, reminder, medicine):
                        notifications_sent += 1
                    
                    # Mark as completed if not recurring
                    if not reminder.is_recurring:
                        reminder.is_completed = True
                    else:
                        # Schedule next occurrence for recurring reminders
                        ReminderScheduler.schedule_next_occurrence(reminder)
                    
                    # Log the action
                    log_action('REMINDER_SENT', 'reminders', reminder.reminder_id)
                
                if due_reminders:
                    db.session.commit()
                    app.logger.info(f"Processed {len(due_reminders)} due reminders, sent {notifications_sent} notifications")
                        
            except Exception as e:
                app.logger.error(f"Error checking due reminders: {e}")
                db.session.rollback()
    
    @staticmethod
    def schedule_next_occurrence(reminder):
        """Schedule next occurrence for recurring reminders"""
        try:
            if not reminder.is_recurring or not reminder.recurrence_pattern:
                return
            
            current_date = reminder.reminder_date
            
            if reminder.recurrence_pattern == 'daily':
                next_date = current_date + timedelta(days=1)
            elif reminder.recurrence_pattern == 'weekly':
                next_date = current_date + timedelta(weeks=1)
            elif reminder.recurrence_pattern == 'monthly':
                # Add one month (approximate)
                next_date = current_date + timedelta(days=30)
            else:
                return
            
            reminder.reminder_date = next_date
            app.logger.info(f"Scheduled next occurrence for reminder {reminder.reminder_id} at {next_date}")
            
        except Exception as e:
            app.logger.error(f"Error scheduling next occurrence for reminder {reminder.reminder_id}: {e}")
    
    @staticmethod
    def send_daily_expiry_alerts():
        """Send daily expiry alerts to users"""
        with app.app_context():
            try:
                now = datetime.now().date()
                
                # Get all users
                users = User.query.filter_by(is_active=True).all()
                alerts_sent = 0
                
                for user in users:
                    # Get user's expired and expiring medicines
                    user_medicines = Medicine.query.filter_by(user_id=user.user_id)
                    
                    expiring_medicines = user_medicines.filter(
                        db.or_(
                            Medicine.is_expired == True,
                            db.and_(
                                Medicine.expiry_date <= now + timedelta(days=7),
                                Medicine.expiry_date > now
                            )
                        )
                    ).all()
                    
                    if expiring_medicines:
                        if NotificationService.send_expiry_alert(user, expiring_medicines):
                            alerts_sent += 1
                
                app.logger.info(f"Sent daily expiry alerts to {alerts_sent} users")
                
            except Exception as e:
                app.logger.error(f"Error sending daily expiry alerts: {e}")
    
    @staticmethod
    def send_weekly_low_stock_digest():
        """Send weekly low stock digest to users"""
        with app.app_context():
            try:
                # Get all users
                users = User.query.filter_by(is_active=True).all()
                digests_sent = 0
                
                for user in users:
                    # Get user's low stock medicines
                    low_stock_medicines = Medicine.query.filter_by(
                        user_id=user.user_id,
                        is_low_stock=True
                    ).all()
                    
                    if low_stock_medicines:
                        if NotificationService.send_low_stock_alert(user, low_stock_medicines):
                            digests_sent += 1
                
                app.logger.info(f"Sent weekly low stock digest to {digests_sent} users")
                
            except Exception as e:
                app.logger.error(f"Error sending weekly low stock digest: {e}")


# Medicine Status Automation
def recalc_all_medicine_status():
    """Daily job to recalculate all medicine status (expired/low-stock)"""
    with app.app_context():
        try:
            medicines = Medicine.query.all()
            updated_count = 0
            
            for medicine in medicines:
                old_expired = medicine.is_expired
                old_low_stock = medicine.is_low_stock
                
                # Update status
                medicine.update_status()
                
                # Check if status changed
                if (old_expired != medicine.is_expired or 
                    old_low_stock != medicine.is_low_stock):
                    updated_count += 1
            
            db.session.commit()
            app.logger.info(f"Updated status for {updated_count} medicines")
            
            # Send low stock digest if there are low stock medicines
            send_low_stock_digest()
            
        except Exception as e:
            db.session.rollback()
            app.logger.error(f"Error updating medicine status: {e}")


def send_low_stock_digest():
    """Send weekly low stock digest to users"""
    try:
        # Get all users with low stock medicines
        users_with_low_stock = db.session.query(User).join(
            Medicine, User.user_id == Medicine.user_id
        ).filter(Medicine.is_low_stock == True).distinct().all()
        
        for user in users_with_low_stock:
            low_stock_medicines = Medicine.query.filter_by(
                user_id=user.user_id, is_low_stock=True
            ).all()
            
            if low_stock_medicines:
                NotificationService.send_low_stock_alert(user, low_stock_medicines)
        
        app.logger.info(f"Sent low stock digest to {len(users_with_low_stock)} users")
        
    except Exception as e:
        app.logger.error(f"Error sending low stock digest: {e}")


# Initialize scheduler
scheduler = None
if os.getenv('SCHEDULER_ENABLED', 'True').lower() == 'true':
    scheduler = BackgroundScheduler()
    
    # Check reminders every minute
    scheduler.add_job(
        func=ReminderScheduler.check_due_reminders,
        trigger=CronTrigger(second=0),  # Run every minute
        id='check_reminders',
        name='Check due reminders',
        replace_existing=True
    )
    
    # Daily medicine status recalculation at 00:05
    scheduler.add_job(
        id='recalc_medicine_status',
        func=recalc_all_medicine_status,
        trigger=CronTrigger(hour=0, minute=5),  # Daily at 00:05
        name='Recalculate medicine status',
        replace_existing=True
    )
    
    # Daily expiry alerts at 08:00
    scheduler.add_job(
        id='daily_expiry_alerts',
        func=ReminderScheduler.send_daily_expiry_alerts,
        trigger=CronTrigger(hour=8, minute=0),  # Daily at 08:00
        name='Send daily expiry alerts',
        replace_existing=True
    )
    
    # Weekly low stock digest on Mondays at 09:00
    scheduler.add_job(
        id='weekly_low_stock_digest',
        func=ReminderScheduler.send_weekly_low_stock_digest,
        trigger=CronTrigger(day_of_week=0, hour=9, minute=0),  # Monday at 09:00
        name='Send weekly low stock digest',
        replace_existing=True
    )


# Decorators
def admin_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if not current_user.is_authenticated or not current_user.is_admin:
            flash('Admin access required.', 'error')
            return redirect(url_for('main.index'))
        return f(*args, **kwargs)
    return decorated_function


def user_required(f):
    """Decorator to ensure only regular users (non-admin) can access certain routes"""
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if not current_user.is_authenticated:
            flash('Please log in to access this page.', 'info')
            return redirect(url_for('auth.login'))
        if current_user.is_admin:
            # Only show flash message if not already on an admin page
            if not request.path.startswith('/admin'):
                flash('This feature is for regular users only. Use admin panel instead.', 'info')
            return redirect(url_for('admin.dashboard'))
        return f(*args, **kwargs)
    return decorated_function


def log_action(action, table_name, record_id=None, old_values=None, new_values=None):
    """Log user actions for audit trail"""
    try:
        log = AuditLog(
            user_id=current_user.user_id if current_user.is_authenticated else None,
            action=action,
            table_name=table_name,
            record_id=record_id,
            old_values=old_values,
            new_values=new_values,
            ip_address=request.remote_addr,
            user_agent=request.headers.get('User-Agent')
        )
        db.session.add(log)
        db.session.commit()
    except Exception as e:
        app.logger.error(f"Audit log error: {e}")


# Main routes
main = Blueprint('main', __name__)

@main.route('/')
def index():
    if current_user.is_authenticated:
        # Role-based dashboard routing
        if current_user.is_admin:
            # Redirect admin users to admin dashboard
            return redirect(url_for('admin.dashboard'))
        else:
            # Regular user dashboard
            return redirect(url_for('main.user_dashboard'))
    return render_template('index.html')

@main.route('/dashboard')
@login_required
def user_dashboard():
    """Optimized user dashboard with caching"""
    if current_user.is_admin:
        # Prevent admin from accessing user dashboard
        return redirect(url_for('admin.dashboard'))
    
    # Use optimized service for dashboard statistics
    global medicine_service
    if not medicine_service:
        from services.medicine_service import MedicineService
        medicine_service = MedicineService(db, cache)
    
    # Get cached dashboard statistics
    stats = medicine_service.get_dashboard_stats_cached(current_user.user_id)
    
    # Get recent medicines with optimized query
    recent_medicines, _ = medicine_service.get_user_medicines_optimized(
        current_user.user_id, 
        page=1, 
        per_page=6
    )
    
    # Get medicines by status in single batch query
    status_medicines = medicine_service.get_medicines_by_status_batch(
        current_user.user_id,
        ['expired', 'near_expiry', 'low_stock', 'ending_soon'],
        limit=5
    )
    
    # Get upcoming reminders (cached query)
    cache_key = f"upcoming_reminders_{current_user.user_id}"
    upcoming_reminders = cache.get(cache_key)
    
    if upcoming_reminders is None:
        upcoming_reminders = Reminder.query.filter_by(
            user_id=current_user.user_id, 
            is_completed=False, 
            is_active=True
        ).filter(
            Reminder.reminder_date >= datetime.now(),
            Reminder.reminder_date <= datetime.now() + timedelta(days=7)
        ).order_by(Reminder.reminder_date).limit(5).all()
        
        # Cache for 5 minutes
        cache.set(cache_key, upcoming_reminders, timeout=300)
    
    return render_template('dashboard.html', 
                         total_medicines=stats['total_medicines'],
                         ending_soon=stats['ending_soon'],
                         ongoing_treatments=stats['ongoing_treatments'],
                         refill_needed=0,  # Will be calculated from status_medicines
                         upcoming_doses_today=0,  # Simplified for now
                         recent_medicines=recent_medicines,
                         total_reminders=stats['total_reminders'],
                         upcoming_reminders=upcoming_reminders,
                         overdue_reminders=stats['overdue_reminders'],
                         expired_medicines=status_medicines.get('expired', []),
                         near_expiry_medicines=status_medicines.get('near_expiry', []),
                         low_stock_medicines=status_medicines.get('low_stock', []))


@main.route('/help')
def help_center():
    """Help Center page"""
    return render_template('help_center.html')


@main.route('/user-guide')
def user_guide():
    """User Guide page"""
    return render_template('user_guide.html')


@main.route('/privacy')
def privacy_policy():
    """Privacy Policy page"""
    return render_template('privacy_policy.html')


@main.route('/terms')
def terms_of_service():
    """Terms of Service page"""
    return render_template('terms_of_service.html')


@main.route('/contact')
def contact_us():
    """Contact Us page"""
    return render_template('contact_us.html')


# AI Assistant routes
@main.route('/assistant')
@login_required
def medication_assistant():
    """Medication Assistant main page"""
    return render_template('medication_assistant.html')


@main.route('/assistant/ask', methods=['POST'])
@login_required
def ask_assistant():
    """Handle AI assistant questions via AJAX"""
    try:
        from services.ai_assistant_service import get_medication_assistant
        
        data = request.get_json()
        if not data or not data.get('question'):
            return jsonify({
                'success': False,
                'error': 'No question provided'
            })
        
        question = data.get('question', '').strip()
        if len(question) < 3:
            return jsonify({
                'success': False,
                'error': 'Please provide a more detailed question'
            })
        
        # Get user context for personalized responses
        user_context = {
            'current_medications': [],
            'conditions': [],
            'allergies': []
        }
        
        # Add user's current medications to context
        user_medicines = Medicine.query.filter_by(
            user_id=current_user.user_id,
            is_active=True
        ).all()
        user_context['current_medications'] = [med.name for med in user_medicines]
        
        # Add user's medical conditions and allergies if available
        if current_user.medical_conditions:
            try:
                if current_user.medical_conditions.startswith('['):
                    import json
                    user_context['conditions'] = json.loads(current_user.medical_conditions)
                else:
                    user_context['conditions'] = [cond.strip() for cond in current_user.medical_conditions.split(',') if cond.strip()]
            except:
                pass
        
        if current_user.allergies:
            try:
                if current_user.allergies.startswith('['):
                    import json
                    user_context['allergies'] = json.loads(current_user.allergies)
                else:
                    user_context['allergies'] = [allergy.strip() for allergy in current_user.allergies.split(',') if allergy.strip()]
            except:
                pass
        
        # Get AI response
        assistant = get_medication_assistant()
        response = assistant.ask_medication_question(question, user_context)
        
        # Add timestamp
        if response.get('success'):
            response['timestamp'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        
        return jsonify(response)
        
    except Exception as e:
        app.logger.error(f"AI Assistant error: {e}")
        return jsonify({
            'success': False,
            'error': 'An error occurred while processing your question. Please try again.'
        })


@main.route('/assistant/medication-info/<medication_name>')
@login_required
def get_medication_info(medication_name):
    """Get detailed information about a specific medication"""
    try:
        from services.ai_assistant_service import get_medication_assistant
        
        assistant = get_medication_assistant()
        response = assistant.get_medication_info(medication_name)
        
        if response.get('success'):
            response['timestamp'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        
        return jsonify(response)
        
    except Exception as e:
        app.logger.error(f"Medication info error: {e}")
        return jsonify({
            'success': False,
            'error': 'An error occurred while fetching medication information.'
        })


@main.route('/assistant/drug-interactions', methods=['POST'])
@login_required  
def check_drug_interactions():
    """Check for drug interactions between medications"""
    try:
        from services.ai_assistant_service import get_medication_assistant
        
        data = request.get_json()
        medications = data.get('medications', [])
        
        if len(medications) < 2:
            return jsonify({
                'success': False,
                'error': 'Please provide at least 2 medications to check for interactions'
            })
        
        assistant = get_medication_assistant()
        response = assistant.check_drug_interactions(medications)
        
        if response.get('success'):
            response['timestamp'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        
        return jsonify(response)
        
    except Exception as e:
        app.logger.error(f"Drug interaction check error: {e}")
        return jsonify({
            'success': False,
            'error': 'An error occurred while checking drug interactions.'
        })


# Authentication routes
auth = Blueprint('auth', __name__, url_prefix='/auth')

@auth.route('/login', methods=['GET', 'POST'])
@limiter.limit("5 per minute")
def login():
    form = LoginForm()
    if form.validate_on_submit():
        # Check if input is email or username
        username_or_email = form.username.data.strip()
        
        # Try to find user by email first, then by username
        if '@' in username_or_email:
            user = User.query.filter_by(email=username_or_email, is_active=True).first()
        else:
            user = User.query.filter_by(username=username_or_email, is_active=True).first()
        
        if user and user.is_account_locked():
            flash('Account is locked. Please try again later.', 'error')
            return render_template('login.html', form=form)
        
        if user and user.check_password(form.password.data):
            user.failed_login_attempts = 0
            user.last_login = datetime.now(timezone.utc)
            user.account_locked_until = None
            db.session.commit()
            
            login_user(user, remember=form.remember_me.data)
            log_action('LOGIN', 'users', user.user_id)
            flash('Login successful!', 'success')
            
            next_page = request.args.get('next')
            return redirect(next_page) if next_page else redirect(url_for('main.index'))
        else:
            if user:
                user.failed_login_attempts += 1
                if user.failed_login_attempts >= 5:
                    user.lock_account()
                    flash('Too many failed attempts. Account locked for 30 minutes.', 'error')
                else:
                    flash(f'Invalid credentials. {5 - user.failed_login_attempts} attempts remaining.', 'error')
                db.session.commit()
            else:
                flash('Invalid username or password.', 'error')
    
    return render_template('login.html', form=form)


@auth.route('/signup', methods=['GET', 'POST'])
@limiter.limit("3 per minute")
def signup():
    form = SignupForm()
    if form.validate_on_submit():
        if User.query.filter_by(username=form.username.data).first():
            flash('Username already exists.', 'error')
            return render_template('signup.html', form=form)
        
        if User.query.filter_by(email=form.email.data).first():
            flash('Email already registered.', 'error')
            return render_template('signup.html', form=form)
        
        user = User(
            username=form.username.data,
            email=form.email.data,
            first_name=form.first_name.data,
            last_name=form.last_name.data,
            phone_number=form.phone_number.data,
            date_of_birth=form.date_of_birth.data
        )
        user.set_password(form.password.data)
        db.session.add(user)
        db.session.commit()
        
        # Send welcome notification
        try:
            NotificationService.send_welcome_notification(user)
            app.logger.info(f"Welcome notification sent to {user.email}")
        except Exception as e:
            app.logger.error(f"Failed to send welcome notification to {user.email}: {e}")
        
        log_action('CREATE', 'users', user.user_id)
        flash('Registration successful! Please check your email for a welcome message and login.', 'success')
        return redirect(url_for('auth.login'))
    
    return render_template('signup.html', form=form)


@auth.route('/logout')
@login_required
def logout():
    log_action('LOGOUT', 'users', current_user.user_id)
    logout_user()
    flash('You have been logged out.', 'info')
    return redirect(url_for('main.index'))


@auth.route('/profile', methods=['GET', 'POST'])
@login_required
def profile():
    form = ProfileForm(obj=current_user)
    
    if form.validate_on_submit():
        old_values = {
            'username': current_user.username,
            'email': current_user.email,
            'first_name': current_user.first_name,
            'last_name': current_user.last_name
        }
        
        # Check if username/email already exists (excluding current user)
        if form.username.data != current_user.username:
            existing_user = User.query.filter_by(username=form.username.data).first()
            if existing_user:
                flash('Username already exists.', 'error')
                return render_template('profile.html', form=form)
        
        if form.email.data != current_user.email:
            existing_email = User.query.filter_by(email=form.email.data).first()
            if existing_email:
                flash('Email already registered.', 'error')
                return render_template('profile.html', form=form)
        
        form.populate_obj(current_user)
        current_user.updated_at = datetime.now(timezone.utc)
        db.session.commit()
        
        new_values = {
            'username': current_user.username,
            'email': current_user.email,
            'first_name': current_user.first_name,
            'last_name': current_user.last_name
        }
        
        log_action('UPDATE', 'users', current_user.user_id, old_values, new_values)
        flash('Profile updated successfully!', 'success')
        return redirect(url_for('main.index'))
    
    return render_template('profile.html', form=form)


@auth.route('/change-password', methods=['GET', 'POST'])
@login_required
@limiter.limit("5 per minute")
def change_password():
    form = ChangePasswordForm()
    
    if form.validate_on_submit():
        if not current_user.check_password(form.current_password.data):
            flash('Current password is incorrect.', 'error')
            return render_template('change_password.html', form=form)
        
        current_user.set_password(form.new_password.data)
        current_user.updated_at = datetime.now(timezone.utc)
        db.session.commit()
        
        # Send password change notification
        try:
            NotificationService.send_password_change_alert(current_user)
            app.logger.info(f"Password change notification sent to {current_user.email}")
        except Exception as e:
            app.logger.error(f"Failed to send password change notification: {e}")
        
        log_action('UPDATE', 'users', current_user.user_id, {'action': 'password_changed'})
        flash('Password changed successfully! A confirmation has been sent to your email.', 'success')
        return redirect(url_for('auth.profile'))
    
    return render_template('change_password.html', form=form)


# Forgot Password Routes
@auth.route('/forgot-password', methods=['GET', 'POST'])
@limiter.limit("3 per minute")
def forgot_password():
    """Forgot password with choice of reset method"""
    form = ForgotPasswordOTPForm()
    
    if form.validate_on_submit():
        user = User.query.filter_by(email=form.email.data).first()
        if user:
            reset_method = form.reset_method.data
            
            if reset_method == 'otp':
                # Check for recent OTP requests (30-second limit)
                recent_otp = PasswordResetToken.query.filter_by(
                    user_id=user.user_id,
                    token_type='otp'
                ).filter(
                    PasswordResetToken.created_at >= datetime.now(timezone.utc) - timedelta(seconds=30)
                ).first()
                
                if recent_otp:
                    time_remaining = 30 - int((datetime.now(timezone.utc) - recent_otp.created_at.replace(tzinfo=timezone.utc)).total_seconds())
                    flash(f'Please wait {time_remaining} seconds before requesting another OTP.', 'warning')
                    return render_template('forgot_password.html', form=form)
                
                # Clean up old tokens for this user
                PasswordResetToken.query.filter_by(user_id=user.user_id).delete()
                
                # Generate OTP
                otp_code = PasswordResetToken.generate_otp()
                token = PasswordResetToken(
                    user_id=user.user_id,
                    otp_code=otp_code,
                    token_type='otp',
                    ip_address=request.remote_addr,
                    user_agent=request.headers.get('User-Agent', ''),
                    expires_at=datetime.now(timezone.utc) + timedelta(minutes=5)  # 5 minute expiry
                )
                db.session.add(token)
                db.session.commit()
                
                # Send OTP email
                try:
                    if notification_service.send_otp_email(user, otp_code):
                        flash('OTP has been sent to your email address. Please check your inbox.', 'success')
                        return redirect(url_for('auth.verify_otp', email=user.email))
                    else:
                        flash('Failed to send OTP. Please try again.', 'error')
                        return render_template('forgot_password.html', form=form)
                except Exception as e:
                    app.logger.error(f"Failed to send OTP email to {user.email}: {e}")
                    flash('Failed to send OTP. Please try again.', 'error')
                    return render_template('forgot_password.html', form=form)
            
            else:  # email link reset
                # Clean up old tokens for this user
                PasswordResetToken.query.filter_by(user_id=user.user_id).delete()
                
                # Generate reset token
                reset_token = PasswordResetToken.generate_reset_token()
                token = PasswordResetToken(
                    user_id=user.user_id,
                    reset_token=reset_token,
                    token_type='email_link',
                    ip_address=request.remote_addr,
                    user_agent=request.headers.get('User-Agent', ''),
                    expires_at=datetime.now(timezone.utc) + timedelta(hours=1)  # 1 hour expiry
                )
                db.session.add(token)
                db.session.commit()
                
                # Send reset email
                try:
                    reset_url = url_for('auth.reset_password', token=reset_token, _external=True)
                    if NotificationService.send_password_reset_email(user, reset_url):
                        flash('Password reset link has been sent to your email address.', 'success')
                        return redirect(url_for('auth.login'))
                    else:
                        flash('Failed to send reset email. Please try again.', 'error')
                        return render_template('forgot_password.html', form=form)
                except Exception as e:
                    app.logger.error(f"Failed to send reset email to {user.email}: {e}")
                    flash('Failed to send reset email. Please try again.', 'error')
                    return render_template('forgot_password.html', form=form)
        else:
            # Don't reveal if email exists or not for security
            flash('If that email address exists, you will receive reset instructions.', 'info')
            return redirect(url_for('auth.login'))
    
    return render_template('forgot_password.html', form=form)


@auth.route('/verify-otp', methods=['GET', 'POST'])
@limiter.limit("5 per minute")
def verify_otp():
    """Verify OTP code"""
    email = request.args.get('email') or request.form.get('email')
    if not email:
        flash('Invalid request.', 'error')
        return redirect(url_for('auth.forgot_password'))
    
    # Get the latest OTP token for timing info
    user = User.query.filter_by(email=email).first()
    otp_token = None
    if user:
        otp_token = PasswordResetToken.query.filter_by(
            user_id=user.user_id,
            token_type='otp',
            is_used=False
        ).order_by(PasswordResetToken.created_at.desc()).first()
    
    form = VerifyOTPForm()
    
    if form.validate_on_submit():
        if not user:
            flash('Invalid request.', 'error')
            return redirect(url_for('auth.forgot_password'))
        
        # Find valid OTP token
        token = PasswordResetToken.query.filter_by(
            user_id=user.user_id,
            otp_code=form.otp.data,
            token_type='otp',
            is_used=False
        ).first()
        
        if token and token.is_valid():
            # Mark token as used
            token.is_used = True
            token.used_at = datetime.now(timezone.utc)
            db.session.commit()
            
            flash('OTP verified successfully! You can now reset your password.', 'success')
            return redirect(url_for('auth.reset_password_otp', user_id=user.user_id, token_id=token.token_id))
        else:
            flash('Invalid or expired OTP. Please try again.', 'error')
    
    return render_template('verify_otp.html', form=form, email=email, otp_token=otp_token)


@auth.route('/resend-otp', methods=['POST'])
@limiter.limit("3 per minute")
def resend_otp():
    """Resend OTP with 30-second rate limit"""
    email = request.form.get('email')
    if not email:
        return jsonify({'success': False, 'message': 'Email is required.'}), 400
    
    user = User.query.filter_by(email=email).first()
    if not user:
        return jsonify({'success': False, 'message': 'Invalid request.'}), 400
    
    # Check for recent OTP requests (30-second limit)
    recent_otp = PasswordResetToken.query.filter_by(
        user_id=user.user_id,
        token_type='otp'
    ).filter(
        PasswordResetToken.created_at >= datetime.now(timezone.utc) - timedelta(seconds=30)
    ).first()
    
    if recent_otp:
        time_remaining = 30 - int((datetime.now(timezone.utc) - recent_otp.created_at.replace(tzinfo=timezone.utc)).total_seconds())
        return jsonify({
            'success': False, 
            'message': f'Please wait {time_remaining} seconds before requesting another OTP.',
            'wait_time': time_remaining
        }), 429
    
    # Clean up old tokens and generate new OTP
    PasswordResetToken.query.filter_by(user_id=user.user_id, token_type='otp').delete()
    
    otp_code = PasswordResetToken.generate_otp()
    token = PasswordResetToken(
        user_id=user.user_id,
        otp_code=otp_code,
        token_type='otp',
        ip_address=request.remote_addr,
        user_agent=request.headers.get('User-Agent', ''),
        expires_at=datetime.now(timezone.utc) + timedelta(minutes=5)  # 5 minute expiry
    )
    db.session.add(token)
    db.session.commit()
    
    # Send OTP email
    try:
        notification_service.send_otp_email(user, otp_code)
        return jsonify({
            'success': True, 
            'message': 'New OTP has been sent to your email address.'
        })
    except Exception as e:
        app.logger.error(f"Failed to send OTP email to {user.email}: {e}")
        return jsonify({'success': False, 'message': 'Failed to send OTP. Please try again.'}), 500


@auth.route('/reset-password/<token>')
@limiter.limit("5 per minute")
def reset_password(token):
    """Reset password using email link token"""
    reset_token = PasswordResetToken.query.filter_by(
        reset_token=token,
        token_type='email_link',
        is_used=False
    ).first()
    
    if not reset_token or not reset_token.is_valid():
        flash('Invalid or expired reset link.', 'error')
        return redirect(url_for('auth.forgot_password'))
    
    form = ResetPasswordForm()
    
    if form.validate_on_submit():
        # Update user password
        user = reset_token.user
        user.set_password(form.password.data)
        user.updated_at = datetime.now(timezone.utc)
        
        # Mark token as used
        reset_token.is_used = True
        reset_token.used_at = datetime.now(timezone.utc)
        
        db.session.commit()
        
        # Send confirmation email
        try:
            NotificationService.send_password_change_alert(user)
        except Exception as e:
            app.logger.error(f"Failed to send password change alert: {e}")
        
        log_action('UPDATE', 'users', user.user_id, {'action': 'password_reset_via_email'})
        flash('Your password has been reset successfully! You can now log in.', 'success')
        return redirect(url_for('auth.login'))
    
    return render_template('reset_password.html', form=form, token=token)


@auth.route('/reset-password-otp/<int:user_id>/<int:token_id>', methods=['GET', 'POST'])
@limiter.limit("5 per minute")
def reset_password_otp(user_id, token_id):
    """Reset password after OTP verification"""
    # Verify the token was used for OTP verification
    token = PasswordResetToken.query.filter_by(
        token_id=token_id,
        user_id=user_id,
        token_type='otp',
        is_used=True
    ).first()
    
    if not token:
        flash('Invalid request.', 'error')
        return redirect(url_for('auth.forgot_password'))
    
    # Check if token was used recently (within 5 minutes of OTP verification)
    session_expiry = None
    if token.used_at:
        session_expiry = token.used_at.replace(tzinfo=timezone.utc) + timedelta(minutes=5)
        if datetime.now(timezone.utc) > session_expiry:
            flash('Reset session expired. Please start over.', 'error')
            return redirect(url_for('auth.forgot_password'))
    
    form = ResetPasswordOTPForm()
    
    if form.validate_on_submit():
        user = User.query.get(user_id)
        if user:
            # Update password
            user.set_password(form.password.data)
            user.updated_at = datetime.now(timezone.utc)
            db.session.commit()
            
            # Clean up all reset tokens for this user
            PasswordResetToken.query.filter_by(user_id=user.user_id).delete()
            db.session.commit()
            
            # Send confirmation email
            try:
                NotificationService.send_password_change_alert(user)
            except Exception as e:
                app.logger.error(f"Failed to send password change alert: {e}")
            
            log_action('UPDATE', 'users', user.user_id, {'action': 'password_reset_via_otp'})
            flash('Your password has been reset successfully! You can now log in.', 'success')
            return redirect(url_for('auth.login'))
        else:
            flash('User not found.', 'error')
            return redirect(url_for('auth.forgot_password'))
    
    return render_template('reset_password_otp.html', form=form, session_expiry=session_expiry)


# Medicine routes
medicines = Blueprint('medicines', __name__, url_prefix='/medicines')

@medicines.route('/')
@login_required
@user_required
def list_medicines():
    """Optimized medicine listing with caching and efficient queries"""
    page = request.args.get('page', 1, type=int)
    per_page = request.args.get('per_page', 20, type=int)
    per_page = min(per_page, 100)  # Limit max per page
    
    # Get filter parameters
    filters = {
        'search': request.args.get('search', '').strip(),
        'category_id': request.args.get('category', type=int),
        'manufacturer_id': request.args.get('manufacturer', type=int),
        'status': request.args.get('filter', ''),
        'expiry_from': request.args.get('expiry_from', ''),
        'expiry_to': request.args.get('expiry_to', ''),
        'stock_min': request.args.get('stock_min', type=int),
        'stock_max': request.args.get('stock_max', type=int),
        'sort_by': request.args.get('sort', 'created_at'),
        'sort_order': request.args.get('order', 'desc')
    }
    
    # Remove empty filters
    filters = {k: v for k, v in filters.items() if v}
    
    # Use optimized service
    global medicine_service
    if not medicine_service:
        from services.medicine_service import MedicineService
        medicine_service = MedicineService(db, cache)
    
    # Get medicines with optimized query
    medicines, total = medicine_service.get_user_medicines_optimized(
        current_user.user_id, page, per_page, filters
    )
    
    # Get cached master data for filters
    master_data = medicine_service.get_frequently_used_data_cached()
    categories = [(cid, name) for cid, name in master_data['categories']]
    manufacturers = [(mid, name) for mid, name in master_data['manufacturers']]
    
    # Get cached statistics
    stats = medicine_service.get_dashboard_stats_cached(current_user.user_id)
    
    # Calculate pagination
    total_pages = (total + per_page - 1) // per_page
    has_prev = page > 1
    has_next = page < total_pages
    prev_num = page - 1 if has_prev else None
    next_num = page + 1 if has_next else None
    
    # Create pagination object for template compatibility
    class Pagination:
        def __init__(self):
            self.page = page
            self.per_page = per_page
            self.total = total
            self.pages = total_pages
            self.has_prev = has_prev
            self.has_next = has_next
            self.prev_num = prev_num
            self.next_num = next_num
            self.items = medicines
            
        def iter_pages(self, left_edge=2, left_current=2, right_current=3, right_edge=2):
            """Generate page numbers for pagination"""
            last = self.pages
            for num in range(1, last + 1):
                if num <= left_edge or \
                   (self.page - left_current - 1 < num < self.page + right_current) or \
                   num > last - right_edge:
                    yield num
    
    pagination = Pagination()
    
    return render_template('medicines.html', 
                         medicines=medicines,
                         pagination=pagination,
                         categories=categories,
                         manufacturers=manufacturers,
                         search=filters.get('search', ''),
                         category_id=filters.get('category_id'),
                         manufacturer_id=filters.get('manufacturer_id'),
                         filter_type=filters.get('status', ''),
                         expiry_from=filters.get('expiry_from', ''),
                         expiry_to=filters.get('expiry_to', ''),
                         stock_min=filters.get('stock_min'),
                         stock_max=filters.get('stock_max'),
                         sort_by=filters.get('sort_by', 'created_at'),
                         sort_order=filters.get('sort_order', 'desc'),
                         expired_count=stats['expired_medicines'],
                         near_expiry_count=stats['near_expiry_medicines'],
                         low_stock_count=stats['low_stock_medicines'],
                         total_count=stats['total_medicines'],
                         now=datetime.now().date())


@medicines.route('/add', methods=['GET', 'POST'])
@login_required
@user_required
def add_medicine():
    form = MedicineForm()
    
    # Populate category and manufacturer choices
    form.category_id.choices = [(c.category_id, c.name) for c in Category.query.filter_by(is_active=True).all()]
    form.manufacturer_id.choices = [(m.manufacturer_id, m.name) for m in Manufacturer.query.filter_by(is_active=True).all()]

    if request.method == 'POST' and form.validate_on_submit():
        try:
            # Handle image upload
            image_filename = None
            if form.medicine_image.data and hasattr(form.medicine_image.data, 'filename') and form.medicine_image.data.filename:
                image = form.medicine_image.data
                image_filename = secure_filename(image.filename)
                # Ensure upload directory exists
                upload_dir = os.path.join(app.root_path, 'static', 'uploads', 'medicines')
                os.makedirs(upload_dir, exist_ok=True)
                image_path = os.path.join(upload_dir, image_filename)
                image.save(image_path)

            # Convert timing strings to time objects
            timing_1 = None
            timing_2 = None
            timing_3 = None
            timing_4 = None
            
            if form.timing_1.data:
                try:
                    timing_1 = datetime.strptime(form.timing_1.data, '%H:%M').time()
                except ValueError:
                    pass
            
            if form.timing_2.data:
                try:
                    timing_2 = datetime.strptime(form.timing_2.data, '%H:%M').time()
                except ValueError:
                    pass
                    
            if form.timing_3.data:
                try:
                    timing_3 = datetime.strptime(form.timing_3.data, '%H:%M').time()
                except ValueError:
                    pass
                    
            if form.timing_4.data:
                try:
                    timing_4 = datetime.strptime(form.timing_4.data, '%H:%M').time()
                except ValueError:
                    pass

            # Create Medicine instance
            medicine = Medicine(
                name=form.name.data,
                generic_name=form.generic_name.data,
                brand_name=form.brand_name.data,
                dosage_form=form.dosage_form.data,
                strength=form.strength.data,
                price=form.price.data or 0,
                currency=form.currency.data,
                reason_for_taking=form.reason_for_taking.data,
                color=form.color.data,
                shape=form.shape.data,
                medicine_image=image_filename,
                dosage=form.dosage.data,
                frequency=form.frequency.data,
                food_instructions=form.food_instructions.data,
                start_date=form.start_date.data,
                end_date=form.end_date.data if not form.is_ongoing.data else None,
                is_ongoing=form.is_ongoing.data,
                timing_1=timing_1,
                timing_2=timing_2,
                timing_3=timing_3,
                timing_4=timing_4,
                doctor_name=form.doctor_name.data,
                total_quantity=form.total_quantity.data,
                unit_of_measurement=form.unit_of_measurement.data,
                refill_reminder_days=form.refill_reminder_days.data or 7,
                batch_number=form.batch_number.data,
                lot_number=None,
                stock_quantity=form.stock_quantity.data or 0,
                minimum_stock_level=form.minimum_stock_level.data or 5,
                manufacture_date=None,
                expiry_date=form.expiry_date.data,
                purchase_date=None,
                description=form.description.data,
                usage_instructions=form.usage_instructions.data,
                side_effects=form.side_effects.data,
                storage_conditions=form.storage_conditions.data,
                prescription_required=form.prescription_required.data,
                is_controlled_substance=form.is_controlled_substance.data,
                category_id=form.category_id.data,
                manufacturer_id=form.manufacturer_id.data,
                user_id=current_user.user_id,
                is_active=True,
                created_at=utc_now(),
                updated_at=utc_now()
            )
            db.session.add(medicine)
            db.session.commit()
            
            # Invalidate user cache after successful operation
            if medicine_service:
                medicine_service.invalidate_user_cache(current_user.user_id)
            
            log_action('CREATE', 'medicines', medicine.medicine_id)
            flash('Medicine added successfully!', 'success')
            return redirect(url_for('medicines.list_medicines'))
            
        except Exception as e:
            db.session.rollback()
            app.logger.error(f"Error adding medicine: {e}")
            flash('Error adding medicine. Please try again.', 'error')
    
    elif request.method == 'POST':
        # Form validation failed
        flash('Please fill in all required fields correctly.', 'error')
    
    # Get filter options for template
    categories = Category.query.filter_by(is_active=True).order_by(Category.name).all()
    manufacturers = Manufacturer.query.filter_by(is_active=True).order_by(Manufacturer.name).all()
    
    return render_template('add_medicine.html', form=form, categories=categories, manufacturers=manufacturers)


@medicines.route('/edit/<int:id>', methods=['GET', 'POST'])
@login_required
@user_required
def edit_medicine(id):
    medicine = Medicine.query.filter_by(medicine_id=id, user_id=current_user.user_id).first_or_404()
    form = MedicineForm(obj=medicine)
    
    # Populate choices
    form.category_id.choices = [(c.category_id, c.name) for c in Category.query.filter_by(is_active=True).all()]
    form.manufacturer_id.choices = [(m.manufacturer_id, m.name) for m in Manufacturer.query.filter_by(is_active=True).all()]
    
    # Pre-populate timing fields from database
    if medicine.timing_1:
        form.timing_1.data = medicine.timing_1.strftime('%H:%M')
    if medicine.timing_2:
        form.timing_2.data = medicine.timing_2.strftime('%H:%M')
    if medicine.timing_3:
        form.timing_3.data = medicine.timing_3.strftime('%H:%M')
    if medicine.timing_4:
        form.timing_4.data = medicine.timing_4.strftime('%H:%M')

    if request.method == 'POST' and form.validate_on_submit():
        try:
            old_values = {
                'name': medicine.name, 
                'price': float(medicine.price),
                'dosage': medicine.dosage,
                'frequency': medicine.frequency
            }
            
            # Handle image upload
            if form.medicine_image.data and hasattr(form.medicine_image.data, 'filename') and form.medicine_image.data.filename:
                image = form.medicine_image.data
                image_filename = secure_filename(image.filename)
                # Ensure upload directory exists
                upload_dir = os.path.join(app.root_path, 'static', 'uploads', 'medicines')
                os.makedirs(upload_dir, exist_ok=True)
                image_path = os.path.join(upload_dir, image_filename)
                image.save(image_path)
                medicine.medicine_image = image_filename

            # Convert timing strings to time objects
            if form.timing_1.data:
                try:
                    medicine.timing_1 = datetime.strptime(form.timing_1.data, '%H:%M').time()
                except ValueError:
                    medicine.timing_1 = None
            else:
                medicine.timing_1 = None
            
            if form.timing_2.data:
                try:
                    medicine.timing_2 = datetime.strptime(form.timing_2.data, '%H:%M').time()
                except ValueError:
                    medicine.timing_2 = None
            else:
                medicine.timing_2 = None
                
            if form.timing_3.data:
                try:
                    medicine.timing_3 = datetime.strptime(form.timing_3.data, '%H:%M').time()
                except ValueError:
                    medicine.timing_3 = None
            else:
                medicine.timing_3 = None
                
            if form.timing_4.data:
                try:
                    medicine.timing_4 = datetime.strptime(form.timing_4.data, '%H:%M').time()
                except ValueError:
                    medicine.timing_4 = None
            else:
                medicine.timing_4 = None

            # Update other fields
            medicine.name = form.name.data
            medicine.generic_name = form.generic_name.data
            medicine.brand_name = form.brand_name.data
            medicine.dosage_form = form.dosage_form.data
            medicine.strength = form.strength.data
            medicine.price = form.price.data or 0
            medicine.currency = form.currency.data
            medicine.reason_for_taking = form.reason_for_taking.data
            medicine.color = form.color.data
            medicine.shape = form.shape.data
            medicine.dosage = form.dosage.data
            medicine.frequency = form.frequency.data
            medicine.food_instructions = form.food_instructions.data
            medicine.start_date = form.start_date.data
            medicine.end_date = form.end_date.data if not form.is_ongoing.data else None
            medicine.is_ongoing = form.is_ongoing.data
            medicine.doctor_name = form.doctor_name.data
            medicine.total_quantity = form.total_quantity.data
            medicine.unit_of_measurement = form.unit_of_measurement.data
            medicine.refill_reminder_days = form.refill_reminder_days.data or 7
            medicine.batch_number = form.batch_number.data
            medicine.stock_quantity = form.stock_quantity.data or 0
            medicine.minimum_stock_level = form.minimum_stock_level.data or 5
            medicine.expiry_date = form.expiry_date.data
            medicine.description = form.description.data
            medicine.usage_instructions = form.usage_instructions.data
            medicine.side_effects = form.side_effects.data
            medicine.storage_conditions = form.storage_conditions.data
            medicine.prescription_required = form.prescription_required.data
            medicine.is_controlled_substance = form.is_controlled_substance.data
            medicine.category_id = form.category_id.data
            medicine.manufacturer_id = form.manufacturer_id.data
            medicine.updated_at = utc_now()
            
            db.session.commit()
            
            # Invalidate user cache after successful operation
            if medicine_service:
                medicine_service.invalidate_user_cache(current_user.user_id)
            
            new_values = {
                'name': medicine.name, 
                'price': float(medicine.price),
                'dosage': medicine.dosage,
                'frequency': medicine.frequency
            }
            log_action('UPDATE', 'medicines', medicine.medicine_id, old_values, new_values)
            flash('Medicine updated successfully!', 'success')
            return redirect(url_for('medicines.list_medicines'))
            
        except Exception as e:
            db.session.rollback()
            app.logger.error(f"Error updating medicine: {e}")
            flash('Error updating medicine. Please try again.', 'error')
    
    elif request.method == 'POST':
        # Form validation failed
        flash('Please correct the errors and try again.', 'error')
    
    # Get filter options for template
    categories = Category.query.filter_by(is_active=True).order_by(Category.name).all()
    manufacturers = Manufacturer.query.filter_by(is_active=True).order_by(Manufacturer.name).all()
    
    return render_template('edit_medicine.html', form=form, medicine=medicine, 
                         categories=categories, manufacturers=manufacturers)


@medicines.route('/delete/<int:id>', methods=['POST'])
@login_required
@user_required
def delete_medicine(id):
    medicine = Medicine.query.filter_by(medicine_id=id, user_id=current_user.user_id).first_or_404()
    
    medicine.is_active = False
    medicine.updated_at = datetime.now(timezone.utc)
    db.session.commit()
    
    # Invalidate user cache after successful operation
    if medicine_service:
        medicine_service.invalidate_user_cache(current_user.user_id)
    
    log_action('DELETE', 'medicines', medicine.medicine_id)
    flash('Medicine deleted successfully!', 'success')
    return redirect(url_for('medicines.list_medicines'))


# Reminder routes
reminders = Blueprint('reminders', __name__, url_prefix='/reminders')

@reminders.route('/')
@login_required
@user_required
def list_reminders():
    """List user's reminders with filtering"""
    page = request.args.get('page', 1, type=int)
    filter_type = request.args.get('filter', '')
    search = request.args.get('search', '')
    
    query = Reminder.query.filter_by(user_id=current_user.user_id)
    
    if search:
        query = query.filter(
            db.or_(
                Reminder.title.contains(search),
                Reminder.description.contains(search)
            )
        )
    
    if filter_type == 'active':
        query = query.filter_by(is_active=True, is_completed=False)
    elif filter_type == 'completed':
        query = query.filter_by(is_completed=True)
    elif filter_type == 'upcoming':
        query = query.filter(
            Reminder.is_active == True,
            Reminder.is_completed == False,
            Reminder.reminder_date >= datetime.now()
        )
    elif filter_type == 'overdue':
        query = query.filter(
            Reminder.is_active == True,
            Reminder.is_completed == False,
            Reminder.reminder_date < datetime.now()
        )
    
    reminders_paginated = query.order_by(Reminder.reminder_date.asc()).paginate(
        page=page, per_page=app.config['ITEMS_PER_PAGE'], error_out=False
    )
    
    # Statistics
    total_reminders = Reminder.query.filter_by(user_id=current_user.user_id).count()
    active_reminders = Reminder.query.filter_by(
        user_id=current_user.user_id, is_active=True, is_completed=False
    ).count()
    upcoming_reminders = Reminder.query.filter(
        Reminder.user_id == current_user.user_id,
        Reminder.is_active == True,
        Reminder.is_completed == False,
        Reminder.reminder_date >= datetime.now(),
        Reminder.reminder_date <= datetime.now() + timedelta(days=7)
    ).count()
    
    return render_template('reminders.html', 
                         reminders=reminders_paginated,
                         total_reminders=total_reminders,
                         active_reminders=active_reminders,
                         upcoming_reminders=upcoming_reminders,
                         current_datetime=datetime.now(),
                         current_datetime_plus_24h=datetime.now() + timedelta(hours=24),
                         filter_type=filter_type,
                         search=search)


@reminders.route('/add', methods=['GET', 'POST'])
@login_required
@user_required
def add_reminder():
    """Add new reminder"""
    form = ReminderForm()
    
    # Populate medicine choices
    user_medicines = Medicine.query.filter_by(
        user_id=current_user.user_id, is_active=True
    ).all()
    
    form.medicine_id.choices = [('', 'No specific medicine')] + [
        (str(m.medicine_id), f"{m.name} - {m.brand_name or 'Generic'}") 
        for m in user_medicines
    ]
    
    if form.validate_on_submit():
        # Combine date and time
        reminder_datetime = datetime.combine(
            form.reminder_date.data,
            datetime.strptime(form.reminder_time.data, '%H:%M').time()
        )
        
        # Apply advance notice
        if form.advance_notice_minutes.data > 0:
            reminder_datetime -= timedelta(minutes=form.advance_notice_minutes.data)
        
        reminder = Reminder(
            title=form.title.data,
            description=form.description.data,
            reminder_type=form.reminder_type.data,
            reminder_date=reminder_datetime,
            is_recurring=form.is_recurring.data,
            recurrence_pattern=form.recurrence_pattern.data if form.is_recurring.data else None,
            email_notification=form.email_notification.data,
            sms_notification=form.sms_notification.data,
            push_notification=form.push_notification.data,
            is_active=form.is_active.data,
            user_id=current_user.user_id,
            medicine_id=form.medicine_id.data if form.medicine_id.data else None
        )
        
        db.session.add(reminder)
        db.session.commit()
        
        log_action('CREATE', 'reminders', reminder.reminder_id)
        flash('Reminder created successfully!', 'success')
        return redirect(url_for('reminders.list_reminders'))
    
    return render_template('add_reminder.html', form=form)


@reminders.route('/edit/<int:id>', methods=['GET', 'POST'])
@login_required
@user_required
def edit_reminder(id):
    """Edit existing reminder"""
    reminder = Reminder.query.filter_by(
        reminder_id=id, user_id=current_user.user_id
    ).first_or_404()
    
    form = ReminderForm(obj=reminder)
    
    # Populate medicine choices
    user_medicines = Medicine.query.filter_by(
        user_id=current_user.user_id, is_active=True
    ).all()
    form.medicine_id.choices = [('', 'No specific medicine')] + [
        (str(m.medicine_id), f"{m.name} - {m.brand_name or 'Generic'}") 
        for m in user_medicines
    ]
    
    # Set form defaults
    if request.method == 'GET':
        form.reminder_date.data = reminder.reminder_date.date()
        form.reminder_time.data = reminder.reminder_date.strftime('%H:%M')
        form.medicine_id.data = str(reminder.medicine_id) if reminder.medicine_id else ''
    
    if form.validate_on_submit():
        old_values = {
            'title': reminder.title,
            'reminder_date': reminder.reminder_date.isoformat()
        }
        
        # Combine date and time
        reminder_datetime = datetime.combine(
            form.reminder_date.data,
            datetime.strptime(form.reminder_time.data, '%H:%M').time()
        )
        
        # Apply advance notice
        if form.advance_notice_minutes.data > 0:
            reminder_datetime -= timedelta(minutes=form.advance_notice_minutes.data)
        
        # Update reminder
        reminder.title = form.title.data
        reminder.description = form.description.data
        reminder.reminder_type = form.reminder_type.data
        reminder.reminder_date = reminder_datetime
        reminder.is_recurring = form.is_recurring.data
        reminder.recurrence_pattern = form.recurrence_pattern.data if form.is_recurring.data else None
        reminder.email_notification = form.email_notification.data
        reminder.sms_notification = form.sms_notification.data
        reminder.push_notification = form.push_notification.data
        reminder.is_active = form.is_active.data
        reminder.medicine_id = form.medicine_id.data if form.medicine_id.data else None
        reminder.updated_at = datetime.now()
        
        db.session.commit()
        
        new_values = {
            'title': reminder.title,
            'reminder_date': reminder.reminder_date.isoformat()
        }
        
        log_action('UPDATE', 'reminders', reminder.reminder_id, old_values, new_values)
        flash('Reminder updated successfully!', 'success')
        return redirect(url_for('reminders.list_reminders'))
    
    return render_template('edit_reminder.html', form=form, reminder=reminder)


@reminders.route('/delete/<int:id>', methods=['POST'])
@login_required
@user_required
def delete_reminder(id):
    """Delete reminder"""
    reminder = Reminder.query.filter_by(
        reminder_id=id, user_id=current_user.user_id
    ).first_or_404()
    
    reminder.is_active = False
    reminder.updated_at = datetime.now()
    db.session.commit()
    
    log_action('DELETE', 'reminders', reminder.reminder_id)
    flash('Reminder deleted successfully!', 'success')
    return redirect(url_for('reminders.list_reminders'))


@reminders.route('/toggle/<int:id>', methods=['POST'])
@login_required
@user_required
def toggle_reminder(id):
    """Toggle reminder active status"""
    reminder = Reminder.query.filter_by(
        reminder_id=id, user_id=current_user.user_id
    ).first_or_404()
    
    reminder.is_active = not reminder.is_active
    reminder.updated_at = datetime.now()
    db.session.commit()
    
    status = 'activated' if reminder.is_active else 'deactivated'
    log_action('UPDATE', 'reminders', reminder.reminder_id, 
               {'is_active': not reminder.is_active}, {'is_active': reminder.is_active})
    flash(f'Reminder {status} successfully!', 'success')
    return redirect(url_for('reminders.list_reminders'))


@reminders.route('/complete/<int:id>', methods=['POST'])
@login_required
@user_required
def complete_reminder(id):
    """Mark reminder as completed"""
    reminder = Reminder.query.filter_by(
        reminder_id=id, user_id=current_user.user_id
    ).first_or_404()
    
    reminder.is_completed = True
    reminder.updated_at = datetime.now()
    db.session.commit()
    
    log_action('UPDATE', 'reminders', reminder.reminder_id, 
               {'is_completed': False}, {'is_completed': True})
    flash('Reminder marked as completed!', 'success')
    return redirect(url_for('reminders.list_reminders'))


# Admin routes
admin = Blueprint('admin', __name__, url_prefix='/admin')

@admin.route('/')
@login_required
@admin_required
def dashboard():
    """Admin-specific dashboard with system-wide statistics"""
    # Prevent non-admin access
    if not current_user.is_admin:
        flash('Access denied. Admin privileges required.', 'error')
        return redirect(url_for('main.user_dashboard'))
    
    # System-wide statistics (all users)
    total_users = User.query.count()
    active_users = User.query.filter_by(is_active=True).count()
    admin_users = User.query.filter_by(is_admin=True).count()
    total_medicines = Medicine.query.count()  # All medicines from all users
    total_categories = Category.query.filter_by(is_active=True).count()
    total_manufacturers = Manufacturer.query.filter_by(is_active=True).count()
    
    # System health metrics
    expired_medicines_system = Medicine.query.filter(
        Medicine.expiry_date < datetime.now().date(),
        Medicine.is_active == True
    ).count()
    
    low_stock_system = Medicine.query.filter(
        Medicine.stock_quantity <= Medicine.minimum_stock_level,
        Medicine.is_active == True
    ).count()
    
    # Recent activity across the system
    recent_users = User.query.order_by(User.created_at.desc()).limit(5).all()
    recent_medicines = Medicine.query.order_by(Medicine.created_at.desc()).limit(5).all()
    recent_logs = AuditLog.query.order_by(AuditLog.timestamp.desc()).limit(10).all()
    
    # System alerts
    system_alerts = []
    if expired_medicines_system > 0:
        system_alerts.append(f"{expired_medicines_system} expired medicines in system")
    if low_stock_system > 0:
        system_alerts.append(f"{low_stock_system} medicines are low in stock")
    if total_users == 0:
        system_alerts.append("No users registered yet")
    
    return render_template('admin_dashboard.html',
                         total_users=total_users,
                         active_users=active_users,
                         admin_users=admin_users,
                         total_medicines=total_medicines,
                         total_categories=total_categories,
                         total_manufacturers=total_manufacturers,
                         expired_medicines_system=expired_medicines_system,
                         low_stock_system=low_stock_system,
                         recent_users=recent_users,
                         recent_medicines=recent_medicines,
                         recent_logs=recent_logs,
                         system_alerts=system_alerts)


@admin.route('/categories')
@login_required
@admin_required
def manage_categories():
    form = CategoryForm()
    page = request.args.get('page', 1, type=int)
    categories_paginated = Category.query.order_by(Category.name).paginate(
        page=page, per_page=app.config['ITEMS_PER_PAGE'], error_out=False
    )
    total_categories = Category.query.count()
    active_categories = Category.query.filter_by(is_active=True).count()
    total_medicines = Medicine.query.count()
    
    return render_template('manage_categories.html', 
                         categories=categories_paginated, 
                         form=form, 
                         total_categories=total_categories,
                         active_categories=active_categories,
                         total_medicines=total_medicines)


@admin.route('/categories/add', methods=['GET', 'POST'])
@login_required
@admin_required
def add_category():
    form = CategoryForm()
    if form.validate_on_submit():
        category = Category(
            name=form.name.data,
            description=form.description.data,
            icon=form.icon.data,
            color=form.color.data,
            is_active=form.is_active.data
        )
        db.session.add(category)
        db.session.commit()
        
        log_action('CREATE', 'categories', category.category_id)
        flash('Category added successfully!', 'success')
        return redirect(url_for('admin.manage_categories'))
    
    return render_template('add_category.html', form=form)


@admin.route('/categories/edit/<int:id>', methods=['GET', 'POST'])
@login_required
@admin_required
def edit_category(id):
    category = Category.query.get_or_404(id)
    form = CategoryForm(obj=category)
    
    if form.validate_on_submit():
        old_values = {'name': category.name}
        form.populate_obj(category)
        category.updated_at = datetime.now(timezone.utc)
        db.session.commit()
        
        new_values = {'name': category.name}
        log_action('UPDATE', 'categories', category.category_id, old_values, new_values)
        flash('Category updated successfully!', 'success')
        return redirect(url_for('admin.manage_categories'))
    
    return render_template('edit_category.html', form=form, category=category)


@admin.route('/categories/delete/<int:id>', methods=['POST'])
@login_required
@admin_required
def delete_category(id):
    category = Category.query.get_or_404(id)
    
    # Check if category has medicines
    if category.medicines:
        flash('Cannot delete category with associated medicines.', 'error')
        return redirect(url_for('admin.manage_categories'))
    
    category.is_active = False
    category.updated_at = datetime.now(timezone.utc)
    db.session.commit()
    
    log_action('DELETE', 'categories', category.category_id)
    flash('Category deleted successfully!', 'success')
    return redirect(url_for('admin.manage_categories'))


@admin.route('/medicines')
@login_required
@admin_required
def view_medicines():
    """Admin view for all medicines across all users"""
    page = request.args.get('page', 1, type=int)
    category_id = request.args.get('category', type=int)
    manufacturer_id = request.args.get('manufacturer', type=int)
    user_id = request.args.get('user', type=int)
    search = request.args.get('search', '')
    
    # Build query
    query = Medicine.query
    
    # Apply filters
    if category_id:
        query = query.filter_by(category_id=category_id)
    if manufacturer_id:
        query = query.filter_by(manufacturer_id=manufacturer_id)
    if user_id:
        query = query.filter_by(user_id=user_id)
    if search:
        query = query.filter(Medicine.name.ilike(f'%{search}%'))
    
    # Paginate results
    medicines_paginated = query.order_by(Medicine.name).paginate(
        page=page, per_page=app.config['ITEMS_PER_PAGE'], error_out=False
    )
    
    # Get filter options
    categories = Category.query.filter_by(is_active=True).order_by(Category.name).all()
    manufacturers = Manufacturer.query.filter_by(is_active=True).order_by(Manufacturer.name).all()
    users = User.query.filter_by(is_active=True).order_by(User.username).all()
    
    return render_template('admin_medicines.html',
                         medicines=medicines_paginated,
                         categories=categories,
                         manufacturers=manufacturers,
                         users=users,
                         selected_category=category_id,
                         selected_manufacturer=manufacturer_id,
                         selected_user=user_id,
                         search=search)


@admin.route('/manufacturers')
@login_required
@admin_required
def manage_manufacturers():
    form = ManufacturerForm()
    page = request.args.get('page', 1, type=int)
    manufacturers_paginated = Manufacturer.query.order_by(Manufacturer.name).paginate(
        page=page, per_page=app.config['ITEMS_PER_PAGE'], error_out=False
    )
    total_manufacturers = Manufacturer.query.count()
    active_manufacturers = Manufacturer.query.filter_by(is_active=True).count()
    total_medicines = Medicine.query.count()
    
    return render_template('manage_manufacturers.html', 
                         manufacturers=manufacturers_paginated, 
                         form=form, 
                         total_manufacturers=total_manufacturers,
                         active_manufacturers=active_manufacturers,
                         total_medicines=total_medicines)


@admin.route('/manufacturers/add', methods=['GET', 'POST'])
@login_required
@admin_required
def add_manufacturer():
    form = ManufacturerForm()
    if form.validate_on_submit():
        manufacturer = Manufacturer(
            name=form.name.data,
            contact_person=form.contact_person.data,
            email=form.email.data,
            phone=form.phone.data,
            address=form.address.data,
            website=form.website.data,
            country=form.country.data,
            license_number=form.license_number.data,
            is_active=form.is_active.data
        )
        db.session.add(manufacturer)
        db.session.commit()
        
        log_action('CREATE', 'manufacturers', manufacturer.manufacturer_id)
        flash('Manufacturer added successfully!', 'success')
        return redirect(url_for('admin.manage_manufacturers'))
    
    return render_template('add_manufacturer.html', form=form)


@admin.route('/manufacturers/edit/<int:id>', methods=['GET', 'POST'])
@login_required
@admin_required
def edit_manufacturer(id):
    manufacturer = Manufacturer.query.get_or_404(id)
    form = ManufacturerForm(obj=manufacturer)
    
    if form.validate_on_submit():
        old_values = {'name': manufacturer.name}
        form.populate_obj(manufacturer)
        manufacturer.updated_at = datetime.now(timezone.utc)
        db.session.commit()
        
        new_values = {'name': manufacturer.name}
        log_action('UPDATE', 'manufacturers', manufacturer.manufacturer_id, old_values, new_values)
        flash('Manufacturer updated successfully!', 'success')
        return redirect(url_for('admin.manage_manufacturers'))
    
    return render_template('edit_manufacturer.html', form=form, manufacturer=manufacturer)


@admin.route('/manufacturers/delete/<int:id>', methods=['POST'])
@login_required
@admin_required
def delete_manufacturer(id):
    manufacturer = Manufacturer.query.get_or_404(id)
    
    # Check if manufacturer has medicines
    if manufacturer.medicines:
        flash('Cannot delete manufacturer with associated medicines.', 'error')
        return redirect(url_for('admin.manage_manufacturers'))
    
    manufacturer.is_active = False
    manufacturer.updated_at = datetime.now(timezone.utc)
    db.session.commit()
    
    log_action('DELETE', 'manufacturers', manufacturer.manufacturer_id)
    flash('Manufacturer deleted successfully!', 'success')
    return redirect(url_for('admin.manage_manufacturers'))


@admin.route('/users')
@login_required
@admin_required
def manage_users():
    page = request.args.get('page', 1, type=int)
    search = request.args.get('search', '', type=str)
    
    query = User.query
    if search:
        query = query.filter(User.username.contains(search) | User.email.contains(search))
    
    users = query.order_by(User.created_at.desc()).paginate(
        page=page, per_page=app.config['ITEMS_PER_PAGE'], error_out=False
    )
    total_users = User.query.count()
    active_users = User.query.filter_by(is_active=True).count()
    admin_users = User.query.filter_by(is_admin=True).count()
    
    return render_template('manage_users.html', users=users, total_users=total_users, 
                         active_users=active_users, admin_users=admin_users, search=search)


@admin.route('/users/toggle/<int:id>', methods=['POST'])
@login_required
@admin_required
def toggle_user_status(id):
    user = db.session.get(User, id)
    if not user:
        abort(404)
    if user.user_id == current_user.user_id:
        flash('Cannot deactivate your own account.', 'error')
        return redirect(url_for('admin.manage_users'))
    
    user.is_active = not user.is_active
    db.session.commit()
    
    status = 'activated' if user.is_active else 'deactivated'
    log_action('UPDATE', 'users', user.user_id, {'is_active': not user.is_active}, {'is_active': user.is_active})
    flash(f'User {status} successfully!', 'success')
    return redirect(url_for('admin.manage_users'))


@admin.route('/users/promote/<int:id>', methods=['POST'])
@login_required
@admin_required
def promote_user(id):
    user = db.session.get(User, id)
    if not user:
        abort(404)
    if user.is_admin:
        flash('User is already an administrator.', 'info')
        return redirect(url_for('admin.manage_users'))
    
    user.is_admin = True
    db.session.commit()
    
    log_action('UPDATE', 'users', user.user_id, {'is_admin': False}, {'is_admin': True})
    flash(f'User {user.username} promoted to administrator!', 'success')
    return redirect(url_for('admin.manage_users'))


@admin.route('/users/demote/<int:id>', methods=['POST'])
@login_required
@admin_required
def demote_user(id):
    user = db.session.get(User, id)
    if not user:
        abort(404)
    if user.user_id == current_user.user_id:
        flash('Cannot demote yourself.', 'error')
        return redirect(url_for('admin.manage_users'))
    
    if not user.is_admin:
        flash('User is not an administrator.', 'info')
        return redirect(url_for('admin.manage_users'))
    
    user.is_admin = False
    db.session.commit()
    
    log_action('UPDATE', 'users', user.user_id, {'is_admin': True}, {'is_admin': False})
    flash(f'User {user.username} demoted from administrator!', 'success')
    return redirect(url_for('admin.manage_users'))


@admin.route('/users/delete/<int:id>', methods=['POST'])
@login_required
@admin_required
def delete_user(id):
    user = db.session.get(User, id)
    if not user:
        abort(404)
    if user.user_id == current_user.user_id:
        flash('Cannot delete your own account.', 'error')
        return redirect(url_for('admin.manage_users'))
    
    # Check if user has medicines
    if user.medicines:
        flash('Cannot delete user with associated medicines. Please transfer or delete medicines first.', 'error')
        return redirect(url_for('admin.manage_users'))
    
    username = user.username
    db.session.delete(user)
    db.session.commit()
    
    log_action('DELETE', 'users', id)
    flash(f'User {username} deleted successfully!', 'success')
    return redirect(url_for('admin.manage_users'))


@admin.route('/users/reset_password/<int:id>', methods=['POST'])
@login_required
@admin_required
def reset_user_password(id):
    user = db.session.get(User, id)
    if not user:
        abort(404)
    
    # Generate a temporary password
    alphabet = string.ascii_letters + string.digits
    temp_password = ''.join(secrets.choice(alphabet) for i in range(12))
    
    user.set_password(temp_password)
    user.failed_login_attempts = 0
    user.account_locked_until = None
    db.session.commit()
    
    log_action('UPDATE', 'users', user.user_id, {'password': 'reset'})
    flash(f'Password reset for {user.username}. Temporary password: {temp_password}', 'success')
    return redirect(url_for('admin.manage_users'))


@admin.route('/system')
@login_required
@admin_required
def system_status():
    # Database statistics
    db_stats = {
        'users': User.query.count(),
        'medicines': Medicine.query.count(),
        'categories': Category.query.count(),
        'manufacturers': Manufacturer.query.count(),
        'audit_logs': AuditLog.query.count()
    }
    
    # Recent activity
    recent_logins = User.query.filter(User.last_login.isnot(None)).order_by(User.last_login.desc()).limit(10).all()
    recent_medicine_adds = Medicine.query.order_by(Medicine.created_at.desc()).limit(10).all()
    recent_audit_logs = AuditLog.query.order_by(AuditLog.timestamp.desc()).limit(20).all()
    
    # System health checks
    health_checks = {
        'database': True,  # If we're here, DB is working
        'memory': True,    # Basic check
        'storage': True    # Basic check
    }
    
    return render_template('admin_system.html', 
                         db_stats=db_stats,
                         recent_logins=recent_logins,
                         recent_medicine_adds=recent_medicine_adds,
                         recent_audit_logs=recent_audit_logs,
                         health_checks=health_checks)


@admin.route('/audit_logs')
@login_required
@admin_required
def audit_logs():
    page = request.args.get('page', 1, type=int)
    action_filter = request.args.get('action', '', type=str)
    table_filter = request.args.get('table', '', type=str)
    
    query = AuditLog.query
    if action_filter:
        query = query.filter(AuditLog.action == action_filter)
    if table_filter:
        query = query.filter(AuditLog.table_name == table_filter)
    
    logs = query.order_by(AuditLog.timestamp.desc()).paginate(
        page=page, per_page=app.config['ITEMS_PER_PAGE'], error_out=False
    )
    
    # Get distinct actions and tables for filters
    actions = db.session.query(AuditLog.action.distinct()).all()
    tables = db.session.query(AuditLog.table_name.distinct()).all()
    
    return render_template('admin_audit_logs.html', 
                         logs=logs, 
                         actions=[a[0] for a in actions],
                         tables=[t[0] for t in tables],
                         action_filter=action_filter,
                         table_filter=table_filter)


@admin.route('/backup_database', methods=['POST'])
@login_required
@admin_required
def backup_database():
    try:
        # Import and run the backup script
        import subprocess
        import os
        
        backup_script = os.path.join(os.path.dirname(__file__), 'scripts', 'mysql_backup.py')
        result = subprocess.run(['python', backup_script], capture_output=True, text=True, cwd=os.path.dirname(__file__))
        
        if result.returncode == 0:
            log_action('BACKUP', 'database', None)
            flash('Database backup completed successfully!', 'success')
        else:
            flash(f'Database backup failed: {result.stderr}', 'error')
    except Exception as e:
        flash(f'Database backup error: {str(e)}', 'error')
    
    return redirect(url_for('admin.system_status'))


@admin.route('/export_data')
@login_required
@admin_required
def export_data():
    from flask import send_file
    import csv
    import tempfile
    import io
    
    try:
        # Create a temporary file for CSV export
        output = io.StringIO()
        writer = csv.writer(output)
        
        # Export all medicines
        writer.writerow([
            'Medicine ID', 'Name', 'Strength', 'Dosage', 'Frequency', 'Start Date', 'End Date', 
            'Doctor', 'Total Quantity', 'Unit', 'Reason', 'Category', 'Manufacturer', 
            'Expiry Date', 'User', 'Created Date', 'Status'
        ])
        medicines = Medicine.query.join(Category).join(Manufacturer).join(User).all()
        
        for medicine in medicines:
            writer.writerow([
                medicine.medicine_id,
                medicine.name,
                medicine.strength,
                medicine.dosage,
                medicine.frequency,
                medicine.start_date.strftime('%Y-%m-%d') if medicine.start_date else '',
                medicine.end_date.strftime('%Y-%m-%d') if medicine.end_date else 'Ongoing' if medicine.is_ongoing else '',
                medicine.doctor_name or '',
                medicine.total_quantity,
                medicine.unit_of_measurement,
                medicine.reason_for_taking or '',
                medicine.category.name,
                medicine.manufacturer.name,
                medicine.expiry_date.strftime('%Y-%m-%d') if medicine.expiry_date else '',
                medicine.user.username,
                medicine.created_at.strftime('%Y-%m-%d %H:%M:%S'),
                medicine.status
            ])
        
        # Convert to bytes
        output.seek(0)
        mem_file = io.BytesIO()
        mem_file.write(output.getvalue().encode('utf-8'))
        mem_file.seek(0)
        
        log_action('EXPORT', 'medicines', None)
        
        return send_file(
            mem_file,
            mimetype='text/csv',
            as_attachment=True,
            download_name=f'medicines_export_{datetime.now().strftime("%Y%m%d_%H%M%S")}.csv'
        )
    except Exception as e:
        flash(f'Export failed: {str(e)}', 'error')
        return redirect(url_for('admin.system_status'))


# API routes
api = Blueprint('api', __name__, url_prefix='/api')

@api.route('/medicines', methods=['GET'])
@login_required
@limiter.limit("100 per minute")
def api_get_medicines():
    medicines = Medicine.query.filter_by(user_id=current_user.user_id, is_active=True).all()
    return jsonify([{
        'id': m.medicine_id,
        'name': m.name,
        'generic_name': m.generic_name,
        'brand_name': m.brand_name,
        'strength': m.strength,
        
        # User-focused fields
        'reason_for_taking': m.reason_for_taking,
        'color': m.color,
        'shape': m.shape,
        'dosage': m.dosage,
        'frequency': m.frequency,
        'food_instructions': m.food_instructions,
        
        # Schedule and timing
        'start_date': m.start_date.isoformat() if m.start_date else None,
        'end_date': m.end_date.isoformat() if m.end_date else None,
        'is_ongoing': m.is_ongoing,
        'timing_1': m.timing_1.strftime('%H:%M') if m.timing_1 else None,
        'timing_2': m.timing_2.strftime('%H:%M') if m.timing_2 else None,
        'timing_3': m.timing_3.strftime('%H:%M') if m.timing_3 else None,
        'timing_4': m.timing_4.strftime('%H:%M') if m.timing_4 else None,
        
        # Prescription and refill
        'doctor_name': m.doctor_name,
        'total_quantity': m.total_quantity,
        'unit_of_measurement': m.unit_of_measurement,
        'refill_reminder_days': m.refill_reminder_days,
        
        # Additional info
        'dosage_form': m.dosage_form,
        'usage_instructions': m.usage_instructions,
        'side_effects': m.side_effects,
        'storage_conditions': m.storage_conditions,
        'prescription_required': m.prescription_required,
        'is_controlled_substance': m.is_controlled_substance,
        
        # System fields
        'price': float(m.price),
        'currency': m.currency,
        'expiry_date': m.expiry_date.isoformat() if m.expiry_date else None,
        'batch_number': m.batch_number,
        'stock_quantity': m.stock_quantity,
        'category': m.category.name,
        'manufacturer': m.manufacturer.name,
        
        # Status fields
        'is_expired': m.is_expired,
        'days_to_expiry': m.days_to_expiry,
        'days_to_end': m.days_to_end,
        'is_treatment_ended': m.is_treatment_ended,
        'needs_refill': m.needs_refill,
        'is_low_stock': m.is_low_stock,
        'status': m.status
    } for m in medicines])


@api.route('/categories', methods=['GET'])
@login_required
@limiter.limit("100 per minute")
def api_get_categories():
    categories = Category.query.filter_by(is_active=True).all()
    return jsonify([{
        'id': c.category_id,
        'name': c.name,
        'description': c.description,
        'icon': c.icon,
        'color': c.color
    } for c in categories])


@api.route('/manufacturers', methods=['GET'])
@login_required
@limiter.limit("100 per minute")
def api_get_manufacturers():
    manufacturers = Manufacturer.query.filter_by(is_active=True).all()
    return jsonify([{
        'id': m.manufacturer_id,
        'name': m.name,
        'contact_person': m.contact_person,
        'email': m.email,
        'phone': m.phone,
        'website': m.website,
        'country': m.country
    } for m in manufacturers])


@api.route('/statistics', methods=['GET'])
@login_required
@limiter.limit("100 per minute")
def api_get_statistics():
    user_medicines = Medicine.query.filter_by(user_id=current_user.user_id, is_active=True)
    
    total_medicines = user_medicines.count()
    expired_medicines = user_medicines.filter(Medicine.expiry_date < datetime.now().date()).count()
    near_expiry = user_medicines.filter(
        Medicine.expiry_date <= (datetime.now().date() + timedelta(days=30))
    ).count()
    low_stock = user_medicines.filter(
        Medicine.stock_quantity <= Medicine.minimum_stock_level
    ).count()
    
    total_value = sum(float(m.price) * m.stock_quantity for m in user_medicines.all())
    
    return jsonify({
        'total_medicines': total_medicines,
        'expired_medicines': expired_medicines,
        'near_expiry_medicines': near_expiry,
        'low_stock_medicines': low_stock,
        'total_inventory_value': round(total_value, 2),
        'categories_count': Category.query.filter_by(is_active=True).count(),
        'manufacturers_count': Manufacturer.query.filter_by(is_active=True).count()
    })


# Register blueprints
app.register_blueprint(main)
app.register_blueprint(auth)
app.register_blueprint(medicines)
app.register_blueprint(reminders)
app.register_blueprint(admin)
app.register_blueprint(api)


# Error handlers
@app.errorhandler(404)
def not_found_error(error):
    return render_template('errors/404.html'), 404


@app.errorhandler(500)
def internal_error(error):
    db.session.rollback()
    return render_template('errors/500.html'), 500


@app.errorhandler(403)
def forbidden_error(error):
    return render_template('errors/403.html'), 403


# Context processors
@app.context_processor
def utility_processor():
    return dict(
        datetime=datetime,
        len=len,
        cache_bust=int(datetime.now().timestamp())  # Cache busting for static files
    )


if __name__ == '__main__':
    with app.app_context():
        db.create_all()
        
        # Initialize services
        from services.medicine_service import MedicineService
        from services.notification_service import NotificationService
        from services.background_tasks import init_celery
        from services.ai_assistant_service import init_medication_assistant
        
        medicine_service = MedicineService(db, cache)
        notification_service = NotificationService(mail, cache)
        
        # Initialize AI Assistant
        try:
            ai_assistant = init_medication_assistant(app)
        except Exception as e:
            pass
        
        # Initialize background tasks (Celery)
        try:
            celery = init_celery(app)
        except Exception as e:
            pass
        
        # Only create tables, no default data
        # Admin users, categories, and manufacturers should be created through the UI
        if not User.query.filter_by(is_admin=True).first():
            pass
        
        # Enhanced scheduler with background tasks
        if scheduler and not scheduler.running:
            scheduler.start()
            
            # Add background maintenance tasks
            try:
                # Check if both celery and Redis are available before scheduling tasks
                if celery:
                    # Test if Redis is actually available for Celery tasks
                    try:
                        import redis
                        redis_client = redis.Redis(host='localhost', port=6379, decode_responses=True)
                        redis_client.ping()
                        redis_available = True
                    except:
                        redis_available = False
                    
                    # Only schedule tasks if Redis is available
                    if redis_available:
                        # Schedule daily status updates at 2 AM
                        scheduler.add_job(
                            id='daily_status_update',
                            func=lambda: celery.send_task('services.background_tasks.bulk_status_update'),
                            trigger=CronTrigger(hour=2, minute=0),
                            replace_existing=True
                        )
                        
                        # Schedule reminder notifications every 15 minutes
                        scheduler.add_job(
                            id='reminder_notifications',
                            func=lambda: celery.send_task('services.background_tasks.send_reminder_notifications'),
                            trigger=CronTrigger(minute='*/15'),
                            replace_existing=True
                        )
                        
                        # Schedule weekly reports on Sundays at 9 AM
                        scheduler.add_job(
                            id='weekly_reports',
                            func=lambda: celery.send_task('services.background_tasks.generate_weekly_reports'),
                            trigger=CronTrigger(day_of_week=6, hour=9, minute=0),
                            replace_existing=True
                        )
                        
                        # Schedule database maintenance weekly on Sundays at 3 AM
                        scheduler.add_job(
                            id='database_maintenance',
                            func=lambda: celery.send_task('services.background_tasks.optimize_database'),
                            trigger=CronTrigger(day_of_week=6, hour=3, minute=0),
                            replace_existing=True
                        )
                
            except Exception as e:
                pass
            
            atexit.register(lambda: scheduler.shutdown())
    
    # Get environment
    env = os.getenv('FLASK_ENV', 'development')
    debug_mode = env == 'development'
    
    # Run with auto-reload enabled for instant code changes
    app.run(
        host='0.0.0.0', 
        port=5000, 
        debug=debug_mode,
        use_reloader=True,  # Auto-reload on code changes
        extra_files=None    # Watch all files
    )