import uuid
from datetime import datetime, date
from sqlalchemy import (
    Column, String, Text, Boolean, Integer, Numeric, Date,
    DateTime, ForeignKey, Enum as SAEnum, UniqueConstraint, Index
)
from sqlalchemy.dialects.postgresql import UUID, INET, MACADDR, JSONB, ARRAY
from sqlalchemy.orm import relationship
from app.database import Base


class Location(Base):
    __tablename__ = "locations"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name = Column(Text, nullable=False, unique=True)
    description = Column(Text)
    parent_id = Column(UUID(as_uuid=True), ForeignKey("locations.id"), nullable=True)
    metadata_ = Column("metadata", JSONB, default={})
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    deleted_at = Column(DateTime(timezone=True), nullable=True)


class User(Base):
    __tablename__ = "users"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    username = Column(Text, nullable=False, unique=True)
    email = Column(Text, nullable=False, unique=True)
    full_name = Column(Text)
    role = Column(SAEnum("admin", "editor", "viewer", name="user_role"), nullable=False, default="viewer")
    team = Column(Text)
    is_active = Column(Boolean, nullable=False, default=True)
    password_hash = Column(Text, nullable=False)
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    deleted_at = Column(DateTime(timezone=True), nullable=True)


class CIClass(Base):
    __tablename__ = "ci_classes"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name = Column(Text, nullable=False, unique=True)
    description = Column(Text)
    parent_id = Column(UUID(as_uuid=True), ForeignKey("ci_classes.id"), nullable=True)
    icon = Column(Text)
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    deleted_at = Column(DateTime(timezone=True), nullable=True)


class CIType(Base):
    __tablename__ = "ci_types"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    class_id = Column(UUID(as_uuid=True), ForeignKey("ci_classes.id"), nullable=False)
    name = Column(Text, nullable=False)
    description = Column(Text)
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    deleted_at = Column(DateTime(timezone=True), nullable=True)

    __table_args__ = (
        UniqueConstraint("class_id", "name"),
    )


class ConfigurationItem(Base):
    __tablename__ = "configuration_items"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    ci_type_id = Column(UUID(as_uuid=True), ForeignKey("ci_types.id"), nullable=False)
    name = Column(Text, nullable=False)
    description = Column(Text)
    status = Column(
        SAEnum("active", "inactive", "maintenance", "deprecated", "planned", name="ci_status"),
        nullable=False, default="active"
    )
    location_id = Column(UUID(as_uuid=True), ForeignKey("locations.id"), nullable=True)
    serial_number = Column(Text)
    asset_tag = Column(Text)
    purchase_date = Column(Date)
    warranty_expiry = Column(Date)
    attributes = Column(JSONB, default={})
    tags = Column(ARRAY(Text), default=[])
    created_by = Column(UUID(as_uuid=True), ForeignKey("users.id"))
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    deleted_at = Column(DateTime(timezone=True), nullable=True)
    version = Column(Integer, nullable=False, default=1)

    ci_type = relationship("CIType", lazy="joined")
    location = relationship("Location", lazy="joined")
    created_by_user = relationship("User", lazy="joined")

    __table_args__ = (
        Index("idx_ci_name_type_active", "name", "ci_type_id", unique=True,
              postgresql_where="deleted_at IS NULL"),
    )


class IPAddress(Base):
    __tablename__ = "ip_addresses"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    ci_id = Column(UUID(as_uuid=True), ForeignKey("configuration_items.id"), nullable=False)
    ip_address = Column(INET, nullable=False)
    subnet_mask = Column(INET)
    gateway = Column(INET)
    dns_servers = Column(ARRAY(INET))
    is_primary = Column(Boolean, nullable=False, default=False)
    vlan_id = Column(Integer)
    dhcp_enabled = Column(Boolean, default=False)
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    deleted_at = Column(DateTime(timezone=True), nullable=True)


class NetworkInterface(Base):
    __tablename__ = "network_interfaces"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    ci_id = Column(UUID(as_uuid=True), ForeignKey("configuration_items.id"), nullable=False)
    name = Column(Text, nullable=False)
    mac_address = Column(MACADDR)
    speed_mbps = Column(Integer)
    interface_type = Column(Text, default="ethernet")
    is_up = Column(Boolean, default=True)
    ip_address_id = Column(UUID(as_uuid=True), ForeignKey("ip_addresses.id"), nullable=True)
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    deleted_at = Column(DateTime(timezone=True), nullable=True)


class HardwareDetail(Base):
    __tablename__ = "hardware_details"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    ci_id = Column(UUID(as_uuid=True), ForeignKey("configuration_items.id"), nullable=False)
    manufacturer = Column(Text)
    model = Column(Text)
    cpu_model = Column(Text)
    cpu_cores = Column(Integer)
    ram_gb = Column(Numeric(10, 2))
    storage_gb = Column(Numeric(10, 2))
    storage_type = Column(Text)
    form_factor = Column(Text)
    power_supply = Column(Text)
    bios_version = Column(Text)
    serial_number = Column(Text)
    specs = Column(JSONB, default={})
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    deleted_at = Column(DateTime(timezone=True), nullable=True)


class SoftwareInstance(Base):
    __tablename__ = "software_instances"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    ci_id = Column(UUID(as_uuid=True), ForeignKey("configuration_items.id"), nullable=False)
    name = Column(Text, nullable=False)
    vendor = Column(Text)
    version = Column(Text)
    license_key = Column(Text)
    license_type = Column(Text)
    install_path = Column(Text)
    config_path = Column(Text)
    port = Column(Integer)
    protocol = Column(Text)
    start_command = Column(Text)
    service_user = Column(Text)
    config = Column(JSONB, default={})
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    deleted_at = Column(DateTime(timezone=True), nullable=True)


class CIRelationship(Base):
    __tablename__ = "ci_relationships"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    source_ci_id = Column(UUID(as_uuid=True), ForeignKey("configuration_items.id"), nullable=False)
    target_ci_id = Column(UUID(as_uuid=True), ForeignKey("configuration_items.id"), nullable=False)
    relationship = Column(
        SAEnum("depends_on", "connected_to", "hosted_on", "runs_on",
               "manages", "contains", "part_of", "related_to", name="relationship_type"),
        nullable=False
    )
    description = Column(Text)
    metadata_ = Column("metadata", JSONB, default={})
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)
    deleted_at = Column(DateTime(timezone=True), nullable=True)

    source_ci = relationship("ConfigurationItem", foreign_keys=[source_ci_id], lazy="joined")
    target_ci = relationship("ConfigurationItem", foreign_keys=[target_ci_id], lazy="joined")

    __table_args__ = (
        UniqueConstraint("source_ci_id", "target_ci_id", "relationship"),
    )


class ChangeLog(Base):
    __tablename__ = "changelog"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    ci_id = Column(UUID(as_uuid=True), ForeignKey("configuration_items.id"), nullable=False)
    action = Column(
        SAEnum("create", "update", "delete", "restore",
               "relationship_add", "relationship_remove", name="change_action"),
        nullable=False
    )
    changed_by = Column(UUID(as_uuid=True), ForeignKey("users.id"))
    field_name = Column(Text)
    old_value = Column(Text)
    new_value = Column(Text)
    snapshot = Column(JSONB)
    version = Column(Integer, nullable=False)
    comment = Column(Text)
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow)