from flask_sqlalchemy import SQLAlchemy
from flask_login import UserMixin
from datetime import datetime
from sqlalchemy import event
from sqlalchemy.engine import Engine

db = SQLAlchemy()

@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    # Only apply SQLite pragmas on SQLite connections.
    # On PostgreSQL (GCP Cloud SQL) this must be a no-op.
    try:
        module = dbapi_connection.__class__.__module__
    except Exception:
        return
    if "sqlite" not in module:
        return
    try:
        cursor = dbapi_connection.cursor()
        cursor.execute("PRAGMA journal_mode=WAL")
        cursor.execute("PRAGMA synchronous=NORMAL")
        cursor.close()
    except Exception:
        pass

user_branch = db.Table('user_branch',
    db.Column('user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True),
    db.Column('branch_id', db.Integer, db.ForeignKey('branch.id'), primary_key=True)
)


class Hospital(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255), nullable=False)
    admin_portal_name = db.Column(db.String(255), nullable=True)
    contact_number = db.Column(db.String(50), nullable=True)
    email = db.Column(db.String(255), unique=True, nullable=True)
    status = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    users = db.relationship('User', backref='hospital', lazy=True)

class User(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    hospital_id = db.Column(db.Integer, db.ForeignKey('hospital.id'), nullable=True) # Null for superadmin
    username = db.Column(db.String(150), nullable=True) # Make nullable, we use email
    email = db.Column(db.String(150), unique=True, nullable=False)
    password = db.Column(db.String(500), nullable=False)
    plaintext_password = db.Column(db.String(150), nullable=True)
    role = db.Column(db.String(50), nullable=False) # patient, doctor, reception, admin, superadmin
    specialization = db.Column(db.String(100), nullable=True) # Doctor specialization
    otp = db.Column(db.String(10), nullable=True)
    otp_expiry = db.Column(db.DateTime, nullable=True)
    
    # Relationships
    patient_profile = db.relationship('PatientProfile', backref='user', uselist=False)
    branches = db.relationship('Branch', secondary=user_branch, lazy='subquery', backref=db.backref('staff_members', lazy=True))

class PatientProfile(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    hospital_id = db.Column(db.Integer, db.ForeignKey('hospital.id'), nullable=True)
    branch_id = db.Column(db.Integer, db.ForeignKey('branch.id'), nullable=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    name = db.Column(db.String(150))
    dob = db.Column(db.String(50))
    age = db.Column(db.Integer)
    gender = db.Column(db.String(20))
    contact = db.Column(db.String(50)) # mobile
    mobile = db.Column(db.String(50))
    address = db.Column(db.Text)
    blood_group = db.Column(db.String(10))
    occupation = db.Column(db.String(100))
    emergency_contact = db.Column(db.String(100))
    patient_uuid = db.Column(db.String(50), unique=True)
    insurance_company_name = db.Column(db.String(150))
    insurance_id = db.Column(db.String(100))
    insurance_limit = db.Column(db.String(50))
    insurance_end_date = db.Column(db.String(50))
    insurance_document = db.Column(db.String(1024))
    
    symptoms = db.relationship('SymptomForm', backref='patient', lazy=True)
    reports = db.relationship('Report', backref='patient', lazy=True)
    appointments = db.relationship('Appointment', backref='patient', lazy=True)
    medical_history = db.relationship('MedicalHistory', backref='patient', lazy=True)
    progression = db.relationship('DiseaseProgression', backref='patient', lazy=True)
    follow_ups = db.relationship('FollowUp', backref='patient', lazy=True)

class MedicalHistory(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    hospital_id = db.Column(db.Integer, db.ForeignKey('hospital.id'), nullable=True)
    patient_id = db.Column(db.Integer, db.ForeignKey('patient_profile.id'), nullable=False)
    date_recorded = db.Column(db.DateTime, default=datetime.utcnow)
    condition = db.Column(db.String(255))
    surgeries = db.Column(db.Text)
    previous_medications = db.Column(db.Text)

class DiseaseProgression(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    hospital_id = db.Column(db.Integer, db.ForeignKey('hospital.id'), nullable=True)
    patient_id = db.Column(db.Integer, db.ForeignKey('patient_profile.id'), nullable=False)
    doctor_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    date_recorded = db.Column(db.DateTime, default=datetime.utcnow)
    status = db.Column(db.String(50)) # Improving, Stable, Worsening
    severity_score = db.Column(db.Integer) # 1-10
    notes = db.Column(db.Text)

class FollowUp(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    hospital_id = db.Column(db.Integer, db.ForeignKey('hospital.id'), nullable=True)
    patient_id = db.Column(db.Integer, db.ForeignKey('patient_profile.id'), nullable=False)
    doctor_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    date_scheduled = db.Column(db.DateTime, nullable=False)
    status = db.Column(db.String(50), default='Pending') # Pending, Completed, Cancelled
    notes = db.Column(db.Text)

class SymptomForm(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    hospital_id = db.Column(db.Integer, db.ForeignKey('hospital.id'), nullable=True)
    patient_id = db.Column(db.Integer, db.ForeignKey('patient_profile.id'), nullable=False)
    description = db.Column(db.Text, nullable=False)
    date_submitted = db.Column(db.DateTime, default=datetime.utcnow)

class Report(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    hospital_id = db.Column(db.Integer, db.ForeignKey('hospital.id'), nullable=True)
    patient_id = db.Column(db.Integer, db.ForeignKey('patient_profile.id'), nullable=False)
    filename = db.Column(db.String(255), nullable=False)
    filepath = db.Column(db.String(1024), nullable=False)
    upload_date = db.Column(db.DateTime, default=datetime.utcnow)

class Appointment(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    hospital_id = db.Column(db.Integer, db.ForeignKey('hospital.id'), nullable=True)
    patient_id = db.Column(db.Integer, db.ForeignKey('patient_profile.id'), nullable=False)
    doctor_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    date_time = db.Column(db.DateTime, nullable=False)
    status = db.Column(db.String(50), default='Scheduled') # Scheduled, Completed, Cancelled
    billing_status = db.Column(db.String(50), default='Pending') # Pending, Paid
    queue_number = db.Column(db.String(20))
    
    doctor = db.relationship('User', foreign_keys=[doctor_id])
    prescription = db.relationship('Prescription', backref='appointment', uselist=False)

class WalkInQueue(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    hospital_id = db.Column(db.Integer, db.ForeignKey('hospital.id'), nullable=True)
    token = db.Column(db.String(50), nullable=False, unique=True)
    symptoms = db.Column(db.Text)
    status = db.Column(db.String(20), default='Waiting') # Waiting, Done
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

class Prescription(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    hospital_id = db.Column(db.Integer, db.ForeignKey('hospital.id'), nullable=True)
    appointment_id = db.Column(db.Integer, db.ForeignKey('appointment.id'), nullable=False)
    medications = db.Column(db.Text, nullable=False)
    notes = db.Column(db.Text)
    date_issued = db.Column(db.DateTime, default=datetime.utcnow)

class ClinicalWorkflow(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    hospital_id = db.Column(db.Integer, db.ForeignKey('hospital.id'), nullable=True)
    patient_id = db.Column(db.Integer, db.ForeignKey('patient_profile.id'), nullable=False)
    doctor_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=True) # Assigned Doctor
    current_step = db.Column(db.String(50), default='registration') # registration, complaint, symptoms, history, vitals, reports, ai_analysis, doctor_review, prescription, follow_up, completed
    status = db.Column(db.String(20), default='Active') # Active, Completed
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # 1. Registration
    # Saved directly in PatientProfile

    # 2. Chief Complaint
    chief_complaint = db.Column(db.String(150), default='None')

    # 3. Symptoms (LUTS & dynamic questionnaire choices)
    frequency_nocturia = db.Column(db.String(10), default='No')
    urgency = db.Column(db.String(10), default='No')
    urge_incontinence = db.Column(db.String(10), default='No')
    hesitancy = db.Column(db.String(10), default='No')
    poor_urinary_stream = db.Column(db.String(10), default='No')
    straining_to_void = db.Column(db.String(10), default='No')
    intermittency = db.Column(db.String(10), default='No')
    post_void_dribbling = db.Column(db.String(10), default='No')
    incomplete_emptying = db.Column(db.String(10), default='No')
    dynamic_symptoms_json = db.Column(db.Text, default='{}') # Stores other dynamic selections

    # 4. Medical History
    htn = db.Column(db.String(10), default='No')
    dm = db.Column(db.String(10), default='No')
    ihd_cad = db.Column(db.String(10), default='No')
    copd = db.Column(db.String(10), default='No')
    kochs = db.Column(db.String(10), default='No')
    thyroid = db.Column(db.String(10), default='No')
    cv_stroke = db.Column(db.String(10), default='No')
    other_medical_history = db.Column(db.String(255), default='None')
    operative_hospitalization = db.Column(db.String(100), default='None')
    appetite = db.Column(db.String(50), default='Good')
    sleep = db.Column(db.String(50), default='Good')
    bowel_habits = db.Column(db.String(50), default='Normal')
    alcohol_drugs = db.Column(db.String(10), default='No')
    tobacco = db.Column(db.String(10), default='No')
    smoking = db.Column(db.String(10), default='No')
    drug_allergy = db.Column(db.String(100), default='None')
    special_medicines = db.Column(db.String(100), default='None')
    family_history = db.Column(db.Text, default='None')

    # 5. Vitals
    bp = db.Column(db.String(50), default='120/80')
    pulse = db.Column(db.Integer, default=72)
    temperature = db.Column(db.Float, default=98.6)
    height = db.Column(db.Float, default=0.0) # in cm
    weight = db.Column(db.Float, default=0.0) # in kg
    bmi = db.Column(db.Float, default=0.0)
    spo2 = db.Column(db.Integer, default=98)

    # Symptom Tracker
    symptom_tracker_notes = db.Column(db.Text)

    # 6. Report Uploads
    # Handled via Report table relationships

    # 7. AI Analysis cache
    ai_diagnosis = db.Column(db.String(255))
    ai_confidence = db.Column(db.Float)
    ai_recommendations_json = db.Column(db.Text, default='[]')
    ai_predictions_json = db.Column(db.Text, default='[]')

    # 8. Doctor Review
    doctor_notes = db.Column(db.Text)

    # 9. Prescription
    prescription_medications = db.Column(db.Text)
    prescription_notes = db.Column(db.Text)

    # 10. Follow-up
    followup_date = db.Column(db.DateTime)
    followup_notes = db.Column(db.Text)

    # Relationships
    patient = db.relationship('PatientProfile', backref=db.backref('workflows', lazy=True, cascade="all, delete-orphan"))
    doctor = db.relationship('User', foreign_keys=[doctor_id])

# ==============================================================================
# PHASE 5: DYNAMIC AI ARCHITECTURE & HOSPITAL MANAGEMENT MODELS
# ==============================================================================

class Branch(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    hospital_id = db.Column(db.Integer, db.ForeignKey('hospital.id'), nullable=True)
    name = db.Column(db.String(100), unique=True, nullable=False)
    status = db.Column(db.Boolean, default=True)
    departments = db.relationship('Department', backref='branch', lazy=True, cascade="all, delete-orphan")

class Department(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    hospital_id = db.Column(db.Integer, db.ForeignKey('hospital.id'), nullable=True)
    name = db.Column(db.String(100), nullable=False)
    branch_id = db.Column(db.Integer, db.ForeignKey('branch.id'), nullable=False)
    status = db.Column(db.Boolean, default=True)

class DiseaseDefinition(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True, nullable=False)
    department = db.Column(db.String(100), nullable=True) # e.g. gastroenterology, urology
    symptoms = db.Column(db.Text) # JSON list
    risk_factors = db.Column(db.Text) # JSON list
    investigations = db.Column(db.Text) # JSON list
    severity_scale = db.Column(db.Text) # JSON dict
    treatments = db.Column(db.Text) # JSON list
    guidelines = db.Column(db.Text) # Text or JSON

class DynamicQuestion(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    question_text = db.Column(db.String(255), nullable=False)
    answer_type = db.Column(db.String(50), default='Boolean') # Boolean, Choice, Scale
    disease_mapping = db.Column(db.String(100)) # e.g. "Gastritis"
    weight = db.Column(db.Float, default=1.0)

class ClinicalRule(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    condition_logic = db.Column(db.Text, nullable=False) # JSON
    action_logic = db.Column(db.Text, nullable=False) # JSON
    is_active = db.Column(db.Boolean, default=True)

class AIAuditLog(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    workflow_id = db.Column(db.Integer, db.ForeignKey('clinical_workflow.id'), nullable=False)
    patient_input_json = db.Column(db.Text)
    ai_prediction = db.Column(db.String(255))
    confidence_score = db.Column(db.Float)
    doctor_diagnosis = db.Column(db.String(255))
    final_diagnosis = db.Column(db.String(255))
    timestamp = db.Column(db.DateTime, default=datetime.utcnow)