CREATE TABLE users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(80) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role ENUM('admin','technician','reviewer','readonly') NOT NULL DEFAULT 'technician', active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE buildings (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(80) NOT NULL UNIQUE, name VARCHAR(200), address VARCHAR(255), notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE locations (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, building_id BIGINT UNSIGNED NOT NULL, floor VARCHAR(80) NOT NULL DEFAULT '', room VARCHAR(80) NOT NULL DEFAULT '', area VARCHAR(160) NOT NULL DEFAULT '', UNIQUE KEY unique_location(building_id,floor,room,area), FOREIGN KEY(building_id) REFERENCES buildings(id));
CREATE TABLE assets (id CHAR(36) PRIMARY KEY, asset_tag VARCHAR(150) UNIQUE NULL, location_id BIGINT UNSIGNED NOT NULL, equipment_type VARCHAR(160) NOT NULL, manufacturer VARCHAR(160), model VARCHAR(160), serial_number VARCHAR(160), description TEXT, manufacture_date DATE NULL, installation_date DATE NULL, capacity_rating VARCHAR(160), voltage VARCHAR(80), power_type VARCHAR(100), condition_label VARCHAR(100), operational_status VARCHAR(100), specifications TEXT, notes TEXT, review_status ENUM('draft','needs_review','reviewed') NOT NULL DEFAULT 'needs_review', created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(location_id) REFERENCES locations(id), FOREIGN KEY(created_by) REFERENCES users(id), INDEX idx_serial(serial_number), INDEX idx_type(equipment_type));
CREATE TABLE asset_photos (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, asset_id CHAR(36) NOT NULL, category ENUM('overview','nameplate','other') NOT NULL, file_path VARCHAR(255) NOT NULL, mime_type VARCHAR(80) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(asset_id) REFERENCES assets(id) ON DELETE CASCADE);
CREATE TABLE service_records (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, asset_id CHAR(36) NOT NULL, service_date DATE, service_type ENUM('preventive','repair','other') NOT NULL DEFAULT 'other', notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(asset_id) REFERENCES assets(id));
CREATE TABLE audit_log (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NULL, action VARCHAR(80) NOT NULL, entity_type VARCHAR(80), entity_id VARCHAR(150), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE sync_operations (id CHAR(36) PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, operation_type VARCHAR(80) NOT NULL, entity_id VARCHAR(150), status VARCHAR(40) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);