import os
import datetime
import sqlalchemy as sa
from decimal import Decimal
from sqlmodel import SQLModel, Session, Field, create_engine, Relationship, select


class Single(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True, gt=-1, lt=2**32)

    humanName: str = Field(max_length=10, unique=True)
    creationDateUTC: datetime.datetime = Field(default_factory=lambda: datetime.datetime.now(datetime.timezone.utc))

    caracs: list["Carac"] = Relationship(back_populates="single", cascade_delete=True)


class Carac(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True, gt=-1, lt=2**32)

    dateUTC: datetime.datetime = Field(default_factory=lambda: datetime.datetime.now(datetime.timezone.utc))

    singleId: int = Field(foreign_key="single.id", ondelete="CASCADE")
    single: Single = Relationship(back_populates="caracs")

    steps: list["Step"] = Relationship(back_populates="carac", cascade_delete=True)


class Step(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True, gt=-1, lt=2**32)

    caracId: int = Field(foreign_key="carac.id", ondelete="CASCADE")
    carac: Carac = Relationship(back_populates="steps")

    capas: list["CapaPoint"] = Relationship(back_populates="step", cascade_delete=True)
    # zernikes: list["ZernikePoint"] = Relationship(back_populates="step", cascade_delete=True)


class CapaPoint(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True, gt=-1, lt=2**64)

    capa_pF: Decimal = Field(max_digits=5, decimal_places=3)
    t_ms: int = Field(gt=-1, lt=2**32)

    stepId: int = Field(foreign_key="step.id", ondelete="CASCADE")
    step: Step = Relationship(back_populates="capas")


# class ZernikePoint(SQLModel, table=True):
#     id: int | None = Field(default=None, primary_key=True, gt=-1, lt=2**64)

#     t_ms: int = Field(gt=-1, lt=2**32)
#     rms: Decimal = Field(max_digits=5, decimal_places=3)

#     stepId: int = Field(foreign_key="step.id", ondelete="CASCADE")
#     step: Step = Relationship(back_populates="zernikes")

#     coeffs: list["ZernikeCoeff"] = Relationship(back_populates="point", cascade_delete=True)


# class ZernikeCoeff(SQLModel, table=True):
#     id: int | None = Field(default=None, primary_key=True, gt=-1, lt=2**64)

#     order: int = Field(gt=0, lt=2**8)
#     value: Decimal = Field(max_digits=5, decimal_places=3)

#     pointId: int = Field(foreign_key="zernikepoint.id", ondelete="CASCADE")
#     point: ZernikePoint = Relationship(back_populates="coeffs")


url = sa.URL.create(drivername="postgresql+psycopg2", username="banc", password=os.getenv("DB_PASSWORD"), host="127.0.0.0", port="4242")
engine = create_engine(url)

SQLModel.metadata.create_all(engine)


def create_data():
    with Session(engine) as session:
        singles = [Single(humanName="S666"), Single(humanName="S4242")]
        caracs = (Carac(single=single) for single in singles)
        steps = (Step(carac=carac) for carac in caracs for _ in range(200))
        capas = (CapaPoint(step=step, capa_pF=0.9, t_ms=12) for step in steps for _ in range(20))
        # zernikes = (ZernikePoint(step=step, t_ms=12, rms=0.12) for step in steps for _ in range(20))
        # coeffs = (ZernikeCoeff(point=point, order=o, value=0.23) for point in zernikes for o in range(21))

        # session.add_all(coeffs)
        session.add_all(capas)
        session.commit()


def display_data():
    with Session(engine) as session:
        stmt = select(Single).where(Single.humanName == "S666")
        results = session.exec(stmt)
        single = results.first()
        print(single.caracs[0].steps[0].capas)


create_data()
display_data()