# database.py
import os
from sqlalchemy import create_engine, Column, Integer, String, Date, Text, DECIMAL, TIMESTAMP, ForeignKey
from sqlalchemy.orm import declarative_base, relationship, sessionmaker

# ------------------------
# Base for models
# ------------------------
Base = declarative_base()

# ------------------------
# Table definitions
# ------------------------
class Patient(Base):
    __tablename__ = "Patient"
    patient_id = Column(Integer, primary_key=True)
    last_name = Column(String, nullable=False)
    first_name = Column(String, nullable=False)
    date_of_birth = Column(Date, nullable=False)
    gender = Column(String, nullable=False)
    address = Column(String)
    phone = Column(String)
    blood_group = Column(String)
    social_security_number = Column(String, unique=True)

    consultations = relationship("Consultation", back_populates="patient")
    medical_records = relationship("MedicalRecord", back_populates="patient")


class Doctor(Base):
    __tablename__ = "Doctor"
    doctor_id = Column(Integer, primary_key=True)
    last_name = Column(String, nullable=False)
    first_name = Column(String, nullable=False)
    specialty = Column(String, nullable=False)
    email = Column(String, unique=True)
    department = Column(String)

    consultations = relationship("Consultation", back_populates="doctor")


class Consultation(Base):
    __tablename__ = "Consultation"
    consultation_id = Column(Integer, primary_key=True)
    patient_id = Column(Integer, ForeignKey("Patient.patient_id"))
    doctor_id = Column(Integer, ForeignKey("Doctor.doctor_id"))
    consultation_date = Column(TIMESTAMP)
    reason = Column(Text)
    weight_kg = Column(DECIMAL(5,2))
    blood_pressure = Column(String)

    patient = relationship("Patient", back_populates="consultations")
    doctor = relationship("Doctor", back_populates="consultations")
    diagnoses = relationship("Diagnosis", back_populates="consultation")


class Diagnosis(Base):
    __tablename__ = "Diagnosis"
    diagnosis_id = Column(Integer, primary_key=True)
    consultation_id = Column(Integer, ForeignKey("Consultation.consultation_id"))
    icd10_code = Column(String)
    description = Column(Text, nullable=False)
    severity = Column(String)

    consultation = relationship("Consultation", back_populates="diagnoses")
    treatments = relationship("Treatment", back_populates="diagnosis")


class Treatment(Base):
    __tablename__ = "Treatment"
    treatment_id = Column(Integer, primary_key=True)
    diagnosis_id = Column(Integer, ForeignKey("Diagnosis.diagnosis_id"))
    medication = Column(String)
    dosage = Column(String)
    start_date = Column(Date)
    end_date = Column(Date)
    nursing_notes = Column(Text)

    diagnosis = relationship("Diagnosis", back_populates="treatments")


class MedicalRecord(Base):
    __tablename__ = "MedicalRecord"
    record_id = Column(Integer, primary_key=True)
    patient_id = Column(Integer, ForeignKey("Patient.patient_id"))
    creation_date = Column(Date)
    family_history = Column(Text)
    allergies = Column(Text)
    vaccination_status = Column(Text)
    last_update = Column(TIMESTAMP)

    patient = relationship("Patient", back_populates="medical_records")


# ------------------------
# Database engine & session
# ------------------------
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
# Connect to medical.db in the same folder
DATABASE_URL = f"sqlite:///{os.path.join(BASE_DIR, 'medical.db')}"

engine = create_engine(DATABASE_URL, connect_args={"check_same_thread": False})
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# ------------------------
# Create tables if they don't exist
# ------------------------
Base.metadata.create_all(bind=engine)