"""Ajoute sites, données techniques et mouvements quantitatifs.

Revision ID: c4d8e9f0a1b2
Revises: b3c7d8e9f0a1
Create Date: 2026-08-14
"""
from alembic import op
import sqlalchemy as sa

revision = "c4d8e9f0a1b2"
down_revision = "b3c7d8e9f0a1"
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        "sites",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("name", sa.String(length=120), nullable=False),
        sa.Column("site_type", sa.String(length=40), nullable=False),
        sa.Column("address", sa.Text(), nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=True),
        sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("name"),
    )
    op.add_column("buildings", sa.Column("site_id", sa.Integer(), nullable=True))
    op.create_foreign_key("fk_buildings_site", "buildings", "sites", ["site_id"], ["id"])
    op.create_index("ix_buildings_site_id", "buildings", ["site_id"])

    equipment_columns = (
        sa.Column("mobility", sa.String(length=20), nullable=False, server_default="non_precise"),
        sa.Column("serial_number", sa.String(length=120), nullable=True),
        sa.Column("manufacturer", sa.String(length=120), nullable=True),
        sa.Column("model_reference", sa.String(length=120), nullable=True),
        sa.Column("supplier_id", sa.Integer(), nullable=True),
        sa.Column("supplier_reference", sa.String(length=120), nullable=True),
        sa.Column("purchase_price", sa.Numeric(12, 2), nullable=True),
        sa.Column("purchase_date", sa.Date(), nullable=True),
        sa.Column("lifecycle_status", sa.String(length=30), nullable=False, server_default="en_service"),
        sa.Column("unavailable_since", sa.DateTime(), nullable=True),
        sa.Column("planned_disposal_date", sa.Date(), nullable=True),
        sa.Column("disposal_date", sa.Date(), nullable=True),
        sa.Column("disposal_reason", sa.String(length=500), nullable=True),
        sa.Column("recurrence_monitoring", sa.Boolean(), nullable=False, server_default=sa.false()),
    )
    for column in equipment_columns:
        op.add_column("equipments", column)
    op.create_foreign_key("fk_equipments_supplier", "equipments", "companies", ["supplier_id"], ["id"])
    op.create_index("ix_equipments_serial_number", "equipments", ["serial_number"])

    op.create_table(
        "equipment_quantity_movements",
        sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False),
        sa.Column("root_equipment_id", sa.Integer(), nullable=False),
        sa.Column("source_equipment_id", sa.Integer(), nullable=True),
        sa.Column("target_equipment_id", sa.Integer(), nullable=True),
        sa.Column("source_room_id", sa.Integer(), nullable=True),
        sa.Column("target_room_id", sa.Integer(), nullable=False),
        sa.Column("quantity", sa.Integer(), nullable=False),
        sa.Column("reason", sa.String(length=255), nullable=True),
        sa.Column("moved_by_id", sa.Integer(), nullable=True),
        sa.Column("moved_at", sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(["root_equipment_id"], ["equipments.id"]),
        sa.ForeignKeyConstraint(["source_equipment_id"], ["equipments.id"]),
        sa.ForeignKeyConstraint(["target_equipment_id"], ["equipments.id"]),
        sa.ForeignKeyConstraint(["source_room_id"], ["rooms.id"]),
        sa.ForeignKeyConstraint(["target_room_id"], ["rooms.id"]),
        sa.ForeignKeyConstraint(["moved_by_id"], ["users.id"], ondelete="SET NULL"),
        sa.PrimaryKeyConstraint("id"),
        sa.CheckConstraint("quantity > 0", name="ck_equipment_quantity_movement_positive"),
    )
    op.create_index("ix_equipment_quantity_movements_root_equipment_id", "equipment_quantity_movements", ["root_equipment_id"])


def downgrade():
    op.drop_table("equipment_quantity_movements")
    op.drop_index("ix_equipments_serial_number", table_name="equipments")
    op.drop_constraint("fk_equipments_supplier", "equipments", type_="foreignkey")
    for name in (
        "recurrence_monitoring", "disposal_reason", "disposal_date", "planned_disposal_date",
        "unavailable_since", "lifecycle_status", "purchase_date", "purchase_price",
        "supplier_reference", "supplier_id", "model_reference", "manufacturer", "serial_number", "mobility",
    ):
        op.drop_column("equipments", name)
    op.drop_index("ix_buildings_site_id", table_name="buildings")
    op.drop_constraint("fk_buildings_site", "buildings", type_="foreignkey")
    op.drop_column("buildings", "site_id")
    op.drop_table("sites")