USE akrcsist_bd0003_2026;

ALTER TABLE usuario_sistema
  ADD COLUMN IF NOT EXISTS id_docente INT UNSIGNED NULL AFTER rol,
  ADD COLUMN IF NOT EXISTS requiere_cambio_clave TINYINT(1) NOT NULL DEFAULT 0 AFTER estado,
  ADD COLUMN IF NOT EXISTS clave_actualizada_en DATETIME NULL AFTER ultimo_acceso;

SET @idx_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'usuario_sistema'
    AND INDEX_NAME = 'idx_usuario_docente'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE usuario_sistema ADD INDEX idx_usuario_docente (id_docente)',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @fk_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE()
    AND TABLE_NAME = 'usuario_sistema'
    AND CONSTRAINT_NAME = 'fk_usuario_docente'
);
SET @sql := IF(@fk_exists = 0,
  'ALTER TABLE usuario_sistema ADD CONSTRAINT fk_usuario_docente FOREIGN KEY (id_docente) REFERENCES docente(id_docente)',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE usuario_sistema
SET rol = CASE WHEN usuario = 'admin' THEN 'ADMIN' ELSE rol END
WHERE usuario = 'admin';