CREATE DATABASE IF NOT EXISTS mantenimiento_campamento CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE mantenimiento_campamento; CREATE TABLE IF NOT EXISTS modulos ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, codigo VARCHAR(20) NOT NULL UNIQUE COMMENT 'Identificador estable para QR/API', nombre VARCHAR(150) NOT NULL, activo TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS ubicaciones ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, codigo VARCHAR(20) NOT NULL UNIQUE COMMENT 'Identificador estable en QR (?u=UB-00001)', modulo_id INT UNSIGNED NOT NULL, nombre VARCHAR(150) NOT NULL, activo TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (modulo_id) REFERENCES modulos(id) ON DELETE RESTRICT, INDEX idx_modulo (modulo_id) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS empresas ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(150) NOT NULL UNIQUE, activo TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS servicios ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, empresa_id INT UNSIGNED NOT NULL, nombre VARCHAR(150) NOT NULL, activo TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE CASCADE, UNIQUE KEY uk_empresa_servicio (empresa_id, nombre) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS registros ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, ubicacion_id INT UNSIGNED NOT NULL, empresa_id INT UNSIGNED NOT NULL, servicio_id INT UNSIGNED NOT NULL, observaciones TEXT, registrado_por VARCHAR(100), created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (ubicacion_id) REFERENCES ubicaciones(id) ON DELETE RESTRICT, FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE RESTRICT, FOREIGN KEY (servicio_id) REFERENCES servicios(id) ON DELETE RESTRICT, INDEX idx_fecha (created_at), INDEX idx_ubicacion (ubicacion_id) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS usuarios ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, usuario VARCHAR(50) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, nombre VARCHAR(100), activo TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB;