from typing import List, Optional

from sqlalchemy import Column, Float, ForeignKey, Integer, String, TIMESTAMP, Table, Text, text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
import datetime

class Base(DeclarativeBase):
    pass


class Datasets(Base):
    __tablename__ = 'Datasets'

    Dataset_name: Mapped[str] = mapped_column(Text)
    Row_count: Mapped[int] = mapped_column(Integer)
    Dataset_type: Mapped[str] = mapped_column(Text)
    Dataset_ID: Mapped[Optional[int]] = mapped_column(Integer, primary_key=True)
    Dataset_description: Mapped[Optional[str]] = mapped_column(Text)
    Uploaded_at: Mapped[Optional[datetime.datetime]] = mapped_column(TIMESTAMP, server_default=text('CURRENT_TIMESTAMP'))
    Status: Mapped[Optional[str]] = mapped_column(Text, server_default=text("'pending'"))


class Models(Base):
    __tablename__ = 'Models'

    ModelID: Mapped[Optional[int]] = mapped_column(Integer, primary_key=True)
    Model_name: Mapped[str] = mapped_column(Text, nullable=False)
    Model_type: Mapped[str] = mapped_column(Text, nullable=False)
    Created_at: Mapped[Optional[datetime.datetime]] = mapped_column(TIMESTAMP, server_default=text('CURRENT_TIMESTAMP'))
    Description: Mapped[Optional[str]] = mapped_column(Text)
    DatasetID: Mapped[Optional[int]] = mapped_column(Integer, ForeignKey('Datasets.Dataset_ID'))
    ModelFilePath: Mapped[Optional[str]] = mapped_column(Text)
    Data_type: Mapped[Optional[str]] = mapped_column(Text)
    Performance_score: Mapped[Optional[float]] = mapped_column(Float)

    # 新增评分指标字段
    MAE: Mapped[Optional[float]] = mapped_column(Float)
    RMSE: Mapped[Optional[float]] = mapped_column(Float)
    CV_score: Mapped[Optional[float]] = mapped_column(Float)

    ModelParameters: Mapped[List['ModelParameters']] = relationship('ModelParameters', back_populates='Models_')

class CurrentReduce(Base):
    __tablename__ = 'current_reduce'

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    Q_over_b: Mapped[float] = mapped_column(Float)
    pH: Mapped[float] = mapped_column(Float)
    OM: Mapped[float] = mapped_column(Float)
    CL: Mapped[float] = mapped_column(Float)
    H: Mapped[float] = mapped_column(Float)
    Al: Mapped[float] = mapped_column(Float)


class CurrentReflux(Base):
    __tablename__ = 'current_reflux'

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    OM: Mapped[float] = mapped_column(Float)
    CL: Mapped[float] = mapped_column(Float)
    CEC: Mapped[float] = mapped_column(Float)
    H_plus: Mapped[float] = mapped_column(Float)
    N: Mapped[float] = mapped_column(Float)
    Al3_plus: Mapped[float] = mapped_column(Float)
    Delta_pH: Mapped[float] = mapped_column(Float)


class ModelParameters(Base):
    __tablename__ = 'ModelParameters'

    ParamID: Mapped[int] = mapped_column(Integer, primary_key=True)
    ModelID: Mapped[int] = mapped_column(ForeignKey('Models.ModelID'))
    ParamName: Mapped[str] = mapped_column(Text)
    ParamValue: Mapped[str] = mapped_column(Text)

    Models_: Mapped['Models'] = relationship('Models', back_populates='ModelParameters')