CREATE TABLE assets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    inventarnummer VARCHAR(255) UNIQUE NOT NULL,
    bezeichnung VARCHAR(500) NOT NULL,
    kategorie_id INT,
    standort_id INT,
    abteilung_id INT,
    raum VARCHAR(100),
    lieferant VARCHAR(255),
    seriennummer VARCHAR(255),
    anschaffungsdatum DATE,
    anschaffungspreis DECIMAL(10,2),
    garantie_bis DATE,
    zustand ENUM('neu', 'gut', 'befriedigend', 'schlecht', 'defekt') DEFAULT 'gut',
    status ENUM('aktiv', 'inaktiv', 'ausgemustert') DEFAULT 'aktiv',
    kostenstelle VARCHAR(50),
    notizen TEXT,
    created_by INT,
    updated_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (kategorie_id) REFERENCES categories(id) ON DELETE SET NULL,
    FOREIGN KEY (standort_id) REFERENCES locations(id) ON DELETE SET NULL,
    FOREIGN KEY (abteilung_id) REFERENCES departments(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_inventarnummer (inventarnummer),
    INDEX idx_kategorie_id (kategorie_id),
    INDEX idx_standort_id (standort_id),
    INDEX idx_status (status),
    INDEX idx_seriennummer (seriennummer),
    INDEX idx_anschaffungsdatum (anschaffungsdatum),
    INDEX idx_garantie_bis (garantie_bis)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;