CREATE TABLE atributo (
  atributo_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  atributo VARCHAR(40) NOT NULL,
  valor VARCHAR(60) NOT NULL,
  activo ENUM('S','N') NOT NULL DEFAULT 'S',
  insert_user varchar(20) NOT NULL,
  insert_datetime datetime NOT NULL,
  update_user varchar(20) NOT NULL,
  update_datetime datetime NOT NULL,
  PRIMARY KEY (atributo_id),
  CONSTRAINT FOREIGN KEY (insert_user) REFERENCES usuario (usuario_id),
  CONSTRAINT FOREIGN KEY (update_user) REFERENCES usuario (usuario_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE tarea (
  tarea_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tipo_registro_anterior_id SMALLINT UNSIGNED NULL,
  nro_registro_anterior MEDIUMINT UNSIGNED NULL,
  fecha DATE NOT NULL,
  tipo_registro_id SMALLINT UNSIGNED NULL,
  nro_registro MEDIUMINT UNSIGNED NULL,
  tema TEXT NOT NULL,
  comentario TEXT NULL,
  estado_id SMALLINT UNSIGNED NULL,
  plazo_respuesta SMALLINT UNSIGNED NULL,
  asignado_a_usuario_id VARCHAR(20) NULL,
  insert_user varchar(20) NOT NULL,
  insert_datetime datetime NOT NULL,
  update_user varchar(20) NOT NULL,
  update_datetime datetime NOT NULL,
  PRIMARY KEY (tarea_id),
  CONSTRAINT FOREIGN KEY (tipo_registro_anterior_id) REFERENCES atributo (atributo_id),
  CONSTRAINT FOREIGN KEY (tipo_registro_id) REFERENCES atributo (atributo_id),
  CONSTRAINT FOREIGN KEY (asignado_a_usuario_id) REFERENCES usuario (usuario_id),
  CONSTRAINT FOREIGN KEY (insert_user) REFERENCES usuario (usuario_id),
  CONSTRAINT FOREIGN KEY (update_user) REFERENCES usuario (usuario_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE tarea_documento (
  tarea_documento_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tarea_id INT UNSIGNED NOT NULL,
  tipo_documento_id SMALLINT UNSIGNED NOT NULL,
  nro_documento MEDIUMINT UNSIGNED NOT NULL,
  adjunto MEDIUMBLOB NOT NULL,
  tipo_adjunto VARCHAR(128) NOT NULL,
  nombre_adjunto VARCHAR(128) NOT NULL,
  insert_user varchar(20) NOT NULL,
  insert_datetime datetime NOT NULL,
  PRIMARY KEY (tarea_documento_id),
  CONSTRAINT FOREIGN KEY (tarea_id) REFERENCES tarea (tarea_id),
  CONSTRAINT FOREIGN KEY (tipo_documento_id) REFERENCES atributo (atributo_id),
  CONSTRAINT FOREIGN KEY (insert_user) REFERENCES usuario (usuario_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE parametros
ADD max_doc_attach TINYINT UNSIGNED NOT NULL AFTER max_lines_export,
ADD max_doc_size SMALLINT UNSIGNED NOT NULL AFTER max_doc_attach,
ADD plazo_estado_id VARCHAR(256) NULL AFTER max_doc_size,
ADD estado_default_id SMALLINT UNSIGNED NULL AFTER max_doc_size,
ADD estado_supervisor_id SMALLINT UNSIGNED NULL AFTER estado_default_id,
ADD mis_asignaciones_estado_id VARCHAR(256) NULL AFTER plazo_estado_id,
ADD estado_sin_asignar_id SMALLINT UNSIGNED NULL AFTER estado_supervisor_id,
ADD estado_abro_signacion_id SMALLINT UNSIGNED NULL AFTER estado_sin_asignar_id,
ADD CONSTRAINT FOREIGN KEY (estado_default_id) REFERENCES atributo (atributo_id),
ADD CONSTRAINT FOREIGN KEY (estado_supervisor_id) REFERENCES atributo (atributo_id),
ADD CONSTRAINT FOREIGN KEY (estado_sin_asignar_id) REFERENCES atributo (atributo_id),
ADD CONSTRAINT FOREIGN KEY (estado_abro_asignacion_id) REFERENCES atributo (atributo_id);

ALTER TABLE usuario ADD cargo VARCHAR(128) NULL AFTER apellido;

CREATE TABLE tarea_hist (
  tarea_hist_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tarea_id INT UNSIGNED NOT NULL,
  asignado_a_usuario_id VARCHAR(20) NOT NULL,
  insert_user varchar(20) NOT NULL,
  insert_datetime datetime NOT NULL,
  PRIMARY KEY (tarea_hist_id),
  CONSTRAINT FOREIGN KEY (tarea_id) REFERENCES tarea (tarea_id),
  CONSTRAINT FOREIGN KEY (asignado_a_usuario_id) REFERENCES usuario (usuario_id),
  CONSTRAINT FOREIGN KEY (insert_user) REFERENCES usuario (usuario_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;