""" SQLAlchemy models for MedTech Production Database Handles 343,794+ medicines from two Excel files """ from sqlalchemy import Column, Integer, String, Text, Float, DateTime, Boolean, Index from sqlalchemy.ext.declarative import declarative_base from datetime import datetime Base = declarative_base() class Medicine(Base): """ Main medicines table - optimized for 343K+ records """ __tablename__ = 'medicines' # Primary Key id = Column(Integer, primary_key=True, autoincrement=True) # Core Product Information (Columns A-N) product_id = Column(String(100), unique=True, index=True, nullable=True) product_name = Column(String(500), nullable=False, index=True) marketer = Column(String(500), nullable=True) salt_composition = Column(Text, nullable=True) medicine_type = Column(String(100), nullable=True, index=True) introduction = Column(Text, nullable=True) benefits = Column(Text, nullable=True) description = Column(Text, nullable=True) symptoms = Column(Text, nullable=True) # Comma-separated symptoms extracted from description how_to_use = Column(Text, nullable=True) safety_advise = Column(Text, nullable=True) if_miss = Column(Text, nullable=True) packaging_detail = Column(Text, nullable=True) package = Column(String(200), nullable=True) qty = Column(String(100), nullable=True) # Product Form & Pricing (Columns O-R) product_form = Column(String(100), nullable=True) mrp = Column(Float, nullable=True) prescription_req = Column(String(50), nullable=True) fact_box = Column(Text, nullable=True) # Medical Information (Columns S-W) primary_use = Column(Text, nullable=True) storage = Column(Text, nullable=True) use_of = Column(Text, nullable=True) common_side_effect = Column(Text, nullable=True) # Interaction Information (Columns X-AB) alcohol_interaction = Column(Text, nullable=True) pregnancy_interaction = Column(Text, nullable=True) lactation_interaction = Column(Text, nullable=True) driving_interaction = Column(Text, nullable=True) kidney_interaction = Column(Text, nullable=True) liver_interaction = Column(Text, nullable=True) # Manufacturer Information (Columns AC-AH) manufacturer_address = Column(Text, nullable=True) country_of_origin = Column(String(100), nullable=True) q_a = Column(Text, nullable=True) how_it_works = Column(Text, nullable=True) interaction = Column(Text, nullable=True) manufacturer_details = Column(Text, nullable=True) marketer_details = Column(Text, nullable=True) # Additional Info (Columns AI-AK) expirtation = Column(String(200), nullable=True) reference = Column(Text, nullable=True) # Metadata source_file = Column(String(50), nullable=False) # 'file1' or 'file2' created_at = Column(DateTime, default=datetime.utcnow, nullable=False) updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) # Indexes for performance # MySQL FULLTEXT indexes for text search __table_args__ = ( Index('idx_product_name_ft', 'product_name', mysql_prefix='FULLTEXT'), Index('idx_salt_composition_ft', 'salt_composition', mysql_prefix='FULLTEXT'), Index('idx_primary_use_ft', 'primary_use', mysql_prefix='FULLTEXT'), Index('idx_symptoms_ft', 'symptoms', mysql_prefix='FULLTEXT'), Index('idx_combined_ft', 'product_name', 'salt_composition', 'primary_use', mysql_prefix='FULLTEXT'), {'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8mb4'}, ) def __repr__(self): return f"" class DosageCache(Base): """ Cache table for AI-generated dosage information. Stores both medical shorthand (OD/BD/TDS/QDS) and meal-slot schedule (1 0 1). """ __tablename__ = 'dosage_cache' medicine_id = Column(Integer, primary_key=True) # AI-generated dosage fields quantity = Column(String(50)) dosage_code = Column(String(10)) # Medical shorthand: OD, BD, TDS, QDS frequency = Column(String(100)) # Human readable: "Twice daily" meal_preference = Column(String(100)) # When to take: "After Meal" duration = Column(String(200)) # Meal-slot schedule: dose count per meal (e.g. 1=one tablet, 0.5=half tablet) dose_morning = Column(Float, default=0) # Breakfast slot dose_afternoon = Column(Float, default=0) # Lunch slot dose_night = Column(Float, default=0) # Dinner slot # Age-specific dosages (JSON string) age_group_dosages = Column(Text) # JSON: {"infant_0_2": {...}, "child_2_12": {...}, ...} # Metadata generated_at = Column(DateTime, default=datetime.utcnow) generation_method = Column(String(50)) # 'gemini', 'rule_based', 'enhanced' confidence_score = Column(Float) @property def dose_schedule(self) -> str: """Return meal-slot schedule string like '1-0-1'.""" m = self.dose_morning or 0 a = self.dose_afternoon or 0 n = self.dose_night or 0 # Format as int if whole number, else float def _fmt(v): return str(int(v)) if v == int(v) else str(v) return f"{_fmt(m)}-{_fmt(a)}-{_fmt(n)}" def __repr__(self): return f"" class SearchLog(Base): """ Log table for analytics and debugging """ __tablename__ = 'search_logs' id = Column(Integer, primary_key=True, autoincrement=True) query = Column(String(500), nullable=False) result_count = Column(Integer) response_time_ms = Column(Float) timestamp = Column(DateTime, default=datetime.utcnow) user_ip = Column(String(50)) __table_args__ = ( Index('idx_query_timestamp', 'query', 'timestamp'), ) class DDICache(Base): """ Cache table for drug-drug interaction results. Stores normalized individual salt pairs (e.g. "aspirin" + "ibuprofen"), not full compositions. Alphabetically sorted so A+B == B+A. ~2000-3000 unique pharma salts worldwide = max ~5M possible pairs. """ __tablename__ = 'ddi_cache' id = Column(Integer, primary_key=True, autoincrement=True) salt_a = Column(String(255), nullable=False) # normalized salt name (lowercase) salt_b = Column(String(255), nullable=False) # normalized salt name (lowercase) severity = Column(String(50)) # none, mild, moderate, severe, contraindicated description = Column(Text) recommendation = Column(Text) source = Column(String(50)) # 'database', 'gemini_ai' confidence = Column(Float) created_at = Column(DateTime, default=datetime.utcnow) __table_args__ = ( Index('idx_ddi_pair', 'salt_a', 'salt_b', unique=True), )